Re: odd behaviour of prepared statement

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
>