Devart Forums

Posted: **Tue 18 Nov 2014 14:57**

Hey there.
I have problems using the TIBCLoader to copy tables from database A to database B. The table is identically defined in both databases. This is the rough sketch of things:

Code: Select all

procedure TForm4.Button10Click(Sender: TObject);
var
  sourceconn, destConn : TIBCConnection;
  QSource : TIBCQuery;
  IBCLoader : TIBCLoader;
  tablename : STring;
  i : integer;
begin
  sourceConn := TIBCConnection.Create(nil);
  destConn := TIBCConnection.Create(nil);

  sourceConn.ClientLibrary  := 'gds32.dll';
  sourceConn.Database       :=  someDatabasePath;
  sourceConn.Password       := 'masterkey';
  sourceConn.SQLDialect     := 3;
  sourceConn.Username       := 'sysdba';
  sourceConn.Connected      := True;
  sourceConn.Options.UseUnicode := True;

  destConn.ClientLibrary  := 'gds32.dll';
  destConn.Database       :=  someOtherDatabasePath;
  destConn.Password       := 'masterkey';
  destConn.SQLDialect     := 3;
  destConn.Username       := 'sysdba';
  destConn.Connected      := True;
  destConn.Options.UseUnicode := True;

  QSource := TIBCQuery.Create(nil);
  QSource.Connection := sourceconn;

  tablename := 'PROJECT';
  QSource.sql.Add('SELECT * FROM ' + tablename);
  QSource.Open;

  IBCLoader := TIBCLoader.Create(nil);
  IBCLoader.TableName := tablename;
  IBCLoader.InsertMode := imUpdateOrInsert;
  IBCLoader.Connection := destConn;
  IBCLoader.AutoCommit := False;
  IBCLoader.Options.QuoteNames := True;
  try
    if QSource.RecordCount > 0 then begin
      IBCLoader.RowsPerBatch := 10;
      IBCLoader.LoadFromDataSet(QSource);
    end;
  except
    on E: Exception do begin
      Application.ProcessMessages;
      messageDlg('Error: ' + E.Message,mtError,[mbOk], 0);
    end;

  end;

end;

in pseudo-code:
1) Get connections to database A and B
2) use a query to retrieve the data to be moved from database A
3) setup an IBCLoader to have destination B, then use LoadFromDataset to move the data

Executing this, I get the following error:
Dynamic SQL Error
SQL error code = -104
Token unknown - line 1, column 663
OR

I am running version 5.2.6 on Delphi XE

Posted: **Wed 19 Nov 2014 09:00**

Unfortunately, we could not reproduce the issue. Please upgrade to the latest version of IBDAC (5.4.11) and check if the problem still persists. If this doesn't help solve the problem, please send us a small sample to demonstrate the issue, including a script to create and fill test database tables.

Posted: **Tue 23 Dec 2014 16:54**

Sadly, the problem persists after updating to IBDAC 5.4.12..

I've made a small procedure that creates and fills the tables, then runs IBCLoader to copy the source table contents into the destination table.
I only see the error when IBCLoader is in imUpdateOrInsert mode.

Code: Select all

procedure TForm4.Button10Click(Sender: TObject);
var
  sourceconn, destConn : TIBCConnection;
  QSource : TIBCQuery;
  QDest : TIBCQuery;
  source, dest : STring;
  IBCLoader : TIBCLoader;
  tablename : STring;
  i : integer;
