Author: johnf
Posted: 2007-08-31 at 18:52:28
On Friday 31 August 2007 14:26, dw wrote:
> Hello:
> I have used the Class Designer with a sqlite3 database to create a grid
> that displays the look up value
> that corresponds to foreign key value, as described in archived posts and
> the Wiki. When I use the
> SQL Builder to create the query which contains a field alias, things
> display properly. When I create the identical
> SQL statement manually and utilize UserSQL, I generate an error. The manual
> SQL statement works when I run it
> directly against sqlite3 and the database. I am interested in using UserSQL
> and manual SQL statements rather
> than SQL Builder, in order to create SQL statements that contain Table
> aliases. SQL Builder does not seem to
> allow Table aliases although it does permit field aliases.
> I am running Windows XP Pro SP2, Python Version: 2.4.4 on win32, Dabo
> Version: Version 0.9a; Revision 3335,
> UI Version: 2.8.1.1 on wxMSW and SQLite version 3.3.12.
>
>
>
> The functioning code is:
> /**********works****************/
> class FotoinfBizobj(dabo.biz.dBizobj):
> def afterInit(self):
> self.DataSource = "FotoInf"
> self.KeyField = "count0"
> self.LinkField = "recordID_fk"
>
> self.addFrom("FotoInf")
> self.addField("recordID_fk")
> self.addField("FInfoID_fk")
> self.addField("count0")
> self.addJoin("InfoPT", "FotoInf.FInfoID_fk= InfoPT.InfoID")
> self.addField("InfoPT.photoType as phType")
>
> def validateRecord(self):
> """Returning anything other than an empty string from
> this method will prevent the data from being saved.
> """
> ret = ""
> # Add your business rules here.
> return ret
>
>
> fotoinfBizobj = FotoinfBizobj(self.Connection)
> self.addBizobj(fotoinfBizobj)
>
> fotoinfBizobj.setLimitClause(None)
> recnameBizobj.addChild(fotoinfBizobj)
>
>
> The code that generates the error is:
> /******************does not work***********************************/
>
> class FotoinfBizobj(dabo.biz.dBizobj):
> def afterInit(self):
> self.DataSource = "FotoInf"
> self.KeyField = "count0"
> self.LinkField = "recordID_fk"
>
>
>
> def validateRecord(self):
> """Returning anything other than an empty string from
> this method will prevent the data from being saved.
> """
> ret = ""
> # Add your business rules here.
> return ret
>
>
> fotoinfBizobj = FotoinfBizobj(self.Connection)
> self.addBizobj(fotoinfBizobj)
>
> testsql = """Select FotoInf.count0, FotoInf.recordID_fk,
> FotoInf.FInfoID_fk, InfoPT.photoType as phType
> From FotoInf inner join InfoPT on FotoInf.FInfoID_fk = InfoPT.InfoID"""
> fotoinfBizobj.UserSQL= testsql
>
> fotoinfBizobj.setLimitClause(None)
> recnameBizobj.addChild(fotoinfBizobj)
I don't use SQLite so take what I'm saying with a grain of salt. When I need
to add a field from a join table I also insure that the field is in my
NonUpdate statement as in;
self.NonUpdateFields=["luspecies"]
self.addField("agspecies.species as luspecies")
self.addJoin('public.agvarieties','agvarieties.pkid=agpoptrs.fk_variety',joinType='left
outer')
It's my understanding a bizobj can only update one table.
--
John Fabiani