Re: [sqlite] Multiple Match statements per query

[David White]

Database abstraction, scope, multi-threading.


A few stack layers out is my loop requesting each record by rowid. And there's 
no predicting which thread will make the request. Likely, there will be 50 
threads making very similar requests at the same time.


If I could clone the statement, then I could make one that remains static. And 
each time I need it, I clone it, use it, and throw it away. I'm sure I would 
need some thread synchronization while performing the clone, but it would be a 
much briefer period than if I were trying to share a single statement with all 
threads.


If cloning isn't an option, I can create a statement pool; I would just prefer 
the simpler option.


Thanks
dw



  _  

From: Igor Tandetnik [mailto:itandet...@mvps.org]
To: sqlite-users@sqlite.org
Sent: Tue, 19 Apr 2011 17:08:09 -0600
Subject: Re: [sqlite] Multiple Match statements per query

On 4/19/2011 6:57 PM, Dave White wrote:
  > So, if I could prepare the statement once, then clone it every time I
  > need to use it, I may see a 4 fold speed increase when calling this
  > operation frequently.
  
  Why can't you just reuse the same statement every time? Are you aware of 
  sqlite3_reset ?
  -- 
  Igor Tandetnik
  
  ___
  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] Multiple Match statements per query

[Igor Tandetnik]

On 4/19/2011 6:57 PM, Dave White wrote:
> So, if I could prepare the statement once, then clone it every time I
> need to use it, I may see a 4 fold speed increase when calling this
> operation frequently.

Why can't you just reuse the same statement every time? Are you aware of 
sqlite3_reset ?
-- 
Igor Tandetnik

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

Re: [sqlite] Multiple Match statements per query

[Dave White]

I'll look into GLOB.

For the statement cloning, I'm looking for something a little different. I 
already have the connection and query string, and duplicating bindings is not 
necessary. It's the sqlite3_stmt that I want. right after sqlite3_prepare_v2 
has been called.

I was doing some profiling today. My statement loads one record by rsn, and 
then parses that data into an external object. Just calling sqlite3_prepare_v2 
takes 3-4x as long as stepping, reading, and parsing the record. This 
particular request is very simple, and the only thing that changes is the rowid 
which is handled with a bind. So, if I could prepare the statement once, then 
clone it every time I need to use it, I may see a 4 fold speed increase when 
calling this operation frequently.

The query is basically "Select * from tableName where rowid=?"

Thanks
dw





On Apr 19, 2011, at 4:40 PM, Mihai Militaru wrote:

> On Tue, 19 Apr 2011 14:18:05 -0600
> Dave White  wrote:
> 
>> For example, this works:
>>  SELECT T01_fts.docid FROM T01_fts JOIN T01 ON T01_fts.docid = T01.rowid 
>> WHERE T01.hasPic = 0 AND T01_fts.words MATCH 'SBNTKN*' 
>> 
>> These do not:
>>  SELECT T01_fts.docid FROM T01_fts JOIN T01 ON T01_fts.docid = T01.rowid 
>> WHERE T01.hasPic = 0 OR T01_fts.words MATCH 'SBNTKN*' 
>>  SELECT T01_fts.docid FROM T01_fts JOIN T01 ON T01_fts.docid = T01.rowid 
>> WHERE T01.hasPic = 0 AND (T01_fts.words MATCH 'SBNTKN*'  OR T01_fts.words 
>> MATCH 'CTLTKN*' )
> 
> I think it is GLOB '*' what you look for (or LIKE '%' for case-insensitive 
> match):
> SELECT T01_fts.docid FROM T01_fts JOIN T01 ON T01_fts.docid = T01.rowid WHERE 
> T01.hasPic = 0 OR T01_fts.words GLOB 'SBNTKN*';
> SELECT T01_fts.docid FROM T01_fts JOIN T01 ON T01_fts.docid = T01.rowid WHERE 
> T01.hasPic = 0 AND (T01_fts.words GLOB 'SBNTKN*' OR T01_fts.words GLOB 
> 'CTLTKN*');
> 
>> And an entirely separate question: Is there currently a way, or will there 
>> soon be a way to clone prepared statements?
> 
> I'd do it like this:
> 
> sqlite3_stmt *stmt2 = NULL;
> sqlite3_prepare_v2(sqlite3_db_handle(stmt1), sqlite3_sql(stmt1), -1, , 
> NULL);
> 
> Check what the respective functions do here: 
> http://www.sqlite.org/c3ref/funclist.html
> Basically:
> - the first argument function returns the database of the first statement 
> (you may pass a different open database handle directly,
>in order to "clone" the first statement over it);
> - the second argument function returns the sql text of the first statement;
> - the third argument is the size of the text to parse, negative to get it up 
> to the first NULL - normally the end;
> - the fourth is a pointer to your new unallocated statement;
> 
> I think copying the bindings is possible using sqlite3_bind_parameter_* and 
> something else I can't figure out right now.
> 
> -- 
> Mihai Militaru 
> ___
> 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] Multiple Match statements per query

[Mihai Militaru]

