ADO Seek problems, part 2.

["Chris Devous" <cdevous@xxxxxxxxxxx>]

--
[ Picked text/plain from multipart/alternative ]
Content-description: Mail message body

Back at the end of October, I posted the following:
------------------------------------------------------------
All,

Has anybody experienced a "false positive" on CAE V5R1 using
the seek method of
the ADO provider?

here is the relevant code:

Set cnDB2 = New ADODB.Connection
cnDB2.CursorLocation = adUseServer
cnDB2.Open "Provider=IBMDA400;Data Source=192.168.1.2;", "",
""
'
Set rsUPCWeb = New ADODB.Recordset
rsUPCWeb.CursorLocation = adUseServer
rsUPCWeb.Index =
"/QSYS.LIB/BPCSCDUSRF.LIB/UPCWEBL6.FILE(*FIRST, *NONE)"
rsUPCWeb.Open "/QSYS.LIB/BPCSCDUSRF.LIB/UPCWEBL6.FILE(*FIRST,
*NONE)", cnDB2, adOpenStatic, adLockReadOnly, adCmdTableDirect


sUPCSKU = Mid(rsOrderLines("lprod"), 1, 6) _
        & "-" _
        & Mid(rsOrderLines("lprod"), 7, 3) _
        & "-" _
        & Mid(rsOrderLines("lprod"), 10, 2) _
        & "-" _
        & sTape
If Not IsEmpty(KeyValues) Then
   Erase KeyValues
End If
KeyValues = Array(sUPCSKU, rsOrderLines("lcust"))
rsUPCWeb.Seek KeyValues, adSeekFirstEQ



Here is a DSPFD over UPCWEBL6:

Record format . . . . . . . . . . . . :            IUPCWEB
   Key field  . . . . . . . . . . . . :            USKU
     Sequence . . . . . . . . . . . . :            Ascending
     Sign specified   . . . . . . . . :            UNSIGNED
     Zone/digit specified . . . . . . :            *NONE
     Alternative collating sequence . :            No
   Key field  . . . . . . . . . . . . :            UCUST
     Sequence . . . . . . . . . . . . :            Ascending
     Sign specified . . . . . . . . . :            SIGNED
     Zone/digit specified . . . . . . :            *NONE
     Alternative collating sequence . :            No


sUPCSKU is a string, and the rsOrderLines("lcust") is from
iSeries data, packed
6 on
the iSeries, and whatever that translates to in an ADO data
type.

Watching my code in debug mode, I can see the valuve of
sUPCSKU and
rsOrderLines("lcust").  There is no match in rsUPCWeb for
these key values.
Seek
does not return an error, but rsUPCWeb.EOF is set FALSE after
the seek,
indicating
a match.  Yet, dumping the keyfields from rsUPCWEB shows that
they don't match
sUPCSKU and UCUST!

Anybody have any ideas?

TIA,

Chris
-------------------------------------------------------------------------------------------------------

Well, I found the answer.  Since I asked the list, I thought I'd share what I 
found.

IBM has reported APAR SE08268, which addresses a bug in the provider.  The fix
will be released in the next V5R1 service pack, due out in December.

The issue is that the provider assumes that the order of fields in a key will 
be the
same order as they are in the table.  For example, if you have a table 
consisting of
Field 1, Field 2, Field 3, Field 4, the provider assumes that you might make a 
key like
Field 1, Field 3, but that you won't make a key like Field 3, Field 1.

The other issue is with ADO.  On a server side cursor, you must access the
recordset for the provider to return EOF.  Doing a movefirst on the recordset is
enough to make this happen.  I changed my code to do a movefirst and my key so
that it's fields were in the same order they appear in the table, and all is 
well.

The next service pack will include a new version of cwbzzodb.dll, which is the 
DLL for
the OLE DB provider.  I have tested the new version in my environment, and it 
fixes
this problem.

In the interim, if you experience this problem, check your key!

--Chris