[DB-SIG] Question on odbc with cross apply and for xml...

[M.-A. Lemburg]

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 <mal at egenix.com>
> To: Christopher Eckman <royhobbsx42 at yahoo.com>
> Cc: db-sig at 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 <mal at egenix.com>
>> To: Christopher Eckman <royhobbsx42 at yahoo.com>
>> Cc: db-sig at 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.
>>
>