Page 1 of 1
Script insert generator
Posted: Thu 26 Feb 2015 13:50
by delphi7
Hello,
is there a methode in odac components to create dynamically a script insert for a table.
thanks
Re: Script insert generator
Posted: Mon 02 Mar 2015 10:18
by AlexP
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));
Re: Script insert generator
Posted: Mon 02 Mar 2015 13:52
by delphi7
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
Re: Script insert generator
Posted: Tue 03 Mar 2015 09:44
by AlexP
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.
Re: Script insert generator
Posted: Thu 26 Apr 2018 08:53
by vanners
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.
Re: Script insert generator
Posted: Fri 27 Apr 2018 08:31
by MaximG
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
Re: Script insert generator
Posted: Mon 30 Apr 2018 02:20
by vanners
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?
Re: Script insert generator
Posted: Wed 02 May 2018 12:15
by MaximG
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.)