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.

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
1.9k2836
accept rate: 16%

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
×11
×3

question asked: 09 Apr '14, 11:55

question was seen: 7,650 times

last updated: 18 Apr '14, 12:31