ODBC SQLRowCount anomaly

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.

thanks
Phil Received on Mon May 21 2007 - 06:24:29 CDT