| 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 Pos('DROP TABLE ',SQL)>0 THEN Exit;
|
|---|
| 214 |
|
|---|
| 215 | if Msg <> nil then
|
|---|
| 216 | raise ESqliteException.CreateFmt(s + ' "%s" : %s', [SQL, Msg])
|
|---|
| 217 | else
|
|---|
| 218 | raise ESqliteException.CreateFmt(s, [SQL, 'No message']);
|
|---|
| 219 |
|
|---|
| 220 | end;
|
|---|
| 221 |
|
|---|
| 222 | procedure TSQLiteDatabase.ExecSQL(const SQL: string);
|
|---|
| 223 | var
|
|---|
| 224 | Stmt: TSQLiteStmt;
|
|---|
| 225 | NextSQLStatement: Pchar;
|
|---|
| 226 | iStepResult: integer;
|
|---|
| 227 | begin
|
|---|
| 228 | try
|
|---|
| 229 |
|
|---|
| 230 | if Sqlite3_Prepare(self.fDB, PChar(SQL), -1, Stmt, NextSQLStatement) <> SQLITE_OK then
|
|---|
| 231 | begin RaiseError('Error executing SQL', SQL) end;
|
|---|
| 232 |
|
|---|
| 233 | if (Stmt = nil) then
|
|---|
| 234 | begin RaiseError('Could not prepare SQL statement', SQL) end;
|
|---|
| 235 |
|
|---|
| 236 | iStepResult := Sqlite3_step(Stmt);
|
|---|
| 237 |
|
|---|
| 238 | if (iStepResult <> SQLITE_DONE) then
|
|---|
| 239 | begin RaiseError('Error executing SQL statement', SQL) end;
|
|---|
| 240 |
|
|---|
| 241 | finally
|
|---|
| 242 |
|
|---|
| 243 | if Assigned(Stmt) then
|
|---|
| 244 | begin Sqlite3_Finalize(stmt) end;
|
|---|
| 245 |
|
|---|
| 246 | end;
|
|---|
| 247 | end;
|
|---|
| 248 |
|
|---|
| 249 | procedure TSQLiteDatabase.UpdateBlob(const SQL: string; BlobData: TStream);
|
|---|
| 250 | var
|
|---|
| 251 | iSize: integer;
|
|---|
| 252 | ptr: pointer;
|
|---|
| 253 | Stmt: TSQLiteStmt;
|
|---|
| 254 | Msg: Pchar;
|
|---|
| 255 | NextSQLStatement: Pchar;
|
|---|
| 256 | iStepResult: integer;
|
|---|
| 257 | iBindResult: integer;
|
|---|
| 258 | begin
|
|---|
| 259 | //expects SQL of the form 'UPDATE MYTABLE SET MYFIELD = ? WHERE MYKEY = 1'
|
|---|
| 260 |
|
|---|
| 261 | if pos('?', SQL) = 0 then
|
|---|
| 262 | begin RaiseError('SQL must include a ? parameter', SQL) end;
|
|---|
| 263 |
|
|---|
| 264 | Msg := nil;
|
|---|
| 265 | try
|
|---|
| 266 |
|
|---|
| 267 | if Sqlite3_Prepare(self.fDB, PChar(SQL), -1, Stmt, NextSQLStatement) <> SQLITE_OK then
|
|---|
| 268 | begin RaiseError('Could not prepare SQL statement', SQL) end;
|
|---|
| 269 |
|
|---|
| 270 | if (Stmt = nil) then
|
|---|
| 271 | begin RaiseError('Could not prepare SQL statement', SQL) end;
|
|---|
| 272 |
|
|---|
| 273 | //now bind the blob data
|
|---|
| 274 | iSize := BlobData.size;
|
|---|
| 275 |
|
|---|
| 276 | GetMem(ptr, iSize);
|
|---|
| 277 |
|
|---|
| 278 | if (ptr = nil) then
|
|---|
| 279 | begin raise ESqliteException.CreateFmt('Error getting memory to save blob', [SQL, 'Error']) end;
|
|---|
| 280 |
|
|---|
| 281 | BlobData.position := 0;
|
|---|
| 282 | BlobData.Read(ptr^, iSize);
|
|---|
| 283 |
|
|---|
| 284 | iBindResult := SQLite3_BindBlob(stmt, 1, ptr, iSize, @DisposePointer);
|
|---|
| 285 |
|
|---|
| 286 | if iBindResult <> SQLITE_OK then
|
|---|
| 287 | begin RaiseError('Error binding blob to database', SQL) end;
|
|---|
| 288 |
|
|---|
| 289 | iStepResult := Sqlite3_step(Stmt);
|
|---|
| 290 |
|
|---|
| 291 | if (iStepResult <> SQLITE_DONE) then
|
|---|
| 292 | begin RaiseError('Error executing SQL statement', SQL) end;
|
|---|
| 293 |
|
|---|
| 294 | finally
|
|---|
| 295 |
|
|---|
| 296 | if Assigned(Stmt) then
|
|---|
| 297 | begin Sqlite3_Finalize(stmt) end;
|
|---|
| 298 |
|
|---|
| 299 | if Assigned(Msg) then
|
|---|
| 300 | begin SQLite3_Free(Msg) end;
|
|---|
| 301 | end;
|
|---|
| 302 |
|
|---|
| 303 | end;
|
|---|
| 304 |
|
|---|
| 305 | //..............................................................................
|
|---|
| 306 |
|
|---|
| 307 | function TSQLiteDatabase.GetTable(const SQL: string): TSQLiteTable;
|
|---|
| 308 | begin
|
|---|
| 309 | Result := TSQLiteTable.Create(Self, SQL);
|
|---|
| 310 | end;
|
|---|
| 311 |
|
|---|
| 312 | procedure TSQLiteDatabase.BeginTransaction;
|
|---|
| 313 | begin
|
|---|
| 314 | if not self.fInTrans then
|
|---|
| 315 | begin
|
|---|
| 316 | self.ExecSQL('BEGIN TRANSACTION;');
|
|---|
| 317 | self.fInTrans := true;
|
|---|
| 318 | end
|
|---|
| 319 | else
|
|---|
| 320 | begin raise ESqliteException.Create('Transaction already open') end;
|
|---|
| 321 | end;
|
|---|
| 322 |
|
|---|
| 323 | procedure TSQLiteDatabase.Commit;
|
|---|
| 324 | begin
|
|---|
| 325 | self.ExecSQL('COMMIT;');
|
|---|
| 326 | self.fInTrans := false;
|
|---|
| 327 | end;
|
|---|
| 328 |
|
|---|
| 329 | procedure TSQLiteDatabase.Rollback;
|
|---|
| 330 | begin
|
|---|
| 331 | self.ExecSQL('ROLLBACK;');
|
|---|
| 332 | self.fInTrans := false;
|
|---|
| 333 | end;
|
|---|
| 334 |
|
|---|
| 335 | function TSQLiteDatabase.TableExists(TableName: string): boolean;
|
|---|
| 336 | var
|
|---|
| 337 | sql: string;
|
|---|
| 338 | ds: TSqliteTable;
|
|---|
| 339 | begin
|
|---|
| 340 | //returns true if table exists in the database
|
|---|
| 341 | sql := 'select [sql] from sqlite_master where [type] = ''table'' and lower(name) = ''' + lowercase(TableName) + ''' ';
|
|---|
| 342 |
|
|---|
| 343 | try
|
|---|
| 344 |
|
|---|
| 345 | ds := self.GetTable(sql);
|
|---|
| 346 |
|
|---|
| 347 | result := (ds.Count > 0);
|
|---|
| 348 |
|
|---|
| 349 | finally
|
|---|
| 350 |
|
|---|
| 351 | freeandnil(ds);
|
|---|
| 352 |
|
|---|
| 353 | end;
|
|---|
| 354 |
|
|---|
| 355 | end;
|
|---|
| 356 |
|
|---|
| 357 |
|
|---|
| 358 | //------------------------------------------------------------------------------
|
|---|
| 359 | // TSQLiteTable
|
|---|
| 360 | //------------------------------------------------------------------------------
|
|---|
| 361 |
|
|---|
| 362 | constructor TSQLiteTable.Create(DB: TSQLiteDatabase; const SQL: string);
|
|---|
| 363 | var
|
|---|
| 364 | Stmt: TSQLiteStmt;
|
|---|
| 365 | NextSQLStatement: Pchar;
|
|---|
| 366 | iStepResult: integer;
|
|---|
| 367 |
|
|---|
| 368 | ptr: pointer;
|
|---|
| 369 | iNumBytes: integer;
|
|---|
| 370 | thisBlobValue: TMemoryStream;
|
|---|
| 371 | thisStringValue: pstring;
|
|---|
| 372 | thisBoolValue: pBoolean;
|
|---|
| 373 | thisDoubleValue: pDouble;
|
|---|
| 374 | thisIntValue: pInteger;
|
|---|
| 375 | thisColType: pInteger;
|
|---|
| 376 | i: integer;
|
|---|
| 377 | DeclaredColType: Pchar;
|
|---|
| 378 | ActualColType: integer;
|
|---|
| 379 | ptrValue: Pchar;
|
|---|
| 380 |
|
|---|
| 381 | begin
|
|---|
| 382 |
|
|---|
| 383 | try
|
|---|
| 384 |
|
|---|
| 385 | self.fRowCount := 0;
|
|---|
| 386 | self.fColCount := 0;
|
|---|
| 387 |
|
|---|
| 388 | //if there are several SQL statements in SQL, NextSQLStatment points to the
|
|---|
| 389 | //beginning of the next one. Prepare only prepares the first SQL statement.
|
|---|
| 390 |
|
|---|
| 391 | if Sqlite3_Prepare(Db.fDB, PChar(SQL), -1, Stmt, NextSQLStatement) <> SQLITE_OK then
|
|---|
| 392 | begin Db.RaiseError('Error executing SQL', SQL) end;
|
|---|
| 393 |
|
|---|
| 394 | if (Stmt = nil) then
|
|---|
| 395 | begin Db.RaiseError('Could not prepare SQL statement', SQL) end;
|
|---|
| 396 |
|
|---|
| 397 | iStepResult := Sqlite3_step(Stmt);
|
|---|
| 398 |
|
|---|
| 399 | while (iStepResult <> SQLITE_DONE) do
|
|---|
| 400 | begin
|
|---|
| 401 |
|
|---|
| 402 | case iStepResult of
|
|---|
| 403 | SQLITE_ROW:
|
|---|
| 404 | begin
|
|---|
| 405 |
|
|---|
| 406 | inc(fRowCount);
|
|---|
| 407 |
|
|---|
| 408 | if (fRowCount = 1) then
|
|---|
| 409 | begin
|
|---|
| 410 | //get data types
|
|---|
| 411 | fCols := TStringList.Create;
|
|---|
| 412 | fCols.CaseSensitive := False;
|
|---|
| 413 | fColTypes := TList.Create;
|
|---|
| 414 |
|
|---|
| 415 | fColCount := SQLite3_ColumnCount(stmt);
|
|---|
| 416 |
|
|---|
| 417 | for i := 0 to Pred(fColCount) do
|
|---|
| 418 | begin
|
|---|
| 419 | fCols.Add(Sqlite3_ColumnName(stmt, i));
|
|---|
| 420 | end;
|
|---|
| 421 |
|
|---|
| 422 | for i := 0 to Pred(fColCount) do
|
|---|
| 423 | begin
|
|---|
| 424 |
|
|---|
| 425 | new(thisColType);
|
|---|
| 426 | DeclaredColType := Sqlite3_ColumnDeclType(stmt, i);
|
|---|
| 427 |
|
|---|
| 428 | if DeclaredColType = nil then begin
|
|---|
| 429 | //use the actual column type instead
|
|---|
| 430 | //seems to be needed for last_insert_rowid
|
|---|
| 431 | thisColType^ := Sqlite3_ColumnType(stmt, i);
|
|---|
| 432 | end else begin
|
|---|
| 433 | DeclaredColType := strupper(DeclaredColType);
|
|---|
| 434 |
|
|---|
| 435 | if DeclaredColType = 'INTEGER' then
|
|---|
| 436 | begin thisColType^ := dtInt end
|
|---|
| 437 | else
|
|---|
| 438 | if DeclaredColType = 'BOOLEAN' then
|
|---|
| 439 | begin thisColType^ := dtBool end
|
|---|
| 440 | else
|
|---|
| 441 | if (DeclaredColType = 'NUMERIC') or (DeclaredColType = 'FLOAT') or (DeclaredColType = 'DOUBLE') then
|
|---|
| 442 | begin thisColType^ := dtNumeric end
|
|---|
| 443 | else
|
|---|
| 444 | if DeclaredColType = 'BLOB' then
|
|---|
| 445 | begin thisColType^ := dtBlob end
|
|---|
| 446 | else
|
|---|
| 447 | begin thisColType^ := dtStr end;
|
|---|
| 448 | end;
|
|---|
| 449 |
|
|---|
| 450 | fColTypes.Add(thiscoltype);
|
|---|
| 451 | end;
|
|---|
| 452 |
|
|---|
| 453 | fResults := TList.Create;
|
|---|
| 454 |
|
|---|
| 455 | end;
|
|---|
| 456 |
|
|---|
| 457 | //get column values
|
|---|
| 458 | for i := 0 to Pred(ColCount) do
|
|---|
| 459 | begin
|
|---|
| 460 |
|
|---|
| 461 | ActualColType := Sqlite3_ColumnType(stmt, i);
|
|---|
| 462 | if (ActualColType = SQLITE_NULL) then
|
|---|
| 463 | begin fResults.Add(nil) end
|
|---|
| 464 | else
|
|---|
| 465 | begin
|
|---|
| 466 | if pInteger(fColTypes[i])^ = dtInt then
|
|---|
| 467 | begin
|
|---|
| 468 | new(thisintvalue);
|
|---|
| 469 | thisintvalue^ := Sqlite3_ColumnInt(stmt, i);
|
|---|
| 470 | fResults.Add(thisintvalue);
|
|---|
| 471 | end
|
|---|
| 472 | else
|
|---|
| 473 | if pInteger(fColTypes[i])^ = dtBool then
|
|---|
| 474 | begin
|
|---|
| 475 | new(thisboolvalue);
|
|---|
| 476 | thisboolvalue^ := not (Sqlite3_ColumnInt(stmt, i) = 0);
|
|---|
| 477 | fResults.Add(thisboolvalue);
|
|---|
| 478 | end
|
|---|
| 479 | else
|
|---|
| 480 | if pInteger(fColTypes[i])^ = dtNumeric then
|
|---|
| 481 | begin
|
|---|
| 482 | new(thisdoublevalue);
|
|---|
| 483 | thisdoublevalue^ := Sqlite3_ColumnDouble(stmt, i);
|
|---|
| 484 | fResults.Add(thisdoublevalue);
|
|---|
| 485 | end
|
|---|
| 486 | else
|
|---|
| 487 | if pInteger(fColTypes[i])^ = dtBlob then
|
|---|
| 488 | begin
|
|---|
| 489 | iNumBytes := Sqlite3_ColumnBytes(stmt, i);
|
|---|
| 490 |
|
|---|
| 491 | if iNumBytes = 0 then
|
|---|
| 492 | begin thisblobvalue := nil end
|
|---|
| 493 | else
|
|---|
| 494 | begin
|
|---|
| 495 | thisblobvalue := TMemoryStream.Create;
|
|---|
| 496 | thisblobvalue.position := 0;
|
|---|
| 497 | ptr := Sqlite3_ColumnBlob(stmt, i);
|
|---|
| 498 | thisblobvalue.writebuffer(ptr^, iNumBytes);
|
|---|
| 499 | end;
|
|---|
| 500 | fResults.Add(thisblobvalue);
|
|---|
| 501 |
|
|---|
| 502 | end
|
|---|
| 503 | else
|
|---|
| 504 | begin
|
|---|
| 505 | new(thisstringvalue);
|
|---|
| 506 | ptrValue := Sqlite3_ColumnText(stmt, i);
|
|---|
| 507 | setstring(thisstringvalue^, ptrvalue, strlen(ptrvalue));
|
|---|
| 508 | fResults.Add(thisstringvalue);
|
|---|
| 509 | end;
|
|---|
| 510 | end;
|
|---|
| 511 |
|
|---|
| 512 | end;
|
|---|
| 513 |
|
|---|
| 514 |
|
|---|
| 515 |
|
|---|
| 516 | end;
|
|---|
| 517 |
|
|---|
| 518 | SQLITE_BUSY:
|
|---|
| 519 | begin raise ESqliteException.CreateFmt('Could not prepare SQL statement', [SQL, 'SQLite is Busy']) end;
|
|---|
| 520 | else
|
|---|
| 521 | begin Db.RaiseError('Could not retrieve data', SQL) end;
|
|---|
| 522 | end;
|
|---|
| 523 |
|
|---|
| 524 | iStepResult := Sqlite3_step(Stmt);
|
|---|
| 525 |
|
|---|
| 526 | end;
|
|---|
| 527 |
|
|---|
| 528 | fRow := 0;
|
|---|
| 529 |
|
|---|
| 530 | finally
|
|---|
| 531 | if Assigned(Stmt) then
|
|---|
| 532 | begin Sqlite3_Finalize(stmt) end;
|
|---|
| 533 | end;
|
|---|
| 534 |
|
|---|
| 535 | end;
|
|---|
| 536 |
|
|---|
| 537 | //..............................................................................
|
|---|
| 538 |
|
|---|
| 539 | destructor TSQLiteTable.Destroy;
|
|---|
| 540 | var i: integer;
|
|---|
| 541 | iColNo: integer;
|
|---|
| 542 | begin
|
|---|
| 543 |
|
|---|
| 544 |
|
|---|
| 545 | if Assigned(fResults) then
|
|---|
| 546 | begin for i := 0 to fResults.Count - 1 do
|
|---|
| 547 | begin
|
|---|
| 548 | //check for blob type
|
|---|
| 549 | iColNo := (i mod fColCount);
|
|---|
| 550 | case pInteger(self.fColTypes[iColNo])^ of
|
|---|
| 551 | dtBlob:
|
|---|
| 552 | begin
|
|---|
| 553 | TMemoryStream(fResults[i]).free;
|
|---|
| 554 | end;
|
|---|
| 555 | dtStr:
|
|---|
| 556 | begin
|
|---|
| 557 | if fResults[i] <> nil then
|
|---|
| 558 | begin
|
|---|
| 559 | setstring(string(fResults[i]^), nil, 0);
|
|---|
| 560 | dispose(fResults[i]);
|
|---|
| 561 | end;
|
|---|
| 562 | end;
|
|---|
| 563 | else
|
|---|
| 564 | begin
|
|---|
| 565 | dispose(fResults[i])
|
|---|
| 566 | end;
|
|---|
| 567 | end;
|
|---|
| 568 | end;
|
|---|
| 569 | fResults.Free;
|
|---|
| 570 | end;
|
|---|
| 571 |
|
|---|
| 572 | if Assigned(fCols) then
|
|---|
| 573 | begin fCols.Free end;
|
|---|
| 574 |
|
|---|
| 575 | if Assigned(fColTypes) then
|
|---|
| 576 | begin for i := 0 to fColTypes.Count - 1 do
|
|---|
| 577 | begin
|
|---|
| 578 | dispose(fColTypes[i]);
|
|---|
| 579 | end end;
|
|---|
| 580 | fColTypes.Free;
|
|---|
| 581 | inherited;
|
|---|
| 582 | end;
|
|---|
| 583 |
|
|---|
| 584 | //..............................................................................
|
|---|
| 585 |
|
|---|
| 586 | function TSQLiteTable.GetColumns(I: Integer): string;
|
|---|
| 587 | begin
|
|---|
| 588 | Result := fCols[I];
|
|---|
| 589 | end;
|
|---|
| 590 |
|
|---|
| 591 | //..............................................................................
|
|---|
| 592 |
|
|---|
| 593 | function TSQLiteTable.GetCountResult: Integer;
|
|---|
| 594 | begin
|
|---|
| 595 | if not EOF then
|
|---|
| 596 | begin Result := StrToInt(Fields[0]) end
|
|---|
| 597 | else
|
|---|
| 598 | begin Result := 0 end;
|
|---|
| 599 | end;
|
|---|
| 600 |
|
|---|
| 601 | function TSQLiteTable.GetCount: Integer;
|
|---|
| 602 | begin
|
|---|
| 603 | Result := FRowCount;
|
|---|
| 604 | end;
|
|---|
| 605 |
|
|---|
| 606 | //..............................................................................
|
|---|
| 607 |
|
|---|
| 608 | function TSQLiteTable.GetEOF: Boolean;
|
|---|
| 609 | begin
|
|---|
| 610 | Result := fRow >= fRowCount;
|
|---|
| 611 | end;
|
|---|
| 612 |
|
|---|
| 613 | function TSQLiteTable.GetBOF: Boolean;
|
|---|
| 614 | begin
|
|---|
| 615 | Result := fRow <= 0;
|
|---|
| 616 | end;
|
|---|
| 617 |
|
|---|
| 618 | //..............................................................................
|
|---|
| 619 |
|
|---|
| 620 | function TSQLiteTable.GetFieldByName(FieldName: string): string;
|
|---|
| 621 | begin
|
|---|
| 622 | Result := GetFields(self.GetFieldIndex(FieldName));
|
|---|
| 623 | end;
|
|---|
| 624 |
|
|---|
| 625 | function TSQLiteTable.GetFieldIndex(FieldName: string): integer;
|
|---|
| 626 | begin
|
|---|
| 627 |
|
|---|
| 628 | if (fCols = nil) then
|
|---|
| 629 | begin
|
|---|
| 630 | raise ESqliteException.Create('Field ' + fieldname + ' Not found. Empty dataset');
|
|---|
| 631 | exit;
|
|---|
| 632 | end;
|
|---|
| 633 |
|
|---|
| 634 | if (fCols.count = 0) then
|
|---|
| 635 | begin
|
|---|
| 636 | raise ESqliteException.Create('Field ' + fieldname + ' Not found. Empty dataset');
|
|---|
| 637 | exit;
|
|---|
| 638 | end;
|
|---|
| 639 |
|
|---|
| 640 | result := fCols.IndexOf(FieldName);
|
|---|
| 641 |
|
|---|
| 642 | if (result < 0) then
|
|---|
| 643 | begin raise ESqliteException.Create('Field not found in dataset: ' + fieldname) end;
|
|---|
| 644 |
|
|---|
| 645 | end;
|
|---|
| 646 |
|
|---|
| 647 | //..............................................................................
|
|---|
| 648 |
|
|---|
| 649 | function TSQLiteTable.GetFields(I: Integer): string;
|
|---|
| 650 | var
|
|---|
| 651 | thisvalue: pstring;
|
|---|
| 652 | ptr: pointer;
|
|---|
| 653 | thisboolvalue: pBoolean;
|
|---|
| 654 | thistype: integer;
|
|---|
| 655 | begin
|
|---|
| 656 | Result := '';
|
|---|
| 657 |
|
|---|
| 658 | if EOF then
|
|---|
| 659 | begin raise ESqliteException.Create('Table is at End of File') end;
|
|---|
| 660 |
|
|---|
| 661 | //integer and boolean types are not stored in the resultset
|
|---|
| 662 | //as strings, so they should be retrieved using the type-specific
|
|---|
| 663 | //methods
|
|---|
| 664 |
|
|---|
| 665 | thistype := pInteger(self.fColTypes[I])^;
|
|---|
| 666 |
|
|---|
| 667 | if (thistype = dtInt) or (thistype = dtNumeric) or (thistype = dtBlob) then
|
|---|
| 668 | begin
|
|---|
| 669 | ptr := self.fResults[(self.frow * self.fColCount) + I];
|
|---|
| 670 |
|
|---|
| 671 | if ptr <> nil then
|
|---|
| 672 | begin
|
|---|
| 673 | raise ESqliteException.Create('Use the specific methods for integer, numeric or blob fields');
|
|---|
| 674 | end;
|
|---|
| 675 |
|
|---|
| 676 | end
|
|---|
| 677 | else
|
|---|
| 678 | if pInteger(self.fColTypes[I])^ = dtBool then
|
|---|
| 679 | begin
|
|---|
| 680 | thisboolvalue := self.fResults[(self.frow * self.fColCount) + I];
|
|---|
| 681 | if thisboolvalue <> nil then
|
|---|
| 682 | begin if thisboolvalue^ then
|
|---|
| 683 | begin result := '1' end
|
|---|
| 684 | else
|
|---|
| 685 | begin result := '0' end end;
|
|---|
| 686 | end
|
|---|
| 687 |
|
|---|
| 688 | else
|
|---|
| 689 |
|
|---|
| 690 | begin
|
|---|
| 691 |
|
|---|
| 692 | thisvalue := self.fResults[(self.frow * self.fColCount) + I];
|
|---|
| 693 | if (thisvalue <> nil) then
|
|---|
| 694 | begin Result := thisvalue^ end
|
|---|
| 695 | else
|
|---|
| 696 | begin Result := '' end; //return empty string
|
|---|
| 697 | end;
|
|---|
| 698 |
|
|---|
| 699 | end;
|
|---|
| 700 |
|
|---|
| 701 | function TSqliteTable.FieldAsBlob(FieldName: string): TMemoryStream;
|
|---|
| 702 | var
|
|---|
| 703 | i: Integer;
|
|---|
| 704 | begin
|
|---|
| 705 |
|
|---|
| 706 | if EOF then
|
|---|
| 707 | begin raise ESqliteException.Create('Table is at End of File') end;
|
|---|
| 708 |
|
|---|
| 709 | i:=Self.FieldIndex[FieldName];
|
|---|
| 710 |
|
|---|
| 711 | if (self.fResults[(self.frow * self.fColCount) + I] = nil) then
|
|---|
| 712 | begin result := nil end
|
|---|
| 713 | else
|
|---|
| 714 | if pInteger(self.fColTypes[I])^ = dtBlob then
|
|---|
| 715 | begin result := TMemoryStream(self.fResults[(self.frow * self.fColCount) + I]) end
|
|---|
| 716 | else
|
|---|
| 717 | begin raise ESqliteException.Create('Not a Blob field') end;
|
|---|
| 718 | end;
|
|---|
| 719 |
|
|---|
| 720 | function TSqliteTable.FieldAsBlobText(FieldName: string): string;
|
|---|
| 721 | var
|
|---|
| 722 | MemStream: TMemoryStream;
|
|---|
| 723 | Buffer: PChar;
|
|---|
| 724 | begin
|
|---|
| 725 | result := '';
|
|---|
| 726 |
|
|---|
| 727 | MemStream := self.FieldAsBlob(FieldName);
|
|---|
| 728 |
|
|---|
| 729 | if MemStream <> nil then
|
|---|
| 730 | begin if MemStream.Size > 0 then
|
|---|
| 731 | begin
|
|---|
| 732 | MemStream.position := 0;
|
|---|
| 733 |
|
|---|
| 734 | Buffer := stralloc(MemStream.Size + 1);
|
|---|
| 735 | MemStream.readbuffer(Buffer[0], MemStream.Size);
|
|---|
| 736 | (Buffer + MemStream.Size)^ := chr(0);
|
|---|
| 737 | SetString(Result, Buffer, MemStream.size);
|
|---|
| 738 | strdispose(Buffer);
|
|---|
| 739 | end end;
|
|---|
| 740 |
|
|---|
| 741 | end;
|
|---|
| 742 |
|
|---|
| 743 |
|
|---|
| 744 | function TSqliteTable.FieldAsInteger(FieldName: string): integer;
|
|---|
| 745 | var
|
|---|
| 746 | i: Integer;
|
|---|
| 747 | begin
|
|---|
| 748 |
|
|---|
| 749 | if EOF then
|
|---|
| 750 | begin raise ESqliteException.Create('Table is at End of File') end;
|
|---|
| 751 |
|
|---|
| 752 | i:=Self.FieldIndex[FieldName];
|
|---|
| 753 |
|
|---|
| 754 | if (self.fResults[(self.frow * self.fColCount) + I] = nil) then
|
|---|
| 755 | begin result := 0 end
|
|---|
| 756 | else
|
|---|
| 757 | if pInteger(self.fColTypes[I])^ = dtInt then
|
|---|
| 758 | begin result := pInteger(self.fResults[(self.frow * self.fColCount) + I])^ end
|
|---|
| 759 | else
|
|---|
| 760 | if pInteger(self.fColTypes[I])^ = dtNumeric then
|
|---|
| 761 | begin result := trunc(strtofloat(pString(self.fResults[(self.frow * self.fColCount) + I])^)) end
|
|---|
| 762 | else
|
|---|
| 763 | begin raise ESqliteException.Create('Not an integer or numeric field') end;
|
|---|
| 764 |
|
|---|
| 765 | end;
|
|---|
| 766 |
|
|---|
| 767 | function TSqliteTable.FieldAsDouble(FieldName: string): double;
|
|---|
| 768 | var
|
|---|
| 769 | i: Integer;
|
|---|
| 770 | begin
|
|---|
| 771 |
|
|---|
| 772 | if EOF then
|
|---|
| 773 | begin raise ESqliteException.Create('Table is at End of File') end;
|
|---|
| 774 |
|
|---|
| 775 | i:=Self.FieldIndex[FieldName];
|
|---|
| 776 |
|
|---|
| 777 | if (self.fResults[(self.frow * self.fColCount) + I] = nil) then
|
|---|
| 778 | begin result := 0 end
|
|---|
| 779 | else
|
|---|
| 780 | if pInteger(self.fColTypes[I])^ = dtInt then
|
|---|
| 781 | begin result := pInteger(self.fResults[(self.frow * self.fColCount) + I])^ end
|
|---|
| 782 | else
|
|---|
| 783 | if pInteger(self.fColTypes[I])^ = dtNumeric then
|
|---|
| 784 | begin result := pDouble(self.fResults[(self.frow * self.fColCount) + I])^ end
|
|---|
| 785 | else
|
|---|
| 786 | begin raise ESqliteException.Create('Not an integer or numeric field') end;
|
|---|
| 787 |
|
|---|
| 788 | end;
|
|---|
| 789 |
|
|---|
| 790 | function TSqliteTable.FieldAsBool(FieldName: string): boolean;
|
|---|
| 791 | var
|
|---|
| 792 | i: Integer;
|
|---|
| 793 | begin
|
|---|
| 794 |
|
|---|
| 795 | if EOF then
|
|---|
| 796 | begin raise ESqliteException.Create('Table is at End of File') end;
|
|---|
| 797 |
|
|---|
| 798 | i:=Self.FieldIndex[FieldName];
|
|---|
| 799 |
|
|---|
| 800 | if (self.fResults[(self.frow * self.fColCount) + I] = nil) then
|
|---|
| 801 | begin result := false end
|
|---|
| 802 | else
|
|---|
| 803 | if pInteger(self.fColTypes[I])^ = dtBool then
|
|---|
| 804 | begin result := pBoolean(self.fResults[(self.frow * self.fColCount) + I])^ end
|
|---|
| 805 | else
|
|---|
| 806 | begin raise ESqliteException.Create('Not a boolean field') end;
|
|---|
| 807 | end;
|
|---|
| 808 |
|
|---|
| 809 | function TSqliteTable.FieldAsString(FieldName: string): string;
|
|---|
| 810 | var
|
|---|
| 811 | i: Integer;
|
|---|
| 812 | begin
|
|---|
| 813 |
|
|---|
| 814 | if EOF then
|
|---|
| 815 | begin raise ESqliteException.Create('Table is at End of File') end;
|
|---|
| 816 |
|
|---|
| 817 | i:=Self.FieldIndex[FieldName];
|
|---|
| 818 |
|
|---|
| 819 | if (self.fResults[(self.frow * self.fColCount) + I] = nil) then
|
|---|
| 820 | begin result := '' end
|
|---|
| 821 | else
|
|---|
| 822 | begin result := self.GetFields(I) end;
|
|---|
| 823 |
|
|---|
| 824 | end;
|
|---|
| 825 |
|
|---|
| 826 | function TSqliteTable.FieldIsNull(FieldName: string): boolean;
|
|---|
| 827 | var
|
|---|
| 828 | thisvalue: pointer;
|
|---|
| 829 | i: Integer;
|
|---|
| 830 | begin
|
|---|
| 831 |
|
|---|
| 832 | if EOF then
|
|---|
| 833 | begin raise ESqliteException.Create('Table is at End of File') end;
|
|---|
| 834 |
|
|---|
| 835 | i:=Self.FieldIndex[FieldName];
|
|---|
| 836 |
|
|---|
| 837 | thisvalue := self.fResults[(self.frow * self.fColCount) + I];
|
|---|
| 838 | result := (thisvalue = nil);
|
|---|
| 839 | end;
|
|---|
| 840 |
|
|---|
| 841 | //..............................................................................
|
|---|
| 842 |
|
|---|
| 843 | function TSQLiteTable.Next: boolean;
|
|---|
| 844 | begin
|
|---|
| 845 | result := false;
|
|---|
| 846 | if not EOF then
|
|---|
| 847 | begin
|
|---|
| 848 | Inc(fRow);
|
|---|
| 849 | result := true;
|
|---|
| 850 | end;
|
|---|
| 851 | end;
|
|---|
| 852 |
|
|---|
| 853 | function TSQLiteTable.Previous: boolean;
|
|---|
| 854 | begin
|
|---|
| 855 | result := false;
|
|---|
| 856 | if not BOF then
|
|---|
| 857 | begin
|
|---|
| 858 | Dec(fRow);
|
|---|
| 859 | result := true;
|
|---|
| 860 | end;
|
|---|
| 861 | end;
|
|---|
| 862 |
|
|---|
| 863 | function TSQLiteTable.MoveFirst: boolean;
|
|---|
| 864 | begin
|
|---|
| 865 | result := false;
|
|---|
| 866 | if self.fRowCount > 0 then
|
|---|
| 867 | begin
|
|---|
| 868 | fRow := 0;
|
|---|
| 869 | result := true;
|
|---|
| 870 | end;
|
|---|
| 871 | end;
|
|---|
| 872 |
|
|---|
| 873 | function TSQLiteTable.MoveLast: boolean;
|
|---|
| 874 | begin
|
|---|
| 875 | result := false;
|
|---|
| 876 | if self.fRowCount > 0 then
|
|---|
| 877 | begin
|
|---|
| 878 | fRow := fRowCount - 1;
|
|---|
| 879 | result := true;
|
|---|
| 880 | end;
|
|---|
| 881 | end;
|
|---|
| 882 |
|
|---|
| 883 |
|
|---|
| 884 | end.
|
|---|
| 885 |
|
|---|
