delphi Sqlite

FFileStream:TFileStream; FData:array of Char; FSQLR:TSQLRequest; ASQL:AnsiString; begin FFileStream:=TFileStream.Create(‘test.xml‘,fmOpenReadWrite); ASQL:=‘INSERT INTO TEST(FileName,Data) VALUES(‘+QuotedStr(‘test.xml‘)+‘,?)‘; try SQLite数据库对象.Execute(‘DELETE FROM TEST‘); // SetLength(FData,FFileStream.Size); FFileStream.Position:=0; FFileStream.Read(PChar(FData)^,Length(FData)); FSQLR.Prepare(SQLite数据库对象.DB,ASQL); FSQLR.Bind(1,PChar(FData),Length(FData)); FSQLR.Execute; finally FreeAndNil(FFileStream); end; View Code

其中问号是参数,SQLite数据库对象类型为TSQLDataBase
读取数据并写入到文件:

var
FBlobField:TSQLBlobStream;
FFileStream:TMemoryStream;
FData:array of Char;
begin
try
FFileStream:=TMemoryStream.Create;
FBlobField:=SQLite数据库对象.Blob(‘‘,‘TEST‘,‘Data‘,1,True);
try
FBlobField.Position:=0;
SetLength(FData,FBlobField.Size);
FBlobField.ReadBuffer(PChar(FData)^,FBlobField.Size);
FFileStream.Write(PChar(FData)^,FBlobField.Size);
FFileStream.SaveToFile("test.xml");
finally
FreeAndNil(FBlobField);
FreeAndNil(FFileStream);
end;
except
Result:=‘‘;
end;

View Code

转载请注明来源,谢谢,

 

Delphi連接SQLite3 SQLite ODBC Driver

最近在嘗試在Delphi中使用SQLite,昨晚終於找到一個連接SQLite的方法:安裝 SQLite ODBC Driver 在 Delphi中通過ADO組件（TADOQuery， TADOConnection）直接訪問。
步驟：
1. 下載 SQLite ODBC Driver；
2. 安裝 SQLite ODBC Driver；
3. 在Delphi工程中添加 TADOQuery， TADOConnection 組件；
4. 設置 TADOConnection 的ConnectionString;
設置步驟:
1)單擊TADOConnection組件 ConectionString變的按鈕, 選擇 "Use Connection String" -> "Build"
2)彈出的菜單中, 程序選擇: "Microsoft OLE DB Provider for ODBC Drivers"
3)指定的數據源: 選"使用數據源名稱"->"SQLite3 Datasource"
4)測試連接是否成功。

(over)

// 創建表 test
procedure TForm1.Bt_createClick(Sender: TObject);
begin
try
if cnnSqlite.Connected=false then cnnSqlite.open;
if sQry.Active then sQry.Close;
sQry.SQL.Clear;
sQry.SQL.Add(‘create TABLE test (testid INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE, title string unique, text text)‘);
sQry.ExecSQL;
finally
cnnSqlite.Close;
end;
showMessage(‘over‘);
end;

View Code

SQLite加密 wxSqlite3

一直在网上搜wxSqlite3的文档，但是总找不到能真正解决问题的，就是一个简单的编译wxSqlite3自带的示例也出了老多问题，后来却发现，其实wxSqlite3的readme中已经有了详细的方法，哦，就在眼皮子底下！为了避免再一次的遗忘，就在这里暂作一个记录吧。

截至记录时间，wxSqlite3的版本号为2.1.1，Sqlite3的版本为3.7.6.2，这两个都可以直接在网上下载，
wxSqlite3的站点http://wxcode.sourceforge.net/components/wxsqlite3；
http://sourceforge.net/projects/wxcode/files/Components/wxSQLite3/
http://sourceforge.net/projects/wxsqlite/?source=directory
Sqlite3的站点http://www.sqlite.org/；

其中wxSqlite3自带了已编译的Sqlite3.7.6.1的DLL，当然，我的目的是自己编译静态的Lib，所以只能自己下来重新编译了。当然，我要的Lib是要带加密功能的，呵呵。

 

wxSQLite3 is a C++ wrapper around the public domain SQLite 3.x database and is specifically designed for use in programs based on the wxWidgets library.

