Re: [sqlite] getting offending constraint

[Simon Slavin]

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

[James Gregurich]

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

[P Kishor]

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

[Simon Slavin]

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

[Jean-Denis Muys]

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

[Simon Slavin]

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

[James Gregurich]

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

[Simon Slavin]

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

[James Gregurich]

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

[P Kishor]

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

[James Gregurich]

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

[Roger Binns]

-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

[James Gregurich]

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