Passing binary(16) to Sybase SQL Anywhere from PHP

ladolf · #1 (**permalink**) 12-15-10, 04:48

We are using Novell Asset management that uses SQL Anywhere database. I am writing PHP script that queries data from this database.

When querying database using Sybase Central, I can execute query like this

Code:

SELECT * 
FROM zenadmin.NC_Workstation
WHERE WorkstationOID=0x60309d8b7ec45d7c35c0700225174bc5

and I get one row with workstation detailis. WorkstationOID is table key that is binary(16).

When I use the same SQL statement from PHP using

Code:

sasql_query($connection, "SELECT * 
FROM zenadmin.NC_Workstation
WHERE WorkstationOID=0x60309d8b7ec45d7c35c0700225174bc5", SASQL_USE_RESULT);

then I get zero results.

I tried stripping 0x and using pack to convert it to binary, but with no success.

Please help. I already lost one day with this.

ladolf · #2 (**permalink**) 12-15-10, 09:14

I finaly found a solution. I can't beleive I lost two days on it. The problem was in sasql_num_rows function. In sybase documentation it says that this function returns:

Quote:

A positive number if the number of rows is exact, or a negative number if it is an estimate. To get the exact number of rows, the database option row_counts must be set permanently on the database, or temporarily on the connection. See sasql_set_option.

I was mistakenly using

Code:

if (sasql_num_rows($result) > 0) ...

without setting row_counts to TRUE so I always got negative number and there were no results displayed.

Here is a code:

Code:

<?php
    ///// QUERY
    $query = "SELECT * FROM zenadmin.NC_Workstation WHERE WorkstationOID=0x60309d8b7ec45d7c35c0700225174bc5";
    echo "<b>{$query}</b><br /><br />";
    /////

    //Database data
    $ASSET_SERVERNAME = "asset";
    $ASSET_IP = "192.168.1.10";
    $ASSET_PORT = "2638";
    $ASSET_DB = "myzone";
    $ASSET_USER = "test";
    $ASSET_PASS = "test";

    //Build connection string
    $conn_str = "UID={$ASSET_USER};PWD={$ASSET_PASS};DBN={$ASSET_DB};ENG={$ASSET_SERVERNAME};LINKS=TCPIP{HOST={$ASSET_IP};PORT={$ASSET_PORT};VERIFY=NO}";
    
    //Connect to database
    $cn = sasql_connect($conn_str);
    
    //Set options !!!!!!!!!!!
    //sasql_set_option($cn, "row_counts", "TRUE");
    
    //Query database
    $result = sasql_query($cn, $query, SASQL_USE_RESULT) or die("Sybase SQL error: ".sasql_error());
    
    //Display results
    echo "NUMBER OF RESULTS: ".sasql_num_rows($result)."<br />";
    echo "RESULTS:<br />";
    while ($line = sasql_fetch_array($result, SASQL_BOTH)) {
        print_r($line);
    }
    
    //Free results
    sasql_free_result($result);
    
    //Disconnect from database
    sasql_disconnect($cn);
?>

Internet Services

Web Development

Digital Marketing

Consumer Tech