Hi Tim,

The following code was generated by the Compare Database statement:

ALTER TABLE "Messages"
ADD COLUMN "MessageLogged" TIMESTAMP;

ALTER TEXT INDEX "CallerIndex" ON "Messages" ("Caller" COLLATE "UNI_CI")
FILTER TYPE COLUMN "Caller";

ALTER TEXT INDEX "SubjectIndex" ON "Messages" ("Subject" COLLATE "UNI_CI")
FILTER TYPE COLUMN "Subject";

ALTER TEXT INDEX "CompanyIndex" ON "Messages" ("Company" COLLATE "UNI_CI")
FILTER TYPE COLUMN "Company";

CREATE TRIGGER "MessageLogged" AFTER INSERT ON "Messages"
BEGIN
  DECLARE Result CURSOR FOR SQLStatement;   
  DECLARE MessageID INTEGER;
  DECLARE MessageLogged TIMESTAMP;

  SET MessageID = NEWROW.MessageID;
  SET MessageLogged = CURRENT_TIMESTAMP;

  PREPARE SQLStatement FROM
     'UPDATE Messages
         SET MessageLogged = ?
       WHERE MessageID = ?';

  EXECUTE SQLStatement USING MessageLogged, MessageID;

END;

When run it produces this error: "ElevateDB Error #700 An error was found in the statement at line 4 and column 1 (Expected end of expression but instead found ALTER)"

= Steve

Steve


I don't know where or how you're trying to run it but I would suspect the terminating ;

If you're running it in EDBManager what's the terminator character set to - the default is !

Roy Lambert

Hi Roy,

<< I don't know where or how you're trying to run it but I would suspect the terminating ;

If you're running it in EDBManager what's the terminator character set to - the default is !  >>

It's being run from an application using a query component.  After Compare Database is done, it iterates through SchemaDifference, running each statement/row in turn.  It is running through around 30 rows.  All of the statements run successfully except this one.  

For some reason this particular row has multiple statements in it whereas all of the others have only one statement.

= Steve

Steve


I think I know what you're talking about now. I've necer used COMPARE DATABASE so its the blind leading the blind here. From the manual


<<If not specified, the statement terminator character defaults to the exclamation character ('!'). If an
object requires multiple statements for creating, altering, or dropping sub-objects (such as
indexes/triggers for tables), then the statement terminator character will be used for separating the
multiple statements in the AlterSQL CLOB column of the SchemaDifference table.>>

The manual didn't say explicitly but I'm guessing there's one row in SchemaDifference for each table and within that CLOB column may be multiple rows of SQL.

You'll need to split those rows up manually, but before you do that make sure the separator character isn't ; or you'll stuff the trigger.

Roy Lambert

Steve,

<< The following code was generated by the Compare Database statement: >>

Yes, you will sometimes get multiple statements in the SchemaDifference table for a single database object.  To make this work in your own code, copy what I'm doing in the reverse-engineering code for the EDB Manager, which you'll find in the edbutilcomps.pas unit provided in the \utilcomps sub-directory under the main installation directory.

The key part is this:

function TEDBReverseEngineer.AlterExistingSchema: Boolean;
var
  TempQuery: TEDBQuery;
  TempStatement: Integer;
  TempProgress: Integer;
  TempSQL: String;
  TempPos: Integer;
  TempSQLStatement: String;
  TempTableName: String;
  TempViewName: String;
  TempFunctionName: String;
  TempProcedureName: String;
