[sqlalchemy] Re: Parameters not handled correctly with adodbapi

2007-02-17 Thread Paul Johnston 

Arnar,

I agree that is an adodbapi problem; I have seen this as well. It is 
because adodbapi isn't setting the length of the parameter, so ADO is 
defaulting to 1 - hence you only getting O. I do have an idea how to 
fix this, however I'm choosing not to for now. adodbapi just isn't 
maintained any more - the mailing list is all but dead, and numerous 
attempts to contact the author have failed. Moreover, pyodbc is now a 
very viable alternative. I had been avoid ODBC because earlier versions 
had no Unicode support, but this is now fixed. So, from now I will be 
devoting my energies to making SQLAlchemy work better with pyodbc.

Paul

--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
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
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Parameters not handled correctly with adodbapi

2007-02-17 Thread Arnar Birgisson 

Hi Paul,

Yes, I had found this about the datatype size by way of breaking into
the relevant code and setting the parameter length manually. This made
the query work but just unveiled other problems.

I too made no further efforts to get it work as pymssql works fine for
my purposes - although server side cursors would have been nice to
have. However, the adodbapi module is fairly simple, it's mostly just
proxying to the underlying COM api. I'm pretty sure it's relatively
easy to fix but since I don't have any real need for it myself I lack
the motivation right now. Maybe someday if the DB-API becomes defunct
due to microsoft dropping support for it..

It's good to hear that someone is working on pyodbc support in SA.

Arnar

On 2/17/07, Paul Johnston [EMAIL PROTECTED] wrote:

 Arnar,

 I agree that is an adodbapi problem; I have seen this as well. It is
 because adodbapi isn't setting the length of the parameter, so ADO is
 defaulting to 1 - hence you only getting O. I do have an idea how to
 fix this, however I'm choosing not to for now. adodbapi just isn't
 maintained any more - the mailing list is all but dead, and numerous
 attempts to contact the author have failed. Moreover, pyodbc is now a
 very viable alternative. I had been avoid ODBC because earlier versions
 had no Unicode support, but this is now fixed. So, from now I will be
 devoting my energies to making SQLAlchemy work better with pyodbc.

 Paul

 


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
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
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Parameters not handled correctly with adodbapi

2007-02-15 Thread Arnar Birgisson 

Thanks Rick,

The SA table definitions were using Unicode so I changed those to
String. I also make sure that the parameter in question is being sent
as a str object and not unicode. Same result in both cases, SQL Server
is only receiving the first character and the type is still detected
as nvarchar(1).

Any other ideas?

Arnar

On 2/14/07, Rick Morrison [EMAIL PROTECTED] wrote:
 The problem is most likely in the table definition which defines the
 dataset column as a Unicode type. A recent patch for adodb makes that an
 NVARCHAR field, vs. a simple VARCHAR for pymssql. Try defining the table
 with varchar or char datatype.




 On 2/14/07, Arnar Birgisson [EMAIL PROTECTED] wrote:
 
 
  Hi there,
 
  I have the following query in SA. It works using pymssql on Linux.
 
  criteria = and_(
  tables.salestable.c.dataset==self.xaldataset,
 
 tables.salestable.c.dataset==tables.projtable.c.dataset ,
 
 tables.salestable.c.proj==tables.projtable.c.proj,
  tables.salestable.c.projclosed==0,
  #tables.salestable.c.salesoffersent!=2,
  #tables.salestable.c.salesofferinvoice!=2,
   tables.salestable.c.projeststatus!=4,
  tables.salestable.c.projeststatus!=6,
  #tables.salestable.c.projeststatus!=7,
  *critlist
  )
 
  query = select([
  tables.salestable.c.dataset ,
  tables.salestable.c.salesnumber,
  tables.salestable.c.salesname,
  tables.salestable.c.ordertaker,
  tables.salestable.c.deliverydate,
  tables.salestable.c.projname ,
  tables.salestable.c.createdate,
  tables.salestable.c.rownumber,
  tables.projtable.c.proj,
  tables.projtable.c.opmprojno,
  tables.projtable.c.framstjori
  ], criteria)
 
  Here, self.xaldataset is ODD and critlist is [].
 
  When run on windows with adodbapi - this is the echoed query:
 
  2007-02-14 11:55:28,030
 sqlalchemy.engine.base.Engine.0x..30 INFO
  SELECT TOP 25 subq.dataset, subq.salesnumber, subq.salesname,
  subq.ordertaker, subq.deliverydate, subq.projname, subq.createdate,
  subq.rownumber, subq.proj, subq.opmprojno, subq.framstjori
  FROM (SELECT salestable.dataset AS dataset, salestable.salesnumber AS
  salesnumber, salestable.salesname AS salesname, salestable.ordertaker
  AS ordertaker, salestable.deliverydate AS deliverydate,
  salestable.projname AS projname, salestable.
  createdate AS createdate, salestable.rownumber AS rownumber,
  projtable.proj AS proj, projtable.opmprojno AS opmprojno,
  projtable.framstjori AS framstjori
  FROM salestable, projtable
  WHERE salestable.dataset = ? AND salestable.dataset =
  projtable.dataset AND salestable.proj = projtable.proj AND
  salestable.projclosed = ? AND salestable.projeststatus != ? AND
  salestable.projeststatus != ?) AS subq ORDER BY deliverydate
 
  2007-02-14 11:55:28,030
 sqlalchemy.engine.base.Engine.0x..30 INFO
  ['ODD', 0, 4, 6]
 
  Note the first parameter being 'ODD'.
 
  Now, the query wasn't returning the expected results so I looked at
  the query that was being sent in sql profiler:
 
  exec sp_executesql N'SELECT TOP 25 subq.dataset, subq.salesnumber,
  subq.salesname, subq.ordertaker, subq.deliverydate, subq.projname,
  subq.createdate, subq.rownumber ,
  subq.proj, subq.opmprojno, subq.framstjori
  FROM (SELECT salestable.dataset AS dataset, salestable.salesnumber AS
  salesnumber, salestable.salesname AS salesname, salestable.ordertaker
  AS ordertaker,
  salestable.deliverydate AS deliverydate, salestable.projname AS
  projname, salestable.createdate AS createdate, salestable.rownumber AS
  rownumber, projtable.proj AS proj,
  projtable.opmprojno AS opmprojno, projtable.framstjori AS framstjori
  FROM salestable, projtable
  WHERE salestable.dataset = @P1 AND salestable.dataset =
  projtable.dataset AND salestable.proj = projtable.proj AND
  salestable.projclosed = @P2 AND salestable.projeststatus
  != @P3 AND salestable.projeststatus != @P4) AS subq ORDER BY
  deliverydate',N'@P1 nvarchar(1),@P2 int,@P3 int,@P4 int',N'O',0,4,6
 
  Here you can see that the first parameter is givent the type
  nvarchar(1) and the value N'O'. I assume this is b/c somewhere the
  parameter is handled as a list - and being a string messes things up.
  Maybe because I'm using unicode strings?
 
  Could this be a SA bug or should I rather look into adodbapi?
 
  Arnar
 
   
 


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
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
-~--~~~~--~~--~--~---