· 8 years ago · Jul 07, 2018, 09:14 PM
1unit SQLiteTable3;
2
3{
4 Simple classes for using SQLite's exec and get_table.
5
6 TSQLiteDatabase wraps the calls to open and close an SQLite database.
7 It also wraps SQLite_exec for queries that do not return a result set
8
9 TSQLiteTable wraps execution of SQL query.
10 It run query and read all returned rows to internal buffer.
11 It allows accessing fields by name as well as index and can move through a
12 result set forward and backwards, or randomly to any row.
13
14 TSQLiteUniTable wraps execution of SQL query.
15 It run query as TSQLiteTable, but reading just first row only!
16 You can step to next row (until not EOF) by 'Next' method.
17 You cannot step backwards! (So, it is called as UniDirectional result set.)
18 It not using any internal buffering, this class is very close to Sqlite API.
19 It allows accessing fields by name as well as index on actual row only.
20 Very good and fast for sequentional scanning of large result sets with minimal
21 memory footprint.
22
23 Warning! Do not close TSQLiteDatabase before any TSQLiteUniTable,
24 because query is closed on TSQLiteUniTable destructor and database connection
25 is used during TSQLiteUniTable live!
26
27 SQL parameter usage:
28 You can add named parameter values by call set of AddParam* methods.
29 Parameters will be used for first next SQL statement only.
30 Parameter name must be prefixed by ':', '$' or '@' and same prefix must be
31 used in SQL statement!
32 Sample:
33 table.AddParamText(':str', 'some value');
34 s := table.GetTableString('SELECT value FROM sometable WHERE id=:str');
35
36 Notes from Andrew Retmanski on prepared queries
37 The changes are as follows:
38
39 SQLiteTable3.pas
40 - Added new boolean property Synchronised (this controls the SYNCHRONOUS pragma as I found that turning this OFF increased the write performance in my application)
41 - Added new type TSQLiteQuery (this is just a simple record wrapper around the SQL string and a TSQLiteStmt pointer)
42 - Added PrepareSQL method to prepare SQL query - returns TSQLiteQuery
43 - Added ReleaseSQL method to release previously prepared query
44 - Added overloaded BindSQL methods for Integer and String types - these set new values for the prepared query parameters
45 - Added overloaded ExecSQL method to execute a prepared TSQLiteQuery
46
47 Usage of the new methods should be self explanatory but the process is in essence:
48
49 1. Call PrepareSQL to return TSQLiteQuery 2. Call BindSQL for each parameter in the prepared query 3. Call ExecSQL to run the prepared query 4. Repeat steps 2 & 3 as required 5. Call ReleaseSQL to free SQLite resources
50
51 One other point - the Synchronised property throws an error if used inside a transaction.
52
53 Acknowledments
54 Adapted by Tim Anderson (tim@itwriting.com)
55 Originally created by Pablo Pissanetzky (pablo@myhtpc.net)
56 Modified and enhanced by Lukas Gebauer
57 Modified and enhanced by Tobias Gunkel
58}
59
60interface
61
62{$IFDEF FPC}
63 {$MODE Delphi}{$H+}
64{$ENDIF}
65
66uses
67 {$IFDEF WIN32}
68 Windows,
69 {$ENDIF}
70 SQLite3, Classes, SysUtils;
71
72const
73
74 dtInt = 1;
75 dtNumeric = 2;
76 dtStr = 3;
77 dtBlob = 4;
78 dtNull = 5;
79
80type
81
82 ESQLiteException = class(Exception)
83 end;
84
85 TSQliteParam = class
86 public
87 name: string;
88 valuetype: integer;
89 valueinteger: int64;
90 valuefloat: double;
91 valuedata: string;
92 end;
93
94 THookQuery = procedure(Sender: TObject; SQL: String) of object;
95
96 TSQLiteQuery = record
97 SQL: String;
98 Statement: TSQLiteStmt;
99 end;
100
101 TSQLiteTable = class;
102 TSQLiteUniTable = class;
103
104 TSQLiteDatabase = class
105 private
106 fDB: TSQLiteDB;
107 fInTrans: boolean;
108 fSync: boolean;
109 fParams: TList;
110 FOnQuery: THookQuery;
111 procedure RaiseError(s: string; SQL: string);
112 procedure SetParams(Stmt: TSQLiteStmt);
113 procedure BindData(Stmt: TSQLiteStmt; const Bindings: array of const);
114 function GetRowsChanged: integer;
115 protected
116 procedure SetSynchronised(Value: boolean);
117 procedure DoQuery(value: string);
118 public
119 constructor Create(const FileName: string);
120 destructor Destroy; override;
121 function GetTable(const SQL: Ansistring): TSQLiteTable; overload;
122 function GetTable(const SQL: Ansistring; const Bindings: array of const): TSQLiteTable; overload;
123 procedure ExecSQL(const SQL: Ansistring); overload;
124 procedure ExecSQL(const SQL: Ansistring; const Bindings: array of const); overload;
125 procedure ExecSQL(Query: TSQLiteQuery); overload;
126 function PrepareSQL(const SQL: Ansistring): TSQLiteQuery;
127 procedure BindSQL(Query: TSQLiteQuery; const Index: Integer; const Value: Integer); overload;
128 procedure BindSQL(Query: TSQLiteQuery; const Index: Integer; const Value: String); overload;
129 procedure ReleaseSQL(Query: TSQLiteQuery);
130 function GetUniTable(const SQL: Ansistring): TSQLiteUniTable; overload;
131 function GetUniTable(const SQL: Ansistring; const Bindings: array of const): TSQLiteUniTable; overload;
132 function GetTableValue(const SQL: Ansistring): int64; overload;
133 function GetTableValue(const SQL: Ansistring; const Bindings: array of const): int64; overload;
134 function GetTableString(const SQL: Ansistring): string; overload;
135 function GetTableString(const SQL: Ansistring; const Bindings: array of const): string; overload;
136 procedure GetTableStrings(const SQL: Ansistring; const Value: TStrings);
137 procedure UpdateBlob(const SQL: Ansistring; BlobData: TStream);
138 procedure BeginTransaction;
139 procedure Commit;
140 procedure Rollback;
141 function TableExists(TableName: string): boolean;
142 function GetLastInsertRowID: int64;
143 function GetLastChangedRows: int64;
144 procedure SetTimeout(Value: integer);
145 function Backup(TargetDB: TSQLiteDatabase): integer; Overload;
146 function Backup(TargetDB: TSQLiteDatabase; targetName: Ansistring; sourceName: Ansistring): integer; Overload;
147 function Version: string;
148 procedure AddCustomCollate(name: string; xCompare: TCollateXCompare);
149 //adds collate named SYSTEM for correct data sorting by user's locale
150 Procedure AddSystemCollate;
151 procedure ParamsClear;
152 procedure AddParamInt(name: string; value: int64);
153 procedure AddParamFloat(name: string; value: double);
154 procedure AddParamText(name: string; value: string);
155 procedure AddParamNull(name: string);
156
157 property DB: TSQLiteDB read fDB;
158 published
159 property IsTransactionOpen: boolean read fInTrans;
160 //database rows that were changed (or inserted or deleted) by the most recent SQL statement
161 property RowsChanged : integer read getRowsChanged;
162 property Synchronised: boolean read FSync write SetSynchronised;
163 property OnQuery: THookQuery read FOnQuery write FOnQuery;
164 end;
165
166 TSQLiteTable = class
167 private
168 fResults: TList;
169 fRowCount: cardinal;
170 fColCount: cardinal;
171 fCols: TStringList;
172 fColTypes: TList;
173 fRow: cardinal;
174 function GetFields(I: cardinal): string;
175 function GetEOF: boolean;
176 function GetBOF: boolean;
177 function GetColumns(I: integer): string;
178 function GetFieldByName(FieldName: string): string;
179 function GetFieldIndex(FieldName: string): integer;
180 function GetCount: integer;
181 function GetCountResult: integer;
182 public
183 constructor Create(DB: TSQLiteDatabase; const SQL: Ansistring); overload;
184 constructor Create(DB: TSQLiteDatabase; const SQL: Ansistring; const Bindings: array of const); overload;
185 destructor Destroy; override;
186 function FieldAsInteger(I: cardinal): int64;
187 function FieldAsBlob(I: cardinal): TMemoryStream;
188 function FieldAsBlobText(I: cardinal): string;
189 function FieldIsNull(I: cardinal): boolean;
190 function FieldAsString(I: cardinal): string;
191 function FieldAsDouble(I: cardinal): double;
192 function Next: boolean;
193 function Previous: boolean;
194 property EOF: boolean read GetEOF;
195 property BOF: boolean read GetBOF;
196 property Fields[I: cardinal]: string read GetFields;
197 property FieldByName[FieldName: string]: string read GetFieldByName;
198 property FieldIndex[FieldName: string]: integer read GetFieldIndex;
199 property Columns[I: integer]: string read GetColumns;
200 property ColCount: cardinal read fColCount;
201 property RowCount: cardinal read fRowCount;
202 property Row: cardinal read fRow;
203 function MoveFirst: boolean;
204 function MoveLast: boolean;
205 function MoveTo(position: cardinal): boolean;
206 property Count: integer read GetCount;
207 // The property CountResult is used when you execute count(*) queries.
208 // It returns 0 if the result set is empty or the value of the
209 // first field as an integer.
210 property CountResult: integer read GetCountResult;
211 end;
212
213 TSQLiteUniTable = class
214 private
215 fColCount: cardinal;
216 fCols: TStringList;
217 fRow: cardinal;
218 fEOF: boolean;
219 fStmt: TSQLiteStmt;
220 fDB: TSQLiteDatabase;
221 fSQL: string;
222 function GetFields(I: cardinal): string;
223 function GetColumns(I: integer): string;
224 function GetFieldByName(FieldName: string): string;
225 function GetFieldIndex(FieldName: string): integer;
226 public
227 constructor Create(DB: TSQLiteDatabase; const SQL: Ansistring); overload;
228 constructor Create(DB: TSQLiteDatabase; const SQL: Ansistring; const Bindings: array of const); overload;
229 destructor Destroy; override;
230 function FieldAsInteger(I: cardinal): int64;
231 function FieldAsBlob(I: cardinal): TMemoryStream;
232 function FieldAsBlobPtr(I: cardinal; out iNumBytes: integer): Pointer;
233 function FieldAsBlobText(I: cardinal): string;
234 function FieldIsNull(I: cardinal): boolean;
235 function FieldAsString(I: cardinal): string;
236 function FieldAsDouble(I: cardinal): double;
237 function Next: boolean;
238 property EOF: boolean read FEOF;
239 property Fields[I: cardinal]: string read GetFields;
240 property FieldByName[FieldName: string]: string read GetFieldByName;
241 property FieldIndex[FieldName: string]: integer read GetFieldIndex;
242 property Columns[I: integer]: string read GetColumns;
243 property ColCount: cardinal read fColCount;
244 property Row: cardinal read fRow;
245 end;
246
247procedure DisposePointer(ptr: pointer); cdecl;
248
249{$IFDEF WIN32}
250function SystemCollate(Userdta: pointer; Buf1Len: integer; Buf1: pointer;
251 Buf2Len: integer; Buf2: pointer): integer; cdecl;
252{$ENDIF}
253
254implementation
255
256procedure DisposePointer(ptr: pointer); cdecl;
257begin
258 if assigned(ptr) then
259 freemem(ptr);
260end;
261
262{$IFDEF WIN32}
263function SystemCollate(Userdta: pointer; Buf1Len: integer; Buf1: pointer;
264 Buf2Len: integer; Buf2: pointer): integer; cdecl;
265begin
266 Result := CompareStringW(LOCALE_USER_DEFAULT, 0, PWideChar(Buf1), Buf1Len,
267 PWideChar(Buf2), Buf2Len) - 2;
268end;
269{$ENDIF}
270
271//------------------------------------------------------------------------------
272// TSQLiteDatabase
273//------------------------------------------------------------------------------
274
275constructor TSQLiteDatabase.Create(const FileName: string);
276var
277 Msg: PAnsiChar;
278 iResult: integer;
279 utf8FileName: UTF8string;
280begin
281 inherited Create;
282 fParams := TList.Create;
283
284 self.fInTrans := False;
285
286 Msg := nil;
287 try
288 utf8FileName := UTF8String(FileName);
289 iResult := SQLite3_Open(PAnsiChar(utf8FileName), Fdb);
290
291 if iResult <> SQLITE_OK then
292 if Assigned(Fdb) then
293 begin
294 Msg := Sqlite3_ErrMsg(Fdb);
295 raise ESqliteException.CreateFmt('Failed to open database "%s" : %s',
296 [FileName, Msg]);
297 end
298 else
299 raise ESqliteException.CreateFmt('Failed to open database "%s" : unknown error',
300 [FileName]);
301
302//set a few configs
303//L.G. Do not call it here. Because busy handler is not setted here,
304// any share violation causing exception!
305
306// self.ExecSQL('PRAGMA SYNCHRONOUS=NORMAL;');
307// self.ExecSQL('PRAGMA temp_store = MEMORY;');
308
309 finally
310 if Assigned(Msg) then
311 SQLite3_Free(Msg);
312 end;
313
314end;
315
316//..............................................................................
317
318destructor TSQLiteDatabase.Destroy;
319begin
320 if self.fInTrans then
321 self.Rollback; //assume rollback
322 if Assigned(fDB) then
323 SQLite3_Close(fDB);
324 ParamsClear;
325 fParams.Free;
326 inherited;
327end;
328
329function TSQLiteDatabase.GetLastInsertRowID: int64;
330begin
331 Result := Sqlite3_LastInsertRowID(self.fDB);
332end;
333
334function TSQLiteDatabase.GetLastChangedRows: int64;
335begin
336 Result := SQLite3_TotalChanges(self.fDB);
337end;
338
339//..............................................................................
340
341procedure TSQLiteDatabase.RaiseError(s: string; SQL: string);
342//look up last error and raise an exception with an appropriate message
343var
344 Msg: PAnsiChar;
345 ret : integer;
346begin
347
348 Msg := nil;
349
350 ret := sqlite3_errcode(self.fDB);
351 if ret <> SQLITE_OK then
352 Msg := sqlite3_errmsg(self.fDB);
353
354 if Msg <> nil then
355 raise ESqliteException.CreateFmt(s +'.'#13'Error [%d]: %s.'#13'"%s": %s', [ret, SQLiteErrorStr(ret),SQL, Msg])
356 else
357 raise ESqliteException.CreateFmt(s, [SQL, 'No message']);
358
359end;
360
361procedure TSQLiteDatabase.SetSynchronised(Value: boolean);
362begin
363 if Value <> fSync then
364 begin
365 if Value then
366 ExecSQL('PRAGMA synchronous = ON;')
367 else
368 ExecSQL('PRAGMA synchronous = OFF;');
369 fSync := Value;
370 end;
371end;
372
373procedure TSQLiteDatabase.BindData(Stmt: TSQLiteStmt; const Bindings: array of const);
374var
375 BlobMemStream: TCustomMemoryStream;
376 BlobStdStream: TStream;
377 DataPtr: Pointer;
378 DataSize: integer;
379 AnsiStr: AnsiString;
380 AnsiStrPtr: PAnsiString;
381 I: integer;
382begin
383 for I := 0 to High(Bindings) do
384 begin
385 case Bindings[I].VType of
386 vtString,
387 vtAnsiString, vtPChar,
388 vtWideString, vtPWideChar,
389 vtChar, vtWideChar:
390 begin
391 case Bindings[I].VType of
392 vtString: begin // ShortString
393 AnsiStr := Bindings[I].VString^;
394 DataPtr := PAnsiChar(AnsiStr);
395 DataSize := Length(AnsiStr)+1;
396 end;
397 vtPChar: begin
398 DataPtr := Bindings[I].VPChar;
399 DataSize := -1;
400 end;
401 vtAnsiString: begin
402 AnsiStrPtr := PAnsiString(@Bindings[I].VAnsiString);
403 DataPtr := PAnsiChar(AnsiStrPtr^);
404 DataSize := Length(AnsiStrPtr^)+1;
405 end;
406 vtPWideChar: begin
407 DataPtr := PAnsiChar(UTF8Encode(WideString(Bindings[I].VPWideChar)));
408 DataSize := -1;
409 end;
410 vtWideString: begin
411 DataPtr := PAnsiChar(UTF8Encode(PWideString(@Bindings[I].VWideString)^));
412 DataSize := -1;
413 end;
414 vtChar: begin
415 DataPtr := PAnsiChar(String(Bindings[I].VChar));
416 DataSize := 2;
417 end;
418 vtWideChar: begin
419 DataPtr := PAnsiChar(UTF8Encode(WideString(Bindings[I].VWideChar)));
420 DataSize := -1;
421 end;
422 else
423 raise ESqliteException.Create('Unknown string-type');
424 end;
425 if (sqlite3_bind_text(Stmt, I+1, DataPtr, DataSize, SQLITE_STATIC) <> SQLITE_OK) then
426 RaiseError('Could not bind text', 'BindData');
427 end;
428 vtInteger:
429 if (sqlite3_bind_int(Stmt, I+1, Bindings[I].VInteger) <> SQLITE_OK) then
430 RaiseError('Could not bind integer', 'BindData');
431 vtInt64:
432 if (sqlite3_bind_int64(Stmt, I+1, Bindings[I].VInt64^) <> SQLITE_OK) then
433 RaiseError('Could not bind int64', 'BindData');
434 vtExtended:
435 if (sqlite3_bind_double(Stmt, I+1, Bindings[I].VExtended^) <> SQLITE_OK) then
436 RaiseError('Could not bind extended', 'BindData');
437 vtBoolean:
438 if (sqlite3_bind_int(Stmt, I+1, Integer(Bindings[I].VBoolean)) <> SQLITE_OK) then
439 RaiseError('Could not bind boolean', 'BindData');
440 vtPointer:
441 begin
442 if (Bindings[I].VPointer = nil) then
443 begin
444 if (sqlite3_bind_null(Stmt, I+1) <> SQLITE_OK) then
445 RaiseError('Could not bind null', 'BindData');
446 end
447 else
448 raise ESqliteException.Create('Unhandled pointer (<> nil)');
449 end;
450 vtObject:
451 begin
452 if (Bindings[I].VObject is TCustomMemoryStream) then
453 begin
454 BlobMemStream := TCustomMemoryStream(Bindings[I].VObject);
455 if (sqlite3_bind_blob(Stmt, I+1, @PAnsiChar(BlobMemStream.Memory)[BlobMemStream.Position],
456 BlobMemStream.Size-BlobMemStream.Position, SQLITE_STATIC) <> SQLITE_OK) then
457 begin
458 RaiseError('Could not bind BLOB', 'BindData');
459 end;
460 end
461 else if (Bindings[I].VObject is TStream) then
462 begin
463 BlobStdStream := TStream(Bindings[I].VObject);
464 DataSize := BlobStdStream.Size;
465
466 GetMem(DataPtr, DataSize);
467 if (DataPtr = nil) then
468 raise ESqliteException.Create('Error getting memory to save blob');
469
470 BlobStdStream.Position := 0;
471 BlobStdStream.Read(DataPtr^, DataSize);
472
473 if (sqlite3_bind_blob(stmt, I+1, DataPtr, DataSize, @DisposePointer) <> SQLITE_OK) then
474 RaiseError('Could not bind BLOB', 'BindData');
475 end
476 else
477 raise ESqliteException.Create('Unhandled object-type in binding');
478 end
479 else
480 begin
481 raise ESqliteException.Create('Unhandled binding');
482 end;
483 end;
484 end;
485end;
486
487procedure TSQLiteDatabase.ExecSQL(const SQL: Ansistring);
488begin
489 ExecSQL(SQL, []);
490end;
491
492procedure TSQLiteDatabase.ExecSQL(const SQL: Ansistring; const Bindings: array of const);
493var
494 Stmt: TSQLiteStmt;
495 NextSQLStatement: PAnsiChar;
496 iStepResult: integer;
497begin
498 try
499 if Sqlite3_Prepare_v2(self.fDB, PAnsiChar(SQL), -1, Stmt, NextSQLStatement) <>
500 SQLITE_OK then
501 RaiseError('Error executing SQL', SQL);
502 if (Stmt = nil) then
503 RaiseError('Could not prepare SQL statement', SQL);
504 DoQuery(SQL);
505 SetParams(Stmt);
506 BindData(Stmt, Bindings);
507
508 iStepResult := Sqlite3_step(Stmt);
509 if (iStepResult <> SQLITE_DONE) then
510 begin
511 SQLite3_reset(stmt);
512 RaiseError('Error executing SQL statement', SQL);
513 end;
514 finally
515 if Assigned(Stmt) then
516 Sqlite3_Finalize(stmt);
517 end;
518end;
519
520{$WARNINGS OFF}
521procedure TSQLiteDatabase.ExecSQL(Query: TSQLiteQuery);
522var
523 iStepResult: integer;
524begin
525 if Assigned(Query.Statement) then
526 begin
527 iStepResult := Sqlite3_step(Query.Statement);
528
529 if (iStepResult <> SQLITE_DONE) then
530 begin
531 SQLite3_reset(Query.Statement);
532 RaiseError('Error executing prepared SQL statement', Query.SQL);
533 end;
534 Sqlite3_Reset(Query.Statement);
535 end;
536end;
537{$WARNINGS ON}
538
539{$WARNINGS OFF}
540function TSQLiteDatabase.PrepareSQL(const SQL: Ansistring): TSQLiteQuery;
541var
542 Stmt: TSQLiteStmt;
543 NextSQLStatement: PAnsiChar;
544begin
545 Result.SQL := SQL;
546 Result.Statement := nil;
547
548 if Sqlite3_Prepare(self.fDB, PAnsiChar(SQL), -1, Stmt, NextSQLStatement) <>
549 SQLITE_OK then
550 RaiseError('Error executing SQL', SQL)
551 else
552 Result.Statement := Stmt;
553
554 if (Result.Statement = nil) then
555 RaiseError('Could not prepare SQL statement', SQL);
556 DoQuery(SQL);
557end;
558{$WARNINGS ON}
559
560{$WARNINGS OFF}
561procedure TSQLiteDatabase.BindSQL(Query: TSQLiteQuery; const Index: Integer; const Value: Integer);
562begin
563 if Assigned(Query.Statement) then
564 sqlite3_Bind_Int(Query.Statement, Index, Value)
565 else
566 RaiseError('Could not bind integer to prepared SQL statement', Query.SQL);
567end;
568{$WARNINGS ON}
569
570{$WARNINGS OFF}
571procedure TSQLiteDatabase.BindSQL(Query: TSQLiteQuery; const Index: Integer; const Value: String);
572begin
573 if Assigned(Query.Statement) then
574 Sqlite3_Bind_Text(Query.Statement, Index, PAnsiChar(Value), Length(Value), @SQLITE_STATIC)
575 else
576 RaiseError('Could not bind string to prepared SQL statement', Query.SQL);
577end;
578{$WARNINGS ON}
579
580{$WARNINGS OFF}
581procedure TSQLiteDatabase.ReleaseSQL(Query: TSQLiteQuery);
582begin
583 if Assigned(Query.Statement) then
584 begin
585 Sqlite3_Finalize(Query.Statement);
586 Query.Statement := nil;
587 end
588 else
589 RaiseError('Could not release prepared SQL statement', Query.SQL);
590end;
591{$WARNINGS ON}
592
593procedure TSQLiteDatabase.UpdateBlob(const SQL: Ansistring; BlobData: TStream);
594var
595 iSize: integer;
596 ptr: pointer;
597 Stmt: TSQLiteStmt;
598 Msg: PAnsiChar;
599 NextSQLStatement: PAnsiChar;
600 iStepResult: integer;
601 iBindResult: integer;
602begin
603 //expects SQL of the form 'UPDATE MYTABLE SET MYFIELD = ? WHERE MYKEY = 1'
604 if pos('?', SQL) = 0 then
605 RaiseError('SQL must include a ? parameter', SQL);
606
607 Msg := nil;
608 try
609
610 if Sqlite3_Prepare_v2(self.fDB, PAnsiChar(SQL), -1, Stmt, NextSQLStatement) <>
611 SQLITE_OK then
612 RaiseError('Could not prepare SQL statement', SQL);
613
614 if (Stmt = nil) then
615 RaiseError('Could not prepare SQL statement', SQL);
616 DoQuery(SQL);
617
618 //now bind the blob data
619 iSize := BlobData.size;
620
621 GetMem(ptr, iSize);
622
623 if (ptr = nil) then
624 raise ESqliteException.CreateFmt('Error getting memory to save blob',
625 [SQL, 'Error']);
626
627 BlobData.position := 0;
628 BlobData.Read(ptr^, iSize);
629
630 iBindResult := SQLite3_Bind_Blob(stmt, 1, ptr, iSize, @DisposePointer);
631
632 if iBindResult <> SQLITE_OK then
633 RaiseError('Error binding blob to database', SQL);
634
635 iStepResult := Sqlite3_step(Stmt);
636
637 if (iStepResult <> SQLITE_DONE) then
638 begin
639 SQLite3_reset(stmt);
640 RaiseError('Error executing SQL statement', SQL);
641 end;
642
643 finally
644
645 if Assigned(Stmt) then
646 Sqlite3_Finalize(stmt);
647
648 if Assigned(Msg) then
649 SQLite3_Free(Msg);
650 end;
651
652end;
653
654//..............................................................................
655
656function TSQLiteDatabase.GetTable(const SQL: Ansistring): TSQLiteTable;
657begin
658 Result := TSQLiteTable.Create(Self, SQL);
659end;
660
661function TSQLiteDatabase.GetTable(const SQL: Ansistring; const Bindings: array of const): TSQLiteTable;
662begin
663 Result := TSQLiteTable.Create(Self, SQL, Bindings);
664end;
665
666function TSQLiteDatabase.GetUniTable(const SQL: Ansistring): TSQLiteUniTable;
667begin
668 Result := TSQLiteUniTable.Create(Self, SQL);
669end;
670
671function TSQLiteDatabase.GetUniTable(const SQL: Ansistring; const Bindings: array of const): TSQLiteUniTable;
672begin
673 Result := TSQLiteUniTable.Create(Self, SQL, Bindings);
674end;
675
676function TSQLiteDatabase.GetTableValue(const SQL: Ansistring): int64;
677begin
678 Result := GetTableValue(SQL, []);
679end;
680
681function TSQLiteDatabase.GetTableValue(const SQL: Ansistring; const Bindings: array of const): int64;
682var
683 Table: TSQLiteUniTable;
684begin
685 Result := 0;
686 Table := self.GetUniTable(SQL, Bindings);
687 try
688 if not Table.EOF then
689 Result := Table.FieldAsInteger(0);
690 finally
691 Table.Free;
692 end;
693end;
694
695function TSQLiteDatabase.GetTableString(const SQL: Ansistring): String;
696begin
697 Result := GetTableString(SQL, []);
698end;
699
700function TSQLiteDatabase.GetTableString(const SQL: Ansistring; const Bindings: array of const): String;
701var
702 Table: TSQLiteUniTable;
703begin
704 Result := '';
705 Table := self.GetUniTable(SQL, Bindings);
706 try
707 if not Table.EOF then
708 Result := Table.FieldAsString(0);
709 finally
710 Table.Free;
711 end;
712end;
713
714procedure TSQLiteDatabase.GetTableStrings(const SQL: Ansistring;
715 const Value: TStrings);
716var
717 Table: TSQLiteUniTable;
718begin
719 Value.Clear;
720 Table := self.GetUniTable(SQL);
721 try
722 while not table.EOF do
723 begin
724 Value.Add(Table.FieldAsString(0));
725 table.Next;
726 end;
727 finally
728 Table.Free;
729 end;
730end;
731
732procedure TSQLiteDatabase.BeginTransaction;
733begin
734 if not self.fInTrans then
735 begin
736 self.ExecSQL('BEGIN TRANSACTION');
737 self.fInTrans := True;
738 end
739 else
740 raise ESqliteException.Create('Transaction already open');
741end;
742
743procedure TSQLiteDatabase.Commit;
744begin
745 self.ExecSQL('COMMIT');
746 self.fInTrans := False;
747end;
748
749procedure TSQLiteDatabase.Rollback;
750begin
751 self.ExecSQL('ROLLBACK');
752 self.fInTrans := False;
753end;
754
755function TSQLiteDatabase.TableExists(TableName: string): boolean;
756var
757 sql: string;
758 ds: TSqliteTable;
759begin
760 //returns true if table exists in the database
761 sql := 'select [sql] from sqlite_master where [type] = ''table'' and lower(name) = ''' +
762 lowercase(TableName) + ''' ';
763 ds := self.GetTable(sql);
764 try
765 Result := (ds.Count > 0);
766 finally
767 ds.Free;
768 end;
769end;
770
771procedure TSQLiteDatabase.SetTimeout(Value: integer);
772begin
773 SQLite3_BusyTimeout(self.fDB, Value);
774end;
775
776function TSQLiteDatabase.Version: string;
777begin
778 Result := SQLite3_Version;
779end;
780
781procedure TSQLiteDatabase.AddCustomCollate(name: string;
782 xCompare: TCollateXCompare);
783begin
784 sqlite3_create_collation(fdb, PAnsiChar(name), SQLITE_UTF8, nil, xCompare);
785end;
786
787procedure TSQLiteDatabase.AddSystemCollate;
788begin
789 {$IFDEF WIN32}
790 sqlite3_create_collation(fdb, 'SYSTEM', SQLITE_UTF16LE, nil, @SystemCollate);
791 {$ENDIF}
792end;
793
794procedure TSQLiteDatabase.ParamsClear;
795var
796 n: integer;
797begin
798 for n := fParams.Count - 1 downto 0 do
799 TSQliteParam(fparams[n]).free;
800 fParams.Clear;
801end;
802
803procedure TSQLiteDatabase.AddParamInt(name: string; value: int64);
804var
805 par: TSQliteParam;
806begin
807 par := TSQliteParam.Create;
808 par.name := name;
809 par.valuetype := SQLITE_INTEGER;
810 par.valueinteger := value;
811 fParams.Add(par);
812end;
813
814procedure TSQLiteDatabase.AddParamFloat(name: string; value: double);
815var
816 par: TSQliteParam;
817begin
818 par := TSQliteParam.Create;
819 par.name := name;
820 par.valuetype := SQLITE_FLOAT;
821 par.valuefloat := value;
822 fParams.Add(par);
823end;
824
825procedure TSQLiteDatabase.AddParamText(name: string; value: string);
826var
827 par: TSQliteParam;
828begin
829 par := TSQliteParam.Create;
830 par.name := name;
831 par.valuetype := SQLITE_TEXT;
832 par.valuedata := value;
833 fParams.Add(par);
834end;
835
836procedure TSQLiteDatabase.AddParamNull(name: string);
837var
838 par: TSQliteParam;
839begin
840 par := TSQliteParam.Create;
841 par.name := name;
842 par.valuetype := SQLITE_NULL;
843 fParams.Add(par);
844end;
845
846procedure TSQLiteDatabase.SetParams(Stmt: TSQLiteStmt);
847var
848 n: integer;
849 i: integer;
850 par: TSQliteParam;
851begin
852 try
853 for n := 0 to fParams.Count - 1 do
854 begin
855 par := TSQliteParam(fParams[n]);
856 i := sqlite3_bind_parameter_index(Stmt, PAnsiChar(par.name));
857 if i > 0 then
858 begin
859 case par.valuetype of
860 SQLITE_INTEGER:
861 sqlite3_bind_int64(Stmt, i, par.valueinteger);
862 SQLITE_FLOAT:
863 sqlite3_bind_double(Stmt, i, par.valuefloat);
864 SQLITE_TEXT:
865 sqlite3_bind_text(Stmt, i, PAnsiChar(par.valuedata),
866 length(par.valuedata), SQLITE_TRANSIENT);
867 SQLITE_NULL:
868 sqlite3_bind_null(Stmt, i);
869 end;
870 end;
871 end;
872 finally
873 ParamsClear;
874 end;
875end;
876
877//database rows that were changed (or inserted or deleted) by the most recent SQL statement
878function TSQLiteDatabase.GetRowsChanged: integer;
879begin
880 Result := SQLite3_Changes(self.fDB);
881end;
882
883procedure TSQLiteDatabase.DoQuery(value: string);
884begin
885 if assigned(OnQuery) then
886 OnQuery(Self, Value);
887end;
888
889//returns result of SQLITE3_Backup_Step
890function TSQLiteDatabase.Backup(TargetDB: TSQLiteDatabase; targetName: Ansistring; sourceName: Ansistring): integer;
891var
892pBackup: TSQLiteBackup;
893begin
894 pBackup := Sqlite3_backup_init(TargetDB.DB,PAnsiChar(targetName),self.DB,PAnsiChar(sourceName));
895
896 if (pBackup = nil) then
897 raise ESqliteException.Create('Could not initialize backup')
898 else begin
899 try
900 result := SQLITE3_Backup_Step(pBackup,-1); //copies entire db
901 finally
902 SQLITE3_backup_finish(pBackup);
903 end;
904 end;
905end;
906
907function TSQliteDatabase.Backup(TargetDB: TSQLiteDatabase): integer;
908begin
909 result := self.Backup(TargetDB,'main','main');
910end;
911
912//------------------------------------------------------------------------------
913// TSQLiteTable
914//------------------------------------------------------------------------------
915
916constructor TSQLiteTable.Create(DB: TSQLiteDatabase; const SQL: Ansistring);
917begin
918 Create(DB, SQL, []);
919end;
920
921constructor TSQLiteTable.Create(DB: TSQLiteDatabase; const SQL: Ansistring; const Bindings: array of const);
922var
923 Stmt: TSQLiteStmt;
924 NextSQLStatement: PAnsiChar;
925 iStepResult: integer;
926 ptr: pointer;
927 iNumBytes: integer;
928 thisBlobValue: TMemoryStream;
929 thisStringValue: pstring;
930 thisDoubleValue: pDouble;
931 thisIntValue: pInt64;
932 thisColType: pInteger;
933 i: integer;
934 DeclaredColType: PAnsiChar;
935 ActualColType: integer;
936 ptrValue: PAnsiChar;
937begin
938 inherited create;
939 try
940 self.fRowCount := 0;
941 self.fColCount := 0;
942 //if there are several SQL statements in SQL, NextSQLStatment points to the
943 //beginning of the next one. Prepare only prepares the first SQL statement.
944 if Sqlite3_Prepare_v2(DB.fDB, PAnsiChar(SQL), -1, Stmt, NextSQLStatement) <> SQLITE_OK then
945 DB.RaiseError('Error executing SQL', SQL);
946 if (Stmt = nil) then
947 DB.RaiseError('Could not prepare SQL statement', SQL);
948 DB.DoQuery(SQL);
949 DB.SetParams(Stmt);
950 DB.BindData(Stmt, Bindings);
951
952 iStepResult := Sqlite3_step(Stmt);
953 while (iStepResult <> SQLITE_DONE) do
954 begin
955 case iStepResult of
956 SQLITE_ROW:
957 begin
958 Inc(fRowCount);
959 if (fRowCount = 1) then
960 begin
961 //get data types
962 fCols := TStringList.Create;
963 fColTypes := TList.Create;
964 fColCount := SQLite3_ColumnCount(stmt);
965 for i := 0 to Pred(fColCount) do
966 fCols.Add(AnsiUpperCase(Sqlite3_ColumnName(stmt, i)));
967 for i := 0 to Pred(fColCount) do
968 begin
969 new(thisColType);
970 DeclaredColType := Sqlite3_ColumnDeclType(stmt, i);
971 if DeclaredColType = nil then
972 thisColType^ := Sqlite3_ColumnType(stmt, i) //use the actual column type instead
973 //seems to be needed for last_insert_rowid
974 else
975 if (DeclaredColType = 'INTEGER') or (DeclaredColType = 'BOOLEAN') then
976 thisColType^ := dtInt
977 else
978 if (DeclaredColType = 'NUMERIC') or
979 (DeclaredColType = 'FLOAT') or
980 (DeclaredColType = 'DOUBLE') or
981 (DeclaredColType = 'REAL') then
982 thisColType^ := dtNumeric
983 else
984 if DeclaredColType = 'BLOB' then
985 thisColType^ := dtBlob
986 else
987 thisColType^ := dtStr;
988 fColTypes.Add(thiscoltype);
989 end;
990 fResults := TList.Create;
991 end;
992
993 //get column values
994 for i := 0 to Pred(ColCount) do
995 begin
996 ActualColType := Sqlite3_ColumnType(stmt, i);
997 if (ActualColType = SQLITE_NULL) then
998 fResults.Add(nil)
999 else
1000 if pInteger(fColTypes[i])^ = dtInt then
1001 begin
1002 new(thisintvalue);
1003 thisintvalue^ := Sqlite3_ColumnInt64(stmt, i);
1004 fResults.Add(thisintvalue);
1005 end
1006 else
1007 if pInteger(fColTypes[i])^ = dtNumeric then
1008 begin
1009 new(thisdoublevalue);
1010 thisdoublevalue^ := Sqlite3_ColumnDouble(stmt, i);
1011 fResults.Add(thisdoublevalue);
1012 end
1013 else
1014 if pInteger(fColTypes[i])^ = dtBlob then
1015 begin
1016 iNumBytes := Sqlite3_ColumnBytes(stmt, i);
1017 if iNumBytes = 0 then
1018 thisblobvalue := nil
1019 else
1020 begin
1021 thisblobvalue := TMemoryStream.Create;
1022 thisblobvalue.position := 0;
1023 ptr := Sqlite3_ColumnBlob(stmt, i);
1024 thisblobvalue.writebuffer(ptr^, iNumBytes);
1025 end;
1026 fResults.Add(thisblobvalue);
1027 end
1028 else
1029 begin
1030 new(thisstringvalue);
1031 ptrValue := Sqlite3_ColumnText(stmt, i);
1032 setstring(thisstringvalue^, ptrvalue, strlen(ptrvalue));
1033 fResults.Add(thisstringvalue);
1034 end;
1035 end;
1036 end;
1037 SQLITE_BUSY:
1038 raise ESqliteException.CreateFmt('Could not prepare SQL statement',
1039 [SQL, 'SQLite is Busy']);
1040 else
1041 begin
1042 SQLite3_reset(stmt);
1043 DB.RaiseError('Could not retrieve data', SQL);
1044 end;
1045 end;
1046 iStepResult := Sqlite3_step(Stmt);
1047 end;
1048 fRow := 0;
1049 finally
1050 if Assigned(Stmt) then
1051 Sqlite3_Finalize(stmt);
1052 end;
1053end;
1054
1055//..............................................................................
1056
1057destructor TSQLiteTable.Destroy;
1058var
1059 i: cardinal;
1060 iColNo: integer;
1061begin
1062 if Assigned(fResults) then
1063 begin
1064 for i := 0 to fResults.Count - 1 do
1065 begin
1066 //check for blob type
1067 iColNo := (i mod fColCount);
1068 case pInteger(self.fColTypes[iColNo])^ of
1069 dtBlob:
1070 TMemoryStream(fResults[i]).Free;
1071 dtStr:
1072 if fResults[i] <> nil then
1073 begin
1074 setstring(string(fResults[i]^), nil, 0);
1075 dispose(fResults[i]);
1076 end;
1077 else
1078 dispose(fResults[i]);
1079 end;
1080 end;
1081 fResults.Free;
1082 end;
1083 if Assigned(fCols) then
1084 fCols.Free;
1085 if Assigned(fColTypes) then
1086 for i := 0 to fColTypes.Count - 1 do
1087 dispose(fColTypes[i]);
1088 fColTypes.Free;
1089 inherited;
1090end;
1091
1092//..............................................................................
1093
1094function TSQLiteTable.GetColumns(I: integer): string;
1095begin
1096 Result := fCols[I];
1097end;
1098
1099//..............................................................................
1100
1101function TSQLiteTable.GetCountResult: integer;
1102begin
1103 if not EOF then
1104 Result := StrToInt(Fields[0])
1105 else
1106 Result := 0;
1107end;
1108
1109function TSQLiteTable.GetCount: integer;
1110begin
1111 Result := FRowCount;
1112end;
1113
1114//..............................................................................
1115
1116function TSQLiteTable.GetEOF: boolean;
1117begin
1118 Result := fRow >= fRowCount;
1119end;
1120
1121function TSQLiteTable.GetBOF: boolean;
1122begin
1123 Result := fRow <= 0;
1124end;
1125
1126//..............................................................................
1127
1128function TSQLiteTable.GetFieldByName(FieldName: string): string;
1129begin
1130 Result := GetFields(self.GetFieldIndex(FieldName));
1131end;
1132
1133function TSQLiteTable.GetFieldIndex(FieldName: string): integer;
1134begin
1135 if (fCols = nil) then
1136 begin
1137 raise ESqliteException.Create('Field ' + fieldname + ' Not found. Empty dataset');
1138 exit;
1139 end;
1140
1141 if (fCols.count = 0) then
1142 begin
1143 raise ESqliteException.Create('Field ' + fieldname + ' Not found. Empty dataset');
1144 exit;
1145 end;
1146
1147 Result := fCols.IndexOf(AnsiUpperCase(FieldName));
1148
1149 if (result < 0) then
1150 begin
1151 raise ESqliteException.Create('Field not found in dataset: ' + fieldname)
1152 end;
1153end;
1154
1155//..............................................................................
1156
1157function TSQLiteTable.GetFields(I: cardinal): string;
1158var
1159 thisvalue: pstring;
1160 thistype: integer;
1161begin
1162 Result := '';
1163 if EOF then
1164 raise ESqliteException.Create('Table is at End of File');
1165 //integer types are not stored in the resultset
1166 //as strings, so they should be retrieved using the type-specific
1167 //methods
1168 thistype := pInteger(self.fColTypes[I])^;
1169
1170 case thistype of
1171 dtStr:
1172 begin
1173 thisvalue := self.fResults[(self.frow * self.fColCount) + I];
1174 if (thisvalue <> nil) then
1175 Result := thisvalue^
1176 else
1177 Result := '';
1178 end;
1179 dtInt:
1180 Result := IntToStr(self.FieldAsInteger(I));
1181 dtNumeric:
1182 Result := FloatToStr(self.FieldAsDouble(I));
1183 dtBlob:
1184 Result := self.FieldAsBlobText(I);
1185 else
1186 Result := '';
1187 end;
1188end;
1189
1190function TSqliteTable.FieldAsBlob(I: cardinal): TMemoryStream;
1191begin
1192 if EOF then
1193 raise ESqliteException.Create('Table is at End of File');
1194 if (self.fResults[(self.frow * self.fColCount) + I] = nil) then
1195 Result := nil
1196 else
1197 if pInteger(self.fColTypes[I])^ = dtBlob then
1198 Result := TMemoryStream(self.fResults[(self.frow * self.fColCount) + I])
1199 else
1200 raise ESqliteException.Create('Not a Blob field');
1201end;
1202
1203function TSqliteTable.FieldAsBlobText(I: cardinal): string;
1204var
1205 MemStream: TMemoryStream;
1206 Buffer: PAnsiChar;
1207begin
1208 Result := '';
1209 MemStream := self.FieldAsBlob(I);
1210 if MemStream <> nil then
1211 if MemStream.Size > 0 then
1212 begin
1213 MemStream.position := 0;
1214 {$IFDEF UNICODE}
1215 Buffer := AnsiStralloc(MemStream.Size + 1);
1216 {$ELSE}
1217 Buffer := Stralloc(MemStream.Size + 1);
1218 {$ENDIF}
1219 MemStream.readbuffer(Buffer[0], MemStream.Size);
1220 (Buffer + MemStream.Size)^ := chr(0);
1221 SetString(Result, Buffer, MemStream.size);
1222 strdispose(Buffer);
1223 end;
1224 //do not free the TMemoryStream here; it is freed when
1225 //TSqliteTable is destroyed
1226
1227end;
1228
1229
1230function TSqliteTable.FieldAsInteger(I: cardinal): int64;
1231begin
1232 if EOF then
1233 raise ESqliteException.Create('Table is at End of File');
1234 if (self.fResults[(self.frow * self.fColCount) + I] = nil) then
1235 Result := 0
1236 else
1237 if pInteger(self.fColTypes[I])^ = dtInt then
1238 Result := pInt64(self.fResults[(self.frow * self.fColCount) + I])^
1239 else
1240 if pInteger(self.fColTypes[I])^ = dtNumeric then
1241 Result := trunc(strtofloat(pString(self.fResults[(self.frow * self.fColCount) + I])^))
1242 else
1243 raise ESqliteException.Create('Not an integer or numeric field');
1244end;
1245
1246function TSqliteTable.FieldAsDouble(I: cardinal): double;
1247begin
1248 if EOF then
1249 raise ESqliteException.Create('Table is at End of File');
1250 if (self.fResults[(self.frow * self.fColCount) + I] = nil) then
1251 Result := 0
1252 else
1253 if pInteger(self.fColTypes[I])^ = dtInt then
1254 Result := pInt64(self.fResults[(self.frow * self.fColCount) + I])^
1255 else
1256 if pInteger(self.fColTypes[I])^ = dtNumeric then
1257 Result := pDouble(self.fResults[(self.frow * self.fColCount) + I])^
1258 else
1259 raise ESqliteException.Create('Not an integer or numeric field');
1260end;
1261
1262function TSqliteTable.FieldAsString(I: cardinal): string;
1263begin
1264 if EOF then
1265 raise ESqliteException.Create('Table is at End of File');
1266 if (self.fResults[(self.frow * self.fColCount) + I] = nil) then
1267 Result := ''
1268 else
1269 Result := self.GetFields(I);
1270end;
1271
1272function TSqliteTable.FieldIsNull(I: cardinal): boolean;
1273var
1274 thisvalue: pointer;
1275begin
1276 if EOF then
1277 raise ESqliteException.Create('Table is at End of File');
1278 thisvalue := self.fResults[(self.frow * self.fColCount) + I];
1279 Result := (thisvalue = nil);
1280end;
1281
1282//..............................................................................
1283
1284function TSQLiteTable.Next: boolean;
1285begin
1286 Result := False;
1287 if not EOF then
1288 begin
1289 Inc(fRow);
1290 Result := True;
1291 end;
1292end;
1293
1294function TSQLiteTable.Previous: boolean;
1295begin
1296 Result := False;
1297 if not BOF then
1298 begin
1299 Dec(fRow);
1300 Result := True;
1301 end;
1302end;
1303
1304function TSQLiteTable.MoveFirst: boolean;
1305begin
1306 Result := False;
1307 if self.fRowCount > 0 then
1308 begin
1309 fRow := 0;
1310 Result := True;
1311 end;
1312end;
1313
1314function TSQLiteTable.MoveLast: boolean;
1315begin
1316 Result := False;
1317 if self.fRowCount > 0 then
1318 begin
1319 fRow := fRowCount - 1;
1320 Result := True;
1321 end;
1322end;
1323
1324{$WARNINGS OFF}
1325function TSQLiteTable.MoveTo(position: cardinal): boolean;
1326begin
1327 Result := False;
1328 if (self.fRowCount > 0) and (self.fRowCount > position) then
1329 begin
1330 fRow := position;
1331 Result := True;
1332 end;
1333end;
1334{$WARNINGS ON}
1335
1336
1337
1338{ TSQLiteUniTable }
1339
1340constructor TSQLiteUniTable.Create(DB: TSQLiteDatabase; const SQL: Ansistring);
1341begin
1342 Create(DB, SQL, []);
1343end;
1344
1345constructor TSQLiteUniTable.Create(DB: TSQLiteDatabase; const SQL: Ansistring; const Bindings: array of const);
1346var
1347 NextSQLStatement: PAnsiChar;
1348 i: integer;
1349begin
1350 inherited create;
1351 self.fDB := db;
1352 self.fEOF := false;
1353 self.fRow := 0;
1354 self.fColCount := 0;
1355 self.fSQL := SQL;
1356 if Sqlite3_Prepare_v2(DB.fDB, PAnsiChar(SQL), -1, fStmt, NextSQLStatement) <> SQLITE_OK then
1357 DB.RaiseError('Error executing SQL', SQL);
1358 if (fStmt = nil) then
1359 DB.RaiseError('Could not prepare SQL statement', SQL);
1360 DB.DoQuery(SQL);
1361 DB.SetParams(fStmt);
1362 DB.BindData(fStmt, Bindings);
1363
1364 //get data types
1365 fCols := TStringList.Create;
1366 fColCount := SQLite3_ColumnCount(fstmt);
1367 for i := 0 to Pred(fColCount) do
1368 fCols.Add(AnsiUpperCase(Sqlite3_ColumnName(fstmt, i)));
1369
1370 Next;
1371end;
1372
1373destructor TSQLiteUniTable.Destroy;
1374begin
1375 if Assigned(fStmt) then
1376 Sqlite3_Finalize(fstmt);
1377 if Assigned(fCols) then
1378 fCols.Free;
1379 inherited;
1380end;
1381
1382function TSQLiteUniTable.FieldAsBlob(I: cardinal): TMemoryStream;
1383var
1384 iNumBytes: integer;
1385 ptr: pointer;
1386begin
1387 Result := TMemoryStream.Create;
1388 iNumBytes := Sqlite3_ColumnBytes(fstmt, i);
1389 if iNumBytes > 0 then
1390 begin
1391 ptr := Sqlite3_ColumnBlob(fstmt, i);
1392 Result.writebuffer(ptr^, iNumBytes);
1393 Result.Position := 0;
1394 end;
1395end;
1396
1397function TSQLiteUniTable.FieldAsBlobPtr(I: cardinal; out iNumBytes: integer): Pointer;
1398begin
1399 iNumBytes := Sqlite3_ColumnBytes(fstmt, i);
1400 Result := Sqlite3_ColumnBlob(fstmt, i);
1401end;
1402
1403function TSQLiteUniTable.FieldAsBlobText(I: cardinal): string;
1404var
1405 MemStream: TMemoryStream;
1406 Buffer: PAnsiChar;
1407begin
1408 Result := '';
1409 MemStream := self.FieldAsBlob(I);
1410 if MemStream <> nil then
1411 try
1412 if MemStream.Size > 0 then
1413 begin
1414 MemStream.position := 0;
1415 {$IFDEF UNICODE}
1416 Buffer := AnsiStralloc(MemStream.Size + 1);
1417 {$ELSE}
1418 Buffer := Stralloc(MemStream.Size + 1);
1419 {$ENDIF}
1420 MemStream.readbuffer(Buffer[0], MemStream.Size);
1421 (Buffer + MemStream.Size)^ := chr(0);
1422 SetString(Result, Buffer, MemStream.size);
1423 strdispose(Buffer);
1424 end;
1425 finally
1426 MemStream.Free;
1427 end
1428end;
1429
1430function TSQLiteUniTable.FieldAsDouble(I: cardinal): double;
1431begin
1432 Result := Sqlite3_ColumnDouble(fstmt, i);
1433end;
1434
1435function TSQLiteUniTable.FieldAsInteger(I: cardinal): int64;
1436begin
1437 Result := Sqlite3_ColumnInt64(fstmt, i);
1438end;
1439
1440function TSQLiteUniTable.FieldAsString(I: cardinal): string;
1441begin
1442 Result := self.GetFields(I);
1443end;
1444
1445function TSQLiteUniTable.FieldIsNull(I: cardinal): boolean;
1446begin
1447 Result := Sqlite3_ColumnText(fstmt, i) = nil;
1448end;
1449
1450function TSQLiteUniTable.GetColumns(I: integer): string;
1451begin
1452 Result := fCols[I];
1453end;
1454
1455function TSQLiteUniTable.GetFieldByName(FieldName: string): string;
1456begin
1457 Result := GetFields(self.GetFieldIndex(FieldName));
1458end;
1459
1460function TSQLiteUniTable.GetFieldIndex(FieldName: string): integer;
1461begin
1462 if (fCols = nil) then
1463 begin
1464 raise ESqliteException.Create('Field ' + fieldname + ' Not found. Empty dataset');
1465 exit;
1466 end;
1467
1468 if (fCols.count = 0) then
1469 begin
1470 raise ESqliteException.Create('Field ' + fieldname + ' Not found. Empty dataset');
1471 exit;
1472 end;
1473
1474 Result := fCols.IndexOf(AnsiUpperCase(FieldName));
1475
1476 if (result < 0) then
1477 begin
1478 raise ESqliteException.Create('Field not found in dataset: ' + fieldname)
1479 end;
1480end;
1481
1482function TSQLiteUniTable.GetFields(I: cardinal): string;
1483begin
1484 Result := Sqlite3_ColumnText(fstmt, i);
1485end;
1486
1487function TSQLiteUniTable.Next: boolean;
1488var
1489 iStepResult: integer;
1490begin
1491 fEOF := true;
1492 iStepResult := Sqlite3_step(fStmt);
1493 case iStepResult of
1494 SQLITE_ROW:
1495 begin
1496 fEOF := false;
1497 inc(fRow);
1498 end;
1499 SQLITE_DONE:
1500 // we are on the end of dataset
1501 // return EOF=true only
1502 ;
1503 else
1504 begin
1505 SQLite3_reset(fStmt);
1506 fDB.RaiseError('Could not retrieve data', fSQL);
1507 end;
1508 end;
1509 Result := not fEOF;
1510end;
1511
1512end.