[sqlite] What does SQLITE_IOERR_TRUNCATE mean, exactly?

[John Belli]

I have a broken database file, with a journal. It is opened read-only
(via sqlite3_open_v2()). The statement "PRAGMA user version" is
prepared successfully. When sqlite3_step() is called, the return code
is SQLITE_IOERR. The extended code is SQLITE_IOERR_TRUNCATE. What
exactly does this mean?


JAB
-- 
John A. Belli
Software Engineer
Refrigerated Transport Electronics, Inc.
http://www.rtelectronics.com

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

[sqlite] Multithreaded vs. serialized

[John Belli]

Assuming I've decided to use evil threads, and am opening a new
connection in each thread, does it matter whether I use multithreaded
(-DSQLITE_THREADSAFE=2) or serialized (-DSQLITE_THREADSAFE=1)? Any
idea if one is faster than the other? I am using SQLite on Win32 and
WinCE, and I'll be using shared caching
(sqlite3_enable_shared_cache(TRUE)).


JAB
-- 
John A. Belli
Software Engineer
Refrigerated Transport Electronics, Inc.
http://www.rtelectronics.com

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

Re: [sqlite] Compiling with mingw32/msys on Windows

[John Belli]

I got the CVS version and it mostly works, but I'm hitting a bug in
mingw32 gcc now (which, after some investigation, may have been fixed
in a later version than the current release). At least I can take the
processed amalgamation and use it in a VS2k5 project.

Note: SQLITE_ENABLE_IOTRACE doesn't appear to work either, unless I
need to pass it to configure as well.

[EMAIL PROTECTED] ~/bld $ make OPTS="-DSQLITE_THREADSAFE=1
-DSQLITE_ENABLE_COLUMN_METADATA -DSQLITE_ENABLE_FTS3
-DSQLITE_ENABLE_MEMORY_MANAGEMENT -DSQLITE_ENABLE_RTREE
-DSQLITE_DEFAULT_FILE_FORMAT=4 -DSQLITE_ENABLE_UPDATE_DELETE_LIMIT"
   ...
