I have code similar to the following:
$data'someField' = (isset($_POST'someField')) ? 'Y' : 'N'; $stmt = $db->prepare("INSERT INTO public.someTable (someField) VALUES (':someField');"); $stmt->bindParam(':someField', ($data'someField'), PDO::PARAM_STR, 1); $db->beginTransaction(); $stmt->execute(); $db->commit();
The field is a character(1) that contains either a Y or N (I’d rather use a bool/int(1), but I can’t change that unfortunately). As you can see, this takes the POST data from an HTML form and set Y or N based on a checkbox. The variable looks properly set when I echo it. The query works if I manually put a ‘Y’ or ‘N’ into the statement, but when using bindParam() I get the following error:
SQLSTATE22001: String data, right truncated: 7 ERROR: value too long for type character(1)
The really odd thing is even if I do something like this:
$stmt->bindParam(':someField', substr($data'someField', -1), PDO::PARAM_STR, 1);
I still get the error. From the user comments on the function page at php.net:
Steve M 19-Nov-2009 07:28 Note that
when using PDOStatement::bindParam an
integer is changed to a string value
upon PDOStatement::execute(). (Tested
There is no further mention of this, or anything on how to get around it. I am assuming that this the root of the problem. I have also tried using bindValue() with no change.
Found the answer for this problem here: String encoding problem on PdoStatement->bindParam()?
Basically PDO will automatically escape the parameter for you, so when I made my query:
"INSERT INTO public.someTable (someField) VALUES (':someField');"
Once PDO was done with it it ended up looking like this:
"INSERT INTO public.someTable (someField) VALUES (''Y'');"
Correct use of the prepare() function is:
$stmt = $db->prepare("INSERT INTO public.someTable (someField) VALUES (:someField);");