How can I create a user-defined function?

[pastor]

Hi All!

How can I create a user-defined function in Zeos? I need it for making case-insensitive LIKE.

This this solution for TSQLite3Dataset but I need same for ZeosLib - http://forum.lazarus.freepascal.org/ind ... ic=34259.0

sqlite3_create_function(...

[marsupilami]

Hello pastor,

user defined functions in Firebird have nothing to do with the client program and Zeos. If you want to extend the firebird database with other functions, you need to write a dll/so and register the functions in your database. The following ressources might help you:

https://www.firebirdsql.org/refdocs/lan ... reext.html
https://www.codeproject.com/articles/43 ... sql-server
http://www.firebirdfaq.org/faq83/
http://www.firebirdfaq.org/faq169/

With best regards,

Jan

[pastor]

Thank you, Jan! But I am using SQLite3.

I've found solution for SQLDB:

uses Unit_SQLite3Dataset_LanguageSupport;
...
SQLite3Dataset_LanguageSupport (SQLite3Connection.Handle);

But it does`t work in Zeos.
How to get a Handle of SQLite database in Zeoslib?

[marsupilami]

Hello Pastor,

indeed that seems to be hard. You would have to Query the TZConnection object for the IZConnection interface. You can do that With TZConnection.DbcConnection. That one should be an IZSQLiteConnection interface - so cast it. It most probably doesn't have a method for connecting user defined functions but it should be fairly easy to extend it to do that. Have a look at ZDbcSqlite.pas. There you will find the interface definition and the TZSQLiteConnection object. Extend both and you should be fine. If you have questions, just ask them and I will answer.

With best regards,

Jan

[pastor]

Jan, thanks for the tip!

This code works:

uses ZDbcSqLite, Unit_SQLite3Dataset_LanguageSupport;
var dbHandle: Pointer;
...
dbHandle := (ZConnection1.DbcConnection as TZSQLiteConnection).GetConnectionHandle();
SQLite3Dataset_LanguageSupport(dbHandle);

Blessings..