Postgres Pro
Downloads
Home   >   mailing lists

Thread: Error with query

Error with query

From
Helgi Örn Helgason
Date:
I'm still a postgres newbie so don't get upset if I'm sending this
question to the wrong forum, just tell me if I am and I'll move it.

I get this error message in Firefox:
Error with query: ERROR: invalid input syntax for type date: "" LINE
1: ..._name, pack_tidin, pack_tidut, pack_lunch) VALUES('', '', ''...
^

When running this query with PHP:

<?php
$db = pg_connect('host=localhost dbname=name user=me password=pass');

$pack_date = pg_escape_string($_POST['pack_date']);
$pack_week = pg_escape_string($_POST['pack_week']);
$pack_day = pg_escape_string($_POST['pack_day']);
$pack_pts = pg_escape_string($_POST['pack_pts']);
$pack_name = pg_escape_string($_POST['pack_name']);
$pack_tidin = pg_escape_string($_POST['pack_tidin']);
$pack_tidut = pg_escape_string($_POST['pack_tidut']);
$pack_lunch = pg_escape_string($_POST['pack_lunch']);

$query = "INSERT INTO timmar(pack_date, pack_week, pack_day, pack_pts,
pack_name, pack_tidin, pack_tidut, pack_lunch) VALUES('" . $pack_date
. "', '" . $pack_week . "', '" . $pack_day . "', '" . $pack_pts . "',
'" . $pack_name . "', '" . $pack_tidin . "', '" . $pack_tidut . "', '"
. $pack_lunch . "')";
$result = pg_query($query);
if (!$result) {
    $errormessage = pg_last_error();
    echo "Error with query: " . $errormessage;
    exit();
}
printf ("Detta har lagts till i databasen - %s %s %s", $pack_date,
$pack_week, $pack_day, $pack_pts, $pack_name, $pack_tidin,
$pack_tidut, $pack_lunch);
pg_close();
?>

I've search every little " and ' and , and . and all I can think of
but it still doesn't work. I started with only 3 columns (in another
test-database) and everything was just fine.
Anyone who can spot something I missed?

If you think you need the FORM part just let me know and I'll send it.

Regards,
Helgi Örn

--
PostgreSQL 8.4.4, Ubuntu Linux 10.04, Firefox 3.6.10

Re: Error with query

From
Jayadevan M
Date:
Hi,
> I get this error message in Firefox:
> Error with query: ERROR: invalid input syntax for type date: "" LINE
> 1: ..._name, pack_tidin, pack_tidut, pack_lunch) VALUES('', '', ''...
In the insert that worked, were you inserting into pack_date column or
other columns of date type? From the error it looks like there is some
issue with the date format/value.
Regards,
Jayadevan






DISCLAIMER:

"The information in this e-mail and any attachment is intended only for
the person to whom it is addressed and may contain confidential and/or
privileged material. If you have received this e-mail in error, kindly
contact the sender and destroy all copies of the original communication.
IBS makes no warranty, express or implied, nor guarantees the accuracy,
adequacy or completeness of the information contained in this email or any
attachment and is not liable for any errors, defects, omissions, viruses
or for resultant loss or damage, if any, direct or indirect."

Re: Error with query

From
Christian Ramseyer
Date:
On 10/7/10 9:54 AM, Helgi Örn Helgason wrote:
> I'm still a postgres newbie so don't get upset if I'm sending this
> question to the wrong forum, just tell me if I am and I'll move it.
>
> I get this error message in Firefox:
> Error with query: ERROR: invalid input syntax for type date: "" LINE
> 1: ..._name, pack_tidin, pack_tidut, pack_lunch) VALUES('', '', ''...
> ^
>
> When running this query with PHP:
>
> <?php
> $db = pg_connect('host=localhost dbname=name user=me password=pass');
>
> $pack_date = pg_escape_string($_POST['pack_date']);
> $pack_week = pg_escape_string($_POST['pack_week']);
> $pack_day = pg_escape_string($_POST['pack_day']);
> $pack_pts = pg_escape_string($_POST['pack_pts']);
> $pack_name = pg_escape_string($_POST['pack_name']);
> $pack_tidin = pg_escape_string($_POST['pack_tidin']);
> $pack_tidut = pg_escape_string($_POST['pack_tidut']);
> $pack_lunch = pg_escape_string($_POST['pack_lunch']);
>
> $query = "INSERT INTO timmar(pack_date, pack_week, pack_day, pack_pts,
> pack_name, pack_tidin, pack_tidut, pack_lunch) VALUES('" . $pack_date
> . "', '" . $pack_week . "', '" . $pack_day . "', '" . $pack_pts . "',
> '" . $pack_name . "', '" . $pack_tidin . "', '" . $pack_tidut . "', '"
> . $pack_lunch . "')";
> $result = pg_query($query);
> if (!$result) {
>     $errormessage = pg_last_error();
>     echo "Error with query: " . $errormessage;
>     exit();
> }
> printf ("Detta har lagts till i databasen - %s %s %s", $pack_date,
> $pack_week, $pack_day, $pack_pts, $pack_name, $pack_tidin,
> $pack_tidut, $pack_lunch);
> pg_close();
> ?>
>
> I've search every little " and ' and , and . and all I can think of
> but it still doesn't work. I started with only 3 columns (in another
> test-database) and everything was just fine.
> Anyone who can spot something I missed?
>