On Tue, 19 Apr 2011 14:18:05 -0600
Dave White  wrote:

> For example, this works:
>   SELECT T01_fts.docid FROM T01_fts JOIN T01 ON T01_fts.docid = T01.rowid 
> WHERE T01.hasPic = 0 AND T01_fts.words MATCH 'SBNTKN*' 
> 
> These do not:
>   SELECT T01_fts.docid FROM T01_fts JOIN T01 ON T01_fts.docid = T01.rowid 
> WHERE T01.hasPic = 0 OR T01_fts.words MATCH 'SBNTKN*' 
>   SELECT T01_fts.docid FROM T01_fts JOIN T01 ON T01_fts.docid = T01.rowid 
> WHERE T01.hasPic = 0 AND (T01_fts.words MATCH 'SBNTKN*'  OR T01_fts.words 
> MATCH 'CTLTKN*' )

I think it is GLOB '*' what you look for (or LIKE '%' for case-insensitive 
match):
SELECT T01_fts.docid FROM T01_fts JOIN T01 ON T01_fts.docid = T01.rowid WHERE 
T01.hasPic = 0 OR T01_fts.words GLOB 'SBNTKN*';
SELECT T01_fts.docid FROM T01_fts JOIN T01 ON T01_fts.docid = T01.rowid WHERE 
T01.hasPic = 0 AND (T01_fts.words GLOB 'SBNTKN*' OR T01_fts.words GLOB 
'CTLTKN*');

> And an entirely separate question: Is there currently a way, or will there 
> soon be a way to clone prepared statements?

I'd do it like this:

sqlite3_stmt *stmt2 = NULL;
sqlite3_prepare_v2(sqlite3_db_handle(stmt1), sqlite3_sql(stmt1), -1, , 
NULL);

Check what the respective functions do here: 
http://www.sqlite.org/c3ref/funclist.html
Basically:
- the first argument function returns the database of the first statement (you 
may pass a different open database handle directly,
in order to "clone" the first statement over it);
- the second argument function returns the sql text of the first statement;
- the third argument is the size of the text to parse, negative to get it up to 
the first NULL - normally the end;
- the fourth is a pointer to your new unallocated statement;

I think copying the bindings is possible using sqlite3_bind_parameter_* and 
something else I can't figure out right now.

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

Re: [sqlite] installing sqlite

[Danny]

SQLite Manager for firefox has real problems.  I made it work for a while until 
I found a real GUI.  And does a pretty decent job.

SQLite Expro Personal is FREE.  Of course, there is also a paid version with 
some additional features.

http://www.sqliteexpert.com/

--- On Tue, 4/19/11, Kees Nuyt  wrote:

From: Kees Nuyt 
Subject: Re: [sqlite] installing sqlite
To: sqlite-users@sqlite.org
Date: Tuesday, April 19, 2011, 5:42 PM

On Tue, 19 Apr 2011 15:04:31 -0400, Carlos Contreras
 wrote:

>sqlite offer a very eficient program but I dont understand how to install it
>in my Windows NT PC. I dont know how to compile a C program and then use it
>as a shell to run the sqlite commands.
>
>Can you help me?

Download the command line tool and/or the .dll for windows from
the download page http://www.sqlite.org/download.html 

Look for the heading "Precompiled Binaries For Windows"
There is nothing to install, just unzip the .zip archive(s) into a
folder of your choice.

Or, if you prefer a GUI tool:
Install the Firefox web browser from 
http://www.mozilla.com/en-US/firefox/new/

and add the SQLite manager add-on from 
https://addons.mozilla.org/en-US/firefox/addon/sqlite-manager/

I have no idea wether all that is compatible with Windows NT.
Windows XP and later are fine.
-- 
  (  Kees Nuyt
  )
c[_]
___
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] installing sqlite

[Kees Nuyt]

On Tue, 19 Apr 2011 15:04:31 -0400, Carlos Contreras
 wrote:

>sqlite offer a very eficient program but I dont understand how to install it
>in my Windows NT PC. I dont know how to compile a C program and then use it
>as a shell to run the sqlite commands.
>
>Can you help me?

Download the command line tool and/or the .dll for windows from
the download page http://www.sqlite.org/download.html 

Look for the heading "Precompiled Binaries For Windows"
There is nothing to install, just unzip the .zip archive(s) into a
folder of your choice.

Or, if you prefer a GUI tool:
Install the Firefox web browser from 
http://www.mozilla.com/en-US/firefox/new/

and add the SQLite manager add-on from 
https://addons.mozilla.org/en-US/firefox/addon/sqlite-manager/

I have no idea wether all that is compatible with Windows NT.
Windows XP and later are fine.
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Building managed only System.Data.SQLite

[Rich Rattanni]

Daniel:

I have not tried Csharp-sqlite, it looks interesting but I do not know
if that is right for me at this moment.

Shane:

I will try what you recommend tomorrow, thank you.

--
Rich

On Tue, Apr 19, 2011 at 4:47 PM, Shane Harrelson
 wrote:
