Re: [sqlite] Triggers in Sqlite using C

[Jay A. Kreibich]

On Fri, Jun 15, 2012 at 12:42:45PM +, Black, Michael (IS) scratched on the 
wall:
> But as somebody pointed out if you muck with database from the shell or
> any other program it will die with "function not found" if the trigger
> is called.
> 
> Ergo a dummy function to make it happy and non-fatal.

  I must have missed something in the thread, but if the trigger is
  part of the database design, it needs to be there, or it shouldn't be
  there and some other means should be used.
  
  If you put the function code into a module, it can also be loaded
  by the shell and many programs.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Triggers in Sqlite using C

[Igor Tandetnik]

Black, Michael (IS)  wrote:
> Looks like that is getting close...but when is this called?  BEFORE or AFTER?

After, as far as I can tell.
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Triggers in Sqlite using C

[Black, Michael (IS)]

Looks like that is getting close...but when is this called?  BEFORE or AFTER?



And you also wouldn't know which trigger got calledonly which table was 
touched.



The dummy function would solve both those problems.



So I can still see an sqlite3_callback() either overloaded or a function 
pointer replacement.  Callable by an SQL function callback('Your info') or such 
which gives the same info as the update_hook plus a known value under control 
of the programmer.







Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Igor Tandetnik [itandet...@mvps.org]
Sent: Friday, June 15, 2012 8:15 AM
To: sqlite-users@sqlite.org
Subject: EXT :Re: [sqlite] Triggers in Sqlite using C

Black, Michael (IS) <michael.bla...@ngc.com> wrote:
> Could the SQLite code add a new dummy function for a callback which we can 
> then override with our own library?

sqlite3_update_hook
--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Triggers in Sqlite using C

[Igor Tandetnik]

Bageesh.M.Bose <bageesh...@gmail.com> wrote:
> Can anyone tell "How to use Triggers in Sqlite using C"

What do you mean by "use triggers", exactly? What are you trying to achieve?

If you are asking how to create a trigger, then it's the same way as creating a 
table - you open a database connection (with sqlite3_open or similar) and issue 
an appropriate CREATE TRIGGER statement (e.g. with sqlite3_exec).
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Triggers in Sqlite using C

[Igor Tandetnik]

Black, Michael (IS)  wrote:
> Could the SQLite code add a new dummy function for a callback which we can 
> then override with our own library?

sqlite3_update_hook
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Triggers in Sqlite using C

[Black, Michael (IS)]

But as somebody pointed out if you muck with database from the shell or any 
other program it will die with "function not found" if the trigger is called.

Ergo a dummy function to make it happy and non-fatal.



Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Jay A. Kreibich [j...@kreibi.ch]
Sent: Friday, June 15, 2012 7:25 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Triggers in Sqlite using C

On Fri, Jun 15, 2012 at 12:12:22PM +, Black, Michael (IS) scratched on the 
wall:
> Could the SQLite code add a new dummy function for a callback which
> we can then override with our own library?

  Just use an SQL function:

  http://sqlite.org/c3ref/create_function.html

   -j

--
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Triggers in Sqlite using C

[Jay A. Kreibich]

On Fri, Jun 15, 2012 at 12:12:22PM +, Black, Michael (IS) scratched on the 
wall:
> Could the SQLite code add a new dummy function for a callback which
> we can then override with our own library?

  Just use an SQL function:

  http://sqlite.org/c3ref/create_function.html

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Triggers in Sqlite using C

[Black, Michael (IS)]

Could the SQLite code add a new dummy function for a callback which we can then 
override with our own library?

That would provide an easy-to-use interface which would then still work in the 
shell (unless you're doing some other database functions inside the callback -- 
coider beware).



e.g.

void callback(sqlite3_context *context, int argc, sqlite3_value **argv)

{

static int called;

// spit out message about trigger callback called but not used -- maybe 
only spit it out once?

// what effect would this have on the trigger?

if (called == 0) {

called = 1;

sqlite3_result_text(context, "Using sqlite dummy 
callback",-1,SQLITE_STATIC);

}

}

Would probably be nice to be able to turn off the message too with a pragma for 
completeness?





Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Christian Smith [csm...@thewrongchristian.org.uk]
Sent: Friday, June 15, 2012 4:54 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Triggers in Sqlite using C

On Fri, Jun 15, 2012 at 11:59:33AM +0530, Bageesh.M.Bose wrote:
> Can anyone tell "How to use Triggers in Sqlite using C"
>

Triggers in SQLite can only contain SQL DML statements. But, you can
interface with C by defining functions, then calling those functions
using SQL SELECT command.

For example:

CREATE TRIGGER mytrigger AFTER INSERT ON mytable
FOR EACH ROW
BEGIN
SELECT myfunction(NEW.field1, NEW.field2);
END;

where myfunction would be defined using:

http://sqlite.org/c3ref/create_function.html

However, such usage would result in a database that can only be used
in your application. Using the database in the SQLite shell or some
other application might result in function not found errors if the
trigger is fired.

Hope that helps,
Christian
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Triggers in Sqlite using C

[Christian Smith]

On Fri, Jun 15, 2012 at 11:59:33AM +0530, Bageesh.M.Bose wrote:
> Can anyone tell "How to use Triggers in Sqlite using C"
> 

Triggers in SQLite can only contain SQL DML statements. But, you can
interface with C by defining functions, then calling those functions
using SQL SELECT command.

For example:

CREATE TRIGGER mytrigger AFTER INSERT ON mytable
FOR EACH ROW
BEGIN
SELECT myfunction(NEW.field1, NEW.field2);
END;

where myfunction would be defined using:

http://sqlite.org/c3ref/create_function.html

However, such usage would result in a database that can only be used
in your application. Using the database in the SQLite shell or some
other application might result in function not found errors if the
trigger is fired.

Hope that helps,
Christian
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Triggers in Sqlite using C

[Bageesh.M.Bose]

Can anyone tell "How to use Triggers in Sqlite using C"

-- 
With Regards,
*Bageesh.M.Bose*
**
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users