Re: Executing SQL Statements throught ADO

"Turkbear" <john.g_at_dot.spamfree.com> wrote in message news:c0p1tv89l5sa697l35p1bmje42ivphrveq_at_4ax.com...
> Comments Inline
>
> >"tojo" <Tojo_at_hotmail.com> wrote in message
> >news:MPG.1a3af072945d62b19896ca_at_news.t-online.de...
> >> In article <lpMzb.9514$d35.2257_at_edtnps84>, mfilson29NOSPAM_at_hotmail.com
> >> says...
> >> > 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.
> >> >
>
> What errors are generated?
>
>

That's the thing. There aren't any errors generated. It's just that the code to create the trigger, which contains the text ' :new ' does not get passed to Oracle. All the other ddl script is passed successfully to Oracle.

A side note: Instead of using the Oracle OLEDB provider, I tried using the Microsoft Oracle OLEDB provider and the triggers are created successfully.

>
>
> We Call a Function through ADO ( It creates a set of records from several
tables and assigns the record set a unique key -
> we then use this key as the selection criteria in a Crystal Report called
from the same asp code)
> Once this 'hits' Oracle is executed just as if I had called it from
SqlPlus.
> ( We use ASP so the code is submitted with an cmdObject.Execute(SqlStr)
call, I don't remember the similar VB one)-
> This is our method ( Commented:)
> --- 'General ADO setup stuff
> Set objDC = Server.CreateObject("ADODB.Connection")
> objDC.ConnectionTimeout = 15
> objDC.CommandTimeout = 30
> -- 'Code to connect to Oracle Database
> objDC.Open "Provider=MSDAORA.1;Password=secret;User ID=private;Data
Source=warehouse1"
> ' Set up Parameters to pass
> SqlRid = "Select report_seq.nextval from dual" - Create a unique ID
> rs1 = objDC.Execute(SqlRid) - and Get it
>
> --- assign it to a variable ( will be used here to insert into the new
records the Function creates and later in the full
> code to call the Crystal Report)
>
> rptNbr = rs1(0)
>
>
> Begdate=Request.Form("Bdate") --- Get a couple of parameters from the
user's interface page
> EndDate=Request.Form("Edate") --- ""
> --------- Build the Function Call
> SqlStr = "DECLARE RetVal NUMBER;BEGIN RetVal := SNOW_AND_ICE_REPORT_BUILD
("
> SqlStr = SqlStr & rptNbr & ", 'TP%', 'PC','2406','9640','JOB_RSPS', 'Y',
'N', 'N', NULL,'" & BegDate & "','" & EndDate & "')"
> SqlStr = SqlStr & ";COMMIT;END; "
> --- Send it to Oracle
> objDC.Execute(SqlStr)
>
>
> Given that this works, I cannot see why a call to a function that Creates
a Trigger would have any problem, But maybe DDL is
> handled differently than DML ..
>
>
> Just my 2c
>
> John
>
Received on Fri Dec 05 2003 - 14:36:29 CST