| 1 | unit 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 sqlite_get_table.
|
|---|
| 10 | It allows accessing fields by name as well as index and can step through a
|
|---|
| 11 | result set with the Next procedure.
|
|---|
| 12 |
|
|---|
| 13 | Adapted by Tim Anderson (tim@itwriting.com)
|
|---|
| 14 | Originally created by Pablo Pissanetzky (pablo@myhtpc.net)
|
|---|
| 15 | }
|
|---|
| 16 |
|
|---|
| 17 | interface
|
|---|
| 18 |
|
|---|
| 19 | uses
|
|---|
| 20 | Windows, SQLite3, Classes, Sysutils;
|
|---|
| 21 |
|
|---|
| 22 | const
|
|---|
| 23 | dtStr = 0;
|
|---|
| 24 | dtInt = 1;
|
|---|
| 25 | dtBool = 2;
|
|---|
| 26 | dtNumeric = 3;
|
|---|
| 27 | dtBlob = 4;
|
|---|
| 28 |
|
|---|
| 29 | type
|
|---|
| 30 |
|
|---|
| 31 | ESQLiteException = class(Exception)
|
|---|
| 32 | private
|
|---|
| 33 | public
|
|---|
| 34 | end;
|
|---|
| 35 |
|
|---|
| 36 | TSQLiteTable = class;
|
|---|
| 37 |
|
|---|
| 38 | TSQLiteDatabase = class
|
|---|
| 39 | private
|
|---|
| 40 | fDB: TSQLiteDB;
|
|---|
| 41 | fInTrans: Boolean;
|
|---|
| 42 | procedure RaiseError(s: string; SQL: string);
|
|---|
| 43 |
|
|---|
| 44 | public
|
|---|
| 45 | constructor Create(const FileName: string);
|
|---|
| 46 | destructor Destroy; override;
|
|---|
| 47 | function GetTable(const SQL: string): TSQLiteTable;
|
|---|
| 48 | procedure ExecSQL(const SQL: string);
|
|---|
| 49 | procedure UpdateBlob(const SQL: string; BlobData: TStream);
|
|---|
| 50 | procedure BeginTransaction;
|
|---|
| 51 | procedure Commit;
|
|---|
| 52 | procedure Rollback;
|
|---|
| 53 | function TableExists(TableName: string): boolean;
|
|---|
| 54 | function GetLastInsertRowID: int64;
|
|---|
| 55 |
|
|---|
| 56 | published
|
|---|
| 57 | property isTransactionOpen: boolean read fInTrans;
|
|---|
| 58 |
|
|---|
| 59 | end;
|
|---|
| 60 |
|
|---|
| 61 | TSQLiteTable = class
|
|---|
| 62 | private
|
|---|
| 63 | fResults: TList;
|
|---|
| 64 | fRowCount: Cardinal;
|
|---|
| 65 | fColCount: Cardinal;
|
|---|
| 66 | fCols: TStringList;
|
|---|
| 67 | fColTypes: TList;
|
|---|
| 68 | fRow: Cardinal;
|
|---|
| 69 |
|
|---|
| 70 | function GetFields(I: Integer): string;
|
|---|
| 71 | function GetEOF: Boolean;
|
|---|
| 72 | function GetBOF: Boolean;
|
|---|
| 73 | function GetColumns(I: Integer): string;
|
|---|
| 74 | function GetFieldByName(FieldName: string): string;
|
|---|
| 75 | function GetFieldIndex(FieldName: string): integer;
|
|---|
| 76 | function GetCount: Integer;
|
|---|
| 77 | function GetCountResult: Integer;
|
|---|
| 78 |
|
|---|
| 79 |
|
|---|
| 80 | public
|
|---|
| 81 | constructor Create(DB: TSQLiteDatabase; const SQL: string);
|
|---|
| 82 | destructor Destroy; override;
|
|---|
| 83 | function FieldAsInteger(FieldName: string): integer;
|
|---|
| 84 | function FieldAsBool(FieldName: string): boolean;
|
|---|
| 85 | function FieldAsBlob(FieldName: string): TMemoryStream;
|
|---|
| 86 | function FieldAsBlobText(FieldName: string): string;
|
|---|
| 87 | function FieldIsNull(FieldName: string): boolean;
|
|---|
| 88 | function FieldAsString(FieldName: string): string;
|
|---|
| 89 | function FieldAsDouble(FieldName: string): double;
|
|---|
| 90 | { function FieldAsInteger(I: integer): integer;
|
|---|
| 91 | function FieldAsBool(I: integer): boolean;
|
|---|
| 92 | function FieldAsBlob(I: Integer): TMemoryStream;
|
|---|
| 93 | function FieldAsBlobText(I: Integer): string;
|
|---|
| 94 | function FieldIsNull(I: integer): boolean;
|
|---|
| 95 | function FieldAsString(I: Integer): string;
|
|---|
| 96 | function FieldAsDouble(I: Integer): double;
|
|---|
| 97 | } function Next: Boolean;
|
|---|
| 98 | function Previous: Boolean;
|
|---|
| 99 | property EOF: Boolean read GetEOF;
|
|---|
| 100 | property BOF: Boolean read GetBOF;
|
|---|
| 101 | property Fields[I: Integer]: string read GetFields;
|
|---|
| 102 | property FieldByName[FieldName: string]: string read GetFieldByName;
|
|---|
| 103 | property FieldIndex[FieldName: string]: integer read GetFieldIndex;
|
|---|
| 104 | property Columns[I: Integer]: string read GetColumns;
|
|---|
| 105 | property ColCount: Cardinal read fColCount;
|
|---|
| 106 | property RowCount: Cardinal read fRowCount;
|
|---|
| 107 | property Row: Cardinal read fRow;
|
|---|
| 108 | function MoveFirst: boolean;
|
|---|
| 109 | function MoveLast: boolean;
|
|---|
| 110 |
|
|---|
| 111 |
|
|---|
| 112 | property Count: Integer read GetCount;
|
|---|
| 113 |
|
|---|
| 114 | // The property CountResult is used when you execute count(*) queries.
|
|---|
| 115 | // It returns 0 if the result set is empty or the value of the
|
|---|
| 116 | // first field as an integer.
|
|---|
| 117 | property CountResult: Integer read GetCountResult;
|
|---|
| 118 | end;
|
|---|
| 119 |
|
|---|
| 120 |
|
|---|
| 121 | procedure DisposePointer(ptr: pointer); cdecl;
|
|---|
| 122 |
|
|---|
| 123 | implementation
|
|---|
| 124 |
|
|---|
| 125 | uses
|
|---|
| 126 | strutils;
|
|---|
| 127 |
|
|---|
| 128 |
|
|---|
| 129 | procedure DisposePointer(ptr: pointer); cdecl;
|
|---|
| 130 | begin
|
|---|
| 131 |
|
|---|
| 132 | if assigned(ptr) then
|
|---|
| 133 | begin freemem(ptr) end;
|
|---|
| 134 |
|
|---|
| 135 | end;
|
|---|
| 136 |
|
|---|
| 137 | //------------------------------------------------------------------------------
|
|---|
| 138 | // TSQLiteDatabase
|
|---|
| 139 | //------------------------------------------------------------------------------
|
|---|
| 140 |
|
|---|
| 141 | constructor TSQLiteDatabase.Create(const FileName: string);
|
|---|
| 142 | var
|
|---|
| 143 | Msg: pchar;
|
|---|
| 144 | iResult: integer;
|
|---|
| 145 | begin
|
|---|
| 146 | inherited Create;
|
|---|
| 147 |
|
|---|
| 148 | self.fInTrans := false;
|
|---|
| 149 |
|
|---|
| 150 | Msg := nil;
|
|---|
| 151 | try
|
|---|
| 152 | iResult := SQLite3_Open(PChar(FileName), Fdb);
|
|---|
| 153 |
|
|---|
| 154 | if iResult <> SQLITE_OK then
|
|---|
| 155 | begin
|
|---|
| 156 | if Assigned(Fdb) then
|
|---|
| 157 | begin
|
|---|
| 158 | Msg := Sqlite3_ErrMsg(Fdb);
|
|---|
| 159 | raise ESqliteException.CreateFmt('Failed to open database "%s" : %s', [FileName, Msg]);
|
|---|
| 160 | end
|
|---|
| 161 | else
|
|---|
| 162 | begin raise ESqliteException.CreateFmt('Failed to open database "%s" : unknown error', [FileName]) end;
|
|---|
| 163 | end;
|
|---|
| 164 |
|
|---|
| 165 | //set a few configs
|
|---|
| 166 | self.ExecSQL('PRAGMA SYNCHRONOUS=NORMAL;');
|
|---|
| 167 |
|
|---|
| 168 | //this pragma not recommended and may disappear in future
|
|---|
| 169 | //sqlite versions
|
|---|
| 170 | //self.ExecSQL('PRAGMA full_column_names = 1;');
|
|---|
| 171 |
|
|---|
| 172 | finally
|
|---|
| 173 | if Assigned(Msg) then
|
|---|
| 174 | begin SQLite3_Free(Msg) end;
|
|---|
| 175 | end;
|
|---|
| 176 |
|
|---|
| 177 |
|
|---|
| 178 | end;
|
|---|
| 179 |
|
|---|
| 180 |
|
|---|
| 181 | //..............................................................................
|
|---|
| 182 |
|
|---|
| 183 | destructor TSQLiteDatabase.Destroy;
|
|---|
| 184 | begin
|
|---|
| 185 |
|
|---|
| 186 | if self.fInTrans then
|
|---|
| 187 | begin self.ExecSQL('ROLLBACK;') end; //assume rollback
|
|---|
| 188 |
|
|---|
| 189 | if Assigned(fDB) then
|
|---|
| 190 | begin SQLite3_Close(fDB) end;
|
|---|
| 191 |
|
|---|
| 192 | inherited;
|
|---|
| 193 | end;
|
|---|
| 194 |
|
|---|
| 195 | function TSQLiteDatabase.GetLastInsertRowID: int64;
|
|---|
| 196 | begin
|
|---|
| 197 | result := Sqlite3_LastInsertRowID(self.fDB);
|
|---|
| 198 | end;
|
|---|
| 199 |
|
|---|
| 200 | //..............................................................................
|
|---|
| 201 |
|
|---|
| 202 | procedure TSQLiteDatabase.RaiseError(s: string; SQL: string);
|
|---|
| 203 | //look up last error and raise and exception with an appropriate message
|
|---|
| 204 | var
|
|---|
| 205 | Msg: PChar;
|
|---|
| 206 | begin
|
|---|
| 207 |
|
|---|
| 208 | Msg := nil;
|
|---|
| 209 |
|
|---|
| 210 | if sqlite3_errcode(self.fDB) <> SQLITE_OK then
|
|---|
| 211 | Msg := sqlite3_errmsg(self.fDB);
|
|---|
| 212 |
|
|---|
| 213 | if Msg <> nil then
|
|---|
| 214 | raise ESqliteException.CreateFmt(s + ' "%s" : %s', [SQL, Msg])
|
|---|
| 215 | else
|
|---|
| 216 | raise ESqliteException.CreateFmt(s, [SQL, 'No message']);
|
|---|
| 217 |
|
|---|
| 218 | end;
|
|---|
| 219 |
|
|---|
| 220 | procedure TSQLiteDatabase.ExecSQL(const SQL: string);
|
|---|
| 221 | var
|
|---|
| 222 | Stmt: TSQLiteStmt;
|
|---|
| 223 | NextSQLStatement: Pchar;
|
|---|
| 224 | iStepResult: integer;
|
|---|
| 225 | begin
|
|---|
| 226 | try
|
|---|
| 227 |
|
|---|
| 228 | if Sqlite3_Prepare(self.fDB, PChar(SQL), -1, Stmt, NextSQLStatement) <> SQLITE_OK then
|
|---|
| 229 | begin RaiseError('Error executing SQL', SQL) end;
|
|---|
| 230 |
|
|---|
| 231 | if (Stmt = nil) then
|
|---|
| 232 | begin RaiseError('Could not prepare SQL statement', SQL) end;
|
|---|
| 233 |
|
|---|
| 234 | iStepResult := Sqlite3_step(Stmt);
|
|---|
| 235 |
|
|---|
| 236 | if (iStepResult <> SQLITE_DONE) then
|
|---|
| 237 | begin RaiseError('Error executing SQL statement', SQL) end;
|
|---|
| 238 |
|
|---|
| 239 | finally
|
|---|
| 240 |
|
|---|
| 241 | if Assigned(Stmt) then
|
|---|
| 242 | begin Sqlite3_Finalize(stmt) end;
|
|---|
| 243 |
|
|---|
| 244 | end;
|
|---|
| 245 | end;
|
|---|
| 246 |
|
|---|
| 247 | procedure TSQLiteDatabase.UpdateBlob(const SQL: string; BlobData: TStream);
|
|---|
| 248 | var
|
|---|
| 249 | iSize: integer;
|
|---|
| 250 | ptr: pointer;
|
|---|
| 251 | Stmt: TSQLiteStmt;
|
|---|
| 252 | Msg: Pchar;
|
|---|
| 253 | NextSQLStatement: Pchar;
|
|---|
| 254 | iStepResult: integer;
|
|---|
| 255 | iBindResult: integer;
|
|---|
| 256 | begin
|
|---|
| 257 | //expects SQL of the form 'UPDATE MYTABLE SET MYFIELD = ? WHERE MYKEY = 1'
|
|---|
| 258 |
|
|---|
| 259 | if pos('?', SQL) = 0 then
|
|---|
| 260 | begin RaiseError('SQL must include a ? parameter', SQL) end;
|
|---|
| 261 |
|
|---|
| 262 | Msg := nil;
|
|---|
| 263 | try
|
|---|
| 264 |
|
|---|
| 265 | if Sqlite3_Prepare(self.fDB, PChar(SQL), -1, Stmt, NextSQLStatement) <> SQLITE_OK then
|
|---|
| 266 | begin RaiseError('Could not prepare SQL statement', SQL) end;
|
|---|
| 267 |
|
|---|
| 268 | if (Stmt = nil) then
|
|---|
| 269 | begin RaiseError('Could not prepare SQL statement', SQL) end;
|
|---|
| 270 |
|
|---|
| 271 | //now bind the blob data
|
|---|
| 272 | iSize := BlobData.size;
|
|---|
| 273 |
|
|---|
| 274 | GetMem(ptr, iSize);
|
|---|
| 275 |
|
|---|
| 276 | if (ptr = nil) then
|
|---|
| 277 | begin raise ESqliteException.CreateFmt('Error getting memory to save blob', [SQL, 'Error']) end;
|
|---|
| 278 |
|
|---|
| 279 | BlobData.position := 0;
|
|---|
| 280 | BlobData.Read(ptr^, iSize);
|
|---|
| 281 |
|
|---|
| 282 | iBindResult := SQLite3_BindBlob(stmt, 1, ptr, iSize, @DisposePointer);
|
|---|
| 283 |
|
|---|
| 284 | if iBindResult <> SQLITE_OK then
|
|---|
| 285 | begin RaiseError('Error binding blob to database', SQL) end;
|
|---|
| 286 |
|
|---|
| 287 | iStepResult := Sqlite3_step(Stmt);
|
|---|
| 288 |
|
|---|
| 289 | if (iStepResult <> SQLITE_DONE) then
|
|---|
| 290 | begin RaiseError('Error executing SQL statement', SQL) end;
|
|---|
| 291 |
|
|---|
| 292 | finally
|
|---|
| 293 |
|
|---|
| 294 | if Assigned(Stmt) then
|
|---|
| 295 | begin Sqlite3_Finalize(stmt) end;
|
|---|
| 296 |
|
|---|
| 297 | if Assigned(Msg) then
|
|---|
| 298 | begin SQLite3_Free(Msg) end;
|
|---|
| 299 | end;
|
|---|
| 300 |
|
|---|
| 301 | end;
|
|---|
| 302 |
|
|---|
| 303 | //..............................................................................
|
|---|
| 304 |
|
|---|
| 305 | function TSQLiteDatabase.GetTable(const SQL: string): TSQLiteTable;
|
|---|
| 306 | begin
|
|---|
| 307 | Result := TSQLiteTable.Create(Self, SQL);
|
|---|
| 308 | end;
|
|---|
| 309 |
|
|---|
| 310 | procedure TSQLiteDatabase.BeginTransaction;
|
|---|
| 311 | begin
|
|---|
| 312 | if not self.fInTrans then
|
|---|
| 313 | begin
|
|---|
| 314 | self.ExecSQL('BEGIN TRANSACTION;');
|
|---|
| 315 | self.fInTrans := true;
|
|---|
| 316 | end
|
|---|
| 317 | else
|
|---|
| 318 | begin raise ESqliteException.Create('Transaction already open') end;
|
|---|
| 319 | end;
|
|---|
| 320 |
|
|---|
| 321 | procedure TSQLiteDatabase.Commit;
|
|---|
| 322 | begin
|
|---|
| 323 | self.ExecSQL('COMMIT;');
|
|---|
| 324 | self.fInTrans := false;
|
|---|
| 325 | end;
|
|---|
| 326 |
|
|---|
| 327 | procedure TSQLiteDatabase.Rollback;
|
|---|
| 328 | begin
|
|---|
| 329 | self.ExecSQL('ROLLBACK;');
|
|---|
| 330 | self.fInTrans := false;
|
|---|
| 331 | end;
|
|---|
| 332 |
|
|---|
| 333 | function TSQLiteDatabase.TableExists(TableName: string): boolean;
|
|---|
| 334 | var
|
|---|
| 335 | sql: string;
|
|---|
| 336 | ds: TSqliteTable;
|
|---|
| 337 | begin
|
|---|
| 338 | //returns true if table exists in the database
|
|---|
| 339 | sql := 'select [sql] from sqlite_master where [type] = ''table'' and lower(name) = ''' + lowercase(TableName) + ''' ';
|
|---|
| 340 |
|
|---|
| 341 | try
|
|---|
| 342 |
|
|---|
| 343 | ds := self.GetTable(sql);
|
|---|
| 344 |
|
|---|
| 345 | result := (ds.Count > 0);
|
|---|
| 346 |
|
|---|
| 347 | finally
|
|---|
| 348 |
|
|---|
| 349 | freeandnil(ds);
|
|---|
| 350 |
|
|---|
| 351 | end;
|
|---|
| 352 |
|
|---|
| 353 | end;
|
|---|
| 354 |
|
|---|
| 355 |
|
|---|
| 356 | //------------------------------------------------------------------------------
|
|---|
| 357 | // TSQLiteTable
|
|---|
| 358 | //------------------------------------------------------------------------------
|
|---|
| 359 |
|
|---|
| 360 | constructor TSQLiteTable.Create(DB: TSQLiteDatabase; const SQL: string);
|
|---|
| 361 | var
|
|---|
| 362 | Stmt: TSQLiteStmt;
|
|---|
| 363 | NextSQLStatement: Pchar;
|
|---|
| 364 | iStepResult: integer;
|
|---|
| 365 |
|
|---|
| 366 | ptr: pointer;
|
|---|
| 367 | iNumBytes: integer;
|
|---|
| 368 | thisBlobValue: TMemoryStream;
|
|---|
| 369 | thisStringValue: pstring;
|
|---|
| 370 | thisBoolValue: pBoolean;
|
|---|
| 371 | thisDoubleValue: pDouble;
|
|---|
| 372 | thisIntValue: pInteger;
|
|---|
| 373 | thisColType: pInteger;
|
|---|
| 374 | i: integer;
|
|---|
| 375 | DeclaredColType: Pchar;
|
|---|
| 376 | ActualColType: integer;
|
|---|
| 377 | ptrValue: Pchar;
|
|---|
| 378 |
|
|---|
| 379 | begin
|
|---|
| 380 |
|
|---|
| 381 | try
|
|---|
| 382 |
|
|---|
| 383 | self.fRowCount := 0;
|
|---|
| 384 | self.fColCount := 0;
|
|---|
| 385 |
|
|---|
| 386 | //if there are several SQL statements in SQL, NextSQLStatment points to the
|
|---|
| 387 | //beginning of the next one. Prepare only prepares the first SQL statement.
|
|---|
| 388 |
|
|---|
| 389 | if Sqlite3_Prepare(Db.fDB, PChar(SQL), -1, Stmt, NextSQLStatement) <> SQLITE_OK then
|
|---|
| 390 | begin Db.RaiseError('Error executing SQL', SQL) end;
|
|---|
| 391 |
|
|---|
| 392 | if (Stmt = nil) then
|
|---|
| 393 | begin Db.RaiseError('Could not prepare SQL statement', SQL) end;
|
|---|
| 394 |
|
|---|
| 395 | iStepResult := Sqlite3_step(Stmt);
|
|---|
| 396 |
|
|---|
| 397 | while (iStepResult <> SQLITE_DONE) do
|
|---|
| 398 | begin
|
|---|
| 399 |
|
|---|
| 400 | case iStepResult of
|
|---|
| 401 | SQLITE_ROW:
|
|---|
| 402 | begin
|
|---|
| 403 |
|
|---|
| 404 | inc(fRowCount);
|
|---|
| 405 |
|
|---|
| 406 | if (fRowCount = 1) then
|
|---|
| 407 | begin
|
|---|
| 408 | //get data types
|
|---|
| 409 | fCols := TStringList.Create;
|
|---|
| 410 | fCols.CaseSensitive := False;
|
|---|
| 411 | fColTypes := TList.Create;
|
|---|
| 412 |
|
|---|
| 413 | fColCount := SQLite3_ColumnCount(stmt);
|
|---|
| 414 |
|
|---|
| 415 | for i := 0 to Pred(fColCount) do
|
|---|
| 416 | begin
|
|---|
| 417 | fCols.Add(Sqlite3_ColumnName(stmt, i));
|
|---|
| 418 | end;
|
|---|
| 419 |
|
|---|
| 420 | for i := 0 to Pred(fColCount) do
|
|---|
| 421 | begin
|
|---|
| 422 |
|
|---|
| 423 | new(thisColType);
|
|---|
| 424 | DeclaredColType := Sqlite3_ColumnDeclType(stmt, i);
|
|---|
| 425 |
|
|---|
| 426 | if DeclaredColType = nil then begin
|
|---|
| 427 | //use the actual column type instead
|
|---|
| 428 | //seems to be needed for last_insert_rowid
|
|---|
| 429 | thisColType^ := Sqlite3_ColumnType(stmt, i);
|
|---|
| 430 | end else begin
|
|---|
| 431 | DeclaredColType := strupper(DeclaredColType);
|
|---|
| 432 |
|
|---|
| 433 | if DeclaredColType = 'INTEGER' then
|
|---|
| 434 | begin thisColType^ := dtInt end
|
|---|
| 435 | else
|
|---|
| 436 | if DeclaredColType = 'BOOLEAN' then
|
|---|
| 437 | begin thisColType^ := dtBool end
|
|---|
| 438 | else
|
|---|
| 439 | if (DeclaredColType = 'NUMERIC') or (DeclaredColType = 'FLOAT') or (DeclaredColType = 'DOUBLE') then
|
|---|
| 440 | begin thisColType^ := dtNumeric end
|
|---|
| 441 | else
|
|---|
| 442 | if DeclaredColType = 'BLOB' then
|
|---|
| 443 | begin thisColType^ := dtBlob end
|
|---|
| 444 | else
|
|---|
| 445 | begin thisColType^ := dtStr end;
|
|---|
| 446 | end;
|
|---|
| 447 |
|
|---|
| 448 | fColTypes.Add(thiscoltype);
|
|---|
| 449 | end;
|
|---|
| 450 |
|
|---|
| 451 | fResults := TList.Create;
|
|---|
| 452 |
|
|---|
| 453 | end;
|
|---|
| 454 |
|
|---|
| 455 | //get column values
|
|---|
| 456 | for i := 0 to Pred(ColCount) do
|
|---|
| 457 | begin
|
|---|
| 458 |
|
|---|
| 459 | ActualColType := Sqlite3_ColumnType(stmt, i);
|
|---|
| 460 | if (ActualColType = SQLITE_NULL) then
|
|---|
| 461 | begin fResults.Add(nil) end
|
|---|
| 462 | else
|
|---|
| 463 | begin
|
|---|
| 464 | if pInteger(fColTypes[i])^ = dtInt then
|
|---|
| 465 | begin
|
|---|
| 466 | new(thisintvalue);
|
|---|
| 467 | thisintvalue^ := Sqlite3_ColumnInt(stmt, i);
|
|---|
| 468 | fResults.Add(thisintvalue);
|
|---|
| 469 | end
|
|---|
| 470 | else
|
|---|
| 471 | if pInteger(fColTypes[i])^ = dtBool then
|
|---|
| 472 | begin
|
|---|
| 473 | new(thisboolvalue);
|
|---|
| 474 | thisboolvalue^ := not (Sqlite3_ColumnInt(stmt, i) = 0);
|
|---|
| 475 | fResults.Add(thisboolvalue);
|
|---|
| 476 | end
|
|---|
| 477 | else
|
|---|
| 478 | if pInteger(fColTypes[i])^ = dtNumeric then
|
|---|
| 479 | begin
|
|---|
| 480 | new(thisdoublevalue);
|
|---|
| 481 | thisdoublevalue^ := Sqlite3_ColumnDouble(stmt, i);
|
|---|
| 482 | fResults.Add(thisdoublevalue);
|
|---|
| 483 | end
|
|---|
| 484 | else
|
|---|
| 485 | if pInteger(fColTypes[i])^ = dtBlob then
|
|---|
| 486 | begin
|
|---|
| 487 | iNumBytes := Sqlite3_ColumnBytes(stmt, i);
|
|---|
| 488 |
|
|---|
| 489 | if iNumBytes = 0 then
|
|---|
| 490 | begin thisblobvalue := nil end
|
|---|
| 491 | else
|
|---|
| 492 | begin
|
|---|
| 493 | thisblobvalue := TMemoryStream.Create;
|
|---|
| 494 | thisblobvalue.position := 0;
|
|---|
| 495 | ptr := Sqlite3_ColumnBlob(stmt, i);
|
|---|
| 496 | thisblobvalue.writebuffer(ptr^, iNumBytes);
|
|---|
| 497 | end;
|
|---|
| 498 | fResults.Add(thisblobvalue);
|
|---|
| 499 |
|
|---|
| 500 | end
|
|---|
| 501 | else
|
|---|
| 502 | begin
|
|---|
| 503 | new(thisstringvalue);
|
|---|
| 504 | ptrValue := Sqlite3_ColumnText(stmt, i);
|
|---|
| 505 | setstring(thisstringvalue^, ptrvalue, strlen(ptrvalue));
|
|---|
| 506 | fResults.Add(thisstringvalue);
|
|---|
| 507 | end;
|
|---|
| 508 | end;
|
|---|
| 509 |
|
|---|
| 510 | end;
|
|---|
| 511 |
|
|---|
| 512 |
|
|---|
| 513 |
|
|---|
| 514 | end;
|
|---|
| 515 |
|
|---|
| 516 | SQLITE_BUSY:
|
|---|
| 517 | begin raise ESqliteException.CreateFmt('Could not prepare SQL statement', [SQL, 'SQLite is Busy']) end;
|
|---|
| 518 | else
|
|---|
| 519 | begin Db.RaiseError('Could not retrieve data', SQL) end;
|
|---|
| 520 | end;
|
|---|
| 521 |
|
|---|
| 522 | iStepResult := Sqlite3_step(Stmt);
|
|---|
| 523 |
|
|---|
| 524 | end;
|
|---|
| 525 |
|
|---|
| 526 | fRow := 0;
|
|---|
| 527 |
|
|---|
| 528 | finally
|
|---|
| 529 | if Assigned(Stmt) then
|
|---|
| 530 | begin Sqlite3_Finalize(stmt) end;
|
|---|
| 531 | end;
|
|---|
| 532 |
|
|---|
| 533 | end;
|
|---|
| 534 |
|
|---|
| 535 | //..............................................................................
|
|---|
| 536 |
|
|---|
| 537 | destructor TSQLiteTable.Destroy;
|
|---|
| 538 | var i: integer;
|
|---|
| 539 | iColNo: integer;
|
|---|
| 540 | begin
|
|---|
| 541 |
|
|---|
| 542 |
|
|---|
| 543 | if Assigned(fResults) then
|
|---|
| 544 | begin for i := 0 to fResults.Count - 1 do
|
|---|
| 545 | begin
|
|---|
| 546 | //check for blob type
|
|---|
| 547 | iColNo := (i mod fColCount);
|
|---|
| 548 | case pInteger(self.fColTypes[iColNo])^ of
|
|---|
| 549 | dtBlob:
|
|---|
| 550 | begin
|
|---|
| 551 | TMemoryStream(fResults[i]).free;
|
|---|
| 552 | end;
|
|---|
| 553 | dtStr:
|
|---|
| 554 | begin
|
|---|
| 555 | if fResults[i] <> nil then
|
|---|
| 556 | begin
|
|---|
| 557 | setstring(string(fResults[i]^), nil, 0);
|
|---|
| 558 | dispose(fResults[i]);
|
|---|
| 559 | end;
|
|---|
| 560 | end;
|
|---|
| 561 | else
|
|---|
| 562 | begin
|
|---|
| 563 | dispose(fResults[i])
|
|---|
| 564 | end;
|
|---|
| 565 | end;
|
|---|
| 566 | end;
|
|---|
| 567 | fResults.Free;
|
|---|
| 568 | end;
|
|---|
| 569 |
|
|---|
| 570 | if Assigned(fCols) then
|
|---|
| 571 | begin fCols.Free end;
|
|---|
| 572 |
|
|---|
| 573 | if Assigned(fColTypes) then
|
|---|
| 574 | begin for i := 0 to fColTypes.Count - 1 do
|
|---|
| 575 | begin
|
|---|
| 576 | dispose(fColTypes[i]);
|
|---|
| 577 | end end;
|
|---|
| 578 | fColTypes.Free;
|
|---|
| 579 | inherited;
|
|---|
| 580 | end;
|
|---|
| 581 |
|
|---|
| 582 | //..............................................................................
|
|---|
| 583 |
|
|---|
| 584 | function TSQLiteTable.GetColumns(I: Integer): string;
|
|---|
| 585 | begin
|
|---|
| 586 | Result := fCols[I];
|
|---|
| 587 | end;
|
|---|
| 588 |
|
|---|
| 589 | //..............................................................................
|
|---|
| 590 |
|
|---|
| 591 | function TSQLiteTable.GetCountResult: Integer;
|
|---|
| 592 | begin
|
|---|
| 593 | if not EOF then
|
|---|
| 594 | begin Result := StrToInt(Fields[0]) end
|
|---|
| 595 | else
|
|---|
| 596 | begin Result := 0 end;
|
|---|
| 597 | end;
|
|---|
| 598 |
|
|---|
| 599 | function TSQLiteTable.GetCount: Integer;
|
|---|
| 600 | begin
|
|---|
| 601 | Result := FRowCount;
|
|---|
| 602 | end;
|
|---|
| 603 |
|
|---|
| 604 | //..............................................................................
|
|---|
| 605 |
|
|---|
| 606 | function TSQLiteTable.GetEOF: Boolean;
|
|---|
| 607 | begin
|
|---|
| 608 | Result := fRow >= fRowCount;
|
|---|
| 609 | end;
|
|---|
| 610 |
|
|---|
| 611 | function TSQLiteTable.GetBOF: Boolean;
|
|---|
| 612 | begin
|
|---|
| 613 | Result := fRow <= 0;
|
|---|
| 614 | end;
|
|---|
| 615 |
|
|---|
| 616 | //..............................................................................
|
|---|
| 617 |
|
|---|
| 618 | function TSQLiteTable.GetFieldByName(FieldName: string): string;
|
|---|
| 619 | begin
|
|---|
| 620 | Result := GetFields(self.GetFieldIndex(FieldName));
|
|---|
| 621 | end;
|
|---|
| 622 |
|
|---|
| 623 | function TSQLiteTable.GetFieldIndex(FieldName: string): integer;
|
|---|
| 624 | begin
|
|---|
| 625 |
|
|---|
| 626 | if (fCols = nil) then
|
|---|
| 627 | begin
|
|---|
| 628 | raise ESqliteException.Create('Field ' + fieldname + ' Not found. Empty dataset');
|
|---|
| 629 | exit;
|
|---|
| 630 | end;
|
|---|
| 631 |
|
|---|
| 632 | if (fCols.count = 0) then
|
|---|
| 633 | begin
|
|---|
| 634 | raise ESqliteException.Create('Field ' + fieldname + ' Not found. Empty dataset');
|
|---|
| 635 | exit;
|
|---|
| 636 | end;
|
|---|
| 637 |
|
|---|
| 638 | result := fCols.IndexOf(FieldName);
|
|---|
| 639 |
|
|---|
| 640 | if (result < 0) then
|
|---|
| 641 | begin raise ESqliteException.Create('Field not found in dataset: ' + fieldname) end;
|
|---|
| 642 |
|
|---|
| 643 | end;
|
|---|
| 644 |
|
|---|
| 645 | //..............................................................................
|
|---|
| 646 |
|
|---|
| 647 | function TSQLiteTable.GetFields(I: Integer): string;
|
|---|
| 648 | var
|
|---|
| 649 | thisvalue: pstring;
|
|---|
| 650 | ptr: pointer;
|
|---|
| 651 | thisboolvalue: pBoolean;
|
|---|
| 652 | thistype: integer;
|
|---|
| 653 | begin
|
|---|
| 654 | Result := '';
|
|---|
| 655 |
|
|---|
| 656 | if EOF then
|
|---|
| 657 | begin raise ESqliteException.Create('Table is at End of File') end;
|
|---|
| 658 |
|
|---|
| 659 | //integer and boolean types are not stored in the resultset
|
|---|
| 660 | //as strings, so they should be retrieved using the type-specific
|
|---|
| 661 | //methods
|
|---|
| 662 |
|
|---|
| 663 | thistype := pInteger(self.fColTypes[I])^;
|
|---|
| 664 |
|
|---|
| 665 | if (thistype = dtInt) or (thistype = dtNumeric) or (thistype = dtBlob) then
|
|---|
| 666 | begin
|
|---|
| 667 | ptr := self.fResults[(self.frow * self.fColCount) + I];
|
|---|
| 668 |
|
|---|
| 669 | if ptr <> nil then
|
|---|
| 670 | begin
|
|---|
| 671 | raise ESqliteException.Create('Use the specific methods for integer, numeric or blob fields');
|
|---|
| 672 | end;
|
|---|
| 673 |
|
|---|
| 674 | end
|
|---|
| 675 | else
|
|---|
| 676 | if pInteger(self.fColTypes[I])^ = dtBool then
|
|---|
| 677 | begin
|
|---|
| 678 | thisboolvalue := self.fResults[(self.frow * self.fColCount) + I];
|
|---|
| 679 | if thisboolvalue <> nil then
|
|---|
| 680 | begin if thisboolvalue^ then
|
|---|
| 681 | begin result := '1' end
|
|---|
| 682 | else
|
|---|
| 683 | begin result := '0' end end;
|
|---|
| 684 | end
|
|---|
| 685 |
|
|---|
| 686 | else
|
|---|
| 687 |
|
|---|
| 688 | begin
|
|---|
| 689 |
|
|---|
| 690 | thisvalue := self.fResults[(self.frow * self.fColCount) + I];
|
|---|
| 691 | if (thisvalue <> nil) then
|
|---|
| 692 | begin Result := thisvalue^ end
|
|---|
| 693 | else
|
|---|
| 694 | begin Result := '' end; //return empty string
|
|---|
| 695 | end;
|
|---|
| 696 |
|
|---|
| 697 | end;
|
|---|
| 698 |
|
|---|
| 699 | function TSqliteTable.FieldAsBlob(FieldName: string): TMemoryStream;
|
|---|
| 700 | var
|
|---|
| 701 | i: Integer;
|
|---|
| 702 | begin
|
|---|
| 703 |
|
|---|
| 704 | if EOF then
|
|---|
| 705 | begin raise ESqliteException.Create('Table is at End of File') end;
|
|---|
| 706 |
|
|---|
| 707 | i:=Self.FieldIndex[FieldName];
|
|---|
| 708 |
|
|---|
| 709 | if (self.fResults[(self.frow * self.fColCount) + I] = nil) then
|
|---|
| 710 | begin result := nil end
|
|---|
| 711 | else
|
|---|
| 712 | if pInteger(self.fColTypes[I])^ = dtBlob then
|
|---|
| 713 | begin result := TMemoryStream(self.fResults[(self.frow * self.fColCount) + I]) end
|
|---|
| 714 | else
|
|---|
| 715 | begin raise ESqliteException.Create('Not a Blob field') end;
|
|---|
| 716 | end;
|
|---|
| 717 |
|
|---|
| 718 | function TSqliteTable.FieldAsBlobText(FieldName: string): string;
|
|---|
| 719 | var
|
|---|
| 720 | MemStream: TMemoryStream;
|
|---|
| 721 | Buffer: PChar;
|
|---|
| 722 | begin
|
|---|
| 723 | result := '';
|
|---|
| 724 |
|
|---|
| 725 | MemStream := self.FieldAsBlob(FieldName);
|
|---|
| 726 |
|
|---|
| 727 | if MemStream <> nil then
|
|---|
| 728 | begin if MemStream.Size > 0 then
|
|---|
| 729 | begin
|
|---|
| 730 | MemStream.position := 0;
|
|---|
| 731 |
|
|---|
| 732 | Buffer := stralloc(MemStream.Size + 1);
|
|---|
| 733 | MemStream.readbuffer(Buffer[0], MemStream.Size);
|
|---|
| 734 | (Buffer + MemStream.Size)^ := chr(0);
|
|---|
| 735 | SetString(Result, Buffer, MemStream.size);
|
|---|
| 736 | strdispose(Buffer);
|
|---|
| 737 | end end;
|
|---|
| 738 |
|
|---|
| 739 | end;
|
|---|
| 740 |
|
|---|
| 741 |
|
|---|
| 742 | function TSqliteTable.FieldAsInteger(FieldName: string): integer;
|
|---|
| 743 | var
|
|---|
| 744 | i: Integer;
|
|---|
| 745 | begin
|
|---|
| 746 |
|
|---|
| 747 | if EOF then
|
|---|
| 748 | begin raise ESqliteException.Create('Table is at End of File') end;
|
|---|
| 749 |
|
|---|
| 750 | i:=Self.FieldIndex[FieldName];
|
|---|
| 751 |
|
|---|
| 752 | if (self.fResults[(self.frow * self.fColCount) + I] = nil) then
|
|---|
| 753 | begin result := 0 end
|
|---|
| 754 | else
|
|---|
| 755 | if pInteger(self.fColTypes[I])^ = dtInt then
|
|---|
| 756 | begin result := pInteger(self.fResults[(self.frow * self.fColCount) + I])^ end
|
|---|
| 757 | else
|
|---|
| 758 | if pInteger(self.fColTypes[I])^ = dtNumeric then
|
|---|
| 759 | begin result := trunc(strtofloat(pString(self.fResults[(self.frow * self.fColCount) + I])^)) end
|
|---|
| 760 | else
|
|---|
| 761 | begin raise ESqliteException.Create('Not an integer or numeric field') end;
|
|---|
| 762 |
|
|---|
| 763 | end;
|
|---|
| 764 |
|
|---|
| 765 | function TSqliteTable.FieldAsDouble(FieldName: string): double;
|
|---|
| 766 | var
|
|---|
| 767 | i: Integer;
|
|---|
| 768 | begin
|
|---|
| 769 |
|
|---|
| 770 | if EOF then
|
|---|
| 771 | begin raise ESqliteException.Create('Table is at End of File') end;
|
|---|
| 772 |
|
|---|
| 773 | i:=Self.FieldIndex[FieldName];
|
|---|
| 774 |
|
|---|
| 775 | if (self.fResults[(self.frow * self.fColCount) + I] = nil) then
|
|---|
| 776 | begin result := 0 end
|
|---|
| 777 | else
|
|---|
| 778 | if pInteger(self.fColTypes[I])^ = dtInt then
|
|---|
| 779 | begin result := pInteger(self.fResults[(self.frow * self.fColCount) + I])^ end
|
|---|
| 780 | else
|
|---|
| 781 | if pInteger(self.fColTypes[I])^ = dtNumeric then
|
|---|
| 782 | begin result := pDouble(self.fResults[(self.frow * self.fColCount) + I])^ end
|
|---|
| 783 | else
|
|---|
| 784 | begin raise ESqliteException.Create('Not an integer or numeric field') end;
|
|---|
| 785 |
|
|---|
| 786 | end;
|
|---|
| 787 |
|
|---|
| 788 | function TSqliteTable.FieldAsBool(FieldName: string): boolean;
|
|---|
| 789 | var
|
|---|
| 790 | i: Integer;
|
|---|
| 791 | begin
|
|---|
| 792 |
|
|---|
| 793 | if EOF then
|
|---|
| 794 | begin raise ESqliteException.Create('Table is at End of File') end;
|
|---|
| 795 |
|
|---|
| 796 | i:=Self.FieldIndex[FieldName];
|
|---|
| 797 |
|
|---|
| 798 | if (self.fResults[(self.frow * self.fColCount) + I] = nil) then
|
|---|
| 799 | begin result := false end
|
|---|
| 800 | else
|
|---|
| 801 | if pInteger(self.fColTypes[I])^ = dtBool then
|
|---|
| 802 | begin result := pBoolean(self.fResults[(self.frow * self.fColCount) + I])^ end
|
|---|
| 803 | else
|
|---|
| 804 | begin raise ESqliteException.Create('Not a boolean field') end;
|
|---|
| 805 | end;
|
|---|
| 806 |
|
|---|
| 807 | function TSqliteTable.FieldAsString(FieldName: string): string;
|
|---|
| 808 | var
|
|---|
| 809 | i: Integer;
|
|---|
| 810 | begin
|
|---|
| 811 |
|
|---|
| 812 | if EOF then
|
|---|
| 813 | begin raise ESqliteException.Create('Table is at End of File') end;
|
|---|
| 814 |
|
|---|
| 815 | i:=Self.FieldIndex[FieldName];
|
|---|
| 816 |
|
|---|
| 817 | if (self.fResults[(self.frow * self.fColCount) + I] = nil) then
|
|---|
| 818 | begin result := '' end
|
|---|
| 819 | else
|
|---|
| 820 | begin result := self.GetFields(I) end;
|
|---|
| 821 |
|
|---|
| 822 | end;
|
|---|
| 823 |
|
|---|
| 824 | function TSqliteTable.FieldIsNull(FieldName: string): boolean;
|
|---|
| 825 | var
|
|---|
| 826 | thisvalue: pointer;
|
|---|
| 827 | i: Integer;
|
|---|
| 828 | begin
|
|---|
| 829 |
|
|---|
| 830 | if EOF then
|
|---|
| 831 | begin raise ESqliteException.Create('Table is at End of File') end;
|
|---|
| 832 |
|
|---|
| 833 | i:=Self.FieldIndex[FieldName];
|
|---|
| 834 |
|
|---|
| 835 | thisvalue := self.fResults[(self.frow * self.fColCount) + I];
|
|---|
| 836 | result := (thisvalue = nil);
|
|---|
| 837 | end;
|
|---|
| 838 |
|
|---|
| 839 | //..............................................................................
|
|---|
| 840 |
|
|---|
| 841 | function TSQLiteTable.Next: boolean;
|
|---|
| 842 | begin
|
|---|
| 843 | result := false;
|
|---|
| 844 | if not EOF then
|
|---|
| 845 | begin
|
|---|
| 846 | Inc(fRow);
|
|---|
| 847 | result := true;
|
|---|
| 848 | end;
|
|---|
| 849 | end;
|
|---|
| 850 |
|
|---|
| 851 | function TSQLiteTable.Previous: boolean;
|
|---|
| 852 | begin
|
|---|
| 853 | result := false;
|
|---|
| 854 | if not BOF then
|
|---|
| 855 | begin
|
|---|
| 856 | Dec(fRow);
|
|---|
| 857 | result := true;
|
|---|
| 858 | end;
|
|---|
| 859 | end;
|
|---|
| 860 |
|
|---|
| 861 | function TSQLiteTable.MoveFirst: boolean;
|
|---|
| 862 | begin
|
|---|
| 863 | result := false;
|
|---|
| 864 | if self.fRowCount > 0 then
|
|---|
| 865 | begin
|
|---|
| 866 | fRow := 0;
|
|---|
| 867 | result := true;
|
|---|
| 868 | end;
|
|---|
| 869 | end;
|
|---|
| 870 |
|
|---|
| 871 | function TSQLiteTable.MoveLast: boolean;
|
|---|
| 872 | begin
|
|---|
| 873 | result := false;
|
|---|
| 874 | if self.fRowCount > 0 then
|
|---|
| 875 | begin
|
|---|
| 876 | fRow := fRowCount - 1;
|
|---|
| 877 | result := true;
|
|---|
| 878 | end;
|
|---|
| 879 | end;
|
|---|
| 880 |
|
|---|
| 881 |
|
|---|
| 882 | end.
|
|---|
| 883 |
|
|---|