This would be a lot easier to read and also safer if you used prepared
statements (see http://www.php.net/manual/en/function.pg-prepare.php), e.g.

$result =         /* no tedious quoting */
    pg_prepare($dbconn, "my_query",
    'insert into timmar(pack_date, pack_tidut) values ($1, $2)');


$result = pg_execute($dbconn, "my_query", array($pack_date, $pack_tidut));

Christian

Re: Error with query

From
Cristian Romanescu
Date:
Could you please echo the resulting SQL and post it here?

Cristian.

On 10/07/2010 10:54 AM, Helgi Örn Helgason wrote:
> I'm still a postgres newbie so don't get upset if I'm sending this
> question to the wrong forum, just tell me if I am and I'll move it.
>
> I get this error message in Firefox:
> Error with query: ERROR: invalid input syntax for type date: "" LINE
> 1: ..._name, pack_tidin, pack_tidut, pack_lunch) VALUES('', '', ''...
> ^
>
> When running this query with PHP:
>
> <?php
> $db = pg_connect('host=localhost dbname=name user=me password=pass');
>
> $pack_date = pg_escape_string($_POST['pack_date']);
> $pack_week = pg_escape_string($_POST['pack_week']);
> $pack_day = pg_escape_string($_POST['pack_day']);
> $pack_pts = pg_escape_string($_POST['pack_pts']);
> $pack_name = pg_escape_string($_POST['pack_name']);
> $pack_tidin = pg_escape_string($_POST['pack_tidin']);
> $pack_tidut = pg_escape_string($_POST['pack_tidut']);
> $pack_lunch = pg_escape_string($_POST['pack_lunch']);
>
> $query = "INSERT INTO timmar(pack_date, pack_week, pack_day, pack_pts,
> pack_name, pack_tidin, pack_tidut, pack_lunch) VALUES('" . $pack_date
> . "', '" . $pack_week . "', '" . $pack_day . "', '" . $pack_pts . "',
> '" . $pack_name . "', '" . $pack_tidin . "', '" . $pack_tidut . "', '"
> . $pack_lunch . "')";
> $result = pg_query($query);
> if (!$result) {
>      $errormessage = pg_last_error();
>      echo "Error with query: " . $errormessage;
>      exit();
> }
> printf ("Detta har lagts till i databasen - %s %s %s", $pack_date,
> $pack_week, $pack_day, $pack_pts, $pack_name, $pack_tidin,
> $pack_tidut, $pack_lunch);
> pg_close();
> ?>
>
> I've search every little " and ' and , and . and all I can think of
> but it still doesn't work. I started with only 3 columns (in another
> test-database) and everything was just fine.
> Anyone who can spot something I missed?
>
> If you think you need the FORM part just let me know and I'll send it.
>
> Regards,
> Helgi Örn
>
> --
> PostgreSQL 8.4.4, Ubuntu Linux 10.04, Firefox 3.6.10
>
>

Re: Error with query

From
Christian Ramseyer
Date:
On 10/7/10 10:26 AM, Christian Ramseyer wrote:
>>
>> $query = "INSERT INTO timmar(pack_date, pack_week, pack_day, pack_pts,
>> pack_name, pack_tidin, pack_tidut, pack_lunch) VALUES('" . $pack_date
>> . "', '" . $pack_week . "', '" . $pack_day . "', '" . $pack_pts . "',
>> '" . $pack_name . "', '" . $pack_tidin . "', '" . $pack_tidut . "', '"
>> . $pack_lunch . "')";
>> $result = pg_query($query);
>> if (!$result) {
>>     $errormessage = pg_last_error();
>>     echo "Error with query: " . $errormessage;
>>     exit();
>> }

Or if you don't like to use prepared statement, you could still drop all
these string concatenations since PHP expands variables in single quotes
that are already in double quotes:

rc@asado-263:~ $ cat foo.php

<?php
$x = "blah";
echo "x = '$x'\n";
?>

rc@asado-263:~ $ php foo.php
x = 'blah'