Unit main;

Interface

Uses
  Windows, SysUtils, Classes, Controls, Forms,
  StdCtrls, DB, ADODB;

Type
  TForm1 = Class(TForm)
    Conn: TADOConnection;
    Btn_Conn: TButton;
    Memo1: TMemo;
    ADOQuery1: TADOQuery;
    Btn_insert: TButton;
    Btn_Query: TButton;
    Btn_Delete: TButton;
    Btn_ChangePass: TButton;
    CBWork: TComboBox;
    Procedure Btn_ConnClick(Sender: TObject);
    Procedure Btn_insertClick(Sender: TObject);
    Procedure Btn_QueryClick(Sender: TObject);
    Procedure Btn_DeleteClick(Sender: TObject);
    Procedure Btn_ChangePassClick(Sender: TObject);
    Procedure FormClose(Sender: TObject; Var Action: TCloseAction);
  Private
    { Private declarations }
  Public
    { Public declarations }
  End;

Var
  Form1: TForm1;

Implementation

{$R *.dfm}
Const
  SQLiteDLL = 'sqlite3.dll';

Type
  TSQLiteDB = Pointer;
  TSQLiteResult = ^PChar;
  TSQLiteStmt = Pointer;

Function SqLite3_Key(DB: TSQLiteDB; Key: PChar; KeyLen: Integer): Integer; Cdecl; External SQLiteDLL Name 'sqlite3_key';
Function SqLite3_Rekey(DB: TSQLiteDB; Key: PChar; KeyLen: Integer): Integer; Cdecl; External SQLiteDLL Name 'sqlite3_rekey';
Function SQLite3_Open(dbname: PChar; Var DB: TSQLiteDB): Integer; Cdecl; External SQLiteDLL Name 'sqlite3_open';
Function SQLite3_Close(DB: TSQLiteDB): Integer; Cdecl; External SQLiteDLL Name 'sqlite3_close';
Function SQLite3_Exec(DB: TSQLiteDB; SQLStatement: PChar; CallbackPtr: Pointer; Sender: TObject; Var ErrMsg: PChar): Integer; Cdecl; External SQLiteDLL Name 'sqlite3_exec';
Function SQLite3_GetTable(DB: TSQLiteDB; SQLStatement: PChar; Var ResultPtr: TSQLiteResult; Var RowCount: Cardinal; Var ColCount: Cardinal; Var ErrMsg: PChar): Integer; Cdecl; External SQLiteDLL Name 'sqlite3_get_table';
Procedure SQLite3_FreeTable(Table: TSQLiteResult); Cdecl; External SQLiteDLL Name 'sqlite3_free_table';

Var
  DB: TSQLiteDB;
  ErrMsg: PChar;

Function GetTableNames(List: TStrings; SystemTables: boolean = false): boolean;
Var
  ResultPtr: TSQLiteResult;
  ResultStr: ^Pointer;
  RowCount: Cardinal;
  ColCount: Cardinal;
  ErrMsg: PAnsiChar;
  i, iResult: Integer;
Begin
  Result := false;
  If Form1.Btn_Conn.Tag = 0 Then Exit;
  iResult := SQLite3_GetTable(DB, PAnsiChar(
    'SELECT name FROM sqlite_master WHERE type="table" ORDER BY name'),
    ResultPtr, RowCount, ColCount, ErrMsg);
  If ErrMsg <> '' Then
  Begin
    MessageBox(GetActiveWindow, PChar(ErrMsg + ' (' + IntToStr(iResult) + ')'), PChar(Form1.Caption), MB_ICONHAND);
    Exit;
  End;
  ResultStr := Pointer(ResultPtr);
  List.Clear;
  Inc(ResultStr);                       // ignore header
  For i := 1 To RowCount Do
  Begin
    If (CompareText('name', PAnsiChar(ResultStr^)) <> 0) Then
      List.Add(PAnsiChar(ResultStr^));
    Inc(ResultStr);
  End;
  If Assigned(ResultPtr) Then SQLite3_FreeTable(ResultPtr);
  Result := true;
End;

Procedure TForm1.Btn_ConnClick(Sender: TObject);
Const
  Ex2Table = 'Create Table ex2(ID integer primary key,a VARCHAR(50), d INTEGER);';
Var
  Pwd: String;
