Re: Invalid SQL is executed

Hi Robert,

Thanks for your reply. The queries that were executed are:

update vessel_declarations set changed=0, response_status='ACC',
child_change=0, transmission_status='TRD' where id=4274;
update containers set changed=0, response_status='ACC' where
cuscar_id=4274;
update cni set changed=0, response_status='ACC' where cuscar_id=4274;
update gid set changed=0, link_change=0, response_status='ACC' where
cni_id in (select cni_id from cni where cuscar_id=4274);

I selected them all together and hit the Run SQL button. The auto commit
option was on.

I ran the below query that is used for the "in (...)" where clause of
the last update query.

select cni_id from cni where cuscar_id=4274

As expected it gives the error:

Error: java.sql.SQLException: ORA-00904: "CNI_ID": invalid identifier
, SQL State: 42000, Error Code: 904

So why did the last update query, update 89,867 rows? Can it be an issue
with the OCI driver of Oracle?

Kind regards,

Mark

-----Original Message-----
From: Robert Manning [mailto:robert.m.manning@xxxxxxxxx] 
Sent: woensdag 21 maart 2007 22:53
To: Mark Noten
Cc: squirrel-sql-users@xxxxxxxxxxxxxxxxxxxxx
Subject: Re: [Squirrel-sql-users] Invalid SQL is executed

On 3/21/07, Mark Noten <mark.noten@xxxxxxxxxx> wrote:
> Hi all,
>
> When executing the below invalid SQL statement, all the 89,867 records
in the database were updated in stead of just a couple rows.
>
> update gid set changed=0, link_change=0, response_status='ACC' where
cni_id in (select cni_id from cni where cuscar_id=4274);
>
> 89,867 Row(s) Updated
> Query 4 of 4 elapsed time (seconds) - Total: 1,675.695, SQL query:
1,675.695, Building output: 0
>
> The cni_id column does not exist in the cni table. In stead of raising
an exception, the invalid SQL update statement was executed without the
where condition. Needless to say that updating 89,867 records with
production data did not receive a warm welcome. ;-)
>
> Can anyone explain me why this invalid query is executed in stead or
raising an error? I'm using Squirrel SQL Client Version 2.3 final with a
Oracle OCI Driver and oracle.jadbc.driver.OracleDriver from the
ojdbc14.jar.
>

I don't know of any issues in 2.3 that would produce this problem.
What do you get when you just execute:

select cni_id from cni where cuscar_id=4274;

Did you only highlight "update gid set changed=0, link_change=0,
response_status='ACC' " when you ran the query?  Or, could it be that
there were two end-of-lines between the first part and the where
clause, like:

update gid set changed=0, link_change=0, response_status='ACC'

where cni_id in (select cni_id from cni where cuscar_id=4274);

If so and you had the cursor on just the the top line, I can see how
that would happen.  The query tokenizer interprets "\n\n" as a
statement separator in addition to ";".

Rob

-------------------------------------------------------------------------
Take Surveys. Earn Cash. Influence the Future of IT
Join SourceForge.net's Techsay panel and you'll get the chance to share your
opinions on IT & business topics through brief surveys-and earn cash
http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV