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

[Christopher Eckman]

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.

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 for all the help and good advice,

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

-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, Nov 28 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 ! ::::





 
____________________________________________________________________________________
Cheap talk?
Check out Yahoo! Messenger's low PC-to-Phone call rates.
http://voice.yahoo.com