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 0 · Answer 1 · answered Mar 11 '22 at 11:55

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.

score -1 · Answer 2 · 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

19,824
17
99
186

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

2 Answers2

Linked