user defined sql not working

[bangorlad@hotmail.com]

Hi,
I have an odbc stage where I firstly want to delete records which are resident within the input based on a particular field and then insert them.
The SQL is;

DELETE FROM db_UK_Directory.dbo.tbl_Contact WHERE intOUID = intOUID;
INSERT INTO db_UK_Directory.dbo.tbl_Contact(intOUId, vchrFunction, vchrDirectTelNo, vchrDirectFaxNo, vchrCustTelNo, vchrCustFaxNo) VALUES (?,?,?,?,?,?);

I get the warning;

A2300B_UpdatePatchTables..Map_fields.L03: DSD.BCIPut call to SQLExecute failed.
SQL statement:DELETE FROM db_UK_Directory.dbo.tbl_Contact WHERE intOUID = intOUID;
INSERT INTO db_UK_Directory.dbo.tbl_Contact(intOUId, vchrFunction, vchrDirectTelNo, vchrDirectFaxNo, vchrCustTelNo, vchrCustFaxNo) VALUES (?,?,?,?,?,?)
SQLSTATE=HY009, DBMS.CODE=0
[DataStage][SQL Client][ODBC][DataDirect][ODBC lib] Invalid argument value

intOUId = 99999
vchrFunction = ""
vchrDirectTelNo = "0845 6080371"
vchrDirectFaxNo = "0131 6624053"
vchrCustTelNo = ""
vchrCustFaxNo = ""

If I take either one statement out they run fine.

I have split them into two links coming from a transformer stage but I am getting incorrect results.

Anybody got any ideas?
Thanks.

[kcbland]

Yeah, you can't do 2 statements. It's fundamentally not the way it works. There's a tabular stream of data going into the ODBC stage, the SQL is then prepared. Your 2 statements don't work. The delete works because it doesn't care about the stream, the second works because it does. Together, it can't be prepared.

So, do the delete first in a separate job. Your SQL is curious, I think I'm missing something. The where clause is WHERE intOUID = intOUID, which seems to delete the entire table. Why not use the generated SQL action to do that very thing for you seamlessly?

[ketfos]

Hi Ken,
Does that mean that you cannot have more than one user defined sql in datastage (Query Type)

Ketfos

[kcbland]

Does that mean that you cannot have more than one user defined sql in datastage

I don't believe so. That's what the before/after SQL tabs do on the OCI stages. The SQL statement is not a script, it's a prepared statement like a cursor, which accepts streaming tabular data. Now, I could be wrong, but that is my understanding.

Not to get sidetracked, my point is that it looks like a clearing of the table and then loading, which the generated SQL handles by first issuing the delete statement, and then preparing the insert statement.