PDO & MS SQL Server: inserting value for integer column results in error

2
votes

I am trying to insert an integer value into my INT column. However, when I execute the following PHP-code, it results in an error telling me that I am trying to insert a "text", which should not be the case...

PHP

$myCode = 1;
$sth = $db->prepare("INSERT INTO tblCodes (myCode) VALUES (:myCode)");
$sth->bindParam(':myCode', $myCode, PDO::PARAM_INT);
$sth->execute();

Error

SQLSTATE[22018]: Invalid character value for cast specification: 206 [FreeTDS][SQL Server]Operand type clash: text is incompatible with int (SQLExecute[206] at /builddir/build/BUILD/php-5.6.9/ext/pdo_odbc/odbc_stmt.c:254)

Can anybody help me? Thanks a lot!

1
phpsql-serverpdo
Can you show the value of $code? Probably not a valid integer. - user_0
actually, your column is what's probably the incorrect type. could be either/or - Funk Forty Niner
@user_0 The value of $code is 1, as shown above. - digifrog
@Fred-ii- I've also tried to insert the value as "PARAM:STR", but that results in the same error. - digifrog
You may want to check again, this seems to be partially fixed in PHP 7. While I can execute the statement once, I cannot seem to execute multiple times. - Jeff Puckett

1 Answers

-1
votes

I think that it is a bug. I solved my problem by change in the query. In your case change the query as below:

$myCode = 1;
$sth = $db->prepare("INSERT INTO tblCodes (myCode) VALUES (CAST(CAST(:myCode AS varchar) AS INTEGER))");
$sth->bindParam(':myCode', $myCode, PDO::PARAM_INT);
$sth->execute();

Now it should be worked by both PDO::PARAM_INT and PDO::PARAM_STR.