Script insert generator

Discussion of open issues, suggestions and bugs regarding ODAC (Oracle Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
delphi7
Posts: 15
Joined: Wed 12 Mar 2014 12:28

Script insert generator

Post by delphi7 » Thu 26 Feb 2015 13:50

Hello,

is there a methode in odac components to create dynamically a script insert for a table.

thanks

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: Script insert generator

Post by AlexP » Mon 02 Mar 2015 10:18

Hello,

To access the TDASQLGenerator class and the GenerateSQL method, you can use the TDBAccessUtils class:

var
DAParamsInfo: TDAParamsInfo;
begin
DAParamsInfo := TDAParamsInfo.Create(TDAParamInfo);
UniQuery1.SQL.Text := 'SELECT * FROM DEPT';
UniQuery1.Open;
ShowMessage(TDBAccessUtils.SQLGenerator(UniQuery1).GenerateSQL(DAParamsInfo, _stInsert,True,0));

delphi7
Posts: 15
Joined: Wed 12 Mar 2014 12:28

Re: Script insert generator

Post by delphi7 » Mon 02 Mar 2015 13:52

hi,

how to modify the code to get the real value of every fields params of the insert generator : insert into table_A (field_1,field_2) values(:field_1,:field_2);

example :

table_A
field_1 | field_2
---------------
1 | 2
3 | 4

my_final_generated_scipt{
insert into table_A (field_1,field_2) values(1,2);
insert into table_A (field_1,field_2) values(3,4);
}

Thanks

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: Script insert generator

Post by AlexP » Tue 03 Mar 2015 09:44

There is no capability in ODAC to generate INSERT scripts automatically basing on existing data.
You can read out data by yourself and substitute the corresponding parameters with this data in the retrieved INSERT script.

vanners
Posts: 3
Joined: Thu 26 Apr 2018 08:43

Re: Script insert generator

Post by vanners » Thu 26 Apr 2018 08:53

When I use the above code I end up with question marks instead of parameters.

e.g.
INSERT INTO Site
(level, secondPosition, LastName, FirstName, MiddleName, Emp_Num, workplace)
VALUES
(?, ?, ?, ?, ?, ?, ?)

this results in there being no parameters, so I can't use this SQL in Batch-Insert (or anything else for that matter).
The Connection's Provider is set to SQL Server.

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: Script insert generator

Post by MaximG » Fri 27 Apr 2018 08:31

To work with Batch-INSERT, use the following query:

Code: Select all

  INSERT INTO SITE (level, secondPosition, LastName, FirstName, MiddleName, Emp_Num, workplace)
        VALUES (:level, :secondPosition, :LastName, :FirstName, :MiddleName, :Emp_Num, :workplace)
You can find more information about using Batch operations with our components by the link :
https://blog.devart.com/using-batch-ope ... nents.html

vanners
Posts: 3
Joined: Thu 26 Apr 2018 08:43

Re: Script insert generator

Post by vanners » Mon 30 Apr 2018 02:20

Yes, I am aware of what batch-INSERT statements look like. What I want to know is why that cannot be generated from a query of a select statement.

It can be generated at design time using the UniQuery Editor's SQL Generator, but when I try the following code at runtime I end up with question marks where the parameters should be:

Code: Select all

  ParamsInfo := TDAParamsInfo.Create(TDAParamInfo);
  sql := TDBAccessUtils.SQLGenerator(UniQuery1).GenerateSQL(ParamsInfo, _stInsert, False);
How do I have SQLGenerator generate a usable SQL statement?

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: Script insert generator

Post by MaximG » Wed 02 May 2018 12:15

You can get the query you are interested in in Run-Time as follows:

Code: Select all

var
  TableInfo: TCRTableInfo;
  KeyAndDataFields: TKeyAndDataFields;
begin
  UniQuery1.SQL.Text := 'SELECT * FROM Site';
  UniQuery1.Open;
  TableInfo := TCRTableInfo.Create(nil);
  try
    TableInfo.TableName := 'Site';
    TDBAccessUtils.GetKeyAndDataFields(UniQuery1, KeyAndDataFields, False);
    TDBAccessUtils.SQLGenerator(UniQuery1).SubstituteParamName := False;
    ShowMessage(TDBAccessUtils.SQLGenerator(UniQuery1).GenerateSQLforUpdTable(TDBAccessUtils.GetUpdater(UniQuery1).ParamsInfo, TableInfo, KeyAndDataFields, _stInsert, False));
  finally
    TableInfo.Free;
  end;
end;
Note that the specified method is implemented using the methods for internal use and may not be applicable in the future
due to any changes in the code of our product (for example, the call parameters can be changed, etc.)

Post Reply