Re: odbc sqlrowcount anomaly

On May 21, 2:25 pm, phancey <d..._at_2bytes.co.uk> wrote:
> 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

i assume this happens because it is now a procedure not a statement. I don't want to return rowcount in a variable because then I will have to change the way the calls are made. Is there an option I can turn on - like set serveroutput on - within the begin....end that will return the rowcount the same as if it were a single statement?

Thanks Received on Tue May 22 2007 - 04:50:15 CDT