PostgreSQL JDBC - JDBC, PrepareStatement and TimeStamp problem

Author

JDBC, PrepareStatement and TimeStamp problem

Jeffrey Leegon

2006-01-10, 8:24 pm

I get an error with the timestamps when running the following code:
(Varibbles currentTrainStart and stop are GregorianCalendars)
String tableName = "\"AdultData\"";

String tempSql = "SELECT
\"Age\",\"Gender\",\"PrimaryLanguage\",\"TimeOfDay\",\"DayOfWeek\",\"ChiefComplaint\",\"BodyFluidIso\",\"RespIso\",\"Acuity\",\"Consult\",\"Labs\",\"Radiology\",";
tempSql+= "\"EKG\",\"MOA\",\"Admitted\" FROM ? ";
tempSql +="WHERE \"DATE_ADMITTED\">= ? AND
\"DATE_ADMITTED\"< ? ;";
query = conn. prepareStatement(tem
pSql);

query.setString(1, tableName);
Timestamp startTrain = new
Timestamp(currentTra
inStart.getTimeInMillis());
query. setTimestamp(2,start
Train);
Timestamp stopTrain = new Timestamp(stop.getTimeInMillis());
query.setTimestamp(3, stopTrain);

System.out.println(query.toString());

query.executeQuery();
// query.clearParameters();

When I execute the following code I get the error:

java.sql.SQLException: ERROR: syntax error at or near "$1"
at
org.postgresql.core.v3.QueryExecutorImpl. receiveErrorResponse
(QueryExecutorImpl.java:1482)
at
org.postgresql.core.v3.QueryExecutorImpl. processResults(Query
ExecutorImpl.java:1267)
at
org.postgresql.core.v3.QueryExecutorImpl. execute(QueryExecuto
rImpl.java:186)
at
org.postgresql.jdbc2. AbstractJdbc2Stateme
nt. execute(AbstractJdbc
2Statement.java:392)
at
org.postgresql.jdbc2. AbstractJdbc2Stateme
nt. executeWithFlags(Abs
tractJdbc2Statement.java:330)
at
org.postgresql.jdbc2. AbstractJdbc2Stateme
nt. executeQuery(Abstrac
tJdbc2Statement.java:240)
at TemporalDataSetCreat
or. getNextDataSet(Tempo
ralDataSetCreator.java:180)
at SequentialBNTest. main(SequentialBNTes
t.java:31)

After initiating the places holders in the prepared statement and call
toString() I get the following line going to the DB:

SELECT
"Age","Gender","PrimaryLanguage","TimeOfDay","DayOfWeek","ChiefComplaint","BodyFluidIso","RespIso","Acuity","Consult","Labs","Radiology","EKG","MOA","Admitted"
FROM "AdultData" WHERE "DATE_ADMITTED">= 2004-05-10 00:00:00.000000+0100
AND "DATE_ADMITTED"< 2004-05-31 00:00:00.000000+0100

Which if I put in pgAdmin III and add single quotes around the
Timestamps if runs fine. If I add \' around the ? in the prepared
statement it gives the ? to the DB and throws and error. What am I doing
wrong?

Thanks,
Jeff

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Kris Jurka

2006-01-10, 8:24 pm

On Tue, 10 Jan 2006, Jeffrey Leegon wrote:

> String tableName = "\"AdultData\"";
> String tempSql = "SELECT [some stuff] FROM ? WHERE [some conditions]";
> query = conn. prepareStatement(tem
pSql);
> query.setString(1, tableName);
>
> java.sql.SQLException: ERROR: syntax error at or near "$1"
> at

You cannot use placeholders for stuctural parts of your query, only
parameters. The server cannot create a prepared plan if it doesn't even
know what table you are operating on.

Kris Jurka

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Sponsored Links