Re: [sqlite] Raise is not working

[Kees Nuyt]

On Sun, 12 Jul 2009 18:03:14 -0700, "Jim Showalter"
 wrote:

>Schema:
>
>create table words (_id integer primary key autoincrement, wordtext 
>text not null unique);
>
>create table definitions (_id integer primary key autoincrement, 
>owningWordId integer not null unique, deftext text not null);
>
>create trigger fki_definitions_words_id before insert on definitions
>for each row
>begin
>select raise (rollback, 'insert on table definitions violates 
>foreign-key constraint fki_definitions_words_id')
>where (select _id from words where _id = NEW.owningWordId ) is 
>null;
>
>end;
>
>Call db.insert, passing it a definition that has the owningWordId set 
>to -1, and the insert returns a -1 instead of throwing.
>
>Because it doesn't throw, I don't have the error message "insert on 
>table definitions violates foreign-key constraint 
>fki_definitions_words_id" to work from. Information is simply lost.
>
>Why isn't it raising an exception? 

It does raise. 
SQLite behaves as expected.
Must be the wrapper.

sqlite_version():3.6.14.2

create table words (
_id integer primary key autoincrement, 
wordtext text not null unique
);
create table definitions (
_id integer primary key autoincrement,
owningWordId integer not null unique,
deftext text not null
);
create trigger fki_definitions_words_id 
before insert on definitions
for each row
begin
select raise (rollback, 
'insert on table definitions violates foreign-key
constraint fki_definitions_words_id')
where (
select _id from words where _id = NEW.owningWordId
) is null;
end;
.bail off
insert into words (wordtext) 
VALUES ('wordone');
insert into definitions (owningWordId,deftext) 
VALUES ((select last_insert_rowid()),'defone');
insert into definitions (owningWordId,deftext) 
VALUES (-1,'deftwo');
SQL error near line 7: insert on table definitions violates
foreign-key constraint fki_definitions_words_id

-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Raise is not working

[Simon Slavin]

On 13 Jul 2009, at 2:17am, Jim Showalter wrote:

> However, calling insertOrThrow raises SQLiteConstraintException, which
> does not contain the text of the raise specified in the schema.
> Instead, it just has:
> error code 19: constraint failed
>
>
>
> Is there any way to get SQLite to honor the message in the raise in
> the schema definition?

You're the second person to report this problem recently.  Can I ask  
how you're getting the text of the error message ?  If you just look  
up 19 it will obviously give you the standard error text for 19, but  
if you call the functions at

http://www.sqlite.org/c3ref/errcode.html

you should get the right text.

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

Re: [sqlite] Raise is not working

[Jim Showalter]

I'm defining the text of the error message in the schema, in the 
definition of raise, in the trigger definition:

create trigger fki_definitions_words_id before insert on definitions
for each row
begin
select raise (rollback, 'insert on table definitions violates 
foreign-key constraint fki_definitions_words_id')
where (select _id from words where _id = NEW.owningWordId ) is
null;

The text I expect to see in the thrown exception is:

'insert on table definitions violates foreign-key constraint 
fki_definitions_words_id'

but that's not happening.



- Original Message - 
From: "Simon Slavin" <slav...@hearsay.demon.co.uk>
To: "Jim Showalter" <j...@jimandlisa.com>; "General Discussion of 
SQLite Database" <sqlite-users@sqlite.org>
Sent: Sunday, July 12, 2009 7:44 PM
Subject: Re: [sqlite] Raise is not working


>
> On 13 Jul 2009, at 2:17am, Jim Showalter wrote:
>
>> However, calling insertOrThrow raises SQLiteConstraintException, 
>> which
>> does not contain the text of the raise specified in the schema.
>> Instead, it just has:
>> error code 19: constraint failed
>>
>>
>>
>> Is there any way to get SQLite to honor the message in the raise in
>> the schema definition?
>
> You're the second person to report this problem recently.  Can I ask 
> how you're getting the text of the error message ?  If you just look 
> up 19 it will obviously give you the standard error text for 19, but 
> if you call the functions at
>
> http://www.sqlite.org/c3ref/errcode.html
>
> you should get the right text.
>
> Simon. 

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

Re: [sqlite] Raise is not working

[Jim Showalter]

However, calling insertOrThrow raises SQLiteConstraintException, which 
does not contain the text of the raise specified in the schema. 
Instead, it just has:
error code 19: constraint failed



Is there any way to get SQLite to honor the message in the raise in 
the schema definition?


- Original Message - 
From: "Jim Showalter" <j...@jimandlisa.com>
To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
Sent: Sunday, July 12, 2009 6:10 PM
Subject: Re: [sqlite] Raise is not working


> Nevermind--for whatever reason, Google saw fit to do this:
>
>/**
> * Convenience method for inserting a row into the database.
> *
> * @param table the table to insert the row into
> * @param nullColumnHack SQL doesn't allow inserting a completely
> empty row,
> *so if initialValues is empty this column will
> explicitly be
> *assigned a NULL value
> * @param values this map contains the initial column values for
> the
> *row. The keys should be the column names and the
> values the
> *column values
> * @return the row ID of the newly inserted row, or -1 if an 
> error
> occurred
> */
>public long insert(String table, String nullColumnHack,
> ContentValues values) {
>try {
>return insertWithOnConflict(table, nullColumnHack, 
> values,
> null);
>} catch (SQLException e) {
>Log.e(TAG, "Error inserting " + values, e);
>return -1;
>}
>}
> - Original Message - 
> From: "Jim Showalter" <j...@jimandlisa.com>
> To: "General Discussion of SQLite Database" 
> <sqlite-users@sqlite.org>
> Sent: Sunday, July 12, 2009 6:03 PM
> Subject: [sqlite] Raise is not working
>
>
>> Schema:
>>
>> create table words (_id integer primary key autoincrement, wordtext
>> text not null unique);
>>
>> create table definitions (_id integer primary key autoincrement,
>> owningWordId integer not null unique, deftext text not null);
>>
>> create trigger fki_definitions_words_id before insert on 
>> definitions
>> for each row
>> begin
>>select raise (rollback, 'insert on table definitions violates
>> foreign-key constraint fki_definitions_words_id')
>>where (select _id from words where _id = NEW.owningWordId ) is
>> null;
>>
>> end;
>>
>> Call db.insert, passing it a definition that has the owningWordId
>> set
>> to -1, and the insert returns a -1 instead of throwing.
>>
>> Because it doesn't throw, I don't have the error message "insert on
>> table definitions violates foreign-key constraint
>> fki_definitions_words_id" to work from. Information is simply lost.
>>
>> Why isn't it raising an exception?
>>
>> ___
>> 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 

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

Re: [sqlite] Raise is not working

[Jim Showalter]

Nevermind--for whatever reason, Google saw fit to do this:

/**
 * Convenience method for inserting a row into the database.
 *
 * @param table the table to insert the row into
 * @param nullColumnHack SQL doesn't allow inserting a completely 
empty row,
 *so if initialValues is empty this column will 
explicitly be
 *assigned a NULL value
 * @param values this map contains the initial column values for 
the
 *row. The keys should be the column names and the 
values the
 *column values
 * @return the row ID of the newly inserted row, or -1 if an error 
occurred
 */
public long insert(String table, String nullColumnHack, 
ContentValues values) {
try {
return insertWithOnConflict(table, nullColumnHack, values, 
null);
} catch (SQLException e) {
Log.e(TAG, "Error inserting " + values, e);
return -1;
}
}
- Original Message - 
From: "Jim Showalter" <j...@jimandlisa.com>
To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
Sent: Sunday, July 12, 2009 6:03 PM
Subject: [sqlite] Raise is not working


> Schema:
>
> create table words (_id integer primary key autoincrement, wordtext
> text not null unique);
>
> create table definitions (_id integer primary key autoincrement,
> owningWordId integer not null unique, deftext text not null);
>
> create trigger fki_definitions_words_id before insert on definitions
> for each row
> begin
>select raise (rollback, 'insert on table definitions violates
> foreign-key constraint fki_definitions_words_id')
>where (select _id from words where _id = NEW.owningWordId ) is
> null;
>
> end;
>
> Call db.insert, passing it a definition that has the owningWordId 
> set
> to -1, and the insert returns a -1 instead of throwing.
>
> Because it doesn't throw, I don't have the error message "insert on
> table definitions violates foreign-key constraint
> fki_definitions_words_id" to work from. Information is simply lost.
>
> Why isn't it raising an exception?
>
> ___
> 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] Raise is not working

[Jim Showalter]

Should have included the code that calls:

long id = mDatabase.insert(tableName, null, contentValues);

if (id == -1)
{
throw new AppException(message, ErrorCodes.INSERT_FAILED);
}

The AppException is thrown. But when insert returns -1, that just 
means there was an error. Where is the specific error code?

Is Android swallowing raised exceptions and turning them all into 
just -1?

- Original Message - 
From: "Jim Showalter" <j...@jimandlisa.com>
To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
Sent: Sunday, July 12, 2009 6:03 PM
Subject: [sqlite] Raise is not working


> Schema:
>
> create table words (_id integer primary key autoincrement, wordtext
> text not null unique);
>
> create table definitions (_id integer primary key autoincrement,
> owningWordId integer not null unique, deftext text not null);
>
> create trigger fki_definitions_words_id before insert on definitions
> for each row
> begin
>select raise (rollback, 'insert on table definitions violates
> foreign-key constraint fki_definitions_words_id')
>where (select _id from words where _id = NEW.owningWordId ) is
> null;
>
> end;
>
> Call db.insert, passing it a definition that has the owningWordId 
> set
> to -1, and the insert returns a -1 instead of throwing.
>
> Because it doesn't throw, I don't have the error message "insert on
> table definitions violates foreign-key constraint
> fki_definitions_words_id" to work from. Information is simply lost.
>
> Why isn't it raising an exception?
>
> ___
> 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

[sqlite] Raise is not working

[Jim Showalter]

Schema:

create table words (_id integer primary key autoincrement, wordtext 
text not null unique);

create table definitions (_id integer primary key autoincrement, 
owningWordId integer not null unique, deftext text not null);

create trigger fki_definitions_words_id before insert on definitions
for each row
begin
select raise (rollback, 'insert on table definitions violates 
foreign-key constraint fki_definitions_words_id')
where (select _id from words where _id = NEW.owningWordId ) is 
null;

end;

Call db.insert, passing it a definition that has the owningWordId set 
to -1, and the insert returns a -1 instead of throwing.

Because it doesn't throw, I don't have the error message "insert on 
table definitions violates foreign-key constraint 
fki_definitions_words_id" to work from. Information is simply lost.

Why isn't it raising an exception? 

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