How can one optimize the creation of the journal file. The problem is
this, for our system which is an event based one each message needs to
be insterted and committed to the database (guaranteed), this results in
a commit per insert, this was obviously unacceptably slow and according
to the lists
I had lots of problems here when starting with SQLite and painstaking I
think I've figured it out.
You have sqlite3_prepare, which compiles the sql into byte code, then
Sqlite3_step to execute the query or update, if it is an update then
there is no row, if query then call step until no more
DLL version
Sqlite3.3.17
The os is windows
After the last query of sqlite3_step
I decided to so some more tests, with threads, if synchronized properly,
it seems that you can use more than one thread without any problem as
long as
Sqlite3_finalize is called is this correct?
: Andre du Plessis [mailto:[EMAIL PROTECTED]
Sent: 19 June 2007 07:21 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] More SQLite Misuse
DLL version
Sqlite3.3.17
The os is windows
After the last query of sqlite3_step
I decided to so some more tests, with threads, if synchronized
In my sample that I supplied I illustrated how two threads does the
following:
Lock (Global Critical Section)
Queryobject.Prepare (Sqlite3_prepare)
QueryObject.Step (Sqlite3_step)
QueryObject.Reset (Sqlite3_reset)
Unlock
QueryObject.Free; (Sqlite3_reset (the missing piece of the puzzle))
In
Is it possible to do this:
Open DB1
Attatch DB2
In DB1 have a trigger that does
Insert into DB2. ?
Theoretically it seems possible but we couldn't get it to work. Before I
investigate further just want to know if it is possible
Thanks.
I saw this post on FTS2, and looked on the site and saw the FTS2 binary,
where can I find more information on this I searched the documentation
and can't find anything, it would be appreciated if someone can point me
to a doc that explains what it is and how to use it.
Thank you.
I have a question on index size, I have a table that by doing some
testing the DB have grown to 250MB, might not seem too large, as the
table have 4million records in it. But for this DB this table would grow
a lot larger than that. So I did some tests with a simple table 3 fields
FIELD1 is a
I have been testing FTS2 and it is awesome I must say, hope that the
project will keep going,
I have this problem though:
Once load extentions is enabled and fts2 is enabled, I cannot see
anything in the DB anymore when I open it in SQLiteDatabaseBrowser.
I CAN open it though, just cant see
Good day everyone.
I would like to know how to create an Autoincrement field and insure
that it is unique across the database, I tested this and it does not
seem to work:
c:\Temp>sqlite3 temp.db
SQLite version 3.3.17
Enter ".help" for instructions
sqlite> create table temptable (id
Unique Sequence
Andre du Plessis <[EMAIL PROTECTED]> wrote:
> I would like to know how to create an Autoincrement field and insure
> that it is unique across the database, I tested this and it does not
> seem to work:
>
> I need this because I need a link table that wont know
functions to
deal
> with it. You could use something like that to implement a
> unique-across-all-tables ID scheme. Though I think someone else
(Igor?)
> already suggested something similar.
>
> -Clark
>
> ----- Original Message
> From: Andre du Plessis <[EMAIL PROTECTED]&
I've been reading up a bit and I understand SQLite has limited support
for ALTER TABLE columns can be added but not dropped.
Some suggestions are to create a temp table and copy the data to it drop
the old table create the new one and then select the data back.
But there appears to be more to
I would like to be able to accomplish the following but don't see any
support for inner joins on update queries.
update A
set Id = B.Id
from A inner join B
on A.Field1 = B.Field_Temp
where B.Price > 0
Is this possible? What is the syntax of this query please?
Id);
-Original Message-
From: Andre du Plessis
Sent: 16 July 2007 02:30 PM
To: Gavin McGuinness
Subject: FW: [sqlite] Re: inner join
Here you go
It was answered by the mighty Igor, he's like THEE guru, guru's turn to
him for help, so don't question just follow :)
-Original Message-
From: I
Hi all
Is there any way to get the list of registered user defined functions
from code or SQL, and their parameters?
I need to provide our users with some GUI's to generate SQL and I would
like to make the user defined functions available,
I know as it is user defined functions I should
How to delete all other distinct rows except first one.
If I have a table with rows
ID, NAME
1, SOME NAME
2, SOME NAME
3, SOME NAME
4, ANOTHER NAME
5, ANOTHER NAME
The delete should work even if you don't know what the value of name is,
so simply for anything that is
4, 'another name' );
sqlite> insert into tmp values( 5, 'another name' );
sqlite>
sqlite> delete from tmp where id not in ( select min(id) from tmp
group by name );
sqlite>
sqlite> select * from tmp;
1|some name
4|another name
sqlite>
Rgds,
Simon
On 08/08/07, Andre du Plessis
sqlite3_enable_load_extension : function (sqlite3_value: Pointer; AOnOff:
Integer): Integer; cdecl;
sqlite3_load_extension : function (sqlite3_value: Pointer; AFileName,
AProcedure : PChar;
var AErrorMsg : PChar): Integer; cdecl;
var
Error : Pchar;
ConnectionHandle : Pointer; // Handle
defines the calling convention
as 'stdcall' (instead of 'cdecl'), was your sqlite3.dll custom built
with this calling convention? I don't think much of sqlite would work
with stdcall.
On 13/08/07, Andre du Plessis <[EMAIL PROTECTED]> wrote:
>
> sqlite3_enable_load_extension : function (s
Being a newbie to SQLite I've had the same problems working with SQLite
so maybe I can help,
It does not matter how well your database is synchronized, a common
pitfall I had was that I would have a query object with an open cursor
which prevents any other statement from committing to the
HI, how can I use .dump or something similar but specify a where clause,
I cant see that the .dump command allows this,
Without any arguments it seems to dump the whole db, the only argument
supported is the table name,
I would like to be able to do something like:
.dump table1 where ID >
below, are thread1 and thread2 connected to the same database, or
different
databases? In my scenario, the threads are connected to different
databases, so I'm not sure if it is the same situation.
Thanks,
Mark
> -Original Message-
> From: Andre du Plessis [mailto:[EMAIL PRO
recipient(s) is prohibited. If you receive this e-mail in error, please
notify the sender by phone or email immediately and delete it!
*
- Original Message -
From: Andre du Plessis <[EMAIL PROTEC
I did not want to highjack the exsiting FTS thread but Ralf,
Does DISQLite have its own implementation of FTS, so not using FTS2 at
all?
Does it use the same mechanism as FTS2 with virtual tables?
And have you compared speed and functionality to FTS2,
Maybe im actually just looking
That's a very neat little trick,
Once again why didn't I think of that :)
Thank you very much.
-Original Message-
From: Igor Tandetnik [mailto:[EMAIL PROTECTED]
Sent: 16 August 2007 01:52 PM
To: SQLite
Subject: [sqlite] Re: Dump with where clause
Andre du Plessis <[EMAIL PROTEC
: 16 August 2007 05:49 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Dump with where clause
Andre du Plessis wrote:
> HI, how can I use .dump or something similar but specify a where clause,
> I cant see that the .dump command allows this,
>
> Without any arguments it seems to du
image_temp as select * from file_folder_data;
For me .dump then works as expected.
Rgds,
Simon
On 17/08/07, Andre du Plessis <[EMAIL PROTECTED]> wrote:
> Hi Dennis this seems like a good idea, but there is a problem:
>
> I use the following statement:
>
> .mode insert
> select
Schema:
create table files (FILE_NAME TEXT, DATA TEXT);
create index idx_file_name on files (FILE_NAME);
create virtual table fts_files using fts2('file_name', 'data');
Ok, I just can't seem to figure out how to load fts2.dll in Python:
Standard sqlite3 module that comes with python
're Hiring! Seeking a passionate developer to join our team building
Flex
based products. Position is in the Washington D.C. metro area. If
interested
contact [EMAIL PROTECTED]
-Original Message-----
From: Andre du Plessis [mailto:[EMAIL PROTECTED]
Sent: Monday, August 20, 2007 11:50 AM
To: sq
Ok this is how I got the fts working in python and .net...
In python 2.5 sqlite3 is automatically included as a builtin. However I
could not find any method which loads the extention or to enable it,
I COULD do it in SQL by using:
conn.cursor().execute("SELECT load_extension('fts2')")
Just wanted to check with you guys that my build is actually stable in
Visual Studio 2005, I get about 250+ warnings when building SQLite I can
come back to you with more details if this is not correct, just want to
make sure that's seems correct, ive been getting the occasional weird
SQLite
I have a UTC date stored in text column in this format:
2007-08-27 08:58:16.601000
I want to do some selects for all that is newer than one day for
example, or then all that is older than 1 month, etc,
Any ideas?
Thanks.
you use the Sqlite floating point number format (function julianday
will do the conversion) you can use functions to compare days and dates.
Andre du Plessis wrote:
> I have a UTC date stored in text column in this format:
>
>
>
> 2007-08-27 08:58:16.601000
>
>
>
Hi how about the following:
CREATE TABLE puids (ID INTEGER PRIMARY KEY AUTOINCREMENT)
In python:
Def GetUniquePUID():
#OPTIONAL, if you already have a transaction
_Conn.cursor().execute("BEGIN EXCLUSIVE")
Try:
_Conn.cursor().execute("INSERT INTO PUIDS (id) values(null)");
What is the difference between:
MYCOLUMN NUMERIC
Vs
MYCOLUMN INTEGER
Or does it really mean nothing,
I remember somewhere the default column type could be integer, after
that it defaults to whatever you insert.
But I was wondering... what would the preferred type be to
A:
Good Evening.
SCHEMA:
SOMECOLUMN TEXT
I use the API as always:
ColType := sqlite3_column_type(Pointer(FHandle), I);
This has always worked fine for me and type returned was SQLITE_TEXT
however lately I noticed that a particular table this is sometimes
returned to me as
: [sqlite] ColType lost
"Andre du Plessis" <[EMAIL PROTECTED]> wrote:
>
> I use the API as always:
>
> ColType := sqlite3_column_type(Pointer(FHandle), I);
>
> This has always worked fine for me and type returned was SQLITE_TEXT
>
If sqlite3_column_type() return
be on a different one than the one that
actually has data, I will keep investigating and report back, I might
have to use
-Original Message-
From: Andre du Plessis [mailto:[EMAIL PROTECTED]
Sent: 05 September 2007 10:08 AM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] ColType lost
Should I
Im importing data
The data has a unique value, call it MD5 for now that could be a unique
value for the data.
Each record that gets imported is converted to MD5, a lookup is done on
the table for that MD5,
if found it must leave it alone, if not found it must insert a new
record...
All
the original table and rename the second temp table
That's likely to be slower on small data sets and faster on larger
datasets, I think. Depends on how much data is already in the database
vs. the amount of data being loaded.
-Tom
> -Original Message-
> From: Andre du P
Sorry if this is actually a noob question, how do I do an ansi style
order by in sqlite
For example
A
b
a
B
Would be sorted as
A
B
a
b
but what you want is
a
A
b
B
I can do order by upper(column)
But then things like
__new__
Goes to the bottom and should
[mailto:[EMAIL PROTECTED]
Sent: 07 September 2007 02:46 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] SQLite or MS Access
On 9/7/07, Andre du Plessis <[EMAIL PROTECTED]> wrote:
[..]
> What I don't understand is how Access, and other DB's are able to
still
> operate much f
Well here are my test results (im using Delphi for this one)
This is my insert statement:
INSERT INTO TEST (TEST_ID_NO_IDX, NO_INDEX, TEST_DATA, TEST_ID) values
(%d, %s, %s, %d)
This table deliberately has NO index.
1000 inserts took:
Inserting MS Access - 4,043.273 ms
Inserting SQLite -
Fts3 which everyone is talking about, I cannot see any mention of it on
the download page, does it mean that its just the development sourcecode
which people are compiling at this point or are there some prebuilt
dll's available, or has it not been officially released yet?
Thanks.
be preferred.
-Original Message-
From: Ralf Junker [mailto:[EMAIL PROTECTED]
Sent: 21 September 2007 12:50 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] FTS3 where ?
Hello Andre du Plessis,
If you are using Delphi, FTS3 is already included in the latest
DISQLite3 (Pro and Personal
I think everyone has this requirement, I was finding many slowness
problems using synchronous = on, and I started tweaking the app with
memory tables,
I recommend you look into it, you can create a memory Database by
specifying :memory: as the filename.
The connection you receive from this you
I know that the limits page says that the limit is 10 by default, I
would just like to know if the limit is per connection handle or global
for the module?
Thanks.
Im sorry if I am missing something obvious but I see precompiled
fts2.dll is no longer available for download alongside 3.5.1, if this
indeed means I am not blind, what does this mean?
Thank you.
49 matches
Mail list logo
