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;
}