Prepared PDO Report on ODBC Sybase Error "PARAM datastream"

Question

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

Error:

"[DataDirect] [Sybase Wire ODBC Protocol Driver] [SQL Server] There is no host variable that matches the one specified in the PARAM data stream. This means that this variable" was not used in the previous DECLARE CURSOR or SQL (SQLExecute [3801 ] in ext \ pdo_odbc \ odbc_stmt.c: 254) "

• I am looking for a single row in a database using a 6-digit identifier, which is stored in the database as VARCHAR, but is usually a 6-digit number.

• A database connection reports success.

• The identifier that has passed the query string is verified.

• A prepared statement results in the above error.

The backup direct ODBC_EXEC statement in the else clause returns the data I'm 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 any experience with this error?

Edit: Sybase Manual reports an error ...

Error 3801: There is no host variable corresponding to that specified in the PARAM data stream. This means that this variable is `%. * S 'was not used in the previous DECLARE CURSOR or SQL command.

Explanation: Adaptive Server was unable to complete the requested action. Check your team for missing or incorrect database objects, variable names, and / or input.

Which is strange, because my error (indicated above) does not tell me which variable does not have a host.

Also crash 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 put a variable in a query like this ...

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

Therefore, I think this is due to the fact that the parameter is not related to the placeholder, but I can not understand why.

If I can't handle this, I will have to go back to creating my query as a string and hope that my input validation will be proved by the bullets.

Answer 1

Your problem seems to be related to the default data type that PHP assigns to variables in placeholders. SQL Statement looks for a number, but PHP interprets it as something else. You can prevent this by using quotation marks around the placeholder variable. Note that in operations 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 a placeholder, it should be the same:

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