SAP SQL Anywhere

Question

I'm having a problem trying to pass the sasql_result resource returned by sasql_stmt_result_metadata() as a parameter in PHP.

As the below example shows, there is no problem passing the sasql_result resource returned by sasql_query().

As the output shows, I'm running PHP version 5.4.34 and SQL Anywhere version 16.0.0.2043, but the error occurs on earlier versions as well.

<?php
class Test {
    private $_connection;
    public function process($rs){
        print "processing\n";
        var_dump($rs);
        sasql_result_all($rs);
    }
    public function execute( $sql ) {
        print "executing\n";
        $pstmt = sasql_prepare( $this->_connection, $sql );
        if( $pstmt ) {
            if( sasql_stmt_execute( $pstmt ) ) {
                $rs = sasql_stmt_result_metadata( $pstmt );
                if( $rs !== FALSE ) {
                    var_dump($rs);
                    sasql_result_all($rs);
                    return $rs;
                }
            }
        }
        return false;
    }
    public function query($sql){
        print "querying\n";
        $rs = sasql_query( $this->_connection, $sql, SASQL_STORE_RESULT );
        return $rs;
    }
    public function connect($cstring){
        print "connecting\n";
        $this->_connection = sasql_connect($cstring);
    }
}
print "<pre>";
print "PHP Version ".PHP_VERSION."\n";
print "Client Info ".sasql_get_client_info()."\n";
$test = new Test();
$test->connect("DSN=SQL Anywhere 17 Demo;UID=DBA;PWD=sql");
$rs = $test->query("SELECT * FROM Departments");
$test->process($rs);
$rs = $test->execute("SELECT * FROM Departments");
$test->process($rs);
?>;

The resulting output is:

PHP Version 5.4.34 Client Info 16.0.0.2043 connecting querying processing resource(3) of type (SQLAnywhere result) DepartmentID DepartmentName DepartmentHeadID 100 R & D 501 200 Sales 902 300 Finance 1293 400 Marketing 1576 500 Shipping 703

executing resource(5) of type (SQLAnywhere result) DepartmentID DepartmentName DepartmentHeadID 100 R & D 501 200 Sales 902 300 Finance 1293 400 Marketing 1576 500 Shipping 703

processing resource(5) of type (Unknown)

Warning: sasql_result_all(): 5 is not a valid SQLAnywhere result resource in D:\localhost\tests\sasql_prepare_test.php on line 10

Has anyone else seen this? Any suggestions?

Thanks,

Terry

Accepted Answer

2

I looked at your ZIP code and, as I said earlier, your prepared statement object is going out of scope so I believe that everything associated with it is being destructed.

Make your prepared statement object a class object. Then it will live longer (and your code will prosper - sorry, couldn't help myself).

private $_connection;
private $cnString;
private $pstmt;
.
.
.
private function _prepare($sql,$params){
         $this->pstmt = sasql_prepare( $this->_connection, $sql );
         if( $this->pstmt ) {
                 if( $params && count($params) ) {;

Of course, this means you can only have one active prepared statement associated with the class (but you can create $pstmt2, etc.). Make sure to "free/close" objects when you are done with them.

You might also try stuffing $pstmt into your result set object to hang on to it.

return new DbResultSetSASQL( $this, $rs, $pstmt );

permanent link

answered 20 Nov '15, 15:48

JBSchueler
3.3k●3●15●64
accept rate: 19%

edited 20 Nov '15, 16:01

Ok, I think I understand it now, and your suggestion worked. "stuffing $pstmt into your result set object" was the ticket. Thanks!

(20 Nov '15, 17:33) Terry Wilkinson

Great. Don't forget to add all those nice free/close calls to free up objects like result sets and avoid leaks (I'm paranoid about these things). I don't know how much PHP will take care of these things for you.

(20 Nov '15, 18:02) JBSchueler

Do my best :-) Thanks again.

(20 Nov '15, 18:13) Terry Wilkinson

flat view

Answer 2

Terry, it looks like the result set object is being freed for some reason (as if sasql_free_result($rs) had been done). My guess it has something to do with the prepared statement object going out of scope.

I tried splitting the prepare from the execute and that seems to have fixed the problem.

public function prep($sql){
    print "preparing\n";
    $pstmt = sasql_prepare( $this->_connection, $sql );
    return $pstmt;
}
public function execute($pstmt){
    print "executing\n";
    if( $pstmt ) {
        if( sasql_stmt_execute( $pstmt ) ) {
            $rs = sasql_stmt_result_metadata( $pstmt );
            if( $rs !== FALSE ) {
                var_dump($rs);
                sasql_result_all($rs);
                return $rs;
            }
        }
    }
    return false;
}
.
.
$ps = $test->prep("SELECT * FROM Departments")
$rs = $test->execute($ps);
$test->process( $rs );

Our PHP expert is on vacation at the moment. I'll defer to him for an official answer.

SAP SQL Anywhere

Problem passing sasql_result as a parameter in PHP function

Follow this question

Related questions