DO NOT REPLY [Bug 30760] - sql task handle pl/sql with ';' in pl/sql code. (patch)

[bugzilla]

DO NOT REPLY TO THIS EMAIL, BUT PLEASE POST YOUR BUG 
RELATED COMMENTS THROUGH THE WEB INTERFACE AVAILABLE AT
<http://issues.apache.org/bugzilla/show_bug.cgi?id=30760>.
ANY REPLY MADE TO THIS MESSAGE WILL NOT BE COLLECTED AND 
INSERTED IN THE BUG DATABASE.

http://issues.apache.org/bugzilla/show_bug.cgi?id=30760

sql task handle pl/sql with ';' in pl/sql code. (patch)

[EMAIL PROTECTED] changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
         OS/Version|Linux                       |All



------- Additional Comments From [EMAIL PROTECTED]  2004-09-10 18:32 -------
Ok, now i have moved beyond Postgresql and i'm now working with Postgresql,
Sybase, and SQL Server.

POSTGRES:
With Postgres i was able to remove my patch and use a workaround to get the
<sql> task to feed the entire .sql file as a single statement to the database
(by setting the delimiter to a string that never occurs in the sql script) and
letting Postgres determine where one statement began and a new one ended.

SYBASE:
The same senario works.

SQL SERVER:
The work around fails.  This is because SQL Server complains that any CREATE
VIEW statements should be sent one at a time.

SO... I revisted patching the <sql> task to support delimiting the statements
once again.  Now that i have 3 databases to use as examples, i can now make sure
that i'm not creating a one off for one database vendor (which my original
quoted expression patch was).

My new patch works similar to the first one i submitted, except now instead of
checking for quoted expression (postgres specific), i check for BLOCK
statements.  A block statement in this case is defined as one that starts with
BEGIN and ends with END.  It also checks for nesting of other BEGIN, END pairs
to prevent sending a statement fragment to the datbase.  The only requirement is
that BEGIN and END occur on a line of there own.

SO, now the following work for Postgresql, Sybase, AND Sql Server (Oracle to be
tested here next week).

POSTGRES: (only change to my original example is that the final END no longer
has a trailing ';'
 
  CREATE OR REPLACE FUNCTION test () RETURNS TRIGGER AS '
    DECLARE
      theTime timestamp;
    BEGIN
      theTime := ''now'';
      UPDATE TheTable
        SET theTime = theTime
        WHERE tableId = NEW.tableId;
      RETURN NEW;
    END
  ' LANGUAGE 'plpgsql';

SYBASE:

  CREATE TRIGGER BI_Test
  BEFORE UPDATE ON TestTable
  REFERENCING NEW AS newRow
  FOR EACH ROW
  BEGIN
    UPDATE TheTable
      SET theTime = (SELECT GETDATE())
      WHERE tableId = newRow.tableId;
  END;

SQL SERVER:

  CREATE TRIGGER AI_Test
  ON TestTable
  AFTER INSERT AS
  BEGIN
    DECLARE @tableId NUMERIC(16);
    SELECT @tableId = (SELECT tableId FROM Inserted);
    Update TheTable set theTime = (SELECT GETDATE()) WHERE tableId = @tableId;
  END;

---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]