PDO Prepared Statement over ODBC Sybase "PARAM datastream" error

Question

I am trying to convert some old PHP ODBC queries over to PDO Prepared statements and am getting an error I cannot find too much information on.

The Error is:

"[DataDirect][ODBC Sybase Wire Protocol driver][SQL Server]There is no host variable corresponding to the one specified by the PARAM datastream. This means that this variable '' was not used in the preceding DECLARE CURSOR or SQL command. (SQLExecute[3801] at ext\pdo_odbc\odbc_stmt.c:254)"

I am searching for a single row in the database using a 6 digit ID that is stored in the database as a VARCHAR but is usually a 6 digit number.
The database connection is reporting successful.
The ID passed by the query string is validated.
The prepared statement results in the above error.

The backup straight ODBC_EXEC statement in the else clause returns the data I am looking for.

//PDO Driver Connect to Sybase
try {
    $pdo = new PDO("odbc:Driver={Sybase ASE ODBC Driver};NA=server,5000;Uid=username;Pwd=password;");
    $pdo_status = "Sybase Connected";
} catch(PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}


if((isset($_GET['id'])) AND ($_GET['id'] != "")) {

//Validate ID String
if(!preg_match("/^[A-Za-z0-9]{5,7}/",$_GET['id'])) {
    $query1_id = FALSE;
    echo "Invalid ID";
    exit;
} else {
    $query1_id = $_GET['id'];
}

$query1 = $pdo->prepare("SELECT * FROM People WHERE PersonId= ?");
$query1->execute(array($query1_id));
if($query1->errorCode() != 0) {
        $person_data = $query1->fetch(PDO::FETCH_ASSOC);    
        echo "Person Data from PDO: ";
        print_r($person_data);
    } else {
        $errors = $query1->errorInfo();
        echo $errors[2];
//Try the old way to confirm data is there.
        $odbc_query1 = "SELECT * FROM People WHERE PersonId='$query1_id' ";
        $person_result = odbc_exec($conn,$odbc_query1) or die("Error getting Data, Query 1");
        $person_data = odbc_fetch_array($person_result);
        echo "Person Data from ODBC_EXEC: ";
        print_r($person_data);
    }

It also fails if I use:

$query1 = $pdo->prepare("SELECT * FROM People WHERE PersonId= :id ");
$query1->execute(array(":id"=>$query1_id));

Does anyone have experience with this error?

Edit: Sybase Manual says this about the error...

Error 3801: There is no host variable corresponding to the one specified by the PARAM datastream. This means that this variable `%.*s' was not used in the preceding DECLARE CURSOR or SQL command.

Explanation: Adaptive Server could not perform the requested action. Check your command for missing or incorrect database objects, variable names, and/or input data.

Which is odd because my error (quoted at the top) doesn't tell me which variable has no host.

Also fails if I use...

$query1 = $pdo->prepare("SELECT * FROM People WHERE PersonId= :id ");
$query1->bindParam(':id',$query1_id,PDO::PARAM_STR);  //Or PARAM_INT
$query1->execute();

The query works if I place the variable in the query like this...

$query1 = $pdo->prepare("SELECT * FROM People WHERE PersonId= '$query1_id'");

So I think it has something to do with the parameter not being bound to the placeholder but I can't figure out why.

If I can't work this out I'll have to revert to building my query as a string and hoping my input validation is bullet proof.

have you tried casting your $query1_id to a string and setting the length? $query1->bindParam(':id',(string) $query1_id,PDO::PARAM_STR, 6); ? — Patrick Forget, Jul 19 '13 at 17:18

Carl McDade · Answer 1 · 2014-02-15T02:40:32.653

Your problem seems to be with the default data type PHP assigns to variables in the placeholders. The SQL Statement is looking for a number but PHP is interpreting it as something else. You can prevent this using quotes around the placeholder variable. Notice that in the statements that work you have apostrophes ('') around the value that PHP sees:

$query1 = $pdo->prepare("SELECT * FROM People WHERE PersonId= '$query1_id'");

Try this when using the placeholder it should be the same:

$query1 = $pdo->prepare("SELECT * FROM People WHERE PersonId= ':id'");

PDO Prepared Statement over ODBC Sybase "PARAM datastream" error

1 Answers1