When inserting an array into Db2 with Lotsu Script, using LC classes, at insert I get the error:Order ref # 100542.20070622115839
Error: [IBM][CLI Driver][AS] SQL0407N Assignment of a NULL value to a NOT NULL column “*N” is not allowed. SQLSTATE=23502, Co at line 192
This code worked fine with current DB2 version, 9.1 and Domino 5 (LEI 3.2). When we upgraded Domino to 7.0.2 and LEI we have encountered the error.
Here is the code, the last line is where it fails:
On Error Goto ErrHandler
Call LogAgentAction("Agent Started")
'three connections to db2 are required -- one for each DB2 table
Set db1 = New LCConnection("db2") 'zipord table
Set db2 = New LCConnection("db2") 'mdcext table
Set db3 = New LCConnection("db2") 'ziptxt00 table
Dim finalnum As Integer 'to count the total number of order documents being processed by this agent
finalnum = 0
Set s = New NotesSession
Set db = s.currentdatabase
If SetupVariables = False Then
'this function is supposed to initialize various notes objects which are REQUIRED for this agent to function
' if any of the variables are not set, then there is no reason to continue with the agent
Exit Sub
End If
'establish a new session for the Lotus Connector class
Set sess = New LCSession
sess.ConnectionPooling = True
'retrieve the db2 relevant data from the config document
dbname = config.db2database(0) ' OAKMF01
user = config.db2username(0)
password = config.db2password(0)
db2table1 = config.db2TableNameSubmittedOrder(0) ' this is the ZIPORD table which will contain one record per item on the order
db2table3 = config.db2TableNameSpecialInstructions(0) 'this is the ZIPTXT00 table which will be populated for mini orders where deliverypickup type is "O"
db2table2 = config.db2TableNameOrderSummary(0) 'this is the MDCEXT table which will contain one record PER order
REM set the appropriate properties to connect to the data sources
'db1 represents the connection to the ZIPORD table
db1.database = dbname
db1.Userid = user
db1.Password = password
db1.Metadata = db2table1 'name of the DB2 table (ZIPORD)
db1.MapByName = True
db1.NoJournal = True 'indicates that the database contains non-journaled data
db1.Connect
db1.MapNulls = 1
REM set the appropriate properties to connect to the data sources
'db3 represents the connection to the ZIPTXT00 table
db3.database = dbname
db3.Userid = user
db3.Password = password
db3.Metadata = db2table3 'name of the DB2 table (ZIPTX00)
db3.MapByName = True
db3.NoJournal = True 'indicates that the database contains non-journaled data
db3.Connect
db3.MapNulls = 1
'db2 represents the connection to the MDCEXT table
db2.database = dbname
db2.Userid = user
db2.password = password
db2.Metadata = db2table2 'name of the DB2 table (MDCEXT)
db2.MapByName = True
db2.NoJournal = True 'indicates that the database contains non-journaled data
db2.connect
db2.MapNulls = 1
Dim n As Integer 'this will keep track of the number of UNIQUE items per order
n = -1
Dim torderdate As String, tstore As String, trefno As String, tpdate As String, itemcode As String, quantity As String 'temporary variables
Set doc = ordersview.getfirstdocument 'this will retrieve the first order to be processed
Do While Not doc Is Nothing
Erase Items 'this is a list which will be used to keep track of the list of UNIQUE items on an order
' erase will remove all items that might have been added to this list for the previous order
'the following dynamic array variables are to be used to keep track of information for each record to be inserted in the ZIPORD table
Erase orderdate()
Erase store()
Erase refno()
Erase itemno()
Erase qty()
Erase pdate()
Erase specinstr()
n = -1 'initialize n for each new order -- this will be used to redim the dynamic arrays and also to keep track of the number of unique items on an order
torderdate = doc.Orderdate(0)
tstore = doc.Db2StoreNumber(0)
trefno = doc.referencenumber(0)
tpdate = doc.pickupdate(0)
finalnum = finalnum+1 'this will keep track of the number of order documents being processed
Call LogAgentaction("Processing Order for Store #" & tstore)
Forall x In doc.Orders_1 ' go through all items in the Orders_1 field
If x <> "" Then ' x will be null when there are no items in the orders_1 field
'each element in the orders_i fields (ie. x) is in the format: itemcode~qty~categorycode~unitprice~totalprice
itemcode = Strleft(x, "~")
quantity = Strleft(Strright(x, "~"), "~")
If Iselement(Items(itemcode)) = False Then 'this means that this item (itemcode) is not in the list, therefore add it to the list
Items(itemcode) = quantity
n = n+1
Redim Preserve orderdate(n)
orderdate(n) = torderdate
Redim Preserve store(n)
store(n) = tstore
Redim Preserve refno(n)
refno(n) = trefno
Redim Preserve itemno(n)
itemno(n) = itemcode
Redim Preserve qty(n)
qty(n) = quantity
Redim Preserve pdate(n)
pdate(n) = tpdate
Redim Preserve specinstr(n)
specinstr(n) = tspecinstr
Else
Call LogAgentError(0, "Duplicate Item found for Store#" & tstore & " for the order created on " & Format(doc.Created, "mm/dd/yyyy hh:mm am/pm") & " for item = " & itemcode)
'the above line will log this error in the agent log to be reviewed by a developer
'the unique items will be sent to DB2 and the order will be continued to be processed
End If
End If
End Forall
'Checking to see if there are any items in the order
If (n > -1) Then
'all the dynamic arrays have been populated with information for each unique item in the order
'now assign each dynamic array to a separate field in the ZIPORD table
'now instantiate the fieldlist for the records to be inserted into the ZIPORD table
'n+1 represents the number of UNIQUE items on the order, therefore that is the number of records to be inserted into the ZIPORD table
Set fields = New LCFieldList(n+1)
'z2oref field contains the order reference number
Set field = fields.Append("z2oref", LCTYPE_TEXT)
field.value = refno
'z2cuno field contains the store number (7 characters) -- if the store number is 6 characters, then a period is appended to the store number
Set field = fields.Append("z2cuno", LCTYPE_TEXT)
field.value = store
'z2ordt field contains the order date
Set field = fields.Append("z2ordt", LCTYPE_INT)
field.value = orderdate
'z2zpdt field contains the pickup date
Set field = fields.Append("z2zpdt", LCTYPE_INT)
field.value = pdate
'z2itno field contains the item number
Set field = fields.Append("z2itno", LCTYPE_TEXT)
field.value = itemno
'z2orqt field contains the quantity of the items being ordered
Set field = fields.Append("z2orqt", LCTYPE_INT)
field.value = qty
'the next statement will insert all the records into ZIPORD table (one record for each item on the order)
Call db1.Insert(fields, 1, n+1)