The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

From: Irene Yu

Newsgroups: sybase.public.sqlanywhere.general

Subject: Correlation error in trigger

Date: 6 Nov 2010 01:17:46 -0800

Good day,

Have a script being used in triggers in 3 tables and it works fine except for one table wheren it's giving an error "Correlation 'new_name' not found"

Script:

REFERENCING OLD AS old_name NEW AS new_name
FOR EACH ROW

Set @ls_sql_old = 'set @ls_old_char_value =
convert(char(200), old_name.'+@ls_column_name + ');';
Set @ls_sql_new = 'set @ls_new_char_value =
convert(char(200), new_name.'+@ls_column_name +');' ;

Execute immediate @ls_sql_new;
Execute immediate @ls_sql_old;

The @ls_column_name holds the column name which is fetched from another table in a cursor.

Error occurs in the execute immediate script.

I have tried adding specific statements just right before the execute immediate script which will have the same statement at one point in both @ls_sql_new & @ls_sql_old statements, like:

set @ls_old_char_value = convert(char(200),
old_name.lastname);
set @ls_new_char_value = convert(char(200),
new_name.lastname);

If cursor fetches @ls_column_name = 'lastname', it will have no problem running the execute immediate scripts. However on the next loop wherein @ls_column_name holds another column name, it will raise the error "Correlation 'new_name' not found"

Using ASA 9.0.2.3044

Would greatly appreciate any insight.

Thanks.

asked 07 Nov '10, 10:33

Breck%20Carter's gravatar image

Breck Carter
26.6k418576824
accept rate: 21%


This looks like a deficiency in version 9.0.2. The problem remains in 9.0.2.3575 and is gone in 10.0.1.3415.

Here is a reproducible test case:

CREATE TABLE t (
   key_1          INTEGER NOT NULL PRIMARY KEY,
   non_key_1      VARCHAR ( 100 ) NOT NULL,
   non_key_2      VARCHAR ( 100 ) NOT NULL,
   non_key_3      VARCHAR ( 100 ) NOT NULL );

INSERT t VALUES ( 1, 'aaa', 'bbb', 'ccc' );
COMMIT;

CREATE VARIABLE @ls_sql_new        LONG VARCHAR;
CREATE VARIABLE @ls_new_char_value LONG VARCHAR;
CREATE VARIABLE @ls_column_name    LONG VARCHAR;

CREATE TRIGGER tru_t BEFORE UPDATE ON t
REFERENCING OLD AS old_name NEW AS new_name
FOR EACH ROW
BEGIN

Set @ls_sql_new 
      = 'set @ls_new_char_value = convert(char(200), new_name.'
      + @ls_column_name 
      + ');' ;

MESSAGE STRING ( 'DIAG ', CURRENT TIMESTAMP, ' @ls_sql_new = "', @ls_sql_new, '"' ) TO CONSOLE;

Execute immediate @ls_sql_new;

END;

SET @ls_column_name = 'non_key_2';

UPDATE t 
   SET non_key_1 = 'xxx', 
       non_key_2 = 'yyy' 
 WHERE key_1 = 1;

MESSAGE STRING ( 'DIAG ', CURRENT TIMESTAMP, ' @ls_column_name    = "', @ls_column_name, '"' ) TO CONSOLE;
MESSAGE STRING ( 'DIAG ', CURRENT TIMESTAMP, ' @ls_new_char_value = "', @ls_new_char_value, '"' ) TO CONSOLE;

SET @ls_column_name = 'non_key_1';

UPDATE t 
   SET non_key_1 = 'ppp', 
       non_key_2 = 'qqq' 
 WHERE key_1 = 1;

MESSAGE STRING ( 'DIAG ', CURRENT TIMESTAMP, ' @ls_column_name    = "', @ls_column_name, '"' ) TO CONSOLE;
MESSAGE STRING ( 'DIAG ', CURRENT TIMESTAMP, ' @ls_new_char_value = "', @ls_new_char_value, '"' ) TO CONSOLE;

Here are the test results...

Adaptive Server Anywhere Database Engine Version 9.0.2.3575

Correlaton name 'new_name' not found
SQLCODE=-142, ODBC 3 State="42S02"

SQL Anywhere Personal Server Version 10.0.1.3415

DIAG 2010-11-07 05:26:16.390 @ls_sql_new = "set @ls_new_char_value = convert(char(200), new_name.non_key_2);"
DIAG 2010-11-07 05:26:16.406 @ls_column_name    = "non_key_2"
DIAG 2010-11-07 05:26:16.406 @ls_new_char_value = "yyy"
DIAG 2010-11-07 05:26:16.421 @ls_sql_new = "set @ls_new_char_value = convert(char(200), new_name.non_key_1);"
DIAG 2010-11-07 05:26:16.421 @ls_column_name    = "non_key_1"
DIAG 2010-11-07 05:26:16.437 @ls_new_char_value = "ppp"

SQL Anywhere Personal Server Version 11.0.1.2276

DIAG 2010-11-07 05:24:56.859 @ls_sql_new = "set @ls_new_char_value = convert(char(200), new_name.non_key_2);"
DIAG 2010-11-07 05:24:56.875 @ls_column_name    = "non_key_2"
DIAG 2010-11-07 05:24:56.875 @ls_new_char_value = "yyy"
DIAG 2010-11-07 05:24:56.890 @ls_sql_new = "set @ls_new_char_value = convert(char(200), new_name.non_key_1);"
DIAG 2010-11-07 05:24:56.890 @ls_column_name    = "non_key_1"
DIAG 2010-11-07 05:24:56.906 @ls_new_char_value = "ppp"

SQL Anywhere Personal Server Version 12.0.0.2589

DIAG 2010-11-07 05:23:06.593 @ls_sql_new = "set @ls_new_char_value = convert(char(200), new_name.non_key_2);"
DIAG 2010-11-07 05:23:06.609 @ls_column_name    = "non_key_2"
DIAG 2010-11-07 05:23:06.609 @ls_new_char_value = "yyy"
DIAG 2010-11-07 05:23:06.609 @ls_sql_new = "set @ls_new_char_value = convert(char(200), new_name.non_key_1);"
DIAG 2010-11-07 05:23:06.625 @ls_column_name    = "non_key_1"
DIAG 2010-11-07 05:23:06.625 @ls_new_char_value = "ppp"
permanent link

answered 07 Nov '10, 10:39

Breck%20Carter's gravatar image

Breck Carter
26.6k418576824
accept rate: 21%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×101

question asked: 07 Nov '10, 10:33

question was seen: 1,033 times

last updated: 07 Nov '10, 10:39