On Thu, 1 Mar 2007, Bart Samwel wrote:
> I'm not experienced with prepared statements in postgresql, but given
> the documentation the syntax "timestamp with time zone $1" does not look
> correct to me. How about trying "$1::timestamp with time zone"?
Bart,
The syntax is correct, assuming $1 were to be replaced with a quoted value.
Here's a working example, and the type of query I had been trying to
generate with the prepared statement:
select * from nac_targets where
execution_time >= (timestamp with time zone '2007.001 UTC') and
execution_time <
((timestamp with time zone '2007.001 UTC') + interval '3 days')
I wasn't aware of the syntax you suggested, but it turns out that was the
solution. The following SQL works:
select * from nac_targets where
execution_time >= '2007.001 UTC'::timestamp with time zone and
execution_time < '2007.001 UTC'::timestamp with time zone +
'3 days'::interval
..and its equivalent prepared statement:
dbHandle->prepare("selectTargetsStmt",
"select * from nac_targets where "
"execution_time >= $1::timestamp with time zone and "
"execution_time < $1::timestamp with time zone + $2::interval")
("timestamp", pqxx::prepare::treat_string)
("interval", pqxx::prepare::treat_string);
It answers my other question, too; you can re-use a token like $1 and it does
the right thing (replacing both instances) when invoked.
Thanks for the help!
Jim
>
> Cheers,
> Bart
>
> Jim Stewart wrote:
> > Hello,
> >
> > I'm trying to use a prepared statement and I'm receiving a syntax error.
> > Here's a code snippet:
> >
> > -----
> > dbHandle->prepare("selectTargetsStmt",
> > "select * from nac_targets where "
> > "execution_time >= (timestamp with time zone $1) and "
> > "execution_time < ((timestamp with time zone $2) + interval $3)")
> > ("varchar", pqxx::prepare::treat_string)
> > ("varchar", pqxx::prepare::treat_string)
> > ("varchar", pqxx::prepare::treat_string);
> >
> > pqxx::work trans(*dbHandle, "selectTargets");
> > pqxx::result res(trans.prepared("selectTargetsStmt")
> > ("2007.001 UTC")("2007.001 UTC")("3 days").exec());
> > trans.commit();
> > -----
> >
> > When I execute this, I get the following error:
> >
> > -----
> > terminate called after throwing an instance of 'pqxx::syntax_error'
> > what(): ERROR: syntax error at or near "$1" at character 77
> >
> > Abort trap
> > -----
> >
> > I've tried variations of this, such as using ("timestamp",
> > pqxx::prepare::treat_string) for the SQL types for prepare(), but that
> > resulted in the same error.
> >
> > Can anyone explain what I'm doing wrong here? If I construct the SQL
> > manually
> > and call trans.exec(sqlString) it works fine; it's only the prepared
> > statement
> > that's causing me trouble.
> >
> > A separate question about prepared statements: can a $n arg be repeated in
> > the
> > string, and will each instance of it be replaced? In my SQL above, I really
> > want $1 and $2 to be identical. I'd like the statement to accept two args,
> > and use one of them twice. I haven't been able to test this due to the
> > problem above.
> >
> > Some system info:
> >
> > Mac OSX 10.4.8/Darwin Kernel Version 8.8.0
> > PostgreSQL 8.1 (DarwinPorts build)
> > libpq 4.1 (Part of DarwinPorts PostgreSQL 8.1 build)
> > libpqxx 2.6.9
> >
> > Thank you in advance for any help.
> >
> > --Jim
> > _______________________________________________
> > Libpqxx-general mailing list
> > [email protected]
> > http://gborg.postgresql.org/mailman/listinfo/libpqxx-general
>
> _______________________________________________
> Libpqxx-general mailing list
> [email protected]
> http://gborg.postgresql.org/mailman/listinfo/libpqxx-general
>
_______________________________________________
Libpqxx-general mailing list
[email protected]
http://gborg.postgresql.org/mailman/listinfo/libpqxx-general
