V5R4 Host Integration Server and .NET Problem

Hi Folks (And I know there are some in this list!)



One of our customers has upgraded from V5R3 to V5R4 this weekend and has
encountered a problem. They have 2 .NET servers



1/. One Windows Server using Host Integration Server (OleDB) and .NET V2.0
(worked with V5R3 for years, now fails with V5R4)

2/. One Windows Server using the IBM .Net Managed DataProvider and .NET 3.5
(works Fine with V5R4)



The problem with Server 1 manifests itself in 2 ways



1/. Using SELECT * in the SQL statement returns a record but NONE of the
returned columns can be accessed (this produces a "System Index Out Of
Range" Error)

2/. When using NAMED columns in the SELECT statement only the first 3 or 4
columns can be accessed. Attempting to access any additional columns beyond
say the fourth returns the same "System Index Out Of Range" error.



The Customer is reporting it to IBM first (and then Microsoft) I just
wondered if anyone out there has encountered anything similar. Granted it is
old code and it also uses Host Integration Server OleDB connection but
surely it has to be something connected with V5R4



The code below is an example and gives some further detail



All help appreciated and many thanks ( at least the new solution works .sigh
of relief)



Maurice O'Prey







'--------------------

' build SQL statement

'--------------------

cmdOleDBSelect = New OleDBCommand("SELECT * from XXDTALIB.XXCMP100 where
CMUSR=? AND CMPWD=?", OleDBConnect)



'-------------------------------

' add and set command parameters

'-------------------------------

cmdOleDBSelect.Parameters.Add("strUsr",
OleDbType.VarChar, 10)

cmdOleDBSelect.Parameters.Add("strPwd",
OleDbType.VarChar, 10)



cmdOleDBSelect.Parameters("strUsr").Value =
strUsr

cmdOleDBSelect.Parameters("strPwd").Value =
strPwd



'----------------------------------------

' execute command to fill the data reader

'----------------------------------------

dtrOleDBXXCMP100 =
cmdOleDBSelect.ExecuteReader()



'----------------

' process results

'----------------



If dtrOleDBXXCMP100.Read() Then



If dtrOleDBXXCMP100("CMUSR").trim = strUsr and
dtrOleDBXXCMP100("CMPWD").trim = StrPwd Then



'-----------------------------------------

' login succeeded - load session variables

' and redirect to requested page

'-----------------------------------------



Load_Session_Var() ' load session
variables

OleDBConnect.Close() ' close connection


FormsAuthentication.RedirectFromLoginPage(strUsr, chkPersist.Checked)



End If

End If



Points to Note



1/. strOleDBXXCMP100 does retrieve and read one record matching the user
login credentials (I have confirmed this)



2/. As the code stands none of the columns from the reader can be accessed,
i.e. dtrOleDBXXCMP100("CMUSR").trim produces the error "System Index Out Of
Range"



3/. Changing the SQL SELECT statement to select named columns (I.e. removing
the Asterisk to select all columns) WORKS



("SELECT CMUSR,CMPWD from XXDTALIB.XXCMP100 where CMUSR=? AND CMPWD=?",
OleDBConnect)



However (and it's a big however)



Further down in the code (not shown here) other columns from the reader are
accessed and I can only add 3 named columns, e.g. select CMUSR,CMPWD,CMFNM
adding the fourth e.g. CMSNM (regardless of what it is) produces the same
"Index Out Of Range Message" when the column is accessed.