Script insert generator
Script insert generator
Hello,
is there a methode in odac components to create dynamically a script insert for a table.
thanks
is there a methode in odac components to create dynamically a script insert for a table.
thanks
Re: Script insert generator
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));
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
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
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
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.
You can read out data by yourself and substitute the corresponding parameters with this data in the retrieved INSERT script.
Re: Script insert generator
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.
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
To work with Batch-INSERT, use the following query:
You can find more information about using Batch operations with our components by the link :
https://blog.devart.com/using-batch-ope ... nents.html
Code: Select all
INSERT INTO SITE (level, secondPosition, LastName, FirstName, MiddleName, Emp_Num, workplace)
VALUES (:level, :secondPosition, :LastName, :FirstName, :MiddleName, :Emp_Num, :workplace)
https://blog.devart.com/using-batch-ope ... nents.html
Re: Script insert generator
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:
How do I have SQLGenerator generate a usable SQL 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);
Re: Script insert generator
You can get the query you are interested in in Run-Time as follows:
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.)
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;
due to any changes in the code of our product (for example, the call parameters can be changed, etc.)