From: | Barry Lind <blind(at)xythos(dot)com> |
---|---|
To: | kevin <kevin(at)mtel(dot)co(dot)uk> |
Cc: | pgsql-jdbc(at)postgresql(dot)org |
Subject: | Re: odd behaviour of prepared statement |
Date: | 2003-08-19 18:25:58 |
Message-ID: | 3F426BB6.30306@xythos.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-jdbc |
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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Waldomiro | 2003-08-20 12:18:24 | FETCH and MOVE |
Previous Message | Dave Cramer | 2003-08-19 12:04:34 | Re: Problem Postgres 6.3 and timestamp/date |