Christopher Eckman wrote: > Hello all, > > I was able to get around this problem. I noticed when I did a cursor > description it gave me a very strange length value for "operators" (the > consolidated field). It reported it was 'STRING' with the length being > 1073741823. So, I took James advice and made a view and did a convert on > that field and made it a nvarchar2 with a much shorter length. I don't know > why it sees it this way but it did.
That's an interesting value: 2**30 - 1. Note that the special SQL_NULL_DATA length value is -1. This does look like an ODBC driver bug to me... have you checked the MS KB regarding this behavior ? > Marc, I tried to get find out what it did when you passed substring -1 as the > last argument but it would throw errors every time I did it. Thanks. I was just asking because this case will occur if your memberList is empty. > Thanks for all the help and good advice, Cheers, -- Marc-Andre Lemburg eGenix.com Professional Python Services directly from the Source (#1, Nov 30 2006) >>> Python/Zope Consulting and Support ... http://www.egenix.com/ >>> mxODBC.Zope.Database.Adapter ... http://zope.egenix.com/ >>> mxODBC, mxDateTime, mxTextTools ... http://python.egenix.com/ ________________________________________________________________________ ::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,FreeBSD for free ! :::: > --Chris > > ----- Original Message ---- > From: M.-A. Lemburg <[EMAIL PROTECTED]> > To: Christopher Eckman <[EMAIL PROTECTED]> > Cc: db-sig@python.org > Sent: Tuesday, November 28, 2006 4:23:36 AM > Subject: Re: [DB-SIG] Question on odbc with cross apply and for xml... > > Christopher Eckman wrote: >> Hi Marc-Andre, >> >> Thank you very much for the suggestion. I tried mxODBC and it behaved in a >> similiar manner as the plain odbc module. I don't think the ODBC driver >> itself is the problem itself though as if I run Microsoft Query, select that >> exact same DSN and execute the query it will give the expected results >> (concatenates the operators into the operator field). > > I'd have to see the log of a mxODBC debug build to comment on that. > > Note that ODBC has various ways of accessing data. It is possible > that MS Query uses a different way of asking for the relevant data > than mxODBC - one which doesn't trigger the problem in the driver. > > The None value is only returned if the driver sends the special > SQL_NULL_DATA field length value, so something in the chain is > setting this value explicitly. > >> The main reason I tried to do this in SQL is that I have a number of queries >> in a report dictionary. It gets the query associated to a given report, >> runs it and makes a .csv out of them. I was trying to avoid putting in >> special handlers for any of the reports (all the others work without me >> doing any query specific handling). At the time I did not know this query >> would prove to be so difficult to handle. The secondary reason is that I am >> the only person that familiar with Python here on this gig. Most all of the >> people on my team are pretty decent with SQL. > > Fair enough :-) BTW, what does substring() return if you pass it > a -1 as third argument ? > >> Thanks, >> >> --Chris >> >> ----- Original Message ---- >> From: M.-A. Lemburg <[EMAIL PROTECTED]> >> To: Christopher Eckman <[EMAIL PROTECTED]> >> Cc: db-sig@python.org >> Sent: Monday, November 27, 2006 4:42:06 PM >> Subject: Re: [DB-SIG] Question on odbc with cross apply and for xml... >> >> Christopher Eckman wrote: >>> Hi all, >>> >>> I am doing a select to concatenate a number of entries into a field like >>> this under 'operators' (sample header is the first line): >>> name company uis_access_control uis_tp_ticketpassing >>> operators >>> UNINA FOO unrestricted No >>> uni-catherine_srvage,uni-robert_woyzik,uni-susan_fooman >>> >>> using the SQL Server functionality cross apply and for xml. Sample select >>> is below: >>> >>> select support_group_name "name", sg.Company "company", sg.f5 >>> "uis_access_control", sg.f6 "uis_tp_ticketpassing", sg.REZ_Manager >>> "manager", >>> substring(memberList, 1, datalength(memberList)/2 - 1) >>> "operators" >>> -- strip the last ',' from the list >>> from >>> ctm_support_group sg cross apply >>> (select convert(nvarchar(60), sgm.support_group_member_name) + ',' >>> as [text()] >>> from tsmi_support_group_members sgm >>> where sg.Support_Group_ID = sgm.Support_Group_ID and sg.Company = >>> 'UNINA' and sg.support_group_name like 'UNI-NA%' >>> order by support_group_name >>> for xml path('')) as Dummy(memberList) >>> go >>> >>> The problem is when I call this via dbi and odbc it will always put 'None' >>> for operators even though if I do this in TOAD or MS Query it will pull the >>> correct values? I tried to get around this by making this a stored >>> procedure but the behavior is the same. Is there something I am missing? >>> I am calling this with the typical >>> >>> cursor.execute(sample_query) >>> for row in cursor.fetchall()... >>> >>> Any help would be appreciated. >> You could try this with mxODBC to see whether it's a problem related to >> the ODBC driver or not. >> >> Note that string processing such as what you are applying to the >> "operators" is much better done in Python than at the SQL level. >> > _______________________________________________ DB-SIG maillist - DB-SIG@python.org http://mail.python.org/mailman/listinfo/db-sig
