johnf wrote:
> On Sunday 22 February 2009 02:41:43 pm Ricardo Aráoz wrote:
>
>> johnf wrote:
>>
>>> My German friend has told me that the code works. He uses unicode so I
>>> thought it might help if we went over the information that we have as of
>>> today. I'm also starting a new thread.
>>>
>> Sorry, he uses unicode WHERE? In the database engine, in the Python
>> interpreter (there are two versions), in wx (also two versions)? There
>> are many variables here relating to unicode.
>>
>>
>>> The issue is the method getTables() in dbMsSQL.py.
>>>
>> And in getFields() too (you find that out after you correct getTables()).
>>
>>
>>> We know that the connection information is being passed correctly. You
>>> added debug statements to the code and print statements reflected that
>>> the information was correct.
>>>
>>> If you copy the getTables() code and then replace dbName with a hard
>>> coded string it works. You used dShell to run the experiment.
>>>
>> Yes
>>
>>
>>> If you replace the {'db':dbName} with {'db':str(dbName)} in the code it
>>> works.
>>>
>> Yes.
>> I prefer to use "dbName = str(self.database)" in the first line of the
>> getTables() method, but I guess that is a matter of style.
>> You must also do the same with dbName and tableName in the getFields()
>> method so that everything works ok.
>>
>>
>>> It was pointed out that psmssql.py wants a tuple. So we changed the code
>>> to:
>>>
>>> cursor.execute("select table_name from INFORMATION_SCHEMA.TABLES where
>>> table_catalog = '%s' and table_type = 'BASE TABLE' order by table_name" %
>>> (dbName,))
>>>
>>>
>>> The above code uses a tuple but did not work. And again you needed to
>>> change the dbName to str(dbName) to get it to work.
>>>
>> Not so, the above code works because the string replacement is done
>> prior to sending the string to cursor.execute. But if you place a comma
>> instead of the percent sign it won't work. The problem with the above
>> code is that it may lead to injection attacks, and it is stressed in
>> every document that you should not use it.
>>
>>
>>> I know the code works for me on linux and windows.
>>>
>>> The traceback you get is
>>> Traceback (most recent call last):
>>> File "C:\Documents and Settings\raraoz\dabo\ui\uiwx\dControlMixin.py",
>>> line 27
>>> , in _onWxHit
>>> File "C:\Python25\lib\site-packages\dabo\dabo\ui\uiwx\dPemMixin.py",
>>> line 940,
>>> in raiseEvent
>>> super(dPemMixin, self).raiseEvent(eventClass, nativeEvent, *args,
>>> **kwargs)
>>> File "C:\Python25\lib\site-packages\dabo\lib\eventMixin.py", line 93,
>>> in raise
>>> Event
>>> File "C:\Documents and Settings\raraoz\dabo\ui\dialogs\Wizard.py",
>>> line 124, i
>>> n onNext
>>> File "C:\Documents and Settings\raraoz\dabo\ui\dialogs\Wizard.py",
>>> line 291, i
>>> n _setCurrPage
>>> File
>>> "C:\Python25\Lib\site-packages\dabo\IDE\wizards\QuickLayoutWizard.py", li
>>> ne 63, in onLeavePage
>>> return self.Wizard.makeConnection()
>>> File
>>> "C:\Python25\Lib\site-packages\dabo\IDE\wizards\QuickLayoutWizard.py", li
>>> ne 788, in makeConnection
>>> tbls = crs.getTables()
>>> File "C:\Python25\lib\site-packages\dabo\db\dCursorMixin.py", line
>>> 1947, in ge
>>> tTables
>>> File "C:\Python25\lib\site-packages\dabo\dabo\db\dbMsSQL.py", line 79,
>>> in getT
>>> ables
>>> {'db':dbName} )
>>> File "C:\Python25\lib\site-packages\dabo\db\dCursorMixin.py", line
>>> 330, in exe
>>> cute
>>> dabo.dException.DBQueryException: internal error: None
>>> SQL: select table_name from INFORMATION_SCHEMA.TABLES where
>>> table_catalog = %(db
>>> )s and table_type = 'BASE TABLE' order by table_name
>>>
>>>
>>>
>>> Ricardo have I covered everything?
>>>
>> I guess so John
>>
>> Cheers
>>
>> Ricardo
>>
>
> Please post directly to the list. It's not that I no longer want to help but
> the solution is beyond my current understanding of unicode. We need more
> people involved.
>
Sorry, thought I had posted to the list.
> The guy in Germany uses both unicode in the database and of course wxPython.
> I assume you are too.
>
> The problem with using your code is I get errors:
>
> using (notice I removed the '%' replaced with a comma)
> cursor.execute("""select table_name
> from INFORMATION_SCHEMA.TABLES
> where table_catalog = %s
> and table_type = 'BASE TABLE'
> order by table_name""", (dbName, ))
>
> I get:
>
>
> Traceback (most recent call last):
> File "/home/johnf/downloads/dabo/dabo/ui/uiwx/dControlMixin.py", line 27,
> in
> _
> onWxHit
> self.raiseEvent(dEvents.Hit, evt, *args, **kwargs)
> File "/home/johnf/downloads/dabo/dabo/ui/uiwx/dPemMixin.py", line 940, in
> rais
> eEvent
> super(dPemMixin, self).raiseEvent(eventClass, nativeEvent, *args,
> **kwargs)
> File "/home/johnf/downloads/dabo/dabo/lib/eventMixin.py", line 93, in
> raiseEve
> nt
> bindingFunction(event)
> File "/home/johnf/downloads/dabo/dabo/ui/dialogs/Wizard.py", line 124, in
> onNe xt
> self.CurrentPage += pg.nextPage()
> File "/home/johnf/downloads/dabo/dabo/ui/dialogs/Wizard.py", line 291, in
> _set CurrPage
> ok = self._pages[self._currentPage].onLeavePage(direction)
> File "/home/johnf/downloads/dabo/ide/wizards/QuickLayoutWizard.py", line
> 63,
> i n onLeavePage
> return self.Wizard.makeConnection()
> File "/home/johnf/downloads/dabo/ide/wizards/QuickLayoutWizard.py", line
> 788, in makeConnection
> tbls = crs.getTables()
> File "/home/johnf/downloads/dabo/dabo/db/dCursorMixin.py", line 1947, in
> getTa bles
> return self.BackendObject.getTables(self.AuxCursor, includeSystemTables)
> File "/home/johnf/downloads/dabo/dabo/db/dbMsSQL.py", line 85, in getTables
> order by table_name""", (dbName, ))
> File "/home/johnf/downloads/dabo/dabo/db/dCursorMixin.py", line 321, in
> execut e
> errMsg = unicode(e)
> UnicodeDecodeError: 'ascii' codec can't decode byte 0xa0 in position 101:
> ordina l not in
> range(128)
>
> If I use str(self.database) I get
> "Key errors: None" which I have not traced down. I am using the
> default "Northwind" database. But I don't get the error when I used the
> current code. So something is wrong with that solution too.
>
> I have asked Ed to get involved but so far he does not have a solution
> either.
>
>
Could you try in your system replacing in dbmssql.py the following methods?
This is what works for me, not the code that's causing your errors, it
would be interesting to see if it also works for you or what error trace
does it output.
(notice the only lines I've changed are the first instruction in
getTables() and the first two instructions in getFields() )
---------------------------------------------------------------------------------------------------
def getTables(self, cursor, includeSystemTables=False):
# jfcs 11/01/06 assumed public schema
# cfk: this worries me: how does it know what db is being used?
# tempCursor.execute("select name from sysobjects where xtype =
'U' order by name")
dbName = str(self.database)
cursor.execute("select table_name"
" from INFORMATION_SCHEMA.TABLES"
" where table_catalog = %(db)s"
" and table_type = 'BASE TABLE'"
" order by table_name",
{'db':dbName} )
rs = cursor.getDataSet()
tables = [x["table_name"] for x in rs]
tables = tuple(tables)
return tables
---------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
def getFields(self, tableName, cursor):
""" Returns the list of fields of the passed table
field: ( fieldname, dabo data type, key )
"""
# fairly standard way of getting column settings
# this may be standard enough to put in the super class
dbName = str(self.database)
tableName = str(tableName)
cursor.execute(
"select COLUMN_NAME, DATA_TYPE"
" from INFORMATION_SCHEMA.COLUMNS"
" where table_catalog = %(db)s"
" and table_name = %(table)s"
" order by ORDINAL_POSITION",
{'table':tableName, 'db':dbName} )
fieldDefs = cursor.getDataSet()
cursor.execute(
"select COLUMN_NAME "
" from information_schema.Constraint_Column_Usage CCU"
" join information_schema.TABLE_CONSTRAINTS TC"
" on CCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME"
" where"
" CONSTRAINT_TYPE = 'PRIMARY KEY'"
" and TC.CONSTRAINT_CATALOG = %(db)s"
" and TC.Table_Name = %(table)s",
{'table':tableName, 'db':dbName} )
pkFields = cursor.getDataSet()
fields = []
for r in fieldDefs:
name = r["COLUMN_NAME"]
ft = self._fieldTypeNativeToDabo(r["DATA_TYPE"])
pk = (name,) in [(p["COLUMN_NAME"], ) for p in pkFields]
fields.append((name, ft, pk))
return tuple(fields)
---------------------------------------------------------------------------------------------------
--- StripMime Report -- processed MIME parts ---
multipart/alternative
text/plain (text body -- kept)
text/html
---
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-users
Searchable Archives: http://leafe.com/archives/search/dabo-users
This message: http://leafe.com/archives/byMID/[email protected]
