ODBC direct insert works thru addrow/setvalue get read-only msg

HCL Domino Domino Forum
1

Hi,I am testing a new application that adds records in an AS400 DB.

Trying to insert a record thru the resultset.AddRow and resultset.SetValue methods returns an ODBC error “626 LS:DO Read Only - Data caanot be updated or deleted”.

And adding the same data performing an “Insert into” statement thru resultset.Execute nicely adds the record to the file.

Any idea what could be causing this. Although using the insert statement is a perfect valid solution to use, I would like to know what is going wrong.

I am on a W2000 professional workstation and using the IBM Client Access ODBC driver.

thanx,

  • Tom -
2

Subject: ODBC direct insert works thru addrow/setvalue get read-only msg

Would you post an example of “using direct SQL statements”.

I’m having the same problem running R6.3 and Oracle 9. It continues to fail on Updaterow.

3

Subject: ODBC direct insert works thru addrow/setvalue get read-only msg

Hi Tom,its not a Bug its a Feature g.

I have the same Problem - it isn’t possible to update or insert a DB2 DB with the Recordset.

You must use SQLExecute and build a insert or update SQL Statement.

Take care of Date Fields and the SQL Naming convention…!

cu,lex

4

Subject: I am more comfortable with using direct SQL anyway

Lex,Thanx, for your response. Although the feature addrow/setvalue is nice, I am more comfortable with using direct SQL statements anyway.

And as it doesnot work that way, I resorted back to original plan to use direct SQL stmts.

I am still curious to find out the reason though.

IBM-Lotus ???

5

Subject: RE: I am more comfortable with using direct SQL anyway

Check your Client Access settings because AS/400 files can definitely be operated on – read or write. If you’re executing the same statement repetitively, you can use a prepared statement which is much more efficient than executing a bunch of raw sql statements. For ODBC access, you can use the LSDO classes or the LC classes.

dgg

6

Subject: Have check CA settings

The Client Access ODBC settings are set to allow Read/Write (all sql statements).Can you please explain how a prepared statement would work using the LS:DO classes.

thanx,

  • Tom -
7

Subject: RE: Have check CA settings

Check again . . .

Start with the basics (these 3 may produce unpredictable results but probably not a read-only resultset):

make sure you are on the current version according to the AS/400 you’re connecting to,

make sure it’s defined as a “System DSN” vs a “User DSN”,

make sure the user profile being used has proper access to the AS/400 database.

Then look at the DSN’s configuration:

look at the “Server” tab,

click on the “Advanced” button,

in the “commit mode” combobox, make sure that it reads “Commit immeditate (*NONE)”.

Use of a prepared statement is pretty well documented in Designer Help, but here’s a simple example:

query.SQL = “SELECT empno, fname, lname, city, state, zip FROM HR.EMPLOYEES WHERE empno = ?employeeNo?”

for x = 1 to someCount&

strEmpNo$ = “some employee number you provide”

Call rez.SetParameter(1, strEmpNo$)

If Not(rez.Execute()) Then

Error 7201, “ODBC query failed to execute”

End If

If Not(rez.IsResultSetAvailable()) Then

'// do something different

Else

'// process the record

End If

next x

hope that helps,

dgg