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 generated error is:
Traceback (most recent call last):
File "C:\projects\dabo\lib\eventMixin.py", line 97, in raiseEvent
bindingFunction(event)
File "C:\projects\dabo\ui\uiwx\dPemMixin.py", line 1155, in __onUpdate
self.update()
File "C:\projects\dabo\ui\dDataControlMixinBase.py", line 109, in update
self.Value = method()
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)
thank you,
dwarder
On Aug 31, 2007, at 5:26 PM, dw wrote:
> The generated error is:
>
> Traceback (most recent call last):
> File "C:\projects\dabo\lib\eventMixin.py", line 97, in raiseEvent
> bindingFunction(event)
> File "C:\projects\dabo\ui\uiwx\dPemMixin.py", line 1155, in
> __onUpdate
> self.update()
> File "C:\projects\dabo\ui\dDataControlMixinBase.py", line 109, in
> update
> self.Value = method()
I have an idea what's going on here, but if you would be willing to
help me out a little more...
If you could modify C:\projects\dabo\ui\dDataControlMixinBase.py so
that the following two unnumbered debugging lines are added between
the current lines 108-109
107 if self.Source and self._srcIsBizobj:
108 # First see if DataField refers to a method of the bizobj:
print "SOURCE", self.Source
print "DATAFIELD", self.DataField
109 method = getattr(self.Source, self.DataField, None)
110 if method is not None:
111 self.Value = method()
Run the code that uses the UserSQL version, and then post the
debugging output here.
-- Ed Leafe
Ed Leafe <ed@...> writes:
>
> On Aug 31, 2007, at 5:26 PM, dw wrote:
>
>
> I have an idea what's going on here, but if you would be willing to
> help me out a little more...
>
> If you could modify C:\projects\dabo\ui\dDataControlMixinBase.py so
> that the following two unnumbered debugging lines are added between
> the current lines 108-109
>
> 107 if self.Source and self._srcIsBizobj:
> 108 # First see if DataField refers to a method of the bizobj:
> print "SOURCE", self.Source
> print "DATAFIELD", self.DataField
> 109 method = getattr(self.Source, self.DataField, None)
> 110 if method is not None:
> 111 self.Value = method()
>
> Run the code that uses the UserSQL version, and then post the
> debugging output here.
>
With the modifications, the debugging output is:
Dabo Info Log: Fri Aug 31 19:19:37 2007: 1 database connection definition(s)
loaded.
Dabo Info Log: Fri Aug 31 19:19:38 2007: User interface set to 'wx' by dApp.
Dabo Info Log: Fri Aug 31 19:19:38 2007: wxPython Version: 2.8.1.1 wxMSW
(unicode)
Dabo Info Log: Fri Aug 31 19:19:38 2007: WARNING: No BasePrefKey has been set
for this application.
SOURCE <__builtin__.RecnameBizobj (baseclass dabo.biz.dBizobj, id:34985392)>
DATAFIELD RecID
SOURCE <__builtin__.RecnameBizobj (baseclass dabo.biz.dBizobj, id:34985392)>
DATAFIELD Name0
SOURCE <__builtin__.RecnameBizobj (baseclass dabo.biz.dBizobj, id:34985392)>
DATAFIELD location0
SOURCE <__builtin__.RecnameBizobj (baseclass dabo.biz.dBizobj, id:34985392)>
DATAFIELD Date0
SOURCE <__builtin__.RecnameBizobj (baseclass dabo.biz.dBizobj, id:34985392)>
DATAFIELD InfSource0
SOURCE <__builtin__.RecnameBizobj (baseclass dabo.biz.dBizobj, id:34985392)>
DATAFIELD RecSource0
SOURCE <__builtin__.RecnameBizobj (baseclass dabo.biz.dBizobj, id:34985392)>
DATAFIELD StorageBox0
SOURCE <__builtin__.RecnameBizobj (baseclass dabo.biz.dBizobj, id:34985392)>
DATAFIELD Type0_fk
SOURCE <__builtin__.RecnameBizobj (baseclass dabo.biz.dBizobj, id:34985392)>
DATAFIELD HideOnline
SOURCE <__builtin__.RecnameBizobj (baseclass dabo.biz.dBizobj, id:34985392)>
DATAFIELD Descript0
SOURCE <__builtin__.RecnameBizobj (baseclass dabo.biz.dBizobj, id:34985392)>
DATAFIELD RecID
SOURCE <__builtin__.RecnameBizobj (baseclass dabo.biz.dBizobj, id:34985392)>
DATAFIELD Name0
SOURCE <__builtin__.RecnameBizobj (baseclass dabo.biz.dBizobj, id:34985392)>
DATAFIELD location0
SOURCE <__builtin__.RecnameBizobj (baseclass dabo.biz.dBizobj, id:34985392)>
DATAFIELD Date0
SOURCE <__builtin__.RecnameBizobj (baseclass dabo.biz.dBizobj, id:34985392)>
DATAFIELD InfSource0
SOURCE <__builtin__.RecnameBizobj (baseclass dabo.biz.dBizobj, id:34985392)>
DATAFIELD RecSource0
SOURCE <__builtin__.RecnameBizobj (baseclass dabo.biz.dBizobj, id:34985392)>
DATAFIELD StorageBox0
SOURCE <__builtin__.RecnameBizobj (baseclass dabo.biz.dBizobj, id:34985392)>
DATAFIELD Type0_fk
SOURCE <__builtin__.RecnameBizobj (baseclass dabo.biz.dBizobj, id:34985392)>
DATAFIELD HideOnline
SOURCE <__builtin__.RecnameBizobj (baseclass dabo.biz.dBizobj, id:34985392)>
DATAFIELD Descript0
Dabo Info Log: Fri Aug 31 19:19:50 2007: Application finished.
Traceback (most recent call last):
File "C:\projects\dabo\dabo\ui\uiwx\dGrid.py", line 297, in IsEmptyCell
return not bizobj.getFieldVal(field, row)
File "C:\projects\dabo\dabo\biz\dBizobj.py", line 1285, in getFieldVal
return cursor.getFieldVal(fld, row)
File "C:\projects\dabo\dabo\db\dCursorMixin.py", line 781, in getFieldVal
raise dException.FieldNotFoundException, "%s '%s' %s" % (
dabo.dException.FieldNotFoundException: Field 'phType' does not exist in the
data set
dwarder
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
On Aug 31, 2007, at 7:52 PM, johnf wrote:
> 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;
Any field that is not in the DataSource's field list is
automatically marked as non-updatable.
-- Ed Leafe
On Aug 31, 2007, at 7:28 PM, dw wrote:
> File "C:\projects\dabo\dabo\db\dCursorMixin.py", line 781, in
> getFieldVal
> raise dException.FieldNotFoundException, "%s '%s' %s" % (
> dabo.dException.FieldNotFoundException: Field 'phType' does not
> exist in the
> data set
That's odd - adding a couple of print statements shouldn't change
the error.
Are you sure that the UserSQL is set to a statement that has a field
alias of 'phType'? Your original example did.
-- Ed Leafe
Ed Leafe <ed@...> writes:
>
> That's odd - adding a couple of print statements shouldn't change
> the error.
>
> Are you sure that the UserSQL is set to a statement that has a field
> alias of 'phType'? Your original example did.
>
I apologize. The originally posted debug output was from a different error. The
actual error is the same as is generated with the altered
dDataControlMixinBase.py file. When SQL Builder constructs the
query and the field alias 'phType' is added,
field 'phType' is found in the dataset. When the same SQL is constructed
manually and UserSQL is invoked, the field alias 'phType' is not found in the
dataset, although it is created in the query :
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"""
/***debug output that should have accompanied the original post********/
Dabo Info Log: Thu Aug 30 22:51:57 2007: 1 database connection definition(s)
loaded.
Dabo Info Log: Thu Aug 30 22:51:58 2007: User interface set to 'wx' by dApp.
Dabo Info Log: Thu Aug 30 22:51:58 2007: wxPython Version: 2.8.1.1 wxMSW
(unicode)
Dabo Info Log: Thu Aug 30 22:51:59 2007: WARNING: No BasePrefKey has been set
for this application.
Traceback (most recent call last):
File "C:\projects\dabo\dabo\ui\uiwx\dGrid.py", line 297, in IsEmptyCell
return not bizobj.getFieldVal(field, row)
File "C:\projects\dabo\dabo\biz\dBizobj.py", line 1285, in getFieldVal
return cursor.getFieldVal(fld, row)
File "C:\projects\dabo\dabo\db\dCursorMixin.py", line 781, in getFieldVal
raise dException.FieldNotFoundException, "%s '%s' %s" % (
dabo.dException.FieldNotFoundException: Field 'phType' does not exist in the
data set
/*****debug output after the dDataControlMixinBase.py was altered******/
Dabo Info Log: Fri Aug 31 19:19:37 2007: 1 database connection definition(s)
loaded.
Dabo Info Log: Fri Aug 31 19:19:38 2007: User interface set to 'wx' by dApp.
Dabo Info Log: Fri Aug 31 19:19:38 2007: wxPython Version: 2.8.1.1 wxMSW
(unicode)
Dabo Info Log: Fri Aug 31 19:19:38 2007: WARNING: No BasePrefKey has been set
for this application.
Dabo Info Log: Fri Aug 31 19:19:50 2007: Application finished.
Traceback (most recent call last):
File "C:\projects\dabo\dabo\ui\uiwx\dGrid.py", line 297, in IsEmptyCell
return not bizobj.getFieldVal(field, row)
File "C:\projects\dabo\dabo\biz\dBizobj.py", line 1285, in getFieldVal
return cursor.getFieldVal(fld, row)
File "C:\projects\dabo\dabo\db\dCursorMixin.py", line 781, in getFieldVal
raise dException.FieldNotFoundException, "%s '%s' %s" % (
dabo.dException.FieldNotFoundException: Field 'phType' does not exist in the
data set
dwarder
On Aug 31, 2007, at 11:18 PM, dw wrote:
> I apologize. The originally posted debug output was from a
> different error. The
> actual error is the same as is generated with the altered
> dDataControlMixinBase.py file. When SQL Builder constructs the
> query and the field alias 'phType' is added,
> field 'phType' is found in the dataset. When the same SQL is
> constructed
> manually and UserSQL is invoked, the field alias 'phType' is not
> found in the
> dataset, although it is created in the query :
>
> 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"""
I'm stumped. I just used the Class Designer to create a form, and
create a grid on it using the Data Environment wizard as you did. I
used a connection file to an SQLite database that I use for tracking
suspicious IP addresses that hit my server. I changed the DataField
for a column to an alias that didn't exist in the table, and then
changed the generated bizobj code to delete the SQL Builder stuff,
and define the UserSQL to include the aliased field. Here's the
bizobj code:
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
def createBizobjs(self):
class IphitsBizobj(dabo.biz.dBizobj):
def afterInit(self):
self.DataSource = "iphits"
self.KeyField = "pkid"
self.UserSQL = """select pkid, hittime, ipaddress as crappo
from iphits
order by hittime desc"""
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
iphitsBizobj = IphitsBizobj(self.Connection)
self.addBizobj(iphitsBizobj)
def afterInitAll(self):
self.requery()
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
The grid column that was originally created with a DataField of
'ipaddress' was changed so that it now had DataField = "crappo". I
ran the design, and everything appeared as expected. From the running
design, I pressed Cmd-D to bring up a command window, and typed
'print self.PrimaryBizobj.getDataSet()', and the output showed a
series of rows, each of which had a key named 'crappo'.
Is it possible that SQLite is changing the case of the alias name?
Try this: right after your self.requery(), add the line:
print self.PrimaryBizobj.getDataSet()[0].keys()
...in order to see what the fields in your data set are actually named.
-- Ed Leafe
Ed Leafe <ed@...> writes:
>
>
> I'm stumped. I just used the Class Designer to create a form, and
> create a grid on it using the Data Environment wizard as you did. I
> used a connection file to an SQLite database that I use for tracking
> suspicious IP addresses that hit my server. I changed the DataField
> for a column to an alias that didn't exist in the table, and then
> changed the generated bizobj code to delete the SQL Builder stuff,
> and define the UserSQL to include the aliased field. Here's the
> bizobj code:
>
> The grid column that was originally created with a DataField of
> 'ipaddress' was changed so that it now had DataField = "crappo". I
> ran the design, and everything appeared as expected. From the running
> design, I pressed Cmd-D to bring up a command window, and typed
> 'print self.PrimaryBizobj.getDataSet()', and the output showed a
> series of rows, each of which had a key named 'crappo'.
>
> Is it possible that SQLite is changing the case of the alias name?
> Try this: right after your self.requery(), add the line:
>
> print self.PrimaryBizobj.getDataSet()[0].keys()
>
> ...in order to see what the fields in your data set are actually named.
>
the output with print self.PrimaryBizobj.getDataSet()[0].keys() is:
['Date0', 'StorageBox0', 'Type0_fk', 'location0', 'InfSource0', 'Descript0',
'Name0',
'RecSource0', 'RecID', 'HideOnline']
Dabo Info Log: Sat Sep 01 12:56:48 2007: Application finished.
Traceback (most recent call last):
File "C:\projects\dabo\dabo\ui\uiwx\dGrid.py", line 297, in IsEmptyCell
return not bizobj.getFieldVal(field, row)
File "C:\projects\dabo\dabo\biz\dBizobj.py", line 1285, in getFieldVal
return cursor.getFieldVal(fld, row)
File "C:\projects\dabo\dabo\db\dCursorMixin.py", line 781, in getFieldVal
raise dException.FieldNotFoundException, "%s '%s' %s" % (
dabo.dException.FieldNotFoundException: Field 'phType' does not exist in the
data set
However, my grid is filled with a dataset that is a child of the PrimaryBizobj,
so the field alias and other grid fields are not listed with
print self.PrimaryBizobj.getDataSet()[0].keys()
dwarder
On Sep 1, 2007, at 1:05 PM, dw wrote:
> However, my grid is filled with a dataset that is a child of the
> PrimaryBizobj,
> so the field alias and other grid fields are not listed with
> print self.PrimaryBizobj.getDataSet()[0].keys()
OK, then we need something other than PrimaryBizobj.
biz = self.getBizobj("your_bizobj_name")
print biz.getDataSet()[0].keys()
Change "your_bizobj_name" to the DataSource of the bizobj in question.
-- Ed Leafe