how to create C functions and refer to them in sql

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

how to create C functions and refer to them in sql

Chase-4

i need a trigger to create and insert a new guid into a table, but
apparently there is no built-in function for creating guids in sqlite.

i can create the guid in C using uuid_generate() and then
uuid_unparse() to get it into a string format.

but how can i call that c code from a trigger?

my understanding was that sqlite allows for user functions written in C
and used from within your sql code, but i forget where i saw that and
i'm so far unable to find in the docs where it shows how this is done.

anyone done this before?

- chase




-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: how to create C functions and refer to them in sql

Eugene Wee
You are probably looking for sqlite3_create_function:
http://www.sqlite.org/capi3ref.html#sqlite3_create_function

Regards,
Eugene Wee

Chase wrote:

>
> i need a trigger to create and insert a new guid into a table, but
> apparently there is no built-in function for creating guids in sqlite.
>
> i can create the guid in C using uuid_generate() and then uuid_unparse()
> to get it into a string format.
>
> but how can i call that c code from a trigger?
>
> my understanding was that sqlite allows for user functions written in C
> and used from within your sql code, but i forget where i saw that and
> i'm so far unable to find in the docs where it shows how this is done.
>
> anyone done this before?
>
> - chase

-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: how to create C functions and refer to them in sql

Chase-4
In reply to this post by Chase-4

okay, wait.... sorry.... wrong question.

here's the deal.  i want this trigger to fire -- and insert valid guids
into a table -- even outside the context of my app.

using sqlite3_create_function(), i can create a sort of temporary
function that only works from with my app (or other running instances
of my app), but i want it to fire even if someone opens the database
file in some other 3rd-party editor and inserts/updates/deletes the
table with the trigger.

well... it DOES fire, but when it does, it won't know what newuuid() means.

so i guess my question should be.... how to i **INSTALL/EMBED** my
custom function **into** the database file.

- chase






On August 6, 2007, Eugene Wee wrote:

> You are probably looking for sqlite3_create_function:
> http://www.sqlite.org/capi3ref.html#sqlite3_create_function
>
> Regards,
> Eugene Wee
>
> Chase wrote:
>>
>> i need a trigger to create and insert a new guid into a table, but
>> apparently there is no built-in function for creating guids in sqlite.
>>
>> i can create the guid in C using uuid_generate() and then uuid_unparse()
>> to get it into a string format.
>>
>> but how can i call that c code from a trigger?
>>
>> my understanding was that sqlite allows for user functions written in C
>> and used from within your sql code, but i forget where i saw that and
>> i'm so far unable to find in the docs where it shows how this is done.
>>
>> anyone done this before?
>>
>> - chase
>
> -----------------------------------------------------------------------------
> To unsubscribe, send email to [hidden email]
> -----------------------------------------------------------------------------
>


-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

RE: how to create C functions and refer to them in sql

James Dennett-3
> -----Original Message-----
> From: Chase [mailto:[hidden email]]
> Sent: Monday, August 06, 2007 2:43 PM
> To: [hidden email]
> Subject: Re: [sqlite] how to create C functions and refer to them in
sql
>
>
> okay, wait.... sorry.... wrong question.
>
> here's the deal.  i want this trigger to fire -- and insert valid
guids

> into a table -- even outside the context of my app.
>
> using sqlite3_create_function(), i can create a sort of temporary
> function that only works from with my app (or other running instances
> of my app), but i want it to fire even if someone opens the database
> file in some other 3rd-party editor and inserts/updates/deletes the
> table with the trigger.
>
> well... it DOES fire, but when it does, it won't know what newuuid()
> means.
>
> so i guess my question should be.... how to i **INSTALL/EMBED** my
> custom function **into** the database file.
>
> - chase

As I understand it: you can't do that.  There's no way to run compiled C
code that's stored within the database (unless you write a C function to
do so, but that's a chicken+egg situation).  This kind of thing doesn't
really match SQLite's mission of being an embedded database.

-- James


-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: how to create C functions and refer to them in sql

Clark Christensen
In reply to this post by Chase-4
I think you'd have to actually add your function into the SQLite source, and recompile.  My guess, not being a C guy, would be for you to have a look at the SQLite source (maybe in func.c?).  Since you already have a C function to do what you want, it seems pretty straightforward :-))

 -Clark

----- Original Message ----
From: Chase <[hidden email]>
To: [hidden email]
Sent: Monday, August 6, 2007 2:43:13 PM
Subject: Re: [sqlite] how to create C functions and refer to them in sql


okay, wait.... sorry.... wrong question.

here's the deal.  i want this trigger to fire -- and insert valid guids
into a table -- even outside the context of my app.

using sqlite3_create_function(), i can create a sort of temporary
function that only works from with my app (or other running instances
of my app), but i want it to fire even if someone opens the database
file in some other 3rd-party editor and inserts/updates/deletes the
table with the trigger.

well... it DOES fire, but when it does, it won't know what newuuid() means.

so i guess my question should be.... how to i **INSTALL/EMBED** my
custom function **into** the database file.

- chase






On August 6, 2007, Eugene Wee wrote:

