1

EDIT:
The issue is not about having a comma after TIMESTAMP as suggested in the comment. TIMESTAMP is not a variable and it is a Postgres keyword to convert the time and date to a timestamp data type before inserting it. Similar to a cast.

I have the following SQL query in Postgres and it works with no errors:

INSERT INTO sms (from_number, to_number, message, reply_time, sent_at) VALUES ('10000000000', '0000000000', 'ffffffff', now(), TIMESTAMP '2015-05-27 18:45:31')

I have a function in PHP with the equivalent query:

   function insertWithSentTime($from, $to, $text, $sentTime)
   {
    $con = pg_connect("host=$this->host dbname=$this->db user=$this->user password=$this->pass") or die ("Could not connect to server\n");

    $query = "INSERT INTO sms (from_number, to_number, message, reply_time, sent_at) VALUES ($1, $2, $3, now(), TIMESTAMP $4)";

    pg_prepare($con, "prepare1", $query) or die ("Cannot prepare statement\n");

    pg_execute($con, "prepare1", array($from, $to, $text, $sentTime)) or die ("Cannot execute statement\n");

    pg_close($con);
   }

When the function is called I get the following message:

PHP Warning:  pg_prepare(): Query failed:
ERROR:  syntax error at or near "$4" LINE 1: ...eply_time, sent_at) VALUES ($1, $2, $3, now(), TIMESTAMP $4)
                                                                    ^

What am I doing wrong here?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Arya
  • 8,473
  • 27
  • 105
  • 175
  • 3
    You need a comma between `TIMESTAMP` and `$4` – Jay Blanchard May 29 '15 at 19:10
  • TIMESTAMP is not a variable, it's a keyword for telling postgresql to convert the timestamp as a String type to timestamp datatype before inserting it – Arya May 29 '15 at 19:25
  • @JayBlanchard Please read my edited part and vote for reopening of the question – Arya May 29 '15 at 19:32
  • @MarkBaker the SQL statement works 100% in PGAdmin 3, I tried it many times. Please read my edited question and please vote to reopen the question – Arya May 29 '15 at 19:33
  • 1
    Have you tried quoting the variables? – Jay Blanchard May 29 '15 at 19:37
  • @JayBlanchard you mean like this INSERT INTO sms (from_number, to_number, message, reply_time, sent_at) VALUES ($1, $2, $3, now(), 'TIMESTAMP $4') – Arya May 29 '15 at 19:42
  • Look at your first example where the query works. – Jay Blanchard May 29 '15 at 19:45
  • @JayBlanchard I have also tried that and it does not work – Arya May 29 '15 at 20:23
  • Based on the unaccepted answer in [this question](http://stackoverflow.com/questions/13693723/how-to-pass-string-with-timestamp-in-prepared-statement), I think you just need to drop the `TIMESTAMP` keyword. – JasonMArcher May 29 '15 at 20:36
  • @JasonMArcher I tried it without the TIMESTAMP keyword and it gives the following message pg_execute(): Query failed: ERROR: invalid input syntax for type timestamp with time zone: "" in /var/www/html/test/database.php on line 38 Cannot execute statement – Arya May 29 '15 at 20:38
  • 3
    What does the value in `$sentTime` look like? – JasonMArcher May 29 '15 at 20:47
  • @JasonMArcher I'm very embarrassed to say that you are right, the mistake was in the $sentTime variable – Arya May 29 '15 at 20:55

0 Answers0