Re: Executing SQL Statements throught ADO

From: Turkbear <john.g_at_dot.spamfree.com>
Date: Fri, 05 Dec 2003 14:19:01 -0600
Message-ID: <c0p1tv89l5sa697l35p1bmje42ivphrveq@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?

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:19:01 CST