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!

Can you show the value of $code? Probably not a valid integer. — user_0, Jun 08 '15 at 15:01
actually, your column is what's probably the incorrect type. could be either/or — Funk Forty Niner, Jun 08 '15 at 15:04
@Fred-ii- I've also tried to insert the value as "PARAM:STR", but that results in the same error. — digifrog, Jun 08 '15 at 15:07
uh....... `code` is a MySQL reserved word which should be wrapped in ticks `\`` - see http://dev.mysql.com/doc/refman/5.5/en/keywords.html that's probably why and could be the same for sql server. Try that. — Funk Forty Niner, Jun 08 '15 at 15:08
In practise, I don't use "code", but a non-reserved term. I replaced it to make it more accessible. Apparently confusing, I will edit. Sorry! — digifrog, Jun 08 '15 at 15:14
In the meantime, I double-checked my column's type, which is INT(4) NOT NULL. — digifrog, Jun 08 '15 at 15:16
You tried to bind `:code` to `:myCode`. You need to change one of those. — Jay Blanchard, Jun 08 '15 at 15:19
@JayBlanchard This was a mistake in my example code, not in my real code. Thanks though... — digifrog, Jun 08 '15 at 15:31
@Mark may be a silly question but I'll ask it anyway; you are using PDO to connect with, yes? — Funk Forty Niner, Jun 08 '15 at 15:37
@Fred-ii- Sorry, what do you mean by that? I am using PDO to connect to my MS SQL Server using ODBC, if that's of any help.... — digifrog, Jun 08 '15 at 16:10
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.](http://stackoverflow.com/q/39088156/4233593) — Jeff Puckett, Aug 22 '16 at 20:29

score -1 · Answer 1 · edited Oct 22 '16 at 17:23

-1

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.

edited Oct 22 '16 at 17:23

halfer

18,701
13
79
158

answered Oct 14 '16 at 13:09

Mohammad Hasan Bakhtiarifar

19
7

Hi Mohammad - please do not add religious proselytism into your posts here, and if people remove non-essential material, please leave it like that. You may roll back if someone has changed the substantive meaning of your technical material. Thanks! – halfer Oct 22 '16 at 17:22

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

1 Answers1

Linked