Обсуждение: odd behaviour of prepared statement
this piece of code is the second PreparedStatement in a bean function.
All code before it seems to work perfectly. no amount of tinkering
with this one will avoid an SQLException "parser: parse error at end
of input" on the executeQuery();
The code executes fine in psql.
db is 7.3.2. java is sun hotspot 1.41.
Any ideas why the code fails parsing?
<code>
String strSQL;
String strSQL_SalesAndReturns;
PreparedStatement pstmtSR = null;
ResultSet results = null;
String strStockid=null;
String strGroup=null;
String strDesc=null;
String strType=null;
int iSales=0;
int iReturns=0;
String strSQL_SalesAndReturns =
"SELECT stock, " +
" p.description, " +
" p.stockgroup, " +
" s.saleqty, " +
" r.returned " +
" FROM " +
" (SELECT stockid as stock, " +
" sum(saleqty) as saleqty " +
" FROM dailysales s " +
" WHERE s.saledate = '2003-08-22' " +
" AND s.custid = ' 5' " +
" GROUP BY s.stockid ) AS s " +
" FULL OUTER JOIN " +
" (SELECT stock, " +
" ordercurrent as returned " +
" FROM orderlines ol " +
" WHERE ol.theorder = 'A 5 12003-08-15' " +
" AND ol.TYPE='R') as r " +
" USING (stock) " +
" INNER JOIN stockitems p ON (p.id=r.stock) " +
" WHERE p.status='N' " +
" ORDER BY ";
strSQL = strSQL_SalesAndReturns;
pstmtSR = livedb.prepareStatement(strSQL);
// global character switch for grouping
switch(cSelectOrder) {
case cDESCRIPTION_SEQ : strSQL = strSQL +
"p.description;";
break;
case cGROUP_SEQ : strSQL = strSQL +
"p.stockgroup,p.description;";
break;
case cCODE_SEQ : strSQL = strSQL + "stock;";
break;
default : strSQL = strSQL +
"p.description;";
break;
}
results = pstmtSR.executeQuery();
while( results.next() ) {
strStockid = results.getString(1);
strDesc = results.getString(2);
strGroup = results.getString(3);
iSales = results.getInt(4);
iReturns = results.getInt(5);
... code to process resultset.
</code>
Kevin-
It looks like you're preparing the statement before you tack on the last
piece after the order by, so the parser just sees "Order By" at the end of
the string with no fields following it.
BTW, since you're not passing any parameters in, it seems like you might as
well just execute this statement straight rather than going through
preparing it.
-Nick
> -----Original Message-----
> From: pgsql-jdbc-owner@postgresql.org
> [mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of kevin
> Sent: Monday, August 18, 2003 10:40 AM
> To: pgsql-jdbc@postgresql.org
> Subject: [JDBC] odd behaviour of prepared statement
>
>
> this piece of code is the second PreparedStatement in a bean function.
> All code before it seems to work perfectly. no amount of tinkering
> with this one will avoid an SQLException "parser: parse error at end
> of input" on the executeQuery();
> The code executes fine in psql.
> db is 7.3.2. java is sun hotspot 1.41.
>
> Any ideas why the code fails parsing?
>
> <code>
> String strSQL;
> String strSQL_SalesAndReturns;
> PreparedStatement pstmtSR = null;
> ResultSet results = null;
>
> String strStockid=null;
> String strGroup=null;
> String strDesc=null;
> String strType=null;
> int iSales=0;
> int iReturns=0;
>
> String strSQL_SalesAndReturns =
> "SELECT stock, " +
> " p.description, " +
> " p.stockgroup, " +
> " s.saleqty, " +
> " r.returned " +
> " FROM " +
> " (SELECT stockid as stock, " +
> " sum(saleqty) as saleqty " +
> " FROM dailysales s " +
> " WHERE s.saledate = '2003-08-22' " +
> " AND s.custid = ' 5' " +
> " GROUP BY s.stockid ) AS s " +
> " FULL OUTER JOIN " +
> " (SELECT stock, " +
> " ordercurrent as returned " +
> " FROM orderlines ol " +
> " WHERE ol.theorder = 'A 5 12003-08-15' " +
> " AND ol.TYPE='R') as r " +
> " USING (stock) " +
> " INNER JOIN stockitems p ON (p.id=r.stock) " +
> " WHERE p.status='N' " +
> " ORDER BY ";
>
> strSQL = strSQL_SalesAndReturns;
> pstmtSR = livedb.prepareStatement(strSQL);
>
> // global character switch for grouping
> switch(cSelectOrder) {
> case cDESCRIPTION_SEQ : strSQL = strSQL +
> "p.description;";
> break;
> case cGROUP_SEQ : strSQL = strSQL +
> "p.stockgroup,p.description;";
> break;
> case cCODE_SEQ : strSQL = strSQL + "stock;";
> break;
> default : strSQL = strSQL +
> "p.description;";
> break;
> }
>
> results = pstmtSR.executeQuery();
>
> while( results.next() ) {
> strStockid = results.getString(1);
> strDesc = results.getString(2);
> strGroup = results.getString(3);
> iSales = results.getInt(4);
> iReturns = results.getInt(5);
> ... code to process resultset.
> </code>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
>
Kevin,
Turn on sql statement tracing on the server. This will then log the
exact sql statement the server is complaining about. That should help
you track down the problem.
thanks,
--Barry
kevin wrote:
> this piece of code is the second PreparedStatement in a bean function.
> All code before it seems to work perfectly. no amount of tinkering
> with this one will avoid an SQLException "parser: parse error at end
> of input" on the executeQuery();
> The code executes fine in psql.
> db is 7.3.2. java is sun hotspot 1.41.
>
> Any ideas why the code fails parsing?
>
> <code>
> String strSQL;
> String strSQL_SalesAndReturns;
> PreparedStatement pstmtSR = null;
> ResultSet results = null;
>
> String strStockid=null;
> String strGroup=null;
> String strDesc=null;
> String strType=null;
> int iSales=0;
> int iReturns=0;
>
> String strSQL_SalesAndReturns =
> "SELECT stock, " +
> " p.description, " +
> " p.stockgroup, " +
> " s.saleqty, " +
> " r.returned " +
> " FROM " +
> " (SELECT stockid as stock, " +
> " sum(saleqty) as saleqty " +
> " FROM dailysales s " +
> " WHERE s.saledate = '2003-08-22' " +
> " AND s.custid = ' 5' " +
> " GROUP BY s.stockid ) AS s " +
> " FULL OUTER JOIN " +
> " (SELECT stock, " +
> " ordercurrent as returned " +
> " FROM orderlines ol " +
> " WHERE ol.theorder = 'A 5 12003-08-15' " +
> " AND ol.TYPE='R') as r " +
> " USING (stock) " +
> " INNER JOIN stockitems p ON (p.id=r.stock) " +
> " WHERE p.status='N' " +
> " ORDER BY ";
>
> strSQL = strSQL_SalesAndReturns;
> pstmtSR = livedb.prepareStatement(strSQL);
>
> // global character switch for grouping
> switch(cSelectOrder) {
> case cDESCRIPTION_SEQ : strSQL = strSQL +
> "p.description;";
> break;
> case cGROUP_SEQ : strSQL = strSQL +
> "p.stockgroup,p.description;";
> break;
> case cCODE_SEQ : strSQL = strSQL + "stock;";
> break;
> default : strSQL = strSQL +
> "p.description;";
> break;
> }
>
> results = pstmtSR.executeQuery();
>
> while( results.next() ) {
> strStockid = results.getString(1);
> strDesc = results.getString(2);
> strGroup = results.getString(3);
> iSales = results.getInt(4);
> iReturns = results.getInt(5);
> ... code to process resultset.
> </code>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
>
<:oops:>
couln't see it because it was under my nose. the statements
(everywhere else) have a standard layout with ? for data items. the
example was my hard coded version to find the bug. the last part is
the dynamic ordering to allow user choice. simply moving the prepare
after the addition of the ordering and (suprise, suprise) it works
first time.
OK call me wooden and rectangular.
thanks,
kev.
nickf@ontko.com ("Nick Fankhauser") wrote in message news:<NEBBLAAHGLEEPCGOBHDGGEHLIAAA.nickf@ontko.com>...
> Kevin-
>
> It looks like you're preparing the statement before you tack on the last
> piece after the order by, so the parser just sees "Order By" at the end of
> the string with no fields following it.
>
> BTW, since you're not passing any parameters in, it seems like you might as
> well just execute this statement straight rather than going through
> preparing it.
>
> -Nick
>
> > -----Original Message-----
> > From: pgsql-jdbc-owner@postgresql.org
> > [mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of kevin
> > Sent: Monday, August 18, 2003 10:40 AM
> > To: pgsql-jdbc@postgresql.org
> > Subject: [JDBC] odd behaviour of prepared statement
> >
> >
> > this piece of code is the second PreparedStatement in a bean function.
> > All code before it seems to work perfectly. no amount of tinkering
> > with this one will avoid an SQLException "parser: parse error at end
> > of input" on the executeQuery();
> > The code executes fine in psql.
> > db is 7.3.2. java is sun hotspot 1.41.
> >
> > Any ideas why the code fails parsing?
> >
> > <code>
> > String strSQL;
> > String strSQL_SalesAndReturns;
> > PreparedStatement pstmtSR = null;
> > ResultSet results = null;
> >
> > String strStockid=null;
> > String strGroup=null;
> > String strDesc=null;
> > String strType=null;
> > int iSales=0;
> > int iReturns=0;
> >
> > String strSQL_SalesAndReturns =
> > "SELECT stock, " +
> > " p.description, " +
> > " p.stockgroup, " +
> > " s.saleqty, " +
> > " r.returned " +
> > " FROM " +
> > " (SELECT stockid as stock, " +
> > " sum(saleqty) as saleqty " +
> > " FROM dailysales s " +
> > " WHERE s.saledate = '2003-08-22' " +
> > " AND s.custid = ' 5' " +
> > " GROUP BY s.stockid ) AS s " +
> > " FULL OUTER JOIN " +
> > " (SELECT stock, " +
> > " ordercurrent as returned " +
> > " FROM orderlines ol " +
> > " WHERE ol.theorder = 'A 5 12003-08-15' " +
> > " AND ol.TYPE='R') as r " +
> > " USING (stock) " +
> > " INNER JOIN stockitems p ON (p.id=r.stock) " +
> > " WHERE p.status='N' " +
> > " ORDER BY ";
> >
> > strSQL = strSQL_SalesAndReturns;
> > pstmtSR = livedb.prepareStatement(strSQL);
> >
> > // global character switch for grouping
> > switch(cSelectOrder) {
> > case cDESCRIPTION_SEQ : strSQL = strSQL +
> > "p.description;";
> > break;
> > case cGROUP_SEQ : strSQL = strSQL +
> > "p.stockgroup,p.description;";
> > break;
> > case cCODE_SEQ : strSQL = strSQL + "stock;";
> > break;
> > default : strSQL = strSQL +
> > "p.description;";
> > break;
> > }
> >
> > results = pstmtSR.executeQuery();
> >
> > while( results.next() ) {
> > strStockid = results.getString(1);
> > strDesc = results.getString(2);
> > strGroup = results.getString(3);
> > iSales = results.getInt(4);
> > iReturns = results.getInt(5);
> > ... code to process resultset.
> > </code>
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 9: the planner will ignore your desire to choose an index scan if your
> > joining column's datatypes do not match
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html