Sorry - it doesn't work.
sqlite3_exec with the pragma directive returns no error, but the program
still agrees to insert a record that violates foreign_key constraint.
Here is the tables creation
==============
CREATE TABLE people(
id integer,
nm text);
INSERT INTO "people" VALUES(1,'Jack');
INSERT INTO "people" VALUES(2,'Jill');
CREATE TABLE activity(
aid integer,
act text,
foreign key (aid) references people(id)
);
=============
Here's the simple c++ program (it is actually c...)
============
#include <stdio.h>
#include <sqlite3.h>
#include <unistd.h>
int main(int argc, char **argv){
sqlite3 *db;
char *zErrMsg = 0,*P0;
const char *Q0;
const char *Z0= {"insert into activity values (1,\"play\")"};
const char *Z1= {"insert into activity values (3,\"eat\")"};
int rc;
if( argc<2 ){
fprintf(stderr, "Usage: %s DATABASE\n", argv[0]);
return 101;
}
//open
rc = sqlite3_open(argv[1], &db);
if( rc ){
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
return 111;
}
rc = sqlite3_exec(db,"PRAGMA foreign_keys = ON;",NULL,NULL,&P0);
printf("pragma returns &d |%s|\n",rc,P0);
//clear
rc = sqlite3_exec(db,"delete from activity;",NULL,NULL,&P0);
//
rc = sqlite3_exec(db,"begin transaction;",NULL,NULL,&P0);
rc = sqlite3_exec(db,Z0,NULL,NULL,&P0);
Q0 = sqlite3_errmsg(db);
printf("|%s| - should succeed : %d,|%s|=>|%s|\n",Z0,rc,P0,Q0);
//
rc = sqlite3_exec(db,Z1,NULL,NULL,&P0);
Q0 = sqlite3_errmsg(db);
printf("|%s| - should fail : %d,|%s|=>|%s|\n",Z1,rc,P0,Q0);
rc = sqlite3_exec(db,"commit;",NULL,NULL,&P0);
sqlite3_close(db);
}
=========
and the results:
[shalom@pato sqlite]$ ./porta tik
pragma returns &d |(null)|
|insert into activity values (1,"play")| - should succeed : 0,|(null)|=>|not
an error|
|insert into activity values (3,"eat")| - should fail : 0,|(null)|=>|not an
error|
[shalom@pato sqlite]$ sqlite3 tik "select * from activity"
1|play
3|eat
=============
????
Shalom
On Thu, Mar 24, 2011 at 1:48 AM, BareFeetWare <list....@barefeetware.com>wrote:
> On 24/03/2011, at 2:50 AM, Shalom Elkin wrote:
>
> > I appreciate the input. Some of the advice comes obviously from very good
> > and talented people who find a challenge at doing things WITHOUT
> reverting
> > to code writing.
>
> Doing as much (or most often, all) of the logic in SQL (instead of
> application code) removes a level of complexity, but is also generally
> faster and internally consistent.
>
> > I did a small program. Current show -stopper :
> >
> > what is the API equivalent of
> >
> > PRAGMA foreign_keys = ON;
>
> You can just send each of the SQL commands, including the pragma statement,
> in sqlite_exec (or you can get fancy with sqlite_prepare etc where it makes
> sense).
>
> Tom
> BareFeetWare
>
> --
> iPhone/iPad/iPod and Mac software development, specialising in databases
> develo...@barefeetware.com
> --
> Comparison of SQLite GUI tools:
> http://www.barefeetware.com/sqlite/compare/?ml
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
Shalom Elkin
+972-544-704994
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
