Ricardo Aráoz wrote:
> johnf wrote:
>> On Tuesday 10 February 2009 04:07:55 pm Ricardo Aráoz wrote:
>>
>>> SQL: select table_name from INFORMATION_SCHEMA.TABLES where
>>> table_catalog = %(db
>>> )s and table_type = 'BASE TABLE' order by table_name
>>>
>>
>> I think the error should include the expanded version of the statement? It
>> looks like 'db' is not expanded to the database name.
>>
>> Please tell me what version of MsSQL you are using.
>> Please copy your connection file code into your reply.
>>
>> Try placing a print statement in the method to determine if you are getting
>> the database.
>>
>> 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 = self.database
>> ## add the line below
>> print dbName
>> 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
>>
>>
> Ok, long set of tries. Even deleted (by hand, wish we had uninstall
> for python modules) _mssql.pyd, pymssql-0.8.0-py2.5.egg-info (and the
> one from 1.0.1, which was still there), pymssql.py (pyc, pyo) and
> ntwdblib.dll just to be certain nothing remained from my installation
> of 1.0.1 (which I suspected might be the culprit). Then reinstalled
> 0.8.0 from the exe installer and started testing.
> I placed this statement : """ print dbName, type(dbName)""" as you
> told me, and the result was """somedb <type 'unicode'>""" which is
> correct. Then kept toying and got this to work (of course had to do
> something similar in both queries of getFields, bit unsafe but I'm not
> in production yet) :
> """
> qry = ("select table_name from INFORMATION_SCHEMA.TABLES"
> " where table_catalog = '%(db)s' and table_type =
> 'BASE TABLE'"
> " order by table_name" % {'db':dbName})
> cursor.execute(qry)
> """
> instead of
> """
> 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} )
> """
> I even tried :
> """
> 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} )
> """ (notice the ' around "%(db)s")
> and
> """
> cursor.execute("select table_name"
> " from INFORMATION_SCHEMA.TABLES"
> " where table_catalog = %s"
> " and table_type = 'BASE TABLE'"
> " order by table_name",
> dbName )
> """
> and
> """
> cursor.execute("select table_name"
> " from INFORMATION_SCHEMA.TABLES"
> " where table_catalog = '%s'"
> " and table_type = 'BASE TABLE'"
> " order by table_name",
> dbName )
> """
> and
> """
> cursor.execute("select table_name"
> " from INFORMATION_SCHEMA.TABLES"
> " where table_catalog = %s"
> " and table_type = 'BASE TABLE'"
> " order by table_name",
> (dbName, ) )
> """
> and
> """
> cursor.execute("select table_name"
> " from INFORMATION_SCHEMA.TABLES"
> " where table_catalog = '%s'"
> " and table_type = 'BASE TABLE'"
> " order by table_name",
> (dbName, ) )
> """
> but nothing (in the error messages dbName was not expanded into the
> query). So I'm guessing it has something to do with the "execute" method?
> Anyway, my db engine is SQLServer 2000 (8.00.194) in case it helps.
> As for now I can go on, but it would be nice to find out what's going
> on. Could it have something to do with having installed 1.0.1?
>
> TIA
>
Ok, got some free time and did the following :
--------------------------------------------------------------------------------------------------------------------------
>>> import pymssql
>>> pymssql.__version__
'0.8.0'
>>> conn = pymssql.connect(user='user', password='pass', host='host',
database='datab')
>>> crsr = conn.cursor()
>>> crsr.execute('select TablasVSID, Nombre from TablasVs (NOLOCK)')
>>> crsr.rowcount
7
>>> class reg(object):
... def __init__(self, cursor, registro):
... for (attr, val) in zip((d[0] for d in cursor.description),
registro) :
... setattr(self, attr, val)
...
>>> for row in crsr.fetchall() :
... r = reg(crsr, row)
... print r.TablasVSID, r.Nombre
...
1 ABCodOp
2 ABMovStock
3 ABAlmacen
4 ABGrupo
5 ABTipo
6 ABUnidad
7 ABArticulo
>>> letra = 'o'
>>> crsr.execute("select TablasVSID, Nombre from TablasVs (NOLOCK) where
Nombre like '%%' + %s + '%%'", letra)
>>> for row in crsr.fetchall() :
... r = reg(crsr, row)
... print r.TablasVSID, r.Nombre
...
1 ABCodOp
2 ABMovStock
4 ABGrupo
5 ABTipo
7 ABArticulo
>>> letra = 'n'
>>> crsr.execute("select TablasVSID, Nombre from TablasVs (NOLOCK) where
Nombre like '%%' + %(db)s + '%%'", {'db':letra})
>>> for row in crsr.fetchall() :
... r = reg(crsr, row)
... print r.TablasVSID, r.Nombre
...
3 ABAlmacen
6 ABUnidad
>>> letra = 'r'
>>> crsr.execute("select TablasVSID, Nombre from TablasVs (NOLOCK) where
Nombre like '%%' + %(db)s + '%%'", {'db':letra})
>>> for row in crsr.fetchall() :
... r = reg(crsr, row)
... print r.TablasVSID, r.Nombre
...
4 ABGrupo
7 ABArticulo
>>>
--------------------------------------------------------------------------------------------------------------------------
So I am completely disoriented here. Module pymssql seems to work ok.
But the same kind of statements in dabo's dbMsSQL.py raises an error.
Any ideas?
--- 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]
