python-sybase-misc

RE: [python-sybase] python-sybase + FreeTDS + MS SQL Server

[Charles B. <Cha...@ut...>]

> -----Original Message-----
> From: Chris Curvey [mailto:cc...@ea...]
> Sent: Tuesday, August 03, 2004 9:31 AM
>=20
> -----Original Message-----
> From: Charles Bearden <Cha...@ut...>
> Sent: Aug 3, 2004 10:22 AM
>=20
> > -----Original Message-----
> > From: Kevin Jacobs [mailto:ja...@th...]
> > Sent: Monday, August 02, 2004 5:07 PM
> >
> > Charles Bearden wrote:
> >
> > >My question is how to supply authentication credentials to the MS
SQL
> > >Server.  I have to use a domain login userid like DOMAIN\username.
> When
> > >I create a Python script like this:
> > >
> > >------------------------------------------------------------
> > >import Sybase
> > >
> > >dsn =3D 'theDb'
> > >uid =3D 'MYDOMAIN\jrandomuser'
> > >pwd =3D 'h4x0r'
> > >cx =3D Sybase.connect(dsn, uid, pwd)
> > >
> > >cx.close()
> > >------------------------------------------------------------
> > >
> > >
> > >
> > Try: uid =3D 'MYDOMAIN\\jrandomuser'
> >
> > Don't forget Python's string quoting rules!  ;)
>=20
>=20
> Thanks for your response.  I should have mentioned that I had already
> tried several permutations of quoting, including raw strings and the
> escaped backslash you recommended.  Still no joy: the "Login failed
for
> user '(null)'" exception is still thrown.  And when I catch that
> exception to print 'uid' before re-raising the exception, it prints
the
> UID correctly, with the single backslash between domain and username
> (and it does so whether or not I've escaped the backslash).
>=20
> The UID and password I am actually using are my personal domain
> credentials that I type in every day to log on.  I'm puzzled by the
> '(null)' user thing.  Does that mean that the string I am passing as
UID
> is not getting through, or that it is not recognized?
>=20
>=20
> Are your user accounts on the SQL Server using the integrated NT login
for
> authentication, or are they using a separate password?  I don't know
for
> sure, but I'd wonder if FreeTDS supported the integrated login scheme.

Thanks for the suggestion--I'll check that out.  I'm a Linux exile in
Windows-land here, except that I'm trying to get this Linux host to talk
to MS SQL, and I am the first to admit that there is a great deal I
don't know about Windows authentication.

Chuck Bearden
Systems Analyst III
School of Health Information Sciences
University of Texas at Houston
713.500.3954 (voice)
713.500.3907 (fax)
Cha...@ut...

RE: [python-sybase] python-sybase + FreeTDS + MS SQL Server

[Chris C. <cc...@ea...>]

-----Original Message-----
From: Charles Bearden <Cha...@ut...>
Sent: Aug 3, 2004 10:22 AM
To: Kevin Jacobs <ja...@th...>
Cc: pyt...@ww...
Subject: RE: [python-sybase] python-sybase + FreeTDS + MS SQL Server

> -----Original Message-----
> From: Kevin Jacobs [mailto:ja...@th...]
> Sent: Monday, August 02, 2004 5:07 PM
> 
> Charles Bearden wrote:
> 
> >My question is how to supply authentication credentials to the MS SQL
> >Server.  I have to use a domain login userid like DOMAIN\username.
When
> >I create a Python script like this:
> >
> >------------------------------------------------------------
> >import Sybase
> >
> >dsn = 'theDb'
> >uid = 'MYDOMAIN\jrandomuser'
> >pwd = 'h4x0r'
> >cx = Sybase.connect(dsn, uid, pwd)
> >
> >cx.close()
> >------------------------------------------------------------
> >
> >
> >
> Try: uid = 'MYDOMAIN\\jrandomuser'
> 
> Don't forget Python's string quoting rules!  ;)


