Board index » delphi » 2nd Post: Query problem on Oracle

2nd Post: Query problem on Oracle

Hi,

I develop on D5Ent and am now facing a strange problem with querying my
Oracle 8.1.5 DB. A simple query produces problems on fields from the first
row that is returned. Subsequent rows are OK.
The same problem occurs also in SQL-Explorer.
My BDECfg:
Oracle Native driver,Batch count 200,Blob size 32,Blobs to cache 64,enable
BCD False,Enable integers True,Enable Schema cache false,Langdriver,
list synonyms none,max rows -1,Net protocol TNS,Object Mode True,Open Mode
Read/write,Rowset Size 20,Sqlpasstroughmode    NOT SHARED,
Sqlqrymode SERVER,DLL    SQLD_ORA.DLL,DLL32    SQLORA8.DLL,Vendor init
OCI.DLL,Trace mode    0

When I run the same query with Oracle product SQL Plus I have no problem at
all, so it seems that this is a bug in BDE or Oracle SQL driver.

DB-table ADT_VISIT (Primary key constraint on first 3 fields, not null
constraint on 4th field)
VISIT_ID        CHAR(20) NOT NULL
ADM_DATE    DATE NOT NULL
ADM_TIME    DATE NOT NULL
PAT_ID         CHAR(12) NOT NULL
DIS_DATE     DATE
DIS_TIME        DATE

Query :    Select VISIT_ID, ADM_DATE, ADM_TIME, DIS_DATE, DIS_TIME from
ADT_VISIT where PAT_ID = '6205291000  ';

On Oracle SQL Plus 3 rows are returned:
VISIT_ID    ADM_DATE    ADM_TIME    DIS_DATE    DIS_TIME
10102000    10/02/2000        10:00                12/02/2000    15:00
10110122    15/06/2000        08:00                30/06/2000    20:00
40102500    03/10/2000        15:00                NULL            NULL

In SQL Explorer this is the result:
VISIT_ID    ADM_DATE    ADM_TIME    DIS_DATE    DIS_TIME
10102000    10/02/2000        10:00
10110122    15/06/2000        08:00                30/06/2000    20:00
40102500    03/10/2000        15:00

As you can see the values for DIS_DATE and DIS_TIME in the first row
returned are not correct.
However when I add the clause: "and DIS_DATE is not null" to my query I get
this result:
VISIT_ID    ADM_DATE    ADM_TIME    DIS_DATE    DIS_TIME
10102000    10/02/2000        10:00                12/02/2000    15:00
10110122    15/06/2000        08:00                30/06/2000    20:00

Values are now there but again this is not what I want because the third row
is now not included.

Has anyone faced this problem before and is there a solution or workaround ?

Thanks in advance for your answers !

Francky Deleu
IT-Manager
RZ Jan Yperman
Briekestraat 12
8900 Ieper
Belgium

 

Re:2nd Post: Query problem on Oracle


Try setting Rowset Size to zero.

Good Luck
Eric

Quote
"Francky Deleu" <francky.de...@yperman.org> wrote in message

news:3c603016$1_2@dnews...
Quote
> Hi,

> I develop on D5Ent and am now facing a strange problem with querying my
> Oracle 8.1.5 DB. A simple query produces problems on fields from the first
> row that is returned. Subsequent rows are OK.
> The same problem occurs also in SQL-Explorer.
> My BDECfg:
> Oracle Native driver,Batch count 200,Blob size 32,Blobs to cache 64,enable
> BCD False,Enable integers True,Enable Schema cache false,Langdriver,
> list synonyms none,max rows -1,Net protocol TNS,Object Mode True,Open Mode
> Read/write,Rowset Size 20,Sqlpasstroughmode    NOT SHARED,
> Sqlqrymode SERVER,DLL    SQLD_ORA.DLL,DLL32    SQLORA8.DLL,Vendor init
> OCI.DLL,Trace mode    0

> When I run the same query with Oracle product SQL Plus I have no problem
at
> all, so it seems that this is a bug in BDE or Oracle SQL driver.

> DB-table ADT_VISIT (Primary key constraint on first 3 fields, not null
> constraint on 4th field)
> VISIT_ID        CHAR(20) NOT NULL
> ADM_DATE    DATE NOT NULL
> ADM_TIME    DATE NOT NULL
> PAT_ID         CHAR(12) NOT NULL
> DIS_DATE     DATE
> DIS_TIME        DATE

> Query :    Select VISIT_ID, ADM_DATE, ADM_TIME, DIS_DATE, DIS_TIME from
> ADT_VISIT where PAT_ID = '6205291000  ';

> On Oracle SQL Plus 3 rows are returned:
> VISIT_ID    ADM_DATE    ADM_TIME    DIS_DATE    DIS_TIME
> 10102000    10/02/2000        10:00                12/02/2000    15:00
> 10110122    15/06/2000        08:00                30/06/2000    20:00
> 40102500    03/10/2000        15:00                NULL            NULL

> In SQL Explorer this is the result:
> VISIT_ID    ADM_DATE    ADM_TIME    DIS_DATE    DIS_TIME
> 10102000    10/02/2000        10:00
> 10110122    15/06/2000        08:00                30/06/2000    20:00
> 40102500    03/10/2000        15:00

> As you can see the values for DIS_DATE and DIS_TIME in the first row
> returned are not correct.
> However when I add the clause: "and DIS_DATE is not null" to my query I
get
> this result:
> VISIT_ID    ADM_DATE    ADM_TIME    DIS_DATE    DIS_TIME
> 10102000    10/02/2000        10:00                12/02/2000    15:00
> 10110122    15/06/2000        08:00                30/06/2000    20:00

> Values are now there but again this is not what I want because the third
row
> is now not included.

> Has anyone faced this problem before and is there a solution or workaround
?

> Thanks in advance for your answers !

> Francky Deleu
> IT-Manager
> RZ Jan Yperman
> Briekestraat 12
> 8900 Ieper
> Belgium

Other Threads