Re: JDBC parse error - Mailing list pgsql-jdbc

John,

The only thing that will help solve the problem is going to be a
reproducable test case.  Can you come up with a small test case that
shows the problem?   If you can't, then you may need to run a java
debugger (or print statements) on your app to see what is going on.

In your output below I only see this one failed query:

SELEC.1 2, name, description, type_id FROM education_degree
ORDER BY name

I don't see a successful version of this query.  What would this query
look like if it hadn't been corrupted?  Also any idea where in your app
data like '.1 2' might be created?  Is this a PreparedStatement query or
a regular Statement?

thanks,
--Barry

John Metz wrote:

 >Barry,
 >
 >You SA upgraded us to PostGres 7.2.1, we had been using 7.1.2, and we are
 >able to recreate the problem.  We are using the JDBC driver that came with
 >the PostGres 7.2.1 distribution.  We are running under Java 1.3.1_01 on
 >Solaris 8.  We did turn debugging on and the output is below.  I
included a
 >successful query executed before the one that failed.  I also included the
 >output from a psql -V command.  I was able to run the exact same operation
 >minutes before and it worked.  Also, I can execute the query (as it is in
 >our Java code) in psql and it works. Please let me know if there's
anything
 >else I can do to help you find the problem.
 >
 >bash-2.03$ psql -V
 >psql (PostgreSQL) 7.2.1
 >Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group
 >Portions Copyright (c) 1996, Regents of the University of California
 >Read the file COPYRIGHT or use the command \copyright to see the
 >usage and distribution terms.
 >
 >--- DEBUG log ---
 >
 >DEBUG: ProcessQuery
 >DEBUG: CommitTransactionCommand
 >DEBUG: StartTransactionCommand
 >DEBUG: query: SELECT id, name FROM clearance ORDER BY name
 >DEBUG: parse tree: { QUERY :command 1 :utility <> :resultRelation 0
:into <>
 >:
 >isPortal false :isBinary false :isTemp false :hasAggs false :hasSubLinks
 >false :
 >rtable ({ RTE :relname clearance :relid 24755 :subquery <> :alias <> :eref
 >{ AT
 >TR :relname clearance :attrs ( "id" "name" )} :inh true :inFromCl true
 >:checkF
 >orRead true :checkForWrite false :checkAsUser 0}) :jointree { FROMEXPR
 >:fromlist
 >({ RANGETBLREF 1 }) :quals <>} :rowMarks () :targetList ({ TARGETENTRY
 >:resdom
 >{ RESDOM :resno 1 :restype 23 :restypmod -1 :resname id :reskey 0
:reskeyop
 >0 :r
 >essortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 1
:vartype
 >23 :
 >vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 1}} { TARGETENTRY
:resdom
 >{
 >RESDOM :resno 2 :restype 1043 :restypmod 34 :resname name :reskey 0
 >:reskeyop 0
 >:ressortgroupref 1 :resjunk false } :expr { VAR :varno 1 :varattno 2
 >:vartype 10
 >43 :vartypmod 34 :varlevelsup 0 :varnoold 1 :varoattno 2}})
:groupClause <>
 >:ha
 >vingQual <> :distinctClause <> :sortClause ({ SORTCLAUSE
:tleSortGroupRef 1
 >:sor
 >top 1066 }) :limitOffset <> :limitCount <> :setOperations <>
 >:resultRelations ()
 >}
 >DEBUG: rewritten parse tree:
 >DEBUG: { QUERY :command 1 :utility <> :resultRelation 0 :into <> :isPortal
 >fal
 >se :isBinary false :isTemp false :hasAggs false :hasSubLinks false :rtable
 >({ RT
 >E :relname clearance :relid 24755 :subquery <> :alias <> :eref { ATTR
 >:relname
 >clearance :attrs ( "id" "name" )} :inh true :inFromCl true :checkForRead
 >true
 >:checkForWrite false :checkAsUser 0}) :jointree { FROMEXPR :fromlist ({
 >RANGETBL
 >REF 1 }) :quals <>} :rowMarks () :targetList ({ TARGETENTRY :resdom {
RESDOM
 >:re
 >sno 1 :restype 23 :restypmod -1 :resname id :reskey 0 :reskeyop 0
 >:ressortgroupr
 >ef 0 :resjunk false } :expr { VAR :varno 1 :varattno 1 :vartype 23
 >:vartypmod -1
 >:varlevelsup 0 :varnoold 1 :varoattno 1}} { TARGETENTRY :resdom { RESDOM
 >:resn
 >o 2 :restype 1043 :restypmod 34 :resname name :reskey 0 :reskeyop 0
 >:ressortgrou
 >pref 1 :resjunk false } :expr { VAR :varno 1 :varattno 2 :vartype 1043
 >:vartypmo
 >d 34 :varlevelsup 0 :varnoold 1 :varoattno 2}}) :groupClause <>
:havingQual
 ><>
 >:distinctClause <> :sortClause ({ SORTCLAUSE :tleSortGroupRef 1 :sortop
 >1066 })
 >:limitOffset <> :limitCount <> :setOperations <> :resultRelations ()}
 >DEBUG: plan: { SORT :startup_cost 1.03 :total_cost 1.03 :rows 2 :width 37
 >:qpta
 >rgetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 23
:restypmod -1
 >:res
 >name id :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr {
 >VAR :v
 >arno 1 :varattno 1 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1
 >:varoat
 >tno 1}} { TARGETENTRY :resdom { RESDOM :resno 2 :restype 1043
:restypmod 34
 >:res
 >name name :reskey 1 :reskeyop 1066 :ressortgroupref 1 :resjunk false }
:expr
 >{ V
 >AR :varno 1 :varattno 2 :vartype 1043 :vartypmod 34 :varlevelsup 0
:varnoold
 >1
 >:varoattno 2}}) :qpqual <> :lefttree { SEQSCAN :startup_cost 0.00
 >:total_cost 1.
 >02 :rows 2 :width 37 :qptargetlist ({ TARGETENTRY :resdom { RESDOM
:resno 1
 >:res
 >type 23 :restypmod -1 :resname id :reskey 0 :reskeyop 0 :ressortgroupref 0
 >:resj
 >unk false } :expr { VAR :varno 1 :varattno 1 :vartype 23 :vartypmod -1
 >:varleve
 >lsup 0 :varnoold 1 :varoattno 1}} { TARGETENTRY :resdom { RESDOM :resno 2
 >:resty
 >pe 1043 :restypmod 34 :resname name :reskey 0 :reskeyop 0
:ressortgroupref 1
 >:re
 >sjunk false } :expr { VAR :varno 1 :varattno 2 :vartype 1043 :vartypmod 34
 >:var
 >levelsup 0 :varnoold 1 :varoattno 2}}) :qpqual <> :lefttree <>
:righttree <>
 >:ex
 >tprm () :locprm () :initplan <> :nprm 0 :scanrelid 1 } :righttree <>