Thanks for your response.  I should have mentioned that I had already
tried several permutations of quoting, including raw strings and the
escaped backslash you recommended.  Still no joy: the "Login failed for
user '(null)'" exception is still thrown.  And when I catch that
exception to print 'uid' before re-raising the exception, it prints the
UID correctly, with the single backslash between domain and username
(and it does so whether or not I've escaped the backslash).

The UID and password I am actually using are my personal domain
credentials that I type in every day to log on.  I'm puzzled by the
'(null)' user thing.  Does that mean that the string I am passing as UID
is not getting through, or that it is not recognized?


Are your user accounts on the SQL Server using the integrated NT login for authentication, or are they using a separate password?  I don't know for sure, but I'd wonder if FreeTDS supported the integrated login scheme.

Curvey Consulting
www.chriscurvey.com

RE: [python-sybase] python-sybase + FreeTDS + MS SQL Server

[Charles B. <Cha...@ut...>]

> -----Original Message-----
> From: Kevin Jacobs [mailto:ja...@th...]
> Sent: Monday, August 02, 2004 5:07 PM
>=20
> Charles Bearden wrote:
>=20
> >My question is how to supply authentication credentials to the MS SQL
> >Server.  I have to use a domain login userid like DOMAIN\username.
When
> >I create a Python script like this:
> >
> >------------------------------------------------------------
> >import Sybase
> >
> >dsn =3D 'theDb'
> >uid =3D 'MYDOMAIN\jrandomuser'
> >pwd =3D 'h4x0r'
> >cx =3D Sybase.connect(dsn, uid, pwd)
> >
> >cx.close()
> >------------------------------------------------------------
> >
> >
> >
> Try: uid =3D 'MYDOMAIN\\jrandomuser'
>=20
> Don't forget Python's string quoting rules!  ;)


Thanks for your response.  I should have mentioned that I had already
tried several permutations of quoting, including raw strings and the
escaped backslash you recommended.  Still no joy: the "Login failed for
user '(null)'" exception is still thrown.  And when I catch that
exception to print 'uid' before re-raising the exception, it prints the
UID correctly, with the single backslash between domain and username
(and it does so whether or not I've escaped the backslash).

The UID and password I am actually using are my personal domain
credentials that I type in every day to log on.  I'm puzzled by the
'(null)' user thing.  Does that mean that the string I am passing as UID
is not getting through, or that it is not recognized?

Thanks,

Chuck Bearden
Systems Analyst III
School of Health Information Sciences
University of Texas at Houston
713.500.3954 (voice)
713.500.3907 (fax)
Cha...@ut...

Re: [python-sybase] python-sybase + FreeTDS + MS SQL Server

[Kevin J. <ja...@th...>]

Charles Bearden wrote:

>My question is how to supply authentication credentials to the MS SQL
>Server.  I have to use a domain login userid like DOMAIN\username.  When
>I create a Python script like this:
>
>------------------------------------------------------------
>import Sybase
>
>dsn = 'theDb'
>uid = 'MYDOMAIN\jrandomuser'
>pwd = 'h4x0r'
>cx = Sybase.connect(dsn, uid, pwd)
>
>cx.close()
>------------------------------------------------------------
>
>  
>
Try: uid = 'MYDOMAIN\\jrandomuser'

Don't forget Python's string quoting rules!  ;)

-Kevin

[python-sybase] python-sybase + FreeTDS + MS SQL Server

[Charles B. <Cha...@ut...>]

I have built freetds-0.62.4 on a RH9 box, and it seems to work based on
tests with tsql.  After setting the environment variables and proceeding
a la=20

  http://www.freetds.org/userguide/python.htm

I successfully installed the Python Sybase module.

My question is how to supply authentication credentials to the MS SQL
Server.  I have to use a domain login userid like DOMAIN\username.  When
I create a Python script like this:

------------------------------------------------------------
import Sybase

dsn =3D 'theDb'
uid =3D 'MYDOMAIN\jrandomuser'
pwd =3D 'h4x0r'
cx =3D Sybase.connect(dsn, uid, pwd)

cx.close()
------------------------------------------------------------

