Multiple queries in ODBC Stage

[dganeshm]

Here are the two queries I have written in the ODBC stage in a Server job..

INSERT INTO DATALOAD(RECORDKEY, RECORDTYPE, MX_MESSAGE, DSJOBNAME) VALUES (?,?,?,?);

UPDATE DATALOADHEADER SET ERRORCOUNT = 0 WHERE JOBNAME = DSJOBNAME;

The error I am getting is :

DSD.BCIOpenW call to function SQLPrepare failed.

UPDATE DATALOADHEADER SET ERRORCOUNT = 0 WHERE JOBNAME = DSJOBNAME
SQLSTATE=S0022, DBMS.CODE=207
[DataStage][SQL Client][ODBC][IBM(DataDirect OEM)][ODBC SQL Server Driver][SQL Server]Invalid column name 'DSJOBNAME'.

----------------------

DSJOBNAME is in the input link to ODBC stage , how can I overcome this?

[chulett]

You need to use parameter markers in your update DML just like is shown for the insert. The problem is going to be they are positional in ODBC so if you place a single ? there it will substitute the value from the first column from the link.

You'll need to rearrange the columns so that the value for DSJOBNAME is first and then see if this works:

Code: Select all

INSERT INTO DATALOAD(DSJOBNAME, RECORDKEY, RECORDTYPE, MX_MESSAGE) VALUES (?,?,?,?); 

UPDATE DATALOADHEADER SET ERRORCOUNT = 0 WHERE JOBNAME = ?; 

[chulett]

Right after I posted this I realized you weren't trying to emulate the 'insert else update' action the stage can do for you but rather than delete my original post (which has valid information in it) I decided to leave it and add this instead.

Two separate actions would use two links. They can both go into the same stage but one would be for the insert with 4 columns and the one for the update would only have the single column. Let the stage generate the DML for you.

[dganeshm]

Thanks chulett, thats a good learning for the day...