Re: Executing SQL Statements throught ADO

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)