> The target build settings can be controlled from SQLite.NET.Settings.targets
> - in particular, you should probably look at UseInteropDll and
> UseSqliteStandard.
> To override the USE_INTEROP_DLL setting, try copying
> SQLite.NET.Settings.targets to SQLite.NET.Settings.targets.user and make the
> settings changes there.
> This should work with VS2008 and VS2010.
>
> HTH.
> -Shane
>
>
> On Tue, Apr 19, 2011 at 3:09 PM, Rich Rattanni  wrote:
>
>> I was wondering if anyone has had any luck building the Managed-Only
>> System.Data.SQLite .NET adapter for SQLite from the source provided at
>> system.data.sqlite.org?  I downloaded the pre-built binaries but they
>> appear to rely on the InterOp assembly.  My current project is running
>> under Linux 2.6 on an ARM processor, and uses managed-only copy of the
>> System.Data.SQLite adapter + SQLite 3.5.0 + mono to run my .NET app.
>> I am looking to do some bug tracing / upgrading so I would like to
>> build my own copy from source.
>>
>> --
>> Rich
>> ___
>> 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] Building managed only System.Data.SQLite

[Shane Harrelson]

The target build settings can be controlled from SQLite.NET.Settings.targets
- in particular, you should probably look at UseInteropDll and
UseSqliteStandard.
To override the USE_INTEROP_DLL setting, try copying
SQLite.NET.Settings.targets to SQLite.NET.Settings.targets.user and make the
settings changes there.
This should work with VS2008 and VS2010.

HTH.
-Shane


On Tue, Apr 19, 2011 at 3:09 PM, Rich Rattanni  wrote:

> I was wondering if anyone has had any luck building the Managed-Only
> System.Data.SQLite .NET adapter for SQLite from the source provided at
> system.data.sqlite.org?  I downloaded the pre-built binaries but they
> appear to rely on the InterOp assembly.  My current project is running
> under Linux 2.6 on an ARM processor, and uses managed-only copy of the
> System.Data.SQLite adapter + SQLite 3.5.0 + mono to run my .NET app.
> I am looking to do some bug tracing / upgrading so I would like to
> build my own copy from source.
>
> --
> Rich
> ___
> 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] Multiple Match statements per query

[Dave White]

It appears that I cannot use MATCH more than once per query. It also looks like 
I can't use it if prefaced with OR or NOT.

For example, this works:
SELECT T01_fts.docid FROM T01_fts JOIN T01 ON T01_fts.docid = T01.rowid 
WHERE T01.hasPic = 0 AND T01_fts.words MATCH 'SBNTKN*' 

These do not:
SELECT T01_fts.docid FROM T01_fts JOIN T01 ON T01_fts.docid = T01.rowid 
WHERE T01.hasPic = 0 OR T01_fts.words MATCH 'SBNTKN*' 
SELECT T01_fts.docid FROM T01_fts JOIN T01 ON T01_fts.docid = T01.rowid 
WHERE T01.hasPic = 0 AND (T01_fts.words MATCH 'SBNTKN*'  OR T01_fts.words MATCH 
'CTLTKN*' )

Obviously I can manually rewrite the queries by hand. The trick is building 
these strings from a bundle of nested logic which has no knowledge about the 
database structure. I could probably get it working with UNION and INTERSECT, 
but I want to avoid those in the interest of speed.

Is this something I need to work around, or might it be supported in future 
releases of sqlite?

And an entirely separate question: Is there currently a way, or will there soon 
be a way to clone prepared statements?

Thanks
dw



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

Re: [sqlite] Building managed only System.Data.SQLite

[Daniel Morgan]

Have you tried C# SQLite?
http://code.google.com/p/csharp-sqlite/



--- On Tue, 4/19/11, Rich Rattanni  wrote:

> From: Rich Rattanni 
> Subject: [sqlite] Building managed only System.Data.SQLite
> To: "General Discussion of SQLite Database" 
> Date: Tuesday, April 19, 2011, 3:09 PM
> I was wondering if anyone has had any
> luck building the Managed-Only
> System.Data.SQLite .NET adapter for SQLite from the source
> provided at
> system.data.sqlite.org?  I downloaded the pre-built
> binaries but they
> appear to rely on the InterOp assembly.  My current
> project is running
> under Linux 2.6 on an ARM processor, and uses managed-only
> copy of the
> System.Data.SQLite adapter + SQLite 3.5.0 + mono to run my
> .NET app.
> I am looking to do some bug tracing / upgrading so I would
> like to
> build my own copy from source.
> 
> --
> Rich
> ___
> 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] Building managed only System.Data.SQLite

[Rich Rattanni]

I was wondering if anyone has had any luck building the Managed-Only
System.Data.SQLite .NET adapter for SQLite from the source provided at
system.data.sqlite.org?  I downloaded the pre-built binaries but they
appear to rely on the InterOp assembly.  My current project is running
under Linux 2.6 on an ARM processor, and uses managed-only copy of the
System.Data.SQLite adapter + SQLite 3.5.0 + mono to run my .NET app.
I am looking to do some bug tracing / upgrading so I would like to
build my own copy from source.

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

