Topic
No replies
mdruk
mdruk
6 Posts
ACCEPTED ANSWER

Pinned topic Read non-externally described file from vb.net 2010 application

‏2012-12-12T15:34:57Z |
I am trying to read non-externally described file in CCSID 65535 from a vb.net 2010 application. I know how how to use the CAST function in sql to handle the conversion from CCSID 65535 to 37. The problem is how do i handle the different record formats of the file. I have attached a file of the record format. The first nine record formats only appear one time at the beginning of file. Then record formats 9 thru 98 can appear many times after that depending on the number of unique account numbers there are. My project is to pass in a unique account number from another routine, capture the first nine record formats and then capture record formats 9 thru 98 for that specific account number.

Following is the test code so far:

Imports IBM.Data.DB2.iSeries
Public Class Form1

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim thisConnection As New iDB2Connection("Datasource=sub; Naming=System; LibraryList=QS36F")
' select ppa,pga,delq date, cut off date arrears, cut off date current,
Dim thisCommand As New iDB2Command("SELECT CASE WHEN CAST(SUBSTR(F00001,35,1) AS CHAR(1) CCSID 37) = '}' THEN " &
"DECIMAL(DECIMAL((CAST(SUBSTR(F00001,29,6) AS CHAR(6) CCSID 37) || '0'),7,0)/10000000,7,7)*-1 " &
"WHEN CAST(SUBSTR(F00001,35,1) AS CHAR(1) CCSID 37) = 'J' THEN " &
"DECIMAL(DECIMAL((CAST(SUBSTR(F00001,29,6) AS CHAR(6) CCSID 37) || '1'),7,0)/10000000,7,7)*-1 " &
"WHEN CAST(SUBSTR(F00001,35,1) AS CHAR(1) CCSID 37) = 'K' THEN " &
"DECIMAL(DECIMAL((CAST(SUBSTR(F00001,29,6) AS CHAR(6) CCSID 37) || '2'),7,0)/10000000,7,7)*-1 " &
"WHEN CAST(SUBSTR(F00001,35,1) AS CHAR(1) CCSID 37) = 'L' THEN " &
"DECIMAL(DECIMAL((CAST(SUBSTR(F00001,29,6) AS CHAR(6) CCSID 37) || '3'),7,0)/10000000,7,7)*-1 " &
"WHEN CAST(SUBSTR(F00001,35,1) AS CHAR(1) CCSID 37) = 'M' THEN " &
"DECIMAL(DECIMAL((CAST(SUBSTR(F00001,29,6) AS CHAR(6) CCSID 37) || '4'),7,0)/10000000,7,7)*-1 " &
"WHEN CAST(SUBSTR(F00001,35,1) AS CHAR(1) CCSID 37) = 'N' THEN " &
"DECIMAL(DECIMAL((CAST(SUBSTR(F00001,29,6) AS CHAR(6) CCSID 37) || '5'),7,0)/10000000,7,7)*-1 " &
"WHEN CAST(SUBSTR(F00001,35,1) AS CHAR(1) CCSID 37) = 'O' THEN " &
"DECIMAL(DECIMAL((CAST(SUBSTR(F00001,29,6) AS CHAR(6) CCSID 37) || '6'),7,0)/10000000,7,7)*-1 " &
"WHEN CAST(SUBSTR(F00001,35,1) AS CHAR(1) CCSID 37) = 'P' THEN " &
"DECIMAL(DECIMAL((CAST(SUBSTR(F00001,29,6) AS CHAR(6) CCSID 37) || '7'),7,0)/10000000,7,7)*-1 " &
"WHEN CAST(SUBSTR(F00001,35,1) AS CHAR(1) CCSID 37) = 'Q' THEN " &
"DECIMAL(DECIMAL((CAST(SUBSTR(F00001,29,6) AS CHAR(6) CCSID 37) || '8'),7,0)/10000000,7,7)*-1 " &
"WHEN CAST(SUBSTR(F00001,35,1) AS CHAR(1) CCSID 37) = 'R' THEN " &
"DECIMAL(DECIMAL((CAST(SUBSTR(F00001,29,6) AS CHAR(6) CCSID 37) || '9'),7,0)/10000000,7,7)*-1 " &
"ELSE DECIMAL(DECIMAL(CAST(SUBSTR(F00001,29,7) AS CHAR(7) CCSID 37),7,0)/10000000,7,7) END AS PPA, " &
"CASE WHEN CAST(SUBSTR(F00001,45,1) AS CHAR(1) CCSID 37) = '}' THEN " &
"DECIMAL(DECIMAL((CAST(SUBSTR(F00001,39,6) AS CHAR(6) CCSID 37) || '0'),7,0)/10000000,7,7)*-1 " &
"WHEN CAST(SUBSTR(F00001,45,1) AS CHAR(1) CCSID 37) = 'J' THEN " &
"DECIMAL(DECIMAL((CAST(SUBSTR(F00001,39,6) AS CHAR(6) CCSID 37) || '1'),7,0)/10000000,7,7)*-1 " &
"WHEN CAST(SUBSTR(F00001,45,1) AS CHAR(1) CCSID 37) = 'K' THEN " &
"DECIMAL(DECIMAL((CAST(SUBSTR(F00001,39,6) AS CHAR(6) CCSID 37) || '2'),7,0)/10000000,7,7)*-1 " &
"WHEN CAST(SUBSTR(F00001,45,1) AS CHAR(1) CCSID 37) = 'L' THEN " &
"DECIMAL(DECIMAL((CAST(SUBSTR(F00001,39,6) AS CHAR(6) CCSID 37) || '3'),7,0)/10000000,7,7)*-1 " &
"WHEN CAST(SUBSTR(F00001,45,1) AS CHAR(1) CCSID 37) = 'M' THEN " &
"DECIMAL(DECIMAL((CAST(SUBSTR(F00001,39,6) AS CHAR(6) CCSID 37) || '4'),7,0)/10000000,7,7)*-1 " &
"WHEN CAST(SUBSTR(F00001,45,1) AS CHAR(1) CCSID 37) = 'N' THEN " &
"DECIMAL(DECIMAL((CAST(SUBSTR(F00001,39,6) AS CHAR(6) CCSID 37) || '5'),7,0)/10000000,7,7)*-1 " &
"WHEN CAST(SUBSTR(F00001,45,1) AS CHAR(1) CCSID 37) = 'O' THEN " &
"DECIMAL(DECIMAL((CAST(SUBSTR(F00001,39,6) AS CHAR(6) CCSID 37) || '6'),7,0)/10000000,7,7)*-1 " &
"WHEN CAST(SUBSTR(F00001,45,1) AS CHAR(1) CCSID 37) = 'P' THEN " &
"DECIMAL(DECIMAL((CAST(SUBSTR(F00001,39,6) AS CHAR(6) CCSID 37) || '7'),7,0)/10000000,7,7)*-1 " &
"WHEN CAST(SUBSTR(F00001,45,1) AS CHAR(1) CCSID 37) = 'Q' THEN " &
"DECIMAL(DECIMAL((CAST(SUBSTR(F00001,39,6) AS CHAR(6) CCSID 37) || '8'),7,0)/10000000,7,7)*-1 " &
"WHEN CAST(SUBSTR(F00001,45,1) AS CHAR(1) CCSID 37) = 'R' THEN " &
"DECIMAL(DECIMAL((CAST(SUBSTR(F00001,39,6) AS CHAR(6) CCSID 37) || '9'),7,0)/10000000,7,7)*-1 " &
"ELSE DECIMAL(DECIMAL(CAST(SUBSTR(F00001,39,7) AS CHAR(7) CCSID 37),7,0)/10000000,7,7) END AS PGA, " &
"CAST(INT(SUBSTR(F00001,50,2)) || '/' || INT(SUBSTR(F00001,52,2)) || '/' || INT(SUBSTR(F00001,54,2)) AS CHAR(10) CCSID 37) AS DelqDate, " &
"CAST(INT(SUBSTR(F00001,60,2)) || '/' || INT(SUBSTR(F00001,62,2)) || '/' || INT(SUBSTR(F00001,64,2)) AS CHAR(10) CCSID 37) AS CutDateArr, " &
"CAST(INT(SUBSTR(F00001,70,2)) || '/' || INT(SUBSTR(F00001,72,2)) || '/' || INT(SUBSTR(F00001,74,2)) AS CHAR(10) CCSID 37) AS CutDateCurr " &
"FROM BILLOUT1 WHERE CAST(SUBSTR(F00001,99,2) AS CHAR(2) CCSID 37)='00'", thisConnection)

