DBI ODBC select statement length

Hey folks -

We have written an app that makes a connection to a MSSQL server using ODBC. It works great as long as the length of our select statement is 668 characters or less. As soon as it hits 669 characters, it hangs forever doing the execute. It also fails if we use $dbh->do() instead of $dbh->prepare() and $dbh->execute.

I have googled like crazy trying to find some direction, but have come up empty. Has anybody seen this before? Or is this something obvious and I should be hit with a rolled up news paper for not figuring it out?

Thanks.

-Brian

Re: DBI ODBC select statement length

Since no one else seems to have answered I'll jump in with a few
questions that might be helpful.

Does it make any difference if there is an embedded newline in the
statement?

Is there a way to query the server to find out what query it thinks
it's gotten? I've never worked with MSSQL so I don't know what sort
of tools are available.

Can you get any useful information by turning DBI tracing on?

-Chris

On Aug 29, 2007, at 2:16 PM, Brian Johnson wrote:

> Hey folks -
>
> We have written an app that makes a connection to a MSSQL server
> using ODBC. It works great as long as the length of our select
> statement is 668 characters or less. As soon as it hits 669
> characters, it hangs forever doing the execute. It also fails if
> we use $dbh->do() instead of $dbh->prepare() and $dbh->execute.
>
> I have googled like crazy trying to find some direction, but have
> come up empty. Has anybody seen this before? Or is this something
> obvious and I should be hit with a rolled up news paper for not
> figuring it out?
>
> Thanks.
>
> -Brian

--
Christopher Sarnowski
csarnows@pcbi.upenn.edu
Penn Bioinformatics Core
1316 Blockley Hall MC 6021
(215) 746-7784

Re: DBI ODBC select statement length

I just thought the problem was so obscure nobody had ideas! Hehe. Anyway, to answer your questions:

We are parsing out all newlines from the select statement.
As for finding out what the server thinks it's getting...we'll have to dig into that. We don't have direct access to the server, only ODBC.
And finally, we did turn on tracing originally. It showed that it makes it as far as execute, and then both the script and the trace stop. We have tried all different levels of tracing (1-15) with the same results.

This is one of those problems that is going to leave a permanent dent in my forehead from repeatedly applying it to the desk.

Thanks for your help!

-Brian



csarnows@pcbi.upenn.edu wrote:
>Since no one else seems to have answered I'll jump in with a few
>questions that might be helpful.
>
>Does it make any difference if there is an embedded newline in the
>statement?
>
>Is there a way to query the server to find out what query it thinks
>it's gotten? I've never worked with MSSQL so I don't know what sort
>of tools are available.
>
>Can you get any useful information by turning DBI tracing on?
>
>-Chris
>
>On Aug 29, 2007, at 2:16 PM, Brian Johnson wrote:
>
>> Hey folks -
>>
>> We have written an app that makes a connection to a MSSQL server
>> using ODBC. It works great as long as the length of our select
>> statement is 668 characters or less. As soon as it hits 669
>> characters, it hangs forever doing the execute. It also fails if
>> we use $dbh->do() instead of $dbh->prepare() and $dbh->execute.
>>
>> I have googled like crazy trying to find some direction, but have
>> come up empty. Has anybody seen this before? Or is this something
>> obvious and I should be hit with a rolled up news paper for not
>> figuring it out?
>>
>> Thanks.
>>
>> -Brian
>
>--
>Christopher Sarnowski
>csarnows@pcbi.upenn.edu
>Penn Bioinformatics Core
>1316 Blockley Hall MC 6021
>(215) 746-7784
-------------------------------------
Brian Johnson
Systems Administrator
Work: 248.577.0383 ext 203
Cell: 906.281.0589
EMOL - Electronic Medical Office Logistics

Re: DBI ODBC select statement length

Chris,

Have you checked the server logs for a specific error?

I had a similar problem related to the length of the query string
with a MYSQL database, which returned the error message "Got a packet
bigger than 'max_allowed_packet' ".

I have forgotten if MSSQL has a similar setting.

Paul

> Since no one else seems to have answered I'll jump in with a few
> questions that might be helpful.
>
> Does it make any difference if there is an embedded newline in the
> statement?
>
> Is there a way to query the server to find out what query it thinks
> it's gotten? I've never worked with MSSQL so I don't know what sort
> of tools are available.
>
> Can you get any useful information by turning DBI tracing on?
>
> -Chris
>
> On Aug 29, 2007, at 2:16 PM, Brian Johnson wrote:
>
>> Hey folks -
>>
>> We have written an app that makes a connection to a MSSQL server
>> using ODBC. It works great as long as the length of our select
>> statement is 668 characters or less. As soon as it hits 669
>> characters, it hangs forever doing the execute. It also fails if
>> we use $dbh->do() instead of $dbh->prepare() and $dbh->execute.
>>
>> I have googled like crazy trying to find some direction, but have
>> come up empty. Has anybody seen this before? Or is this
>> something obvious and I should be hit with a rolled up news paper
>> for not figuring it out?
>>
>> Thanks.
>>
>> -Brian
>
> --
> Christopher Sarnowski
> csarnows@pcbi.upenn.edu
> Penn Bioinformatics Core
> 1316 Blockley Hall MC 6021
> (215) 746-7784


--
Sincerely,

Paul Appleby

(416) 530-0070
http://www.paulappleby.com
http://myspider.ca

Re: DBI ODBC select statement length

On Aug 31, 2007, at 1:34 PM, Brian Johnson wrote:

> I just thought the problem was so obscure nobody had ideas! Hehe.
> Anyway, to answer your questions:
>
> We are parsing out all newlines from the select statement.

What happens if you leave the newlines in? I'm thinking there may be
some internal line-based buffer that can be "fooled" into accepting
biq queries if they are multiple-line. But as I said below, I don't
really know anything about MSSQL.


> As for finding out what the server thinks it's getting...we'll have
> to dig into that. We don't have direct access to the server, only
> ODBC.

I was going to ask if the query is known to work through some other
interface. Can you run it with some other ODBC tool? dbvis or something?

I'm also forwarding an email from Paul Appleby to the list, separately.

> And finally, we did turn on tracing originally. It showed that it
> makes it as far as execute, and then both the script and the trace
> stop. We have tried all different levels of tracing (1-15) with
> the same results.
>
> This is one of those problems that is going to leave a permanent
> dent in my forehead from repeatedly applying it to the desk.
>
> Thanks for your help!
>
> -Brian

> csarnows@pcbi.upenn.edu wrote:
>> Since no one else seems to have answered I'll jump in with a few
>> questions that might be helpful.
>>
>> Does it make any difference if there is an embedded newline in the
>> statement?
>>
>> Is there a way to query the server to find out what query it thinks
>> it's gotten? I've never worked with MSSQL so I don't know what sort
>> of tools are available.
>>
>> Can you get any useful information by turning DBI tracing on?
>>
>> -Chris
>>
>> On Aug 29, 2007, at 2:16 PM, Brian Johnson wrote:
>>
>>> Hey folks -
>>>
>>> We have written an app that makes a connection to a MSSQL server
>>> using ODBC. It works great as long as the length of our select
>>> statement is 668 characters or less. As soon as it hits 669
>>> characters, it hangs forever doing the execute. It also fails if
>>> we use $dbh->do() instead of $dbh->prepare() and $dbh->execute.

--
Christopher Sarnowski
csarnows@pcbi.upenn.edu
Penn Bioinformatics Core
1316 Blockley Hall MC 6021
(215) 746-7784