three-part table names in Update/Insert leads to SQLCODE=-518

[SvKN0]

Node.js version: 12.16.2 idb-connector version: 1.2.9 IBM i version: 3 7

I connect to the *LOCAL database and try to update/insert a table on the remote system through DRDA access using three-part table name. It works well through JDBC or green screen but leads an error through idb-connector: -518 THE EXECUTE STATEMENT DOES NOT IDENTIFY A VALID PREPARED STATEMENT

To Reproduce

1. You need two i systems e.g. S1, S2
2. configure DRDA connection from S1 to S2 for user to be used.
3. create test table on S2 create table S2.testlib.sbjid (sbj int);
4. run test script on S1

let db = require('idb-connector'); let dbconn = new db.dbconn(); dbconn.conn("*LOCAL"); // connection to S1 dbconn.debug(true); let sql = 'insert into S2.testlib.sbjid (sbj) values (1) with NC'; // leads an err //let sql = 'update S2.testlib.sbjid set sbj=2 where sbj=1 with NC'; // leads an err //let sql = 'select sbj from S2.testlib.sbjid limit 1'; // works well let sqlStm = new db.dbstmt(dbconn); sqlStm.execSync(sql, function (rs, errMsg) { if (errMsg) { console.log('errMsg: ' + errMsg); } else { console.log('rs: ' + JSON.stringify(rs)); } }); sqlStm.close();

5. get error message errMsg: Error: SQLSTATE=07003 SQLCODE=-518

[dmabupt]

Hello @SvKN0 , Would you tell me how to configure DRDA connection? Generally I use the WRKRDBDIRE command to configure the remote database entries. And then connect to the remote database with the entry name --

let db = require('idb-connector');
let dbconn = new db.dbconn();
dbconn.conn("S2", "XUMENG", "PASSWORD"); // connection with the entry name
let sql = 'insert into testlib.sbjid (sbj) values (1) with NC';
let sqlStm = new db.dbstmt(dbconn);
sqlStm.execSync(sql, function (rs, errMsg) {

In this way, the record can be inserted to the remote database correctly.

[SvKN0]

Hi @dmabupt.

I am not a system administrator and I don't know about any specific system settings (DRDA). For me it's just add a remote database and save credentials: ADDRDBDIRE RDB(S2 S2) RMTLOCNAME(S2*IP) ADDSVRAUTE USRPRF(USR) SERVER(S2) PASSWORD(PWD)

Yes I know that if make connection to remote DB it works. But this connection is very unstable. It crashs several times per day and to fix it needs to restart server. I need to access multiple DB. Using three-part table name in my case is very useful and the topic of this issue exact "three-part name" =)

Can you, please, try to INSERT through strsql using three-part name. If you are able to do this then the DRDA are configured well.

[dmabupt]

Hello @SvKN0 , Thanks for the information. Seems we are using the same command the configure remote db access. I just did not know the ADDSVRAUTE trick.

I checked the manual here and started to suspect some commit related attributes. And then I found this one _SQL_ATTRCOMMIT.

Would you try to set this connection attribute to SQL_TXN_NO_COMMIT?

let db = require('idb-connector');
let dbconn = new db.dbconn();
dbconn.setConnAttr(db.SQL_ATTR_COMMIT, db.SQL_TXN_NO_COMMIT); // <-- Add this
dbconn.conn("*LOCAL"); 

[SvKN0]

Thank you @dmabupt now it works!

I used WITH NC clause in the sql statement. let sql = 'insert into S2.testlib.sbjid (sbj) values (1) WITH NC'; As I understand it's absolutely the same stuff as QL_TXN_NO_COMMIT attribute but it works only for *LOCAL DB request :[ . Very strange but it solved the problem =)

I very grateful for your time spent!