wxSQLite3 does not try to hide the underlying database, in contrary almost all special features of the current SQLite version 3.7.10 are supported, like for example the creation of user defined scalar or aggregate functions. Since SQLite stores strings in UTF-8 encoding, the wxSQLite3 methods provide automatic conversion between wxStrings and UTF-8 strings. This works best for the Unicode builds of wxWidgets. In ANSI builds the current locale conversion object (wxConvCurrent) is used for conversion to/from UTF-8. Special care has to be taken if external administration tools are used to modify the database contents, since not all of these tools operate in Unicode resp. UTF-8 mode.

Since version 1.7.0 optional support for key based database encryption (128 bit AES) is also included. Starting with version 1.9.6 of wxSQLite3 the encryption extension is compatible with the SQLite amalgamation source and includes the extension functions module. Support for 256 bit AES encryption has been added in version 1.9.8.

从wxSqlite3 1.9.6开始，它的加密扩展就已经从C++转为纯C语言实现，因此现在可以直接编译SQLite
amalgamation source distribution实现了。只需要编译文件sqlite3secure.c即可，其已经include了所有需要的源文件。当然C++版本的文件也在codec目录（\sqlite3\secure\src\codec-c）中，但作者强烈的不推荐用它。

唠了这么多，现在正式开始。
1、下载sqlite-amalgamation-XXXXXX.zip，它已经包含了所有的源文件，也不需要另外的辅助工具了，解压到某一目录，如Sqlite3。
2、如果你下的sqlite-amalgamation-XXXXXX.zip没有makefile，那就自己做一个project吧，在VS2008里新建一个空工程，只加入sqlite3secure.c文件到工程即可，修改其中的#include "sqlite.c"的sqlite.c文件路径为上一步解压的文件的路径。
3、在配置属性中设置配置类型为静态库(.Lib)，添加预处理
SQLITE_HAS_CODEC=1
CODEC_TYPE=CODEC_TYPE_AES128
SQLITE_CORE
THREADSAFE
SQLITE_SECURE_DELETE
SQLITE_SOUNDEX
SQLITE_ENABLE_COLUMN_METADATA
4、编译生成Lib文件。
5、将生成的Lib文件取代wxsqlite3-2.1.1\sqlite3\lib中的sqlite3.lib文件，然后编译wxSqlite3为静态库，别忘了设置USE_DYNAMIC_SQLITE3_LOAD=0。

》》》》》》》》》》》》》》》》》》》》

Delphi使用wxsqlite加密Sqlite数据库

提起桌面数据库，Sqlite谁都知道，但对于它的加密一直困扰着很多人，而delphi的加密使用范例更少。在网上混了两天，查找了一些Sqlite加密的相关资料，也知道了wxsqlite这个东西，它是一个sqlite3.dll的嵌入扩展，重要的是里面加入了对Sqlite的AES 128bit-256bit加密扩展，而且用的是Sqlite预留的函数接口，这意味着什么呢？UniDAC从3.5.14版本以后加入了对encrypted sqlite的支持，这功能支持 SEE/CEROD这样使用预留接口开发的加密功能的第三方扩展，也就是说wxsqlite同样能被UniDAC所支持，这意味着你就不用再去改UniDAC的源代码了，wxsqlite可以在我这下载http://download.csdn.net/source/3215472

下面说说具体的使用方法，下载后你可以在sqlite3/secure目录下找到AES128和AES256两个文件夹，分别包含两种加密类型扩展的库文件，找到sqlite3.dll，然后放到你要调用的路径，然后下面是我随便Copy修改后的delphi的源码：

unit main; 

interface 


uses 
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms, 
Dialogs, DB, MemDS, DBAccess; 

type 
TSQLiteDB = Pointer; 
Tsqlite3=Pointer; 
TForm4 = class(TForm) 
procedure FormCreate(Sender: TObject); 
private 
{ Private declarations } 
public 
{ Public declarations } 
end; 
const 
{$IF Defined(MSWINDOWS)} 
SQLiteDLL = ‘sqlite3.dll‘; 
{$ELSEIF Defined(DARWIN)} 
SQLiteDLL = ‘libsqlite3.dylib‘; 
{$linklib libsqlite3} 
{$ELSEIF Defined(UNIX)} 
SQLiteDLL = ‘sqlite3.so‘; 
{$IFEND} 
var 
Form4: TForm4; 
db: TSQLiteDB; 
function SQLite3_Open(filename: PAnsiChar; var db: TSQLiteDB): integer; cdecl; external SQLiteDLL name ‘sqlite3_open‘; 
function sqlite3_key ( 
pDb: Tsqlite3; // Database handle 
pKey: PAnsiChar; // Database PassWord (UTF-8) 
nKey: Integer // Database sizeofpassword 
): integer; cdecl; external SQLiteDLL name ‘sqlite3_key‘; 
function sqlite3_rekey ( 
pDb: Tsqlite3; // Database handle 
pKey: PAnsiChar; // Database PassWord (UTF-8) 
nKey: Integer // Database sizeofpassword 
): integer; cdecl; external SQLiteDLL name ‘sqlite3_rekey‘; 
implementation 

