topic-36796

Posts: 434

Joined: Wed Jun 06, 2007 02:58 PM

null field in FW_OpenRecordSet
Posted: Thu Feb 21, 2019 11:41 AM

Hi, I have this problem.
I use a my function to read tables in DB2 database into XBROWSE:

cSelect = "Select * from MyTable " 
 oRs := FW_OpenRecordSet( oConnnection, cSelect, 1 )
...
...
@ 40,10 XBROWSE oBrw SIZE -10,-50 PIXEL CELL AUTOSORT FASTEDIT;
     RECORDSET oRs AUTOCOLS;
OF oDlg2

if table has null field I get this:

is there a way to get right data by FW_OpenRecordSet when there are null fields inside table?

thanks

FiveWin for xHarbour 24.02 - Feb. 2024 - Embarcadero C++ 7.60 for Win32 Copyright (c) 1993-2023

FWH 64 for Harbour 19.06 (MSVC++) Jun. 2019 - Harbour 3.2.0dev (r1904111533)

Visual Studio 2019 - Pelles C V.8.00.60 (Win64)

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: null field in FW_OpenRecordSet
Posted: Thu Feb 21, 2019 01:00 PM

Can you please test
? oRs:State

This is surprising.
I have no experience with DB2, but when tested with mysql,msssql,oracle, etc. we never had any problem with fields containing null values. Anyway we will again test with null values.

Regards

G. N. Rao.

Hyderabad, India

damianodec

Posts: 434

Joined: Wed Jun 06, 2007 02:58 PM

Re: null field in FW_OpenRecordSet
Posted: Thu Feb 21, 2019 01:23 PM

hi mr Rao,
msginfo(oRs:State) is 1.000

FiveWin for xHarbour 24.02 - Feb. 2024 - Embarcadero C++ 7.60 for Win32 Copyright (c) 1993-2023

FWH 64 for Harbour 19.06 (MSVC++) Jun. 2019 - Harbour 3.2.0dev (r1904111533)

Visual Studio 2019 - Pelles C V.8.00.60 (Win64)

damianodec

Posts: 434

Joined: Wed Jun 06, 2007 02:58 PM

Re: null field in FW_OpenRecordSet
Posted: Thu Feb 21, 2019 01:40 PM

hi mr Rao,
I'm checking myTable, and now I understand that there are a lot of fields that have blanks value in numeric field.
I'd like that FW_OpenRecordSet manages this situation.

FiveWin for xHarbour 24.02 - Feb. 2024 - Embarcadero C++ 7.60 for Win32 Copyright (c) 1993-2023

FWH 64 for Harbour 19.06 (MSVC++) Jun. 2019 - Harbour 3.2.0dev (r1904111533)

Visual Studio 2019 - Pelles C V.8.00.60 (Win64)

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: null field in FW_OpenRecordSet
Posted: Thu Feb 21, 2019 01:51 PM

Let me see.

Please try
? oRs:Fields( 0 ):Value
? oRs:Fields( 1 ):Value
etc

Regards

G. N. Rao.

Hyderabad, India

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: null field in FW_OpenRecordSet
Posted: Thu Feb 21, 2019 01:58 PM

Please also try

? oRs:ClassName()
? oRs:Fields:Count()
? oRs:RecordCount()

Regards

G. N. Rao.

Hyderabad, India

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: null field in FW_OpenRecordSet
Posted: Thu Feb 21, 2019 02:31 PM

Please help with these small tests. I am eager to find where the problem is.

Regards

G. N. Rao.

Hyderabad, India

damianodec

Posts: 434

Joined: Wed Jun 06, 2007 02:58 PM

Re: null field in FW_OpenRecordSet
Posted: Thu Feb 21, 2019 02:43 PM

oRs:ClassName() = toleauto
oRs:Fields:Count() = 103
oRs:RecordCount() = ERROR!!!
Error description: Error ADODB.RecordSet:FIELDS/9
l record corrente corrisponde all'inizio o alla fine del file oppure è stato eliminato.
Per eseguire l'operazione richiesta è necessario disporre di un record corrente.: VALUE