Begin
  Pwd := '123456';
  Case CBWork.ItemIndex Of
    0:                                  //ADO方式
      Begin
        If Btn_Conn.Tag = 1 Then
        Begin
          Conn.Close;
          Btn_Conn.Tag := 0;
          Btn_Conn.Caption := '連接';
          CBWork.Enabled := true;
          Exit;
        End;
        Conn.ConnectionString := 'Provider=MSDASQL.1;Extended Properties="Driver={SQLite3 ODBC Driver};Database=' + ExtractFilePath(ParamStr(0)) + '\Sample.DB;"';
        //Conn.ConnectionString :='DSN=SQLite3 Datasource;Database=' + ExtractFilePath(ParamStr(0)) + '\Sample.DB;PWD=' + Pwd;
        Conn.ConnectionString := 'Driver=SQLite3 ODBC Driver;Database=' + ExtractFilePath(ParamStr(0)) + '\Sample.DB;PassWord=' + Pwd;
        Try
          Conn.Open();
          Btn_Conn.Tag := 1;
          Btn_Conn.Caption := '關閉';
          CBWork.Enabled := false;
        Except
          On E: EADOError Do
          Begin
            Application.MessageBox(PChar(E.Message), '', 16);
            CBWork.Enabled := true;
            Exit;
          End;
        End;
        Conn.GetTableNames(Memo1.Lines);
        If Memo1.Lines.IndexOf('ex2') = -1 Then
          Conn.Execute(Ex2Table);
      End;
    1:                                  //API方式
      Begin
        If Btn_Conn.Tag = 1 Then
        Begin
          SQLite3_Close(DB);
          Btn_Conn.Tag := 0;
          Btn_Conn.Caption := '連接';
          CBWork.Enabled := true;
          Exit;
        End;
        If SQLite3_Open(PChar(AnsiToUtf8(ExtractFilePath(ParamStr(0)) + 'Sample.DB')), DB) = 0 Then
        Begin
          Btn_Conn.Tag := 1;
          Btn_Conn.Caption := '關閉';
          CBWork.Enabled := false;
          SqLite3_Key(DB, PChar(AnsiToUtf8(Pwd)), Length(Pwd));
          If Not GetTableNames(Memo1.Lines) Then Exit;  
          If Memo1.Lines.IndexOf('ex2') = -1 Then
            SQLite3_Exec(DB, PChar(Ex2Table), Nil, Nil, ErrMsg);
        End;
      End;
  End;
End;

Procedure TForm1.Btn_insertClick(Sender: TObject);
Const
  Sql = 'insert into ex2(a,d) values (''%s'',%d);';
Begin
  Randomize;
  Case CBWork.ItemIndex Of
    0: Conn.Execute(Format(Sql, ['中文測試 1', Random(99999999)])); //ADO方式
    1: SQLite3_Exec(DB, PChar(AnsiToUtf8(Format(Sql, ['中文測試 1', Random(99999999)]))), Nil, Nil, ErrMsg); //API方式
  End;
End;

Procedure TForm1.Btn_QueryClick(Sender: TObject);
Const
  Sql = 'select * from ex2;';
Begin
  Memo1.Clear;
  Case CBWork.ItemIndex Of
    0:                                  //ADO方式
      Begin
        ADOQuery1.Close;
        ADOQuery1.Sql.Text := Sql;
        ADOQuery1.Open;
        While Not ADOQuery1.Eof Do
        Begin
          Memo1.Lines.Add(Format('%s-----%d', [ADOQuery1.FieldByName('a').AsString, ADOQuery1.FieldByName('d').AsInteger]));
          ADOQuery1.Next;
        End;
      End;
    1:                                  //API方式
      Begin
        Memo1.Lines.Add('略...');
      End;
  End;
End;

Procedure TForm1.Btn_DeleteClick(Sender: TObject);
Const
  Sql = 'delete from ex2;';
Begin
  Case CBWork.ItemIndex Of
    0: Conn.Execute(Sql);               //ADO方式
    1: SQLite3_Exec(DB, PChar(Sql), Nil, Nil, ErrMsg); //API方式
  End;
  Memo1.Lines.Clear;
End;

Procedure TForm1.Btn_ChangePassClick(Sender: TObject);
Const
  Pwd = '123';
Begin
  Case CBWork.ItemIndex Of
    0:                                  //ADO方式
      //Conn.Execute('pragma password =NULL;');
      //Conn.Execute('pragma password =;');
      Conn.Execute('pragma password =' + Pwd + ';');
    1: SqLite3_Rekey(DB, PChar(Pwd), Length(Pwd)); //API方式
  End;

End;

Procedure TForm1.FormClose(Sender: TObject; Var Action: TCloseAction);
Begin
  If Btn_Conn.Tag = 1 Then Btn_ConnClick(Nil);
End;

End.