RE: [sqlite] Pre-compiled SQL using the commandline interface

[Robert Simpson]

If you're going to all the trouble to write a program in C#, why not just
use the ADO.NET data provider for SQLite and insert into the databsae
yourself?

Seems rather silly to generate an insert statement and not actually execute
it in your code.  The command-line interface to sqlite isn't designed for
prepared statements and parameterized queries.

If you're using VS2003/.NET 1.1, use the Finisar ADO.NET library at
http://sourceforge.net/projects/adodotnetsqlite

If you're using VS2005/.NET 2.0, use my ADO.NET provider at
http://sourceforge.net/projects/sqlite-dotnet2

Robert


> -Original Message-
> From: Alan McGovern [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, August 23, 2005 5:51 AM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] Pre-compiled SQL using the commandline interface
> 
> Hi,
> 
> I can't seem to find information about creating precompiled 
> statements when using the commandline program to access the 
> sqlite database. What i'm doing at the moment is using a C# 
> program to generate text files of insert statements up to 
> 100megs in size and then using .read to import them. Each 
> insert statement is identical except for the values that are 
> being imported, so in this scenario if i could use 
> precompiled SQL, it would result in quite a benefit, but i 
> can't seem to be able to do this.
> 
> Thanks,
> Alan.
> 

[sqlite] ok wrapper...what gives???

[Jim McNamara]

hi-

i am trying to use christian werners wrapper with
mixed results.  it recognizes the db version but
nothing prints out in the console of eclipse when i
run this code.
if anyone else is using it could you take a glance and
see why i am not getting a console print out of the
data? i get this version number for my console print
out.

3.2.1

i tested the table for data with the command line
utility.  it is the right table and the data is in
there.

thanks very much,
jim

package calcpackage;

import SQLite.Database;
import SQLite.Callback;

import java.lang.System;


import SQLite.TableResult;


/**
 * @author HP_Owner
 *
 * TODO To change the template for this generated type
comment go to
 * Window - Preferences - Java - Code Style - Code
Templates
 */
