Thread: JDBC Driver Problem

JDBC Driver Problem

From
"Gautham S. Rao"
Date:
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.

Re: JDBC Driver Problem

From
"Nick Fankhauser"
Date:
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.


Re: JDBC Driver Problem

From
"Gautham S. Rao"
Date:
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.
>


Re: JDBC Driver Problem

From
Tom Lane
Date:
"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

Re: JDBC Driver Problem

From
Dave Cramer
Date:
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
>
>




Re: JDBC Driver Problem

From
"Gautham S. Rao"
Date:
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
>


Re: JDBC Driver Problem

From
Dave Cramer
Date:
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
> >
>
>



Re: JDBC Driver Problem

From
"Gautham S. Rao"
Date:
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
> > >
> >
> >
>
>