Re: odd behaviour of prepared statement

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(at)postgresql(dot)org
> [mailto:pgsql-jdbc-owner(at)postgresql(dot)org]On Behalf Of kevin
> Sent: Monday, August 18, 2003 10:40 AM
> To: pgsql-jdbc(at)postgresql(dot)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
>