(where 'theDB' is an actually defined profile in my freetds.conf, and
'uid' contains a working domain username and 'pwd' the corresponding
password, I get an exception like so:

Sybase.DatabaseError: Msg 18452, Level 14, State 1
Login failed for user '(null)'. Reason: Not associated with a trusted
SQL Server
 connection.Layer: 0, Origin: 0
Login incorrect.

It seems not to like something about the way I specify the username.  I
checked the docs, and the python-sybase list archives back into last
year, and I didn't find anything to enlighten me.  Is this doable?

Thanks for any pointers,

Chuck Bearden
Systems Analyst III
School of Health Information Sciences
University of Texas at Houston
713.500.3954 (voice)
713.500.3907 (fax)
Cha...@ut...

Re: [python-sybase] CS_MEM_ERROR only in CGI execution environment

[John J L. <jj...@po...>]

On Sat, 31 Jul 2004, Gregory Bond wrote:

> > I can import Sybase from a Solaris shell prompt fine.
> >
> > When I try importing Sybase from code run from a CGI script, though, it
> > fails at the first cs_ctx_alloc() in Sybase.py, and I get the following in
> > the debug file:
>
> That'll be LD_LIBRARY_PATH issues.  When you do a connect, the sybase
> libs dynamically link other libraries, giving these wierd errors if it
> fails.  There's no way (afaict) of futzing -R flags at compile time
> for this, you just gotta have the right LD_LIBRARY_PATH (or symlink
> the offenders into /usr/local/lib or whatever.)
>
> Seems to be a problem with 12.0/12.5 more than 11.0 libraries.

Thank you!

I've been bashing my head against this for some time now.

I *am* setting LD_LIBRARY_PATH (using a shell script wrapper around a
Python CGI script), but I guess it's non-Sybase libs that are not being
found.

With hindsight, it seems blindingly obvious that I should have rote-copied
the shell environment and then experimented to track this down...  I guess
my mental block was assuming that some obscure Solaris security feature
was to blame.


John

Re: [python-sybase] CS_MEM_ERROR only in CGI execution environment

[Gregory B. <gn...@it...>]

> I can import Sybase from a Solaris shell prompt fine.
> 
> When I try importing Sybase from code run from a CGI script, though, it
> fails at the first cs_ctx_alloc() in Sybase.py, and I get the following in
> the debug file:

That'll be LD_LIBRARY_PATH issues.  When you do a connect, the sybase
libs dynamically link other libraries, giving these wierd errors if it
fails.  There's no way (afaict) of futzing -R flags at compile time
for this, you just gotta have the right LD_LIBRARY_PATH (or symlink
the offenders into /usr/local/lib or whatever.)

Seems to be a problem with 12.0/12.5 more than 11.0 libraries.

Re: [python-sybase] problems with executing dynamically created queries.

[stan l. <st...@ya...>]

--- Erik Thiele <er...@th...> wrote:

> On Wed, 21 Jul 2004 17:04:45 +0200
> Erik Thiele <er...@th...> wrote:
> 
> > hi.
> > 
> > i am using 
> > 
> > - gentoo linux.
> > - sybase-0.36
> > - freetds-0.62.3
> > - python 2.3.3
> > 
> > from this linux machine i am accessing a mssql 7
> server on an nt4.0
> > windows server.
> > 
> > i am doing
> > 
> >
>
db=Sybase.connect('myserver','myuser','mypass','mydb')
> > c=db.cursor()
> > c.execute('select * from foo where something=@x',
> {'@x' : "blabla"})
> > 
> > it sais:
> > 
> > Sybase.DatabaseError: ------
> > Must declare the variable '@x'
> > 
> > ??? i do not understand. why is this so?
> > 
> > i am trying to use python DB API 2.0. but i find
> the documentation for
> > this not very sufficient. there seems to be no
> good example at all on
> > the net.
> > 
> > questions never covered and done wrong in almost
> all examples is the
> > issue with escaping the datatypes. some really do
> > c.execute("select from where x="%s"" % mystring)
> > this is of course fatal since mystring may contain
> closing " and then
> > sql commands.
> > 
> > anyway... how do i get the module to work?
> 
> hmmmm. still i have no working example.
> 
> there is this python-sybase module and i cannot get
> it to work. there
> must be something wrong here. Could you give me a
> query that runs on the
> above configuration? i want to create the query
> dynamically. i.e. no
> static query, but instead some query like the one
> above.
> 
> is the python-sybase module broken?
> 
> 
> -- 
> Erik Thiele
> _______________________________________________
> Python-sybase mailing list
> Pyt...@ww...
>
https://www.object-craft.com.au/cgi-bin/mailman/listinfo/python-sybase
> 
Hi,

I haven't had reason to use python to call Sybase on
linux, but have used it to call Sybase on Win XP (at
work).

The equivalent to what you write above would be:
import odi
import odbc # order is important, not sure this is it

db=odbc.connect('myserver'/'myuser'/'mypass')
c=db.cursor()
c.execute('select * from foo where something=?',
("blabla",))
results = c.fetchall()

In windows, the link to the odbc driver for myserver
is defined in a dialog, and the odbc call
automatically invokes the correct link.  I would think
there should be a configuration file for linux that
does the same.
Probably somewhere in /etc or the home directory. 
Note that the variable substitution occurs from a
tuple to the variables.  Here there is only one, but
more would be filled from the tuple in the order they
occur in the query.  The tuple is usually populated
using variables so that the query changes depending on
the values assigned to them.

I have had no troubles with this except when there was
a small error in win32py, which Mark fixed quickly.

Hope this helps.

stan



		
__________________________________
Do you Yahoo!?
Yahoo! Mail Address AutoComplete - You start. We finish.
http://promotions.yahoo.com/new_mail 

[python-sybase] CS_MEM_ERROR only in CGI execution environment

[John J L. <jj...@po...>]

This may well be a Sybase issue, so apologies in advance if that's the
case.

I'm getting memory errors that don't appear to make sense given the
available resources.

I can import Sybase from a Solaris shell prompt fine.

When I try importing Sybase from code run from a CGI script, though, it
fails at the first cs_ctx_alloc() in Sybase.py, and I get the following in
the debug file:

cs_ctx_alloc(CS_VERSION_100, &ctx) -> CS_MEM_ERROR, None


The CGI process runs as the same user that I log in as when using the
module from the shell prompt.  In any case, there don't appear to be any
memory limitations on the CGI process, so I don't understand why I'm
getting this error.

Thanks for any help


John

[python-sybase] errors

[Josh C. <na...@gm...>]

I'm getting these errors almost every time I use sybase. I'm using it
with mssql. I build it like so:

python setup.py build_ext -D WANT_THREADS -D HAVE_FREETDS -U WANT_BULKCOPY
python setup.py install

and I'm getting these errors:

Traceback (most recent call last):
File "./check_mx.py", line 69, in ?
   results = [ R(row) for row in cursor.fetchall() ]
File "/usr/lib/python2.3/site-packages/Sybase.py", line 733, in fetchall
   return self._fetcher.fetchall()
File "/usr/lib/python2.3/site-packages/Sybase.py", line 517, in fetchall
   row = self.fetchone()
File "/usr/lib/python2.3/site-packages/Sybase.py", line 467, in fetchone
   self._fetch_rowcount()
File "/usr/lib/python2.3/site-packages/Sybase.py", line 592, in _fetch_rowcount
   self._raise_error(Error, 'ct_results')
File "/usr/lib/python2.3/site-packages/Sybase.py", line 432, in _raise_error
   raise exc(text)
Error: ct_results

This happens all the time.

I've also got this:

Exception Sybase.DatabaseError: <Sybase.DatabaseError instance at
0x409a702c> in <bound method _FetchLazy.__del__ of <Sybase._FetchLazy
instance at 0x40d890ac>> ignored

Anyone know what's going on?

-Josh

[python-sybase] new installation under WinXP SP1

[Richard S. <rr_...@ya...>]

I installed the Python-sybase software using:
python setup.py install and all went well, it seems. 
However, when I try to run the following:

import Sybase
db = Sybase.connect(<server>, <id>, <pw>,
delay_connect = 1)
db.set_property(Sybase.CS_HOSTNAME, 'MYPC')
print 'Sybase.CS_TDS_VERSION = ',
print db.get_property(Sybase.CS_TDS_VERSION)
db.connect()

Where <server>, <id>, <pw> have appropriate values,
the code fails on the db.connect().  

The property print displays: Sybase.CS_TDS_VERSION = 
7364.  Then the code fails with the msg box
"python.exe has encountered a problem and needs to
close. ..."

The Sybase interface data is stored in
\sybase\ini\sql.ini.  Do I have to configure
Python-sybase to look there for the interface data?

Python otherwise runs just fine.  I'm guessing that I
didn't install something correctly, but I don't know
what.  Although new to Python, I am not new to
programming in general, nor Sybase Open/Client in
particular.

Any thoughts where I can start looking for a solution?

I appreciate any help.





	
		
__________________________________
Do you Yahoo!?
New and Improved Yahoo! Mail - 100MB free storage!
http://promotions.yahoo.com/new_mail 

Re: [python-sybase] cursor.rowcount broken?

[Andrew M. <an...@ob...>]

>I'm using version 0.36 of the Sybase module, linked against Sybase OCS
>libraries.
>I want to use the cursor.rowcount value, but it always seems to return -1, did I
>understand something wrong?
>
> >>> db = Sybase.connect("XXX", "XXX", "XXX")
> >>> c = db.cursor()
> >>> c.execute("select * from pers")
> >>> print c.rowcount
> -1
> >>> print len(c.fetchall())
> 7

I can't speak for the Sybase module, but pyPgSQL (a python postgres
connector) only initialises this variable after you've fetched your rows.
In their case, it appears to be a limitation of the Postgres C API,
rather than a problem with the python adaptor: I think the database is
reserving the right to return partial results while still performing
the query (so even it doesn't know how many rows will be returned).

-- 
Andrew McNamara, Senior Developer, Object Craft
http://www.object-craft.com.au/

[python-sybase] cursor.rowcount broken?

[Alex S. <ale...@gm...>]

Hi list,

I'm using version 0.36 of the Sybase module, linked against Sybase OCS
libraries.
I want to use the cursor.rowcount value, but it always seems to return -1, did I
understand something wrong?

>>> db = Sybase.connect("XXX", "XXX", "XXX")
>>> c = db.cursor()
>>> c.execute("select * from pers")
>>> print c.rowcount
-1
>>> print len(c.fetchall())
7
>>> c.close()
>>> db.close()

INSERT statements do not set rowcount correctly either. Can someone help?

Regards, Alex
-- 
Alex Suzuki | as...@cy... | http://n.ethz.ch/student/asuzuki/
"Computer Science is no more about computers than astronomy
 is about telescopes." - E.W.Dijkstra

Re: [python-sybase] problems with executing dynamically created queries.

[Joshua Moore-O. <jo...@ch...>]

On July 26, 2004 02:14 pm, Jeff Younker wrote:
> Erik Thiele wrote:
> > i am looking for a working example of a query with parameter substitution.
> > ...
> > FreeTDS 0.62.3
>   ^^^^^^^^^^^^^^
> 
> Never managed to get parameter substitution working with FreeTDS.
> As far as I know you are screwed.

What packages do you use to get parameter substitution working with MSSQL?

Thanks, Josh.

> 
> -jeff
> _______________________________________________
> Python-sybase mailing list
> Pyt...@ww...
> https://www.object-craft.com.au/cgi-bin/mailman/listinfo/python-sybase
> 

RE: [python-sybase] problems with executing dynamically created queries.

[Jeff Y. <jyo...@of...>]

Erik Thiele wrote:
> i am looking for a working example of a query with parameter =
substitution.
> ...
> FreeTDS 0.62.3
  ^^^^^^^^^^^^^^

Never managed to get parameter substitution working with FreeTDS.
As far as I know you are screwed.

-jeff

Re: [python-sybase] problems with executing dynamically created queries.

[Joshua Moore-O. <jo...@ch...>]

I'm looking for exactly the same thing myself :(

If freetds isn't the tool to use for this, could I have some information on what tools I need to install to get query substitution working?

Josh.
> 
> FreeTDS 0.62.3
> Sybase 0.36
> Python 2.3.3
> Gentoo Linux. everything is kept up-to-date.
> MS-SQL Server 7.0 on Windows NT 4.0
> 
> i am looking for a working example of a query with parameter substitution.
> 
> erik
> 
> 

Re: [python-sybase] problems with executing dynamically created queries.

[Erik T. <er...@th...>]

On Fri, 23 Jul 2004 11:46:10 -0700
"Jeff Younker" <jyo...@of...> wrote:

> Erik Thiele writes:
> 
> hmmmm. still i have no working example.
> 
> there is this python-sybase module and i cannot get it to work. there
> must be something wrong here. Could you give me a query that runs on the
> above configuration? i want to create the query dynamically. i.e. no
> static query, but instead some query like the one above.
> 
> is the python-sybase module broken?
> 
> --
> 
> Ok, to try to help here:   What database libraries are
> you linking against? The three possibilities that I can
> think of are: Sybase,  ODBC of some sort, and FreeTDS.
> 
> I know that there are problems with FreeTDS and parameter
> substitution.  I'm not sure whether the problems are with
> the Sybase module or FreeTDS itself.
> 
> The module works great against the client sybase
> libraries.
> 
> -jeff

FreeTDS 0.62.3
Sybase 0.36
Python 2.3.3
Gentoo Linux. everything is kept up-to-date.
MS-SQL Server 7.0 on Windows NT 4.0

i am looking for a working example of a query with parameter substitution.

erik


-- 
Erik Thiele

RE: [python-sybase] problems with executing dynamically created queries.

[Jeff Y. <jyo...@of...>]

Erik Thiele writes:

hmmmm. still i have no working example.

there is this python-sybase module and i cannot get it to work. there
must be something wrong here. Could you give me a query that runs on the
above configuration? i want to create the query dynamically. i.e. no
static query, but instead some query like the one above.

is the python-sybase module broken?

--

Ok, to try to help here:   What database libraries are
you linking against? The three possibilities that I can
think of are: Sybase,  ODBC of some sort, and FreeTDS.

I know that there are problems with FreeTDS and parameter
substitution.  I'm not sure whether the problems are with
the Sybase module or FreeTDS itself.

The module works great against the client sybase
libraries.

-jeff

Re: [python-sybase] problems with executing dynamically created queries.

[Erik T. <er...@th...>]

On Wed, 21 Jul 2004 17:04:45 +0200
Erik Thiele <er...@th...> wrote:

> hi.
> 
> i am using 
> 
> - gentoo linux.
> - sybase-0.36
> - freetds-0.62.3
> - python 2.3.3
> 
> from this linux machine i am accessing a mssql 7 server on an nt4.0
> windows server.
> 
> i am doing
> 
> db=Sybase.connect('myserver','myuser','mypass','mydb')
> c=db.cursor()
> c.execute('select * from foo where something=@x', {'@x' : "blabla"})
> 
> it sais:
> 
> Sybase.DatabaseError: ------
> Must declare the variable '@x'
> 
> ??? i do not understand. why is this so?
> 
> i am trying to use python DB API 2.0. but i find the documentation for
> this not very sufficient. there seems to be no good example at all on
> the net.
> 
> questions never covered and done wrong in almost all examples is the
> issue with escaping the datatypes. some really do
> c.execute("select from where x="%s"" % mystring)
> this is of course fatal since mystring may contain closing " and then
> sql commands.
> 
> anyway... how do i get the module to work?

hmmmm. still i have no working example.

there is this python-sybase module and i cannot get it to work. there
must be something wrong here. Could you give me a query that runs on the
above configuration? i want to create the query dynamically. i.e. no
static query, but instead some query like the one above.

is the python-sybase module broken?


-- 
Erik Thiele

Re: [python-sybase] problems with executing dynamically created queries.

[Erik T. <er...@th...>]

On Fri, 23 Jul 2004 14:19:32 +1000
Dave Cole <dj...@ob...> wrote:

> Erik Thiele wrote:
> > hi.
> > 
> > 
> > i am trying to use python DB API 2.0. but i find the documentation
> > for this not very sufficient. there seems to be no good example at
> > all on the net.
> > 
> > questions never covered and done wrong in almost all examples is the
> > issue with escaping the datatypes. some really do
> > c.execute("select from where x="%s"" % mystring)
> > this is of course fatal since mystring may contain closing " and
> > then sql commands.
> > 
> > anyway... how do i get the module to work?
> 
> FreeTDS has historically had problems with named parameters.  Are you 
> able to test the same query using the Perl bindings to FreeTDS?

i am using mxODBC, there it works. but not named parameters. it uses
this ?-parameter thing, like all ODBC it sais.

it seems like freetds has problems with timestamp columns. it returns
them as hex data. datetime works. The term "timestamp" and "datetime" is
what the SQL Enterprise Manager on SQL Server 7.0 on WinNT sais to be
the datatype of the columns.

i'm so confused :-)

now i have a working mxODBC with broken mssql-timestamp

and i have a working sybase python module with broken parameter passing
and broken mssql-timestamp also.

so maybe the timestamp thing really is a freetds issue.

help!


regards
erik


-- 
Erik Thiele

Re: [python-sybase] problems with executing dynamically created queries.

[Skip M. <sk...@po...>]

    >> and... why is there several styles in DB API 2.0? i mean the
    >> Sybase.paramstyle thing. i thought DB API was there for portability,
    >> and then i find out that different modules have different ways to
    >> create sql queries. so there is absolute zero portability. why?

    Dave> You will have to ask the database vendors that one.

Assuming all the possible substitution styles are equivalent (I've seen "%s"
and "?" as placeholders as well as the dict style the python-sybase module
supports), I think the DB API could have specified one style then required
implementers to map from it to the native library's style.  That might have
been a PITA for some, but should be possible you'd think.  DB API 3.0
anyone?

Skip

Re: [python-sybase] problems with executing dynamically created queries.

[Erik T. <er...@th...>]

On Fri, 23 Jul 2004 17:07:55 +1000
Dave Cole <dj...@ob...> wrote:

> Erik Thiele wrote:
> > On Fri, 23 Jul 2004 14:19:32 +1000
> > Dave Cole <dj...@ob...> wrote:
> > 
> > 
> >>Erik Thiele wrote:
> >>
> >>
> >>>and... why is there several styles in DB API 2.0? i mean the
> >>>Sybase.paramstyle thing. i thought DB API was there for
> >portability,>>and then i find out that different modules have
> >different ways to>>create sql queries. so there is absolute zero
> >portability. why?>
> >>You will have to ask the database vendors that one.
> > 
> > 
> > ah! so it's a problem with the database? i thought that this
> > parameter substitution thing was an issue of the DB API and every
> > python database driver programmer just decides which one to use. so
> > that's not true! actually there should be a wrapper function on top
> > of DB API that can transform one single paramstyle into all the
> > others. or is there a technical reason why that's not possible?
> > anyway once you maybe answer this question i will make a suggestion
> > to python DB guys to add this to the FAQ. i tried to figure out why
> > this is so for just too long time. also that FAQ entry must point
> > out why there is no wrapper, or where it is :)
> 
> I am not sure that it is that simple for all databases.  If at all 
> possible it is a good idea to pass binary parameters to the server and
> 
> let the binding of parameters to the SQL occur at the server. 
> Unfortunately each database vendor has decided upon different
> parameter placeholders.  Some databases do not support parameter
> passing so the client side must format the arguments into the SQL
> query sent to the server.
> 
> It might be a good idea if some very clever person developed a 
> bullet-proof SQL query formatter that worked for all SQL variants. 
> Then the DB-API would just need to include formatters for native
> database types that could yield strings to be used by the formatter. 
> Who knows - this might have already been done.

