Hi Jim,
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"?
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
