The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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
496142036
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
1.9k2836
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
1.9k2836
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:

×187
×47
×11
×3

question asked: 18 Nov '15, 13:37

question was seen: 443 times

last updated: 20 Nov '15, 18:13