Postgresql insert in PDO

1
votes

All my scripts are written in pdo-mysql . Now i am migrating to posgresql. I have been stuck in an insert query.

CREATE TABLE bus_spot
(
    bus_id integer NOT NULL,
    spot_loc_id integer NOT NULL,
    bus_dir_id integer NOT NULL
)

php query used

$Bus_Id = 579;
$Bus_Dir_Id = 3;
$User_Loc_Id = 465;

$ISp_Res = $pdo->prepare("INSERT INTO bus_spot(bus_id, bus_dir_id, spot_loc_id) VALUES(?, ?, ?)");
$ISp_Res->execute(array($Bus_Id, $Bus_Dir_Id, $User_Loc_Id));

I am getting the following error when trying to insert this row

SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input syntax for integer: \"\""}

3
phppostgresqlpdo

3 Answers

2
votes

To me it looks like Postgres is being a bit picky about the input format for the integers. Try binding the parameters using the appropriate type instead of passing them to the execute() method which implicitly uses strings

$ISp_Res = $pdo->prepare(...);

$ISp_Res->bindParam(1, $Bus_Id, PDO::PARAM_INT);
$ISp_Res->bindParam(2, $Bus_Dir_Id, PDO::PARAM_INT);
$ISp_Res->bindParam(3, $User_Loc_Id, PDO::PARAM_INT);

$ISp_Res->execute();
2
votes

If that example is the actual code you're using, then your server is borked. :P Your example works just fine in my VM.

More likely is that one of the "integers" you're passing to the query is stringifying as an empty string. That's the case for the constants null and false, and of course ''. PostgreSQL is stricter about conversions than MySQL is, and generally will throw an error rather than try to convert a string to an integer if it doesn't look like one.

See what happens if you pass intval($Bus_Id) etc instead of passing the values as is. If that works, then your values are broken, and you need to figure out why.

0
votes

You have 4 placeholders in your VALUES

VALUES(?,?,?,?)

But your table/array is only 3

array($Bus_Id,$Bus_Dir_Id,$User_Loc_Id)

Remove 1 placeholder

VALUES(?,?,?)