Custom SQL to Update using ODBC or OLE/DB?

[zbethem]

Has anyone had any success in writing a custom SQL statement to update records using either a ODBC or a OLE/DB stage? I'm trying to update existing rows in SQL Server while including a subquery in my where clause. Here's a snippet of the SQL:

Code: Select all

UPDATE 
scorecard
SET 
id=?, mo_date=?, region=?, employee=?, 
stage_code=?, temp_id=?, category_id=?, first_employed_date=?,
last_employed_date=?
WHERE 
id=? AND 
region_id=? AND 
employee=? AND 
broker_stage_code=? AND 
temp_id=? AND 
category_id=? and	
exists(
	select 1
	from calendar
	where scorecard.mo_date = calendar.mo_date and
	mo =? and
	yr =?);

Using Toad for SQL Server and plugging in the bind variables, I have no issues. I'm new to SQL Server I must admit. I'm used to positional numbers similar to Oracle.

How do the bind variables get assigned? Basically, how does the '?' get the right input value from the inbound link?

Thanks in advance.

[chulett]

The ODBC parameter markers aren't quite as... flexible... as the numbered ones the OCI stage uses. The numbered ones can be used more than once in a query, while the '?' markers cannot as they are purely positional. Meaning, if you have 10 columns you can only have 10 parameter markers and they are assigned in the order they are encountered in the sql.

That's my understanding, anywho.

[DSguru2B]

Try using the DRS stage. That might do the trick as it uses numbered markers like the OCI stage.

[chulett]

Not in my 7.5.1A version... it uses question marks, even when set to Oracle.

[DSguru2B]

My statement was incorrect. I thought i had seen numbered parameters in a custom update in a DRS stage. But that was'nt the case. It was some other stage.
Sorry about that guys.