disqlite3_create_function.dpr

来自「DELPHI 访问SQLITE3 数据库的VCL控件」· DPR 代码 · 共 158 行

{ DISQLite3 example project showing how to create user defined functions which
  can will become of the SQL syntax and can be executed just like normal SQL
  functions.

  !!! Important: This project requires the full version of DISQLite3 since
  !!!            it uses some functions not available in DISQLite Personal.

  This demo uses the table created by the Create_Table demo. Compile and
  execute the Create_Table demo at least once before running the project.

  Visit the DISQLite3 Internet site for latest information and updates:

    http://www.yunqa.de/delphi/

  Copyright (c) 2005-2007 Ralf Junker, The Delphi Inspiration <[e-mail]>

------------------------------------------------------------------------------ }

program DISQLite3_Create_Function;

{$I DI.inc}
{$I DISQLite3.inc}

{$IFDEF DISQLite3_Personal}
!!! This project requires functionality unavailable in DISQLite3 Personal. !!!
!!! To compile, download DISQLite3 Pro from www.yunqa.de/delphi/           !!!
{$ENDIF DISQLite3_Personal}

{$APPTYPE CONSOLE}

uses
  SysUtils, DISQLite3Api,
  DISQLite3_Demos_Common in '..\DISQLite3_Common_Units\DISQLite3_Demos_Common.pas';

//------------------------------------------------------------------------------

{ This Pascal function implements the DISQLite3 POS function. }
procedure Pos_Function_Callback(Context: Pointer; nArgs: Integer; Args: PPointerArray);
var
  Arg1, Arg2: AnsiString;
  p: Integer;
begin
  { Retrieve the two string arguments. }
  Arg1 := sqlite3_value_str(Args[0]); // Compile Error? Read first comment above!
  Arg2 := sqlite3_value_str(Args[1]);
  { Determine the POSition. }
  p := Pos(Arg1, Arg2);
  { Set the result value. }
  sqlite3_result_int(Context, p);
end;

//------------------------------------------------------------------------------

{ This Pascal function implements the DISQLite3 REPLACE function. }
procedure Replace_Function_Callback(Context: Pointer; nArgs: Integer; Args: PPointerArray);
var
  Arg1, Arg2, Arg3, r: AnsiString;
begin
  { Retrieve the arguments as strings. }
  Arg1 := sqlite3_value_str(Args[0]);
  Arg2 := sqlite3_value_str(Args[1]);
  Arg3 := sqlite3_value_str(Args[2]);
  { Replace all substrings. }
  r := StringReplace(Arg1, Arg2, Arg3, [rfReplaceAll]);
  { Return the result. }
  sqlite3_result_text(Context, Pointer(r), Length(r), SQLITE_TRANSIENT);
end;

//------------------------------------------------------------------------------

{ This is a callback function triggered for each row in the table.
  We use it to write all fields of a record to the console. }
function WriteRowCallback(UserData: Pointer; ColumnCount: Integer; ColumnValues, ColumnNames: PPAnsiCharArray): Integer;
var
  i: Integer;
begin
  for i := 0 to ColumnCount - 1 do
    begin
      if i > 0 then Write('|');
      Write(ColumnValues[i]);
    end;
  WriteLn;
  Result := 0; // Return 0 to retrieve the next record, 1 to abort.
end;

//------------------------------------------------------------------------------

{ Shows the results of an SQL SELECT. }
procedure ShowSelect(const ADescription, ASQL: AnsiString);
begin
  WriteLn('* ', ADescription, ':');
  WriteLn;
  WriteLn('  ', ASQL);
  WriteLn;
  sqlite3_check(sqlite3_exec(DB, PAnsiChar(ASQL), WriteRowCallback, nil, nil), DB);
  WriteLn;
end;

//------------------------------------------------------------------------------

begin
  WriteLn('SQLite Version ', sqlite3_libversion); WriteLn;
  try
    Open_Demo_Database;
    try
      { Register user functions with the database handle. }

      { The POS function returns the position of the first character in a
        specified substring that occurs in a given string. }
      sqlite3_create_function(
        DB, // Handle to database
        'POS', // The new function's name.
        2, // Number of function arguments.
        SQLITE_UTF8, // Preferred string encoding for function callback.
        nil, // User data.
        Pos_Function_Callback, // Function callback
        nil, // Step callback, not needed.
        nil); // Final callback, not needed.

      { The REPLACE function replaces substrings within a string. }
      sqlite3_create_function(
        DB,
        'REPLACE',
        3,
        SQLITE_UTF8,
        nil,
        Replace_Function_Callback,
        nil,
        nil);

      { Run some example queries to demonstrate the functions.}

      { We use POS to search for a space character in FirstName which
        separates 2 first names. }
      ShowSelect(
        'People with 2 first names determined by POS',
        'SELECT * FROM People WHERE POS('' '', FirstName) > 0 LIMIT 5;');

      { Demonstrate the REPLACE function. For simplicity, this SELECT does not
        return any data, but the REPLACE function can just as well be used with
        all kind of data operations. }
      ShowSelect('Simple, no data REPLACE',
        'SELECT REPLACE (''Text with string to replace.'', ''string'', ''nothing'');');

    finally
      Close_Demo_Database;
    end;

  except
    on e: Exception do
      WriteLn(e.Message);
  end;

  WriteLn('Done - Press ENTER to Exit');
  ReadLn;
end.