[Zope-DB] sql query works in database connector but not in ZSQL method

[robert rottermann]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Kevin Campbell schrieb:
> On Wed, Jan 17, 2007 at 03:42:04PM +0100, robert rottermann wrote:
>> -----BEGIN PGP SIGNED MESSAGE-----
>> Hash: SHA1
>>
>> Hi there,
>>
>> I try the following statement:
>> CALL selectVertexProperties(1, @error2)
>>
>> when I execute this directly in the test "window" of the database
>> connection, it works fine.
>> when i execute the same line in the test window of a ZSQL Method
>> then I get an error:
>>
>> Error, Products.mxODBCZopeDA.ZopeDA.ReplayTransaction: OperationalError
>> on : ('HYT00', 2014, "[unixODBC][MySQL][ODBC 3.51
>> Driver][mysqld-5.0.26]Commands out of sync; you can't run this command
>> now", 6113) SQL used:
>>
>> CALL selectVertexProperties(1, @error2)
>>
>> when I call this method from plain python, it works fine also.
>>
>> Any pointers what could be the reason would be greatly appreciated.
> 
> Robert,
> 
> Can you give an example of how you call this in plain python? It may be
> that in using plain python you are starting a new transaction, and there
> are previous sql commands which are causing problems. Perhaps some more
> details of the sql statements issued would be helpful also.
> 
> Kevin
> 
thanks for your answer,
 here is what I do in a plain python script (using MySQLdb):

def findUser(username):
    query = "CALL selectVertexIdFromLogin('%s', @id, @error1);" \
            "CALL selectVertexProperties(@id, @error2)" % username
    cursor.execute(query)
    cursor.nextset()
    print cursor.fetchall()

this is what I try to do in a method in a Zope product of ours:
    def getUserDataById(self, userid=''):
        "return base user data for user. if userid is not given use
looged in user"
        db = self.redscout_tool.scout_connection()
        sql_delimiter = self.sql_delimiter
        if not userid:
            userid = self.portal_membership.getAuthenticatedMember().getId()
        query = "CALL selectVertexIdFromLogin('%s', @id, @error); select
@error, @id" % userid
        result = db.query((query).replace(';', sql_delimiter))
        if result:
            result = result[1][0]
            error = int(result[0])
            if error:
                "handle error"
                return 'fehler'
            else:
                dbid = int(result[1])
                query =  "CALL selectVertexProperties(%s, @error2)" % dbid
- ----->>         result = db.query((query).replace(';', sql_delimiter))
                return result


thanks for your help
robert
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.5 (GNU/Linux)
Comment: Using GnuPG with SUSE - http://enigmail.mozdev.org

iD8DBQFFrkw9GaryJ0T9kUYRArRdAJ9KR+vSS8oF8zIdmkSXEdBJ3d4p9wCfaVtb
e1vDt07FPzJMYh0T4PqgEl4=
=NbWY
-----END PGP SIGNATURE-----