Making a datapump problems

[andrewsvaz]

Hello,

As I am making a datapump type app for a client I am trying to insert data from one Firebird database (v1.5.6) to another (v.2.5.3).

The problem is that there is a very large amount of tables to transfer the data and I am doing so dynamically (using a Stringlist whith the name of the tables) and I am not able to generate a correct SQL insert statement to use.

I am using this piece of code found here in the forum, but it isn't quite doing the trick.

Tabela := 'USUARIO';
SqlSel := 'Select * from '+Tabela;

DmMgr.tbNovo.SQL.Clear;
DmMgr.tbNovo.SQLUpdate.Clear;
DmMgr.tbNovo.SQL.Append(SqlSel);
DmMgr.tbNovo.UpdatingTable := Tabela;
DmMgr.tbNovo.Open;

SetLength(KeyAndDataFields.DataFieldDescs, DmMgr.tbNovo.Fields.Count);
for i := 0 to DmMgr.tbNovo.Fields.Count - 1 do
KeyAndDataFields.DataFieldDescs := DmMgr.tbNovo.GetFieldDesc(DmMgr.tbNovo.Fields) as TCRFieldDesc;

try
TablesInfo := TDBAccessUtils.GetTablesInfo(DmMgr.tbNovo);
oldCaseSensitive := TablesInfo.CaseSensitive;
try
TablesInfo.CaseSensitive := False;
// ShowMessage(TDBAccessUtils.SQLGenerator(DmMgr.tbNovo).GenerateSQL(_stInsert,False,0));
ShowMessage(TDBAccessUtils.SQLGenerator(DmMgr.tbNovo).GenerateSQL(_stInsert,True,0));
finally
TablesInfo.CaseSensitive := oldCaseSensitive;
end;
finally
end;

This gives me a Sql like this:

INSERT INTO USUARIO
(IDUSUARIO, BKP, DTABKP, USULOGNOME, USUNOME, USUSENHA, USUATIVO, USUDTATIV, USUDTDESATIV, USUDTCRIACAO, IDCRIADORUSU, USUCOMPL, USUASSINATURA, USUFLIMFILIAL, IDSKIN)
VALUES
(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

where I believe I need something like this (thus not working)

INSERT INTO USUARIO
(IDUSUARIO, BKP, DTABKP, USULOGNOME, USUNOME, USUSENHA, USUATIVO, USUDTATIV, USUDTDESATIV, USUDTCRIACAO, IDCRIADORUSU, USUCOMPL, USUASSINATURA, USUFLIMFILIAL, IDSKIN)
VALUES
(:IDUSUARIO, :BKP, :DTABKP, :USULOGNOME, :USUNOME, :USUSENHA, :USUATIVO, :USUDTATIV, :USUDTDESATIV, :USUDTCRIACAO, :IDCRIADORUSU, :USUCOMPL, :USUASSINATURA, :USUFLIMFILIAL, :IDSKIN)

Is there a way to get a correct sql insert statement or am I using it the wrong way?

Using XE3 with Unidac v5.3.8.

Thanks

[andrewsvaz]

Seeing that nobody answered, is my question understandable?

[PavloP]

To solve the problem, before generating the SQL statement, set the SubstituteParamName property of the TDASQLGenerator class to False:

Code: Select all

  TDBAccessUtils.SQLGenerator(DmMgr.tbNovo).SubstituteParamName := False;
  TDBAccessUtils.SQLGenerator(DmMgr.tbNovo).GenerateSQL(_stInsert,True,0));

[andrewsvaz]

Thanks for your attention PavloP.

[PavloP]

Feel free to contact us if you have any further questions.