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.
>
--
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 ! ::::
_______________________________________________
DB-SIG maillist - DB-SIG@python.org
http://mail.python.org/mailman/listinfo/db-sig
