Yes, you should type coerce the values coming in from the web so that they are type-compatible with the database columns. I don't know if other dialects are more permissive and allow string representations of numbers etc.
pjjH On Jun 30, 7:15 pm, Vishakh <[email protected]> wrote: > Hello, > > Thanks for your replies. I am working on Windows XP with Sybase OCS, > perhaps that is why pjjH's code doesn't work out of the box for me. > For example, I am unable to import _FetchLazy. Would I have to install > FreeTDS to get this working? Is it a wise idea to adapt this to > Windows? > > I wrote a small test script after reading Michael's message: > > import Sybase > db = Sybase.connect ('*****', '*****', '*****', '*****') > c = db.cursor() > c.execute ("select * from SomeTable where lab...@label and i...@id", > {'@label': 'FOO', '@id': 7}) > for r in c.fetchall(): > print r > db.close > > The above works without any problems. However, when I quote the 7 and > change the relevant line: > > c.execute ("select * from SomeTable where lab...@label and i...@id", > {'@label': 'FOO', '@id': '7'}) > > I get the following error: > DatabaseError: Msg 257, Level 16, State 1, Line 1 > Implicit conversion from datatype 'CHAR' to 'INT' is not allowed. Use > the CONVERT function to run this query. > > The relevant code in my Pylons controller (using my modified version > of Alexander Houben's code) iterates over all HTTP POST variables and > appends them to a query: > > for q in request.params: > if request.params[q]: statuses = statuses.filter(q+"=:"+q) > statuses = statuses.params(request.params).all() > > This gives the same error as above since all the param values are > strings and some column types are integers: > > DataError: (DataError) ('22018', "[22018] [DataDirect][ODBC Sybase > Wire Protocol driver][SQL Server]Implicit conversion > from datatype 'CHAR' to 'INT' is not allowed. Use the CONVERT > function to run this query.\n (257) (SQLExecDirectW)") > > Am I doing something fundamentally stupid by not checking the types of > and casting the POST values or is this a problem specific to Sybase? > If it is the latter, is there any way I can modify the SybaseInteger > or Sybase SQL Dialect class to cast all columns which are integers? > > Thanks again! > > On Jun 30, 11:02 am, "[email protected]" > > <[email protected]> wrote: > > > as a note, I made some attempts with the sybase dialect and I can say > > > that FreeTDS with sybase is not usable at all since it doesn't render > > > bind parameters correctly. > > > This is correct if one is using the FreeTDS implementation of the CT- > > Lib API but is not the case when using pyodbc: placeholders work OK > > (at least for vanilla datatypes) > > > pjjH --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---
