So we just went live with a SQL Anywhere 12 database that was migrated from ASA6. I knew I would end up running into some issues with it. I'm sure this is not the only question I will post that have to do with this migration.

We had a Watcom based trigger that was no longer working correctly. I've removed the internal logic since it doesn't apply to the issue. Based on the way it is designed, it is supposed to create a connection level variable the first time it runs, and from that point on until the connection is terminated, that connection level variable exists for that user.

We basically determined if the variable existed by trying to set its value to itself. In ASA6, that would throw an exception. Then in our exception code we would create the variable then run the same code. The problem is that when it breaks to the exception (var_not_found), it doesn't go to that section, it goes to the "When Others" section...

ALTER TRIGGER "tia_get_vwa_child_keys" after insert order 1 on
pa.est_pkg_to_wa_join
referencing new as epwj
for each row
begin
  //
  //    create the needed variables for the trigger
  declare @cnt integer;
  declare @level integer;
  //
  //    determine if variable exists
  declare var_not_found exception for sqlstate value '42w14';
  set bid_pkg_recursion_flag=bid_pkg_recursion_flag;
  //
  //    Determine if we need to find the children to this row or not.
  //    Trigger logic here
  end if
exception
  when var_not_found
  then
    message 'creating bid_pkg_recursion_flag' type info to console;
    create variable bid_pkg_recursion_flag char(1);
    set bid_pkg_recursion_flag='Y';
    //
    //    Create temporary table for collecting keys
    end if
  when others then
    resignal
end

I figured as a way to determine what might be happening, I created a variable in the trigger (declare @myerror CHAR;)

Then in the When Others section of the trigger, I added the line (set @myerror = errormsg();)

ALTER TRIGGER "tia_get_vwa_child_keys" after insert order 1 on
pa.est_pkg_to_wa_join
referencing new as epwj
for each row
begin
  //
  //    create the needed variables for the trigger
  declare @cnt integer;
  declare @level integer;
  declare @myerror CHAR;
  //
  //    determine if variable exists
  declare var_not_found exception for sqlstate value '42w14';
  set bid_pkg_recursion_flag=bid_pkg_recursion_flag;
  //
  //    Determine if we need to find the children to this row or not.
  //    Trigger logic here
  end if
exception
  when var_not_found
  then
    message 'creating bid_pkg_recursion_flag' type info to console;
    create variable bid_pkg_recursion_flag char(1);
    set bid_pkg_recursion_flag='Y';
    //
    //    Create temporary table for collecting keys
    end if
  when others then
    set @myerror = errormsg();
    resignal
end

So when I went to test the trigger again (which had been failing every time I ran it), it all of a sudden started working just like we had initially designed it. The only change had been those two lines of code.

Anybody have any thoughts on what might be going on here?? I hate it when something isn't working, and then just by recomiling it, it starts working again. My fear is that I'll move on to something else and then somebody will start running into this issue again.

For the record, we are running SQL Anywhere 12.0.1 Build 3484

Any ideas would be greatly appreciated!!

TIA

Jeff Gibson
Intercept Solutions - Sybase SQL Anywhere OEM Partner
Nashville, TN

asked 10 Nov '11, 15:16

Jeff%20Gibson's gravatar image

Jeff Gibson
1.8k416172
accept rate: 20%

edited 17 Nov '11, 16:45

What happens if you remove the two lines - does the problem reappear?

BTW: There are two unmatched "end if" lines in each of your sample code - Is this just a cut-and-paste-edit error?

(10 Nov '11, 15:24) Mark Culp
Replies hidden

Removed the two lines I added and the error does not reappear.

The unmatched end if's were my bad. Was just trying to shorten the trigger to only the needed information.

I will post a screen shot of when I was getting the error. Maybe something could be gleaned from that.

Thanks Mark!

Jeff

(10 Nov '11, 16:28) Jeff Gibson
1

Since the error does not reappear when you remove the two lines then I'm thinking that there is a subtle difference in the original version (from your v6 database) and the new version which you created when your altered the definition.

If you could use dbisql to extract the original procedure definition by selecting the proc_defn value from the sysprocedure view and then compare it to the proc_defn value after you have altered it.

e.g. (not tested)

begin
  declare @defn long varchar;
  select trigger_defn
    into @defn
    from systrigger
   where trigger_name = 'tia_get_vwa_child_keys';
  call xp_write_file( 'trig_before.txt', @defn );
end;

Run the above before and after altering the trigger (changing the name of the written file) and then run a diff of the two files.

My guess is that v6 and v12 are interpreting the trigger in slightly different ways (due to subtle parsing rule changes between the two versions). E.g. perhaps v6 interpreted pieces of your trigger as TSQL statements and now they are interpreted as WATCOM SQL? e.g. you have left off semicolons on a number of statements and this could make the statements treated as TSQL in v6? v12 is much more strict about attempting to keep the entire proc/trigger as one dialect.

(10 Nov '11, 21:06) Mark Culp

Have you considered VAREXISTS()?

   IF VAREXISTS ( 'bid_pkg_recursion_flag' ) = 0 THEN
      CREATE VARIABLE bid_pkg_recursion_flag CHAR ( 1 );
      SET bid_pkg_recursion_flag = 'Y';
   END IF;

For an example see http://sqlanywhere.blogspot.com/2008/02/tip-triggering-audit-trail.html

permanent link

answered 10 Nov '11, 15:31

Breck%20Carter's gravatar image

Breck Carter
32.5k5417261050
accept rate: 20%

Thanks for the heads up on the Varexists function Breck!!

Looks like that was released on ASA8. I'm guessing I'm going to dig back through the help files for 7, 8 and 9 to see what other interesting additions are in there!!

Jeff

(10 Nov '11, 16:43) Jeff Gibson
Replies hidden
3

Yeah, there have been one or two things added since V6 :)

(10 Nov '11, 17:54) Breck Carter

Not sure what the story is with the problem cropping back up Breck (see below), but I didn't have the time to try and make what we had work. I went ahead and started converting the triggers and procedurs that referenced the 42w14 exception and changed it so it uses the varexists function.

It's working great!!

Thanks for the heads up on using this function. I'm not sure if the sql processor thinks that the variable reference is a column reference or what.

If you have any ideas on the furthing information I posted, don't hesitate to put it up on here!

Thanks again Breck!!

Jeff

(19 Nov '11, 04:01) Jeff Gibson

I'll do some guesswork. Most of the time when I get the column not found, if it is not actually a column, it is an issue with the quotes around a string that I either forgot, or typed too many so they are misinterpreted. That leads to the system thinking there are not quotes around a string, and as Breck said if it can't figure out what it is, column is its first guess.

It could be part of the setting the variable =

It could think that the part on the right should be a column for some reason and that column is not found.

permanent link

answered 10 Nov '11, 17:57

Siger%20Matt's gravatar image

Siger Matt
3.3k5672101
accept rate: 15%

edited 10 Nov '11, 18:08

Screenshot from our application. On the check of an item in the treeview, an insert is immediately triggered into the table. Before I updated the trigger, this is the error I was seeing on every attempt to use this part of the application.

Since the update, The trigger is working as expected.

Jeff

alt text

permanent link

answered 10 Nov '11, 17:17

Jeff%20Gibson's gravatar image

Jeff Gibson
1.8k416172
accept rate: 20%

1

[Start Extreme Guesswork] FWIW "column not found" and "variable not found" are two different error messages... different SQLCODE and SQLSTATE values, where "variable" means a "CREATE VARIABLE" thing, and "column" is what SQL Anywhere seems to assume for everything else (if it can't find it, it assumes it is a missing column in a table). When the statement "SET x = x" is executed, the right-hand x may be interpreted as a column not found if that's the first thing executed, or the left-hand x may be interpreted as a variable not found if that's what SQL Anywhere evaluates first. Perhaps the engine wobbles between the two execution paths depending on whether it has eaten lately or not.[End Extreme Guesswork]

(10 Nov '11, 17:52) Breck Carter
Replies hidden

And for the record on this Breck. bid_pkg_recursion_flag doesn't even exist as a column. Even did an extra scan of the system tables to see if I had missed something. It's created as a connection level variable in one trigger and used one additional trigger. That's it.

That's some good observations though when it comes to the order of how the trigger is interpreted.

I have original copies of the database from ASA6 and when I got it to SQL Anywhere 12 before I started updating anything. I'm going to extract both in their raw form and place here to see if that might point to something as well!! Thanks for the "EXTREME" guesswork! :)

(14 Nov '11, 01:33) Jeff Gibson

OK. This issue is officially driving me nuts.

Yesterday, I got buzzed by one of our users. The trigger error (that I posted from the above screen shot) had started back up. I tweaked the trigger by making the variable string length longer. Error immediately stopped.

Now fast forward 24 hours to today. I got back into that module so I could test it again. Click on the work area and here comes the error again.

Here is the complete trigger...

ALTER TRIGGER "tia_get_vwa_child_keys" after insert order 1 on
pa.est_pkg_to_wa_join
referencing new as epwj
for each row
begin
  //
  //    create the needed variables for the trigger
  declare @cnt integer;
  declare @level integer;
  declare @myerror varchar(250);
  //
  //    determine if variable exists
  declare var_not_found exception for sqlstate value '42w14';
  set bid_pkg_recursion_flag=bid_pkg_recursion_flag;
  //
  //    Determine if we need to find the children to this row or not.
  //    Reason - If you are in the wizard for a bid package and decide to select a particular work area,
  //    then you will want to select the children.  If you are duplicating selected project types from the bidding wizard, 
  //    then you will not need the child levels selected.
  //    Need to determine this via the bid_pkg_recursion_flag connection level variable.
  //    If = 'N' then do not recurse table
  if bid_pkg_recursion_flag = 'Y' then
    //
    //    Create temporary table for collecting keys
    create table #stack(
      keyseq decimal(16) null,
      thelevel integer null,
      );
    if current remote user is null then
      //
      //  Perform initial insert into the temporary table
      insert into #stack values(epwj.work_area_seq,1);
      set @level=1;
      //
      //    Loop while the level is greater than zero
      mainloop:
      while @level > 0 loop
        if exists(select* from #stack where thelevel = @level) then
          //
          //  Insert items into the temporary table
          insert into #stack(keyseq,thelevel)
            select vwa.work_area_seq,@level+1 from
              pa.ven_work_areas as vwa where
              vwa.work_area_parent_seq = any(select keyseq from #stack where thelevel = @level);
          //
          //  if we have items from previous select then bump the level variable.  If not, then break out of the loop
          if @@rowcount > 0 then
            set @level=@level+1
          else
            leave mainloop
          end if
        else
          //
          //  The initial insert would have had to failed in order to pass through this section
          //  This would immidiatly set the level back to 0
          set @level=@level-1
        end if
      end loop mainloop;
      //
      //  check the result set in #stack to make sure a row is not going to be inserted
      //  that may have been added from another user, or if a child item is selected and then
      //  the parent is inserted after that.
      delete from
        #stack where
        keyseq = any(select epwaj.work_area_seq from
          pa.est_pkg_to_wa_join as epwaj where
          epwaj.est_pkg_seq = epwj.est_pkg_seq);
      //
      //  Set the bid_pkg_recursion_flag equal to 'N' in order to keep the embedded insert from firing its own trigger recursively
      set bid_pkg_recursion_flag='N';
      //  
      //  Insert new set of unique keys
      insert into
        pa.est_pkg_to_wa_join(est_pkg_seq,work_area_seq)
        select epwj.est_pkg_seq,
          keyseq from
          #stack where thelevel > 1;
      //
      //  Set the bid_pkg_recursion_flag back to its default of 'Y'.  This will allow this trigger to fire the next time it needs to be
      set bid_pkg_recursion_flag='Y'
    end if
  end if
exception
  when var_not_found
  then
    message 'creating bid_pkg_recursion_flag' type info to console;
    create variable bid_pkg_recursion_flag char(1);
    set bid_pkg_recursion_flag='Y';
    //
    //    Create temporary table for collecting keys
    create table #stack(
      keyseq decimal(16) null,
      thelevel integer null,
      );
    if current remote user is null then
      //
      //  Perform initial insert into the temporary table
      insert into #stack values(epwj.work_area_seq,1);
      set @level=1;
      //
      //    Loop while the level is greater than zero
      mainloop2:
      while @level > 0 loop
        if exists(select* from #stack where thelevel = @level) then
          //
          //  Insert items into the temporary table
          insert into #stack(keyseq,thelevel)
            select vwa.work_area_seq,@level+1 from
              pa.ven_work_areas as vwa where
              vwa.work_area_parent_seq = any(select keyseq from #stack where thelevel = @level);
          //
          //  if we have items from previous select then bump the level variable.  If not, then break out of the loop
          if @@rowcount > 0 then
            set @level=@level+1
          else
            leave mainloop2
          end if
        else
          //
          //  The initial insert would have had to failed in order to pass through this section
          //  This would immidiatly set the level back to 0
          set @level=@level-1
        end if
      end loop mainloop2;
      //
      //  check the result set in #stack to make sure a row is not going to be inserted
      //  that may have been added from another user, or if a child item is selected and then
      //  the parent is inserted after that.
      delete from
        #stack where
        keyseq = any(select epwaj.work_area_seq from
          pa.est_pkg_to_wa_join as epwaj where
          epwaj.est_pkg_seq = epwj.est_pkg_seq);
      //
      //  Set the bid_pkg_recursion_flag equal to 'N' in order to keep the embedded insert from firing its own trigger recursively
      set bid_pkg_recursion_flag='N';
      //  
      //  Insert new set of unique keys
      insert into
        pa.est_pkg_to_wa_join(est_pkg_seq,work_area_seq)
        select epwj.est_pkg_seq,
          keyseq from
          #stack where thelevel > 1;
      //
      //  Set the bid_pkg_recursion_flag back to its default of 'Y'.  This will allow this trigger to fire the next time it needs to be
      set bid_pkg_recursion_flag='Y'
    end if
  when others then
    set @myerror = errormsg();
    resignal
end

Here is a screen shot of the debugger when it hit the breakpoint in the trigger...

alt text

You can clearly see that the error returned references the variable as a column. Which it's not.

I'm beginning to think that the only way around this is to use the VAREXISTS function and modify all these triggers.

My question is... Why is it if I recomiple the trigger with a small change, that it starts working like it should? And then 24 hours later it stops working!!

Any thoughts on what's happening here would be appreciated. This has got to be one of the strangest errors I've ran into in SQL Anywhere.

TIA!!

Jeff Gibson
Intercept Solutions - Sybase SQL Anywhere OEM Partner
Nashville, TN

permanent link

answered 17 Nov '11, 16:43

Jeff%20Gibson's gravatar image

Jeff Gibson
1.8k416172
accept rate: 20%

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:

×63
×53
×7

question asked: 10 Nov '11, 15:16

question was seen: 3,629 times

last updated: 19 Jan '12, 06:30