Re: [sqlite] Updating a database by email
Jim Dodgen wrote: On Fri, Jul 10, 2009 at 9:16 PM, Bill Harris bill_har...@facilitatedsystems.com wrote: I have to coordinate a task list between two sites, and my only convenient connection between the two is email. MS Access 2007 offers an email insert and update feature, but it's not quite doing what I need. Is there a way to update / synchronize an sqlite database between two sites successfully using email? I could even see having a copy at both sites and periodically emailing the slave version to the master and then running a merge of some sort, but I'm not sure that would do what I need. Thoughts? Thanks, Bill not beyond the realm of possibilities. I back up via email, a nice protocol. I dump tar gzip and email a small but important database to 6 people each day via a cron job. I don't remember exactly how but on unix boxes I have created user accounts that receive emails automatically and then process them. I think this was done with .forward files. you could also send individual sql statements via email but email doesn't always arrive. that is a major problem. you would need an acknowledgement and retry mechanism. perhaps there's an april fool's RFC for implementing TCP/IP via email :) once you've got that sorted, you'll need to make sure that the updates are digitally signed and probably encrypted and verify the signatures at the recipient's end. and i'm only half joking :-) cheers, raf p.s. i think .forward files only forward all messages. procmail is probably what you need to invoke arbitrary commands to process messages matching your criteria. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] database is locked error using lastest linux kernel
My problem is get a database is locked error using lastest linux kernel
(above 2.6.28) , while the code can run smoothly on linux 2.6.26.2
kernel(vmware pc686 host) and on 2.6.26.3 kernel(arm9 embed system).
The problem code is:
#if 1
if (sqlite3_exec(gJcDb, PRAGMA cache_size = 4000, NULL, NULL, errMsg) !=
SQLITE_OK)
{
fprintf(stderr, !!!cache_size set error, %s\n, errMsg);
sqlite3_free(errMsg);
}
#endif
Even if I comment the the #if 0/1 #endif code block, still can't open a
table and access the table data. I tried sqlite3.6.7 and sqlite3.6.16, the
problem is same.
Three attached files are compilingmaking sqlite3 Makefile, short test
code(code.c) and test database(jc.db).
Any advise from you will be appreciated!
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] database is locked error using lastest linux kernel
My problem is get a database is locked error using lastest linux kernel
(above 2.6.28) , while the code can run smoothly on linux 2.6.26.2
kernel(vmware pc686 host) and on 2.6.26.3 kernel(arm9 embed system).
The problem code is:
#if 1
if (sqlite3_exec(gJcDb, PRAGMA cache_size = 4000, NULL, NULL, errMsg) !=
SQLITE_OK)
{
fprintf(stderr, !!!cache_size set error, %s\n, errMsg);
sqlite3_free(errMsg);
}
#endif
Even if I comment the the #if 0/1 #endif code block, still can't open a
table and access the table data. I tried sqlite3.6.7 and sqlite3.6.16, the
problem is same.
Three attached files are compilingmaking sqlite3 Makefile, short test
code(code.c) and test database(jc.db).
Any advise from you will be appreciated!
inline static int PragmaSetCallback(void * pParam, int pColumnCount, char **
pColumnValue, char ** pColumnName)
{
fprintf(stdout, %s value is %s\n, (char *)pParam, pColumnValue[0]);
return 0;
}
static bool OpenAndInitDb(char * pDbFileName)
{
char * errMsg = NULL;
sqlite3_enable_shared_cache(1);
if (sqlite3_open(pDbFileName, gJcDb) != SQLITE_OK)
{
fprintf(stderr, !!!Open database error: %s\n,
sqlite3_errmsg(gJcDb));
return false;
}
#if 1
if (sqlite3_exec(gJcDb, PRAGMA cache_size = 4000, NULL, NULL,
errMsg) != SQLITE_OK)
{
fprintf(stderr, !!!cache_size set error, %s\n, errMsg);
sqlite3_free(errMsg);
}
#endif
sqlite3_exec(gJcDb, PRAGMA cache_size, PragmaSetCallback, PRAGMA
cache_size, errMsg);
#if 1
if (SQLITE_OK != sqlite3_exec(gJcDb, PRAGMA synchronous = FULL, NULL,
NULL, errMsg)) //OFF FULL NORMAL
{
fprintf(stderr, !!!synchronous set error, %s\n, errMsg);
sqlite3_free(errMsg);
}
#endif
sqlite3_exec(gJcDb, PRAGMA synchronous, PragmaSetCallback, PRAGMA
synchronous, errMsg);
return true;
}
static bool LoadTerminalFromDb(sqlite3 * pDb, Terminal * pTerminal)
{
pTerminal-WorkStateId = 1;
pTerminal-DefaultUpChannelTypeId = ChannelType_UpTnGprsClient;
pTerminal-IsChanged = false;
sqlite3_stmt * stmt = 0;
if (sqlite3_prepare_v2(pDb, select * from Terminal, -1, stmt, 0) !=
SQLITE_OK)
{
return false;
}
if (sqlite3_step(stmt) != SQLITE_ROW)
{
sqlite3_finalize(stmt);
return false;
}
return true;
}
sqlite3 *gJcDb = NULL;
int main(int argc, char *argv[])
{
char * db = ./jc.db;
if (access(db, F_OK) || !OpenAndInitDb(db))
{
fprintf(stderr, !!!Open and init db failed);
return 1;
}
if (!LoadTerminalFromDb(gJcDb, gTerminal))
{
fprintf(stderr, !!!Load Terminal from db failed);
CloseDb(gJcDb);
return 2;
}
return 0;
}#!/usr/make
#
# Makefile for SQLITE
#
# This makefile is suppose to be configured automatically using the
# autoconf. But if that does not work for you, you can configure
# the makefile manually. Just set the parameters below to values that
# work well for your system.
#
# If the configure script does not work out-of-the-box, you might
# be able to get it to work by giving it some hints. See the comment
# at the beginning of configure.in for additional information.
#
# The toplevel directory of the source tree. This is the directory
# that contains this Makefile.in and the configure.in script.
#
TOP = .
# C Compiler and options for use in building executables that
# will run on the platform that is doing the build.
#
BCC = gcc -g
# C Compile and options for use in building executables that
# will run on the target platform. (BCC and TCC are usually the
# same unless your are cross-compiling.)
#
TCC = arm-9tdmi-linux-gnueabi-gcc -g -O2 -DSQLITE_OS_UNIX=1 -I. -I${TOP}/src
# Define this for the autoconf-based build, so that the code knows it can
# include the generated config.h
#
TCC += -D_HAVE_SQLITE_CONFIG_H
# Define -DNDEBUG to compile without debugging (i.e., for production usage)
# Omitting the define will cause extra debugging code to be inserted and
# includes extra comments when EXPLAIN stmt is used.
#
TCC += -DNDEBUG -DSQLITE_ALLOW_XTHREAD_CONNECT=1
# Compiler options needed for programs that use the TCL library.
#
TCC +=
# The library that programs using TCL must link against.
#
LIBTCL =
# Compiler options needed for programs that use the readline() library.
#
READLINE_FLAGS = -DHAVE_READLINE=0
# The library that programs using readline() must link against.
#
LIBREADLINE =
# Should the database engine be compiled threadsafe
#
TCC += -DSQLITE_THREADSAFE=1
# Do threads override each others locks by default (1), or do we test (-1)
#
TCC += -DSQLITE_THREAD_OVERRIDE_LOCK=-1
# Any target libraries which libsqlite must be linked against
#
TLIBS = -lpthread
# Flags controlling use of the in memory btree implementation
#
# SQLITE_TEMP_STORE is 0 to
[sqlite] How-to change column limit?
Dear all, Sorry ahead for a really trivial question, but I'm not very familiar with sqlite and just can't figure it out ... I'm importing large survey files into sqlite, and I run into the problem of creating a table with too many columns, i.e. bash$ sqlite3 tables.sql produces bash$ SQL error near line 3: too many columns on t3 (where tables.sql looks like this: create table t1(var1,var2,...); create t2(var1, var2, ...); ) Now I figured that I have to change the limit on columns -- see http://www.sqlite.org/c3ref/c_limit_attached.html but I don't know how! I tred all kinds of variations of int sqlite3_limit(sqlite3*, SQLITE_LIMIT_COLUMN, 5000) as for instance sqlite int sqlite3_limit(sqlite3*, SQLITE_LIMIT_COLUMN, 5000); all without success. Thanks in advance fr the help -- you'll do me a big favor. Stephan -- --- Stephan Lindner University of Michigan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Updating a database by email
On 11 Jul 2009, at 8:03am, raf wrote: you could also send individual sql statements via email but email doesn't always arrive. that is a major problem. you would need an acknowledgement and retry mechanism. perhaps there's an april fool's RFC for implementing TCP/IP via email :) once you've got that sorted, you'll need to make sure that the updates are digitally signed and probably encrypted and verify the signatures at the recipient's end. and i'm only half joking :-) Well of course you're joking. It's blatantly obvious that the correct transport protocol for SQL messages would be an RSS feed. Although come to think of it, you could Tweet them. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How-to change column limit?
On 12/07/2009 10:23 PM, Stephan Lindner wrote: I'm importing large survey files into sqlite, and I run into the problem of creating a table with too many columns, i.e. How many columns do you have? bash$ sqlite3 tables.sql produces bash$ SQL error near line 3: too many columns on t3 (where tables.sql looks like this: create table t1(var1,var2,...); create t2(var1, var2, ...); ) Now I figured that I have to change the limit on columns -- see http://www.sqlite.org/c3ref/c_limit_attached.html but I don't know how! I tred all kinds of variations of int sqlite3_limit(sqlite3*, SQLITE_LIMIT_COLUMN, 5000) as for instance sqlite int sqlite3_limit(sqlite3*, SQLITE_LIMIT_COLUMN, 5000); Huh? sqlite indicates the command-line executable program ... but int sqlite3_limit() is C code all without success. http://www.sqlite.org/limits.html Summary: Default is 2000. You can change the default max at COMPILE time, up to 32767. You can REDUCE the maximum at RUN time using sqlite3_limit(). If, as it appears, you have more than 2000 columns, you might like to consider a bit of normalisation of your schema. Please note carefully the remarks about O(N**2) in the docs. HTH, John ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Updating a database by email
what about to use a unique identifier for each row?but a unique identifier like UUID in sqlserver or Quuid in Qt? as it is unique over the world it could be enough? at the worst case, prefix an id by the name of your machine if you have only 2 machines. then merge the records together? cheers, Sylvain On Sun, Jul 12, 2009 at 2:56 PM, Simon Slavin slav...@hearsay.demon.co.ukwrote: On 11 Jul 2009, at 8:03am, raf wrote: you could also send individual sql statements via email but email doesn't always arrive. that is a major problem. you would need an acknowledgement and retry mechanism. perhaps there's an april fool's RFC for implementing TCP/IP via email :) once you've got that sorted, you'll need to make sure that the updates are digitally signed and probably encrypted and verify the signatures at the recipient's end. and i'm only half joking :-) Well of course you're joking. It's blatantly obvious that the correct transport protocol for SQL messages would be an RSS feed. Although come to think of it, you could Tweet them. 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] Updating a database by email
... sorry, I forgot to mention to have an updatedBy column also having the computer name as a distinct valuefor knowing who updated the last one (and eventually an updatedTime to know when) Cheers, Sylvain On Sun, Jul 12, 2009 at 5:06 PM, Sylvain Pointeau sylvain.point...@gmail.com wrote: what about to use a unique identifier for each row?but a unique identifier like UUID in sqlserver or Quuid in Qt? as it is unique over the world it could be enough? at the worst case, prefix an id by the name of your machine if you have only 2 machines. then merge the records together? cheers, Sylvain On Sun, Jul 12, 2009 at 2:56 PM, Simon Slavin slav...@hearsay.demon.co.uk wrote: On 11 Jul 2009, at 8:03am, raf wrote: you could also send individual sql statements via email but email doesn't always arrive. that is a major problem. you would need an acknowledgement and retry mechanism. perhaps there's an april fool's RFC for implementing TCP/IP via email :) once you've got that sorted, you'll need to make sure that the updates are digitally signed and probably encrypted and verify the signatures at the recipient's end. and i'm only half joking :-) Well of course you're joking. It's blatantly obvious that the correct transport protocol for SQL messages would be an RSS feed. Although come to think of it, you could Tweet them. 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] Updating a database by email
We use a system of updating an Sqlite database buit use port 80 to send data in XML format. Is there a reason you have access to the email port and not the HTTP port 80? It is very complex to identify a missing email but using port 80 and HTTP the process is almost trivial Our system maintains sync'd databases and compensates for network outages quite robustly. Remote work sites can continue to operate when the communication link is down and thus deliver 24/7 availability.. Sylvain Pointeau wrote: what about to use a unique identifier for each row?but a unique identifier like UUID in sqlserver or Quuid in Qt? as it is unique over the world it could be enough? at the worst case, prefix an id by the name of your machine if you have only 2 machines. then merge the records together? cheers, Sylvain On Sun, Jul 12, 2009 at 2:56 PM, Simon Slavin slav...@hearsay.demon.co.ukwrote: On 11 Jul 2009, at 8:03am, raf wrote: you could also send individual sql statements via email but email doesn't always arrive. that is a major problem. you would need an acknowledgement and retry mechanism. perhaps there's an april fool's RFC for implementing TCP/IP via email :) once you've got that sorted, you'll need to make sure that the updates are digitally signed and probably encrypted and verify the signatures at the recipient's end. and i'm only half joking :-) Well of course you're joking. It's blatantly obvious that the correct transport protocol for SQL messages would be an RSS feed. Although come to think of it, you could Tweet them. 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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How-to change column limit?
On Sun, Jul 12, 2009 at 08:23:35AM -0400, Stephan Lindner scratched on the wall: Dear all, Sorry ahead for a really trivial question, but I'm not very familiar with sqlite and just can't figure it out ... I'm importing large survey files into sqlite, and I run into the problem of creating a table with too many columns, i.e. bash$ sqlite3 tables.sql produces bash$ SQL error near line 3: too many columns on t3 You need to re-compile SQLite with a larger limit. The limit is defined by the compile-time define SQLITE_MAX_COLUMNs. The default is 2000. You need to seriously consider your database design if you're going over this. http://sqlite.org/limits.html#max_column http://sqlite.org/compile.html Now I figured that I have to change the limit on columns -- see http://www.sqlite.org/c3ref/c_limit_attached.html but I don't know how! I tred all kinds of variations of int sqlite3_limit(sqlite3*, SQLITE_LIMIT_COLUMN, 5000) That's a C programming call. And you can only use it to lower the limit, not raise it. By default the soft-limit is set to the hard-limit. -j -- Jay A. Kreibich J A Y @ K R E I B I.C H Our opponent is an alien starship packed with atomic bombs. We have a protractor. I'll go home and see if I can scrounge up a ruler and a piece of string. --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Updating a database by email
We use a system of updating an Sqlite database buit use port 80 to send data in XML format. Is there a reason you have access to the email port and not the HTTP port 80? It is very complex to identify a missing email but using port 80 and HTTP the process is almost trivial Our system maintains sync'd databases and compensates for network outages quite robustly. Remote work sites can continue to operate when the communication link is down and thus deliver 24/7 availability.. Could you describe this in more detail? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Number truncation problem in SQLite 3
create table words (_id integer primary key autoincrement, wordtext
text not null unique, timestamp integer not null);
public class Word
{
long _id;
String wordtext;
long timestamp;
}
timestamp:
before save: 1247435151517
after save : 1247435160847
64-bit max is: 9223372036854775807, so it should fit.
What am I doing wrong?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Number truncation problem in SQLite 3
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Jim Showalter wrote:
create table words (_id integer primary key autoincrement, wordtext
text not null unique, timestamp integer not null);
public class Word
{
long _id;
String wordtext;
long timestamp;
}
timestamp:
before save: 1247435151517
after save : 1247435160847
64-bit max is: 9223372036854775807, so it should fit.
http://catb.org/esr/faqs/smart-questions.html
Your mailer headers show that you are using Windows. On Windows
(including a 64 bit environment) long is 32 bits.
http://blogs.msdn.com/oldnewthing/archive/2005/01/31/363790.aspx
Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
iEYEARECAAYFAkpaZl0ACgkQmOOfHg372QR3OQCeJN8iq1kfjjM5Lel8qGToQ06j
/ocAoLdOsq3gVaf5mNAiWanqfQN6dxSe
=9JUC
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Number truncation problem in SQLite 3
On 13/07/2009 8:40 AM, Roger Binns wrote:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Jim Showalter wrote:
create table words (_id integer primary key autoincrement, wordtext
text not null unique, timestamp integer not null);
public class Word
{
long _id;
String wordtext;
long timestamp;
}
timestamp:
before save: 1247435151517
after save : 1247435160847
The after number is greater than the before number. That's
truncation? Looks like some more information is required e.g. a small
piece of code (preferably runnable) that shows what exactly was done,
with debug prints in appropriate places.
64-bit max is: 9223372036854775807, so it should fit.
http://catb.org/esr/faqs/smart-questions.html
Your mailer headers show that you are using Windows. On Windows
(including a 64 bit environment) long is 32 bits.
Ummm, each of the quoted numbers takes up 41 bits (unsigned).
Cheers,
John
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Number truncation problem in SQLite 3
I have found the problem. There was an incrementing trigger left in
the database accidentally.
- Original Message -
From: John Machin sjmac...@lexicon.net
To: General Discussion of SQLite Database sqlite-users@sqlite.org
Sent: Sunday, July 12, 2009 4:25 PM
Subject: Re: [sqlite] Number truncation problem in SQLite 3
On 13/07/2009 8:40 AM, Roger Binns wrote:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Jim Showalter wrote:
create table words (_id integer primary key autoincrement,
wordtext
text not null unique, timestamp integer not null);
public class Word
{
long _id;
String wordtext;
long timestamp;
}
timestamp:
before save: 1247435151517
after save : 1247435160847
The after number is greater than the before number. That's
truncation? Looks like some more information is required e.g. a
small
piece of code (preferably runnable) that shows what exactly was
done,
with debug prints in appropriate places.
64-bit max is: 9223372036854775807, so it should fit.
http://catb.org/esr/faqs/smart-questions.html
Your mailer headers show that you are using Windows. On Windows
(including a 64 bit environment) long is 32 bits.
Ummm, each of the quoted numbers takes up 41 bits (unsigned).
Cheers,
John
___
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
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
Re: [sqlite] Raise is not working
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
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
Re: [sqlite] Raise is not working
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
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
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
