Variable binding for Informix returns an error

Question

I get the following error when I add: I start how to skip this parameter. I know that an SQL query works if I hardcode the SKIP / NEXT and: clientID values ​​alone. If I delete the sentence: start and leave it as SKIP 1 FIRST 5 ... WHERE t1.customer_num = :customerID ... , it works fine. I can not find the cause of the error.

Error

 exception 'PDOException' with message 'SQLSTATE[HY004]: Invalid SQL data type: -11064 [Informix][Informix ODBC Driver]SQL data type out of range 

The material I tried:

• Using BindParam instead of BindValue to bind parameters by reference.
• Using PDO_STR for binding attempts: start as a string. No success.
• The value of hard coding for: run in the SQL query itself. It works.
• Using $sql->bindValue(':start', (int) 1, PDO:PARAM_INT); - not.
• Trying number 4, assigning the PHP variable first, the same result.

Any suggestions? I am using PHP 5.3 (something recent) with Informix 11 using a PDO connector. Again, it only works with clientID, but not with: start and returns the above error.

 $sql = null; $sql= $conn->prepare('SELECT SKIP :start FIRST 5 TRIM(loc_esi_id) FROM customer t1,customer_ts_data t2 WHERE t1.customer_num = :customerID AND t1.customer_num = t2.customer_num'); //Bind values to parameters(by value) $sql->bindValue(':start', $start ,PDO::PARAM_INT); $sql->bindValue(':customerID', $customerID, PDO::PARAM_INT); //$sql->bindParam(':count',$count,PDO::PARAM_INT); $results = null; try{ $sql->execute(); $results = $sql->fetchAll(); } catch (PDOException $e) { //Error Handling, etc. 

Answer 1

In general, the notation :start for a placeholder is neither standard SQL syntax nor Informix (native) syntax. Should you use ? for the placeholder, thus:

 $sql= $conn->prepare('SELECT SKIP ? FIRST 5 TRIM(loc_esi_id) FROM customer t1 JOIN customer_ts_data t2 ON t1.customer_num = t2.customer_num WHERE t1.customer_num = ? AND '); 

(and if all this should be on the same line in PHP, I apologize for the accuracy of the mass kill in the interests of readability).

Now there is a possibility that the PDO system will automatically convert the notation :start to ? , and in this case we have another problem. But if you are not sure that the :name notation works, ... One of the reasons why you are not sure that the call to bindValue() seems to need names rather than the quantity that you might need ? . bindValue() your code error checked bindValue() calls?

This ESQL / C code works by creating the expected result.

 #include <stdio.h> #include <stdlib.h> int main(int argc, char **argv) { $ char *dbase = "stores"; $ int num_skip = 3; $ int num_fetch = 5; if (argc > 2) { fprintf(stderr, "Usage: %s [dbase]\n", argv[0]); exit(1); } if (argc == 2) dbase = argv[1]; exec sql whenever error stop; exec sql connect to :dbase; exec sql prepare p from "select skip ? first ? tabid, tabname from informix.systables"; exec sql declare c cursor for p; exec sql open c using :num_skip, :num_fetch; while (sqlca.sqlcode == 0) { $ int4 tabid; $ varchar tabname[129]; exec sql fetch c into :tabid, :tabname; if (sqlca.sqlcode != 0) break; printf("%d: %s\n", tabid, tabname); } exec sql close c; exec sql free c; exec sql free p; exec sql disconnect all; return 0; } 

Exit

 4: systabauth 5: syscolauth 6: sysviews 7: sysusers 8: sysdepend 

This shows that if the measurement designations are used correctly, you can use the parameters for SKIP and FIRST.

If you cannot find a way to make this work with PDO, you may have received an error. If you can run your code with the environment variable SQLIDEBUG=2:/tmp/your_sub_dir/check set to some similar value, then you should find a record of what was sent to the server (except for setting up the connection) in a file with the name e.g. /tmp/your_sub_dir/check_21484_0_aedc1e0 , The number pattern is a bit variable. Then you can run the sqliprint program in the file and see what PDO sends to the server. This will be a very quick way to determine if PDO or Informix contains an error.

For example, part of the result obtained from sqliprint was:

 C->S (20) Time: 2012-05-29 17:55:08.65225 SQ_CONNECT "stores" [6] "stores" [6] C->S (72) Time: 2012-05-29 17:55:08.65239 SQ_PREPARE # values: 2 CMD.....: "select skip ? first ? tabid, tabname from informix.systables" [60] SQ_NDESCRIBE SQ_WANTDONE SQ_EOT 

You can see that the SQL statement is sent very clearly. If you do not see ? placeholders, then there is a problem upstream; either the PDO driver for Informix is ​​not doing its job properly or is being misused. If you saw placeholders ? , we will have a different set of problems, but I will be surprised if this problem.

The only caveat in the SQLIDEBUG mechanism is that you need to get the environment variable set in the environment of any process connecting to the database. For a stand-alone ESQL / C program, this is trivial. If you go through a web server and PHP, it can be more complicated and more efficient; but it can be done.