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

Christopher Eckman royhobbsx42 at yahoo.com
Tue Nov 28 04:53:11 CET 2006


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

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.

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 27 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 ! ::::







More information about the DB-SIG mailing list