Re: [sqlite] getting offending constraint
On 2 Jul 2009, at 6:35pm, James Gregurich wrote: > works as expected in the sqlite3 exe. in C code, I get > "constraint failed" from sqlite3_errmsg. [...] > CREATE TRIGGER trig BEFORE INSERT ON test1b > BEGIN > SELECT CASE > WHEN (1) > THEN RAISE(ABORT, 'no parent element') > END; The language in that looks a little strange. Make your trigger more obvious and more like standard SQLite3: CREATE TRIGGER trig BEFORE INSERT ON test1b FOR EACH ROW BEGIN SELECT RAISE(ABORT, 'no parent element') WHERE new.i = 1; END; (Note that this will prevent one of your existing table entries.) See if you still get inconsistent results. If you don't, make gradual changes until it does what you want it to do. If you still get inconsistent results, you're perhaps using the wrong function to fetch the text of the error message. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] getting offending constraint
I dropped the constraint and added the trigger. strange. works as expected in the sqlite3 exe. in C code, I get "constraint failed" from sqlite3_errmsg. If I drop the trigger shown below, the C code has no constraint violation as would be expected which means the trigger is causing the constraint violation. sqlite> .dump BEGIN TRANSACTION; CREATE TABLE test1(i INTEGER, a INTEGER, b INTEGER, c INTEGER, d INTEGER); INSERT INTO "test1" VALUES(0,1,2,3,4); INSERT INTO "test1" VALUES(1,10,20,30,40); INSERT INTO "test1" VALUES(2,100,200,300,400); INSERT INTO "test1" VALUES(3,1000,2000,3000,4000); INSERT INTO "test1" VALUES(4,1,2,3,4); INSERT INTO "test1" VALUES(5,10,20,30,40); CREATE TABLE test1a(i INTEGER, a INTEGER, b INTEGER); CREATE TABLE test1b(i INTEGER, c INTEGER, d INTEGER); CREATE TRIGGER trig BEFORE INSERT ON test1b BEGIN SELECT CASE WHEN (1) THEN RAISE(ABORT, 'no parent element') END; END; COMMIT; sqlite> INSERT INTO "test1b" VALUES(1,10,20); SQL error: no parent element sqlite> On Jul 1, 2009, at 6:40 PM, Simon Slavin wrote: > > On 2 Jul 2009, at 1:57am, James Gregurich wrote: > >> I tried that, but I still got back "constraint failed" rather than my >> RAISE message. Since you say it should work, I probably did something >> wrong. I'll look at it again. > > If you left the constraint definition in in your table definition then > you're getting an error from that rather than from the trigger. > > Simon. > ___ > 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] getting offending constraint
On Thu, Jul 2, 2009 at 4:17 AM, Jean-Denis Muys wrote: > Wow, I have been on the internet since 1986, and I had never realized that > this could be a problem. I am often guilty of the same, for lazyness > reasons, as this is a convenient way to avoid having to reenter the "to", > "cc", and "bcc" fields. > > I went back to James' original message, which my mail client definitely does > NOT show as a followup to anything. > > But when I examine its raw headers, I found this one: > > In-reply-to: > > Is this the header that made you point your finger? Right. Keep in mind, not all email clients use this... some do use just the subject line, and while that may be nicer for humans, you can get threading of unrelated emails just because their subject lines are the same. > > In any case, I learned something today, and though this is off-topic here, > it may be worth going to the bottom of this. > Another thing worth thinking about -- there is absolutely no need to cc me (or anyone) an email personally if you are also sending it to the main list. Keep in mind, I am also a member of the list. By cc-ing it to me, you end up sending me two copies, and that necessitates one extra delete click for me. :-) > Jean-Denis > > > > On 7/1/09 22:41 , "P Kishor" wrote: > >> On Wed, Jul 1, 2009 at 3:39 PM, James Gregurich wrote: >>> >>> How would I have "hijacked" a thread? I changed the subject and >>> removed the original text. >>> >> ... >> >> that is exactly how a thread is hijacked... changing the subject is >> not enough. Every message has a unique id that is used by the mail >> programs to keep track of threading. >> ___ >> 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] getting offending constraint
On 2 Jul 2009, at 10:17am, Jean-Denis Muys wrote: > But when I examine its raw headers, I found this one: > > In-reply-to: > > > Is this the header that made you point your finger? Yes, this header is how some mail clients understand threading. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] getting offending constraint
Wow, I have been on the internet since 1986, and I had never realized that this could be a problem. I am often guilty of the same, for lazyness reasons, as this is a convenient way to avoid having to reenter the "to", "cc", and "bcc" fields. I went back to James' original message, which my mail client definitely does NOT show as a followup to anything. But when I examine its raw headers, I found this one: In-reply-to: Is this the header that made you point your finger? In any case, I learned something today, and though this is off-topic here, it may be worth going to the bottom of this. Jean-Denis On 7/1/09 22:41 , "P Kishor" wrote: > On Wed, Jul 1, 2009 at 3:39 PM, James Gregurich wrote: >> >> How would I have "hijacked" a thread? I changed the subject and >> removed the original text. >> > ... > > that is exactly how a thread is hijacked... changing the subject is > not enough. Every message has a unique id that is used by the mail > programs to keep track of threading. > ___ > 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] getting offending constraint
On 2 Jul 2009, at 1:57am, James Gregurich wrote: > I tried that, but I still got back "constraint failed" rather than my > RAISE message. Since you say it should work, I probably did something > wrong. I'll look at it again. If you left the constraint definition in in your table definition then you're getting an error from that rather than from the trigger. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] getting offending constraint
thanks. I tried that, but I still got back "constraint failed" rather than my RAISE message. Since you say it should work, I probably did something wrong. I'll look at it again. On Jul 1, 2009, at 3:59 PM, Simon Slavin wrote: > > On 1 Jul 2009, at 8:19pm, James Gregurich wrote: > >> Would there be a way to identify the offending constraint if >> "SQLITE_CONSTRAINT" is returned? >> >> >> sqlite3_errmsg is just telling me "constraint failed"...which is of >> limited usefulness. > > Instead of the constraint, you could define a trigger, and use the > 'RAISE' form to supply your own error message. Here's an example: > > CREATE TRIGGER authors_books_insert BEFORE INSERT ON books > FOR EACH ROW BEGIN > SELECT RAISE(ROLLBACK, 'Attempt to add a book with an author > number which is not valid.') > WHERE (SELECT id FROM authors WHERE id = new.author) IS NULL; > END > > You get back exactly the error message you put in. > > Simon. > ___ > 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] getting offending constraint
On 1 Jul 2009, at 8:19pm, James Gregurich wrote: > Would there be a way to identify the offending constraint if > "SQLITE_CONSTRAINT" is returned? > > > sqlite3_errmsg is just telling me "constraint failed"...which is of > limited usefulness. Instead of the constraint, you could define a trigger, and use the 'RAISE' form to supply your own error message. Here's an example: CREATE TRIGGER authors_books_insert BEFORE INSERT ON books FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'Attempt to add a book with an author number which is not valid.') WHERE (SELECT id FROM authors WHERE id = new.author) IS NULL; END You get back exactly the error message you put in. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] getting offending constraint
ah. I have no knowledge of how mailing list programs work. no "poor etiquette" was intended. On Jul 1, 2009, at 1:41 PM, P Kishor wrote: > On Wed, Jul 1, 2009 at 3:39 PM, James Gregurich > wrote: >> >> How would I have "hijacked" a thread? I changed the subject and >> removed the original text. >> > ... > > that is exactly how a thread is hijacked... changing the subject is > not enough. Every message has a unique id that is used by the mail > programs to keep track of threading. > ___ > 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] getting offending constraint
On Wed, Jul 1, 2009 at 3:39 PM, James Gregurich wrote: > > How would I have "hijacked" a thread? I changed the subject and > removed the original text. > ... that is exactly how a thread is hijacked... changing the subject is not enough. Every message has a unique id that is used by the mail programs to keep track of threading. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] getting offending constraint
How would I have "hijacked" a thread? I changed the subject and removed the original text. On Jul 1, 2009, at 12:32 PM, Roger Binns wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > James Gregurich wrote: >> >> howdy! > > You hijacked someone else's thread by hitting reply, rather than > starting a new one. That is very poor netiquette. > >> Would there be a way to identify the offending constraint if >> "SQLITE_CONSTRAINT" is returned? >> >> sqlite3_errmsg is just telling me "constraint failed"...which is of >> limited usefulness. > > http://www.sqlite.org/cvstrac/tktview?tn=1648 > > Roger > -BEGIN PGP SIGNATURE- > Version: GnuPG v1.4.9 (GNU/Linux) > > iEYEARECAAYFAkpLub0ACgkQmOOfHg372QTM5wCeO38HYFTMGi77aHcgtl1Y1xyK > H3EAoJav+Q+pAq3LzpWnoMugx87ZnmrF > =JN3m > -END PGP SIGNATURE- > ___ > 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] getting offending constraint
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 James Gregurich wrote: > > howdy! You hijacked someone else's thread by hitting reply, rather than starting a new one. That is very poor netiquette. > Would there be a way to identify the offending constraint if > "SQLITE_CONSTRAINT" is returned? > > sqlite3_errmsg is just telling me "constraint failed"...which is of > limited usefulness. http://www.sqlite.org/cvstrac/tktview?tn=1648 Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) iEYEARECAAYFAkpLub0ACgkQmOOfHg372QTM5wCeO38HYFTMGi77aHcgtl1Y1xyK H3EAoJav+Q+pAq3LzpWnoMugx87ZnmrF =JN3m -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] getting offending constraint
howdy! Would there be a way to identify the offending constraint if "SQLITE_CONSTRAINT" is returned? sqlite3_errmsg is just telling me "constraint failed"...which is of limited usefulness. -James ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users