:extprm
 >()
 >:locprm () :initplan <> :nprm 0 :keycount 1 }
 >DEBUG: ProcessQuery
 >DEBUG: CommitTransactionCommand
 >DEBUG: StartTransactionCommand
 >DEBUG: query: SELEC.1 2, name, description, type_id FROM education_degree
 >ORDER
 >BY name
 >ERROR: parser: parse error at or near "selec"
 >DEBUG: AbortCurrentTransaction
 >DEBUG: proc_exit(0)
 >DEBUG: shmem_exit(0)
 >DEBUG: exit(0)
 >DEBUG: reaping dead processes
 >DEBUG: child process (pid 23821) exited with exit code 0
 >
 >----- Original Message -----
 >From: "Barry Lind" <barry@xythos.com>
 >To: "John Metz" <johnmetz@excite.com>
 >Cc: <pgsql-jdbc@postgresql.org>
 >Sent: Tuesday, June 18, 2002 2:00 PM
 >Subject: Re: [JDBC] JDBC parse error
 >
 >
 >
 >
 >>John,
 >>
 >>Can you turn on query logging on the server to see what the actual
 >>queries sent from the jdbc driver to the server actually are?  That
 >>might give some indication of where the problem is.  Finally are you
 >>sure that your own code isn't munging the string before it is passed to
 >>the jdbc driver?  I haven't heard of anyone else seeing this problem.
 >>  We need to track down what is different in your environment that is
 >>causing it.  Also if you would mention what version of the server and
 >>jdbc driver you are using, that would be helpful.
 >>
 >>thanks,
 >>--Barry
 >>
 >>John Metz wrote:
 >>
 >> >Hi,
 >> >
 >> >I'm having an intermittent problem with the PostGres JDBC Driver I
 >>recently
 >> >downloaded from http://jdbc.postgresql.org/download.html.  I am running
 >>
 >>
 >a
 >
 >
 >> >Java Servlet application under Tomcat 4.0  and Solaris 8.  On some
 >>occasions
 >> >it seems that the String passed to a Statement object gets altered when
 >> >executed. I get a SQLException with the text "parse error at or near
 >>selec".
 >> >It always seems to happen on a select.  On some occasions it works but
 >>
 >>
 >it
 >
 >
 >> >seems to happen on some queries more than others.  The text in the
parse
 >> >error is different at times for the same query (another example was
 >> >selecaria).  Here is an example stack trace:
 >> >
 >> >java.sql.SQLException: ERROR:  parser: parse error at or near "selec"
 >> >
 >> > at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:94)
 >> > at org.postgresql.Connection.ExecSQL(Connection.java:398)
 >> > at org.postgresql.jdbc2.Statement.execute(Statement.java:130)
 >> > at org.postgresql.jdbc2.Statement.executeQuery(Statement.java:54)
 >> > at
 >>
 >>org.postgresql.jdbc2.PreparedStatement.executeQuery(PreparedStatement.java:
 >>
 >>
 >9
 >
 >
 >> >9)
 >> > at
 >>
 >>com.conquestnet.database.entity.EducationDegree.getAllEducationDegrees(Educ
 >>
 >>
 >a
 >
 >
 >> >tionDegree.java:211)
 >> > at org.apache.jsp.edit$jsp._jspService(edit$jsp.java:218)
 >> > at org.apache.jasper.runtime.HttpJspBase.service(Unknown Source)
 >> > at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
 >> > at
 >>
 >>
 >org.apache.jasper.servlet.JspServlet$JspServletWrapper.service(Unknown
 >
 >
 >> >Source)
 >> > at org.apache.jasper.servlet.JspServlet.serviceJspFile(Unknown Source)
 >> > at org.apache.jasper.servlet.JspServlet.service(Unknown Source)
 >> >
 >> >Any help that can be provided is much appriciated.  I've searched the
 >>
 >>
 >FAQ
 >
 >
 >> >but could not find anything that seemed like the same problem.
 >> >
 >> >Thanks,
 >> >
 >> >John Metz
 >> >
 >> >
 >> >---------------------------(end of
broadcast)---------------------------
 >> >TIP 4: Don't 'kill -9' the postmaster
 >> >
 >> >
 >> >
 >>
 >>
 >>
 >>
 >>
 >>
 >
 >
 >
 >