Prepared statement sasql_stmt_execute always returns empty results set

[Former Member]

Hello,
I'm using Sybase SQL Anywhere sasql_* PHP functions in order to connect to a Sybase database.

I'm trying to launch this query:

$query = "SELECT contract_head.contract_number FROM contract_head WHERE contract_head.year
= '2014'";


If I use this code everyting works just fine (I get 152 results):
$results = sasql_query($db, $query); // $db is the connection handler previously created using sasql_connect();
while( $res = sasql_fetch_array($results, SASQL_ASSOC) ) {
    // use $res to read the results
}


Let's say I want to use prepared statements:
$a = sasql_prepare($db, "SELECT contract_head.contract_number FROM contract_head WHERE contract_head.year = ? ");
echo '$a: '; var_dump($a);

$year = 2014;
$b = sasql_stmt_bind_param($a, 'i', $year); // if I use 's' nothing changes
echo '$b: '; var_dump($b);

$e = sasql_stmt_execute($a);
echo '$e: '; var_dump($e);

$i = sasql_stmt_num_rows($a);
echo '$i: '; var_dump($i);

$n = sasql_stmt_next_result($a);
echo '$n: '; var_dump($n);

$r = sasql_stmt_result_metadata($a);
echo '$r: '; var_dump($r);
var_dump($r);


What I see on my browser after executing this code is:
$a: resource(3) of type (SQLAnywhere statement)
$e: bool(true)
$i: int(0)
$n: bool(false)
$r: bool(false)
bool(false)


What should I check in order to make prepared statements work?

[Breck_Carter]

Is that the actual code?

If so, the prepared statement is in $st, not $a; try this:

$b = sasql_stmt_bind_param($st, 'i', $year);

If that is NOT the actual code, then please post the actual code.

[Former Member]

@Breck Thanks for your comment. This is the actual code, actually I made a mistake on copy-paste, but the statement is $a and I get empty results.

[VolkerBarth]

What does $b return?

Can you replace the call to sasql_stmt_bind_param() with sasql_stmt_bind_param_ex(), which allows a 1:1 binding of parameters?

(That's all wild guessing...)

[Former Member]

$b returns bool(true).

If I change the function to sasql_stmt_bind_param_ex($a, 0, $year, 'i'); nothing changes: it returns bool(true) and 0 results.

[Breck_Carter]

Try telling SQL Anywhere to turn on "request level logging" so you can see exactly what the server sees. Assuming you are using version 16, see this Help topic: Request logging

[jack_schueler]

Note that $n = sasql_stmt_next_result($a); skips to the next result set (but you have only one). But that isn't the only issue with the code.

[jack_schueler]

$i = sasql_stmt_num_rows($a);

From the doc: The actual number of rows in the result set can only be determined after the sasql_stmt_store_result function is called to buffer the entire result set. If the sasql_stmt_store_result function has not been called, 0 is returned.

[jack_schueler]

$n = sasql_stmt_next_result($a);

This moves to the next result set. You haven't dealt with the first one (and presumably the only one).

[jack_schueler · Accepted solution]

Here's an example that might help. It's similar to what you want to do.

<?php
$conn = sasql_connect("Server=demo;UID=DBA;PWD=sql;LOG=C:\\Temp\\MyClient.Log");
$stmt = sasql_prepare($conn, "SELECT ID,Name,Description FROM Products WHERE ID > ? ");

$year = 500;
$b = sasql_stmt_bind_param($stmt, 'i', $year);

$num_params = sasql_stmt_param_count($stmt);
echo "Number of params = $num_params\\n";

$b = sasql_stmt_execute($stmt);
# // one way to fetch the result set
# // for query "SELECT Name FROM Products WHERE ID > ? "
#    $b = sasql_stmt_bind_result($stmt, $name );
#    while( sasql_stmt_fetch( $stmt ) )
#    {
#        echo "Fetched: $name\\n";
#    }
# // a second, more general, method
$res = sasql_stmt_result_metadata($stmt);

$num_cols = sasql_num_fields($res);
echo "Number of cols = $num_cols\\n";

$num_rows = sasql_num_rows($res);
echo "Number of rows = $num_rows\\n";

$curr_row = 0;
echo "Fetching rows:\\n";
while( ($row = sasql_fetch_row( $res )) )
{
        echo "FetchedRow:\\n";
        $curr_row++;
        $curr_col = 0;
        while( $curr_col < $num_cols ) {
            echo "$row[$curr_col]\\t|";
            $curr_col++;
        }
        echo "\\n";
}

# Clean up.
sasql_free_result( $res );
sasql_disconnect( $conn );
?>

[Former Member · Accepted solution]

@JBSchueler Thank you very much! Everything works now. I prefer using sasql_fetch_assoc instead of sasql_fetch_row, so that I can obtain an associative array in $row with the tables fields as keys (it's more confortable). Just one more question: in most databases (MSSSQL, Postgre, MySql, etc...) I can use prep stmts with labels instead of "?" question marks (ie.: "....WHERE id = :productID"). Then I bind every param like for example: stmt_bind(':productID', $_POST['product_id'], SQL_INT); Is there a way to obtain the same behaviour also on SQLAnywhere? Thank you again very very much!

[Breck_Carter · Accepted solution]

Try asking your new question as a new question, you will get more people to pay attention to it.