Thread: JDBC Driver Problem
Hi,
We have a query that involves 'union' and 'except' clause. When it is run on the psql database at the backend it retuns the required data. However, when it is executed using the jdbc driver it is returning less number of records. The statements after the union and except clause are not getting executed. Is this a bug in the JDBC driver?
Also a similar problem is encountered in another query that uses 'case when <> then <> else <> end' the equivalent of 'decode'. This query returns records when exceuted at the backend, but when exceuted using the JDBC driver, it retuns no records. What could be the problem? Have any of you encountered a similar problem and how do we solve it?
Regards,
Gautham.
Gautham- Supplying as much of the source code as possible will make it more likely that someone can help solve the problem. I don't recall hearing of such a problem before, so this is probably not a known issue. At the very least, copies of the queries might help so we can try something similar in our environments. -Nick -------------------------------------------------------------------------- Nick Fankhauser nickf@ontko.com Phone 1.765.935.4283 Fax 1.765.962.9788 Ray Ontko & Co. Software Consulting Services http://www.ontko.com/ -----Original Message----- From: pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of Gautham S. Rao Sent: Thursday, April 04, 2002 7:53 AM To: pgsql-jdbc@postgresql.org Subject: [JDBC] JDBC Driver Problem Hi, We have a query that involves 'union' and 'except' clause. When it is run on the psql database at the backend it retuns the required data. However, when it is executed using the jdbc driver it is returning less number of records. The statements after the union and except clause are not getting executed. Is this a bug in the JDBC driver? Also a similar problem is encountered in another query that uses 'case when <> then <> else <> end' the equivalent of 'decode'. This query returns records when exceuted at the backend, but when exceuted using the JDBC driver, it retuns no records. What could be the problem? Have any of you encountered a similar problem and how do we solve it? Regards, Gautham.
Hi Nick, Here is the query that is giving the problem, select bill_num, bill_par_num, to_char(bill_from_dt,'DD-Mon-YYYY') as bill_from_dt,to_char(BILL_TO_DT,'DD-Mon-YYYY') as BILL_TO_DT, to_char(BilL_AMT,'999999999.99') as BilL_AMT, to_char(BILL_DT,'DD-Mon-YYYY') as bill_dt, (bill_amt-paid_amt) as OUTSTANDING_AMT, to_char(BilL_Py_By_Dt,'DD-Mon-YYYY') as bill_py_by_dt,BilL_STAT,BilL_CURr_CD from((select bill_num, bill_par_num, bill_from_dt, BILL_TO_DT, BilL_AMT, coalesce(sum(pymt_amt),0)as paid_amt, BILL_DT, BilL_Py_By_Dt,BilL_STAT,BilL_CURr_CD from pc_t_payment,pc_t_bill where pymt_bill_num = bill_num and pymt_cust_id = <cust_id> group by bill_num, bill_par_num, bill_from_dt, BILL_TO_DT, bill_amt, BILL_DT, BilL_Py_By_Dt, BilL_STAT, BilL_CURr_CD) union (select bill_num, bill_par_num, bill_from_dt, BILL_TO_DT, BilL_AMT, 0 as paid_amt, BILL_DT, BilL_Py_By_Dt, BilL_STAT, BilL_CURr_CD from pc_t_bill where bill_cust_id = <cust_id> except (select bill_num, bill_par_num, bill_from_dt, BILL_TO_DT, BilL_AMT, 0 as paid_amt , BILL_DT, BilL_Py_By_Dt,BilL_STAT,BilL_CURr_CD from pc_t_payment,pc_t_bill where pymt_bill_num = bill_num and pymt_cust_id =<cust_id>))) as bill_tmp order by bill_num desc; This query returns 4 records when executed from the back end which is the desired result. But when it is executed using the JDBC driver for postgreSQL 7.1 and JDK 1.2, it returns only 3 records. This is the result of the first query excluding the union. So it appears as if only the first part of the query is working through the JDBC driver. This is the other query that is using CASE .. WHEN. Select bm_link from pc_m_banner where bm_type= <type> and (CASE WHEN bm_ctg_cd= '' THEN <val> ELSE bm_ctg_cd END) = <val> It returns one record when excuted through the backend and no records when executed through the JDBC driver. Thanks & Regards, Gautham. ----- Original Message ----- From: "Nick Fankhauser" <nickf@ontko.com> To: "Gautham S. Rao" <gautam.rao@tatainfotech.com>; <pgsql-jdbc@postgresql.org> Sent: Thursday, April 04, 2002 6:26 PM Subject: RE: [JDBC] JDBC Driver Problem > Gautham- > > Supplying as much of the source code as possible will make it more likely > that someone can help solve the problem. I don't recall hearing of such a > problem before, so this is probably not a known issue. > > At the very least, copies of the queries might help so we can try something > similar in our environments. > > -Nick > > -------------------------------------------------------------------------- > Nick Fankhauser nickf@ontko.com Phone 1.765.935.4283 Fax 1.765.962.9788 > Ray Ontko & Co. Software Consulting Services http://www.ontko.com/ > > -----Original Message----- > From: pgsql-jdbc-owner@postgresql.org > [mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of Gautham S. Rao > Sent: Thursday, April 04, 2002 7:53 AM > To: pgsql-jdbc@postgresql.org > Subject: [JDBC] JDBC Driver Problem > > > Hi, > > We have a query that involves 'union' and 'except' clause. When it is run on > the psql database at the backend it retuns the required data. However, when > it is executed using the jdbc driver it is returning less number of records. > The statements after the union and except clause are not getting executed. > Is this a bug in the JDBC driver? > > Also a similar problem is encountered in another query that uses 'case when > <> then <> else <> end' the equivalent of 'decode'. This query returns > records when exceuted at the backend, but when exceuted using the JDBC > driver, it retuns no records. What could be the problem? Have any of you > encountered a similar problem and how do we solve it? > > Regards, > Gautham. >
"Gautham S. Rao" <gautam.rao@tatainfotech.com> writes:
> This query returns 4 records when executed from the back end which is the
> desired result. But when it is executed using the JDBC driver for postgreSQL
> 7.1 and JDK 1.2, it returns only 3 records. This is the result of the first
> query excluding the union. So it appears as if only the first part of the
> query is working through the JDBC driver.
Could the driver be dropping part of the query? I suggest turning on
query logging at the backend (restart postmaster with -d2 switch, or
set debug_print_query = true in postgresql.conf) and then look in the
postmaster log to see exactly what query is being sent by JDBC.
regards, tom lane
This would be my guess too. It would make sense that the code that is checking for escape sequences is broken Dave On Fri, 2002-04-05 at 11:09, Tom Lane wrote: > "Gautham S. Rao" <gautam.rao@tatainfotech.com> writes: > > This query returns 4 records when executed from the back end which is the > > desired result. But when it is executed using the JDBC driver for postgreSQL > > 7.1 and JDK 1.2, it returns only 3 records. This is the result of the first > > query excluding the union. So it appears as if only the first part of the > > query is working through the JDBC driver. > > Could the driver be dropping part of the query? I suggest turning on > query logging at the backend (restart postmaster with -d2 switch, or > set debug_print_query = true in postgresql.conf) and then look in the > postmaster log to see exactly what query is being sent by JDBC. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > >
Hi Dave/Tom, Thanks very much for the suggestion. The problem was beacuse the paramaters in the where clause were not getting replaced with the actual values. This I could figure out from the log and have corrected the logic to handle it properly. Now the queries are working fine. Thanks & Regards, Gautham ----- Original Message ----- From: "Dave Cramer" <Dave@micro-automation.net> To: "Tom Lane" <tgl@sss.pgh.pa.us> Cc: "Gautham S. Rao" <gautam.rao@tatainfotech.com>; <pgsql-jdbc@postgresql.org> Sent: Friday, April 05, 2002 10:22 PM Subject: Re: [JDBC] JDBC Driver Problem > This would be my guess too. It would make sense that the code that is > checking for escape sequences is broken > > Dave > > On Fri, 2002-04-05 at 11:09, Tom Lane wrote: > > "Gautham S. Rao" <gautam.rao@tatainfotech.com> writes: > > > This query returns 4 records when executed from the back end which is the > > > desired result. But when it is executed using the JDBC driver for postgreSQL > > > 7.1 and JDK 1.2, it returns only 3 records. This is the result of the first > > > query excluding the union. So it appears as if only the first part of the > > > query is working through the JDBC driver. > > > > Could the driver be dropping part of the query? I suggest turning on > > query logging at the backend (restart postmaster with -d2 switch, or > > set debug_print_query = true in postgresql.conf) and then look in the > > postmaster log to see exactly what query is being sent by JDBC. > > > > regards, tom lane > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 6: Have you searched our list archives? > > > > http://archives.postgresql.org > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org >
Gautham, So what was the solution? Is the driver broken? Did you work around the problem? Dave On Mon, 2002-04-08 at 08:01, Gautham S. Rao wrote: > Hi Dave/Tom, > > Thanks very much for the suggestion. The problem was beacuse the paramaters > in the where clause were not getting replaced with the actual values. This I > could figure out from the log and have corrected the logic to handle it > properly. Now the queries are working fine. > > Thanks & Regards, > Gautham > > ----- Original Message ----- > From: "Dave Cramer" <Dave@micro-automation.net> > To: "Tom Lane" <tgl@sss.pgh.pa.us> > Cc: "Gautham S. Rao" <gautam.rao@tatainfotech.com>; > <pgsql-jdbc@postgresql.org> > Sent: Friday, April 05, 2002 10:22 PM > Subject: Re: [JDBC] JDBC Driver Problem > > > > This would be my guess too. It would make sense that the code that is > > checking for escape sequences is broken > > > > Dave > > > > On Fri, 2002-04-05 at 11:09, Tom Lane wrote: > > > "Gautham S. Rao" <gautam.rao@tatainfotech.com> writes: > > > > This query returns 4 records when executed from the back end which is > the > > > > desired result. But when it is executed using the JDBC driver for > postgreSQL > > > > 7.1 and JDK 1.2, it returns only 3 records. This is the result of the > first > > > > query excluding the union. So it appears as if only the first part of > the > > > > query is working through the JDBC driver. > > > > > > Could the driver be dropping part of the query? I suggest turning on > > > query logging at the backend (restart postmaster with -d2 switch, or > > > set debug_print_query = true in postgresql.conf) and then look in the > > > postmaster log to see exactly what query is being sent by JDBC. > > > > > > regards, tom lane > > > > > > ---------------------------(end of broadcast)--------------------------- > > > TIP 6: Have you searched our list archives? > > > > > > http://archives.postgresql.org > > > > > > > > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 6: Have you searched our list archives? > > > > http://archives.postgresql.org > > > >
Hi Dave, The problem was not beacuse of the JDBC driver at all. In the query we had some parameters in the where clause that had to be replaced with the actual values during runtime. This piece of code was written in Java and the replacement logic was not handled properly. Only the first occurance of the parameter was getting replaced. The logic has now been changed to handle such situations also. Regards, Gautham. ----- Original Message ----- From: "Dave Cramer" <Dave@micro-automation.net> To: "Gautham S. Rao" <gautam.rao@tatainfotech.com> Cc: "Tom Lane" <tgl@sss.pgh.pa.us>; <pgsql-jdbc@postgresql.org> Sent: Tuesday, April 09, 2002 5:31 AM Subject: Re: [JDBC] JDBC Driver Problem > Gautham, > > So what was the solution? Is the driver broken? Did you work around the > problem? > > Dave > On Mon, 2002-04-08 at 08:01, Gautham S. Rao wrote: > > Hi Dave/Tom, > > > > Thanks very much for the suggestion. The problem was beacuse the paramaters > > in the where clause were not getting replaced with the actual values. This I > > could figure out from the log and have corrected the logic to handle it > > properly. Now the queries are working fine. > > > > Thanks & Regards, > > Gautham > > > > ----- Original Message ----- > > From: "Dave Cramer" <Dave@micro-automation.net> > > To: "Tom Lane" <tgl@sss.pgh.pa.us> > > Cc: "Gautham S. Rao" <gautam.rao@tatainfotech.com>; > > <pgsql-jdbc@postgresql.org> > > Sent: Friday, April 05, 2002 10:22 PM > > Subject: Re: [JDBC] JDBC Driver Problem > > > > > > > This would be my guess too. It would make sense that the code that is > > > checking for escape sequences is broken > > > > > > Dave > > > > > > On Fri, 2002-04-05 at 11:09, Tom Lane wrote: > > > > "Gautham S. Rao" <gautam.rao@tatainfotech.com> writes: > > > > > This query returns 4 records when executed from the back end which is > > the > > > > > desired result. But when it is executed using the JDBC driver for > > postgreSQL > > > > > 7.1 and JDK 1.2, it returns only 3 records. This is the result of the > > first > > > > > query excluding the union. So it appears as if only the first part of > > the > > > > > query is working through the JDBC driver. > > > > > > > > Could the driver be dropping part of the query? I suggest turning on > > > > query logging at the backend (restart postmaster with -d2 switch, or > > > > set debug_print_query = true in postgresql.conf) and then look in the > > > > postmaster log to see exactly what query is being sent by JDBC. > > > > > > > > regards, tom lane > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > > > TIP 6: Have you searched our list archives? > > > > > > > > http://archives.postgresql.org > > > > > > > > > > > > > > > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > > TIP 6: Have you searched our list archives? > > > > > > http://archives.postgresql.org > > > > > > > > >