losing resource id# with sql anywhere and php

[TW]

I don't know if this is a SQL Anywhere problem or a PHP problem, but
it only seems to happen with SQL Anywhere so I'm suspicious :-)

I'm using SQL Anywhere 11.0.1.2452 and PHP 5.3.1.

There seems to be something funny about the resource returned by
sasql_stmt_result_metadata. When I run the script below, I get the
output shown.

You can see that just after the value for $_rs in DBResult is set, the
print statement shows it to be a SQLAnywhere result resource. When
it is tested a few statements later, it is still a resource, but the
type is now unknown, causing the subsequent call to sasql_fetch_array
to fail.

If I exchange line 8 for line 9 so that the 'query' call is used
instead of 'pquery' in the script below (i.e., it uses sasql_query
instead of sasql_prepare etc.), it works fine.

I'd appreciate any help,
Thanks,
Terry

OUTPUT:
SETRESOURCETYPE:Resource id #4SQLAnywhere result
test1RESOURCETYPE:Resource id #4SQLAnywhere result
test2RESOURCETYPE:Resource id #4Unknown
Warning: sasql_fetch_array(): 4 is not a valid SQLAnywhere result
resource in C:\Users\Terry Wilkinson\Documents\EclipseWorkspaces\PHP\NQ
\test.php on line 11


SCRIPT:
<?php

$db = new DBConnection();
$db->connect();

$sql = "select * from nqTests where eID = 3";

//$rsx = $db->query( $sql ); //WORKS IF I USE THIS ONE
$rsx = $db->pquery( $sql ); //FAILS IF I USE THIS ONE
print "test2RESOURCETYPE:$rsx->_rs".get_resource_type($rsx-
>_rs)."<br>";
$row = sasql_fetch_array( $rsx->_rs );

class DBConnection {
private $_cn;
public function connect() {
$this->_cn =
sasql_connect( "ENG=swcore_service;DBN=swCore;UID=DBA;PWD=sql" );
}

public function query( $sql ) {
$rs = sasql_query( $this->_cn, $sql, SASQL_USE_RESULT );
$newrs = new DBResult( $rs );
print "test1RESOURCETYPE:$newrs->_rs".get_resource_type($newrs-
>_rs)."<br>";
return $newrs;
}

public function pquery( $sql ) {
$stmt = sasql_prepare( $this->_cn, $sql );
if( $stmt ) {
$ok = sasql_stmt_execute( $stmt );
if( $ok ) {
$rs = sasql_stmt_result_metadata( $stmt );
$newrs = new DBResult( $rs );
print "test1RESOURCETYPE:$newrs->_rs".get_resource_type($newrs-
>_rs)."<br>";
return $newrs;
}
}
throw new Exception( $this->errorMessage());
}
}

class DBResult {
public $_rs;

public function DBResult( $rs ) {
$this->_rs = $rs;
print "SETRESOURCETYPE:$this->_rs".get_resource_type($this-
>_rs)."<br>";
}
}
?>

[Eric Farrar]

Hi Terry,

I can explain the reason behind the behavior that you are seeing, as
well as a workaround. We will investigate ways to make this easier in
future versions.

The simple answer to why the resource seems to disappear, is because
it has.

When you perform a query, the actual result set object can either be
stored at the server (using SASQL_STORE_RESULT), or wholly transfered
to the client (using SASQL_USE_RESULT). In the example you are using
in the query() function, you have used SASQL_USE_RESULT so that the
entire result set object is copied over to the client.

The problem is that the result set that is returned by the
sasql_stmt_result_metadata() function implicitly uses
SASQL_STORE_RESULT. This means that the prepared statement object must
be around in order to use that result set since the actual result set
has not been copied to the client.

In your pquery() function, the prepared statement object ($stmt) is
defined locally in the pquery() function. Once that function
completes, PHP will automatically garbage collect the $stmt object.
The problem is that you have a reference ($rsx) to the metadata result
set that was part of the prepared statment object ($stmt) which no
longer exists. In order to use the result set after the prepared
statement object is gone, you would have to store the result set
object locally using something like SASQL_USE_RESULT. Unfortunately,
there is currently no way to do this for result sets returned by
sasql_stmt_result_metadata().

So for now the workaround is to convert the result set to an object or
array (using something like sasql_fetch_array() ) BEFORE the statement
object goes out of scope.

- Eric

[TW]

Eric,

Thank you for the clear and complete reply. I now understand what is
happening, and have gained a better understanding of the difference
between SASQL_STORE_RESULT and SASQL_USE_RESULT as well.

I tried a different work-around which is to have the DBResult object
maintain a reference to the $stmt object, thereby preventing it from
being garbage-collected. I.e.,

class DBResult {
private $_stmt;
public $_rs;
public function DBResult( $stmt, $rs ) {
$this->_stmt = $stmt;
$this->_rs = $rs;
...........
}
}

That seems to work as well.

Thanks again,
Terry

[Mohammed]

I just want to make a clear distinction between SASQL_USE_RESULT and
SASQL_STORE_RESULT.

With SASQL_STORE_RESULT, the result set is cached at the client side.
By client
I mean the component that is interpreting the PHP script. In the
example below, that would be in the
web server's address space. The SASQL_STORE_RESULT causes the SA PHP
driver to fetch all the rows
and cache them in the web server's address space.

With SASQL_USE_RESULT, the SA PHP driver would only open a cursor to
the database server.
No rows are actually fetched. The fetching of the rows from the
database server happens every time
one of the sasql_fetch_*() functions is called.

The Immediate benefit of SASQL_STORE_RESULT is that the whole result
set is cached at the client
and no extra communication with the server is needed. Also, the
sasql_num_rows() would actually return the
exact (not estimate) number of rows in the result set. The draw back
is that there will be a memory overhead
on the web server to cache all the rows of the result sets. If the
expected number of rows is small, then this
overhead is small; otherwise, SASQL_STORE_RESULT should be avoided if
there is a possibility that the web
server might run out of address space to cache all the rows of the
result set.

Using sasql_stmt_result_metadata() is equivalent to SASQL_USE_RESULT
as no rows are cached
on the client side. Every call to sasql_fetch_*() would translate into
a request to the database server assuming
no prefetching is happening on the client side.

--
Mohammed

[TW]

Thanks - I think I've got it now.
Terry