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