Nested queries gets "Warning: SQL error: , SQL state"

[GeckoNZ]

If I try to run a query while looping through a result set from another query e.g. :

$act = " SELECT account_number acct ";
$act .= " FROM acct_master ";

$Xresult = odbc_exec($conn,$accts);

while(odbc_fetch_row($Xresult))
{
$x_acct = odbc_result($Xresult,"acct");

$tran = " SELECT trans_date ";
$tran .= " FROM trans ";
$tran .= " WHERE acct = '$x_acct' ";

$Aresult = odbc_exec($conn,$tran);
$Arow = odbc_fetch_row ($Aresult);
}

I get an error like :
Warning: SQL error: , SQL state

This is running on apache with an ODBC connection to a MS SQL database. I can run individual queries just fine. It is only when I try nesting them that I have a problem. I run this type of nested query on an informix database, and MySQL databases no problems. It is only the SQL databases that give this error.

Is this a limitation of MS SQL ?

P.S. Don't take the example too seriously. It is a very simple example which I know I could fix by joining tables etc.

 

[sleipnir214]

Is the code you've posted an example or the actual code you're trying to run in your project? If it's the latter, I'd recommend you bypass the problem by combinging the two queries into one using a join.

If you print out your second query, does it look right? If you copy-and-paste that query into an application that allows you to make queries against MSSQL, does it return what you want or return an error?


Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[GeckoNZ]

see P.S. on original post. The example is a simplified query & I know I could do a join.

The second query does not produce a result set. It fails with the SQL State error.

 

[sleipnir214]

See second paragraph my original post, paying particular attention to the two questions there.


Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[GeckoNZ]

yes, the second query is OK. I can paste it into MS Query Analyser & it runs fine.

The problem has something to do with the nesting of queries and I can replicate the error by trying to run any query when looping through a previous result set array.

thanks

 

[sleipnir214]

When you ran the loop-internal query against the Informix database, were you using ODBC there, too?

On what platform are you running PHP?


Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[GeckoNZ]

Here's a dump from "PHPinfo" which shows sections of the apache support for the different databases. So I guess, looking at that, that there is "native" support for the other databases ?

odbc
ODBC Support enabled
Active Persistent Links 0
Active Links 0
ODBC library iodbc
ODBC_INCLUDE -I/usr/local/src/odbcsdk/include
ODBC_LFLAGS -L/usr/local/src/odbcsdk/lib
ODBC_LIBS -liodbc

Directive Local Value Master Value
odbc.allow_persistent
On On
odbc.check_persistent
On On
odbc.default_db
no value no value
odbc.default_pw
no value no value
odbc.default_user
no value no value
odbc.defaultbinmode
return as is return as is
odbc.defaultlrl
return up to 4096 bytes return up to 4096 bytes
odbc.max_links
Unlimited Unlimited
odbc.max_persistent
Unlimited Unlimited


mysql
MySQL Support enabled
Active Persistent Links 0
Active Links 0
Client API version 3.23.39
MYSQL_MODULE_TYPE builtin
MYSQL_SOCKET /var/lib/mysql/mysql.sock
MYSQL_INCLUDE
MYSQL_LIBS

Directive Local Value Master Value
mysql.allow_persistent
On On
mysql.default_host
no value no value
mysql.default_password
no value no value
mysql.default_port
no value no value
mysql.default_socket
no value no value
mysql.default_user
no value no value
mysql.max_links
Unlimited Unlimited
mysql.max_persistent
Unlimited Unlimited


informix
Informix support enabled
Active Persistent links 0
Active links 0
ESQL/C Version 9.51

Directive Local Value Master Value
ifx.allow_persistent
On On
ifx.blobinfile
0 0
ifx.byteasvarchar
0 0
ifx.charasvarchar
0 0
ifx.default_host
no value no value
ifx.default_password
no value no value
ifx.default_user
no value no value
ifx.max_links
Unlimited Unlimited
ifx.max_persistent
Unlimited Unlimited
ifx.nullformat
0 0
ifx.textasvarchar
0 0

 

[sleipnir214]

PHP has native support for a great number of databases, including MySQL, PostgreSQL, MSSQL, Informix, Firebird/Interbase and Ingress. Poking around in the PHP online manual will find more.

That excerpt from phpinfo() tells me you currently have native support for MySQL and Informix and that you have ODBC available, too.

I have had, whenever possible, using native communications libraries rather than ODBC. You might try installing support for MSSQL.

Did you use Informix-specific functions to access the Informix data, or did you use ODBC for that, too?



Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[GeckoNZ]

I'll look into the native MSSQL support option.

I used the informix specific functions in php whenever accessing the informix databases.

thanks

 

[sleipnir214]

From your phpinfo() output, I infer you're running PHP on a unix-like OS. I've had good success using the FreeTDS libraries to connect to MSSQL from Linux.


Want the best answers? Ask the best questions!

TANSTAAFL!!