Help with Create own Function in sqlite with Zeos

[Sapiem]

Hello:

Please, I need create any function like Pos() inside sqlite. There is sqlite_create_function, but I don't know how to use/translate it to use with Zeos in Delphi.

[EgonHugeist]

Sapiem,

Oh that's not so easy to implement in the current Zeos-Design. I had a look how we could implement such interfaces for callback-functions but i must admit currently i've no clue how to support such things from the plain-layer up to the component-layer.

The transaltion of this litte C-Lines isn't the problem. Further more is this a very spezial implementation for one protocol. It might be possible if you switch down to the dbc-layer??..

[mdaems]

I suppose it would be possible to implement this on the dbc level the way the encryption stuff has been implemented: by adding methods to the TZSQLiteConnection that allow to attach the callback functions.

Another place in the zeoslib codebase where we're doing something with callback functions in the postgresql driver : look for DefaultNoticeProcessor in ZDbcPostgreSql.pas. (There we replace the default notice processor of the postgres library by a callback function of our own to avoid the notices are written to STDOUT in console apps)

So Sapiem, I'm sure this is possible, but you'll have to dive in the code yourself, I'm afraid. If you're used to writing callback functions for sqlite, I'm sure this shouldn't be a problem. And if you have it working fine, just share the code. So we can integrate it in a next zeoslib version.

[fduenas]

Hi To all just wanted to give my two cents about this:

You should take a look to the project: http://code.google.com/p/sv-utils/

also

http://code.google.com/p/sv-utils/wiki/Intro

Specially the SQLite 3 components

There Tim Anderson have already translated the headers necessary to register custom functions, what we would need is a wrapper for zeos only that will manage those functions and register them into SQL API

Check specially these sources:
http://code.google.com/p/sv-utils/sourc ... 3%2FSource

Here are the Headers for the DLL:
http://code.google.com/p/sv-utils/sourc ... QLite3.pas

Here are the definition and management of custom functions:
http://code.google.com/p/sv-utils/sourc ... Table3.pas

Here are the definition for managing virtual tables:
http://code.google.com/p/sv-utils/sourc ... Table3.pas

And check the test cases to see how it works:
http://code.google.com/p/sv-utils/sourc ... rce%2FTest

They also have added what in Sqlite is named virtual tables.
http://www.sqlite.org/vtab.html

I have used once an implementation where you can link other Tdatasets with this virtual tables and you can run a full query over them. These virtual tables are only wrappers and cursors for each linked Tdataset so they will work over the TDataset data, but you can achieve a big goal:

Use ZEOS to run queries over any Tdataset compatible componet. Like the old and now renewed TxQuery (http://code.google.com/p/txquery/) which personally I'm giving support with other developers:

Check:
http://code.google.com/p/txquery/issues/detail?id=40

Here is a link of a commercial component, formerly named anyDAC and that now is FireDAC and is part of Embarcadero suite. you can see what can be achieved with Virtual tables, they called it there LocalSQL, but they implement SQLite virtual tables.

http://www.da-soft.com/blogs/anydac-for ... l-sql.html

Regards to all of you.