{$R *.dfm} 

procedure TForm4.FormCreate(Sender: TObject); 
begin 
SQLite3_Open(‘ak.db‘,db); //打开数据库 
SQLite3_key(db,‘cba‘,3); //设置密码 
SQLite3_rekey(db,‘abc‘,3); //更改密码 
end; 

end.

View Code

不过我下载的最新版本的wxsqlite里已经编译好的sqlite3.dll中，sqlite3_key函数好像是空的，不起作用，后来查了查，是自己在使用UniDAC时直接链接了，所以我直接试着调用了sqlite3_rekey实现了对数据库的加密，加密后的数据库再用记事本打开时已经是乱码了，然后用数据库工具查看时会提示“数据库已经加密”。要更改密码的话首先得把所有多余的链接断掉，然后先Open数据库，再用sqlite3_key设置用来操作数据库的原密码，这样获得操作权之后就能用sqlite3_rekey设置新的密码了。

接下来说说UniDAC里怎么链接加密后的数据库。上面说了，UniDAC3.5.14以后的版本加入了Sqlite的encryption功能，所以在添加链接的时候你在specificOption项里可以将option项中的encryptionkey参数设置为你的数据库密码，这样链接数据库后你就可以随意操作了。

有时间再把这些东西整整，做个直接加密的工具，以后就能非常方便的加密自己的数据库了！

 sqlite3

unit SQLite3;

{
  Simplified interface for SQLite.
  Updated for Sqlite 3 by Tim Anderson (tim@itwriting.com)
  Update for use with Dbx4Pg by Thiago Borges de Oliveira (thborges at gmail.com)
  Note: NOT COMPLETE for version 3, just minimal functionality
  Adapted from file created by Pablo Pissanetzky (pablo@myhtpc.net)
  which was based on SQLite.pas by Ben Hochstrasser (bhoc@surfeu.ch)
}

interface

const

  // SQLiteDLL = ‘sqlite3.dll‘;

  // Return values for sqlite3_exec() and sqlite3_step()

  __turboFloat: integer = 0;

  SQLITE_OK = 0; // Successful result
  SQLITE_ERROR = 1; // SQL error or missing database
  SQLITE_INTERNAL = 2; // An internal logic error in SQLite
  SQLITE_PERM = 3; // Access permission denied
  SQLITE_ABORT = 4; // Callback routine requested an abort
  SQLITE_BUSY = 5; // The database file is locked
  SQLITE_LOCKED = 6; // A table in the database is locked
  SQLITE_NOMEM = 7; // A malloc() failed
  SQLITE_READONLY = 8; // Attempt to write a readonly database
  SQLITE_INTERRUPT = 9; // Operation terminated by sqlite3_interrupt()
  SQLITE_IOERR = 10; // Some kind of disk I/O error occurred
  SQLITE_CORRUPT = 11; // The database disk image is malformed
  SQLITE_NOTFOUND = 12; // (Internal Only) Table or record not found
  SQLITE_FULL = 13; // Insertion failed because database is full
  SQLITE_CANTOPEN = 14; // Unable to open the database file
  SQLITE_PROTOCOL = 15; // Database lock protocol error
  SQLITE_EMPTY = 16; // Database is empty
  SQLITE_SCHEMA = 17; // The database schema changed
  SQLITE_TOOBIG = 18; // Too much data for one row of a table
  SQLITE_CONSTRAINT = 19; // Abort due to contraint violation
  SQLITE_MISMATCH = 20; // Data type mismatch
  SQLITE_MISUSE = 21; // Library used incorrectly
  SQLITE_NOLFS = 22; // Uses OS features not supported on host
  SQLITE_AUTH = 23; // Authorization denied
  SQLITE_FORMAT = 24; // Auxiliary database format error
  SQLITE_RANGE = 25; // 2nd parameter to sqlite3_bind out of range
  SQLITE_NOTADB = 26; // File opened that is not a database file
  SQLITE_ROW = 100; // sqlite3_step() has another row ready
  SQLITE_DONE = 101; // sqlite3_step() has finished executing

  SQLITE_INTEGER = 1;
  SQLITE_FLOAT = 2;
  SQLITE_TEXT = 3;
  SQLITE_BLOB = 4;
  SQLITE_NULL = 5;

  SQLITE_UTF8 = 1;
  SQLITE_UTF16 = 2;
  SQLITE_UTF16BE = 3;
  SQLITE_UTF16LE = 4;
  SQLITE_ANY = 5;

  SQLITE_TRANSIENT = pointer(-1);
  SQLITE_STATIC = pointer(0);

