Re: SQL::Statement cannot parse valid Postgresql statement

[Terrence Brannon]

On Monday, December 10, 2001, at 09:16 AM, Jeff Zucker wrote:

> Terrence Brannon wrote:
>>
>> The following SQL is not parseable by SQL::Statement:
>>
>> INSERT
>>    INTO   thot_log (thot_fk,thot_type_fk,thot_temporality_fk,date)
>>    VALUES (?,?,?,CURRENT_TIMESTAMP)
>>
>> the error msg is :
>>
>> Parse error near CURRENT_TIMESTAMP)
>
> What is your aim here?  If you are inserting into PostgresSQL, then use
> DBD::Pg.  If you are using SQL::Statement to check the statement first,
> then the PG specific CURRENT_TIMESTAMP will be treated as a string and
> will expect to be quoted.  If you're trying something else, let me know
> what it is you're aiming for and I can perhaps make a suggestion.
>
>

What I have is a program which reads a SQL statement from a file. 
It parses it with SQL::Statement, then it prepares this 
statement, then does an execute against it with @ARGV. I tried 
your quoting suggestion. In fact, I put the following 3 different 
things in the file:

non-quoted:
INSERT
   INTO   thot_log (thot_fk,thot_type_fk,thot_temporality_fk,date)
   VALUES (?,?,?,CURRENT_TIMESTAMP)

single-quoted:
INSERT
   INTO   thot_log (thot_fk,thot_type_fk,thot_temporality_fk,date)
   VALUES (?,?,?,'CURRENT_TIMESTAMP')

double-quoted:
INSERT
   INTO   thot_log (thot_fk,thot_type_fk,thot_temporality_fk,date)
   VALUES (?,?,?,"CURRENT_TIMESTAMP")

the double-quoted one failed with an execute error:

DBD::Pg::st execute failed: ERROR:  Attribute 'CURRENT_TIMESTAMP' 
not found at \
/Users/metaperl/install/lib/site_perl/5.7.2/darwin/SQL/Catalog.pm 
line 187, <A>\
  line 4.

As you can see from the table, the single-quoted one put the word 
"current" in the date field. The non-quoted one actually put the 
current timestamp in the table as desired:

mydb=# select * from thot_log;
  thot_fk | thot_type_fk | thot_temporality_fk |          date
---------+--------------+---------------------+------------------------
       13 |           13 |                  13 | current
       14 |           14 |                  14 | 2001-12-10 09:45:50-08




===================
further information
===================

The actual script I am running is sql_test in the SQL::Catalog 
distribution. It is very small:

use SQL::Catalog;
use strict;

my $file = shift;

SQL::Catalog->test($file,@ARGV);


and sub SQL::Catalog::test is:

sub test {
     my ($class,$file,@bind_args) = @_;
     warn "NO PLACEHOLDER VALUES SUPPLIED" unless @bind_args;
     my $sql = load_sql_from $file; ## opens file and reads SQL

     my $parse = parse_sql $sql;    ## calls SQL::Statement

     my $dbh = db_handle;
     my $sth = $dbh->prepare($sql);

     $sth->execute(@bind_args);

     use Data::Dumper;

     return unless ($parse->{command} =~ /select/i);

     open T, '>testexec.out' or die 'cannot create output file';
     print T "Query
$sql

";

     print T "Bind Values
@ARGV

";

     printf T "Results (%d rows)\n", $sth->rows;

     while (my $rec = $sth->fetchrow_hashref) {
         print Dumper($rec);
         print T Dumper($rec);
     }


     $dbh->disconnect;

}