null field in FW_OpenRecordSet

[damianodec]

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

Code: Select all  Expand view  RUN

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

[nageswaragunupudi]

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.

[damianodec]

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

[damianodec]

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.

[nageswaragunupudi]

Let me see.

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

[nageswaragunupudi]

Please also try

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

[nageswaragunupudi]

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

[damianodec]

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()

[nageswaragunupudi]

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

[damianodec]

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: Select all  Expand view  RUN

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

I can write all fields with:

Code: Select all  Expand view  RUN

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

or some fields:

Code: Select all  Expand view  RUN

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

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

in AS400 I use SQL istruction:

Code: Select all  Expand view  RUN

SELECT * FROM MYTABLE

I get:

Code: Select all  Expand view  RUN

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: Select all  Expand view  RUN

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

I get:

Code: Select all  Expand view  RUN

n.Rek     A                       B                    C
00001  FIVEWIN       WINDOWS            10

[Armando]

Damiano:

Just out of curiosity, do a test with ADO

Regards

[damianodec]

I tried it... always the same error.