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

Question

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!

Answer 1

Such a situation may appear if the user to whom you connect to the database does not have the appropriate permissions.

A moment ago I had a similar problem with the scalar function. It turned out that adding execute permissions to the user solved the problem. Without permission, it seems to me that the driver is unable to determine the data types, and by default it assumes it is text.

Answer 2

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.