Thread: upgrade problem with driver postgresql-8.1-407.jdbc3.jar
Hi all, I'm running into this problem when upgrading my jdbc driver from pg74.216.jdbc3.jar to postgresql-8.1-407.jdbc3.jar I started having syntax error with the new driver and cannot find where is the problem. Here is the log: 2006-09-06 09:48:20 LOG: statement: PREPARE <unnamed> AS COPY vd (VDVSSRC, VDVSNUM, VDKEY, VDDATA) FROM $1 2006-09-06 09:48:20 ERROR: syntax error at or near "$1" at character 66 2006-09-06 09:48:20 STATEMENT: COPY vd (VDVSSRC, VDVSNUM, VDKEY, VDDATA) FROM $1 Here is the VARCHAR given for $1: c:\\temp\\webCatalog\\20060517030156.zip20060517030156.zip20060517030156.zip If I try the corresponding command in pgAdmin3 it works well... Any idea what is the error? Which I suspect is quite evident .. :-/ Thanks for the help! Best Regards /David
David, The driver has no idea how to deal with a $, It also uses prepared statements if you use the preparedStatement interface. Dave On 6-Sep-06, at 10:00 AM, David Gagnon wrote: > Hi all, > > I'm running into this problem when upgrading my jdbc driver from > pg74.216.jdbc3.jar to postgresql-8.1-407.jdbc3.jar > > I started having syntax error with the new driver and cannot find > where is the problem. > Here is the log: > > > 2006-09-06 09:48:20 LOG: statement: PREPARE <unnamed> AS > COPY vd (VDVSSRC, VDVSNUM, VDKEY, VDDATA) FROM $1 > 2006-09-06 09:48:20 ERROR: syntax error at or near "$1" at > character 66 > 2006-09-06 09:48:20 STATEMENT: COPY vd (VDVSSRC, > VDVSNUM, VDKEY, VDDATA) FROM $1 > Here is the VARCHAR given for $1: > c:\\temp\\webCatalog\ > \20060517030156.zip20060517030156.zip20060517030156.zip > > If I try the corresponding command in pgAdmin3 it works well... > > Any idea what is the error? Which I suspect is quite evident .. :-/ > > Thanks for the help! > Best Regards > /David > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org >
David Gagnon wrote: > I started having syntax error with the new driver and cannot find where > is the problem. > Here is the log: > 2006-09-06 09:48:20 LOG: statement: PREPARE <unnamed> AS COPY > vd (VDVSSRC, VDVSNUM, VDKEY, VDDATA) FROM $1 Unfortunately that means you are using a ? placeholder in a location where the server-side SQL grammar doesn't allow a parameter. You will have to construct the query by hand yourself rather than using ?. -O
Is that a bug then since this way working with the 7.4 jdbc driver ? Any idea why it stops working ? Thanks for the help! Regards /David Oliver Jowett wrote: > David Gagnon wrote: > >> I started having syntax error with the new driver and cannot find >> where is the problem. Here is the log: > >> 2006-09-06 09:48:20 LOG: statement: PREPARE <unnamed> AS >> COPY vd (VDVSSRC, VDVSNUM, VDKEY, VDDATA) FROM $1 > > Unfortunately that means you are using a ? placeholder in a location > where the server-side SQL grammar doesn't allow a parameter. You will > have to construct the query by hand yourself rather than using ?. > > -O > > > > . >
David Gagnon wrote: > Is that a bug then since this way working with the 7.4 jdbc driver ? It's not really a bug, it's that the driver is now more picky about where parameters may be placed in the query. You were never really meant to put parameters just anywhere in a query, it just happened to work in the past. > Any idea why it stops working ? The older driver you used did literal substitution of parameter values into the query. Newer drivers turn ? placeholders into $n placeholders and pass the actual parameter values separately to the query string. So they only allow ? placeholders where a $n placeholder is accepted by the server. -O