begin
  source := 'e:\Shares\bjarke.moeholt\SCIExport\WS3_Test_1_.gdb'; //use your own database files here..
  dest := 'e:\Shares\bjarke.moeholt\SCIExport\WS4_Test_2_.gdb';

  //setup Queries and Connections
  sourceConn := TIBCConnection.Create(nil);
  destConn := TIBCConnection.Create(nil);

  sourceConn.ClientLibrary  := 'gds32.dll';
  sourceConn.Database       :=  source;
  sourceConn.Password       := 'masterkey';
  sourceConn.SQLDialect     := 3;
  sourceConn.Username       := 'sysdba';
  sourceConn.Connected      := True;
  sourceConn.Options.UseUnicode := True;

  destConn.ClientLibrary  := 'gds32.dll';
  destConn.Database       :=  dest;
  destConn.Password       := 'masterkey';
  destConn.SQLDialect     := 3;
  destConn.Username       := 'sysdba';
  destConn.Connected      := True;
  destConn.Options.UseUnicode := True;

  QSource := TIBCQuery.Create(nil);
  QSource.Connection := sourceconn;
  QDest := TIBCQuery.Create(nil);
  QDest.Connection := destConn;

  //drop the tables..
  try
    QSource.SQL.Clear;
    QSource.SQL.Add('DROP TABLE MOEDITION');
    QSource.ExecSQL;
  except
    //silent exception (we expect an exception if the table does not exist)
  end;
  try
    QDest.SQL.Clear;
    QDest.SQL.Add('DROP TABLE MOEDITION');
    QDest.ExecSQL;
  except
    //silent exception
  end;

  //restore the tables to the initial state..

  QSource.SQL.Clear;
  QSource.SQL.Add('CREATE TABLE MOEDITION( EDITION Integer not null, MODSUBTYPE Varchar(64) not null, '
  + 'MODELPARAMETERISATION Integer, PRIO Integer, NOTE Varchar(1024), IDENT Varchar(256) not null, ' +
  'PRIMARY KEY (EDITION) ); ');
  try
    QSource.ExecSQL;
  except
    on e: Exception do begin
      MessageDlg('could not create table in QSource!', mtError, [mbOk], 0);
      exit;
    end;
  end;

  QDest.SQL.Clear;
  QDest.SQL.Add('CREATE TABLE MOEDITION( EDITION Integer not null, MODSUBTYPE Varchar(64) not null, '
  + 'MODELPARAMETERISATION Integer, PRIO Integer, NOTE Varchar(1024), IDENT Varchar(256) not null, ' +
  'PRIMARY KEY (EDITION) ); ');
  try
    QDest.ExecSQL;
  except
    on e: Exception do begin
      MessageDlg('could not create table in QDest!', mtError, [mbOk], 0);
      exit;
    end;
  end;

  //add some data to the tables
  QSource.SQL.Clear;
  QSource.SQL.Add('INSERT INTO MOEDITION (EDITION, MODSUBTYPE, MODELPARAMETERISATION, PRIO, NOTE, IDENT) ' +
   'VALUES( 1, ''1dsci'', 0, 1, '''', ''test.skytem2.refmodelnone.1d-vertical.sharp.noprior.25layers_SOURCE'' );');
  try
    QSource.ExecSQL;
  except
    MessageDlg('failed to add data', mtError, [mbok], 0);
  end;

  QSource.SQL.Clear;
  QSource.SQL.Add('INSERT INTO MOEDITION (EDITION, MODSUBTYPE, MODELPARAMETERISATION, PRIO, NOTE, IDENT) ' +
   'VALUES( 2, ''2dsci'', 0, 1, '''', ''test.skytem2.refmodelnone.2d-vertical.sharp.noprior.25layers_SOURCE'' );');
  try
    QSource.ExecSQL;
  except
    MessageDlg('failed to add data', mtError, [mbok], 0);
  end;

  QDest.SQL.Clear;
  QDest.SQL.Add('INSERT INTO MOEDITION (EDITION, MODSUBTYPE, MODELPARAMETERISATION, PRIO, NOTE, IDENT) ' +
   'VALUES( 1, ''1dsci'', 0, 1, '''', ''test.skytem2.refmodelnone.1d-vertical.sharp.noprior.25layers_DEST'' );');
  try
    QDest.ExecSQL;
  except
    MessageDlg('failed to add data', mtError, [mbok], 0);
  end;


  //Now the tables have data.
  //Run IBCLoader to get source -> destination..
  QSource.SQL.Clear;
  QSource.sql.Add('SELECT * FROM MOEDITION');
  QSource.Open;

  IBCLoader := TIBCLoader.Create(nil);
  IBCLoader.TableName := 'MOEDITION';
  IBCLoader.InsertMode := imUpdateOrInsert;
  IBCLoader.Connection := destConn;
  IBCLoader.AutoCommit := False;
  IBCLoader.Options.QuoteNames := True;
  try
    if QSource.RecordCount > 0 then begin
      IBCLoader.RowsPerBatch := 10;
      IBCLoader.LoadFromDataSet(QSource);
    end;
  except
    on E: Exception do begin
      Application.ProcessMessages;
      messageDlg('Error: ' + E.Message,mtError,[mbOk], 0);
      //I get Error:
      //Dynamic SQL Error
      //SQL Error Code = -104
      //Token Unknown - line 1, column 255
      //OR
    end;
  end;
end;

I hope this will help you to find the error. Merry Christmas

Posted: **Wed 24 Dec 2014 08:38**

This error may occur if you use Firebird server version lower than 2.1. When using TIBCLoader in the imUpdateOrInsert mode, on calling TIBCLoader.LoadFromDataSet, the "UPDATE OR INSERT INTO" construct is transferred to the server, that was added to the Firebird version 2.1. More details about this construct can be found via the link: http://www.firebirdsql.org/refdocs/lang ... nsert.html

Posted: **Mon 05 Jan 2015 08:23**

Updating to a higher version of Firebird server resolved the problem.

Thank you for your time

Posted: **Mon 05 Jan 2015 08:48**

Glad to see that the issue was resolved. Feel free to contact us if you have any further questions about IBDAC.

Devart Forums

TIBCLoader: SQL error on LoadFromDataset

TIBCLoader: SQL error on LoadFromDataset

Re: TIBCLoader: SQL error on LoadFromDataset

Re: TIBCLoader: SQL error on LoadFromDataset

Re: TIBCLoader: SQL error on LoadFromDataset

Re: TIBCLoader: SQL error on LoadFromDataset

Re: TIBCLoader: SQL error on LoadFromDataset