it would be the ultimate step in the direction of database independency
if there was only one paramstyle left. then the DB-API wins over the
other APIS finally. until now i have to do some assertion that the
paramstyle is correct, otherwise the program does not run. i cannot
quite understand why they didn't include it from the beginning. i don't
see a big problem in writing a function that converts the query in one
paramstyle into a query of another paramstyle. Am i missing something?


-- 
Erik Thiele

Re: [python-sybase] problems with executing dynamically created queries.

[Dave C. <dj...@ob...>]

Erik Thiele wrote:
> On Fri, 23 Jul 2004 14:19:32 +1000
> Dave Cole <dj...@ob...> wrote:
> 
> 
>>Erik Thiele wrote:
>>
>>
>>>and... why is there several styles in DB API 2.0? i mean the
>>>Sybase.paramstyle thing. i thought DB API was there for portability,
>>>and then i find out that different modules have different ways to
>>>create sql queries. so there is absolute zero portability. why?
>>
>>You will have to ask the database vendors that one.
> 
> 
> ah! so it's a problem with the database? i thought that this parameter
> substitution thing was an issue of the DB API and every python database
> driver programmer just decides which one to use. so that's not true!
> actually there should be a wrapper function on top of DB API that can
> transform one single paramstyle into all the others. or is there a
> technical reason why that's not possible? anyway once you maybe answer
> this question i will make a suggestion to python DB guys to add this to
> the FAQ. i tried to figure out why this is so for just too long time.
> also that FAQ entry must point out why there is no wrapper, or where it
> is :)

