I'm really perplexed here.
Long story short, we have some php scripts that have been running against an ASA 6.0.4 Build 3799 database for years. The server died so we ended up having to put a new server together. We put PHP back on the machine (5.2 I believe). For the most part, all the scripts are running just fine, except for one script that is failing when we call the odbc_exec function.
I've been able to determine that it is failing in the where clause. Here is that line...
"WHERE cc.comcon_user_seq in (SELECT value FROM pa.app_ini WHERE parms='comcon_user_seq') ".
The odbc_error return code was 07006
As a brute force way to check and see what was happening, I hard coded some key values into the where clause...
"Where cc.comcon_user_seq in (7577924854296,7577924869457,7577925115603,7577925115626,7577925398200) ".
When I tested the second where clause, the php script ran just fine.
I thought I might be looking at an issue running into reserved words since the version of PHP we have on the new machine may be newer then what we had on the old machine. I ran the following to add the double quotes...
"WHERE cc.comcon_user_seq in (SELECT \"value\" FROM pa.app_ini WHERE \"parms\"='comcon_user_seq') ".
Again the script failed with an odbc_error code of 07006
Let me first say, we had another developer that was here that had put together the php scripts. I am VERY green when it comes to php. Just starting to get my feet wet. Can anybody tell from looking at this what might happen to cause PHP to choke on this embedded select in the where clause??
Any help would be greatly appreciated.
I've copied my response from the sqlanywhere.general newsgroup, where Jeff asked the same question. I did have two more thoughts this morning, so I've augmented my response accordingly.
You're under a misconception about SQL. Any implementation is free to re-order the evaluation of predicates - you wouldn't want anything else, because being able to reorder predicate evaluation is at the heart of query optimization. In this situation, the optimizer has converted your nested query to a join, and because all of the predicates are conjunctive they can be ordered arbitrarily.
The only two expressions in SQL Anywhere that are guaranteed to evaluate in order are IF and CASE.
You could use a temporary table to guarantee that only numeric values of "value" are included in the comparison. Otherwise, you could use an IF-expression in the comparison (perhaps using the ISNUMERIC() function as part of the condition), so that if the value is not numeric then comparison is made with NULL.
You could also perform the comparison in the string domain by CASTing the "value" to CHAR, so that the comparison will never result in a data exception.
Both the CAST and the use of an IF function have implications for query execution, because the server may not be able to use an index (for example) to compute the result of the join (or compute the query using naive nested iteration if the nested query is retained).
answered 08 Apr '11, 17:41
If you're getting an error code from ODBC, it's probably not a PHP issue.
Are you setup to output the error message when the ODBC call fails?
If not, I believe you can get the message with:
If you get the embedded error message, it should help you debug the issue further. If not, post it back here and someone may have hints to debug.
answered 07 Apr '11, 16:38