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.

Jeff Gibson
Intercept Solutions - Sybase SQL Anywhere OEM Partner
Nashville, TN

asked 07 Apr '11, 16:18

Jeff%20Gibson's gravatar image

Jeff Gibson
1.8k416172
accept rate: 20%

edited 15 Mar '13, 21:39

Mark%20Culp's gravatar image

Mark Culp
24.9k10141297

1

Welcome to sqlanywhere-forum, Jeff! FYI when responding to an answer, click the "Add new comment" and enter a comment rather than a new answer. Answers are usually sorted by upvotes so they may not appear chronologically, whereas comments will. Also, there is no need to add your name or a signature to the bottom of your postings.

(07 Apr '11, 23:09) Graeme Perrow

Thanks for clearing that up for me Graema. I've always wondered what the difference was between comments and answers. Even when it was over on stackexchange. :)

(08 Apr '11, 00:32) Jeff Gibson

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.

Hi Jeff,

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

permanent link

answered 08 Apr '11, 17:41

Glenn%20Paulley's gravatar image

Glenn Paulley
10.8k576106
accept rate: 43%

1

FWIW, some examples for similar queries can be found in in this question...

(09 Apr '11, 06:58) Volker Barth
1

Hey Glenn. I have to say that I believe your answer has done two things.

  1. It helped get this issue fixed.
  2. It's made me want to go back in sit in my high chair so somebody can spoon feed me SQL syntax again since I feel like the predicate issue has completely altered my reality of SQL. (Thanks a TON!!) ;)

First, for my fix, I ended up inserting the needed keys into a global temporary table and then put that in my where clause so I knew I was only passing it valid keys.

Now here is where my issue is now. My "perception" has always been that I've treated subqueries, embedded selects, function/procedure calls like mathematical expressions. I.e., you've got expressions sitting inside parentheses, and the deepest expression gets evaluated first, and then it works its way out to the main expression/function.

Now, based on what you've told me, and what I've read on here about predicates, it seems like my assumptions has been WAY off!

So if you have any links to the cliff notes on writing correct sql syntax, I would really appreciate it.

I think for the sake of what I’m trying to understand, I’ll post another question to the group so it’s not buried in this question.

Thanks again Glenn!! Any further help is always appreciated!!

(13 Apr '11, 20:32) Jeff Gibson
Replies hidden
1

Jeff, while I really appreciate your words of self-critism and your readiness to learn further, I guess your current SQL usage is alright with me:

It just points out that SQL - as a primarily declarative language - usually seems to do what one expects it to, and that's one of the big advantages IMHO. Of course there are exceptions to the rule, and that's when one will have to find them and learn something new... (we all here do, methinks). But that usual learning experience need not (and should not) lead to a self-assessment of "having it done wrong all the time"...

...particularly as you have used the right database system all the time, for sure:)

(15 Apr '11, 06:08) Volker Barth

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:

:::PHP
$result = @odbc_exec($conn,$sql_statement) or die(obdc_errormsg());

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.

permanent link

answered 07 Apr '11, 16:38

Tyson%20Lewis's gravatar image

Tyson Lewis
2.2k1641
accept rate: 22%

I appreciate your quick response Tyson,

I changed my getting the return value from odbc_error to odbc_errormsg. When I did that, I got a more detailed response back on what was going on. It came back with the following error...

[Sybase] [ODBC Driver] [Adaptive Server Anywhere] Restricted data type attribute violation: Cannot convert LC001 to a numeric(30,6)

Again, here is my embedded select in the where clause with some more detail. When I run the embedded select on its own in ISQL, it works like a champ. Pulls back just the requested values.

WHERE cc.comcon_user_seq in (SELECT value FROM pa.app_ini WHERE application='intranet' and section='personalschedule' and parms='comcon_user_seq')

However, if I go into the app_ini table and look at the value in the "value" column on the first row, it reads "LC001". My impression however, is that by putting the conditions (WHERE application='intranet' and section='personalschedule' and parms='comcon_user_seq'), it would only bring back the rows that meet those criteria. At least that's what I was taught in SQL 101. :)

For your info, the row that has the value that is showing up as failing has a section value of faxbillingcode, parms has a value of 7571485326432, and then value is 'LC001'.

So from what I am looking at here, it feels like the where clause it being ignored in my embedded select.

Any ideas on why this might be occuring. This has been running just fine in PHP for years. However, when we built our new machine, we may have gone up a few versions.

Any thoughts would be greatly appreciated!!!

(07 Apr '11, 18:26) Jeff Gibson

The where clause shouldn't be ignored when sent from PHP, so if it is dynamically created, there might be a failure there. In the code I gave you before try concatenating the sql statement to the error message like: die($sql_statement."
".odbc_errormsg());.

If the where clause comes back as expected, check to see if your ASA installation is up to the previous EBF level that it was before; although be warned that ASA 6 is long past end-of-life so EBFs are no longer available.

(07 Apr '11, 18:44) Tyson Lewis
Replies hidden

ASA installation is on the absolute latest (and last) release for ASA6. ASA 6.0.4 Build 3799. Yes. We're fully aware that this is an EXTREMELY out of date version. The good news is that we're testing our database right now in SQL Anywhere 12. :)

What's so odd is that this php script ran just fine until we moved to a new version of php. That's what makes me think this is a php issue and not a SQL Anywhere issue.

I can tell you that when the error happens, the SQL script that has been concatenated together gets echoed to the webpage. The SQL statement looks exactly like it's supposed to. I can even copy it off the page and run it in ISQL and it runs flawlessly.

I will try the die statement tomorrow morning and see what addition information I can asertain from it.

I appreciate your help for the newbie!!

(08 Apr '11, 01:02) Jeff Gibson

What kind of db extension are you using to access ASA with PHP? Something SA-specific - or a generic ODBC module?

Just a wild guess: May there be any difference in these modules between the old and the new system? (Newer SA versions have their own PHP module but I don't know whether you can use them with an ASA 6 db - though 9.0.2 as the first ASA version with PHP support might be able to connect to ASA 6). - Just a wild guess, as stated...

(08 Apr '11, 03:39) Volker Barth

We're connecting to the database through a standard ODBC connection. Nothing special. We have one php script that is referenced that holds the dsn information. Pretty basic.

I can tell you that the only big difference is the servers. Before we lost our last machine, we were running everything on a Windows 2000 Server box. Now we are using a Windows Server 2003 Standard box.

Could we be looking at a difference in how old versions of ODBC handled the data?

(08 Apr '11, 14:13) Jeff Gibson
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×145
×95
×70
×7

question asked: 07 Apr '11, 16:18

question was seen: 8,602 times

last updated: 15 Mar '13, 21:39