For one reason or another, I want to update a lot of columns in NEWROW - several hundred; it's a huge record - and want to do this in a loop, iterating over the columns and using SET NEWROW.????? as appropriate. However, of course I can't substitue the name of the column into the SQL/PSM code within the trigger. Or can I?

Is this achievable?

Here's my code so far:-

BEGIN
  DECLARE IX INTEGER DEFAULT 1;
  DECLARE NUMCOLS INTEGER;
  DECLARE COLNAME, MMO_COLNAME, MMO_VALUE, MMO_STMT VARCHAR;

  DECLARE SHIPDATA_CURS CURSOR FOR STMT;

  PREPARE STMT FROM 'TABLE SHIPDATA';
  OPEN SHIPDATA_CURS;     

  SET NUMCOLS = COLUMNCOUNT(SHIPDATA_CURS);

  WHILE IX <= NUMCOLS DO
     SET COLNAME = COLUMNNAME(SHIPDATA_CURS, IX);
     IF UPPER(LEFT(COLNAME, 4)) = 'MMO_' THEN
        SET MMO_COLNAME = SUBSTRING(COLNAME FROM 5 FOR 64); /* REMOVE mmo_ PREFIX */
        SET MMO_STMT = 'SET NEWROW.'+COLNAME+' = ''ABC''';
        /* What now??? */

        /* EXECUTE IMMEDIATE MMO_STMT; */
     END IF;

     SET IX = IX + 1;
  END WHILE;

  CLOSE SHIPDATA_CURS;

END

Charles


EXECUTE IMMEDIATE expects a valid sql string to execute.so all you have to do is give it a valid string and away it will go.

I hope I'm not insulting you when I say you don't seem to have sussed parts of ElevateDB yet. You can get the field you want far simpler by using the Information system tables.

Have a look at the following in EDBManager

SELECT Name FROM Information.TableColumns WHERE TableName = 'SHIPDATA' AND Name LIKE 'MMO_%'

You can declare and open that cursor in your trigger and that gives you the list of names you need. You can then use FETCH to get the name into your variable, use that to build the statement and use EXECUTE IMMEDIATE.

Have a look at the FETCH example in the OLH for EDBManager that shows you pretty much everything you need (and it save me creating and typing one

Roy Lambert

Thanks Roy, getting the column names isn't the problem, it's constructing the SET NEWROW.??? bit that's got me stumped. I can construct the statement OK, but EXECUTE IMMEDIATE won't have it:-

<< ElevateDB Error #700 An error was found in the statement... (Expected BACKUPS, FILES, UPDATES, INFORMATION, MIGRATOR but instead found NEWROW) >>

On the face of it, the column name must be a literal value in the script; it cannot be a variable value determined at run-time.

Charles


For some reason I missed the NEWROW bit. That can't be passed into an EXECUTE IMMEDIATE. Effectively an EXECUTE IMMEDIATE creates a query, sets its SQL parameter to whatever you've given it, executes the query and then closes down. NEWROW is only valid within the context of the trigger.

In theory you could use EXECUTE IMMEDIATE without NEWROW but thinking about it some more since you're trying to update the same table using EXECUTE IMMEDIATE will probably fire the trigger again and you don't have anything to stop a a loop.

I think you're hitting against the same problem as not being able to use parameters for column names in a query. Nothing occurs to me instantly but I'm going to have a play - don't hold your breath.

The only positive suggestion is - if you have any support tickets left in your plan - ask Tim directly.


Roy Lambert

Charles


I'm just having a hunt through the newsgroups and found this from 2009

-------------------------------------------------------------------------------------------------------
<< I thought of that, but wasn't sure the text reduction vs cut'n'paste with
a bit of edit would be worth any performance loss. I guess I'm spoilt with
Delphi. I started thinking of using a procedure, passing in a table simply
looping round the Fields. About 20 lines in all. I'll have to wait for Tim
to allow us to loop round NEWROW and OLDROW I guess. >>

That stuff will be coming with the array (done) and row (almost done)
support.  I'll still need to add a way to reference columns dynamically, but
that should be doable.
---------------------------------------------------------------------------------------------------------

Its a reply from Tim to a question of mine similar to your problem.

I've done a bit of experimenting and can say that EXECUTE IMMEDIATE just won't work because the row you want to set the NEWROW values for is locked because you're updating it

The only thought that's occurred so far is that if the table structure isn't subject to much change you could use Delphi and the Information tables to create the trigger programatically. The only problem with that is that you need the table for exclusive access whilst doing it.


Roy Lambert