examples/fbsql/fbsql.lpr

(*
 *  IBX For Lazarus (Firebird Express)
 *
 *  The contents of this file are subject to the Initial Developer's
 *  Public License Version 1.0 (the "License"); you may not use this
 *  file except in compliance with the License. You may obtain a copy
 *  of the License here:
 *
 *    http://www.firebirdsql.org/index.php?op=doc&id=idpl
 *
 *  Software distributed under the License is distributed on an "AS
 *  IS" basis, WITHOUT WARRANTY OF ANY KIND, either express or
 *  implied. See the License for the specific language governing rights
 *  and limitations under the License.
 *
 *  The Initial Developer of the Original Code is Tony Whyman.
 *
 *  The Original Code is (C) 2014 Tony Whyman, MWA Software
 *  (http://www.mwasoftware.co.uk).
 *
 *  All Rights Reserved.
 *
 *  Contributor(s): ______________________________________.
 *
*)
program fbsql;

{$mode objfpc}{$H+}

uses
  {$IFDEF UNIX}{$IFDEF UseCThreads}
  cthreads,
  {$ENDIF}{$ENDIF}
  Classes, SysUtils, CustApp
  { you can add units after this }
  ,IBDatabase, ibxscript, IBExtract, IBQuery, DB;

resourcestring

  sUnknownField = 'Unknown Field Type';
  sBadGraphic   = 'Unable to generate CSV data for a Graphic Field';
  sBadParadox   = 'Unable to generate CSV data for a Paradox OLE Field';
  sBadDBase     = 'Unable to generate CSV data  for a DBase OLE Field';
  sBadBinary    = 'Unable to generate CSV data  for a Binary Field';
  sBadCursor    = 'Unable to generate CSV data  for a Cursor Field';

type

  { TFBSQL }

  TFBSQL = class(TCustomApplication)
  private
    FIBDatabase: TIBDatabase;
    FIBTransaction: TIBTransaction;
    FIBXScript: TIBXScript;
    FExtract: TIBExtract;
    FQuery: TIBQuery;
    FSQL: TStringStream;
    procedure LogHandler(Sender: TObject; Msg: string);
    procedure ErrorLogHandler(Sender: TObject; Msg: string);
    procedure HandleSelectSQL(Sender: TObject; SQLText: string);
    procedure WriteCSV;
  protected
    procedure DoRun; override;
    procedure ShowException(E: Exception); override;
  public
    constructor Create(TheOwner: TComponent); override;
    destructor Destroy; override;
    procedure WriteHelp; virtual;
  end;

{ TFBSQL }

procedure TFBSQL.LogHandler(Sender: TObject; Msg: string);
begin
  writeln( Msg);
end;

procedure TFBSQL.ErrorLogHandler(Sender: TObject; Msg: string);
begin
  writeln(stderr, Msg);
end;

procedure TFBSQL.HandleSelectSQL(Sender: TObject; SQLText: string);
begin
  FQuery.SQL.Text := SQLText;
  FQuery.Active := true;
  try
    WriteCSV;
  finally
    FQuery.Active := false;
  end;
end;

procedure TFBSQL.WriteCSV;

  procedure WriteQuotedText(Text: string);
  var Index: integer;
  begin
    Index := 1;
    while Index <= Length(Text) do
      if Text[Index] = '"' then
      begin
        Insert('"',Text,Index);
        Inc(Index,2)
      end
      else
        Inc(Index,1);
    write('"' + Text + '"')
  end;

  procedure WriteFieldList(Fields: TFields);
  var I: integer;
  begin
    for I := 0 to Fields.Count - 1 do
    begin
      if I > 0 then write(',');
      write(Fields[I].FieldName)
    end;
    writeln;
  end;

  procedure WriteRecord;
  var I: integer;
  begin
    with FQuery do
    begin
      for I := 0 to FieldCount - 1 do
      begin
        if I <> 0 then write(',');
        case Fields[I].DataType of
        ftUnknown:  raise Exception.Create(sUnknownField);
        ftString:   WriteQuotedText(Fields[I].AsString);
        ftSmallint,
        ftInteger,
        ftWord,
        ftLargeInt,
        ftBoolean:  write(Fields[I].DisplayText);
        ftFloat,
        ftCurrency,
        ftFmtBCD,
        ftBCD:      write(Fields[I].AsString);
        ftDate,
        ftTime:     write(DateTimeToStr(Fields[I].AsDateTime));
        ftDateTime: WriteQuotedText(Fields[I].AsString);
        ftBytes,
        ftVarBytes,
        ftBlob,
        ftAutoInc:  write(Fields[I].AsString);
        ftMemo:     WriteQuotedText(Fields[I].AsString);
        ftGraphic:  raise Exception.Create(sBadGraphic);
        ftFmtMemo:  WriteQuotedText(Fields[I].AsString);
        ftParadoxOle: raise Exception.Create(sBadParadox);
        ftDBaseOle:   raise Exception.Create(sBadDBase);
        ftTypedBinary:raise Exception.Create(sBadBinary);
        ftCursor:    raise Exception.Create(sBadCursor);
       end
      end;
      writeln;
    end;
  end;
begin
  with FQuery do
  begin
    WriteFieldList(Fields);
    First;
    while not EOF do
    begin
      WriteRecord;
      Next
    end;
  end
end;

procedure TFBSQL.DoRun;
var
  ErrorMsg: String;
  SQLFileName: string;
  DoExtract: boolean;
  i: integer;
begin
  writeln(stderr,'fbsql: a non-interactive SQL interpreter for Firebird');
  writeln(stderr,'Copyright (c) MWA Software 2016');
  // quick check parameters
  ErrorMsg:=CheckOptions('ahbeufprs',['help','user','pass','role']);
  if ErrorMsg<>'' then begin
    ShowException(Exception.Create(ErrorMsg));
    Terminate;
    Exit;
  end;

  // parse parameters
  if HasOption('h','help') or (ParamCount = 0) then
  begin
    WriteHelp;
    Terminate;
    Exit;
  end;

  SQLFileName := '';
  DoExtract := false;

  {Initialise user_name and password from environment if available}

  if GetEnvironmentVariable('ISC_USER') <> '' then
    FIBDatabase.Params.Add('user_name=' + GetEnvironmentVariable('ISC_USER'));

  if GetEnvironmentVariable('ISC_PASSWORD') <> '' then
    FIBDatabase.Params.Add('password=' + GetEnvironmentVariable('ISC_PASSWORD'));

  {Process Command line options}

  if HasOption('u','user') then
    FIBDatabase.Params.Add('user_name=' + GetOptionValue('u','user'));

  if HasOption('p','pass') then
    FIBDatabase.Params.Add('password=' + GetOptionValue('p','pass'));

  if HasOption('r','role') then
    FIBDatabase.Params.Add('sql_role_name=' + GetOptionValue('r','role'));

  if (ParamCount >= 1) and (ParamStr(ParamCount)[1] <> '-')  then
    FIBDatabase.DatabaseName := ParamStr(ParamCount)
  else
    raise Exception.Create('Database Name Missing');

  if not HasOption('b') then
    FIBXScript.StopOnFirstError := false;

  if not HasOption('e') then
    FIBXScript.Echo := false;

  if HasOption('a') then
    DoExtract := true;

  if HasOption('f') then
    SQLFileName := GetOptionValue('f');

  if HasOption('s') then
  begin
    FSQL.WriteString(GetOptionValue('s'));
    FSQL.Position := 0;
  end;

  {Validation}

  if not DoExtract then
  begin
    if (SQLFileName = '') and (FSQL.DataString = '') then
      raise Exception.Create('An SQL File must be provided');

    if (FSQL.DataString <> '') and (SQLFileName <> '') then
       raise Exception.Create('An SQL Script File and text cannot be simulateously requested');

    if (FSQL.DataString = '') and not FileExists(SQLFileName) then
      raise Exception.CreateFmt('SQL File "%s" not found!',[SQLFileName]);

  end;

  if DoExtract and ((SQLFileName <> '') or (FSQL.DataString <> '')) then
    raise Exception.Create('Extract and script execution cannot be simulateously requested');

  {This is where it all happens}

  FIBDatabase.Connected := true;
  try
    if DoExtract then
    begin
      FExtract.ExtractObject(eoDatabase);
      for i := 0 to FExtract.Items.Count - 1 do
        writeln(FExtract.Items[i]);
    end
    else
    if FSQL.DataString = '' then
      FIBXScript.PerformUpdate(SQLFileName,true)
    else
      FIBXScript.PerformUpdate(FSQL,true);
  finally
    FIBDatabase.Connected := false;
  end;


  // stop program loop
  Terminate;
end;

procedure TFBSQL.ShowException(E: Exception);
begin
  writeln(stderr,'Error: ' + E.Message);
end;

constructor TFBSQL.Create(TheOwner: TComponent);
begin
  inherited Create(TheOwner);
  StopOnException:=True;
  FSQL := TStringStream.Create('');

  { Create Components }
  FIBDatabase := TIBDatabase.Create(self);
  FIBTransaction := TIBTransaction.Create(self);
  FIBTransaction.DefaultDatabase := FIBDatabase;
  FIBXScript := TIBXScript.Create(self);
  FIBXScript.Database := FIBDatabase;
  FIBXScript.Transaction := FIBTransaction;
  FIBXScript.OnOutputLog := @LogHandler;
  FIBXScript.OnErrorLog := @ErrorLogHandler;
  FIBXScript.OnSelectSQL := @HandleSelectSQL;
  FExtract := TIBExtract.Create(self);
  FExtract.Database := FIBDatabase;
  FExtract.Transaction := FIBTransaction;
  FQuery := TIBQuery.Create(self);
  FQuery.AllowAutoActivateTransaction := true;
  FQuery.Database := FIBDatabase;
  FQuery.Transaction := FIBTransaction;

  FIBTransaction.Params.Add('concurrency');
  FIBTransaction.Params.Add('wait');
  FIBDatabase.Params.Add('lc_ctype=UTF8');

end;

destructor TFBSQL.Destroy;
begin
  if assigned(FSQL) then FSQL.Free;
  inherited Destroy;
end;

procedure TFBSQL.WriteHelp;
begin
  writeln(stderr,'Usage: ',ExtractFileName(ExeName),' <options> <database name>');
  writeln(stderr,'Options:');
  writeln(stderr,'-a            write database metadata to stdout');
  writeln(stderr,'-b            stop on first error');
  writeln(stderr,'-e            echo sql statements to stdout');
  writeln(stderr,'-f <filename> execute SQL script from file');
  writeln(stderr,'-h            show this information');
  writeln(stderr,'-p <password> provide password on command line (insecure)');
  writeln(stderr,'-r <rolename> open database with this rolename');
  writeln(stderr,'-s <sql>      Execute SQL text');
  writeln(stderr,'-u <username> open database with this username (defaults to SYSDBA)');
  writeln;
  writeln(stderr,'Environment Variables:');
  writeln(stderr,'ISC_USER      Login user Name');
  writeln(stderr,'ISC_PASSWORD  Login password');
end;

var
  Application: TFBSQL;
begin
  Application:=TFBSQL.Create(nil);
  Application.Title:='fbsql';
  Application.Run;
  Application.Free;
end.

Revision:	45
Committed:	Tue Dec 6 10:33:46 2016 UTC (7 years, 11 months ago) by tony
File size:	9669 byte(s)
Log Message:	Committing updates for Release R2-0-0
#	Content
1	(*
2	* IBX For Lazarus (Firebird Express)
3	*
4	* The contents of this file are subject to the Initial Developer's
5	* Public License Version 1.0 (the "License"); you may not use this
6	* file except in compliance with the License. You may obtain a copy
7	* of the License here:
8	*
9	* http://www.firebirdsql.org/index.php?op=doc&id=idpl
10	*
11	* Software distributed under the License is distributed on an "AS
12	* IS" basis, WITHOUT WARRANTY OF ANY KIND, either express or
13	* implied. See the License for the specific language governing rights
14	* and limitations under the License.
15	*
16	* The Initial Developer of the Original Code is Tony Whyman.
17	*
18	* The Original Code is (C) 2014 Tony Whyman, MWA Software
19	* (http://www.mwasoftware.co.uk).
20	*
21	* All Rights Reserved.
22	*
23	* Contributor(s): ______________________________________.
24	*
25	*)
26	program fbsql;
27
28	{$mode objfpc}{$H+}
29
30	uses
31	{$IFDEF UNIX}{$IFDEF UseCThreads}
32	cthreads,
33	{$ENDIF}{$ENDIF}
34	Classes, SysUtils, CustApp
35	{ you can add units after this }
36	,IBDatabase, ibxscript, IBExtract, IBQuery, DB;
37
38	resourcestring
39
40	sUnknownField = 'Unknown Field Type';
41	sBadGraphic = 'Unable to generate CSV data for a Graphic Field';
42	sBadParadox = 'Unable to generate CSV data for a Paradox OLE Field';
43	sBadDBase = 'Unable to generate CSV data for a DBase OLE Field';
44	sBadBinary = 'Unable to generate CSV data for a Binary Field';
45	sBadCursor = 'Unable to generate CSV data for a Cursor Field';
46
47	type
48
49	{ TFBSQL }
50
51	TFBSQL = class(TCustomApplication)
52	private
53	FIBDatabase: TIBDatabase;
54	FIBTransaction: TIBTransaction;
55	FIBXScript: TIBXScript;
56	FExtract: TIBExtract;
57	FQuery: TIBQuery;
58	FSQL: TStringStream;
59	procedure LogHandler(Sender: TObject; Msg: string);
60	procedure ErrorLogHandler(Sender: TObject; Msg: string);
61	procedure HandleSelectSQL(Sender: TObject; SQLText: string);
62	procedure WriteCSV;
63	protected
64	procedure DoRun; override;
65	procedure ShowException(E: Exception); override;
66	public
67	constructor Create(TheOwner: TComponent); override;
68	destructor Destroy; override;
69	procedure WriteHelp; virtual;
70	end;
71
72	{ TFBSQL }
73
74	procedure TFBSQL.LogHandler(Sender: TObject; Msg: string);
75	begin
76	writeln( Msg);
77	end;
78
79	procedure TFBSQL.ErrorLogHandler(Sender: TObject; Msg: string);
80	begin
81	writeln(stderr, Msg);
82	end;
83
84	procedure TFBSQL.HandleSelectSQL(Sender: TObject; SQLText: string);
85	begin
86	FQuery.SQL.Text := SQLText;
87	FQuery.Active := true;
88	try
89	WriteCSV;
90	finally
91	FQuery.Active := false;
92	end;
93	end;
94
95	procedure TFBSQL.WriteCSV;
96
97	procedure WriteQuotedText(Text: string);
98	var Index: integer;
99	begin
100	Index := 1;
101	while Index <= Length(Text) do
102	if Text[Index] = '"' then
103	begin
104	Insert('"',Text,Index);
105	Inc(Index,2)
106	end
107	else
108	Inc(Index,1);
109	write('"' + Text + '"')
110	end;
111
112	procedure WriteFieldList(Fields: TFields);
113	var I: integer;
114	begin
115	for I := 0 to Fields.Count - 1 do
116	begin
117	if I > 0 then write(',');
118	write(Fields[I].FieldName)
119	end;
120	writeln;
121	end;
122
123	procedure WriteRecord;
124	var I: integer;
125	begin
126	with FQuery do
127	begin
128	for I := 0 to FieldCount - 1 do
129	begin
130	if I <> 0 then write(',');
131	case Fields[I].DataType of
132	ftUnknown: raise Exception.Create(sUnknownField);
133	ftString: WriteQuotedText(Fields[I].AsString);
134	ftSmallint,
135	ftInteger,
136	ftWord,
137	ftLargeInt,
138	ftBoolean: write(Fields[I].DisplayText);
139	ftFloat,
140	ftCurrency,
141	ftFmtBCD,
142	ftBCD: write(Fields[I].AsString);
143	ftDate,
144	ftTime: write(DateTimeToStr(Fields[I].AsDateTime));
145	ftDateTime: WriteQuotedText(Fields[I].AsString);
146	ftBytes,
147	ftVarBytes,
148	ftBlob,
149	ftAutoInc: write(Fields[I].AsString);
150	ftMemo: WriteQuotedText(Fields[I].AsString);
151	ftGraphic: raise Exception.Create(sBadGraphic);
152	ftFmtMemo: WriteQuotedText(Fields[I].AsString);
153	ftParadoxOle: raise Exception.Create(sBadParadox);
154	ftDBaseOle: raise Exception.Create(sBadDBase);
155	ftTypedBinary:raise Exception.Create(sBadBinary);
156	ftCursor: raise Exception.Create(sBadCursor);
157	end
158	end;
159	writeln;
160	end;
161	end;
162	begin
163	with FQuery do
164	begin
165	WriteFieldList(Fields);
166	First;
167	while not EOF do
168	begin
169	WriteRecord;
170	Next
171	end;
172	end
173	end;
174
175	procedure TFBSQL.DoRun;
176	var
177	ErrorMsg: String;
178	SQLFileName: string;
179	DoExtract: boolean;
180	i: integer;
181	begin
182	writeln(stderr,'fbsql: a non-interactive SQL interpreter for Firebird');
183	writeln(stderr,'Copyright (c) MWA Software 2016');
184	// quick check parameters
185	ErrorMsg:=CheckOptions('ahbeufprs',['help','user','pass','role']);
186	if ErrorMsg<>'' then begin
187	ShowException(Exception.Create(ErrorMsg));
188	Terminate;
189	Exit;
190	end;
191
192	// parse parameters
193	if HasOption('h','help') or (ParamCount = 0) then
194	begin
195	WriteHelp;
196	Terminate;
197	Exit;
198	end;
199
200	SQLFileName := '';
201	DoExtract := false;
202
203	{Initialise user_name and password from environment if available}
204
205	if GetEnvironmentVariable('ISC_USER') <> '' then
206	FIBDatabase.Params.Add('user_name=' + GetEnvironmentVariable('ISC_USER'));
207
208	if GetEnvironmentVariable('ISC_PASSWORD') <> '' then
209	FIBDatabase.Params.Add('password=' + GetEnvironmentVariable('ISC_PASSWORD'));
210
211	{Process Command line options}
212
213	if HasOption('u','user') then
214	FIBDatabase.Params.Add('user_name=' + GetOptionValue('u','user'));
215
216	if HasOption('p','pass') then
217	FIBDatabase.Params.Add('password=' + GetOptionValue('p','pass'));
218
219	if HasOption('r','role') then
220	FIBDatabase.Params.Add('sql_role_name=' + GetOptionValue('r','role'));
221
222	if (ParamCount >= 1) and (ParamStr(ParamCount)[1] <> '-') then
223	FIBDatabase.DatabaseName := ParamStr(ParamCount)
224	else
225	raise Exception.Create('Database Name Missing');
226
227	if not HasOption('b') then
228	FIBXScript.StopOnFirstError := false;
229
230	if not HasOption('e') then
231	FIBXScript.Echo := false;
232
233	if HasOption('a') then
234	DoExtract := true;
235
236	if HasOption('f') then
237	SQLFileName := GetOptionValue('f');
238
239	if HasOption('s') then
240	begin
241	FSQL.WriteString(GetOptionValue('s'));
242	FSQL.Position := 0;
243	end;
244
245	{Validation}
246
247	if not DoExtract then
248	begin
249	if (SQLFileName = '') and (FSQL.DataString = '') then
250	raise Exception.Create('An SQL File must be provided');
251
252	if (FSQL.DataString <> '') and (SQLFileName <> '') then
253	raise Exception.Create('An SQL Script File and text cannot be simulateously requested');
254
255	if (FSQL.DataString = '') and not FileExists(SQLFileName) then
256	raise Exception.CreateFmt('SQL File "%s" not found!',[SQLFileName]);
257
258	end;
259
260	if DoExtract and ((SQLFileName <> '') or (FSQL.DataString <> '')) then
261	raise Exception.Create('Extract and script execution cannot be simulateously requested');
262
263	{This is where it all happens}
264
265	FIBDatabase.Connected := true;
266	try
267	if DoExtract then
268	begin
269	FExtract.ExtractObject(eoDatabase);
270	for i := 0 to FExtract.Items.Count - 1 do
271	writeln(FExtract.Items[i]);
272	end
273	else
274	if FSQL.DataString = '' then
275	FIBXScript.PerformUpdate(SQLFileName,true)
276	else
277	FIBXScript.PerformUpdate(FSQL,true);
278	finally
279	FIBDatabase.Connected := false;
280	end;
281
282
283	// stop program loop
284	Terminate;
285	end;
286
287	procedure TFBSQL.ShowException(E: Exception);
288	begin
289	writeln(stderr,'Error: ' + E.Message);
290	end;
291
292	constructor TFBSQL.Create(TheOwner: TComponent);
293	begin
294	inherited Create(TheOwner);
295	StopOnException:=True;
296	FSQL := TStringStream.Create('');
297
298	{ Create Components }
299	FIBDatabase := TIBDatabase.Create(self);
300	FIBTransaction := TIBTransaction.Create(self);
301	FIBTransaction.DefaultDatabase := FIBDatabase;
302	FIBXScript := TIBXScript.Create(self);
303	FIBXScript.Database := FIBDatabase;
304	FIBXScript.Transaction := FIBTransaction;
305	FIBXScript.OnOutputLog := @LogHandler;
306	FIBXScript.OnErrorLog := @ErrorLogHandler;
307	FIBXScript.OnSelectSQL := @HandleSelectSQL;
308	FExtract := TIBExtract.Create(self);
309	FExtract.Database := FIBDatabase;
310	FExtract.Transaction := FIBTransaction;
311	FQuery := TIBQuery.Create(self);
312	FQuery.AllowAutoActivateTransaction := true;
313	FQuery.Database := FIBDatabase;
314	FQuery.Transaction := FIBTransaction;
315
316	FIBTransaction.Params.Add('concurrency');
317	FIBTransaction.Params.Add('wait');
318	FIBDatabase.Params.Add('lc_ctype=UTF8');
319
320	end;
321
322	destructor TFBSQL.Destroy;
323	begin
324	if assigned(FSQL) then FSQL.Free;
325	inherited Destroy;
326	end;
327
328	procedure TFBSQL.WriteHelp;
329	begin
330	writeln(stderr,'Usage: ',ExtractFileName(ExeName),' <options> <database name>');
331	writeln(stderr,'Options:');
332	writeln(stderr,'-a write database metadata to stdout');
333	writeln(stderr,'-b stop on first error');
334	writeln(stderr,'-e echo sql statements to stdout');
335	writeln(stderr,'-f <filename> execute SQL script from file');
336	writeln(stderr,'-h show this information');
337	writeln(stderr,'-p <password> provide password on command line (insecure)');
338	writeln(stderr,'-r <rolename> open database with this rolename');
339	writeln(stderr,'-s <sql> Execute SQL text');
340	writeln(stderr,'-u <username> open database with this username (defaults to SYSDBA)');
341	writeln;
342	writeln(stderr,'Environment Variables:');
343	writeln(stderr,'ISC_USER Login user Name');
344	writeln(stderr,'ISC_PASSWORD Login password');
345	end;
346
347	var
348	Application: TFBSQL;
349	begin
350	Application:=TFBSQL.Create(nil);
351	Application.Title:='fbsql';
352	Application.Run;
353	Application.Free;
354	end.
355