Re: [PHP] ODBC and long text fields
Larry Garfield wrote: On Sunday 07 January 2007 8:26 pm, Jochem Maas wrote: ... with regard to wanting results returned in a 'named' fashion, does the new setup still not allow use of odbc_fetch_array() instead of odbc_fetch_row()? not that I see any logic in that solving the issue, again it might be worth trying to see if it does. Honestly I've not tried. (I also don't have the wrapper code in front of me at the moment. g) That could be a nice thing to simplify for performance, but I suspect that if the problem is with odbc_fetch_row() then odbc_fetch_array() will have the same bug. quite likely - but you never know until you give it a shot - weirder things have happened :-) I don't know much about MSSQL, or whether 'text' is an actual field type, but maybe this function offers a wayout?: http://php.net/manual/en/function.odbc-binmode.php No, that looks like it's for binary data. Text is a MS SQL field for huge-ass text fields, essentially similar to the MySQL field of the same name (at least at an API level). We also tried playing with odbc_longreadlen() and setting MS SQL's TEXTSIZE and TEXTLENGTH parameters to no avail. ah I see; double drat! are the php versions on the 2 systems you mention exactly the same? (you mention 5.1 and 5.1.6) this could be a clue to the problem. It's either both 5.1.6 or 5.1.6 live and 5.1.4 devel. (I don't recall what our exact version is atm.) We're in the process of upgrading our server soon, and I'm not sure what it will have. Probably 5.2, just to keep life interesting. :-) I would get the systems running exactly the same version just to rule out that as a possible source of the problem. lastly I don't suppose that it possible for you to switch to using the MSSQL specific php extension? [http://php.net/mssql] it might offer a better/faster connection to the MSSQL server as well as making the issue go away. I don't believe the client allows direct MSSQL connections. Besides, the MSSQL functions still require an ODBC driver underneath, I didn't know that. so if the problem is with the ODBC driver then that won't change anything. if what you say is true then it it would be a great way of determining if the it is the ODBC driver that's the problem, and if using MSSQL extension make the problem dissapear then you have a solution (given that it has to use an ODBC driver underneath, you will still be conforming to the client's requirements by not connecting directly). sorry I couldn't be of more help/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] ODBC and long text fields
Hi all. I've a question regarding PHP's ODBC support. Here's the situation: We've a PHP app that uses ODBC to talk to a MS SQL server. Its original home was on a server that used the OpenLink ODBC driver, which was a POS, so we build an abstraction wrapper around the subset of PHP's ODBC functions that we were able to use to make it usable. The internal code for a query is built on odbc_exec, odbc_fetch_row, and odbc_result. The main interesting part is that the original driver didn't allow for named results, so instead we have a small string parser that reads the incoming SQL query, extracts the field names, and then uses that to map the numeric result indexes to their field name. Kinda clunky, but worked well and used only a minimal ODBC footprint. That worked fine on their old system. However, the client then moved from a Windows IIS server to Apache and PHP 5.1.6 on a Linux box, but still talking to an MS SQL server on a Windows box. We migrated our test environment to the same; Linux/Apache/PHP 5.1 talking to MS SQL on a Windows box over the network. Our system uses the unix_ODBC and freetds stack for ODBC connectivity, and that works fine. On the client's system, it works fine except for a few odd cases. Specifically, on a few queries that pull data from large text fields the query may hang. It seems to reliably hang on certain records only, and only after those records have been edited. It seems that when updating a record, there is a small chance of something happening to that record and it then not working thereafter. A PHP test script run from the command line freezes, while the same query run directly on the SQL server returns almost instantly. The client has been in contact with their ODBC driver vendor (they're using a commercial driver), and the vendor's response is that we're not using ODBC correctly. Specifically, they say: - I followed the ODBC calls in your odbc trace file and I got error Invalid Descriptor Index on SQLGetData as well. I know we normally don't handle the retrieval of large dataset like Text using SQLGetData as your application is doing. I'm in the research of whether the way your application does is valid or not. - They then followed up with: - We have done some research on this issue. We realized that your application is binding all the columns for the resultset and then use SQLGetData. This is not the correct way to do. You can either use bind column for result set and then print out the data in the bound buffer, or use SQLGetData to get the unbound resultset. - They then include some sample code that is all in C. Now I'll be honest and say I don't quite follow what they're talking about. I do not claim to be an ODBC guru, but SQLGetData is a lower-level operation, SQL level or C level I don't know, but not something that happens in PHP code as far as I am aware. Are they saying there's a bug in PHP's odbc_fetch_row()? Or is it a bug in their driver if it can't handle whatever it is odbc_fetch_row() does internally? Or should we be using odbc_result() instead of odbc_fetch_row() if we're dealing with a text field rather than a varchar or int? I am confused, and would appreciate assistance in becoming less confused. :-) Thanks. -- Larry Garfield AIM: LOLG42 [EMAIL PROTECTED] ICQ: 6817012 If nature has made any one thing less susceptible than all others of exclusive property, it is the action of the thinking power called an idea, which an individual may exclusively possess as long as he keeps it to himself; but the moment it is divulged, it forces itself into the possession of every one, and the receiver cannot dispossess himself of it. -- Thomas Jefferson -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] ODBC and long text fields
no real answer, but ... Larry Garfield wrote: Hi all. I've a question regarding PHP's ODBC support. Here's the situation: We've a PHP app that uses ODBC to talk to a MS SQL server. Its original home was on a server that used the OpenLink ODBC driver, which was a POS, so we build an abstraction wrapper around the subset of PHP's ODBC functions that we were able to use to make it usable. The internal code for a query is built on odbc_exec, odbc_fetch_row, and odbc_result. The main interesting part is that the original driver didn't allow for named results, so instead we have a small string parser that reads the incoming SQL query, extracts the field names, and then uses that to map the numeric result indexes to their field name. Kinda clunky, but worked well and used only a minimal ODBC footprint. That worked fine on their old system. However, the client then moved from a Windows IIS server to Apache and PHP 5.1.6 on a Linux box, but still talking to an MS SQL server on a Windows box. We migrated our test environment to the same; Linux/Apache/PHP 5.1 talking to MS SQL on a Windows box over the network. Our system uses the unix_ODBC and freetds stack for ODBC connectivity, and that works fine. On the client's system, it works fine except for a few odd cases. Specifically, on a few queries that pull data from large text fields the query may hang. It seems to reliably hang on certain records only, and only after those records have been edited. It seems that when updating a record, there is a small chance of something happening to that record and it then not working thereafter. A PHP test script run from the command line freezes, while the same query run directly on the SQL server returns almost instantly. ... Now I'll be honest and say I don't quite follow what they're talking about. I do not claim to be an ODBC guru, but SQLGetData is a lower-level operation, SQL level or C level I don't know, but not something that happens in PHP code as far as I am aware. Are they saying there's a bug in PHP's odbc_fetch_row()? Or is it a bug in their driver if it can't handle whatever it is odbc_fetch_row() does internally? Or should we be using odbc_result() instead of odbc_fetch_row() if we're dealing with a text field rather than a varchar or int? the way I read it odbc_result() is just an alternative way of doing what you do with odbc_fetch_row() - I can't see any reason why doing it with odbc_result() would suddenly make it work - nonetheless it might be worth trying it just to rule it out. with regard to wanting results returned in a 'named' fashion, does the new setup still not allow use of odbc_fetch_array() instead of odbc_fetch_row()? not that I see any logic in that solving the issue, again it might be worth trying to see if it does. I don't know much about MSSQL, or whether 'text' is an actual field type, but maybe this function offers a wayout?: http://php.net/manual/en/function.odbc-binmode.php are the php versions on the 2 systems you mention exactly the same? (you mention 5.1 and 5.1.6) this could be a clue to the problem. lastly I don't suppose that it possible for you to switch to using the MSSQL specific php extension? [http://php.net/mssql] it might offer a better/faster connection to the MSSQL server as well as making the issue go away. I am confused, and would appreciate assistance in becoming less confused. :-) Thanks. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] ODBC and long text fields
On Sunday 07 January 2007 8:26 pm, Jochem Maas wrote: Now I'll be honest and say I don't quite follow what they're talking about. I do not claim to be an ODBC guru, but SQLGetData is a lower-level operation, SQL level or C level I don't know, but not something that happens in PHP code as far as I am aware. Are they saying there's a bug in PHP's odbc_fetch_row()? Or is it a bug in their driver if it can't handle whatever it is odbc_fetch_row() does internally? Or should we be using odbc_result() instead of odbc_fetch_row() if we're dealing with a text field rather than a varchar or int? the way I read it odbc_result() is just an alternative way of doing what you do with odbc_fetch_row() - I can't see any reason why doing it with odbc_result() would suddenly make it work - nonetheless it might be worth trying it just to rule it out. with regard to wanting results returned in a 'named' fashion, does the new setup still not allow use of odbc_fetch_array() instead of odbc_fetch_row()? not that I see any logic in that solving the issue, again it might be worth trying to see if it does. Honestly I've not tried. (I also don't have the wrapper code in front of me at the moment. g) That could be a nice thing to simplify for performance, but I suspect that if the problem is with odbc_fetch_row() then odbc_fetch_array() will have the same bug. I don't know much about MSSQL, or whether 'text' is an actual field type, but maybe this function offers a wayout?: http://php.net/manual/en/function.odbc-binmode.php No, that looks like it's for binary data. Text is a MS SQL field for huge-ass text fields, essentially similar to the MySQL field of the same name (at least at an API level). We also tried playing with odbc_longreadlen() and setting MS SQL's TEXTSIZE and TEXTLENGTH parameters to no avail. are the php versions on the 2 systems you mention exactly the same? (you mention 5.1 and 5.1.6) this could be a clue to the problem. It's either both 5.1.6 or 5.1.6 live and 5.1.4 devel. (I don't recall what our exact version is atm.) We're in the process of upgrading our server soon, and I'm not sure what it will have. Probably 5.2, just to keep life interesting. :-) lastly I don't suppose that it possible for you to switch to using the MSSQL specific php extension? [http://php.net/mssql] it might offer a better/faster connection to the MSSQL server as well as making the issue go away. I don't believe the client allows direct MSSQL connections. Besides, the MSSQL functions still require an ODBC driver underneath, so if the problem is with the ODBC driver then that won't change anything. -- Larry Garfield AIM: LOLG42 [EMAIL PROTECTED] ICQ: 6817012 If nature has made any one thing less susceptible than all others of exclusive property, it is the action of the thinking power called an idea, which an individual may exclusively possess as long as he keeps it to himself; but the moment it is divulged, it forces itself into the possession of every one, and the receiver cannot dispossess himself of it. -- Thomas Jefferson -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
