Re: odd behaviour of prepared statement - Mailing list pgsql-jdbc

<: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