[sqlite] installing sqlite

[Carlos Contreras]

sqlite offer a very eficient program but I dont understand how to install it
in my Windows NT PC. I dont know how to compile a C program and then use it
as a shell to run the sqlite commands.

Can you help me?

-- 
Carlos Contreras, presidente
Club Científico de Peñalolén, Santiago, CHILE
http://www.clubcientifico.cl
fonos:  562-769130709-2114827
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] What happens if you insert more than your RAM

[jeff archer]

>Tue Apr 19 18:35:27 GMT 2011 Danny dragonslayer2k at yahoo.com 
>
>Depends on access type.  If accessing sequentially, paging would be minimal, 
>that is, you would process the "segment" that fits into memory, then page 
>in another "segment" and process that, etc., etc.
Even so it would now be accessed at disk speeds and thus very slow by 
comparison.
And OBTW, the db won't likely do sequential access.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What happens if you insert more than your RAM

[Danny]

Depends on access type.  If accessing sequentially, paging would be minimal, 
that is, you would process the "segment" that fits into memory, then page in 
another "segment" and process that, etc., etc.

However completely random hits on the database could result in heavy paging, 
unless it were possible to do the random accesses in a "sorted" manner.  For 
example, input transactions sorted by the same key that you are accessing by.

--- On Tue, 4/19/11, jeff archer  wrote:

> From: jeff archer 
> Subject: [sqlite] What happens if you insert more than your RAM
> To: "SQLite-user.org" 
> Date: Tuesday, April 19, 2011, 2:29 PM
> Wouldn't it page to disk, thrash and
> be very slow first?  
> 
> >On Mon, 18 Apr 2011 09:46:44 -0400, Pavel Ivanov 
> wrote:
> >You won't be able to insert. The statement will fail.
> >
> >On Mon, Apr 18, 2011 at 9:44 AM, Adam DeVita 
> wrote:
> >> Good day,
> >>
> >> What happens if you insert more than your RAM size
> into an in memory
> >> database?
> >> (I'm particularly interested in the Windows
> context).
> >>
> Jeff Archer
> Nanotronics Imaging
> jsarc...@nanotronicsimaging.com
> <330>819.4615 
> ___
> 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] What happens if you insert more than your RAM size into an in memory database?

[John Drescher]

