Re: Executing SQL Statements throught ADO

From: Mark Filson <mfilson29NOSPAM_at_hotmail.com>
Date: Thu, 04 Dec 2003 21:18:52 GMT
Message-ID: <0xNzb.9535$d35.6492@edtnps84>

"mcstock" <mcstock @ enquery .com> wrote in message news:RYOdnfTT6fbmPFKiRVn-gw_at_comcast.com...
> "Mark Filson" <mfilson29NOSPAM_at_hotmail.com> wrote in message
> news:lpMzb.9514$d35.2257_at_edtnps84...
> | Hi All,
> |
> | Here's the issue I'm having... I have a VB COM component that is used to
> | parse SQL files and apply them to an instance of Oracle. Most of the
> | statements in the sql file work but a few don't. Here is an example of
an
> | offending one:
> |
> |
> | CREATE OR REPLACE TRIGGER TR_SEQ_APPLICATION_SETTINGS
> | AFTER INSERT OR UPDATE ON APPLICATION_SETTINGS
> | FOR EACH ROW
> | BEGIN
> | SELECT SEQ_APPLICATION_SETTINGS.nextval INTO :new.ID FROM dual;
> | END TR_SEQ_APPLICATION_SETTINGS;
> | /
> |
> |
> | The Sequence that's referenced is created successfully and my code says
> that
> | the trigger is created (ie, no errors are thrown), but when I attempt to
> | insert a bunch of values into the table APPLICATION_SETTINGS, errors are
> | generated. Is there something about the syntax of this create statement
> | thats wrong. For what it's worth, the above statement can be run in SQL
> | PLUS successfully and the trigger is created.
> |
> | Cheers.
> |
> |
>
> if the statement works in SQL*Plus, and you checked the data dictionary to
> make sure the trigger is valid, then the issue seams to be the errors that
> are being generated when you do your inserts -- how 'bout if you post
those?
>
> --
> Mark C. Stock
> mcstock -> enquery(dot)com
> www.enquery.com training & consulting
>
>

Hi Mark.

First of all, thanks for the constructive comments (Unlike Sybrand) The error that's occurring is as follows:

---

Err.Number = -2147217900
Err.Description = ORA-01400: cannot insert NULL into ("IMAGEDB"."APPLICATION_SETTINGS"."ID")
SQL Statement : INSERT INTO IMAGEDB.APPLICATION_SETTINGS (type, name ,setting, description, section, editable) VALUES ('FLAG', 'TEST', '1', 'Description', 'Section', '1') failed.

---

I know why the error's occurring. The table 'APPLICATION_SETTINGS' has a field called ID that is defined as NOT NULL. Because the 'BEFORE INSERT' trigger is not being created, this error is being generated. If I manually create the trigger in SQL PLUS and then manually run the above SQL statement, it works. The question I have though, is why doesn't the trigger get created in the first place. Like I said, ADO doesn't show any errors being generated, so it thinks the trigger is created successfully.

>
Received on Thu Dec 04 2003 - 15:18:52 CST