I am not sure that it is that simple for all databases.  If at all 
possible it is a good idea to pass binary parameters to the server and 
let the binding of parameters to the SQL occur at the server. 
Unfortunately each database vendor has decided upon different parameter 
placeholders.  Some databases do not support parameter passing so the 
client side must format the arguments into the SQL query sent to the server.

It might be a good idea if some very clever person developed a 
bullet-proof SQL query formatter that worked for all SQL variants.  Then 
the DB-API would just need to include formatters for native database 
types that could yield strings to be used by the formatter.  Who knows - 
this might have already been done.

- Dave

-- 
http://www.object-craft.com.au

Re: [python-sybase] problems with executing dynamically created queries.

[Erik T. <er...@th...>]

On Fri, 23 Jul 2004 14:19:32 +1000
Dave Cole <dj...@ob...> wrote:

> Erik Thiele wrote:
>=20
> > and... why is there several styles in DB API 2.0? i mean the
> > Sybase.paramstyle thing. i thought DB API was there for portability,
> > and then i find out that different modules have different ways to
> > create sql queries. so there is absolute zero portability. why?
>=20
> You will have to ask the database vendors that one.

ah! so it's a problem with the database? i thought that this parameter
substitution thing was an issue of the DB API and every python database
driver programmer just decides which one to use. so that's not true!
actually there should be a wrapper function on top of DB API that can
transform one single paramstyle into all the others. or is there a
technical reason why that's not possible? anyway once you maybe answer
this question i will make a suggestion to python DB guys to add this to
the FAQ. i tried to figure out why this is so for just too long time.
also that FAQ entry must point out why there is no wrapper, or where it
is :)