type
  TSQLiteDB = pointer;
  TSQLiteResult = ^PAnsiChar;
  TSQLiteStmt = pointer;

  // function prototype for define own collate
  TCollateXCompare = function(Userdta: pointer; Buf1Len: integer;
    Buf1: pointer; Buf2Len: integer; Buf2: pointer): integer; cdecl;

function _SQLite3_Open(dbname: PAnsiChar; var db: TSQLiteDB): integer; cdecl;
external;
function _SQLite3_Close(db: TSQLiteDB): integer; cdecl; external;
function _SQLite3_Exec(db: TSQLiteDB; SQLStatement: PAnsiChar;
  CallbackPtr: pointer; Sender: TObject; var ErrMsg: PAnsiChar): integer;
  cdecl; external;
function _SQLite3_Version(): PAnsiChar; cdecl; external;
function _SQLite3_ErrMsg(db: TSQLiteDB): PAnsiChar; cdecl; external;
function _SQLite3_ErrCode(db: TSQLiteDB): integer; cdecl; external;
procedure _SQlite3_Free(P: PAnsiChar); cdecl; external;
function _SQLite3_Get_Table(db: TSQLiteDB; SQLStatement: PAnsiChar;
  var ResultPtr: TSQLiteResult; var RowCount: Cardinal;
  var ColCount: Cardinal; var ErrMsg: PAnsiChar): integer; cdecl; external;
procedure _SQLite3_Free_Table(Table: TSQLiteResult); cdecl; external;
function _SQLite3_Complete(P: PAnsiChar): boolean; cdecl; external;
function _SQLite3_Last_Insert_RowID(db: TSQLiteDB): Int64; cdecl; external;
procedure _SQLite3_Interrupt(db: TSQLiteDB); cdecl; external;
procedure _SQLite3_Busy_Handler(db: TSQLiteDB; CallbackPtr: pointer;
  Sender: TObject); cdecl; external;
procedure _SQLite3_Busy_Timeout(db: TSQLiteDB; TimeOut: integer); cdecl;
external;
function _SQLite3_Changes(db: TSQLiteDB): integer; cdecl; external;
function _SQLite3_Total_Changes(db: TSQLiteDB): integer; cdecl; external;
function _SQLite3_Prepare(db: TSQLiteDB; SQLStatement: PAnsiChar;
  nBytes: integer; var hStmt: TSQLiteStmt; var pzTail: PAnsiChar): integer;
  cdecl; external;
function _SQLite3_Prepare_v2(db: TSQLiteDB; SQLStatement: PAnsiChar;
  nBytes: integer; var hStmt: TSQLiteStmt; var pzTail: PAnsiChar): integer;
  cdecl; external;
function _SQLite3_Column_Count(hStmt: TSQLiteStmt): integer; cdecl; external;
function _Sqlite3_Column_Name(hStmt: TSQLiteStmt; ColNum: integer): PAnsiChar;
  cdecl; external;
function _Sqlite3_Column_DeclType(hStmt: TSQLiteStmt;
  ColNum: integer): PAnsiChar; cdecl; external;
function _Sqlite3_Step(hStmt: TSQLiteStmt): integer; cdecl; external;
function _SQLite3_Data_Count(hStmt: TSQLiteStmt): integer; cdecl; external;

// function _Sqlite3_Column_TableName(hStmt: TSqliteStmt; ColNum: integer): PAnsiChar; cdecl; external;

function _Sqlite3_Column_Blob(hStmt: TSQLiteStmt; ColNum: integer): pointer;
  cdecl; external;
function _Sqlite3_Column_Bytes(hStmt: TSQLiteStmt; ColNum: integer): integer;
  cdecl; external;
