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?

asked 09 Apr '14, 11:55

tzanarella's gravatar image

tzanarella
36227
accept rate: 0%

edited 10 Apr '14, 02:56

Comment Text Removed

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.

(09 Apr '14, 14:50) Breck Carter

@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.

(10 Apr '14, 02:57) tzanarella
Replies hidden

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...)

(10 Apr '14, 03:34) Volker Barth

$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.

(10 Apr '14, 04:35) tzanarella

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

(10 Apr '14, 08:24) Breck Carter

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.

(10 Apr '14, 14:12) JBSchueler

$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.

(10 Apr '14, 14:21) JBSchueler

$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).

(10 Apr '14, 14:22) JBSchueler
More comments hidden
showing 4 of 8 show all flat view

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 );
?>
permanent link

answered 10 Apr '14, 14:04

JBSchueler's gravatar image

JBSchueler
2.1k2837
accept rate: 15%

edited 10 Apr '14, 14:38

1

@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!

(18 Apr '14, 12:26) tzanarella
Replies hidden

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

(18 Apr '14, 12:31) Breck Carter
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:

×47
×13
×3

question asked: 09 Apr '14, 11:55

question was seen: 7,869 times

last updated: 18 Apr '14, 12:31