×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Sybase query via python not working when using arguments

Sybase query via python not working when using arguments

Sybase query via python not working when using arguments

(OP)
sqlToRun  = '''SELECT TOP 1 * FROM ADDRESS WHERE ad_city = @city '''
    curs.execute(sqlToRun, {'@city': 'NEW YORK' })
    #sqlToRun  = '''SELECT TOP 1 * FROM ADDRESS WHERE ad_city = 'NEW YORK' '''    
    #curs.execute(sqlToRun)

   inlineSqlResults = curs.fetchall()
    print inlineSqlResults

Any idea why the commented stuff returns a result but the uncommented does not?

Thanks in advance,

Chris

RE: Sybase query via python not working when using arguments

try printing sqlToRun before you execute it to see if it actually a valid Query. i suspect you might be missing some Quotes in the resultant string.
 

RE: Sybase query via python not working when using arguments

Hi

Would be useful to know the class of the curs object.

If it is the Sybase module's Cursor object, then the query syntax should be correct. ( Unless you are not using the ( apparently ) latest version 0.39 and there are some version incompatibilities. )

Feherke.
http://free.rootshell.be/~feherke/

RE: Sybase query via python not working when using arguments

(OP)
Yes it the cursor object. I couldn't figure out how to tell which version we were using (I thought of that also). There were were a few version properties on the module but most of them returned much higher numbers than 0.39. I thought that maybe 0.39 was the package version and that what I really needed to check was something else, but google didn't help me with that. Do you know how to check the version?

Thanks,

Chris

RE: Sybase query via python not working when using arguments

(OP)
These are the things I've tried:

>>> Sybase.CS_VER_STRING
9144
>>> Sybase.CS_CURRENT_VERSION
112
>>> Sybase.CS_PACKAGE_CMD
152
>>> Sybase.CS_TDS_VERSION
9105
>>> Sybase.CS_VER_STRING
9144
>>> Sybase.sys()
TypeError: 'module' object is not callable

>>> Sybase.sys
<module 'sys' (built-in)>
>>> Sybase.sys.api_version
1013
>>> Sybase.sys.version
'2.6.4 (r264:75706, Feb  4 2010, 19:04:16) [MSC v.1500 32 bit (Intel)]'
>>> Sybase.sys.version_info
(2, 6, 4, 'final', 0)
 

RE: Sybase query via python not working when using arguments

(OP)
0.39 is the version. Cheers. So its not that...

RE: Sybase query via python not working when using arguments

(OP)
sqlToRun was in first instance

SELECT TOP 1 * FROM ADDRESS WHERE ad_city = @city

In the second it was

SELECT TOP 1 * FROM ADDRESS WHERE ad_city = 'NEW YORK'

So in the first I thought @city would have been replaced with 'NEW YORK' by the excute method...

Maybe I can turn on logging in the database to see whats happening.

RE: Sybase query via python not working when using arguments

Hi

One thing I would try for debugging, is shortening it :

CODE

select @city as whatever;

select '@city' as whatever;
To see if anything is returned and if yes, what.

( Supposing that Sybase supports the above syntax. )

Feherke.
http://free.rootshell.be/~feherke/

RE: Sybase query via python not working when using arguments

(OP)
Great suggestion am trying now. Just doing select 'me' which should return me

RE: Sybase query via python not working when using arguments

(OP)
So....
sqlToRun = '''Select @test '''    
curs.execute(sqlToRun, {'@test': 'me'})
print curs.fetchall()

returns [('@test',)]

sqlToRun = '''Select @test '''    
curs.execute(sqlToRun, { 'me': '@test'})
print curs.fetchall()

returns [('me',)]

sqlToRun = '''Select @test '''    
curs.execute(sqlToRun, { 'me': '@thisshouldntwork'})
print curs.fetchall()

returns [('me',)]

not exactly what I was expecting...




 

RE: Sybase query via python not working when using arguments

Hi