function _Sqlite3_Column_Double(hStmt: TSQLiteStmt; ColNum: integer): double;
  cdecl; external;
function _Sqlite3_Column_Int(hStmt: TSQLiteStmt; ColNum: integer): integer;
  cdecl; external;
function _Sqlite3_Column_Text(hStmt: TSQLiteStmt; ColNum: integer): PAnsiChar;
  cdecl; external;
function _Sqlite3_Column_Type(hStmt: TSQLiteStmt; ColNum: integer): integer;
  cdecl; external;
function _Sqlite3_Column_Int64(hStmt: TSQLiteStmt; ColNum: integer): Int64;
  cdecl; external;
function _SQLite3_Finalize(hStmt: TSQLiteStmt): integer; cdecl; external;
function _SQLite3_Reset(hStmt: TSQLiteStmt): integer; cdecl; external;
function _SQLite3_Clear_Bindings(hStmt: TSQLiteStmt): integer; cdecl; external;

//
// In the SQL strings input to sqlite3_prepare() and sqlite3_prepare16(),
// one or more literals can be replace by a wildcard "?" or ":N:" where
// N is an integer.  These value of these wildcard literals can be set
// using the routines listed below.
//
// In every case, the first parameter is a pointer to the sqlite3_stmt
// structure returned from sqlite3_prepare().  The second parameter is the
// index of the wildcard.  The first "?" has an index of 1.  ":N:" wildcards
// use the index N.
//
// The fifth parameter to sqlite3_bind_blob(), sqlite3_bind_text(), and
// sqlite3_bind_text16() is a destructor used to dispose of the BLOB or
// text after SQLite has finished with it.  If the fifth argument is the
// special value SQLITE_STATIC, then the library assumes that the information
// is in static, unmanaged space and does not need to be freed.  If the
// fifth argument has the value SQLITE_TRANSIENT, then SQLite makes its
// own private copy of the data.
//
// The sqlite3_bind_* routine must be called before sqlite3_step() after
// an sqlite3_prepare() or sqlite3_reset().  Unbound wildcards are interpreted
// as NULL.
//

function _SQLite3_Bind_Blob(hStmt: TSQLiteStmt; ParamNum: integer;
  ptrData: pointer; numBytes: integer; ptrDestructor: pointer): integer;
  cdecl; external;
function _SQLite3_Bind_Double(hStmt: TSQLiteStmt; ParamNum: integer;
  Data: double): integer; cdecl; external;
function _SQLite3_Bind_Int(hStmt: TSQLiteStmt; ParamNum: integer;
  intData: integer): integer; cdecl; external;
function _SQLite3_Bind_int64(hStmt: TSQLiteStmt; ParamNum: integer;
  Data: Int64): integer; cdecl; external;
function _SQLite3_Bind_null(hStmt: TSQLiteStmt; ParamNum: integer): integer;
  cdecl; external;
function _SQLite3_Bind_text(hStmt: TSQLiteStmt; ParamNum: integer;
  Data: PAnsiChar; numBytes: integer; ptrDestructor: pointer): integer; cdecl;
external;

function _SQLite3_Bind_Parameter_Index(hStmt: TSQLiteStmt;
  zName: PAnsiChar): integer; cdecl; external;

function _sqlite3_enable_shared_cache(value: integer): integer; cdecl; external;

// user collate definiton
function _sqlite3_create_collation(db: TSQLiteDB; Name: PAnsiChar;
  eTextRep: integer; UserData: pointer; xCompare: TCollateXCompare): integer;
  cdecl; external;

function SQLiteFieldType(SQLiteFieldTypeCode: integer): String;
function SQLiteErrorStr(SQLiteErrorCode: integer): String;

function _atol(const s: PAnsiChar): integer; cdecl;
external ‘msvcrt.dll‘ name ‘atol‘;
function __ftol(f: double): integer; cdecl; external ‘msvcrt.dll‘ name ‘_ftol‘;
function __ftoul(f: double): longword; cdecl;
external ‘msvcrt.dll‘ name ‘_ftol‘;
function _malloc(s: longword): pointer; cdecl;
external ‘msvcrt.dll‘ name ‘malloc‘;
procedure _free(P: pointer); cdecl; external ‘msvcrt.dll‘ name ‘free‘;
function _realloc(P: pointer; s: longword): pointer; cdecl;
external ‘msvcrt.dll‘ name ‘realloc‘;
function _localtime(const __timer: pointer): pointer; cdecl;
external ‘msvcrt.dll‘ name ‘localtime‘;