> What happens if you insert more than your RAM size into an in memory
> database?
> (I'm particularly interested in the Windows context).
>

Are we talking about 32bit windows? I mean under 32 bit windows the
normal address space limit (without the /3GB switch and
LARGEADDRESSAWARE link flag) is 2GB and fragmented so that the largest
single process allocation (without resorting to AWE) is somewhere
around 1.2GB to 1.5GB even if you have a system with 4GB of RAM and
8GB of swap space.

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

[sqlite] What happens if you insert more than your RAM

[jeff archer]

Wouldn't it page to disk, thrash and be very slow first?  

>On Mon, 18 Apr 2011 09:46:44 -0400, Pavel Ivanov  wrote:
>You won't be able to insert. The statement will fail.
>
>On Mon, Apr 18, 2011 at 9:44 AM, Adam DeVita  wrote:
>> Good day,
>>
>> What happens if you insert more than your RAM size into an in memory
>> database?
>> (I'm particularly interested in the Windows context).
>>
Jeff Archer
Nanotronics Imaging
jsarc...@nanotronicsimaging.com
<330>819.4615 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Building sqlite for windows in a proper way

[jeff archer]

>On Mon, Apr 18, 2011 at 8:42 AM, Kuba Nowak  wrote:
>
>Does anyone one know how to build sqlite to get the same binary as on
>download page ?
>

What do you use to compare the speed between the builds of SQLite?

Are using debug build from VS2010?  My experience is that VS debug builds run 
most code significantly slower than the release builds.

Jeff Archer
Nanotronics Imaging
jsarc...@nanotronicsimaging.com
<330>819.4615 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] insert help

[Igor Tandetnik]

On 4/19/2011 1:59 PM, vquickl27 wrote:
> Jean-Christophe Deschamps-3 wrote:
>> So you need to perform as many inserts as values you have to insert:
>> insert into mytable (date, value) values ('2011/04/18 21:35:33', 1);
>> insert into mytable (date, value) values ('2011/04/18 21:35:33', 2);
>> insert into mytable (date, value) values ('2011/04/18 21:35:33', 3);
>> insert into mytable (date, value) values ('2011/04/18 21:35:33', 4);
>> insert into mytable (date, value) values ('2011/04/18 21:35:33', 5);
>
> that worked perfect. thanks for the help.   Do you think a join would be
> better then a insert?

A join between what table and what other table?
-- 
Igor Tandetnik

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

Re: [sqlite] insert help

[vquickl27]

Jean-Christophe Deschamps-3 wrote:
> 
> 
>>My date column is set when the program starts and i do not want it to
>>change.
> 
> How is this date column set in the database without inserting anything?
> 
>>   So I have my  with  and two columns  and
>>.   I have say 5 values (1 2 3 4 5) that I wanted inserted
>>into mytable where the date is equal to date that was preset my 
>>starting the
>>program.
> 
> Preset, how?  I guess you have this date stored in some variable 
> somewhere.  Just use it to fill the date column in the each new row.
> 
>>   So a select of my table would look like this:
>>select * from mytable where date='2011/04/18 21:35:33';
>>2011/04/18 21:35:33|1
>>2011/04/18 21:35:33|2
>>2011/04/18 21:35:33|3
>>2011/04/18 21:35:33|4
>>2011/04/18 21:35:33|5
> 
> So you need to perform as many inserts as values you have to insert:
> insert into mytable (date, value) values ('2011/04/18 21:35:33', 1);
> insert into mytable (date, value) values ('2011/04/18 21:35:33', 2);
> insert into mytable (date, value) values ('2011/04/18 21:35:33', 3);
> insert into mytable (date, value) values ('2011/04/18 21:35:33', 4);
> insert into mytable (date, value) values ('2011/04/18 21:35:33', 5);
> 
> If you have really _many_ inserts to perform, wrap the lot in a 
> transaction to speed up the process:
> begin;
>
> commit;
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

that worked perfect. thanks for the help.   Do you think a join would be
better then a insert?   
-- 
View this message in context: 
http://old.nabble.com/insert-help-tp31429185p31434954.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] insert help

[Jean-Christophe Deschamps]

>My date column is set when the program starts and i do not want it to
>change.

How is this date column set in the database without inserting anything?

>   So I have my  with  and two columns  and
>.   I have say 5 values (1 2 3 4 5) that I wanted inserted
>into mytable where the date is equal to date that was preset my 
>starting the
>program.

Preset, how?  I guess you have this date stored in some variable 
somewhere.  Just use it to fill the date column in the each new row.

>   So a select of my table would look like this:
>select * from mytable where date='2011/04/18 21:35:33';
>2011/04/18 21:35:33|1
>2011/04/18 21:35:33|2
>2011/04/18 21:35:33|3
>2011/04/18 21:35:33|4
>2011/04/18 21:35:33|5

So you need to perform as many inserts as values you have to insert:
insert into mytable (date, value) values ('2011/04/18 21:35:33', 1);
insert into mytable (date, value) values ('2011/04/18 21:35:33', 2);
insert into mytable (date, value) values ('2011/04/18 21:35:33', 3);
insert into mytable (date, value) values ('2011/04/18 21:35:33', 4);
insert into mytable (date, value) values ('2011/04/18 21:35:33', 5);

If you have really _many_ inserts to perform, wrap the lot in a 
transaction to speed up the process:
begin;
   
commit;


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

Re: [sqlite] SQLite3.DLL 3.7.6 memory leaks

[Jim Morris]

Are you sure these leaks aren't yours? Although I don't know the Sqlite 
internals some of the data values don't seem to be related to Sqlite, like:

c:/DEV/Platform/
PolicyDataPack.i

You can try to use the allocation number to narrow down the code 
location.  Don't remember the specifics though.

On 4/18/2011 6:03 AM, Khanh Nguyen wrote:
> Hi,
>
>
>
> My name is Khanh Nguyen and currently using SQLite3.DLL 3.7.6 in my
> application (DLL built with VS2008 C++ with this flag: Multi-threaded
> Debug DLL (/MDd).
>
>
>
> The DLL has some memory leaks that I have captured here:
>
>
>
> The thread 'Win32 Thread' (0x368) has exited with code 0 (0x0).
>
> Detected memory leaks!
>
> Dumping objects ->
>
> {8390} normal block at 0x01364C70, 64 bytes long.
>
>   Data:  63 3A 2F 44 45 56 2F 50 6C 61 74 66 6F 72 6D
> 2F
>
> {8382} normal block at 0x0138C9C0, 32 bytes long.
>
>   Data:<7Zl7ji/F9x+bOgbG>  37 5A 6C 37 6A 69 2F 46 39 78 2B 62 4F 67 62
> 47
>
> {8380} normal block at 0x0138B938, 32 bytes long.
>
>   Data:  50 6F 6C 69 63 79 44 61 74 61 50 61 63 6B 2E
> 69
>
> {8377} normal block at 0x01391F48, 448 bytes long.
>
>   Data:< >  10 1F F3 02 00 00 00 00 00 00 00 00 CD CD CD
> CD
>
> {7174} normal block at 0x01391DC8, 64 bytes long.
>
>   Data:  63 3A 2F 44 45 56 2F 50 6C 61 74 66 6F 72 6D
> 2F
>
> {7166} normal block at 0x013621D8, 32 bytes long.
>
>   Data:<7Zl7ji/F9x+bOgbG>  37 5A 6C 37 6A 69 2F 46 39 78 2B 62 4F 67 62
> 47
>
> {7164} normal block at 0x01386690, 32 bytes long.
>
>   Data:  50 6F 6C 69 63 79 44 61 74 61 50 61 63 6B 2E
> 69
>
> {7161} normal block at 0x01390430, 448 bytes long.
>
>   Data:< >  10 1F F3 02 00 00 00 00 00 00 00 00 CD CD CD
> CD
>
> {7108} normal block at 0x0138CDC8, 64 bytes long.
>
>   Data:  63 3A 2F 44 45 56 2F 50 6C 61 74 66 6F 72 6D
> 2F
>
> {7100} normal block at 0x01386320, 32 bytes long.
>
>   Data:  45 4E 74 35 4B 6B 59 48 52 71 65 55 2F 69 4C
> 52
>
> {7098} normal block at 0x0138BCD8, 32 bytes long.
>
>   Data:  50 6F 6C 69 63 79 44 61 74 61 50 61 63 6B 2E
> 62
>
> {7095} normal block at 0x0138D530, 448 bytes long.
>
>   Data:< >  10 1F F3 02 00 00 00 00 00 00 00 00 CD CD CD
> CD
>
> {7033} normal block at 0x013796D8, 64 bytes long.
>
>   Data:  63 3A 2F 44 45 56 2F 50 6C 61 74 66 6F 72 6D
> 2F
>
> {7025} normal block at 0x0136DED0, 32 bytes long.
>
>   Data:<5U/jyx2txHeUQUe/>  35 55 2F 6A 79 78 32 74 78 48 65 55 51 55 65
> 2F
>
> {7023} normal block at 0x013658D0, 32 bytes long.
>
>   Data:  4E 65 74 77 6F 72 6B 53 65 74 74 69 6E 67 33
> 2E
>
> {7020} normal block at 0x01389BF0, 448 bytes long.
>
>   Data:< >  10 1F F3 02 00 00 00 00 00 00 00 00 CD CD CD
> CD
>
> Object dump complete.
>
> The program '[5432] DebugConsole.exe: Native' has exited with code 0
> (0x0).
>
>
>
> Please help me overcome this memory leak issue.
>
>
>
> Thanks and Kind Regards,
>
>
>
> Khanh
>
>
>
> ___
> 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] What happens if you insert more than your RAM size into an in memory database?

[Jay A. Kreibich]

On Tue, Apr 19, 2011 at 03:29:42PM +0100, Simon Slavin scratched on the wall:
> 
> On 19 Apr 2011, at 2:58pm, Adam DeVita wrote:
> 
> > Our application is typically implemented on a standard laptop PC.  It seems
> > that the symptoms displayed are consistent with what this list describes
> > would happen,  so it looks like I can start thinking of how to write a
> > defence.   It does suddenly become very slow.
> > 
> > I think the potential solutions we may implement  are all in application
> > code, so not really an SQLite problem.
> 
> If you are using an in-memory database purely for speed, you might try
> the simple change of not doing that.  Make your database use disk
> space as any normal one would, and delete it after you close it. 
> Windows is pretty good at caching stuff these days and on-disk
> databases aren't as slow as some people would guess.

  You can pass the sqlite3_open*() calls an empty string for the
  filename.  That will create a disk-backed temporary database that
  automatically cleans up after itself.  If you crank the SQLite cache
  up, the performance should be roughly the same as an in-memory
  database, right up until it runs out of memory.  After that, the
  performance should be a bit better, as SQLite's cache is likely to
  be more efficient than paging memory to disk.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] insert help

