Re: Executing SQL Statements throught ADO

"Andy Hassall" <andy_at_andyh.co.uk> wrote in message news:l7cvsvo6k5r613qjipi9kk5l40619o3716_at_4ax.com...
> On Thu, 04 Dec 2003 21:57:36 +0000, Andy Hassall <andy_at_andyh.co.uk> wrote:
>
> >On Thu, 04 Dec 2003 20:02:25 GMT, "Mark Filson"
<mfilson29NOSPAM_at_hotmail.com>
> >wrote:
> >
> >>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;
> >
> > Probably the :new is being interpreted as a placeholder for a bind
variable,
> >rather than the literal ':new' that you want as part of the trigger body.
> >
> > Not sure of a workaround off the top of my head.
>
> Set the command type to adCmdText (1), and it works (at least here it
does).
>
> <%
> Option Explicit
> Response.Buffer = false
>
> Dim oConnection
> Set oConnection = Server.CreateObject("ADODB.Connection")
> oConnection.Open "DSN=testbox;UID=test;PWD=test"
>
> Dim cmdCreateTrigger
> Set cmdCreateTrigger = Server.CreateObject("ADODB.Command")
> Set cmdCreateTrigger.ActiveConnection = oConnection
>
> cmdCreateTrigger.CommandType = 1
>
> cmdCreateTrigger.CommandText = _
> "create or replace trigger tr_test " & chr(10) & _
> "before insert on test " & chr(10) & _
> "for each row " & chr(10) & _
> "begin" & chr(10) & _
> " select testseq.nextval into :new.test_id from dual; " & chr(10) &
_
> "end tr_test;" & chr(10)
>
> cmdCreateTrigger.Execute
> %>
>
> --
> Andy Hassall (andy@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
> Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)

Hi Andy,

I basically tried to do what you have done, but in VB instead of VBScript. I was previously using a Connection.Execute(strSQL) call but that didn't work for these particular triggers.

I've now tried an ADODB.Command object instead, but the results are the same. These particular triggers are not created.

Thanks for your input though.

Cheers.
mf Received on Thu Dec 04 2003 - 16:51:23 CST