Re: Executing SQL Statements throught ADO

"Alkos" <azerty_at_nospam.org> wrote in message news:bqpg36$2tm4_at_news.rd.francetelecom.fr...
>
> "Mark Filson" <mfilson29NOSPAM_at_hotmail.com> a écrit dans le 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.
> >
> >
>
> Hello,
>
> At first glance, you may want to generate kind of artificial unique key.
> The correct way to do (imho) may be the following :
> CREATE OR REPLACE TRIGGER TR_SEQ_APPLICATION_SETTINGS
> BEFORE INSERT ON APPLICATION_SETTINGS
> FOR EACH ROW
> DECLARE
> new_uid INTEGER;
> BEGIN
> SELECT SEQ_APPLICATION_SETTINGS.nextval INTO new_uid FROM dual;
> :new.ID:=new_uid;
> END TR_SEQ_APPLICATION_SETTINGS;
>
> HTH
>
> --
> Alkos
>
> In addition,
> Personnally, I don't think Sybrand is an asshole. He is just fed up of
what
> some lousy developpers gone to North Pole let in the DBs he has in charge.
I
> don't know if you read this NG frequently but most of Sybrand statements
> were valuable. He may not be always polite or kindly but he's true most of
> the time.
> Sure is you are NOT . . .
>
>
>
>
>
>
>
>
>

Hi,

I appreciate the response, but what you have proposed is what I'm already doing. The " :new " in the above line is the offending construct. There appears to be an issue with the ORAOLEDB.Oracle ADO provider. It does not seem to like the " :new ". I have yet to find a workaround for this. Apparently the Microsoft Oracle driver will correctly pass this text to Oracle, but it has other problems which prevent us from using it.

mf. Received on Fri Dec 05 2003 - 12:26:00 CST