thx!
erik


--=20
Erik Thiele
Horst Thiele Maschinenbau-Hydraulische Ger=E4te GmbH
Im Kampfrad 2 - 74196 Neuenstadt
Tel.: 07139/4801-19
Fax.: 07139/4801-29
email: er...@th...
Internet: http://www.thiele-hydraulik.de/

Re: [python-sybase] problems with executing dynamically created queries.

[Dave C. <dj...@ob...>]

Erik Thiele wrote:
> hi.
> 
> i am using 
> 
> - gentoo linux.
> - sybase-0.36
> - freetds-0.62.3
> - python 2.3.3
> 
> from this linux machine i am accessing a mssql 7 server on an nt4.0 windows server.
> 
> i am doing
> 
> db=Sybase.connect('myserver','myuser','mypass','mydb')
> c=db.cursor()
> c.execute('select * from foo where something=@x', {'@x' : "blabla"})
> 
> it sais:
> 
> Sybase.DatabaseError: ------
> Must declare the variable '@x'
> 
> ??? i do not understand. why is this so?
> 
> i am trying to use python DB API 2.0. but i find the documentation for this
> not very sufficient. there seems to be no good example at all on the net.
> 
> questions never covered and done wrong in almost all examples is the issue
> with escaping the datatypes. some really do
> c.execute("select from where x="%s"" % mystring)
> this is of course fatal since mystring may contain closing " and then sql commands.
> 
> anyway... how do i get the module to work?

FreeTDS has historically had problems with named parameters.  Are you 
able to test the same query using the Perl bindings to FreeTDS?

> and... why is there several styles in DB API 2.0? i mean the Sybase.paramstyle
> thing. i thought DB API was there for portability, and then i find out
> that different modules have different ways to create sql queries. so there
> is absolute zero portability. why?

You will have to ask the database vendors that one.

- Dave

-- 
http://www.object-craft.com.au