[sqlite] Bug in Sqlite3 and parameters
I am using a parametric sql statement, with parameters of the form :Name The following routines give an access violation, when called via the windows dll: sqlite3_bind_parameter_name sqlite3_bind_parameter_index The access violation indicates that a NULL pointer is being accessed. A brief look at the code suggests that some problem with the azVar initialization must be the reason, but I do not know enough C to fix it. environment: Windows 2000, Delphi 7 calling sqlite3.dll. can you pls do something about it, quickly, as I cannot continue my project. thanks.
[sqlite] Compress/uncompress inline
Hello all, Is it possible to have some sort of a zip-unzip (compress-uncompress) function built-in when writing/reading the SQLite v3 databases, like the encode()/decode() functions were in v2? Has anyone implemented this? Is it available, for example, like the encryption at a charge? If nobody implemented this, could you please point out to me what should be modified to add compress-uncompress code, i.e. at which stage should this be done? Thanks in advance, Dennis // MCP, MCSD // Software for animal shelters // http://www.smartpethealth.com
[sqlite] Need Type info with Views
Hello DRH. This is further to the previous query regarding no type information being returned with Views. I had previously asked if this could be added, but have not received any comment. I'm sure anyone would agree that getting the column type data type back with views is no less important than getting it back with SELECTs. Why would it be? I need type info for any row-returning SQL command, be if from directly via SELECTs or indirectly from Views. Until this is fixed, I must tell my 3.0 customers they can't use Views :-( regards -brett
Re: [sqlite] ON INSERT trigger
> But I think the gist of you email is that you'd prefer the "INSERT OR > REPLACE" to be handled like an update. I can't see a reason why an OLD > record could be created for replace inserts. The problem then becomes how > do you know in the trigger whether OLD is valid? If the insert is a > straight insert, then OLD will not be valid, and you'll probably get an > exception trying to use it. Well, I'd prefer the 'ON DELETE' trigger to be called every time a deletion occures regardless of whether it's caused by a "DELETE" or "REPLACE" statement. > In which case it might be better to convert the replace insert into an > update, and use the update trigger which is guaranteed to have valid OLD > and NEW. I'm afraid, it's impossible. The actual SQL code will be written by different persons, and they must be able to use all the SQL features supported in SQLite. Probably, I should provide some more info on the project I'm working on. There exists a legacy db which does not support SQL syntax at all. The goal of the project is to allow SQL management for this db. SQLite was chosen as the SQL interface provider, but the problem is that the old db contains quite big blob objects (up to 25 MB and more) which cannot be simply copied into a SQLite table. The obvious solution is to copy not the blob itself, but only a reference to it (this is what the SecDbKey field actually is). In this case any changes in an SQLite table must be properly reflected in the old db. What is worse, the problem of maintaining the coherency is highly desirable to be solved in the most common manner, since there is no way for me to predict the structure of SQLite tables ever created or SQL queries ever constructed. Triggers would be the right solution, but they don't seem to be accurate enough (at least, the INSERT one). Still thinking what to do... Thank you guys for all of your suggestions. I'll see what I can do about it. Regards, Alex
Re: [sqlite] ON INSERT trigger
> CREATE TABLE table (ID text UNIQUE, Name text, Surname text, SecDbKey text)
> INSERT INTO table VALUES ('001', 'John', 'Smith', 'A0B1C2D3')
> INSERT OR REPLACE INTO table VALUES ('001', 'Jane', 'Parker', 'E4F51234')
I'm not sure exactly what relationship you're trying to maintain
between this table and the other one, but assuming that SecDbKey
is a foreign key that references table2 and that you have no
duplicate foreign keys (SecDbKey should probably be declared
unique if that's the case), then you can execute
delete from table2 where table2.DbKey not in
(select SecDbKey from table)
after you do your , or after you do several
of them. You can put it in a trigger if you don't want to do
it explicitly.
BTW, you might want to review your db design. Usually you
delete foreign keys when the referenced primary key disappears;
you seem to be doing the opposite.
Regards
Re: [sqlite] ON INSERT trigger
On Tue, September 21, 2004 11:26, Christian Smith said: > On Tue, 21 Sep 2004, Alex wrote: > > In which case it might be better to convert the replace insert into an > update, and use the update trigger which is guaranteed to have valid OLD > and NEW. > > But that is not my decision to make. Create a new enhancement ticket in > CVSTrac. Also see ticket #368, which I reported on a similar issue. -- My GPG public key is at http://ronware.org/ fingerprint: 8130 734C 69A3 6542 0853 CB42 3ECF 9259 AD29 415D
Re: [sqlite] ON INSERT trigger
On Tue, 21 Sep 2004, Alex wrote:
>Hi, Christian
>
>Thanks for your response. The situation, however, is more complicated.
>Please, consider an example in SQL:
>
>CREATE TABLE table (ID text UNIQUE, Name text, Surname text, SecDbKey text)
>
>INSERT INTO table VALUES ('001', 'John', 'Smith', 'A0B1C2D3')
>
>INSERT OR REPLACE INTO table VALUES ('001', 'Jane', 'Parker', 'E4F51234')
>
>In the given example the "UNIQUE" constraint will cause the row to be
>overwritten, and the "A0B1C2D3" key will be lost. And it seems there is no
>way to intercept this deletion. The ROWID will also be changed, so it cannot
>be used for tracking purposes. If anyone knows a solution, please, advice.
What would you be planning to do with this "SecDbKey" column? There's
nothing stopping you retrieving the current value from table.
But I think the gist of you email is that you'd prefer the "INSERT OR
REPLACE" to be handled like an update. I can't see a reason why an OLD
record could be created for replace inserts. The problem then becomes how
do you know in the trigger whether OLD is valid? If the insert is a
straight insert, then OLD will not be valid, and you'll probably get an
exception trying to use it.
In which case it might be better to convert the replace insert into an
update, and use the update trigger which is guaranteed to have valid OLD
and NEW.
But that is not my decision to make. Create a new enhancement ticket in
CVSTrac.
>
>Thanks,
>Alex
>
>
>> If there is an "OR REPLACE" clause on your primary key, then you know
>> exactly which row to delete from the secondary db. It will be the one with
>> the same primary key as the new row. So, for each insert, just delete the
>> row from the secondary db with the same key. If it doesn't exist, you'll
>> have lost nothing (a bit of time, perhaps, as you'll have to do a negative
>> index scan.) Then just insert the data as you would normally do.
>>
>>
>>
>> Christian
>
>
>
--
/"\
\ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
X - AGAINST MS ATTACHMENTS
/ \
Re: [sqlite] ON INSERT trigger
"Alex" <[EMAIL PROTECTED]> writes:
> CREATE TABLE table (ID text UNIQUE, Name text, Surname text, SecDbKey text)
>
> INSERT INTO table VALUES ('001', 'John', 'Smith', 'A0B1C2D3')
>
> INSERT OR REPLACE INTO table VALUES ('001', 'Jane', 'Parker', 'E4F51234')
>
> In the given example the "UNIQUE" constraint will cause the row to be
> overwritten, and the "A0B1C2D3" key will be lost. And it seems there is no
> way to intercept this deletion. The ROWID will also be changed, so it cannot
> be used for tracking purposes. If anyone knows a solution, please, advice.
If I understand properly what you're trying to do, then you can probably add
an automatically-maintained map table, something like this:
CREATE TABLE table (id TEXT PRIMARY KEY,
name TEXT,
surname TEXT,
secDbKey TEXT);
CREATE TABLE map (id TEXT PRIMARY KEY,
secDbKey TEXT);
CREATE TABLE SecDb (secDbKey TEXT PRIMARY KEY);
CREATE TRIGGER table_insert_tr AFTER INSERT ON table
FOR EACH ROW
BEGIN
DELETE FROM SecDb WHERE secDbKey =
(SELECT secDbKey FROM map WHERE id = new.id);
INSERT OR REPLACE INTO map VALUES (new.id, new.secDbKey);
END
Note that the PRIMARY KEY definitions are necessary to ensure that the correct
rowid (which is also 'id') is overwritten on an INSERT OR REPLACE query.
Derrell
Re: [sqlite] ON INSERT trigger
Hi, Christian
Thanks for your response. The situation, however, is more complicated.
Please, consider an example in SQL:
CREATE TABLE table (ID text UNIQUE, Name text, Surname text, SecDbKey text)
INSERT INTO table VALUES ('001', 'John', 'Smith', 'A0B1C2D3')
INSERT OR REPLACE INTO table VALUES ('001', 'Jane', 'Parker', 'E4F51234')
In the given example the "UNIQUE" constraint will cause the row to be
overwritten, and the "A0B1C2D3" key will be lost. And it seems there is no
way to intercept this deletion. The ROWID will also be changed, so it cannot
be used for tracking purposes. If anyone knows a solution, please, advice.
Thanks,
Alex
> If there is an "OR REPLACE" clause on your primary key, then you know
> exactly which row to delete from the secondary db. It will be the one with
> the same primary key as the new row. So, for each insert, just delete the
> row from the secondary db with the same key. If it doesn't exist, you'll
> have lost nothing (a bit of time, perhaps, as you'll have to do a negative
> index scan.) Then just insert the data as you would normally do.
>
>
>
> Christian
[sqlite] question on synchronous operation
This question is for DRH, mostly, but anybody who feels they know the internals sqlite, pls answer : I normally want to operate in synchronous=FULL mode. However, at some point in time, I would like to turn synchronous OFF, do a batch operation, and then turn synchronous to FULL again. This is easy to do. however, at the point where synchronous is turned ON again, I also want to do a FlushFileBuffers call on all respective file handles. the pseudo code is like this: set sync=off //first transaction begin ... lots of inserts/updates/deletes commit // another transaction begin ... lots of inserts/updates/deletes commit set sync=on (no pending transactions) Flush <- is there a way to do this, other than calling the OS directly ? If not, may I suggest that an function sqlite3_flush() is added in the API, for this purpose? I believe others will benefit, as this is a common scenario.
Re: [sqlite] Problems compiling SQLite 3.0.7 with MinGW
On Tue, 21 Sep 2004 08:58:22 -0400, Doug Currie <[EMAIL PROTECTED]> wrote: Tuesday, September 21, 2004, 6:04:53 AM, Peter wrote: Hello all, I've been using MinGW to compile SQLite for over a year already and encountered a problem I've never seen before when trying to compile SQLite 3.0.7. There is a problem in the Makefile in 3.0.7, fixed in CVS yesterday. Windows build doesn't need pthread library at all. Just change LIBPTHREAD=-lpthread to LIBPTHREAD= e The error message is as follows: $ mingw32-make ./libtool --mode=link gcc -g -O2 -DOS_WIN=1 -I. -I../src -DNDEBUG -DHAVE_READLINE=1 -I/mingw/include/readline -lpthread \ -o sqlite3 ../src/shell.c libsqlite3.la -lreadline gcc -g -O2 -DOS_WIN=1 -I. -I../src -DNDEBUG -DHAVE_READLINE=1 -I/mingw/include/readline -o sqlite3 ../src/shell.c ./.libs/libsqlite3.a -lpthread -lreadline c:\msys\mingw\bin\..\lib\gcc\mingw32\3.4.1\..\..\..\..\mingw32\bin\ld.exe: cannot find -lpthread collect2: ld returned 1 exit status c:\msys\mingw\bin\mingw32-make.exe: *** [sqlite3.exe] Error 1 Oh thank you, this makes me feel a lot better. ^^ Regards, Peter Bartholdsson
Re: [sqlite] Problems compiling SQLite 3.0.7 with MinGW
Tuesday, September 21, 2004, 6:04:53 AM, Peter wrote: > Hello all, I've been using MinGW to compile SQLite for > over a year already and encountered a problem I've never > seen before when trying to compile SQLite 3.0.7. There is a problem in the Makefile in 3.0.7, fixed in CVS yesterday. Windows build doesn't need pthread library at all. Just change LIBPTHREAD=-lpthread to LIBPTHREAD= e > The error message is as follows: > $ mingw32-make > ./libtool --mode=link gcc -g -O2 -DOS_WIN=1 -I. -I../src -DNDEBUG > -DHAVE_READLINE=1 -I/mingw/include/readline -lpthread \ > -o sqlite3 ../src/shell.c libsqlite3.la -lreadline > gcc -g -O2 -DOS_WIN=1 -I. -I../src -DNDEBUG -DHAVE_READLINE=1 > -I/mingw/include/readline -o sqlite3 ../src/shell.c > ./.libs/libsqlite3.a -lpthread -lreadline > c:\msys\mingw\bin\..\lib\gcc\mingw32\3.4.1\..\..\..\..\mingw32\bin\ld.exe: cannot > find -lpthread > collect2: ld returned 1 exit status > c:\msys\mingw\bin\mingw32-make.exe: *** [sqlite3.exe] Error 1
Re: [sqlite] Problems compiling SQLite 3.0.7 with MinGW
On Tue, 21 Sep 2004 13:15:52 +0200, Martins Mozeiko <[EMAIL PROTECTED]> wrote: Did you try googling for "pthreads win32": http://sources.redhat.com/pthreads-win32/ If you want thread safety with sqlite then you must install this pthreads library, if not - then just rerun ./configure with --disable-threadsafe Closest to that I googled on was libpthreads win32. >< Thanks a lot for the help and the quick reply. Regards, Peter Bartholdsoon
Re: [sqlite] Problems compiling SQLite 3.0.7 with MinGW
Did you try googling for "pthreads win32": http://sources.redhat.com/pthreads-win32/ If you want thread safety with sqlite then you must install this pthreads library, if not - then just rerun ./configure with --disable-threadsafe Peter Bartholdsson wrote: > Hello all, I've been using MinGW to compile SQLite for > over a year already and encountered a problem I've never > seen before when trying to compile SQLite 3.0.7. > > The error message is as follows: > $ mingw32-make > ./libtool --mode=link gcc -g -O2 -DOS_WIN=1 -I. -I../src -DNDEBUG -DHAVE_READLINE=1 > -I/mingw/include/readline -lpthread \ > -o sqlite3 ../src/shell.c libsqlite3.la -lreadline > gcc -g -O2 -DOS_WIN=1 -I. -I../src -DNDEBUG -DHAVE_READLINE=1 > -I/mingw/include/readline -o sqlite3 ../src/shell.c ./.libs/libsqlite3.a -lpthread > -lreadline > c:\msys\mingw\bin\..\lib\gcc\mingw32\3.4.1\..\..\..\..\mingw32\bin\ld.exe: cannot > find -lpthread > collect2: ld returned 1 exit status > c:\msys\mingw\bin\mingw32-make.exe: *** [sqlite3.exe] Error 1 > > > Just as a note SQLite 3.0.6 compiles without any problem. > From googling I've understood this has something to do with > threading but everything I found was linux related, none of > it related to MinGW. > Apparently it's something called liblpthread, however I've > not seen this for MinGW and none of the packages I've installed > seems to include it. > > Anyone have any ideas? > > Regards, > Peter Bartholdsson
[sqlite] Problems compiling SQLite 3.0.7 with MinGW
Hello all, I've been using MinGW to compile SQLite for over a year already and encountered a problem I've never seen before when trying to compile SQLite 3.0.7. The error message is as follows: $ mingw32-make ./libtool --mode=link gcc -g -O2 -DOS_WIN=1 -I. -I../src -DNDEBUG -DHAVE_READLINE=1 -I/mingw/include/readline -lpthread \ -o sqlite3 ../src/shell.c libsqlite3.la -lreadline gcc -g -O2 -DOS_WIN=1 -I. -I../src -DNDEBUG -DHAVE_READLINE=1 -I/mingw/include/readline -o sqlite3 ../src/shell.c ./.libs/libsqlite3.a -lpthread -lreadline c:\msys\mingw\bin\..\lib\gcc\mingw32\3.4.1\..\..\..\..\mingw32\bin\ld.exe: cannot find -lpthread collect2: ld returned 1 exit status c:\msys\mingw\bin\mingw32-make.exe: *** [sqlite3.exe] Error 1 Just as a note SQLite 3.0.6 compiles without any problem. From googling I've understood this has something to do with threading but everything I found was linux related, none of it related to MinGW. Apparently it's something called liblpthread, however I've not seen this for MinGW and none of the packages I've installed seems to include it. Anyone have any ideas? Regards, Peter Bartholdsson
Re: [sqlite] Version 3.0.7
Thank you, I am looking forward to it. The Sqlite-Wince is really useful, I only copy it to my directory and compile on Win32, WinCE, Linux and QNX. Jakub Nuno Lucas wrote: Jakub Adamek, dando pulos de alegria, escreveu : That's GREAT ! Nuno, are you going to merge Sqlite-Wince STABLE ? I'll do that during this week. Regards, ~Nuno Lucas -- Jakub Adamek Programmer Telematix Software, a.s. Karoliny Svetle 12, Praha 1 http://www.telematix.cz Tel: +420 608 247197 Office: +420 224233470
