Sybase query via python not working when using arguments

[christhedonstar]

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

 

[IPGuru]

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.

 

[feherke]

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/

 

[christhedonstar]

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

 

[christhedonstar]

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)

 

[feherke]

Hi

I would check [tt]__version__[/tt].

Note that I never used Sybase, so checking the version was only a hint.


Feherke.

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

 

[christhedonstar]

0.39 is the version. Cheers. So its not that...

 

[christhedonstar]

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.

 

[feherke]

Hi

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

[b]select[/b] @city [b]as[/b] whatever[teal];[/teal]

[b]select[/b] [green][i]'@city'[/i][/green] [b]as[/b] whatever[teal];[/teal]

To see if anything is returned and if yes, what.

( Supposing that Sybase supports the above syntax. )

Feherke.

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

 

[christhedonstar]

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

 

[christhedonstar]

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...

 

[feherke]

Hi

That looks like the parameter handling would be messed.

Generally there are two ways to pass parameters to a prepared statement :
[ul]
[li]by name[/li]
[li]by order number[/li]
[/ul]
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 :

sqlToRun [teal]=[/teal][green][i] '''Select @test,@best,@rest '''[/i][/green]    
curs[teal].[/teal][COLOR=darkgoldenrod]execute[/color][teal]([/teal]sqlToRun[teal],[/teal] [teal][[/teal] [green][i]'first'[/i][/green][teal],[/teal] [green][i]'second'[/i][/green][teal],[/teal] [green][i]'third'[/i][/green] [teal]])[/teal]

Feherke.

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

 

[feherke]

Hi

Submitted to soon.

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

sqlToRun [teal]=[/teal][green][i]  '''Select ?,?,? '''[/i][/green]    
curs[teal].[/teal][COLOR=darkgoldenrod]execute[/color][teal]([/teal]sqlToRun[teal],[/teal] [teal][[/teal] [green][i]'first'[/i][/green][teal],[/teal] [green][i]'second'[/i][/green][teal],[/teal] [green][i]'third'[/i][/green] [teal]])[/teal]

Feherke.

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

 

[christhedonstar]

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

 

[feherke]

Hi

Just one things.

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 :
[ul]
[li]client sends the statement to prepare - the server parses it and translates it into its own internal format[/li]
[li]client sends the parameters - the server puts them to their place into the statement and executes it[/li]
[/ul]
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/

 

[christhedonstar]

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?

 

[christhedonstar]

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?

 

[feherke]

Hi

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.

What factor of speed benefit would we be talking here?

No idea. That is Sybase related and I not know Sybase.

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 [tt]execute()[/tt] 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 [tt]executemany()[/tt] that is much probable :

curs[teal].[/teal][COLOR=darkgoldenrod]executemany[/color][teal]([/teal]sqlToRun[teal],[/teal] [teal][[/teal]
  [teal]([/teal] [green][i]'first'[/i][/green][teal],[/teal] [green][i]'second'[/i][/green][teal],[/teal] [green][i]'third'[/i][/green] [teal]),[/teal]
  [teal]([/teal] [green][i]'forth'[/i][/green][teal],[/teal] [green][i]'fifth'[/i][/green][teal],[/teal]  [green][i]'sixth'[/i][/green] [teal])[/teal]
[teal]])[/teal]

Feherke.

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