[vquickl27]

Jean-Christophe Deschamps-3 wrote:
> 
> 
>>Newbie here.   i'm trying to insert multiple values into a table by a 
>>certain
>>date and when I use where clause it fails.  This is my code "insert 
>>into db
>>(table) values ('value') where date = 'date range'". Thanks for any help.
> 
> There is no where clause in insert statements, it wouldn't make sense.
> Your insert should look like:
> 
> insert into mytable (datecolumn) values 
> (litteral_date_in_the_format_you_choose);
> 
> See http://www.sqlite.org/lang_insert.html
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

My date column is set when the program starts and i do not want it to
change.  So I have my  with  and two columns  and
.   I have say 5 values (1 2 3 4 5) that I wanted inserted
into mytable where the date is equal to date that was preset my starting the
program.  So a select of my table would look like this:
select * from mytable where date='2011/04/18 21:35:33';
2011/04/18 21:35:33|1
2011/04/18 21:35:33|2
2011/04/18 21:35:33|3
2011/04/18 21:35:33|4
2011/04/18 21:35:33|5
-- 
View this message in context: 
http://old.nabble.com/insert-help-tp31429185p31433593.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] What happens if you insert more than your RAM size into an in memory database?

[Simon Slavin]

On 19 Apr 2011, at 2:58pm, Adam DeVita wrote:

> Our application is typically implemented on a standard laptop PC.  It seems
> that the symptoms displayed are consistent with what this list describes
> would happen,  so it looks like I can start thinking of how to write a
> defence.   It does suddenly become very slow.
> 
> I think the potential solutions we may implement  are all in application
> code, so not really an SQLite problem.

If you are using an in-memory database purely for speed, you might try the 
simple change of not doing that.  Make your database use disk space as any 
normal one would, and delete it after you close it.  Windows is pretty good at 
caching stuff these days and on-disk databases aren't as slow as some people 
would guess.

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