implementation

uses
  Windows, SysUtils;
{$LINK .\obj\sqlite3.obj}
{$LINK .\obj\_ll.obj}
{$LINK .\obj\strncmp.obj}
{$LINK .\obj\memset.obj}
{$LINK .\obj\memcpy.obj}
{$LINK .\obj\memmove.obj}
{$LINK .\obj\memcmp.obj}


function SQLiteFieldType(SQLiteFieldTypeCode: integer): String;
begin
  case SQLiteFieldTypeCode of
    SQLITE_INTEGER:
      Result := ‘Integer‘;
    SQLITE_FLOAT:
      Result := ‘Float‘;
    SQLITE_TEXT:
      Result := ‘Text‘;
    SQLITE_BLOB:
      Result := ‘Blob‘;
    SQLITE_NULL:
      Result := ‘Null‘;
  else
    Result := ‘Unknown SQLite Field Type Code "‘ + IntToStr
      (SQLiteFieldTypeCode) + ‘"‘;
  end;
end;

function SQLiteErrorStr(SQLiteErrorCode: integer): String;
begin
  case SQLiteErrorCode of
    SQLITE_OK:
      Result := ‘Successful result‘;
    SQLITE_ERROR:
      Result := ‘SQL error or missing database‘;
    SQLITE_INTERNAL:
      Result := ‘An internal logic error in SQLite‘;
    SQLITE_PERM:
      Result := ‘Access permission denied‘;
    SQLITE_ABORT:
      Result := ‘Callback routine requested an abort‘;
    SQLITE_BUSY:
      Result := ‘The database file is locked‘;
    SQLITE_LOCKED:
      Result := ‘A table in the database is locked‘;
    SQLITE_NOMEM:
      Result := ‘A malloc() failed‘;
    SQLITE_READONLY:
      Result := ‘Attempt to write a readonly database‘;
    SQLITE_INTERRUPT:
      Result := ‘Operation terminated by sqlite3_interrupt()‘;
    SQLITE_IOERR:
      Result := ‘Some kind of disk I/O error occurred‘;
    SQLITE_CORRUPT:
      Result := ‘The database disk image is malformed‘;
    SQLITE_NOTFOUND:
      Result := ‘(Internal Only) Table or record not found‘;
    SQLITE_FULL:
      Result := ‘Insertion failed because database is full‘;
    SQLITE_CANTOPEN:
      Result := ‘Unable to open the database file‘;
    SQLITE_PROTOCOL:
      Result := ‘Database lock protocol error‘;
    SQLITE_EMPTY:
      Result := ‘Database is empty‘;
    SQLITE_SCHEMA:
      Result := ‘The database schema changed‘;
    SQLITE_TOOBIG:
      Result := ‘Too much data for one row of a table‘;
    SQLITE_CONSTRAINT:
      Result := ‘Abort due to contraint violation‘;
    SQLITE_MISMATCH:
      Result := ‘Data type mismatch‘;
    SQLITE_MISUSE:
      Result := ‘Library used incorrectly‘;
    SQLITE_NOLFS:
      Result := ‘Uses OS features not supported on host‘;
    SQLITE_AUTH:
      Result := ‘Authorization denied‘;
    SQLITE_FORMAT:
      Result := ‘Auxiliary database format error‘;
    SQLITE_RANGE:
      Result := ‘2nd parameter to sqlite3_bind out of range‘;
    SQLITE_NOTADB:
      Result := ‘File opened that is not a database file‘;
    SQLITE_ROW:
      Result := ‘sqlite3_step() has another row ready‘;
    SQLITE_DONE:
      Result := ‘sqlite3_step() has finished executing‘;
  else
    Result := ‘Unknown SQLite Error Code "‘ + IntToStr(SQLiteErrorCode) + ‘"‘;
  end;
end;

function ColValueToStr(value: PAnsiChar): String;
begin
  if (value = nil) then
    Result := ‘NULL‘
  else
    Result := String(PAnsiChar(value));
end;

end.

View Code

delphi 与 sqlite 参考

file:0 前言

本文的目的在于采用流水账方式来记录学习delphi访问嵌入式数据库sqlite中的一些点滴。欢迎各位同好共同学习和批评指正。

file:1 准备工作part1