public class calc {
static final String JDBC_DRIVER ="SQLite.JDBCDriver";
static final String DATABASE_URL =
"jdbc:sqlite://C:/calcDB/t1.db";


  
public static void main(String args[])
{

try 
{

 
SQLite.Database d4 = new SQLite.Database();
   d4.open("t1.db",0);
  System.out.print(d4.get_table("select * from
mytbl"));
  System.out.print(d4.dbversion());
   
   d4.close();
}
catch(SQLite.Exception myException)
{
myException.printStackTrace();
}

}


}





Start your day with Yahoo! - make it your home page 
http://www.yahoo.com/r/hs 
 

Re: [sqlite] "where not exists (union-select)" fails on 2nd where

[Kurt Welgehausen]

> SELECT * FROM PRIM AS P
> WHERE NOT EXISTS
> (
> SELECT REFID FROM REF1 WHERE REF1.REFID=P.ID
> UNION
> SELECT REFID FROM REF2 WHERE REF2.REFID=P.ID
> );

I looks like correct SQL according to the SQLite docs,
but I don't understand why you coded the select that
way. You should get the same result from

   select * from prim as p
   where not exists (select refid from ref1
 where refid=p.id) and
 not exists (select refid from ref2
 where refid=p.id)

I would guess that this form would be more efficient
because if the first test fails, the second sub-select
should not be executed, perhaps saving a complete scan
of ref2.

Regards

[sqlite] "where not exists (union-select)" fails on 2nd where

[Markus Weissmann]

Hi folks,

I've got a little problem with a - at least I think so - correct SQL- 
statement:


three tables, two referencing the 1st one

---
SELECT * FROM PRIM AS P
WHERE NOT EXISTS
(
SELECT REFID FROM REF1 WHERE REF1.REFID=P.ID
UNION
SELECT REFID FROM REF2 WHERE REF2.REFID=P.ID
);
---

chokes with "SQL error: no such column: P.ID"; as long as I do not use
a WHERE statement in the 2nd 'inner' SELECT statement, it works as  
expected.


I've worked around this by creating a VIEW from the union of the two
referencing tables so I do not need the UNION in the original query.

Is this a bug or did I just not get the docs?


thanks,

-Markus


PS: sqlite really rocks & the C-API doc is very nice!

---
Markus W. Weissmann
http://www.mweissmann.de/
http://www.opendarwin.org/~mww/

Re: [sqlite] read and update record at the same time

[D. Richard Hipp]

On Tue, 2005-08-23 at 21:46 +0200, tone skoda wrote:
> read (SELECT) has to find physical position of record on disk.
> update too has to find physical position of record on disk.
> this is inefficient if it's done twice in a row, because two searches for 
> record have to be performed. 
> only one search would be enough and more efficient.
> is there any way SELECT and UPDATE can be done in in one SQL statement?
> 
> in fact, why isn't it possible to refer to records by their physicall offset 
> on 
> disk? as far as i know thay don't change.
> 

SQLite supports variable-length records with automatic
compaction and defragmentation.  Records are subject to
being moved about on the disk after any change to nearby
records.
-- 
D. Richard Hipp <[EMAIL PROTECTED]>

[sqlite] read and update record at the same time

[tone skoda]

read (SELECT) has to find physical position of record on disk.
update too has to find physical position of record on disk.
this is inefficient if it's done twice in a row, because two searches for 
record have to be performed. 
only one search would be enough and more efficient.
is there any way SELECT and UPDATE can be done in in one SQL statement?

in fact, why isn't it possible to refer to records by their physicall offset on 
disk? as far as i know thay don't change.




http://www.email.si/

[sqlite] Please unsubscribe me

[Bill Henderson]

I have tried uncuccessfully to unsubscribe several times
 
PLEASE REMOVE ME FROM THIS LIST


-
 Appel audio GRATUIT partout dans le monde avec le nouveau Yahoo! Messenger
 Téléchargez le ici !  

Re: [sqlite] Speed.html

[D. Richard Hipp]

I just ran a speed comparison between version 2.8.16 and 3.2.3.
Version 3.2.3 is faster in almost every case.  See

  http://www.sqlite.org/speed-2816-v-323.html

-- 
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] Speed.html

[D. Richard Hipp]

On Tue, 2005-08-23 at 17:36 +0200, Steven Van Ingelgem wrote:
> Hi, 
>  
> I just redid the speedtest.tcl-script in PHP 
> (http://www.karels0ft.be/tmp/speed_script.html).
> 
> Can  anyone check why the SQLite3's are so slow compared to SQLite2's ?
> 
> 

Aha!  Probably you are using an older test script that tries
to run of synchronous using

   PRAGMA default_synchronous=OFF;

The "default_synchronous" pragma was dropped form SQLite 3
because it was considered too dangerous.  There was too much
risk of data loss and database corruption after a power
failure.  Thus the synchronous behavior is never being
disabled in your tests and you are reporting synchronous
times in the "no-sync" column.

Another thing to note is that SQLite3 has

   PRAGMA synchronous=FULL

by default whereas version 2 had synchronous=ON by default.
FULL is a little slower to commit, but it is also safer in
the face of power failures.  In spite of this handicap,
SQLite version 3 still manages to be faster than version 2
in many tests.
-- 
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] Speed.html

[D. Richard Hipp]

On Tue, 2005-08-23 at 17:36 +0200, Steven Van Ingelgem wrote:
> Hi, 
>  
> I just redid the speedtest.tcl-script in PHP 
> (http://www.karels0ft.be/tmp/speed_script.html).
> 
> Can  anyone check why the SQLite3's are so slow compared to SQLite2's ?
> 
> 

Most of the important SQLite3 times are faster than SQLite2's.
What times are you specifically concerned about?

Perhaps you are concerned about Test1 with SQLite3 set to
no-sync.  It appears that the no-sync is not working.  I'll
look into it.

On the other hand, you should always leave synchronization 
enabled to prevent data loss after a power failure.  So this
is not a real high priority.
-- 
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] Speed.html

[Robert Simpson]

- Original Message - 
From: "Steven Van Ingelgem" <[EMAIL PROTECTED]>

To: 
Sent: Tuesday, August 23, 2005 8:36 AM
Subject: [sqlite] Speed.html



Hi,

I just redid the speedtest.tcl-script in PHP
(http://www.karels0ft.be/tmp/speed_script.html).

Can  anyone check why the SQLite3's are so slow compared to
SQLite2's ?


This script maybe could be used by someone with too much time to
check up the timings of the latest versions, to recreate the
speed.html-document, as it's a little outdated.
[http://www.karels0ft.be/tmp/speed.html]


To be complete, you should include prepared statement execution tests in 
your speed comparisons.  Most modern databases are designed around a 
prepared statement/parameterized execution model, and you're not getting 
ideal performance when you're inserting or updating thousands and thousands 
of rows by manufacturing an INSERT/UPDATE statement for each row.


Even your select tests would benefit from being prepared once and queried 
using parameters.


As an example, using a prepared statement and an integer parameter inside a 
transaction, I can insert about 100,000 rows a second into an indexed 
SQLite3 table using my ADO.NET 2.0 provider in C#.  (Computer is a 3.2ghz HT 
laptop running XP Media Center Ed.).  Times will of course drop when 
inserting strings or adding multiple parameters, but in theory prepared 
statements using parameters should always win over self-generated statements 
in all bulk op tests.


Robert

[sqlite] Speed.html

[Steven Van Ingelgem]

Hi, 
 
I just redid the speedtest.tcl-script in PHP 
(http://www.karels0ft.be/tmp/speed_script.html).

Can  anyone check why the SQLite3's are so slow compared to SQLite2's ?


This script maybe could be used by someone with too much time to check up the 
timings of the latest versions, to recreate the speed.html-document, as it's a 
little outdated. [http://www.karels0ft.be/tmp/speed.html]



Reactions would be nice too :)


 
Greetings,
Steven 

Re: [sqlite] How to insert a binary file into the database of sqlite in C++?

[Dennis Cote]

我本楚狂人 wrote:


I have search with Google , and find these information as below

sqlite3_prepare(..., "insert into foo values(?);", -1, , ...);
sqlite3_bind_blob(stmt, 1, "bar", 3, SQLITE_TRANSIENT);
sqlite3_step(stmt);

But if there is a file in this path "C:\a.mp3",
I use 
"ifstream mp3("C:\\a.mp3")

open this file .

Then I don't know how can I insert it  to the database with the
pointer "sqlite3 *db; "
(the database has two column,
the first's type is text(to write the name of file)
;
the second's type is blob(to write the binary file ) )

Would you please give me some code about this problem?Thank you.

And another question,
What's the lastest parameter's mean of sqlite3_prepare?
I have read the help of this problem , but I can't understand.
 

This (or something like it) should do what you want, but I'm not sure if 
putting large objects like MP3 files into the database is really a good 
idea. Others have said they had better performance using the file system 
to store large files and simply saving the file names in the database.


ifstream mp3("C:\\a.mp3");
mp3.seekg(0, ios::end);
long sz = mp3.tellg();
mp3.seekg(0, ios::beg);
stringstream sbuf;
sbuf << mp3.rdbuf();
char* buf = sbuf.str();

sqlite3_prepare(..., "insert into foo values(?);", -1, , ...);
sqlite3_bind_blob(stmt, 1, buf, sz, SQLITE_TRANSIENT);
sqlite3_step(stmt);

HTH
Dennis Cote

Re: [sqlite] malformed database schema - near "AUTOINCREMENT": syntax error

[Kurt Welgehausen]

> "malformed database schema - near "AUTOINCREMENT": syntax error"

It might help if you published your schema. Also, it would
be interesting to know whether your v3.2.3 passed all the
tests in the test suite.

Regards

Re: [sqlite] malformed database schema - near "AUTOINCREMENT": syntax error

[Puneet Kishor]

On Aug 23, 2005, at 5:10 AM, Christiane Lemke wrote:


Dear Mailing List users,

I am new to SQLite and didn't find help on the mailing list archives or
google for my problem.

I am using SQLite in my CGI/C program. I got it running perfectly under
three Linux systems (one ubuntu, two gentoos), but now that I want to
run the program under a kubuntu system, I get a

"malformed database schema - near "AUTOINCREMENT": syntax error"



I had a similar experience where everything worked well on my Mac 
(SQLite version 321) but gave me the above error on Windows (SQLite 
version 320, I think... well, definitely not 321). Upgrading on Windows 
settled everything.


Weird.

--
Puneet Kishor

[sqlite] Pre-compiled SQL using the commandline interface

[Alan McGovern]

Hi,

I can't seem to find information about creating precompiled statements when 
using the commandline program to access the sqlite database. What i'm doing at 
the moment is using a C# program to generate text files of insert statements up 
to 100megs in size and then using .read to import them. Each insert statement 
is identical except for the values that are being imported, so in this scenario 
if i could use precompiled SQL, it would result in quite a benefit, but i can't 
seem to be able to do this.

Thanks,
Alan.

Re: [sqlite] Can you use random(*) to retrieve a pseudo random r owfrom 3.2.3?

[Jakob Hirsch]

Brandon, Nicholas wrote:

> CREATE TABLE MyTable(start_col int,end_col int);
> INSERT INTO "MyTable" VALUES(-2, 2);
...
> sqlite> select * from MyTable join (select random(*) as number) as rand
> where start_col >= rand.number and end_col < rand.number;

Your logic is broken. As every of your row's start_col is less than
end_row, the result set will be empty for every possible value of
rand.number.
Don't even know what you expected to get with this query.

RE: [sqlite] Can you use random(*) to retrieve a pseudo random r owfrom 3.2.3?

[Brandon, Nicholas]

>Nick,
>
>I just thought I should clarify my SQL. The select clause should be
>
>select * from MyTbale join...
>
>since the random number is already included in each row of the joined 
>table.

>Dennis Cote

Thanks Dennis for the tip. Did you give it try?

I've tried it and its not selecting any rows after a couple hundred
attempts. I appreciate its "random" but to test I subsequently put in a row
with the min/max random values and that row is still not selected.

CREATE TABLE MyTable(start_col int,end_col int);
INSERT INTO "MyTable" VALUES(-2, 2);
INSERT INTO "MyTable" VALUES(-21, 21);
INSERT INTO "MyTable" VALUES(-21, 21);
INSERT INTO "MyTable" VALUES(-2147483648, 2147483647);

sqlite> select * from MyTable join (select random(*) as number) as rand
where st
art_col >= rand.number and end_col < rand.number;
sqlite>

Regards
Nick


This email and any attachments are confidential to the intended
recipient and may also be privileged. If you are not the intended
recipient please delete it from your system and notify the sender.
You should not copy it or use it for any purpose nor disclose or
distribute its contents to any other person.

[sqlite] CAST documentation error in lang_expr.html

[Ralf Junker]

Hello DRH,

there is a documentation error in lang_expr.html. The current HTML reads:

"A CAST expression changes the datatype of the into the type specified by . can 
be any non-empty type name that if valid for the type in a column definition of 
a CREATE TABLE statement."

The  in the TCL srcipt not properly converted and interpreted as a HTML 
tag which is not displayed by the browser.

Regards,

Ralf 


The Delphi Inspiration product brief:http://www.yunqa.de/delphi/

DIUcl: The Delphi port of the popular UCL Compression Library using the same
   lossless algorithm as the UPX Ultimate Packer for eXecutables. Super-
   fast, realtime decompression. Ratio up to ZIP and BZIP2. Freeware.

Re: [sqlite] Why can i open a textfile?

[Edwin Knoppert]

Wow, that suprises me, i just stepped of the _gettable() call to get me the 
result using prepare and step.

So i finally could read the $NUL BLOB data.
I can't imagne what would be better since a forward only behaviour is the 
best there is.

Besides it's side-effects of not being able to navigate back.

There are people like me they never heard about TCL, i don't know what it is 
and also what schema's are.

It's an area i never visit since it's not familiar to me.
I can not imagne to do things like this not using your API.

I don't think i'm blind for other things but there siomply are programmers 
used to use plain functions.


To make sure you understand, through PowerBASIC i'm able to connect to your 
sqlite through api's.

The issue is that conversion from your api to a BASIC syntax is pretty hard.
Espec. if calls have pointers which are in fact only interesting for 
including the lib into c but is irrellevant to dll users.


Don't get me wrong, it's not to annoy you, just want to show my perspective 
and might even help to consider this for the future.
Like a major rewrite to standard calls and making use of not-so-c syntax 
(pointers/casting or whatever removed, just an example)


(Again: it's not handy to show *sqlite in a param but simply have a byval 
hstmt as long (but then in c syntax :) ) would be much better since it does 
not tell unecessary info that *sqlite is actually a pointer to a structure 
or similar, this might reach more end-users)


I usually keep in mind a 'dumb' VB should be able to access it.
If that works it usually works with anything.

Using wrappers is no option to me unless it's for dotnet (asp.net), i fell 
dumb if i would use a wrapper lib.
Of course the PowerBASIC code being spread for sqlite gives me a headstart 
to access the dll.

But the code is not my taste so i do it myself.

Is there any reason not to stick with prepare() and step()?
Works for me at this time.

:)



- Original Message - 
From: "D. Richard Hipp" <[EMAIL PROTECTED]>

To: 
Sent: Tuesday, August 23, 2005 1:28 AM
Subject: Re: [sqlite] Why can i open a textfile?



On Tue, 2005-08-23 at 00:04 +0200, Edwin Knoppert wrote:
All i meant is that sqlite is created by a c programmer, like my 
colleague

he seems to forget there are different languages but he doesn't care.



Actually, I'm a TCL programmer.  I only resort to C code to write
new TCL extensions.  I personally never use the SQLite C API except
when I am working on internals of the TCL bindings.  I use SQLite
in my professional work a lot, but I always access it through the
TCL bindings.  Even when I am working on SQLite itself, I almost
always access it through the TCL bindings.  (All of the test
scripts are writtne in TCL.)

And this brings up an important point:  I'm increasingly aware of
the fact that the C API to SQLite really wants to be wrapped.  There
are a lot of loose ends that can cause complication and trouble if
you try to use the C API directly in your application.  Things like
the infamous SQLITE_SCHEMA returns.  And the notorious SQLITE_BUSY.
And then the recent conversation about how to tell if the file is
really a database or not.  These kinds of things are best dealt with
once in a wrapper and then forgotten.  You do not want to have to
be worrying about error return codes when you are trying to make
the next great klller app.  Programmers need to save their brain
cycles to focus on the really hard problems, not on handling lots
of pesky return codes.

Most people who are using SQLite successfully have, I imagine,
either written their own wrappers around the core API  (which
is not hard as I do provide you with a lot of helper routines
such as sqlite3_vmprintf and friends) or they are using an existing
wrapper written by someone else.

If you are not doing this - if you are trying to make low-level
SQLite API calls like sqlite3_prepare and sqlite3_step for each
bit of SQL you want to run, then let me suggest that you are using
the library incorrectly.  That is way too much work.  I expose
those calls because they provide you with a lot of control and
give wrapper writers the freedom to do lots of interesting things
with the database.  If you are working on an application that just
needs access to data, sqlite3_prepare and sqlite3_step are way
too low level.  Find a wrapper suitable for your needs and use it
instead.  Or write your own wrapper if an appropriate one can't
be found.  This will result in a program that is easier to write,
easier to maintain, has fewer bugs, and just works better.
--
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] Linker Error when trying to use Sqlite with GCC

[Ulrik Petersen]

Hi Michael,

Michael Gaskins wrote:


I'm working on an application that will require an embedded database
backend and Sqlite is looking to be my prime choice here.

Now, I typically write the GUI and the working code seperately anyways
and tie them together later, so I figured I'd begin work on my backend
that talks with the database.  I've never actually done any previous
database programming so I'm learning as I go here :).

I'm having trouble getting my (very primitive) experimental program to
link however.  Below is my code for test.c:

#include 
#include 

int main (int argc, char **argv) {

 struct sqlite *pilotLog;
 
 pilotLog = sqlite_open("logbook.dat", 0, NULL);


 sqlite_close(pilotLog);
 



How about using sqlite3_open and sqlite3_close ?


HTH

Ulrik P.

--
Ulrik Petersen, Denmark