I know that inside field 95 there is a bad value (blanks instead zeros)
and then:
oRs:Fields( 95 ):Name = ERROR like oRs:RecordCount()

FiveWin for xHarbour 24.02 - Feb. 2024 - Embarcadero C++ 7.60 for Win32 Copyright (c) 1993-2023

FWH 64 for Harbour 19.06 (MSVC++) Jun. 2019 - Harbour 3.2.0dev (r1904111533)

Visual Studio 2019 - Pelles C V.8.00.60 (Win64)

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: null field in FW_OpenRecordSet
Posted: Thu Feb 21, 2019 02:50 PM

Yes.
That is the reason, xbrowse is not recognising this oRs as a valid recordset.

The problem is not in FW_OpenRecordSet()

The problem is
(1) either in the Oledb Provider for DB2 which is unable to handle this table correctly
or
(2) The data in this table is corrupted.

Next best option now is to read selected fields:
SELECT FLD1,FLD2,.... FROM ...
Select fields which you are confident

Regards

G. N. Rao.

Hyderabad, India

damianodec

Posts: 434

Joined: Wed Jun 06, 2007 02:58 PM

Re: null field in FW_OpenRecordSet
Posted: Thu Feb 21, 2019 03:02 PM

db2 progrramers use RPG III language (like me), when we write a record we can write all fields of record or just some fileds. i.e.:

Tabld MYTABLE record name MYREK with 3 fields:

Code (fw): Select all Collapse

A CAHR(10)
B CHAR(10)
C DECIMAL(3, 0)

I can write all fields with:

Code (fw): Select all Collapse

A = "FIVEWIN"
B= "WINDOWS"
C=10
WRITE MYREK

or some fields:

Code (fw): Select all Collapse

A = "ADO"
B= "AS400"
WRITE MYREK

in seconds case field C has blanks (manages inside DB)

in AS400 I use SQL istruction:

Code (fw): Select all Collapse

SELECT * FROM MYTABLE

I get:

Code (fw): Select all Collapse

n.Rek     A                       B                    C
00001  FIVEWIN       WINDOWS            10
00002   ado               AS400             +++++++++

field C in 2nd record return all ++++++++ because field is not managed in WRITE istrution field C

I can use HEX function in AS400 SQL db2 to avoid seconds record, blank value in hexadecimal is '40'

Code (fw): Select all Collapse

SELECT * FROM MYTABLE
WHERE HEX(C) <> '404040'

I get:

Code (fw): Select all Collapse

n.Rek     A                       B                    C
00001  FIVEWIN       WINDOWS            10

FiveWin for xHarbour 24.02 - Feb. 2024 - Embarcadero C++ 7.60 for Win32 Copyright (c) 1993-2023

FWH 64 for Harbour 19.06 (MSVC++) Jun. 2019 - Harbour 3.2.0dev (r1904111533)

Visual Studio 2019 - Pelles C V.8.00.60 (Win64)

Armando

Posts: 3358

Joined: Fri Oct 07, 2005 08:20 PM

Re: null field in FW_OpenRecordSet
Posted: Thu Feb 21, 2019 10:39 PM

Damiano:

Just out of curiosity, do a test with ADO

Regards

SOI, s.a. de c.v.
estbucarm@gmail.com
http://www.soisa.mex.tl/
http://sqlcmd.blogspot.com/
Tel. (722) 174 44 45
Carpe diem quam minimum credula postero

damianodec

Posts: 434

Joined: Wed Jun 06, 2007 02:58 PM

Re: null field in FW_OpenRecordSet
Posted: Fri Feb 22, 2019 07:23 AM

I tried it... always the same error.

FiveWin for xHarbour 24.02 - Feb. 2024 - Embarcadero C++ 7.60 for Win32 Copyright (c) 1993-2023

FWH 64 for Harbour 19.06 (MSVC++) Jun. 2019 - Harbour 3.2.0dev (r1904111533)

Visual Studio 2019 - Pelles C V.8.00.60 (Win64)

FiveTech Support Forums

Continue the discussion