1

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: \"\""}

Improve this question

3 Answers 3

Reset to default
2

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();
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Thanks Phil.. It wrked :).. I again got stuck in another line.. This is the code I used to get last insert id $Ins_Id = $pdo->lastInsertId(Bus_Spot_Id) in PHP... But it is not working in postgresql..
@Dileep: PostgreSQL doesn't really have a concept of "last insert ID". (Even if it did, specifying the ID when you insert would keep an ID from getting auto-created.) But if your ID field is of type serial or bigserial, then the most reliable solution is to use the sequence that is created as part of defining a serial field. Get a new ID via SELECT nextval('bus_spot_bus_id_seq') (or something very similar), and use that as the ID when you INSERT. (At which point you already have the ID, so you don't need to ask the server "what was the last ID you generated for me?". :))
finally i got the insert id by using the "RETURNING bus_spot_id " in the insert query.. Thanks :)
2

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.

Improve this answer

2 Comments

Thanks Chao.. I used Phil answer and it wrked
@Dileep: Then take a closer look at what you're actually passing, vs what you think you're passing. If forcing the type "works" here, then it's probably by accident -- at least one of your values probably isn't what you think it is.
0

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(?,?,?)
Improve this answer

1 Comment

I have edited it, it was a mistake while copying. Even after removing 1 place holder i am getting the same error. I guess this is not the right way in passing integers

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.