'Message(info)
Dim thisCommand2 As New iDB2Command("SELECT CAST(SUBSTR(F00001,1,42) AS CHAR(42) CCSID 37) AS MESS1, " &
"CAST(SUBSTR(F00001,44,42) AS CHAR(42) CCSID 37) AS MESS9, " &
"FROM BILLOUT1 WHERE CAST(SUBSTR(F00001,99,2) AS CHAR(2) CCSID 37)='01'", thisConnection)
Dim thisCommand3 As New iDB2Command("SELECT CAST(SUBSTR(F00001,1,42) AS CHAR(42) CCSID 37) AS MESS2, " &
"CAST(SUBSTR(F00001,44,42) AS CHAR(42) CCSID 37) AS MESS10, " &
"FROM BILLOUT1 WHERE CAST(SUBSTR(F00001,99,2) AS CHAR(2) CCSID 37)='02'", thisConnection)
Dim thisCommand4 As New iDB2Command("SELECT CAST(SUBSTR(F00001,1,42) AS CHAR(42) CCSID 37) AS MESS3, " &
"CAST(SUBSTR(F00001,44,42) AS CHAR(42) CCSID 37) AS MESS11, " &
"FROM BILLOUT1 WHERE CAST(SUBSTR(F00001,99,2) AS CHAR(2) CCSID 37)='03'", thisConnection)
Dim thisCommand5 As New iDB2Command("SELECT CAST(SUBSTR(F00001,1,42) AS CHAR(42) CCSID 37) AS MESS4, " &
"CAST(SUBSTR(F00001,44,42) AS CHAR(42) CCSID 37) AS MESS12, " &
"FROM BILLOUT1 WHERE CAST(SUBSTR(F00001,99,2) AS CHAR(2) CCSID 37)='04'", thisConnection)
Dim thisCommand6 As New iDB2Command("SELECT CAST(SUBSTR(F00001,1,42) AS CHAR(42) CCSID 37) AS MESS5, " &
"CAST(SUBSTR(F00001,44,42) AS CHAR(42) CCSID 37) AS MESS13, " &
"FROM BILLOUT1 WHERE CAST(SUBSTR(F00001,99,2) AS CHAR(2) CCSID 37)='05'", thisConnection)
Dim thisCommand7 As New iDB2Command("SELECT CAST(SUBSTR(F00001,1,42) AS CHAR(42) CCSID 37) AS MESS6, " &
"CAST(SUBSTR(F00001,44,42) AS CHAR(42) CCSID 37) AS MESS14, " &
"FROM BILLOUT1 WHERE CAST(SUBSTR(F00001,99,2) AS CHAR(2) CCSID 37)='06'", thisConnection)
Dim thisCommand8 As New iDB2Command("SELECT CAST(SUBSTR(F00001,1,42) AS CHAR(42) CCSID 37) AS MESS7, " &
"CAST(SUBSTR(F00001,44,42) AS CHAR(42) CCSID 37) AS MESS15, " &
"FROM BILLOUT1 WHERE CAST(SUBSTR(F00001,99,2) AS CHAR(2) CCSID 37)='07'", thisConnection)
Dim thisCommand9 As New iDB2Command("SELECT CAST(SUBSTR(F00001,1,42) AS CHAR(42) CCSID 37) AS MESS8, " &
"CAST(SUBSTR(F00001,44,42) AS CHAR(42) CCSID 37) AS MESS16, " &
"FROM BILLOUT1 WHERE CAST(SUBSTR(F00001,99,2) AS CHAR(2) CCSID 37)='08'", thisConnection)
'bill info by account no
Dim thisCommand1 As New iDB2Command("SELECT CAST(SUBSTR(F00001,1,9) AS CHAR(9) CCSID 37) AS AcctNo, CAST(SUBSTR(F00001,41,5) AS CHAR(5) CCSID 37) AS DaysCurrBC, " &
"CAST(SUBSTR(F00001,46,5) AS CHAR(5) CCSID 37) AS DaysCurrLM, " &
"CAST(SUBSTR(F00001,51,5) AS CHAR(5) CCSID 37) AS DaysCurrLY, " &
"CAST(SUBSTR(F00001,1,24) AS CHAR(24) CCSID 37) AS Name " &
"FROM BILLOUT1 WHERE CAST(SUBSTR(F00001,99,2) " &
"AS CHAR(2) CCSID 37)='09' AND CAST(SUBSTR(F00001,1,9) AS CHAR(9) CCSID 37)='112012800'", thisConnection)
Dim thisCommand10 As New iDB2Command("SELECT CAST(SUBSTR(F00001,1,24) AS CHAR(24) CCSID 37) AS Name " &
"FROM BILLOUT1 WHERE CAST(SUBSTR(F00001,99,2) AS CHAR(2) CCSID 37)='10'", thisConnection)
Dim thisReader As iDB2DataReader
Dim thisReader1 As iDB2DataReader
Dim thisReader10 As iDB2DataReader
Try
thisConnection.Open()
thisReader = thisCommand.ExecuteReader(CommandBehavior.SingleRow)
If thisReader.HasRows Then
While thisReader.Read()
Label2.Text = CStr(thisReader("PPA"))
Label4.Text = CStr(thisReader("PGA"))
Label6.Text = CStr(thisReader("DelqDate"))
Label13.Text = CStr(thisReader("CutDateArr"))
Label14.Text = CStr(thisReader("CutDateCurr"))
End While
End If
thisReader.Close()
thisCommand.Dispose()
thisReader1 = thisCommand1.ExecuteReader()
Dim dt As New DataTable()
dt.Load(thisReader1)
dgv1.DataSource = dt
If thisReader1.HasRows Then
While thisReader1.Read()

Label8.Text = CStr(thisReader1("AcctNo"))
Label15.Text = CStr(thisReader1("DaysCurrBC"))
Label16.Text = CStr(thisReader1("DaysCurrLM"))
Label17.Text = CStr(thisReader1("DaysCurrLY"))
Label18.Text = CStr(thisReader1("Name"))
End While
End If
thisReader1.Close()
thisCommand1.Dispose()
thisReader10 = thisCommand10.ExecuteReader(CommandBehavior.SingleRow)
If thisReader10.HasRows Then
While thisReader10.Read()

Label18.Text = CStr(thisReader10("Name"))

End While
End If
thisReader10.Close()
thisCommand10.Dispose()
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
thisConnection.Close()
End Try
End Sub
End Class