delphi版本:delphi2007 for win32 update3。任意安装版本即可。
sqlite dll版本:3.5.3。目前最新版本的sqlite引擎。[ http://www.sqlite.org/ ]
sqlite for delphi:simple sqlite 3.0 for delphi。目前的最新版本的发布日期为27 August 2007，支持sqlite dll版本3.4.2。经简单测试，3.5.3也是可以的。自带demo。[ http://www.itwriting.com/sqlitesimple.php ]

file:2 准备工作part2

新建一个窗体应用程序工程，并设定保存的文件夹。将simple sqlite 3.0 for delphi 中的 sqlite3.pas，sqlite3table.pas，sqlite3udf.pas拷贝至工程所在的文件夹。并在工程中添加这三个文件。拷贝 sqlite.dll到编译生成exe文件的文件夹。这个要看个人的设定。

file:3 初步测试

引用SQLiteTable3.pas单元。
在窗体上创建一个叫btnVersion的按钮(Tbutton)。在btnVersion的click事件中写入下面的代码。
procedure TfrmAbout.btnVersionClick(Sender: TObject);
var
SqliteDB:TSQLiteDatabase;
begin
SqliteDB:=TSQLiteDatabase.Create(‘‘);
showmessage(‘Sqlite dll version:‘+SqliteDb.version);
SqliteDB.Free;
end;
编译运行，成功的话，将会显示当前的sqlite dll的版本号，我的是3.5.3。

file:4 简单工作原理描述

在simple sqlite3.0 for delphi的几个文件中，主要用到两个文件。分别是sqlite3.pas，sqlite3table.pas。
sqlite3.pas实现sqlite.dll的接口外部定义。
sqlite3table.pas进行简单的访问函数封装。
在delphi中，通过sqlite3table.pas来实现对sqlite数据库的各种访问。
关于sqlite3udf.pas，根据作者的描述，主要用与创建用户自定义函数，具体功能未测试。

file:5 读取数据

假设，我们有一个叫做database.db的sqlite数据库文件，在编译生成的exe文件所在的目录。里面有一个叫做countries的表格。
表格创建语句如下。
CREATE TABLE "Countries" (
Name VARCHAR NOT NULL PRIMARY KEY UNIQUE,
Capital VARCHAR NOT NULL,
Area INTEGER NOT NULL,
Pop INTEGER NOT NULL,
PCI INTEGER NOT NULL
);
我们该如何访问其中的第一条数据呢。
var
SqliteDB:TSQLiteDatabase;
SqliteTB:TSQLiteTable;
begin
SqliteDB:=TSQLiteDatabase.Create(‘database.db‘);
SqliteTB:=SqliteDB.GetTable(‘Select * from countries‘);
显示控件1.text:=SqliteTB.FieldAsString(SqliteTB.FieldIndex[‘Name‘]);
显示控件2.text:=SqliteTB.FieldAsString(SqliteTB.FieldIndex[‘Capital‘]);
显示控件3.text:=SqliteTB.FieldAsString(SqliteTB.FieldIndex[‘Area‘]);
显示控件4.text:=SqliteTB.FieldAsString(SqliteTB.FieldIndex[‘Pop‘]);
显示控件5.text:=SqliteTB.FieldAsString(SqliteTB.FieldIndex[‘PCI‘]);
SqliteTB.free;
SqliteDB.free;
end;
TSQLiteTable类有两个方法，分别是Next和Previous，是用来向后和向前移动数据游标的。配合这两个方法，我们可以读取表格任意数据 内容。例如select * from countries where area >8000000的数据。

file:6 写入数据

我们可以读取数据，就可以写入数据。如何来做到呢？还是以Countries表为例。
var
SqliteDB:TSQLiteDatabase;
begin
SqliteDB:=TSQLiteDatabase.Create(‘database.db‘);
SqliteDB.ExecSQL(‘Insert Into Countries (Name,Capital,Area,Pop,PCI) values ("China","Beijing",9600000,1500000000,6000)‘);
SqliteDB.Free;
end;
同样，数据的update方法也可以按照这种方法来实现。在这里可以看到,字符类数据可以用双引号来标记起来,而不是通常sqlserver的单引号标记。

file:7 字符编码

访问过MySql数据库数据的朋友应该记得噩梦般的中文数据存取经历。数据库里用了一种编码，程序又是另一种编码，导致中文数据变成乱码。
sqlite中，数据库内用的是UTF-8存取，DELPHI取出的数据是ASCII编码。也就是说，我们需要在存取的同时做编码转换。
有这样两个方法，utf8decode()，utf8encode()。当从数据库中读取数据时，我们用utf8decode()；当向数据库中写入数据时，我们用utf8encode()；
例如:
显示控件.text:=utf8decode(sltb.FieldAsString(sltb.FieldIndex[‘Name‘]));
SqliteDB.ExecSQL(utf8encode(‘Insert Into Countries (Name,Capital,Area,Pop,PCI) values ("中国","北京",9600000,1500000000,6000)‘));
在有更好的方法之前，我们只能这么用……

file8: Blob Blob Blob

某些时候，我们会需要向数据库中存储和读取图片，视频，音频等信息的数据，例如：前女友们的性感照片。sqlite中有一种数据类型叫做Blob,可以满足我们的要求。如何访问和读取呢？
下面以在database.db数据库中PhotoLib表格中存取一个JPEG图片为例：
CREATE TABLE "PhotoLib" (Id Integer NOT NULL PRIMARY KEY UNIQUE,
Photo BLOB);
写入：
var
SqliteDB: TSQLiteDatabase;
FS: TFileStream;
begin
SqliteDB:=TSQLiteDatabase.Create(‘database.db‘);
FS := TFileStream.Create(‘test.jpeg‘,fmOpenRead);
SqliteDB.UpdateBlob(‘UPDATE PhotoLib set Photo = ? WHERE ID = 1‘,FS);
FS.free;
SqliteDB.Free;
end;
读取至TImage控件显示：
var
MS: TMemoryStream;
PIC: TJPegImage;
SqliteDB: TSQLiteDatabase;
SqliteTB: TSQLIteTable;
begin
SqliteDB:=TSQLiteDatabase.Create(‘database.db‘);
SqliteTB:=SqliteDB.GetTable(‘Select * From PhotoLib Where Id=1‘);
MS := SqliteTB.FieldAsBlob(SqliteTB.FieldIndex[‘photo‘]);
if (MS = nil) then
begin
showmessage(‘该条记录没有前女友照片数据.‘);
exit;
end;
MS.Position := 0;
PIC := TJPEGImage.Create;
PIC.LoadFromStream(ms);
self.Image2.Picture.Graphic := PIC;
MS.free;
PIC.Free;
end;

delphi与sqlite3

delphi与sqlite
file:0 前言
本文的目的在于采用流水账方式来记录学习delphi访问嵌入式数据库sqlite中的一些点滴。欢迎各位同好共同学习和批评指正。

file:1 准备工作part1

delphi版本:delphi2007 for win32 update3。任意安装版本即可。
sqlite dll版本:3.5.3。目前最新版本的sqlite引擎。[ http://www.sqlite.org/ ]
sqlite for delphi:simple sqlite 3.0 for delphi。目前的最新版本的发布日期为Update 16 October 2008，支持sqlite dll版本3.4.2。经简单测试，3.5.3也是可以的。自带demo。 http://www.itwriting.com/blog/articles/a-simple-delphi-wrapper-for-sqlite-3/comment-page-2

file:2 准备工作part2

新建一个窗体应用程序工程，并设定保存的文件夹。将simple sqlite 3.0 for delphi 中的 sqlite3.pas，sqlite3table.pas，sqlite3udf.pas拷贝至工程所在的文件夹。并在工程中添加这三个文件。拷贝 sqlite.dll到编译生成exe文件的文件夹。这个要看个人的设定。

file:3 初步测试

引用SQLiteTable3.pas单元。
在窗体上创建一个叫btnVersion的按钮(Tbutton)。在btnVersion的click事件中写入下面的代码。
procedure TfrmAbout.btnVersionClick(Sender: TObject);
var
SqliteDB:TSQLiteDatabase;
begin
SqliteDB:=TSQLiteDatabase.Create(‘‘);
showmessage(‘Sqlite dll version:‘+SqliteDb.version);
SqliteDB.Free;
end;
编译运行，成功的话，将会显示当前的sqlite dll的版本号，我的是3.5.9。

file:4 简单工作原理描述

在simple sqlite3.0 for delphi的几个文件中，主要用到两个文件。分别是sqlite3.pas，sqlite3table.pas。
sqlite3.pas实现sqlite.dll的接口外部定义。
sqlite3table.pas进行简单的访问函数封装。
在delphi中，通过sqlite3table.pas来实现对sqlite数据库的各种访问。