REXX DB2 SQL EXECUTE command fails with Host Variables SQLState 2000

[TheArchies]

New to DB2/SQL, but I have worked with REXX. I've searched books, etc., I do not see what I'm doing wrong (obviously!). I connect to DB2, create the SQL statement, but fail when I perform "EXECSQL EXECUTE S1 USING :nfname, :nlname, :chguser". I have tried many different configuration regarding the SQL Statement's "?" and the execute statement - but no luck - the results are: SQLSTATE 2000, -110 (invalid Hexadecimal), -1, 100

I can get it to work if I don't use the host variables - but just the rexx names within the SQL statement -
" SET FRST_NM = '"nfname"'," , - any direction, assistance is greatly appreciated.

TRACE R
PARSE VAR NEWNAME NEWFNAME ' ' NEWLNAME
"ISPEXEC LIBDEF ISPLLIB DATASET ID('"MY.SDSNLOAD"')"
ADDRESS TSO "SUBCOM DSNREXX"
IF RC THEN DO
S_RC = RXSUBCOM('ADD','DSNREXX','DSNREXX')
END
ADDRESS DSNREXX
SSID = "DBZ1"
"CONNECT "SSID
IF SQLCODE <> 0 THEN CALL DBERROR

NAMSTMT="UPDATE APLGRP.USER_CNTCT ",
"SET FRST_NM = '"||?||"',",
"LAST_NM = '"||?||"'",
"WHERE ",
"USER_ID_CD = '"||?||"'"
nfname = NEWFNAME
nlname = NEWLNAME
chguser = USERID

ADDRESS DSNREXX
ADDRESS DSNREXX
"EXECSQL DECLARE C1 CURSOR FOR S1"
IF SQLCODE <> 0 THEN CALL DBERROR
ADDRESS DSNREXX
"EXECSQL PREPARE S1 FROM :NAMSTMT"
IF SQLCODE <> 0 THEN CALL DBERROR
ADDRESS DSNREXX
"EXECSQL EXECUTE S1 USING :nfname, :nlname, :chguser"
IF SQLCODE <> 0 THEN CALL DBERROR
ADDRESS DSNREXX
"EXECSQL COMMIT"
IF SQLCODE <> 0 THEN CALL DBERROR
ADDRESS ISPEXEC
'SETMSG MSG(ISRZ001)'
CALL USER_NAME

 

[mikrom]

You don't need cursor for update.

Instead of declaring NAMESTR as

UPDATE APLGRP.USER_CNTCT
  SET FRST_NM = '?', 
  LAST_NM = '?' 
WHERE USER_ID_CD = '?'

declare it like this

UPDATE APLGRP.USER_CNTCT
  SET FRST_NM = ?, 
  LAST_NM = ? 
WHERE USER_ID_CD = ?

Then use this order:

EXECSQL 'PREPARE S1 FROM :NAMSTMT'
EXECSQL 'EXECUTE S1 USING :nfname, :nlname, :chguser'

 

[TheArchies]

Yes, I didn't think I needed to declare a cursor either, but I saw another link where someone with a similar issue said they had declared a cursor and that solved there problem. I was at the 'try anything' point.

Thanks so much - it works now. I could have sworn I tried the plain "?" - but must have had a comma off or something (it did take me a while to see a missing comma in the SQL).

Yee Haw!

 

[mikrom]

You need the cursor, when you want to use FETCH to read records using SQL SELECT. For example see persons.rexx which I posted in this DB2 thread:

http://www.tek-tips.com/viewthread.cfm?qid=1570702