odbc sqlrowcount anomaly

sorry, not sure which group is more appropriate (already posted to oracle.misc):

Hi,

I am executing a prepared query in C via ODBC.

The query is this:

BEGIN
IF (:I06 = 1) THEN
  INSERT INTO myschema.mytable( col1,col2,col3,col4,col5,col6,col7)   values (-1, :I00, :C01, :C02, :C03, :C04, :C05) ELSE
  INSERT INTO myschema.mytable( col1,col2,col3,col4,col5,col6,col7)   SELECT -1, :I00, :C01, :C02, :C03, :C04, :C05   FROM DUAL WHERE NOT EXISTS (select 1 from myschema.mytable where col3 = :C01 and
  col2 > :I06 and col2 < :I00)
END IF
END When running this (using the else part of the query) it does no insert (correctly) BUT the SQLRowCount value is returned as 1 which is causing problems (it returns 1 whether the exists clause returns false and therefore an insert DOES take place or true when it doesn't).

If I change the query to:

  INSERT INTO myschema.mytable( col1,col2,col3,col4,col5,col6,col7)   SELECT -1, :I00, :C01, :C02, :C03, :C04, :C05   FROM DUAL WHERE NOT EXISTS (select 1 from myschema.mytable where col3 = :C01 and
  col2 > :I06 and col2 < :I00)

then it works but obviously that doesn't meet the requirements. It is either the IF clause OR the BEGIN..END that causes the problem. What is it and how do I fix it? I don't really want to make 2 separate calls as that would mean a much bigger change to the client code.

If I used an "insert.... where :I06 = 1 or not exists (...)" - would this optimize correctly in all cases of :I06? (i.e not do any selection from mytable when :I06 =1 but do so when :I06 <> 1). I'm not sure how SQLPrepare on this statement will affect the query plan for the different values. I'm guessing it would not work as fast as it should in cases where :I06 = 1.

thanks
Phil Received on Mon May 21 2007 - 08:25:18 CDT