That looks like the parameter handling would be messed.

Generally there are two ways to pass parameters to a prepared statement :
  • by name
  • by order number
I already saw case when those two ways were messed and the documentation said one and the library used the other. ( I can not remember the circumstances, but was neither Python nor Sybase. )

If my theory is correct, the following will work :

CODE

sqlToRun = ''Select @test,@best,@rest ''    
curs.execute(sqlToRun, [ 'first', 'second', 'third' ])

Feherke.
http://free.rootshell.be/~feherke/

RE: Sybase query via python not working when using arguments

Hi

Submitted to soon.

And if the above works, try this, as this is the usual way to pass parameters by order number :

CODE

sqlToRun =  ''Select ?,?,? ''    
curs.execute(sqlToRun, [ 'first', 'second', 'third' ])

Feherke.
http://free.rootshell.be/~feherke/

RE: Sybase query via python not working when using arguments

(OP)
thanks only just read these replies, but came to the same conclusion earlier. But I thought it was down to our implementation - not the documentation being wrong!!

This for me is not much better than using

sqlToRun = 'Select %s %s %s' % ('first', 'second', 'third')

Although documentation said not to do that based on the fact it would not be dynamic, but if I creat sqlToRun again then to me it would be dynamic... Unless I'm missing something.

I was really keen to have named value pairs to change the sql (Its very long unfortunately) so I've resorted to create a function which takes a dictionary and replaces the arguments with value in the dictionary...

Thanks for the replies and let me know if you have any comments?

Cheers,

Chris

RE: Sybase query via python not working when using arguments

Hi

Just one things.

Quote (Chris):

sqlToRun = 'Select %s %s %s' % ('first', 'second', 'third')

Although documentation said not to do that based on the fact it would not be dynamic, but if I creat sqlToRun again then to me it would be dynamic...
Dynamic here should mean prepared statement. They are like functions with parameters and are executed in two steps :
  • client sends the statement to prepare - the server parses it and translates it into its own internal format
  • client sends the parameters - the server puts them to their place into the statement and executes it
The benefit comes as speed improvement when the second step is executed multiple times, without the need to execute the first step again and again.

Beside that, the server escapes the parameters. So if you are not using parameters, you have to escape the values to avoid SQL injection attacks.
 

Feherke.
http://free.rootshell.be/~feherke/

RE: Sybase query via python not working when using arguments

(OP)
Cool Thanks. So the speed benefit only comes if you use the same cursor object correct?

What factor of speed benefit would we be talking here?

RE: Sybase query via python not working when using arguments

(OP)
furthermore to clarify these two statements:

curs.execute(sqlToRun, [ 'first',  'second', 'third' ])

curs.execute(sqlToRun, [ 'forth',  'fifth', 'sixth' ])

Does that mean that the server doesn't need to translate sqlToRun to internal format twice?

RE: Sybase query via python not working when using arguments

Hi

Quote (Chris):

So the speed benefit only comes if you use the same cursor object correct?
The server could optimize it with a string comparison if it stores both the original and the parsed statement.

Quote (Chris):

What factor of speed benefit would we be talking here?
No idea. That is Sybase related and I not know Sybase.

Quote (Chris):

curs.execute(sqlToRun, [ 'first',  'second', 'third' ])

curs.execute(sqlToRun, [ 'forth',  'fifth', 'sixth' ])

Does that mean that the server doesn't need to translate sqlToRun to internal format twice?
Theoretically. I have no idea how execute() is executed behind the scene. The statement may be or may not be actually reused.

Given that in other databases/languages/database drivers the preparing and parameter binding usually is done by two distinct methods, I have a doubt that the statement will be actually reused.

However with executemany() that is much probable :

CODE

curs.executemany(sqlToRun, [
  ( 'first', 'second', 'third' ),
  ( 'forth', 'fifth',  'sixth' )
])

Feherke.
http://free.rootshell.be/~feherke/

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close