libtool: compile:  gcc -DSQLITE_OS_WIN=1 -I. -I../sqlite/src
-D_HAVE_SQLITE_CONFIG_H -DNDEBUG -IC:/Tcl/include
-DSQLITE_THREADSAFE=1 -DSQLITE_THREAD_OVERRIDE_LOCK=-1
-DSQLITE_ENABLE_UPDATE_DELETE_LIMIT -DSQLITE_THREADSAFE=1
-DSQLITE_ENABLE_COLUMN_METADATA -DSQLITE_ENABLE_FTS3
-DSQLITE_ENABLE_MEMORY_MANAGEMENT -DSQLITE_ENABLE_RTREE
-DSQLITE_DEFAULT_FILE_FORMAT=4 -DSQLITE_ENABLE_UPDATE_DELETE_LIMIT
-DUSE_TCL_STUBS=1 -c ../sqlite/src/tclsqlite.c  -DDLL_EXPORT -DPIC -o
.libs/tclsqlite.o
../sqlite/src/tclsqlite.c: In function `Sqlite3_Init':
../sqlite/src/tclsqlite.c:2524: internal compiler error: in
rest_of_handle_final, at toplev.c:2067
Please submit a full bug report,
with preprocessed source if appropriate.
See  for instructions.
make: *** [tclsqlite.lo] Error 1

JAB
-- 
John A. Belli
Software Engineer
Refrigerated Transport Electronics, Inc.
http://www.rtelectronics.com

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

Re: [sqlite] Compiling with mingw32/msys on Windows

[John Belli]

On Wed, 22 Oct 2008 14:34:04 -0400, "Shane Harrelson"
<[EMAIL PROTECTED]> wrote:

>I was able to duplicate the issue, and I've updated the configure
>scripts to correct the problem.

OK, great. Is the tarball updated, or do I need to get it from CVS?

>If you are generating your makefile using configure, you can pass it
>options like this:
>
>   configure CFLAGS=-DSQLITE_ENABLE_UPDATE_DELETE_LIMIT=1
>
>This will generate a makefile which passes the
>SQLITE_ENABLE_UPDATE_DELETE_LIMIT option to the build components.
>If you already have a makefile generated by configure, you should be
>able to add custom compile options like this:
>
>   make OPTS=-DSQLITE_ENABLE_UPDATE_DELETE_LIMIT=1
>
>or for multiple options:
>
>   make "OPTS=-DSQLITE_ENABLE_UPDATE_DELETE_LIMIT=1 -DSQLITE_OMIT_TRIGGER=1"

I thought you used OPTS with configure, too? At least, I did use it,
as shown upthread.

Thanks for looking into this.


JAB
-- 
John A. Belli
Software Engineer
Refrigerated Transport Electronics, Inc.
http://www.rtelectronics.com

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

Re: [sqlite] Transaction across different DB handles returns SQLITE_INTERNAL

[John Belli]

On Mon, 20 Oct 2008 18:01:03 + (UTC), MikeW
<[EMAIL PROTECTED]> wrote:


>Still, I would like to be able to run the Transaction over these two calls ...
>What's the best way, given I have two separate handles ?
>Make them separate Transactions and do a ROLLBACK on the first if the
>second exec fails, possibly ?

Tht might work. You could also look into ATTACH DATABASE syntax (the
proper way to maintain atomicity between databases)
http://www.sqlite.org/lang_attach.html

Attach the 2nd database before beginning the transaction.

JAB
-- 
John A. Belli
Software Engineer
Refrigerated Transport Electronics, Inc.
http://www.rtelectronics.com

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

Re: [sqlite] Can you perform an ATTACH-type operation just using DB handles ?

[John Belli]

On Fri, 17 Oct 2008 07:53:52 + (UTC), MikeW
<[EMAIL PROTECTED]> wrote:

>John Belli <[EMAIL PROTECTED]> writes:
>> 
>> On Thu, 16 Oct 2008 17:54:52 + (UTC), MikeW
>> <[EMAIL PROTECTED]> wrote:
>> 
>> >My code has two pre-opened DB connection handles, and would like to
>> >perform an SQL operation over the two databases.
>> >The DB filenames are not available at this point, hence an ATTACH is
>> >not feasible.
>> >Is there any way to achieve this by applying an ATTACH at the handle level ?
>> 
>> I don't think so, but check into "PRAGMA database_list" (See
>> http://www.sqlite.org/pragma.html ).
>> 
>> JAB
>
>Thanks, that might be useful in some situations but not in this one.
>
>The use case is a very specific one in which a database may contain one-off
>overrides for a second, normally independent (but already opened) database.
>I can obviously implement this by reading rows from the first one
>and updating the second, but if I could briefly refer to both DBs at
>the same time, it would be just one 'atomic' SQL statement -
>much simpler and more robust.
>
>Still, it seems surprising that there isn't a (SQLite-specific) handle-level
>operation to do this attach, maybe I should put in a feature request.
>(Or even have a look in the SQLite code to see if it's an easy mod ...)

GIVEN: db1 is the open handle of the first database, and db2 is the
open handle of the second database

WANTED: attach database open as db1 to db2 to do an atomic update
 * * * WARNING: untested code ahead! * * *
char **table;
sqlite3_get_table(db1, "PRAGMA database_list", , NULL, NULL,
  NULL);
char *tmp = sqlite3_mprintf("ATTACH %q AS a", table[5]);
sqlite3_free_table(table);
sqlite3_exec(db2, tmp, NULL, NULL, NULL);
sqlite3_free(tmp);
   ...do your stuff here...
sqlite3_exec(db2, "DETACH a", NULL, NULL, NULL);

There should be a bunch of error checking in there. This is left as an
exercise for the reader.

This will not work if db1 is a memory db. In that case, you would have
to do it the other way around. If both are memory dbs, then you're out
of luck.


JAB
-- 
John A. Belli
Software Engineer
Refrigerated Transport Electronics, Inc.
http://www.rtelectronics.com

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

[sqlite] Compiling with mingw32/msys on Windows

[John Belli]

OK, I should have all the required utils, and configure apparently
worked properly, but make barfs (log below):
MinGW-5.1.4
MSYS-1.0.10
ActiveTcl8.5.4.0.286921-win32-ix86-threaded (after install, linked
bin/tclsh85.exe to tclsh.exe)

Any idea?


JAB
-- 
John A. Belli
Software Engineer
Refrigerated Transport Electronics, Inc.
http://www.rtelectronics.com


[EMAIL PROTECTED] ~/bld $ ../sqlite/configure --with-tcl=/c/Tcl/lib
--enable-load-extension OPTS="-DSQLITE_ENABLE_IOTRACE
-DSQLITE_THREADSAFE=1
 -DSQLITE_ENABLE_COLUMN_METADATA -DSQLITE_ENABLE_FTS3
-DSQLITE_ENABLE_MEMORY_MANAGEMENT -DSQLITE_ENABLE_RTREE -DSQLITE_
DEFAULT_FILE_FORMAT=4 -DSQLITE_ENABLE_UPDATE_DELETE_LIMIT"

checking build system type... i686-pc-mingw32
checking host system type... i686-pc-mingw32
checking for gcc... gcc
checking for C compiler default output file name... a.exe
checking whether the C compiler works... yes
checking whether we are cross compiling... no
checking for suffix of executables... .exe
checking for suffix of object files... o
checking whether we are using the GNU C compiler... yes
checking whether gcc accepts -g... yes
checking for gcc option to accept ANSI C... none needed
checking for a sed that does not truncate output... /bin/sed
checking for egrep... grep -E
checking for fgrep... grep -F
checking for ld used by gcc... c:/MinGW/mingw32/bin/ld.exe
checking if the linker (c:/MinGW/mingw32/bin/ld.exe) is GNU ld... yes
checking for BSD- or MS-compatible name lister (nm)... /mingw/bin/nm
checking the name lister (/mingw/bin/nm) interface... BSD nm
checking whether ln -s works... yes
checking the maximum length of command line arguments... 8192
checking whether the shell understands some XSI constructs... yes
checking whether the shell understands "+="... no
checking for c:/MinGW/mingw32/bin/ld.exe option to reload object
files... -r
checking how to recognize dependent libraries... file_magic file
format pei*-i386(.*architecture: i386)?
checking for ar... ar
checking for strip... strip
checking for ranlib... ranlib
checking command to parse /mingw/bin/nm output from gcc object... ok
checking how to run the C preprocessor... gcc -E
checking for ANSI C header files... yes
checking for sys/types.h... yes
checking for sys/stat.h... yes
checking for stdlib.h... yes
checking for string.h... yes
checking for memory.h... yes
checking for strings.h... yes
checking for inttypes.h... yes
checking for stdint.h... yes
checking for unistd.h... yes
checking for dlfcn.h... no
checking for objdir... .libs
checking if gcc supports -fno-rtti -fno-exceptions... no
checking for gcc option to produce PIC... -DDLL_EXPORT -DPIC
checking if gcc PIC flag -DDLL_EXPORT -DPIC works... yes
checking if gcc static flag -static works... yes
checking if gcc supports -c -o file.o... yes
checking if gcc supports -c -o file.o... (cached) yes
checking whether the gcc linker (c:/MinGW/mingw32/bin/ld.exe) supports
shared libraries... yes
checking whether -lc should be explicitly linked in... yes
checking dynamic linker characteristics... Win32 ld.exe
checking how to hardcode library paths into programs... immediate
checking whether stripping libraries is possible... yes
checking if libtool supports shared libraries... yes
checking whether to build shared libraries... yes
checking whether to build static libraries... yes
checking for a BSD-compatible install... /bin/install -c
checking for gawk... gawk
checking for special C compiler options needed for large files... no
checking for _FILE_OFFSET_BITS value needed for large files... no
checking for _LARGE_FILES value needed for large files... no
checking for int8_t... yes
checking for int16_t... yes
checking for int32_t... yes
checking for int64_t... yes
checking for intptr_t... yes
checking for uint8_t... yes
checking for uint16_t... yes
checking for uint32_t... yes
checking for uint64_t... yes
checking for uintptr_t... yes
checking for sys/types.h... (cached) yes
checking for stdlib.h... (cached) yes
checking for stdint.h... (cached) yes
checking for inttypes.h... (cached) yes
checking for usleep... no
checking for fdatasync... no
checking for localtime_r... no
checking for gmtime_r... no
checking for localtime_s... no
checking for tclsh8.4... no
checking for tclsh... tclsh
Version set to 3.6
Release set to 3.6.4
Version number set to 3006004
checking whether to support threadsafe operation... yes
checking for library containing pthread_create... no
checking whether to allow connections to be shared across threads...
no
checking whether threads can override each others locks... no
checking whether to support shared library linked as release mode or
not... no
checking whether to use an in-ram database for temporary tables... no
checking if executables have the .exe suffix... unknown
checking for Tcl configuration... found /c/Tcl/lib/tclConfig.sh
checking for existence of /c/Tcl/lib/tclConfig.sh... loading
checking for library containing tgetent... no
checking for readline in -lreadline... no
checking 

Re: [sqlite] Can you perform an ATTACH-type operation just using DB handles ?

[John Belli]

On Thu, 16 Oct 2008 17:54:52 + (UTC), MikeW
<[EMAIL PROTECTED]> wrote:

>My code has two pre-opened DB connection handles, and would like to
>perform an SQL operation over the two databases.
>The DB filenames are not available at this point, hence an ATTACH is
>not feasible.
>Is there any way to achieve this by applying an ATTACH at the handle level ?

I don't think so, but check into "PRAGMA database_list" (See
http://www.sqlite.org/pragma.html ).


JAB
-- 
John A. Belli
Software Engineer
Refrigerated Transport Electronics, Inc.
http://www.rtelectronics.com

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

Re: [sqlite] Foreign Key Triggers: ABORT, FAIL, or ROLLBACK

[John Belli]

On Tue, 14 Oct 2008 22:10:12 +0700, Dan
<[EMAIL PROTECTED]> wrote:

>ABORT seems right to me. Causes the current statement to have no
>effect, but does not rollback the current transaction.

On Tue, 14 Oct 2008 11:16:17 -0500, Stephen Woodbridge
<[EMAIL PROTECTED]> wrote:

>I'm not sure there is a "standard" answer. The "right" answer probably 
>depends on the needs of the application and whether it is robsut enough 
>to deal with various situations that might occur that would trigger a 
>conflict.
>
>For example:
>
>1) under what conditions might a conflict occur?
>2) does the application code doing the insert have a recovery if it fails.
>3) is the application communicating back to a real user that can decide 
>how to handle the situation?
>4) is this an automated process that needs to work or fail leaving the 
>DB unchanged on failure?

After considering both of your inputs, I have changed the triggers to
"ABORT." I always check my returns and issue a ROLLBACK on errors, so
this is really the best choice.


JAB

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

Re: [sqlite] Error:"Expression cannot be evaluated" withsqlite3_exec()

[John Belli]

On Thu, 16 Oct 2008 08:23:10 + (GMT), Alberto Tellaeche
<[EMAIL PROTECTED]> wrote:

>int ActualizarBBDDAlumnos(char *dni, float nota)
>{
>char *errorMsg;
>
>char *orden_SQL = sqlite3_mprintf("update ALUMNO set nota=%0.1f where 
> dni=%Q;",nota,dni);
>sqlite3_exec(db,orden_SQL,0,0,);
>sqlite3_free(errorMsg);
>sqlite3_free(orden_SQL);
>return(0);
>}

"db" appears to be a global. Are you certain that is has been properly
initialized with sqlite3_open(), sqlite3_open16() or sqlite3_open_v2()
before calling this function?


JAB

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

Re: [sqlite] Problem with inserting and integer primary key

[John Belli]

On Wed, 15 Oct 2008 12:43:00 -0400, "Igor Tandetnik"
<[EMAIL PROTECTED]> wrote:

>Be aware that, in order to create a column that aliases ROWID (and thus 
>has special properties in SQLite, such as getting an automatically 
>assigned unique integer) it has to be spelled precisely INTEGER PRIMARY 
>KEY.

Note that INT PRIMARY KEY is not sufficient, which may be useful.


JAB
-- 
John A. Belli
Software Engineer
Refrigerated Transport Electronics, Inc.
http://www.rtelectronics.com

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

[sqlite] Compiling with mingw32/msys, and using options

[John Belli]

Perhaps I just don't know how to use configure and make, but I can't
figure out how to use, for example, SQLITE_ENABLE_UPDATE_DELETE_LIMIT
when compiling with msys. Do I add it somehow when calling configure,
when calling make, or do I edit the Makefile after configure? I am
having no problem getting "sqlite.exe" out by following the README, or
with options that show up in "configure --help".

Also, is tcl required to produce an amalgamation? All this started
when I found the amalgamation does not support the DELETE ... LIMIT
syntax. If so, can I use ActiveState tcl or should I build from
source?


JAB

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

[sqlite] Foreign Key Triggers: ABORT, FAIL, or ROLLBACK

[John Belli]

What is the recommended conflict to raise during an FK enforcement
trigger? I'm not asking about how to create the triggers, I've figured
that part out; I just want to know which conflict should be used. I
think I'm asking, which do other db systems tend to use?


JAB
-- 
John A. Belli
Software Engineer
Refrigerated Transport Electronics, Inc.
http://www.rtelectronics.com

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

[sqlite] Re: progress handler code changed 3.4.0

[John Belli]

On Mon, 25 Jun 2007 18:38:30 +, D. Richard Hipp wrote:

>"John Belli" <[EMAIL PROTECTED]> wrote:
>> If the progress handler returns non-zero, sqlite_exec is returning 
>> SQLITE_INTERRUPT instead of SQLITE_ABORT. Was this intentional? The 
>> comment says SQLITE_ABORT, but the return is being set to 
>> SQLITE_INTERRUPT.
>> (vdbe.c line 553)
>>  
>
>This change is part of the fix for the corruption bug in ticket #2409.  
>We didn't think anybody would notice.  Is it causing problems for you?

Hah, no, not really. I just changed my code to look for either return
value, so I can display the proper message. I was testing the return
from a sqlite3_exec() for an error message display, and displaying a
"canceled" message if it was SQLITE_ABORT.


JAB
-- 
John A. Belli
Software Engineer
Refrigerated Transport Electronics, Inc.
http://www.rtelectronics.com

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] progress handler code changed 3.4.0

[John Belli]

If the progress handler returns non-zero, sqlite_exec is returning
SQLITE_INTERRUPT instead of SQLITE_ABORT. Was this intentional? The
comment says SQLITE_ABORT, but the return is being set to
SQLITE_INTERRUPT.
(vdbe.c line 553)
 

JAB