Difference between revisions of "TSQLExporter"
From Lazarus wiki
Jump to navigationJump to search (Created page with "'''TSQLExporter''' image:tsqlexporter.png is a database export component for use with a TDataSet. To export the contents of a TDataSet (for instance the result of a...") |
(Note that the lazdbexport package needs to be installed) |
||
(6 intermediate revisions by 2 users not shown) | |||
Line 1: | Line 1: | ||
− | '''TSQLExporter''' [[image:tsqlexporter.png]] is a database export component for use with a [[TDataSet]]. | + | '''TSQLExporter''' [[image:tsqlexporter.png]] is a database export component for use with a [[TDataSet]]. It may be found on the Data Export tab of the [[Component Palette]] once you have installed the [[lazdbexport]] package. |
− | |||
To export the contents of a TDataSet (for instance the result of a [[TSQLQuery]] correctly setup to link with a [[TSQLConnector]] and [[TSQLTransaction]]) also <tt>FormatSettings.TableName</tt> need be provided. | To export the contents of a TDataSet (for instance the result of a [[TSQLQuery]] correctly setup to link with a [[TSQLConnector]] and [[TSQLTransaction]]) also <tt>FormatSettings.TableName</tt> need be provided. | ||
In the example below a a [[TSynEdit]] is filled with exported contents of table <tt>mytable</tt>. | In the example below a a [[TSynEdit]] is filled with exported contents of table <tt>mytable</tt>. | ||
− | <syntaxhighlight> | + | <syntaxhighlight lang=pascal> |
type | type | ||
myForm = class(TForm) | myForm = class(TForm) | ||
btnExport: TButton; | btnExport: TButton; | ||
− | |||
myConnector: TSQLConnector; | myConnector: TSQLConnector; | ||
myExporter: TSQLExporter; | myExporter: TSQLExporter; | ||
Line 30: | Line 28: | ||
− | procedure | + | procedure myForm.ExportTable( const tblnam: string ); |
var | var | ||
ts: TStringStream; | ts: TStringStream; | ||
Line 36: | Line 34: | ||
try | try | ||
ts := TStringStream.Create( '' ); | ts := TStringStream.Create( '' ); | ||
+ | myTransaction.Database := myConnector; | ||
+ | myQuery.Database := myConnector; | ||
+ | myExporter.DataSet := myQuery; | ||
+ | |||
myQuery.SQL.Text := 'SELECT * FROM ' + tblnam; | myQuery.SQL.Text := 'SELECT * FROM ' + tblnam; | ||
myQuery.Open(); | myQuery.Open(); | ||
− | myExporter.FormatSettings | + | |
+ | with myExporter.FormatSettings do begin | ||
+ | TableName := tblnam; // exporter uses tablename in insert statements | ||
+ | DateFormat := 'YYYY-MM-DD'; // SQL date format | ||
+ | DateTimeFormat := 'YYYY-MM-DD hh:mm:ss'; // | ||
+ | end; | ||
+ | |||
myExporter.ExportToStream( ts ); | myExporter.ExportToStream( ts ); | ||
− | myScriptText.Text := ts.DataString; | + | myScriptText.Text := ts.DataString; //show formatted script text |
+ | |||
myQuery.Close(); | myQuery.Close(); | ||
finally | finally | ||
Line 49: | Line 58: | ||
+ | [[Category:Lazarus]] | ||
[[Category:Databases]] | [[Category:Databases]] | ||
+ | [[Category:LCL]] | ||
[[Category:Components]] | [[Category:Components]] |
Latest revision as of 04:29, 18 July 2020
TSQLExporter is a database export component for use with a TDataSet. It may be found on the Data Export tab of the Component Palette once you have installed the lazdbexport package. To export the contents of a TDataSet (for instance the result of a TSQLQuery correctly setup to link with a TSQLConnector and TSQLTransaction) also FormatSettings.TableName need be provided.
In the example below a a TSynEdit is filled with exported contents of table mytable.
type
myForm = class(TForm)
btnExport: TButton;
myConnector: TSQLConnector;
myExporter: TSQLExporter;
myQuery: TSQLQuery;
myTransaction: TSQLTransaction;
myScriptText: TSynEdit;
mySyntax: TSynSQLSyn;
procedure btnExportClick(Sender: TObject);
private
procedure ExportTable( const tblnam: String );
end;
implementation
procedure myForm.btnExportClick(Sender: TObject);
begin
ExportTable( 'mytable' );
end;
procedure myForm.ExportTable( const tblnam: string );
var
ts: TStringStream;
begin
try
ts := TStringStream.Create( '' );
myTransaction.Database := myConnector;
myQuery.Database := myConnector;
myExporter.DataSet := myQuery;
myQuery.SQL.Text := 'SELECT * FROM ' + tblnam;
myQuery.Open();
with myExporter.FormatSettings do begin
TableName := tblnam; // exporter uses tablename in insert statements
DateFormat := 'YYYY-MM-DD'; // SQL date format
DateTimeFormat := 'YYYY-MM-DD hh:mm:ss'; //
end;
myExporter.ExportToStream( ts );
myScriptText.Text := ts.DataString; //show formatted script text
myQuery.Close();
finally
ts.Free();
end;
end;