Re: [sqlite] Building sqlite for windows in a proper way

[Pavel Ivanov]

> Does anyone one know how to build sqlite to get the same binary as on
> download page ?

Did you try to remove all those defines that you add at build time and
leave only default values set inside sqlite3.c file?


Pavel


On Mon, Apr 18, 2011 at 8:42 AM, Kuba Nowak  wrote:
> Hello
>
> My problem is no matter how i build sqlite - my binary is much slower than
> the precompiled one on sqlite download page (about 3 - 6 times depending on
> the query).
>
> I am using sqlite3.h and sqlite3.c from the amalgamation source:
>
> http://www.sqlite.org/sqlite-amalgamation-3070602.zip
>
> I have added the following flags when compiling sqlite:
>
> gcc
> -s -O4 -I. -fomit-frame-pointer
> -DNDEBUG
> -DSQLITE_OS_WIN=1
> -DSQLITE_HAVE_READLINE=0
> -DSQLITE_THREADSAFE=1
> -DSQLITE_TEMP_STORE=2
> -DSQLITE_ENABLE_RTREE
> -DSQLITE_ENABLE_FTS3
> -DSQLITE_OMIT_COMPILEOPTION_DIAGS
> -DSQLITE_ENABLE_COLUMN_METADATA
> -DNO_TCL
>
> I built it both with MINGW and with MSVS 2010.
>
> Does anyone one know how to build sqlite to get the same binary as on
> download page ?
>
> Any help would be appreciated.
>
> Thanks.
>
> Jakub
> ___
> 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] What happens if you insert more than your RAM size into an in memory database?

[Adam DeVita]

Thanks for the responses.

Our application is typically implemented on a standard laptop PC.  It seems
that the symptoms displayed are consistent with what this list describes
would happen,  so it looks like I can start thinking of how to write a
defence.   It does suddenly become very slow.

I think the potential solutions we may implement  are all in application
code, so not really an SQLite problem.

Thanks,
Adam



On Mon, Apr 18, 2011 at 10:07 AM, eLaReF  wrote:

