0

The below query works in Oracle but not in BizTalk. A quick google has informed me that

"Multiple statements do not work with Oracle (and other databases might prevent this as well). Oracle prevents what is called SQL injection, a mechanism that a hacker would use to get the database to run code within the database."

Is this the same as sub-queries? Would it be possible to write this query without a sub-query?

update order_header oh set uploaded = 'Y'
where oh.user_def_chk_3 = 'N' 
and oh.status in('Complete','Shipped') 
and oh.client_id in (select c.client_id from client c 
    where upper(c.notes) like 'BERGEN%');
Ben
  • 51,770
  • 36
  • 127
  • 149
Andy
  • 2,248
  • 7
  • 34
  • 57
  • 2
    No idea about biztalk, but subqueries and multiple statements are different things, so this ought to be fine as a single statement. But... do you leave the trailing `;` on when you run it via the adapter? JDBC doesn't like that, and it could be that it is being interpreted by biztalk as an attempt to join two statements together - so I'd guess it isn't needed. Otherwise, actual error messages would be helpful. – Alex Poole Jun 15 '12 at 13:57
  • @AlexPoole Wow, i didn't think that a sub query would mean multiple statements, but i never did think that using a ; would mean that BizTalk thinks there's 2 queries. removing the semi colon fixed the problem. – Andy Jun 18 '12 at 07:35

2 Answers2

2

(Based on feedback from wild-guess comment): BizTalk is apparently interpreting the trailing ; as a statement separator, so it's falling foul of the multiple-statement restriction even though it's really only one. Removing the semicolon will prevent this.

See this answer for background on why it is often used in SQL*Plus and SQL Developer etc. even for single statements. Things are different through other clients and adapters.

Community
  • 1
  • 1
Alex Poole
  • 183,384
  • 11
  • 179
  • 318
1

You might place your query within a stored procedure or view. You didn't say how you're executing the query. Are you using a WCF custom adapter?

Jay
  • 13,803
  • 4
  • 42
  • 69
  • due to the database not being administratored by us, stored procedures and views are at risk of being removed at any time. That makes those not a solution for us. We indeed use a WCF-Custom adapter. – Andy Jun 18 '12 at 07:36