> You are probably looking for sqlite3_create_function:
> http://www.sqlite.org/capi3ref.html#sqlite3_create_function
>
> Regards,
> Eugene Wee
>
> Chase wrote:
>>
>> i need a trigger to create and insert a new guid into a table, but
>> apparently there is no built-in function for creating guids in sqlite.
>>
>> i can create the guid in C using uuid_generate() and then uuid_unparse()
>> to get it into a string format.
>>
>> but how can i call that c code from a trigger?
>>
>> my understanding was that sqlite allows for user functions written in C
>> and used from within your sql code, but i forget where i saw that and
>> i'm so far unable to find in the docs where it shows how this is done.
>>
>> anyone done this before?
>>
>> - chase
>
> -----------------------------------------------------------------------------
> To unsubscribe, send email to [hidden email]
> -----------------------------------------------------------------------------
>


-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------





-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: how to create C functions and refer to them in sql

developir@yahoo.com
In reply to this post by Chase-4
--- Chase <[hidden email]> wrote:

> here's the deal.  i want this trigger to fire -- and insert valid guids
> into a table -- even outside the context of my app.
>
> using sqlite3_create_function(), i can create a sort of temporary
> function that only works from with my app (or other running instances
> of my app), but i want it to fire even if someone opens the database
> file in some other 3rd-party editor and inserts/updates/deletes the
> table with the trigger.
>
> well... it DOES fire, but when it does, it won't know what newuuid() means.
>
> so i guess my question should be.... how to i **INSTALL/EMBED** my
> custom function **into** the database file.

Not much you can do with the standard SQLite because it lacks a built-in
stored procedure language, and a facility to automatically initialize
stuff based on information in a table.

You can hack your own version of SQLite that upon creating a connection
it automatically loads functions written in an interpreted language
from a specific table. But this does not help you if others choose not
to run your specific version of SQLite to examine the database.

For something fairly close to what you're looking for, see Christian
Werner's sqlite ODBC driver:

  http://www.ch-werner.de/sqliteodbc/

Specifically, read up on see then DSN option LoadExt for loadable
extensions. Also take a look at its sqlite+tcc facility for
on-the-fly compilation of C functions for use in SQLite.


       
____________________________________________________________________________________
Yahoo! oneSearch: Finally, mobile search
that gives answers, not web links.
http://mobile.yahoo.com/mobileweb/onesearch?refer=1ONXIC

-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: how to create C functions and refer to them in sql

D. Richard Hipp
In reply to this post by Chase-4
"Chase" <[hidden email]> wrote:

> okay, wait.... sorry.... wrong question.
>
> here's the deal.  i want this trigger to fire -- and insert valid guids
> into a table -- even outside the context of my app.
>
> using sqlite3_create_function(), i can create a sort of temporary
> function that only works from with my app (or other running instances
> of my app), but i want it to fire even if someone opens the database
> file in some other 3rd-party editor and inserts/updates/deletes the
> table with the trigger.
>
> well... it DOES fire, but when it does, it won't know what newuuid() means.
>
> so i guess my question should be.... how to i **INSTALL/EMBED** my
> custom function **into** the database file.
>

Can you use hex(randomblob(20))?  Or do you really need a UUID
in the RFC 4122 format?  If all you need is a universally
unique identifier then hex(randomblob(20)) is going to be better
than anything that uuid_generate() will give you.

You cannot install/embed a custom function into a database file.

--
D. Richard Hipp <[hidden email]>


-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: how to create C functions and refer to them in sql

John Stanton-3
In reply to this post by Chase-4
Chase wrote:

>
> okay, wait.... sorry.... wrong question.
>
> here's the deal.  i want this trigger to fire -- and insert valid guids
> into a table -- even outside the context of my app.
>
> using sqlite3_create_function(), i can create a sort of temporary
> function that only works from with my app (or other running instances of
> my app), but i want it to fire even if someone opens the database file
> in some other 3rd-party editor and inserts/updates/deletes the table
> with the trigger.
>
> well... it DOES fire, but when it does, it won't know what newuuid() means.
>
> so i guess my question should be.... how to i **INSTALL/EMBED** my
> custom function **into** the database file.
>
> - chase
>
>
>
>
>
>
> On August 6, 2007, Eugene Wee wrote:
>
>> You are probably looking for sqlite3_create_function:
>> http://www.sqlite.org/capi3ref.html#sqlite3_create_function
>>
>> Regards,
>> Eugene Wee
>>
>> Chase wrote:
>>
>>>
>>> i need a trigger to create and insert a new guid into a table, but
>>> apparently there is no built-in function for creating guids in sqlite.
>>>
>>> i can create the guid in C using uuid_generate() and then
>>> uuid_unparse() to get it into a string format.
>>>
>>> but how can i call that c code from a trigger?
>>>
>>> my understanding was that sqlite allows for user functions written in
>>> C and used from within your sql code, but i forget where i saw that
>>> and i'm so far unable to find in the docs where it shows how this is
>>> done.
>>>
>>> anyone done this before?
>>>
>>> - chase
>>
You can make your new function permanent by modifying the Sqlite library
to make it a standard Sqlite function.  Use the examples in the source
as a template.  Note that your third party applications will have to be
linked with your modified Sqlite library.

-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------