Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2002
    Location
    Germany
    Posts
    4

    Unanswered: Progress 9.1 Triggers & procedures

    Hi,

    I try to create a trigger using the Progress SQL Explorer as follows:

    CREATE TRIGGER DEL_test BEFORE DELETE ON TEST1 REFERENCING OLDROW
    FOR EACH ROW
    IMPORT java.sql.*;
    BEGIN
    int testid_value;
    testid_value = OLDROW.getValue(1, INTEGER);
    SQLIStatement delStatement (
    "DELETE FROM test2 WHERE testid = ?");

    delStatement.setParam(1, testid_value);
    delStatement.Execute();
    END

    If I try to execute this statement I get an error message

    === SQL Exception 1 ===
    SQLState=HY000
    ErrorCode=-20141
    [JDBC Progress Driver]:error in compiling the stored procedure

    I always get this error message when I try to create a trigger or a procedure even if the body of the trigger/proc is empty.

    How could that be ?!

    Does it depend on the system configuration ( on the env-variable CLASSPATH, or whatever) or is it just my fault ?!

    I'm using Progress Enterprise DB v9.1D / SQL Explorer @ Win2k Pro

    Help !

    Thanks in advance

  2. #2
    Join Date
    Nov 2002
    Posts
    3
    try without import
    or else

    CREATE ...
    ...
    ...
    FOR EACH ROW

    IMPORT
    import java.sql.*;

    BEGIN
    ...
    ...
    ...
    END

  3. #3
    Join Date
    Oct 2008
    Location
    Bangalore
    Posts
    3

    [JDBC Progress Driver]:error in compiling the stored procedure

    When I run the below SP in Progress SQL Exlorer I too get the same error but not sure why?

    Can someone help me out?

    Below is the SP

    PS: The same query works in old server with 9.1D and the new one has 9.1E.

    Rgds,
    M Vijendra
    --------------------------------------------------------------------------

    === Statement 1. ===
    drop procedure spiCoinew;


    === Statement 2. ===
    commit;


    === Statement 3. ===
    create procedure spiCoinew (
    in co_num varchar(15) not null,
    in cust_num varchar(10) not null,
    in cust_seq int not null,
    in stat varchar(1) not null,
    in co_line int not null,
    in item varchar(30) not null,
    in qty_ordered int not null,
    in price decimal(10,2) not null,
    in tax_code1 varchar(6) not null,
    in tax_code2 varchar(6) not null,
    in due_date date not null,
    in disc decimal(3,2) not null,
    in whse varchar(4) not null,
    in locn varchar(3) not null,
    in div varchar(2) not null,
    in dtp decimal(10,2) not null,
    in cust_item varchar(3) not null
    )
    begin
    String c1 = new String("\"co-num\", ");
    String c2 = new String("\"cust-num\", ");
    String c3 = new String("\"cust-seq\", ");
    String c4 = new String("stat, ");
    String c5 = new String("\"co-line\", ");
    String c6 = new String("item, ");
    String c7 = new String("\"qty-ordered\", ");
    String c8 = new String("price, ");
    String c9 = new String("\"tax-code1\", ");
    String c10 = new String("\"tax-code2\", ");
    String c11 = new String("\"due-date\", ");
    String c12 = new String("disc, ");
    String c13 = new String("whse, ");
    String c14 = new String("\"price-conv\", ");
    String c15 = new String("\"qty-ordered-conv\", ");
    String c16 = new String("\"qty-invoiced\", ");
    String c17 = new String("\"ref-type\", ");
    String c18 = new String("\"u-m\", ");
    String c19 = new String("\"key\", ");
    String c20 = new String("\"locn\", ");
    String c21 = new String("\"div\", ");
    String c22 = new String("\"cust-item\", ");
    String c23 = new String("\"dtp\" ");
    String c30 = new String("\"key\" ");
    Integer iKey = new Integer(0);
    String sConum = new String("\"co-num\" ");
    String sColine = new String("\"co-line\" ");
    SQLCursor sItem = new SQLCursor
    ( "select " + c30 + " from pub.item where item = ?");
    sItem.setParam (1, item);
    sItem.open ();
    sItem.fetch ();
    if ( sItem.found() )
    {
    iKey = (Integer) sItem.getValue (1, INTEGER);
    }
    sItem.close ();
    SQLCursor sCoitem = new SQLCursor
    ( "select " + sConum + " from pub.coitem where " + sConum + " = ? and " + sColine + " = ? ");
    sCoitem.setParam (1, co_num);
    sCoitem.setParam (2, co_line);
    sCoitem.open ();
    sCoitem.fetch ();
    if ( ! sCoitem.found() )
    {
    SQLIStatement iCoitem= new SQLIStatement
    ( "INSERT INTO pub.coitem(" + c1 + c2 + c3 + c4 + c5 + c6 + c7 + c8 + c9 + c10 + c11 + c12 + c13 + c14 + c15 + c16 + c17 + c18 + c19 + c20 + c21 + c22 + c23 + ") values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? ,?) " );
    iCoitem.setParam (1, co_num);
    iCoitem.setParam (2, cust_num);
    iCoitem.setParam (3, cust_seq);
    iCoitem.setParam (4, stat);
    iCoitem.setParam (5, co_line);
    iCoitem.setParam (6, item);
    iCoitem.setParam (7, qty_ordered);
    iCoitem.setParam (8, price);
    iCoitem.setParam (9, tax_code1);
    iCoitem.setParam (10, tax_code2);
    iCoitem.setParam (11, due_date);
    iCoitem.setParam (12, disc);
    iCoitem.setParam (13, whse);
    iCoitem.setParam (14, price);
    iCoitem.setParam (15, qty_ordered);
    iCoitem.setParam (16, "0");
    iCoitem.setParam (17, "I");
    iCoitem.setParam (18, "NO");
    iCoitem.setParam (19, iKey);
    iCoitem.setParam (20, locn);
    iCoitem.setParam (21, div);
    iCoitem.setParam (22, cust_item);
    iCoitem.setParam (23, dtp);
    iCoitem.execute();
    }
    sCoitem.close();
    end;
    === SQL Exception 1 ===
    SQLState=HY000
    ErrorCode=-20141
    [JDBC Progress Driver]:error in compiling the stored procedure

    Errors 1.

    === Statement 4. ===
    commit work;


    Statements: 4; Updates 0; Rows 0; Errors: 1; Warnings: 0. (8926)

    --------------------------------------------------------------------------

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •