Error 7200/4004 after upgrading to ADS v11

[doug_woodrow]

I've run into a serious problem after upgrading a client from Advantage v8 to v11, and would appreciate it if you could see what is causing this error, and hopefully come up with a fix for it.

I've been able to replicate the problem with a very small example:

Step 1: Run this SQL to create table BAD.DBF:

= BOF =============================

/* Table Type of Bad.dbf is CDX*/

Create Table Bad(

   PO Char( 9 ),

   GRADINGORD Numeric( 2 ,0 )

);

Create Index PO on Bad( PO );

//Create Index DPONUM on Bad( );

= EOF =============================

The "Export table structure" utility in Arc32 doesn't cope with this next index expression, but it can be created manually in Arc32:

Step 2: use Arc32 to manually create an index called "DPONUM", with expression: "Descend(SubStr(po,4))"

Step 3: try to update the table by running the following SQL:

INSERT INTO "Bad" VALUES( 'X00259008', NULL );

Result:

poQuery: Error 7200:  AQE Error:  State = HY000;   NativeError = 4004;  [iAnywhere Solutions][Advantage SQL][ASA] Error 4004:  Advantage Expression Engine evaluator stack overflow.

[doug_woodrow]

I've found a fix:

change the index expression to "Descend(SubStr(po,4,6))".

[doug_woodrow]

Sorry, I omitted the SQL that causes the error:

update Bad

set gradingord=6

where po='X00259008';