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

asked 18 Nov '15, 13:37

Terry%20Wilkinson's gravatar image

Terry Wilkinson
496162136
accept rate: 40%

edited 18 Nov '15, 22:27


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's gravatar image

JBSchueler
2.0k2836
accept rate: 16%

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

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.

permanent link

answered 20 Nov '15, 10:10

JBSchueler's gravatar image

JBSchueler
2.0k2836
accept rate: 16%

Jack, thanks for the workaround. That fixes the problem in my simplified example, but unfortunately not in my real code - guess I'll have to look a little harder :-)

(20 Nov '15, 11:31) Terry Wilkinson

OK, I have a more complex example - see https://dl.dropboxusercontent.com/u/69801480/SASQL_BUG.zip

Hopefully it's just another example of the same problem.

(20 Nov '15, 12:13) Terry Wilkinson
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:

×197
×46
×13
×4

question asked: 18 Nov '15, 13:37

question was seen: 515 times

last updated: 20 Nov '15, 18:13