What is the Correct LotusScript Syntax to run an SQL statement to Update a AS400 File

HCL Domino Domino Forum
1

I am struggling to get the correct syntax for an SQL statement that will update an AS400 file

I’ve tried SetRow, but it did not update my file on the AS400:

qry1.SQL = | SELECT * FROM | & DB2Table1 & | WHERE APROCD = ’ ’ |

’ Execute the query

ret = result1.Execute

If Not ret Then

’ Set error message for Notes Log

EndMsg = "Error executing query. Error Message: " & result1.ErrorMessage & " - - " & result1.GetExtendedErrorMessage

Goto done

End If

.

.

.

Call result1.SetValue (“APROCD”,“Y”)

ret = result1.updateRow

Then I tried an SQL statement, and it did not update the file either

Set Qry.Connection = Con

Set result.Query = Qry

sqlstr = “UPDATE AUTOEMAIL SET APROCD = ‘Y’ where AFILE = '” & AttFile & “'”

Qry.SQL=sqlstr

result.Execute

The user id being used for the ODBC connection, keeps getting a joblog with this error, but I don’t think it is necessarily the problem:

Message . . . . : AUTOEMAIL in QS36F not valid for operation.

Cause . . . . . : The reason code is 3. Reason codes are: 3 –

AUTOEMAIL not journaled, no authority to the journal, or the journal state

is *STANDBY. Files with an RI constraint action of CASCADE, SET NULL, or

SET DEFAULT must be journaled to the same journal.

Any help is appreciated, I’m losing hair over this one

Chad

2

Subject: What is the Correct LotusScript Syntax to run an SQL statement to Update a AS400 File

Now it has been a while since I’ve done this with Lotusscript, but I checked some old code. The first method you showed was the same as I’ve done in the past successfully (select, and update the result set), but the AS400 files DO need to be journalled for this to work, so the message you were seeing on the AS400 side was correct.

Setup the the journal on this file and try again.

Hope that helps.

Henry