Update SQL Table - Help Required

HCL Domino Domino Forum
1

I have a bit of lotusscript code that retrieves as recordset from a sql table, from which i create notes documents.

That Works fine, what i am then trying to do is set a flag called “Status” in the sql table to be “Yes” as each record is processed, i am using the “results.EXECUTE” which updates the current record and then drops the recordset.

Is there a way of doing this so i can keep looping through the recordset and update the SQL table until the end of data ??

Code i’m using …

Sub AddNewRecord(reference)

Set newdoc = db.CreateDocument



newdoc.Form = "Copy Chase Note"

newdoc.Periodical = results.GetValue("Periodical")

newdoc.Vnuid= results.GetValue("OURID")

newdoc.Issuedate= results.GetValue("Issuedate")

newdoc.Salesexec = results.GetValue("Salesexec")





'add flag to say processed



OURID = (results.GetValue("OURID"))



Line1 = "UPDATE SQLTable "

Line2 = "Set SQLTable.status = 1 "

Line3 = "WHERE SQLTable.OURID = " 

Line4 = "'"

Line5 = OURID

Line6 = "'"

QryString1 = Line1 & Line2 & Line3 & Line4 & Line5 & Line6



qry.SQL = QryString1

Stop



'results.Execute

'results.UpdateRow



Test = newdoc.ComputeWithForm(True,False)

Call newdoc.Save(True,False)

Stop

End Sub

2

Subject: Update SQL Table - Help Required

I would gather all OURIDs in variant array while creating your notes documents or once you get the result of your select.Then create a routing that fetches the content of this array and update the table.

You may even create a basis sql string:

basis = "UPDATE SQLTABLE SET SQLTABLE.STATUS=1 WHERE "

then loop through your array and build the where statement:

forall n in Vourid

if wherestatement=“” then

wherestatement = {OURID=‘} + n + {’}

else

wherestatement = " OR " + wherestatement + { OURID=‘} + n + {’}

end if

end forall

finally complete your query string:

SQLString = basis + wherestatement

then execute it.