int8 postgres data type and php problem

Anon

I have a table:

create table board_uid_globalpool (
type int4,
first int8 default -1 not NULL,
last int8 default -1 not NULL,
);

The query sent from php is:
INSERT INTO board_uid_pooltracking (type, first, last) VALUES (1, 512::int8, 767::int8);

Which gives:
Warning: PostgreSQL query failed: ERROR: Unterminated quoted string

When query is pasted into the postgres interactive terminal it executes correctly.

Additional data:
-The error does not happen all the time, it depends on the values of "first" and "last".

-The error existed before ::int8 typecasting as well.
-If the order of fields is changed the query will sometimes execute. (example first, type, last)

Any assistance is appreciated.

Anon

Please ignore the difference in table names. It was an editing error.

create table board_uid_globalpool (
type int4,
first int8 default -1 not NULL,
last int8 default -1 not NULL,
);

The query sent from php is:
INSERT INTO board_uid_pooltracking (type, first, last) VALUES (1, 512::int8, 767::int8);

Anon

Could you post the php code?

Anon

//convert large numbers into strings
$FIRST=sprintf("%s", gmp_strval($FIRST_GMP, 10));
$LAST=sprintf("%s", gmp_strval($LAST_GMP, 10));

$QUERY="INSERT INTO board_uid_pooltracking (type, first, last) VALUES ($TYPE, $FIRST::int8, $LAST::int8);";

$DBHANDLE=pg_connect("host=localhost user=postgres dbname=boardbook2");
if($DBHANDLE==FALSE){...}

$RESULT=pg_Exec($DBHANDLE, $QUERY);

Anon

Sorry, I can't get this error to come up on php 4.2.1.

Anon

4.0.6

Anon

Well, I'm pretty sure there's nothing wrong with postgres or php - there will be a bug in your code somewhere.

For starters, you are coding in a very dangerous style. Try using this as your query instead:

INSERT INTO board_uid_pooltracking (type, first, last) VALUES ('$TYPE', '$FIRST'::int8, '$LAST'::int8);

Also, add an 'echo $QUERY' after the '$QUERY=' line and tell us exactly what the query is when it fails.

Chris

Anon

I agree with Chris and I want to add that there's no need to determine what type of integer the field is in your insert query, like you did:

$QUERY="INSERT INTO board_uid_pooltracking (type, first, last) VALUES ($TYPE, $FIRST::int8, $LAST::int8)";

I suggest you remove the ::int8 after the string you insert and as Chris said have all your values in single quotes.

$QUERY="INSERT INTO board_uid_pooltracking (type, first, last) VALUES ('$TYPE', '$FIRST', '$LAST')";

Oh saw it right now... At the end of your insert query string you have an additional semicolon: $LAST::int8);"; Remove the one that is before the double quotes...

At the end I want to tell you that it is better to have the $DBHANDLER string in a separate file, say config.inc so you won't have to type it down in every file you want to do a db query... Hope I helped 🙂

Anon

The problem has been found:

The numbers 'first' and 'last' were generated by the following statements:

$FIRST=gmp_strval($a);
$LAST=gmp_strval($b);

gmp_strval() appends a \0 (NULL) character to the end of $FIRST and $LAST. This only occurs when $a or $b are > 0. When the query string was parsed, parsing stoped at the NULL character. When the query string was echo'ed PHP did not continued to dislay the rest of the string past the first encountered NULL.

Hope this helps someone.