Progress 9.1 Triggers & procedures [Archive]

J.Raum

07-16-02, 08:49

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

MVijendraD

10-21-08, 06:42

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)

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