begin
  Result:=True;
  TempQuery:=TEDBQuery.Create(nil);
  try
     with TempQuery do
        begin
        SessionName:=FSourceDatabase.SessionName;
        DatabaseName:=FSourceDatabase.DatabaseName;
        RequestSensitive:=True;
        SQL.Text:='SELECT * FROM Information.SchemaDifference';
        Open;
        try
           if (RecordCount > 0) then
              begin
              First;
              TempStatement:=1;
              while (not EOF) do
                 begin
                 if (FieldByName('Type').AsString='Table') then
                    begin
                    TempTableName:=FieldByName('Name').AsString;
                    DoStatusMessage('Reverse-engineering table '+QuotedString(TempTableName,'"'));
                    AddSQLStatus('Altering table '+QuotedString(TempTableName,'"'));
                    TempSQL:=FieldByName('AlterSQL').AsString;
                    TempPos:=1;
                    TempSQLStatement:=LTrimString(NextSQLStatement(TempSQL,TempPos,FStatementTerminator),CRLF);
                    while (TempSQLStatement <> '') do
                       begin
                       AddSQLStatement(TempSQLStatement);
                       TempSQLStatement:=LTrimString(NextSQLStatement(TempSQL,TempPos,FStatementTerminator),CRLF);
                       end;
                    end
                 else if (FieldByName('Type').AsString='View') then
                    begin
                    TempViewName:=FieldByName('Name').AsString;
                    DoStatusMessage('Reverse-engineering view '+QuotedString(TempViewName,'"'));
                    TempSQL:=FieldByName('AlterSQL').AsString;
                    AddSQLStatus('Altering view '+QuotedString(TempViewName,'"'));
                    AddSQLStatement(TempSQL);
                    end
                 else if (FieldByName('Type').AsString='Function') then
                    begin
                    TempFunctionName:=FieldByName('Name').AsString;
                    DoStatusMessage('Reverse-engineering function '+QuotedString(TempFunctionName,'"'));
                    TempSQL:=FieldByName('AlterSQL').AsString;
                    AddSQLStatus('Altering function '+QuotedString(TempFunctionName,'"'));
                    AddSQLStatement(TempSQL);
                    end
                 else if (FieldByName('Type').AsString='Procedure') then
                    begin
                    TempProcedureName:=FieldByName('Name').AsString;
                    DoStatusMessage('Reverse-engineering procedure '+QuotedString(TempProcedureName,'"'));
                    TempSQL:=FieldByName('AlterSQL').AsString;
                    AddSQLStatus('Altering procedure '+QuotedString(TempProcedureName,'"'));
                    AddSQLStatement(TempSQL);
                    end;
                 TempProgress:=Integer(Trunc((TempStatement/RecordCount)*100));
                 AddSQLProgress(TempProgress);
                 Result:=DoProgress(TempProgress);
                 if (not Result) then Exit;
                 Next;
                 Inc(TempStatement);
                 end;
              end;
     finally
        Close;
     end;
     end;
  finally
     FreeAndNil(TempQuery);
  end;
end;

The NextSQLStatement function is in the edbstring.pas unit, and it will handle all of the parsing properly for you.

Tim Young
Elevate Software
www.elevatesoft.com

<< The manual didn't say explicitly but I'm guessing there's one row in SchemaDifference for each table and within that CLOB column may be multiple rows of SQL.

You'll need to split those rows up manually, but before you do that make sure the separator character isn't ; or you'll stuff the trigger. >>

Thanks Roy, I didn't realise that a row could have multiple statements.

= Steve

Thanks Tim, that's fantastic.

= Steve

Hi Tim,

I've tried implementing the code changes as per your example, but when I check the log I create I'm getting a lot of these errors:  "ElevateDB Error #1007 The row has been deleted since last cached for the temporary table SchemaDifference".

I also get this error message occasionally:

"ElevateDB Error #700 An error was found in the statement at line 10 and column 27 (Expected PRIMARY, UNIQUE, FOREIGN, REFERENCES, CHECK but instead found !)"

This is the generated SQL that is causing the error:

ALTER TABLE "SystemEmailSettings"
ADD COLUMN "EHLO" BOOLEAN DEFAULT False,
ADD COLUMN "SASL" BOOLEAN,
ADD COLUMN "SASLOnly" BOOLEAN,
ADD COLUMN "UseTLS" INTEGER,
ADD COLUMN "SSL2" BOOLEAN,
ADD COLUMN "SSL3" BOOLEAN,
ADD COLUMN "TLS" BOOLEAN,
ADD COLUMN "TLS11" BOOLEAN,
ADD COLUMN "TLS12" BOOLEAN!

= Steve

Hi Tim,

I tried setting RequestSensitive to False and I no longer get this error: "ElevateDB Error #1007 The row has been deleted since last cached for the temporary table SchemaDifference".

However, I still get the this error (3 occurrences): ElevateDB Error #700 An error was found in the statement at line 2 and column 37 (Expected PRIMARY, UNIQUE, FOREIGN, REFERENCES, CHECK but instead found !)

Not sure where the exclamation mark comes from.  Is this something that code generation adds?

= Steve

I think my eyes are going on me.  The ! (exclamation mark) looked like a | (pipe) to me.  Once I changed the code to ! it all works fine, as long as I leave RequestSensitive = False.

= Steve