Nataraj S Narayan escreveu:
Hi
Anybody has written custom UDF for Sqlite 3 using fpc?
If so kindly help me with some hints to get started.
See the attached files. It implements a custom format date function
Luiz
program testfunction;
{$Mode ObjFpc}
{$H+}
{$define DEBUGHEAP}
uses
{$ifdef DEBUGHEAP}
Heaptrc,
{$endif}
{$ifdef Linux}
cmem,
{$endif}
sqlite3ds,
sqlite3,
sysutils, db,
inifiles;
procedure CustomFunction(context: PSqlite3_Context; argc: LongInt; argv: PPSqlite3_Value); cdecl;
procedure DumpVariable(Index: Integer);
begin
case sqlite3_value_type(argv[Index]) of
SQLITE_INTEGER:
WriteLn('Type: SQLITE_INTEGER - Value: ', sqlite3_value_int(argv[Index]),' ' ,sqlite3_value_double(argv[Index]));
SQLITE_NULL:
WriteLn('Type: SQLITE_NULL - Value: NULL');
SQLITE_FLOAT:
writeln('Type: SQLITE_FLOAT - Value: ', sqlite3_value_double(argv[Index]));
SQLITE_TEXT:
writeln('Type: SQLITE_TEXT - Value: ', sqlite3_value_text(argv[Index]));
else
WriteLn('Type: Unknown - Value: ', sqlite3_value_text(argv[Index]));
end;
end;
var
i: Integer;
begin
WriteLn('argc: ', argc);
for i := 0 to argc - 1 do
DumpVariable(i);
sqlite3_result_int(context, 13);
end;
procedure StrFTime(context: PSqlite3_Context; argc: LongInt; argv: PPSqlite3_Value); cdecl;
var
Year, Month, Day: word;
FormatStr: String;
begin
if (sqlite3_value_type(argv[1]) SQLITE_INTEGER) and
(sqlite3_value_type(argv[1]) SQLITE_FLOAT) then
begin
sqlite3_result_null(context);
Exit;
end;
FormatStr := UpperCase(sqlite3_value_text(argv[0]));
DecodeDate(sqlite3_value_double(argv[1]), Year, Month, Day);
if FormatStr = '%Y' then
sqlite3_result_int(context, Year)
else
if FormatStr = '%M' then
sqlite3_result_int(context, Month)
else
if FormatStr = '%D' then
sqlite3_result_int(context, Day)
else
sqlite3_result_null(context);
end;
const
SQLITEDS_TESTS_INI_FILE = 'sqlitedstests.ini';
DEFAULT_TABLENAME = 'tabletest';
DEFAULT_FILENAME = 'test.db';
var
dsTest:TSqlite3Dataset;
ini: TIniFile;
begin
{$ifdef DEBUGHEAP}
SetHeapTraceOutput(ExtractFileName(ParamStr(0))+'.heap.log');
{$endif}
dsTest := TSqlite3Dataset.Create(nil);
with dsTest do
begin
//Load Database properties from a inifile
ini := TIniFile.Create(SQLITEDS_TESTS_INI_FILE);
FileName := ini.ReadString('testinfo', 'filename', DEFAULT_FILENAME);
TableName := ini.ReadString('testinfo', 'tablename', DEFAULT_TABLENAME);
ini.Destroy;
if not TableExists then
begin
with FieldDefs do
begin
Clear;
Add('Integer', ftAutoInc);
Add('String', ftString);
Add('DateTime', ftDateTime);
Add('Date', ftDate);
Add('Time', ftTime);
end;
CreateTable;
end;
writeln('ReturnString after CreateTable: ', ReturnString);
Open;
sqlite3_create_function(SqliteHandle, 'strftime', 2, sqlite_any, nil, @strftime, nil, nil);
Append;
FieldByName('DateTime').AsDateTime := Now;
FieldByName('Date').AsDateTime := Date;
FieldByName('Time').AsDateTime := Time;
WriteLn('Date: ',FieldByName('Date').AsDateTime);
WriteLn('Time: ',FieldByName('Time').AsDateTime);
WriteLn('DateTime: ',FieldByName('DateTime').AsDateTime);
Post;
ApplyUpdates;
WriteLn(QuickQuery('Select strftime(''%Y'',Date) as mydate from '+ tablename +' limit 1;'));
WriteLn(QuickQuery('Select strftime(''%M'',Date) as mydate from '+ tablename +' limit 1;'));
WriteLn(QuickQuery('Select strftime(''%D'',DateTime) as mydate from '+ tablename +' limit 1;'));
Destroy;
end;
end.
___
fpc-pascal maillist - fpc-pascal@lists.freepascal.org
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