> Talking as a Windows user only rather than an SQL expert (I'm not even
> good enough to call myself a beginner!)
>
> Are we talking about a small netbook type with only say 8GB of memory
> and no hard drive.
>
> If a Windows m/c has a hard drive, surely virtual memory
> (drive-swapping) comes into play?
> It would, of course become v-e-r-y slow in comparison.
>
>
> eLaReF
>
>
>
>
> On 18/04/2011 14:46, Pavel Ivanov wrote:
> > You won't be able to insert. The statement will fail.
> >
> > Pavel
> >
> >
> > On Mon, Apr 18, 2011 at 9:44 AM, Adam DeVita
>  wrote:
> >> Good day,
> >>
> >> What happens if you insert more than your RAM size into an in memory
> >> database?
> >> (I'm particularly interested in the Windows context).
> >>
> >> regards,
> >> Adam
> >> ___
> >> 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
>



-- 
VerifEye Technologies Inc.
905-948-0015x245
151 Whitehall Dr, Unit 2
Markham ON, L3R 9T1
Canada
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE_MISUSE error code

[Jay A. Kreibich]

On Tue, Apr 19, 2011 at 04:19:55PM +0530, Navaneeth Sen B scratched on the wall:
 
> What is this SQLITE_MISUSE error code? When is it exactly thrown?

  It means that the programmer is using the SQLite API incorrectly,
  and that there is a fundamental flow or logic issue in the code.

  An example might be calling sqlite3_bind_xxx() on an "active"
  statement (one that has been stepped, but not yet reset/finalized).
  Such action simply does not make sense.

  It is usually the result of a new-to-SQLite programmer not fully
  understanding the correct way to use SQLite API, but can also
  point to buggy flow control.

  Are you having issues with a specific call returning MISUSE?

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How to download precompiled files for 3.6.21-x86-Windows

[Aydinoz, Baris]

Hi all,
 
We want to upgrade our SQLite version to 3.6.21. But, I could not find
any amalgamation or precompiled download link for that version in
official website. Therefore, I am requesting a download link where I can
verify hashcodes.
 
I need below files:
 
sqlite3.dll
sqlite3.lib.
sqlite3.h
sqlite3ext.h
 
thanks in advance.
 
We currently use 3.0.8 version.
 
Best regards
 
Baris
 
 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What happens if you insert more than your RAM size into an in memory database?

[eLaReF]

Talking as a Windows user only rather than an SQL expert (I'm not even 
good enough to call myself a beginner!)

Are we talking about a small netbook type with only say 8GB of memory 
and no hard drive.

If a Windows m/c has a hard drive, surely virtual memory 
(drive-swapping) comes into play?
It would, of course become v-e-r-y slow in comparison.


eLaReF




On 18/04/2011 14:46, Pavel Ivanov wrote:
> You won't be able to insert. The statement will fail.
>
> Pavel
>
>
> On Mon, Apr 18, 2011 at 9:44 AM, Adam DeVita  wrote:
>> Good day,
>>
>> What happens if you insert more than your RAM size into an in memory
>> database?
>> (I'm particularly interested in the Windows context).
>>
>> regards,
>> Adam
>> ___
>> 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

[sqlite] SQLite3.DLL 3.7.6 memory leaks

[Khanh Nguyen]

Hi,

 

My name is Khanh Nguyen and currently using SQLite3.DLL 3.7.6 in my
application (DLL built with VS2008 C++ with this flag: Multi-threaded
Debug DLL (/MDd).

 

The DLL has some memory leaks that I have captured here:

 

The thread 'Win32 Thread' (0x368) has exited with code 0 (0x0).

Detected memory leaks!

Dumping objects ->

{8390} normal block at 0x01364C70, 64 bytes long.

 Data:  63 3A 2F 44 45 56 2F 50 6C 61 74 66 6F 72 6D
2F 

{8382} normal block at 0x0138C9C0, 32 bytes long.

 Data: <7Zl7ji/F9x+bOgbG> 37 5A 6C 37 6A 69 2F 46 39 78 2B 62 4F 67 62
47 

{8380} normal block at 0x0138B938, 32 bytes long.

 Data:  50 6F 6C 69 63 79 44 61 74 61 50 61 63 6B 2E
69 

{8377} normal block at 0x01391F48, 448 bytes long.

 Data: <> 10 1F F3 02 00 00 00 00 00 00 00 00 CD CD CD
CD 

{7174} normal block at 0x01391DC8, 64 bytes long.

 Data:  63 3A 2F 44 45 56 2F 50 6C 61 74 66 6F 72 6D
2F 

{7166} normal block at 0x013621D8, 32 bytes long.

 Data: <7Zl7ji/F9x+bOgbG> 37 5A 6C 37 6A 69 2F 46 39 78 2B 62 4F 67 62
47 

{7164} normal block at 0x01386690, 32 bytes long.

 Data:  50 6F 6C 69 63 79 44 61 74 61 50 61 63 6B 2E
69 

{7161} normal block at 0x01390430, 448 bytes long.

 Data: <> 10 1F F3 02 00 00 00 00 00 00 00 00 CD CD CD
CD 

{7108} normal block at 0x0138CDC8, 64 bytes long.

 Data:  63 3A 2F 44 45 56 2F 50 6C 61 74 66 6F 72 6D
2F 

{7100} normal block at 0x01386320, 32 bytes long.

 Data:  45 4E 74 35 4B 6B 59 48 52 71 65 55 2F 69 4C
52 

{7098} normal block at 0x0138BCD8, 32 bytes long.

 Data:  50 6F 6C 69 63 79 44 61 74 61 50 61 63 6B 2E
62 

{7095} normal block at 0x0138D530, 448 bytes long.

 Data: <> 10 1F F3 02 00 00 00 00 00 00 00 00 CD CD CD
CD 

{7033} normal block at 0x013796D8, 64 bytes long.

 Data:  63 3A 2F 44 45 56 2F 50 6C 61 74 66 6F 72 6D
2F 

{7025} normal block at 0x0136DED0, 32 bytes long.

 Data: <5U/jyx2txHeUQUe/> 35 55 2F 6A 79 78 32 74 78 48 65 55 51 55 65
2F 

{7023} normal block at 0x013658D0, 32 bytes long.

 Data:  4E 65 74 77 6F 72 6B 53 65 74 74 69 6E 67 33
2E 

{7020} normal block at 0x01389BF0, 448 bytes long.

 Data: <> 10 1F F3 02 00 00 00 00 00 00 00 00 CD CD CD
CD 

Object dump complete.

The program '[5432] DebugConsole.exe: Native' has exited with code 0
(0x0).

 

Please help me overcome this memory leak issue.

 

Thanks and Kind Regards,

 

Khanh

 

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

[sqlite] SQLITE_MISUSE error code

[Navaneeth Sen B]

Hi All,

What is this SQLITE_MISUSE error code? When is it exactly thrown?

-- 

Thanks,
Sen


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

[sqlite] [SQLITE] precompiled files for 3.6.21-x86-Windows

[Aydinoz, Baris]

Hi all,
 
We want to upgrade our SQLite version to 3.6.21. But, I could not find
any amalgamation or precompiled download link for that version in
official website. Therefore, I need your help to find a download link
where I can verify hashcodes. 
 
I need below files:
 
sqlite3.dll
sqlite3.lib
sqlite3.h
sqlite3ext.h
 
thanks in advance.
 
We currently use 3.0.8 version.
 
Best regards
 
Baris
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] insert help

[Jean-Christophe Deschamps]

>Newbie here.   i'm trying to insert multiple values into a table by a 
>certain
>date and when I use where clause it fails.  This is my code "insert 
>into db
>(table) values ('value') where date = 'date range'". Thanks for any help.

There is no where clause in insert statements, it wouldn't make sense.
Your insert should look like:

insert into mytable (datecolumn) values 
(litteral_date_in_the_format_you_choose);

See http://www.sqlite.org/lang_insert.html

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