RE: [sqlite] INSERT INTO with SELECT

[RB Smissaert]

My text file is only an intermediate and I can make the way I want.
I need to move data from Interbase to SQLite. Fastest method sofar is:
IB > ADO recordset
ADO recordset > text file
Import text file with SQLite .import command

Problem with .import is that it doesn't like double quotes.

RBS

-Original Message-
From: Jay Sprenkle [mailto:[EMAIL PROTECTED] 
Sent: 16 November 2006 04:24
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] INSERT INTO with SELECT

You should create your database and your table first.
Do that in sqlite3.exe using your create table statement below.
What format is your text file in? What separates one field from another?


On 11/14/06, RB Smissaert <[EMAIL PROTECTED]> wrote:
> Thanks and in fact I had a look at your text importer about an hour ago.
> Couldn't figure out the parameters though.
> How would these be if I wanted to do this.
>
> Import a text file called ReadCode.txt to a database called ReadCode.db
> Database has one table called ReadCode with these fields:
> SUBJECT_TYPE, READ_CODE, TERM30 , TERM60
>
> SQL to create the table is like this:
>
> create table ReadCode
>   (
> SUBJECT_TYPE   varchar(5),
> READCODE   varchar(5),
> TERM30   varchar(30),
> TERM60varchar(60)
>   );
>
--
SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com


-
To unsubscribe, send email to [EMAIL PROTECTED]

-




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

RE: [sqlite] SQLITE readonly Performance on a CD DB

[Vis Naicker]

[EMAIL PROTECTED] uttered:

> I have 300K ..and the blob .. separate database.. CD 
> media, the performance .. 2 minutes sometime 
> to retreive the result

>>>[Is the cdrom noisy at this point? Making intermittent whirring
noises? 
Some cdroms are noisier than others.]

* I tried different discs in a few machines, including vmware


>
> the blob database... within 1.5sec or less from the CD. ... LED on the
CD rom blinking only occasionally, not 
> constantly like when I copy a file.


>>>[If the LED only lights when data is being transferred, then what
you're 
seeing is the latency of individual reads. This is not surprising, as 
cdroms have horrendously slow seek times. When copying regular files,
the 
file itself is likely to be contiguous on the CD, therefore no seeks are

required. A SQLite database, on the other hand, has tree structure 
scattered around the file. Seeks are common and slow. Vacuuming, as DRH 
suggests, will reduce the seeks as tables will be more contiguous.]

* The database is only populated once , I posted search fields into one
database which performs badly, and posted the text as blobs which
performs well even on CD. I also have a system in place where I can post
the blobs as either sqlite or zip or raw files and I am happy with that
performance

>>>[The OS should shield you from this. One way to possibly increase 
performance is to the prime the OS cache by reading in the CD file in
it's 
entirety (just read the raw file). Hopefully, the file should fit in the

OS's memory cache, and subsequent SQLite reads can be satisfied from the

OS cache, though that may be unfeasable with a database of your size.]

* The records db is 20MB indexed, and the blob is 140MB.

>>>[You might also want to increase the database page size when creating
the 
database in the first place, as you'll have less actual pages and hence 
less seeks for a given database size.]

* I am using a Delphi wrapper, I need to investigate further.

[* sorry first time with outlook I have to set it up later properly]

-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.394 / Virus Database: 268.14.6/535 - Release Date:
15/11/2006
 

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

[sqlite] 回复： [sqlite] Re: Newbie sqlite questions: c heck existence of column

[Linker M Lin]

And mine is:

>>> import sqlite
>>> sqlite.version
'1.1.8'
>>>

 
--
Linker M Lin
[EMAIL PROTECTED]
  ※
  ※※我思故我在※※
  ※

- 原始邮件 
发件人： Florent THIERY <[EMAIL PROTECTED]>
收件人： sqlite-users@sqlite.org
已发送： 2006/11/13(周一), 下午10:09:12
主题： [sqlite] Re: Newbie sqlite questions: check existence of column

I'd add :

python

>>> import sqlite
>>> sqlite.version
'1.0.1'








___ 
抢注雅虎免费邮箱-3.5G容量，20M附件！ 
http://cn.mail.yahoo.com

Re: [sqlite] INSERT INTO with SELECT

[Jay Sprenkle]

You should create your database and your table first.
Do that in sqlite3.exe using your create table statement below.
What format is your text file in? What separates one field from another?


On 11/14/06, RB Smissaert <[EMAIL PROTECTED]> wrote:

Thanks and in fact I had a look at your text importer about an hour ago.
Couldn't figure out the parameters though.
How would these be if I wanted to do this.

Import a text file called ReadCode.txt to a database called ReadCode.db
Database has one table called ReadCode with these fields:
SUBJECT_TYPE, READ_CODE, TERM30 , TERM60

SQL to create the table is like this:

create table ReadCode
  (
SUBJECT_TYPE   varchar(5),
READCODE   varchar(5),
TERM30   varchar(30),
TERM60varchar(60)
  );


--
SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

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

Re: [sqlite] Importing text file via .bat file

[Jay Sprenkle]

On 11/15/06, RB Smissaert <[EMAIL PROTECTED]> wrote:

OK, writing the textfile as inserts and running that is a bit slower than
the .import, but it has one advantage and that is that comma's in the field
are no problem as the values can be enclosed in double-quotes.
With the .import I had to take the comma's out first. There weren't many
comma's and it wasn't a big problem, but it is best to go for accuracy, so
It is a bit better. Is there no way to do .import with the data in double
quotes, so that the comma's are no trouble?

Now the next thing to try is doing inserts directly from VB, from the ADO
recordset. I suppose this should be the fastest. I can think I can do it
with the VB wrapper, but not sure how to it without it. Can I do SQLite3.exe
directly with the insert data as an argument?


You can certainly do this from vb. It's a bit of work though.
You will need to read the data file and parse it yourself,
then insert the data directly. You should wrap all the inserts
in a transaction since it will speed it up greatly (this is true
for any method of doing inserts into sqlite).

You can also run the script to import it but if I recall
correctly vb lets you start a process but it takes more work
to wait for the external process to complete and then to
get it's return code from the operating system. The system
call I was familiar with just started the script running, it
didn't wait for it to finish.

I doubt you will see any significant difference in speed
between the two methods.


--
SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

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

RE: [sqlite] Importing text file via .bat file

[RB Smissaert]

Not sure if Shell can do something like that.
What would the VB code be?

RBS

-Original Message-
From: Clark Christensen [mailto:[EMAIL PROTECTED] 
Sent: 15 November 2006 23:53
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Importing text file via .bat file

Shell

- Original Message 
From: RB Smissaert <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Wednesday, November 15, 2006 2:16:32 PM
Subject: RE: [sqlite] Importing text file via .bat file

>   sqlite3 c:\sqlite\ReadCode.db ".read c:\sqlite\ReadCode.sql"

Not sure how that would work from VBA. Did you mean to run this with Shell
or the Windows API?

RBS


-Original Message-
From: Clark Christensen [mailto:[EMAIL PROTECTED] 
Sent: 15 November 2006 21:37
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Importing text file via .bat file

RBS,

Sorry to jump in late here.  Others have given good advice, but I'm
wondering, since this is all running from VB, why not do all the work in VB
and skip the batch (or cmd) file.  I'm not a VB guy, but I do know it's
pretty powerful.

Are you having some trouble with a VB wrapper for SQLite?  If no, then what
you propose should be as simple as iterating through the IB recordset and
inserting what you need into your SQLite table.  If you are having trouble
with a wrapper, then it seems to me like VB can (and should) do everything
except the actual import.

If you create your SQL script as:

--ReadCode.sql to build and populate ReadCode.db
drop table if exists ReadCode;
create table ReadCode
  (
SUBJECT_TYPE   varchar(5),
READ_CODE   varchar(5),
TERM30   varchar(30),
TERM60  varchar(60)
  );
.mode csv
.import c:\sqlite\ReadCode.txt ReadCode
--END SQL

Then, from VB, you issue a single command like:

sqlite3 c:\sqlite\ReadCode.db ".read c:\sqlite\ReadCode.sql"

and wait for SQLite to finish (or read the exit code, or read SQLite's
stdout output).  If it's a success, there'll be no output from SQLite.

If what you really want is to have one single SQL file to do the job, you
would have your "Recordset to text" step write out each row as an insert
statement into ReadCode.sql, so ReadCode.sql would then look like:

--ReadCode.sql to build and populate ReadCode.db

drop table if exists ReadCode;

create table ReadCode

  (

SUBJECT_TYPE   varchar(5),

READ_CODE   varchar(5),

TERM30   varchar(30),

TERM60  varchar(60)

  );

begin transaction;
insert into ReadCode values (...);
insert into ReadCode values (...);
insert into ReadCode values (...);
...
commit;
--END SQL

Then issue the same command from VB to start the job:

sqlite3 c:\sqlite\ReadCode.db ".read c:\sqlite\ReadCode.sql"

Either way, you would be able to eliminate the batch file, and handle
everything from within VB.

 -Clark
- Original Message 
From: RB Smissaert <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Tuesday, November 14, 2006 3:44:12 PM
Subject: [sqlite] Importing text file via .bat file

Have figure out now what the quickest way is to move data from Interbase to
a SQLite db file:
IB to ADO recordset
Recordset to text
Import the text file with the .import command.

Now I am trying to figure out how to automate the last step with a .bat
file.
What I got sofar is:
Have a SQL file with:

create table ReadCode
  (
SUBJECT_TYPE   varchar(5),
READ_CODE   varchar(5),
TERM30   varchar(30),
TERM60  varchar(60)
  );

Run a .bat file with this:

cd C:\SQLite
del ReadCode.db
type ReadCode.sql | sqlite3 ReadCode.db

Then run from the command prompt:

Cd C:\SQLite  (press return)
SQLite3 ReadCode.db  (press return)
.mode csv(press return)
.import ReadCode.txt ReadCode   (press return)

This runs nice and quick, but how would I combine all this in one .bat file
or how could I run this all from VB? I know very little about .bat files,
but I would think that somehow it must be possible.
Thanks for any assistance.


RBS





-
To unsubscribe, send email to [EMAIL PROTECTED]

-






-
To unsubscribe, send email to [EMAIL PROTECTED]

-





-
To unsubscribe, send email to [EMAIL PROTECTED]

-






-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsubscribe, send email to [EMAIL PROTECTED]

Re: [sqlite] Importing text file via .bat file

[Clark Christensen]

Shell

- Original Message 
From: RB Smissaert <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Wednesday, November 15, 2006 2:16:32 PM
Subject: RE: [sqlite] Importing text file via .bat file

>   sqlite3 c:\sqlite\ReadCode.db ".read c:\sqlite\ReadCode.sql"

Not sure how that would work from VBA. Did you mean to run this with Shell
or the Windows API?

RBS


-Original Message-
From: Clark Christensen [mailto:[EMAIL PROTECTED] 
Sent: 15 November 2006 21:37
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Importing text file via .bat file

RBS,

Sorry to jump in late here.  Others have given good advice, but I'm
wondering, since this is all running from VB, why not do all the work in VB
and skip the batch (or cmd) file.  I'm not a VB guy, but I do know it's
pretty powerful.

Are you having some trouble with a VB wrapper for SQLite?  If no, then what
you propose should be as simple as iterating through the IB recordset and
inserting what you need into your SQLite table.  If you are having trouble
with a wrapper, then it seems to me like VB can (and should) do everything
except the actual import.

If you create your SQL script as:

--ReadCode.sql to build and populate ReadCode.db
drop table if exists ReadCode;
create table ReadCode
  (
SUBJECT_TYPE   varchar(5),
READ_CODE   varchar(5),
TERM30   varchar(30),
TERM60  varchar(60)
  );
.mode csv
.import c:\sqlite\ReadCode.txt ReadCode
--END SQL

Then, from VB, you issue a single command like:

sqlite3 c:\sqlite\ReadCode.db ".read c:\sqlite\ReadCode.sql"

and wait for SQLite to finish (or read the exit code, or read SQLite's
stdout output).  If it's a success, there'll be no output from SQLite.

If what you really want is to have one single SQL file to do the job, you
would have your "Recordset to text" step write out each row as an insert
statement into ReadCode.sql, so ReadCode.sql would then look like:

--ReadCode.sql to build and populate ReadCode.db

drop table if exists ReadCode;

create table ReadCode

  (

SUBJECT_TYPE   varchar(5),

READ_CODE   varchar(5),

TERM30   varchar(30),

TERM60  varchar(60)

  );

begin transaction;
insert into ReadCode values (...);
insert into ReadCode values (...);
insert into ReadCode values (...);
...
commit;
--END SQL

Then issue the same command from VB to start the job:

sqlite3 c:\sqlite\ReadCode.db ".read c:\sqlite\ReadCode.sql"

Either way, you would be able to eliminate the batch file, and handle
everything from within VB.

 -Clark
- Original Message 
From: RB Smissaert <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Tuesday, November 14, 2006 3:44:12 PM
Subject: [sqlite] Importing text file via .bat file

Have figure out now what the quickest way is to move data from Interbase to
a SQLite db file:
IB to ADO recordset
Recordset to text
Import the text file with the .import command.

Now I am trying to figure out how to automate the last step with a .bat
file.
What I got sofar is:
Have a SQL file with:

create table ReadCode
  (
SUBJECT_TYPE   varchar(5),
READ_CODE   varchar(5),
TERM30   varchar(30),
TERM60  varchar(60)
  );

Run a .bat file with this:

cd C:\SQLite
del ReadCode.db
type ReadCode.sql | sqlite3 ReadCode.db

Then run from the command prompt:

Cd C:\SQLite  (press return)
SQLite3 ReadCode.db  (press return)
.mode csv(press return)
.import ReadCode.txt ReadCode   (press return)

This runs nice and quick, but how would I combine all this in one .bat file
or how could I run this all from VB? I know very little about .bat files,
but I would think that somehow it must be possible.
Thanks for any assistance.


RBS





-
To unsubscribe, send email to [EMAIL PROTECTED]

-






-
To unsubscribe, send email to [EMAIL PROTECTED]

-




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





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

RE: [sqlite] Importing text file via .bat file

[RB Smissaert]

OK, writing the textfile as inserts and running that is a bit slower than
the .import, but it has one advantage and that is that comma's in the field
are no problem as the values can be enclosed in double-quotes.
With the .import I had to take the comma's out first. There weren't many
comma's and it wasn't a big problem, but it is best to go for accuracy, so
It is a bit better. Is there no way to do .import with the data in double
quotes, so that the comma's are no trouble?

Now the next thing to try is doing inserts directly from VB, from the ADO
recordset. I suppose this should be the fastest. I can think I can do it
with the VB wrapper, but not sure how to it without it. Can I do SQLite3.exe
directly with the insert data as an argument?

RBS


-Original Message-
From: Clark Christensen [mailto:[EMAIL PROTECTED] 
Sent: 15 November 2006 21:37
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Importing text file via .bat file

RBS,

Sorry to jump in late here.  Others have given good advice, but I'm
wondering, since this is all running from VB, why not do all the work in VB
and skip the batch (or cmd) file.  I'm not a VB guy, but I do know it's
pretty powerful.

Are you having some trouble with a VB wrapper for SQLite?  If no, then what
you propose should be as simple as iterating through the IB recordset and
inserting what you need into your SQLite table.  If you are having trouble
with a wrapper, then it seems to me like VB can (and should) do everything
except the actual import.

If you create your SQL script as:

--ReadCode.sql to build and populate ReadCode.db
drop table if exists ReadCode;
create table ReadCode
  (
SUBJECT_TYPE   varchar(5),
READ_CODE   varchar(5),
TERM30   varchar(30),
TERM60  varchar(60)
  );
.mode csv
.import c:\sqlite\ReadCode.txt ReadCode
--END SQL

Then, from VB, you issue a single command like:

sqlite3 c:\sqlite\ReadCode.db ".read c:\sqlite\ReadCode.sql"

and wait for SQLite to finish (or read the exit code, or read SQLite's
stdout output).  If it's a success, there'll be no output from SQLite.

If what you really want is to have one single SQL file to do the job, you
would have your "Recordset to text" step write out each row as an insert
statement into ReadCode.sql, so ReadCode.sql would then look like:

--ReadCode.sql to build and populate ReadCode.db

drop table if exists ReadCode;

create table ReadCode

  (

SUBJECT_TYPE   varchar(5),

READ_CODE   varchar(5),

TERM30   varchar(30),

TERM60  varchar(60)

  );

begin transaction;
insert into ReadCode values (...);
insert into ReadCode values (...);
insert into ReadCode values (...);
...
commit;
--END SQL

Then issue the same command from VB to start the job:

sqlite3 c:\sqlite\ReadCode.db ".read c:\sqlite\ReadCode.sql"

Either way, you would be able to eliminate the batch file, and handle
everything from within VB.

 -Clark
- Original Message 
From: RB Smissaert <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Tuesday, November 14, 2006 3:44:12 PM
Subject: [sqlite] Importing text file via .bat file

Have figure out now what the quickest way is to move data from Interbase to
a SQLite db file:
IB to ADO recordset
Recordset to text
Import the text file with the .import command.

Now I am trying to figure out how to automate the last step with a .bat
file.
What I got sofar is:
Have a SQL file with:

create table ReadCode
  (
SUBJECT_TYPE   varchar(5),
READ_CODE   varchar(5),
TERM30   varchar(30),
TERM60  varchar(60)
  );

Run a .bat file with this:

cd C:\SQLite
del ReadCode.db
type ReadCode.sql | sqlite3 ReadCode.db

Then run from the command prompt:

Cd C:\SQLite  (press return)
SQLite3 ReadCode.db  (press return)
.mode csv(press return)
.import ReadCode.txt ReadCode   (press return)

This runs nice and quick, but how would I combine all this in one .bat file
or how could I run this all from VB? I know very little about .bat files,
but I would think that somehow it must be possible.
Thanks for any assistance.


RBS





-
To unsubscribe, send email to [EMAIL PROTECTED]

-






-
To unsubscribe, send email to [EMAIL PROTECTED]

-




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

RE: [sqlite] Importing text file via .bat file

[RB Smissaert]

>   sqlite3 c:\sqlite\ReadCode.db ".read c:\sqlite\ReadCode.sql"

Not sure how that would work from VBA. Did you mean to run this with Shell
or the Windows API?

RBS


-Original Message-
From: Clark Christensen [mailto:[EMAIL PROTECTED] 
Sent: 15 November 2006 21:37
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Importing text file via .bat file

RBS,

Sorry to jump in late here.  Others have given good advice, but I'm
wondering, since this is all running from VB, why not do all the work in VB
and skip the batch (or cmd) file.  I'm not a VB guy, but I do know it's
pretty powerful.

Are you having some trouble with a VB wrapper for SQLite?  If no, then what
you propose should be as simple as iterating through the IB recordset and
inserting what you need into your SQLite table.  If you are having trouble
with a wrapper, then it seems to me like VB can (and should) do everything
except the actual import.

If you create your SQL script as:

--ReadCode.sql to build and populate ReadCode.db
drop table if exists ReadCode;
create table ReadCode
  (
SUBJECT_TYPE   varchar(5),
READ_CODE   varchar(5),
TERM30   varchar(30),
TERM60  varchar(60)
  );
.mode csv
.import c:\sqlite\ReadCode.txt ReadCode
--END SQL

Then, from VB, you issue a single command like:

sqlite3 c:\sqlite\ReadCode.db ".read c:\sqlite\ReadCode.sql"

and wait for SQLite to finish (or read the exit code, or read SQLite's
stdout output).  If it's a success, there'll be no output from SQLite.

If what you really want is to have one single SQL file to do the job, you
would have your "Recordset to text" step write out each row as an insert
statement into ReadCode.sql, so ReadCode.sql would then look like:

--ReadCode.sql to build and populate ReadCode.db

drop table if exists ReadCode;

create table ReadCode

  (

SUBJECT_TYPE   varchar(5),

READ_CODE   varchar(5),

TERM30   varchar(30),

TERM60  varchar(60)

  );

begin transaction;
insert into ReadCode values (...);
insert into ReadCode values (...);
insert into ReadCode values (...);
...
commit;
--END SQL

Then issue the same command from VB to start the job:

sqlite3 c:\sqlite\ReadCode.db ".read c:\sqlite\ReadCode.sql"

Either way, you would be able to eliminate the batch file, and handle
everything from within VB.

 -Clark
- Original Message 
From: RB Smissaert <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Tuesday, November 14, 2006 3:44:12 PM
Subject: [sqlite] Importing text file via .bat file

Have figure out now what the quickest way is to move data from Interbase to
a SQLite db file:
IB to ADO recordset
Recordset to text
Import the text file with the .import command.

Now I am trying to figure out how to automate the last step with a .bat
file.
What I got sofar is:
Have a SQL file with:

create table ReadCode
  (
SUBJECT_TYPE   varchar(5),
READ_CODE   varchar(5),
TERM30   varchar(30),
TERM60  varchar(60)
  );

Run a .bat file with this:

cd C:\SQLite
del ReadCode.db
type ReadCode.sql | sqlite3 ReadCode.db

Then run from the command prompt:

Cd C:\SQLite  (press return)
SQLite3 ReadCode.db  (press return)
.mode csv(press return)
.import ReadCode.txt ReadCode   (press return)

This runs nice and quick, but how would I combine all this in one .bat file
or how could I run this all from VB? I know very little about .bat files,
but I would think that somehow it must be possible.
Thanks for any assistance.


RBS





-
To unsubscribe, send email to [EMAIL PROTECTED]

-






-
To unsubscribe, send email to [EMAIL PROTECTED]

-




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

RE: [sqlite] Importing text file via .bat file

[RB Smissaert]

Hi Clark,

I started out with doing everything from VBA, setting up the ADO recordset
with data from IB, then creating a SQLiteDb.Recordset (I use the VB wrapper
from Terra Informatica) then pushing data from the ADO recordset to the
SQLiteDB recordset and then doing updates every so many cycles of the loop.
This was a bit too slow and ADO recorset to text and importing that text
into the SQLite DB is much faster.
Haven't looked yet into doing direct UPDATES or INSERTS, without the
SQLiteDB recordset and I suspect that is faster then the first method.
I am not sure if I do UPDATES or INSERTS if this faster with the VB wrapper
or with direct commands to SQLite. I take it the last is faster.
Doing away with the text file should make it faster although writing that
file from the ADO recordset is pretty fast.
We are not talking about enormous amounts of data here, about 14 rows
and 4 fields. Eventually though if this works out well it might be up to a
few million rows.

OK, will just have to do some further experimenting. At least I got
something going now that is fast already and works well.

RBS


-Original Message-
From: Clark Christensen [mailto:[EMAIL PROTECTED] 
Sent: 15 November 2006 21:37
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Importing text file via .bat file

RBS,

Sorry to jump in late here.  Others have given good advice, but I'm
wondering, since this is all running from VB, why not do all the work in VB
and skip the batch (or cmd) file.  I'm not a VB guy, but I do know it's
pretty powerful.

Are you having some trouble with a VB wrapper for SQLite?  If no, then what
you propose should be as simple as iterating through the IB recordset and
inserting what you need into your SQLite table.  If you are having trouble
with a wrapper, then it seems to me like VB can (and should) do everything
except the actual import.

If you create your SQL script as:

--ReadCode.sql to build and populate ReadCode.db
drop table if exists ReadCode;
create table ReadCode
  (
SUBJECT_TYPE   varchar(5),
READ_CODE   varchar(5),
TERM30   varchar(30),
TERM60  varchar(60)
  );
.mode csv
.import c:\sqlite\ReadCode.txt ReadCode
--END SQL

Then, from VB, you issue a single command like:

sqlite3 c:\sqlite\ReadCode.db ".read c:\sqlite\ReadCode.sql"

and wait for SQLite to finish (or read the exit code, or read SQLite's
stdout output).  If it's a success, there'll be no output from SQLite.

If what you really want is to have one single SQL file to do the job, you
would have your "Recordset to text" step write out each row as an insert
statement into ReadCode.sql, so ReadCode.sql would then look like:

--ReadCode.sql to build and populate ReadCode.db

drop table if exists ReadCode;

create table ReadCode

  (

SUBJECT_TYPE   varchar(5),

READ_CODE   varchar(5),

TERM30   varchar(30),

TERM60  varchar(60)

  );

begin transaction;
insert into ReadCode values (...);
insert into ReadCode values (...);
insert into ReadCode values (...);
...
commit;
--END SQL

Then issue the same command from VB to start the job:

sqlite3 c:\sqlite\ReadCode.db ".read c:\sqlite\ReadCode.sql"

Either way, you would be able to eliminate the batch file, and handle
everything from within VB.

 -Clark
- Original Message 
From: RB Smissaert <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Tuesday, November 14, 2006 3:44:12 PM
Subject: [sqlite] Importing text file via .bat file

Have figure out now what the quickest way is to move data from Interbase to
a SQLite db file:
IB to ADO recordset
Recordset to text
Import the text file with the .import command.

Now I am trying to figure out how to automate the last step with a .bat
file.
What I got sofar is:
Have a SQL file with:

create table ReadCode
  (
SUBJECT_TYPE   varchar(5),
READ_CODE   varchar(5),
TERM30   varchar(30),
TERM60  varchar(60)
  );

Run a .bat file with this:

cd C:\SQLite
del ReadCode.db
type ReadCode.sql | sqlite3 ReadCode.db

Then run from the command prompt:

Cd C:\SQLite  (press return)
SQLite3 ReadCode.db  (press return)
.mode csv(press return)
.import ReadCode.txt ReadCode   (press return)

This runs nice and quick, but how would I combine all this in one .bat file
or how could I run this all from VB? I know very little about .bat files,
but I would think that somehow it must be possible.
Thanks for any assistance.


RBS





-
To unsubscribe, send email to [EMAIL PROTECTED]

-






-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsub

Re: [sqlite] Importing text file via .bat file

[Clark Christensen]

RBS,

Sorry to jump in late here.  Others have given good advice, but I'm wondering, 
since this is all running from VB, why not do all the work in VB and skip the 
batch (or cmd) file.  I'm not a VB guy, but I do know it's pretty powerful.

Are you having some trouble with a VB wrapper for SQLite?  If no, then what you 
propose should be as simple as iterating through the IB recordset and inserting 
what you need into your SQLite table.  If you are having trouble with a 
wrapper, then it seems to me like VB can (and should) do everything except the 
actual import.

If you create your SQL script as:

--ReadCode.sql to build and populate ReadCode.db
drop table if exists ReadCode;
create table ReadCode
  (
SUBJECT_TYPE   varchar(5),
READ_CODE   varchar(5),
TERM30   varchar(30),
TERM60  varchar(60)
  );
.mode csv
.import c:\sqlite\ReadCode.txt ReadCode
--END SQL

Then, from VB, you issue a single command like:

sqlite3 c:\sqlite\ReadCode.db ".read c:\sqlite\ReadCode.sql"

and wait for SQLite to finish (or read the exit code, or read SQLite's stdout 
output).  If it's a success, there'll be no output from SQLite.

If what you really want is to have one single SQL file to do the job, you would 
have your "Recordset to text" step write out each row as an insert statement 
into ReadCode.sql, so ReadCode.sql would then look like:

--ReadCode.sql to build and populate ReadCode.db

drop table if exists ReadCode;

create table ReadCode

  (

SUBJECT_TYPE   varchar(5),

READ_CODE   varchar(5),

TERM30   varchar(30),

TERM60  varchar(60)

  );

begin transaction;
insert into ReadCode values (...);
insert into ReadCode values (...);
insert into ReadCode values (...);
...
commit;
--END SQL

Then issue the same command from VB to start the job:

sqlite3 c:\sqlite\ReadCode.db ".read c:\sqlite\ReadCode.sql"

Either way, you would be able to eliminate the batch file, and handle 
everything from within VB.

 -Clark
- Original Message 
From: RB Smissaert <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Tuesday, November 14, 2006 3:44:12 PM
Subject: [sqlite] Importing text file via .bat file

Have figure out now what the quickest way is to move data from Interbase to
a SQLite db file:
IB to ADO recordset
Recordset to text
Import the text file with the .import command.

Now I am trying to figure out how to automate the last step with a .bat
file.
What I got sofar is:
Have a SQL file with:

create table ReadCode
  (
SUBJECT_TYPE   varchar(5),
READ_CODE   varchar(5),
TERM30   varchar(30),
TERM60  varchar(60)
  );

Run a .bat file with this:

cd C:\SQLite
del ReadCode.db
type ReadCode.sql | sqlite3 ReadCode.db

Then run from the command prompt:

Cd C:\SQLite  (press return)
SQLite3 ReadCode.db  (press return)
.mode csv(press return)
.import ReadCode.txt ReadCode   (press return)

This runs nice and quick, but how would I combine all this in one .bat file
or how could I run this all from VB? I know very little about .bat files,
but I would think that somehow it must be possible.
Thanks for any assistance.


RBS




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





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

RE: [sqlite] Importing text file via .bat file

[RB Smissaert]

The application is run from a VBA .xla add-in with a number of VB6 ActiveX
helper files. Good point, I could delete the imported text file and use that
As the trigger to notify VBA that all is done.
Not sure VBA can monitor a rename event other that by running a loop and
checking for the existence of a file.

RBS


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: 15 November 2006 20:11
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Importing text file via .bat file

I've noticed that you mention both VB and VBS.  Is this something that you
are eventually going to run from an actual VB application?  If so, you might
be able to use a directory notification on the directory where the import
is, then just trigger an action when the import file is deleted, which the
.bat file could do after the import is done.  Or, if you don't want to be
that drastic, monitor the specific file for a Rename event.  Of course, I
don't know that VB 6 supports this, but I know that VB.Net does.

--
Eric Pankoke
Founder / Lead Developer
Point Of Light Software
http://www.polsoftware.com/

 -- Original message --
From: "RB Smissaert" <[EMAIL PROTECTED]>
> Of course the .bat file could make a little file to notify VB.
> Still, running a loop to check for this is not ideal.
> 
> RBS
> 
> -Original Message-
> From: Griggs, Donald [mailto:[EMAIL PROTECTED] 
> Sent: 15 November 2006 19:37
> To: sqlite-users@sqlite.org
> Subject: RE: [sqlite] Importing text file via .bat file
> 
>  
> Echo  .mode csv   >MyCommands.tmp
> Echo  .import ReadCode.txt ReadCode  >>MyCommands.tmp
> 
> Sqlite3 ReadCode.db ".read myCommands.tmp"
> 
> =
> -Original Message-
> From: RB Smissaert [mailto:[EMAIL PROTECTED] 
> Sent: Wednesday, November 15, 2006 1:13 PM
> To: sqlite-users@sqlite.org
> Subject: RE: [sqlite] Importing text file via .bat file
> 
> Hi Donald,
> 
> Thanks, but I don't quite get it yet.
> What is in this file MyCommand.tmp?
> Is there no way to put the whole thing in on .bat file or even better
> run the whole sequence from VB?
> 
> RBS
> ===
> ===
> 
> Regarding: "What is in this file MyCommand.tmp?"
> 
> The file, "MyCommand.tmp" would be a temporary file created by your
> batch file itself as it runs.
> 
> 
> 
> Regarding: "...one batch file...?"
> 
> In a sense, the entire logic *is* in one batch file, but it's true that
> a small, temporary file is created as part of the process, and you can
> delete it when done.  Unless you're doing this thousands of times an
> hour, the extra time introduced by having a temporary file will be
> minimal.
> 
> Kees Nyut showed a nicer syntax, one that I *thought* I had problems
> with for early versions of sqlite.exe under windows, but seems to work
> well now:
>  Instead of:
>   Sqlite3 ReadCode.db ".read myCommands.tmp"
>  use:
>   Sqlite3 ReadCode.db  
> 
> Re: "..or VB?" 
> I don't use Visual Basic, so I'm no expert, but the advantage of
> using the sqlite command-line utility (called sqlite3.exe) is that the
> code for parsing comma-delimited strings is built in.   If you have your
> routines for this, or want to incorporate the code from the sqlite
> command utility source, then you can certainly do everything within VB.
> 
> 
> Returning to the "single batch file" question:
> 
>  I've actually used a sneaky means of doing this, but it assumes
> you're pretty familiar with batch files.  It's more straightforward for
> unix scripts, but for windows, we start it with a line of
> /*
> Which gives a (harmless) error when the batch file starts, but it allows
> us to treat the top portion of the file as a set of inputs to sqlite
> command utility.
> 
> -beginning of file Mybatchfile.bat
> /*
> GOTO  :startit
>   This is a combination sql and batch file to ...
> */
> 
> /* Put all your sql commands and DOT commands here */
> drop table if exists ReadCode;
> create table ReadCode (
>   SUBJECT_TYPE varchar(5),
>   READ_CODEvarchar(5),
>   TERM30   varchar(30),
>   TERM60   varchar(60)
> );
> .mode csv
> .import ReadCode.txt ReadCode
> 
> -- end sqlite with .quit, but batch file continues
> .quit
> 
> == nothing runs in this space till we get to "startit"
> 
> 
> :Startit
> Rem  Beginning of batch file commands
> @echo off
> Rem clear the harmless error off the screen
> Cls
> 
> Rem [Do any other preparatory batch file commands here]
> 
> Rem Invoke sqlite3 command utility and submit the top of this batch file
> as a set of commands
> Rem we assume this entire batch file is named "Mybatchfile" and it's in
> our current directory.
> 
> Sqlite3 myData.db   
> Rem [ put here any windows batch commands we want after Sqlite3 leaves
> with the .quit command ]
> 
> end of mybatchfile.bat
> 
> 
> 
>
--

RE: [sqlite] Importing text file via .bat file

[RB Smissaert]

There is no actual VBS file. I run it like this from VBA:

Sub UpdateReadSQLite2(bShowSQL As Boolean)

   Dim oShell

   UpdateReadTextFile bShowSQL, True

   Application.StatusBar = _
   "transferring the data from ReadCodeNoQuotes.txt to the SQLite DB"
   Set oShell = CreateObject("WSCript.shell")
   oShell.Run "cmd /C C:\SQLite\ReadCode.bat", 0
   Set oShell = Nothing

   Application.StatusBar = False

End Sub

Still, these are minor details and I will figure that out.

RBS


-Original Message-
From: Kees Nuyt [mailto:[EMAIL PROTECTED] 
Sent: 15 November 2006 20:30
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Importing text file via .bat file

On Wed, 15 Nov 2006 19:46:32 -, you wrote:

>Kees,
>
>Just one other thing needed.
>In the .sql file is there a way to notify VB that the text import is
>finished? I run the .bat file now from VBS, so it won't be visible.
>I couldn't see anything suitable in the dot commands to tell VB.
>
>RBS

It is done when the .bat returns to VBS, I guess.
You could try to execute like this (just guessing, i never use
VBS): 
start /w ReadCode.bat

Or add this to the sql script:
 at the top:
if exist signal.txt del signal.txt

 at the bottom:
.output signal.txt
select SUBJECT_TYPE from ReadCode limit 1;

and detect the presence of the file signal.txt in your VBS
script. Or do everything in your .bat (or better, .cmd).
-- 
  (  Kees Nuyt
  )
c[_]


-
To unsubscribe, send email to [EMAIL PROTECTED]

-




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

RE: [sqlite] Importing text file via .bat file

[RB Smissaert]

Didn't know about .cmd scripts (not that I know much about .bat) and will
have a look at that. There is a slim chance that some of my users are still
on Win98, so that could be a problem.

Will have a look at Sqlite3Explorer as well.
Only started with SQLite a few days ago, but I can see it is great software,
especially as I only need as a local, temporary, one user database.
Shame I don't know C.

RBS


-Original Message-
From: Kees Nuyt [mailto:[EMAIL PROTECTED] 
Sent: 15 November 2006 20:22
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Importing text file via .bat file

On Wed, 15 Nov 2006 19:15:38 -, you wrote:

>Hi Kees,
>
>Thanks a lot for that. All working perfect now.
>I think all this should be on the SQLite site as 
>it took me a long time to get this fixed.

http://www.sqlite.org/sqlite.html and the .help command taught
me the commands, http://www.sqlite.org/lang.html the SQL as
understood by sqlite; the rest is straight shell scripting,
redirecting and piping. Which is nasty at first, but quite
powerful once you get used to it. Worth to learn, really.
But not really on topic on the sqlite site.

But here are some hints: On windows NT4, 2000, XP and 2003
you're better off with writing .cmd scripts instead of .bat.
.cmd is interpreted by CMD.EXE, which is much more powerful than
command.exe, which is (usually) interpreted by the legacy
command.exe command shell. 

Add some tools like make (for process management) and gawk (for
data cleaning, input filtering and transformations), and there's
your database factory ;)
CMD.EXE even allows you to use forward slashes, you just have to
put paths and filenames between double quotes as in
"disk:/path/filename.extension".

For easy table browsing and testing I use the excellent
Sqlite3Explorer by Mike Cariotoglou (see
http://www.sqlite.org/cvstrac/wiki?p=ManagementTools for a
pointer), which even includes a query editor and a report
generator.

>Nogmaals bedankt.

Veel plezier!

>RBS
-- 
  (  Kees Nuyt
  )
c[_]


-
To unsubscribe, send email to [EMAIL PROTECTED]

-




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

Re: [sqlite] Importing text file via .bat file

[Kees Nuyt]

On Wed, 15 Nov 2006 19:46:32 -, you wrote:

>Kees,
>
>Just one other thing needed.
>In the .sql file is there a way to notify VB that the text import is
>finished? I run the .bat file now from VBS, so it won't be visible.
>I couldn't see anything suitable in the dot commands to tell VB.
>
>RBS

It is done when the .bat returns to VBS, I guess.
You could try to execute like this (just guessing, i never use
VBS): 
start /w ReadCode.bat

Or add this to the sql script:
 at the top:
if exist signal.txt del signal.txt

 at the bottom:
.output signal.txt
select SUBJECT_TYPE from ReadCode limit 1;

and detect the presence of the file signal.txt in your VBS
script. Or do everything in your .bat (or better, .cmd).
-- 
  (  Kees Nuyt
  )
c[_]

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

Re: [sqlite] Sqlite books

[Rich Shepard]

On Wed, 15 Nov 2006, Michael Young wrote:


Does the book offer any details on compiling that might make the purchase
worthwhile?


Mike,

  I don't know. But, on page 31 "SQLite on POSIX Systems," he starts with,
"If you are using Mac OS 10.4 ("Tiger") or greater, you already have SQLite
on your system. If not, there are several routes you can take to install
it."

Rich

--
Richard B. Shepard, Ph.D.   |The Environmental Permitting
Applied Ecosystem Services, Inc.(TM)|Accelerator
 Voice: 503-667-4517  Fax: 503-667-8863

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

Re: [sqlite] Importing text file via .bat file

[Kees Nuyt]

On Wed, 15 Nov 2006 19:15:38 -, you wrote:

>Hi Kees,
>
>Thanks a lot for that. All working perfect now.
>I think all this should be on the SQLite site as 
>it took me a long time to get this fixed.

http://www.sqlite.org/sqlite.html and the .help command taught
me the commands, http://www.sqlite.org/lang.html the SQL as
understood by sqlite; the rest is straight shell scripting,
redirecting and piping. Which is nasty at first, but quite
powerful once you get used to it. Worth to learn, really.
But not really on topic on the sqlite site.

But here are some hints: On windows NT4, 2000, XP and 2003
you're better off with writing .cmd scripts instead of .bat.
.cmd is interpreted by CMD.EXE, which is much more powerful than
command.exe, which is (usually) interpreted by the legacy
command.exe command shell. 

Add some tools like make (for process management) and gawk (for
data cleaning, input filtering and transformations), and there's
your database factory ;)
CMD.EXE even allows you to use forward slashes, you just have to
put paths and filenames between double quotes as in
"disk:/path/filename.extension".

For easy table browsing and testing I use the excellent
Sqlite3Explorer by Mike Cariotoglou (see
http://www.sqlite.org/cvstrac/wiki?p=ManagementTools for a
pointer), which even includes a query editor and a report
generator.

>Nogmaals bedankt.

Veel plezier!

>RBS
-- 
  (  Kees Nuyt
  )
c[_]

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

Re: [sqlite] Sqlite books

[Michael Young]

I've had PHP5 for two months now and have not been able to compile it  
successfully on my Mac so that SQLite 3.x files can be accessed. I  
had hoped to find help in compiling in Mike Owens' book.


Does the book offer any details on compiling that might make the  
purchase worthwhile? I've tried in vain to find Mike Owens' email  
address to ask this simple question. Can't find it though.


Mike


On Nov 15, 2006, at 8:38 AM, Christian Smith wrote:


John Gunnarsson uttered:


Hi,

I'm about to buy a book about Sqlite. I'm coding in C++ so I'm  
looking

for the best book to suite my needs.

In my local bookstore I have:
The Definitive Guide to SQLite, APress
http://www.apress.com/book/bookDisplay.html?bID=10130

and

SQLite, Sams
http://safari.samspublishing.com/067232685X

Have anyone of you a opinion of the books above, or maybe an even
better recommendation?



The former is more up to date, covering SQLite 3.x. The latter  
covers only 2.x, which is unfortunate.


I have both books, and the former is more comprehensive IMO. If you  
want just the one book, go for the former.





//John




Christian

--
/"\
\ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
 X   - AGAINST MS ATTACHMENTS
/ \

-- 
---

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





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

RE: [sqlite] Importing text file via .bat file

[epankoke]

I've noticed that you mention both VB and VBS.  Is this something that you are 
eventually going to run from an actual VB application?  If so, you might be 
able to use a directory notification on the directory where the import is, then 
just trigger an action when the import file is deleted, which the .bat file 
could do after the import is done.  Or, if you don't want to be that drastic, 
monitor the specific file for a Rename event.  Of course, I don't know that VB 
6 supports this, but I know that VB.Net does.

--
Eric Pankoke
Founder / Lead Developer
Point Of Light Software
http://www.polsoftware.com/

 -- Original message --
From: "RB Smissaert" <[EMAIL PROTECTED]>
> Of course the .bat file could make a little file to notify VB.
> Still, running a loop to check for this is not ideal.
> 
> RBS
> 
> -Original Message-
> From: Griggs, Donald [mailto:[EMAIL PROTECTED] 
> Sent: 15 November 2006 19:37
> To: sqlite-users@sqlite.org
> Subject: RE: [sqlite] Importing text file via .bat file
> 
>  
> Echo  .mode csv   >MyCommands.tmp
> Echo  .import ReadCode.txt ReadCode  >>MyCommands.tmp
> 
> Sqlite3 ReadCode.db ".read myCommands.tmp"
> 
> =
> -Original Message-
> From: RB Smissaert [mailto:[EMAIL PROTECTED] 
> Sent: Wednesday, November 15, 2006 1:13 PM
> To: sqlite-users@sqlite.org
> Subject: RE: [sqlite] Importing text file via .bat file
> 
> Hi Donald,
> 
> Thanks, but I don't quite get it yet.
> What is in this file MyCommand.tmp?
> Is there no way to put the whole thing in on .bat file or even better
> run the whole sequence from VB?
> 
> RBS
> ===
> ===
> 
> Regarding: "What is in this file MyCommand.tmp?"
> 
> The file, "MyCommand.tmp" would be a temporary file created by your
> batch file itself as it runs.
> 
> 
> 
> Regarding: "...one batch file...?"
> 
> In a sense, the entire logic *is* in one batch file, but it's true that
> a small, temporary file is created as part of the process, and you can
> delete it when done.  Unless you're doing this thousands of times an
> hour, the extra time introduced by having a temporary file will be
> minimal.
> 
> Kees Nyut showed a nicer syntax, one that I *thought* I had problems
> with for early versions of sqlite.exe under windows, but seems to work
> well now:
>  Instead of:
>   Sqlite3 ReadCode.db ".read myCommands.tmp"
>  use:
>   Sqlite3 ReadCode.db  
> 
> Re: "..or VB?" 
> I don't use Visual Basic, so I'm no expert, but the advantage of
> using the sqlite command-line utility (called sqlite3.exe) is that the
> code for parsing comma-delimited strings is built in.   If you have your
> routines for this, or want to incorporate the code from the sqlite
> command utility source, then you can certainly do everything within VB.
> 
> 
> Returning to the "single batch file" question:
> 
>  I've actually used a sneaky means of doing this, but it assumes
> you're pretty familiar with batch files.  It's more straightforward for
> unix scripts, but for windows, we start it with a line of
> /*
> Which gives a (harmless) error when the batch file starts, but it allows
> us to treat the top portion of the file as a set of inputs to sqlite
> command utility.
> 
> -beginning of file Mybatchfile.bat
> /*
> GOTO  :startit
>   This is a combination sql and batch file to ...
> */
> 
> /* Put all your sql commands and DOT commands here */
> drop table if exists ReadCode;
> create table ReadCode (
>   SUBJECT_TYPE varchar(5),
>   READ_CODEvarchar(5),
>   TERM30   varchar(30),
>   TERM60   varchar(60)
> );
> .mode csv
> .import ReadCode.txt ReadCode
> 
> -- end sqlite with .quit, but batch file continues
> .quit
> 
> == nothing runs in this space till we get to "startit"
> 
> 
> :Startit
> Rem  Beginning of batch file commands
> @echo off
> Rem clear the harmless error off the screen
> Cls
> 
> Rem [Do any other preparatory batch file commands here]
> 
> Rem Invoke sqlite3 command utility and submit the top of this batch file
> as a set of commands
> Rem we assume this entire batch file is named "Mybatchfile" and it's in
> our current directory.
> 
> Sqlite3 myData.db   
> Rem [ put here any windows batch commands we want after Sqlite3 leaves
> with the .quit command ]
> 
> end of mybatchfile.bat
> 
> 
> 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> 
> -
> 
> 
> 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 



-
To unsubscribe, send email to [EMAIL PROTECTE

RE: [sqlite] Importing text file via .bat file

[RB Smissaert]

Of course the .bat file could make a little file to notify VB.
Still, running a loop to check for this is not ideal.

RBS

-Original Message-
From: Griggs, Donald [mailto:[EMAIL PROTECTED] 
Sent: 15 November 2006 19:37
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Importing text file via .bat file

 
Echo  .mode csv   >MyCommands.tmp
Echo  .import ReadCode.txt ReadCode  >>MyCommands.tmp

Sqlite3 ReadCode.db ".read myCommands.tmp"

=
-Original Message-
From: RB Smissaert [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, November 15, 2006 1:13 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Importing text file via .bat file

Hi Donald,

Thanks, but I don't quite get it yet.
What is in this file MyCommand.tmp?
Is there no way to put the whole thing in on .bat file or even better
run the whole sequence from VB?

RBS
===
===

Regarding: "What is in this file MyCommand.tmp?"

The file, "MyCommand.tmp" would be a temporary file created by your
batch file itself as it runs.



Regarding: "...one batch file...?"

In a sense, the entire logic *is* in one batch file, but it's true that
a small, temporary file is created as part of the process, and you can
delete it when done.  Unless you're doing this thousands of times an
hour, the extra time introduced by having a temporary file will be
minimal.

Kees Nyut showed a nicer syntax, one that I *thought* I had problems
with for early versions of sqlite.exe under windows, but seems to work
well now:
 Instead of:
Sqlite3 ReadCode.db ".read myCommands.tmp"
 use:
Sqlite3 ReadCode.db 

RE: [sqlite] Importing text file via .bat file

[RB Smissaert]

Will see if I can get that .bat file working.
Thanks again. One thing I will need to figure out is how to notify VB that
all is finished. I suppose I could run a loop at the end of my VB procedure
checking for something, but I couldn't see anything suitable (amongst the
dot commands) that VB could check for.
Maybe the .bat file should show at the end to tell the user things are
finished. I now run the .bat file from VBS, so I can make it run invisible.

RBS


-Original Message-
From: Griggs, Donald [mailto:[EMAIL PROTECTED] 
Sent: 15 November 2006 19:37
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Importing text file via .bat file

 
Echo  .mode csv   >MyCommands.tmp
Echo  .import ReadCode.txt ReadCode  >>MyCommands.tmp

Sqlite3 ReadCode.db ".read myCommands.tmp"

=
-Original Message-
From: RB Smissaert [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, November 15, 2006 1:13 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Importing text file via .bat file

Hi Donald,

Thanks, but I don't quite get it yet.
What is in this file MyCommand.tmp?
Is there no way to put the whole thing in on .bat file or even better
run the whole sequence from VB?

RBS
===
===

Regarding: "What is in this file MyCommand.tmp?"

The file, "MyCommand.tmp" would be a temporary file created by your
batch file itself as it runs.



Regarding: "...one batch file...?"

In a sense, the entire logic *is* in one batch file, but it's true that
a small, temporary file is created as part of the process, and you can
delete it when done.  Unless you're doing this thousands of times an
hour, the extra time introduced by having a temporary file will be
minimal.

Kees Nyut showed a nicer syntax, one that I *thought* I had problems
with for early versions of sqlite.exe under windows, but seems to work
well now:
 Instead of:
Sqlite3 ReadCode.db ".read myCommands.tmp"
 use:
Sqlite3 ReadCode.db 

RE: [sqlite] Importing text file via .bat file

[RB Smissaert]

Kees,

Just one other thing needed.
In the .sql file is there a way to notify VB that the text import is
finished? I run the .bat file now from VBS, so it won't be visible.
I couldn't see anything suitable in the dot commands to tell VB.

RBS



-Original Message-
From: Kees Nuyt [mailto:[EMAIL PROTECTED] 
Sent: 15 November 2006 18:54
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Importing text file via .bat file

On Tue, 14 Nov 2006 23:44:12 -, you wrote:

>Have figure out now what the quickest way is to move data from Interbase to
>a SQLite db file:
>IB to ADO recordset
>Recordset to text
>Import the text file with the .import command.
>
>Now I am trying to figure out how to automate the last step with a .bat
>file.
>What I got sofar is:
>Have a SQL file with:
>
>create table ReadCode
>  (
>SUBJECT_TYPE   varchar(5),
>READ_CODE   varchar(5),
>TERM30   varchar(30),
>TERM60   varchar(60)
>  );
>
>Run a .bat file with this:
>
>cd C:\SQLite
>del ReadCode.db
>type ReadCode.sql | sqlite3 ReadCode.db
>
>Then run from the command prompt:
>
>Cd C:\SQLite  (press return)
>SQLite3 ReadCode.db  (press return)
>.mode csv(press return)
>.import ReadCode.txt ReadCode   (press return)
>
>This runs nice and quick, but how would I combine all this in one .bat file
>or how could I run this all from VB? I know very little about .bat files,
>but I would think that somehow it must be possible.
>Thanks for any assistance.
>
>
>RBS

Input scripts for the sqlite command line utility aren't
restricted to SQL, you can also put 'dot commands' in it.

Try this (some day you may be working on an existing
database instead of deleting it first, so I would drop the table
first):

--- file ReadCode.sql begin  ---
drop table if exists ReadCode;
create table ReadCode (
  SUBJECT_TYPE varchar(5),
  READ_CODEvarchar(5),
  TERM30   varchar(30),
  TERM60   varchar(60)
);
.mode csv
.import ReadCode.txt ReadCode
--- file ReadCode.sql end ---

And this is your shell script:
--- file ReadCode.bat begin ---
cd  C:\SQLite
del ReadCode.db
SQLite3 ReadCode.db 

RE: [sqlite] Importing text file via .bat file

[Griggs, Donald]

 
Echo  .mode csv   >MyCommands.tmp
Echo  .import ReadCode.txt ReadCode  >>MyCommands.tmp

Sqlite3 ReadCode.db ".read myCommands.tmp"

=
-Original Message-
From: RB Smissaert [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, November 15, 2006 1:13 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Importing text file via .bat file

Hi Donald,

Thanks, but I don't quite get it yet.
What is in this file MyCommand.tmp?
Is there no way to put the whole thing in on .bat file or even better
run the whole sequence from VB?

RBS
===
===

Regarding: "What is in this file MyCommand.tmp?"

The file, "MyCommand.tmp" would be a temporary file created by your
batch file itself as it runs.



Regarding: "...one batch file...?"

In a sense, the entire logic *is* in one batch file, but it's true that
a small, temporary file is created as part of the process, and you can
delete it when done.  Unless you're doing this thousands of times an
hour, the extra time introduced by having a temporary file will be
minimal.

Kees Nyut showed a nicer syntax, one that I *thought* I had problems
with for early versions of sqlite.exe under windows, but seems to work
well now:
 Instead of:
Sqlite3 ReadCode.db ".read myCommands.tmp"
 use:
Sqlite3 ReadCode.db 

RE: [sqlite] Importing text file via .bat file

[RB Smissaert]

Hi Kees,

Thanks a lot for that. All working perfect now.
I think all this should be on the SQLite site as it took me a long time
to get this fixed.
Nogmaals bedankt.

RBS


-Original Message-
From: Kees Nuyt [mailto:[EMAIL PROTECTED] 
Sent: 15 November 2006 18:54
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Importing text file via .bat file

On Tue, 14 Nov 2006 23:44:12 -, you wrote:

>Have figure out now what the quickest way is to move data from Interbase to
>a SQLite db file:
>IB to ADO recordset
>Recordset to text
>Import the text file with the .import command.
>
>Now I am trying to figure out how to automate the last step with a .bat
>file.
>What I got sofar is:
>Have a SQL file with:
>
>create table ReadCode
>  (
>SUBJECT_TYPE   varchar(5),
>READ_CODE   varchar(5),
>TERM30   varchar(30),
>TERM60   varchar(60)
>  );
>
>Run a .bat file with this:
>
>cd C:\SQLite
>del ReadCode.db
>type ReadCode.sql | sqlite3 ReadCode.db
>
>Then run from the command prompt:
>
>Cd C:\SQLite  (press return)
>SQLite3 ReadCode.db  (press return)
>.mode csv(press return)
>.import ReadCode.txt ReadCode   (press return)
>
>This runs nice and quick, but how would I combine all this in one .bat file
>or how could I run this all from VB? I know very little about .bat files,
>but I would think that somehow it must be possible.
>Thanks for any assistance.
>
>
>RBS

Input scripts for the sqlite command line utility aren't
restricted to SQL, you can also put 'dot commands' in it.

Try this (some day you may be working on an existing
database instead of deleting it first, so I would drop the table
first):

--- file ReadCode.sql begin  ---
drop table if exists ReadCode;
create table ReadCode (
  SUBJECT_TYPE varchar(5),
  READ_CODEvarchar(5),
  TERM30   varchar(30),
  TERM60   varchar(60)
);
.mode csv
.import ReadCode.txt ReadCode
--- file ReadCode.sql end ---

And this is your shell script:
--- file ReadCode.bat begin ---
cd  C:\SQLite
del ReadCode.db
SQLite3 ReadCode.db 

Re: [sqlite] Query planner confusion

[Jean-Paul Calderone]

On Wed, 15 Nov 2006 03:28:50 +, [EMAIL PROTECTED] wrote:

Jean-Paul Calderone <[EMAIL PROTECTED]> wrote:

I've hit a case where SQLite is selecting a suboptimal index for my query and I want to 
do something to tell it to use a different index.  I'm familiar with the "+" 
trick to confuse the optimizer but I can't see how it would apply to this case.


Is there any way to convince SQLite to use the index I want?



The deficiency you have found in the optimizer is known to me.
I just haven't had the opportunity to fix it.

Usually if you run ANALYZE after putting some data in to the
database, the problem goes away.


Alright, thanks.

Jean-Paul

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

Re: [sqlite] fts2 selecting terms

[Scott Hess]

On 11/14/06, Vikram Bhandoh <[EMAIL PROTECTED]> wrote:

I'm using fts2 to index some documents. And I want to get a list of all the
distinct terms that are in those documents. Is there a seperate table which
holds the terms or is there a function maybe like snippet, offset?


Just to clarify - you don't want the distinct terms in _a_ document,
you want the set of terms in the index?

Either way, the current code does not support that, but it should at
some point allow you to generate a list of the unique terms in index.
Given the current structures, this would require processing the entire
index, so it's unlikely to happen in the near term.  Longer-term,
though, I think it would be worthwhile to store meta information such
as term frequency, for use in query optimization.  Once that's in
place, it's likely that returning the set of terms in the index would
become pretty easy.

-scott

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

Re: [sqlite] Importing text file via .bat file

[Kees Nuyt]

On Tue, 14 Nov 2006 23:44:12 -, you wrote:

>Have figure out now what the quickest way is to move data from Interbase to
>a SQLite db file:
>IB to ADO recordset
>Recordset to text
>Import the text file with the .import command.
>
>Now I am trying to figure out how to automate the last step with a .bat
>file.
>What I got sofar is:
>Have a SQL file with:
>
>create table ReadCode
>  (
>SUBJECT_TYPE   varchar(5),
>READ_CODE   varchar(5),
>TERM30   varchar(30),
>TERM60   varchar(60)
>  );
>
>Run a .bat file with this:
>
>cd C:\SQLite
>del ReadCode.db
>type ReadCode.sql | sqlite3 ReadCode.db
>
>Then run from the command prompt:
>
>Cd C:\SQLite  (press return)
>SQLite3 ReadCode.db  (press return)
>.mode csv(press return)
>.import ReadCode.txt ReadCode   (press return)
>
>This runs nice and quick, but how would I combine all this in one .bat file
>or how could I run this all from VB? I know very little about .bat files,
>but I would think that somehow it must be possible.
>Thanks for any assistance.
>
>
>RBS

Input scripts for the sqlite command line utility aren't
restricted to SQL, you can also put 'dot commands' in it.

Try this (some day you may be working on an existing
database instead of deleting it first, so I would drop the table
first):

--- file ReadCode.sql begin  ---
drop table if exists ReadCode;
create table ReadCode (
  SUBJECT_TYPE varchar(5),
  READ_CODEvarchar(5),
  TERM30   varchar(30),
  TERM60   varchar(60)
);
.mode csv
.import ReadCode.txt ReadCode
--- file ReadCode.sql end ---

And this is your shell script:
--- file ReadCode.bat begin ---
cd  C:\SQLite
del ReadCode.db
SQLite3 ReadCode.db 

Re: [sqlite] Handling null characters in blob data

[John Stanton]

Vivien Malerba wrote:
On 11/13/06, Shivshankar Subramani - TLS , Chennai <[EMAIL PROTECTED]> 
wrote:



Hi all,

> SQLite version 2.8 and earlier could not (easily) store binary
> data - data with embedded \000 characters.  Thus the encode/decode
> routines were provide to transform data so that it contained no
> \000 characters.
>
> SQLite version 3.0 can store binary data without difficulty.

This is what  I read in the site

http://www.mail-archive.com/sqlite-users@sqlite.org/msg04332.html  
. but
i am having difficulty in storing data with null characters in it.Is 
there

any specific method in which i can solve this problem?



I personally use the X'AABBCCDD' syntax to store BLOBS where AABBCCDD
is the hexadecimal representation of my binary data (one byte
translated into ist 2 digits hexa equivalent). for example:
"insert into mytable values (1, X'AABBCCDD');"

Regards,

Vivien

- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 




That is exapnsion, not compression!

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

RE: [sqlite] Importing text file via .bat file

[RB Smissaert]

Hi Donald,

Thanks, but I don't quite get it yet.
What is in this file MyCommand.tmp?
Is there no way to put the whole thing in on .bat file or even better run
the whole sequence from VB?

RBS


-Original Message-
From: Griggs, Donald [mailto:[EMAIL PROTECTED] 
Sent: 15 November 2006 15:02
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Importing text file via .bat file



Regarding:

"...but how would I combine all this in one .bat file ...?"

Hello, RBS.  The following is in windows commandline syntax:


Echo  .mode csv   >MyCommands.tmp
Echo  .import ReadCode.txt ReadCode  >>MyCommands.tmp

Sqlite3 ReadCode.db ".read myCommands.tmp"


  or, if your file of sqlite commands is not in the same directory:

Sqlite3 ReadCode.db ".read c:\\myDirectory\\myCommands.tmp"\

You can use your file of commands, of course, to do many other things,
such as creating indices after the import, creating views, tallying
statistics, etc.


[opinions expressed are my own, and not those of my company]



-
To unsubscribe, send email to [EMAIL PROTECTED]

-




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

Re: [sqlite] Re: unrecognized token error

[Florent THIERY]

THANK YOU A LOT, it works like a charm now :)

In fact, my hash wasn't calculated the same way before, and the hash wasn't
starting with a number, so i did'nt realized why it was malfunctioning.

Have a nice evening

Florent

On 11/15/06, Igor Tandetnik <[EMAIL PROTECTED]> wrote:


Florent THIERY <[EMAIL PROTECTED]> wrote:
> "create table %s (profileID char(32) etc...)" % (tableID)   # Where
> tableID is a 32 char hexstring (hash)
> 4b0c25789ab124628444951dd995fdcc in this case

Table name must be a proper identifier (begin with letter or underscore,
consist of letters, digits and underscores), or else enclosed in double
quotes. You attempt to create one whose name begins with a digit. Make
it

"create table T%s (profileID char(32) etc...)" % (tableID)
# or
"create table \"%s\" (profileID char(32) etc...)" % (tableID)

Igor Tandetnik



-
To unsubscribe, send email to [EMAIL PROTECTED]

-

RE: [sqlite] unrecognized token error

[Robert Simpson]

> -Original Message-
> From: Florent THIERY [mailto:[EMAIL PROTECTED] 
> Sent: Wednesday, November 15, 2006 9:27 AM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] unrecognized token error
> 
> Hi
> 
> My request:
> 
> "create table %s (profileID char(32) etc...)" % (tableID)   # 
> Where tableID
> is a 32 char hexstring (hash) 
> 4b0c25789ab124628444951dd995fdcc in this case
> 
> create table 4b0c25789ab124628444951dd995fdcc (profileID 
> char(32) , _soi
> char(7) etc. )
> 
> The unswear: _sqlite.DatabaseError: unrecognized token:
> "4b0c25789ab124628444951dd995fdcc"
> 
> Why the heck? Any idea?

Table names cannot start with a number. 

Robert



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

Re: [sqlite] Sqlite books

[Christian Smith]

John Gunnarsson uttered:


Hi,

I'm about to buy a book about Sqlite. I'm coding in C++ so I'm looking
for the best book to suite my needs.

In my local bookstore I have:
The Definitive Guide to SQLite, APress
http://www.apress.com/book/bookDisplay.html?bID=10130

and

SQLite, Sams
http://safari.samspublishing.com/067232685X

Have anyone of you a opinion of the books above, or maybe an even
better recommendation?



The former is more up to date, covering SQLite 3.x. The latter covers only 
2.x, which is unfortunate.


I have both books, and the former is more comprehensive IMO. If you want 
just the one book, go for the former.





//John




Christian

--
/"\
\ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
 X   - AGAINST MS ATTACHMENTS
/ \

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

[sqlite] Re: unrecognized token error

[Igor Tandetnik]

Florent THIERY <[EMAIL PROTECTED]> wrote:

"create table %s (profileID char(32) etc...)" % (tableID)   # Where
tableID is a 32 char hexstring (hash)
4b0c25789ab124628444951dd995fdcc in this case


Table name must be a proper identifier (begin with letter or underscore, 
consist of letters, digits and underscores), or else enclosed in double 
quotes. You attempt to create one whose name begins with a digit. Make 
it


"create table T%s (profileID char(32) etc...)" % (tableID)
# or
"create table \"%s\" (profileID char(32) etc...)" % (tableID)

Igor Tandetnik 



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

[sqlite] unrecognized token error

[Florent THIERY]

Hi

My request:

"create table %s (profileID char(32) etc...)" % (tableID)   # Where tableID
is a 32 char hexstring (hash) 4b0c25789ab124628444951dd995fdcc in this case

create table 4b0c25789ab124628444951dd995fdcc (profileID char(32) , _soi
char(7) etc. )

The unswear: _sqlite.DatabaseError: unrecognized token:
"4b0c25789ab124628444951dd995fdcc"

Why the heck? Any idea?

The database isn't created.
I tried %r and '%s' , the error gets away, but the table isn't created

Are there any length limitations on the name, the length of the request,
?.

Thanks for your help

Regards

Florent

Re: [sqlite] autoincrement and integer primary key

[gwithrow]

In the FAQ's on the web site it indicated that when the primary key is
autoincrement, the data type is a signed 64 bit number.  Has this been your
experience?


Gary W Withrow
Senior Software Engineer
HID Global
11674 N. Huron
Northglenn, CO 80234
303-453-4020 - Office
303-378-7647 - Cell

"We are smarter today than we were yesterday" R. Sjodin

This e-mail is confidential and may be privileged and/or proprietary.  If you 
are not the intended recipient, any review, disclosure, copying, or use of this 
e-mail is prohibited.

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

Re: [sqlite] SQLITE readonly Performance on a CD DB

[Christian Smith]

[EMAIL PROTECTED] uttered:

I have 300K records in my database and the blob text file (invoices) in 
a separate database. I query the database over a network or USB stick 
and the performance is good. However when I moved my database to CD 
media, the performance dropped drastically. It takes 2 minutes sometime 
to retreive the result. The CPU is not busy during the time, and the 
memory usage increases slowly.



Is the cdrom noisy at this point? Making intermittent whirring noises? 
Some cdroms are noisier than others.





When the user selects a row, the system queries the blob database, and 
here I am able to retreive the text, convert it into a tiff file, and 
load it into my program within 1.5sec or less from the CD. Copying the 
database to the hard drive and querying that resumes with normal 
performance. Querying the CD version (immeadiately) and the performance 
lags again. The LED on the CD rom blinking only occasionally, not 
constantly like when I copy a file.



If the LED only lights when data is being transferred, then what you're 
seeing is the latency of individual reads. This is not surprising, as 
cdroms have horrendously slow seek times. When copying regular files, the 
file itself is likely to be contiguous on the CD, therefore no seeks are 
required. A SQLite database, on the other hand, has tree structure 
scattered around the file. Seeks are common and slow. Vacuuming, as DRH 
suggests, will reduce the seeks as tables will be more contiguous.


The OS should shield you from this. One way to possibly increase 
performance is to the prime the OS cache by reading in the CD file in it's 
entirety (just read the raw file). Hopefully, the file should fit in the 
OS's memory cache, and subsequent SQLite reads can be satisfied from the 
OS cache, though that may be unfeasable with a database of your size.


You might also want to increase the database page size when creating the 
database in the first place, as you'll have less actual pages and hence 
less seeks for a given database size.





Both databases have 300k records, the first(invoice no indexed) has 8 
retrieval fields, while the second has just the ID and blob. The average 
row size is 60-80 bytes, while the blob has on average 500bytes. I am 
looking to improve the performance, and reasons for the failings. It 
does sound like cacheing mentioned in the thread ...





Only in that the required data is not in the cache, and has to be bought 
in from high latency media.



Christian


--
/"\
\ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
 X   - AGAINST MS ATTACHMENTS
/ \

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

RE: [sqlite] Importing text file via .bat file

[Griggs, Donald]

Regarding:

"...but how would I combine all this in one .bat file ...?"

Hello, RBS.  The following is in windows commandline syntax:


Echo  .mode csv   >MyCommands.tmp
Echo  .import ReadCode.txt ReadCode  >>MyCommands.tmp

Sqlite3 ReadCode.db ".read myCommands.tmp"


  or, if your file of sqlite commands is not in the same directory:

Sqlite3 ReadCode.db ".read c:\\myDirectory\\myCommands.tmp"\

You can use your file of commands, of course, to do many other things,
such as creating indices after the import, creating views, tallying
statistics, etc.


[opinions expressed are my own, and not those of my company]


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

Re: [sqlite] index in CREATE TABLE

[Cécilia Vigny]

Mario Frasca a écrit :

Cécilia Vigny wrote:

With MySQL, it's possible to declare an index in CREATE TABLE, using 
KEY, like in this example :


CREATE TABLE  table_name (
 id int(6) NOT NULL auto_increment,
 field_name date default NULL,
 PRIMARY KEY  (id),
 KEY idx_field_name (fieldname),
) ;

I want to know if there is an equivalence with SQLite ?


maybe this is what you want:

CREATE TABLE  table_name (
id integer PRIMARY KEY NOT NULL,
field_name date default NULL
) ;
CREATE INDEX idx_field_name ON table_name(field_name);

also: have a look at http://www.sqlite.org/lang.html (SQL As 
Understood By SQLite)


(editing your code you forgot an underscore and left an extra comma)

regards,
Mario

Thank you for your correction, it is just a piece of my code that I had 
changed for the instance so it's not a problem, but shame on me ;)
What you suggest is what I had thought, I wondered whether there were 
not a solution which make it possible to do with just one request...

I think that I will use CREATE INDEX separately.


- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 






Ce message est protégé par les règles relatives au secret des correspondances. 
Il est donc établi à destination exclusive de son destinataire. Celui-ci peut 
donc contenir des informations confidentielles. La divulgation de ces 
informations est à ce titre rigoureusement interdite. Si vous avez reçu ce 
message par erreur, merci de le renvoyer à l'expéditeur dont l'adresse e-mail 
figure ci-dessus et de détruire le message ainsi que toute pièce jointe.

This message is protected by the secrecy of correspondence rules. Therefore, 
this message is intended solely for the attention of the addressee. This 
message may contain privileged or confidential information, as such the 
disclosure of these informations is strictly forbidden. If, by mistake, you 
have received this message, please return this message to the addressser whose 
e-mail address is written above and destroy this message and all files attached.



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

Re: [sqlite] Sqlite books

[Rich Shepard]

On Wed, 15 Nov 2006, John Gunnarsson wrote:


The Definitive Guide to SQLite, APress
http://www.apress.com/book/bookDisplay.html?bID=10130



SQLite, Sams


John,

  Check the publication dates and versions they cover.

  I bought a copy of the former and like it a lot. It explained many things
and is a good reference. Not all is covered, but a quick trip to the web
site fills in the blanks.

  My one complaint about the book, and I wrote the publisher about this, is
the index. It's almost completely useless; I've not seen too many technical
books with such a poor index. I find more things by searching the table of
contents and the pages than I do with the index. The publisher never
responded to my comments.

  FWIW, I do know something about writing indices. Springer-Verlag did not
hire a professional indexer for my book so I had to reseach and learn how to
do it. It is almost as difficult to write a good index as it is to write a
good book. So, I am highly sensitive to useful indices.

Rich

--
Richard B. Shepard, Ph.D.   |The Environmental Permitting
Applied Ecosystem Services, Inc.(TM)|Accelerator
 Voice: 503-667-4517  Fax: 503-667-8863

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

Re: [sqlite] index in CREATE TABLE

[Mario Frasca]

Cécilia Vigny wrote:

With MySQL, it's possible to declare an index in CREATE TABLE, using 
KEY, like in this example :


CREATE TABLE  table_name (
 id int(6) NOT NULL auto_increment,
 field_name date default NULL,
 PRIMARY KEY  (id),
 KEY idx_field_name (fieldname),
) ;

I want to know if there is an equivalence with SQLite ?


maybe this is what you want:

CREATE TABLE  table_name (
id integer PRIMARY KEY NOT NULL,
field_name date default NULL
) ;
CREATE INDEX idx_field_name ON table_name(field_name);

also: have a look at http://www.sqlite.org/lang.html (SQL As Understood 
By SQLite)


(editing your code you forgot an underscore and left an extra comma)

regards,
Mario


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

Re: [sqlite] Re: index in CREATE TABLE

[Cécilia Vigny]

Igor Tandetnik a écrit :

Cécilia Vigny 
wrote:

With MySQL, it's possible to declare an index in CREATE TABLE, using
KEY, like in this example :

CREATE TABLE  table_name (
 id int(6) NOT NULL auto_increment,
 field_name date default NULL,
 PRIMARY KEY  (id),
 KEY idx_field_name (fieldname),
) ;

I want to know if there is an equivalence with SQLite ?


You can put PRIMARY KEY and UNIQUE constraints either on an individual 
column, or on the table (possibly listing several columns). Both 
implicitly create indexes to enforce the constraint. As far as I can 
tell, there is no way to create a non-unique index implicitly, without 
separate CREATE INDEX statement.


Igor Tandetnik


Thank you !


- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 






Ce message est protégé par les règles relatives au secret des correspondances. 
Il est donc établi à destination exclusive de son destinataire. Celui-ci peut 
donc contenir des informations confidentielles. La divulgation de ces 
informations est à ce titre rigoureusement interdite. Si vous avez reçu ce 
message par erreur, merci de le renvoyer à l'expéditeur dont l'adresse e-mail 
figure ci-dessus et de détruire le message ainsi que toute pièce jointe.

This message is protected by the secrecy of correspondence rules. Therefore, 
this message is intended solely for the attention of the addressee. This 
message may contain privileged or confidential information, as such the 
disclosure of these informations is strictly forbidden. If, by mistake, you 
have received this message, please return this message to the addressser whose 
e-mail address is written above and destroy this message and all files attached.



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

[sqlite] Re: index in CREATE TABLE

[Igor Tandetnik]

Cécilia Vigny 
wrote:

With MySQL, it's possible to declare an index in CREATE TABLE, using
KEY, like in this example :

CREATE TABLE  table_name (
 id int(6) NOT NULL auto_increment,
 field_name date default NULL,
 PRIMARY KEY  (id),
 KEY idx_field_name (fieldname),
) ;

I want to know if there is an equivalence with SQLite ?


You can put PRIMARY KEY and UNIQUE constraints either on an individual 
column, or on the table (possibly listing several columns). Both 
implicitly create indexes to enforce the constraint. As far as I can 
tell, there is no way to create a non-unique index implicitly, without 
separate CREATE INDEX statement.


Igor Tandetnik 



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

[sqlite] index in CREATE TABLE

[Cécilia Vigny]

Hi,

With MySQL, it's possible to declare an index in CREATE TABLE, using 
KEY, like in this example :


CREATE TABLE  table_name (
 id int(6) NOT NULL auto_increment,
 field_name date default NULL,
 PRIMARY KEY  (id),
 KEY idx_field_name (fieldname),
) ;

I want to know if there is an equivalence with SQLite ?


Ce message est prot?g? par les r?gles relatives au secret des correspondances. 
Il est donc ?tabli ? destination exclusive de son destinataire. Celui-ci peut 
donc contenir des informations confidentielles. La divulgation de ces 
informations est ? ce titre rigoureusement interdite. Si vous avez re?u ce 
message par erreur, merci de le renvoyer ? l'exp?diteur dont l'adresse e-mail 
figure ci-dessus et de d?truire le message ainsi que toute pi?ce jointe.

This message is protected by the secrecy of correspondence rules. Therefore, 
this message is intended solely for the attention of the addressee. This 
message may contain privileged or confidential information, as such the 
disclosure of these informations is strictly forbidden. If, by mistake, you 
have received this message, please return this message to the addressser whose 
e-mail address is written above and destroy this message and all files attached.



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

RE: Re[4]: [sqlite] Re: Re: Handling null characters in blob data

[Shivshankar Subramani - TLS , Chennai]

Hi Teg,

Thanks for your snipet!

With Regards,


SHIVSHANKER S.


HCL Technologies Limited,  

Chennai-600058
Tel: +91-44-43935000 

Extn-5029 Mobile-9884656906
Email: [EMAIL PROTECTED]



-Original Message-
From: Teg [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, November 15, 2006 7:22 PM
To: Shivshankar Subramani - TLS , Chennai
Subject: Re[4]: [sqlite] Re: Re: Handling null characters in blob data


Hello Chennai,

Wednesday, November 15, 2006, 3:09:08 AM, you wrote:

SSTC> Hey I guess you should know more of compression algorithm before u 
SSTC> call one stupid

SSTC> With Regards,


SSTC> SHIVSHANKER S.


SSTC> HCL Technologies Limited,

SSTC> Chennai-600058
SSTC> Tel: +91-44-43935000

SSTC> Extn-5029 Mobile-9884656906
SSTC> Email: [EMAIL PROTECTED]



SSTC> -Original Message-
SSTC> From: Gussimulator [mailto:[EMAIL PROTECTED]
SSTC> Sent: Wednesday, November 15, 2006 12:34 PM
SSTC> To: sqlite-users@sqlite.org
SSTC> Subject: Re: Re[2]: [sqlite] Re: Re: Handling null characters in blob
data


SSTC> What kind of compression algorithm leaves out null characters? every
char
SSTC> will be taking a byte, its just stupid.



SSTC> - Original Message - 
SSTC> From: "Shivshankar Subramani - TLS , Chennai" <[EMAIL PROTECTED]>
SSTC> To: 
SSTC> Sent: Wednesday, November 15, 2006 2:02 AM
SSTC> Subject: RE: Re[2]: [sqlite] Re: Re: Handling null characters in blob
data


SSTC> Hi Teg,

SSTC> Can u please send me the sample code

SSTC> Thanks in advance

SSTC> With Regards,


SSTC> SHIVSHANKER S.


SSTC> HCL Technologies Limited,

SSTC> Chennai-600058
SSTC> Tel: +91-44-43935000

SSTC> Extn-5029 Mobile-9884656906
SSTC> Email: [EMAIL PROTECTED]



SSTC> -Original Message-
SSTC> From: Teg [mailto:[EMAIL PROTECTED]
SSTC> Sent: Wednesday, November 15, 2006 3:26 AM
SSTC> To: Shivshankar Subramani - TLS , Chennai
SSTC> Subject: Re[2]: [sqlite] Re: Re: Handling null characters in blob data


SSTC> Hello Chennai,

SSTC> Tuesday, November 14, 2006, 9:20:10 AM, you wrote:


SSTC>> Hi,

SSTC>> Actually im trying to store a compressed value,which has lots of
SSTC>> null characters.When I try to store it into the database as a blob
SSTC>> item ,only the characters before null gets stored rest does not.I
SSTC>> want to store the entire compressed buffer. Is it possible?

SSTC>> BLOB Data stored in the table ->PK...

SSTC>> Expected data to be stored
SSTC>>   PK.
SSTC>>   ...Ɲn5
SSTC>>   n»(tm)g..
SSTC>>   .è.
SSTC>>   ..simpl
SSTC>>   e.datUT
SSTC>>   ...û.ZE
SSTC>>   û.ZEû.Z
SSTC>>   Eí'KNÃ0
SSTC>>   .?S6Hoe
SSTC>>   Eå}ã.6U
SSTC>>   "¤B¨HHå
SSTC>>   !õ..6n2
SSTC>>   ¤-.ÇÄN"
SSTC>>   r..ì8DO
SSTC>>   .â.E¤Í£
SSTC>>   [EMAIL PROTECTED]
SSTC>>   è÷÷..«
SSTC>>   Y.¬½EURHÒ
SSTC>>   Û¨«.¨.
SSTC>>   Ü.=Ê}.M
SSTC>>   Æg.ê;.
SSTC>>   .-fº|lg
SSTC>>   p-®N®;






SSTC>> Shivshankar Subramani - TLS , Chennai 
SSTC>> <[EMAIL PROTECTED]> wrote:
>>> I did follow the procedure to store the blob data.I have no problem
>>> in excuting it but my problem is that when my data is of the below
>>> type in memory

SSTC>> [snip]

>>> Where there are lots of null character in the buffer the sqlite fails
>>> to take them as values

SSTC>> What makes you think so? Exactly what are you doing, what results
SSTC>> do you expect, what results do you get, and how the two differ?

SSTC>> Igor Tandetnik


SSTC>>
SSTC>

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

SSTC>> -
SSTC>> DISCLAIMER
SSTC>> The contents of this e-mail and any attachment(s) are confidential
SSTC>> and intended for the

SSTC>> named recipient(s) only. It shall not attach any liability on the
SSTC>> originator or HCL or its

SSTC>> affiliates. Any views or opinions presented in this email are 
SSTC>> solely those of the author and

SSTC>> may not necessarily reflect the opinions of HCL or its affiliates.
SSTC>> Any form of reproduction,

SSTC>> dissemination, copying, disclosure, modification, distribution and
SSTC>> / or publication of this

SSTC>> message without the prior written consent of the author of this
SSTC>> e-mail
SSTC> is strictly

SSTC>> prohibited. If you have received this email in error please delete
SSTC>> it and notify the sender

SSTC>> immediately. Before opening any mail and attachments please check
SSTC>> them for viruses and

SSTC>> defect.

SSTC>>
SSTC>

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

SSTC> -

SSTC> I store JPG files in Sqlite DB's without any issues. You can't treat
them
SSTC> like text though which is what it sounds like you're doing. You really
have
SSTC> two choices, encode them as text using something like UU, Mime or
Sqli

Re[4]: [sqlite] Re: Re: Handling null characters in blob data

[Teg]

Hello Chennai,

Wednesday, November 15, 2006, 3:09:08 AM, you wrote:

SSTC> Hey I guess you should know more of compression algorithm before u call 
one
SSTC> stupid

SSTC> With Regards,


SSTC> SHIVSHANKER S.


SSTC> HCL Technologies Limited,  

SSTC> Chennai-600058
SSTC> Tel: +91-44-43935000 

SSTC> Extn-5029 Mobile-9884656906
SSTC> Email: [EMAIL PROTECTED]



SSTC> -Original Message-
SSTC> From: Gussimulator [mailto:[EMAIL PROTECTED] 
SSTC> Sent: Wednesday, November 15, 2006 12:34 PM
SSTC> To: sqlite-users@sqlite.org
SSTC> Subject: Re: Re[2]: [sqlite] Re: Re: Handling null characters in blob data


SSTC> What kind of compression algorithm leaves out null characters? every char
SSTC> will be taking a byte, its just stupid.



SSTC> - Original Message - 
SSTC> From: "Shivshankar Subramani - TLS , Chennai" <[EMAIL PROTECTED]>
SSTC> To: 
SSTC> Sent: Wednesday, November 15, 2006 2:02 AM
SSTC> Subject: RE: Re[2]: [sqlite] Re: Re: Handling null characters in blob data


SSTC> Hi Teg,

SSTC> Can u please send me the sample code

SSTC> Thanks in advance

SSTC> With Regards,


SSTC> SHIVSHANKER S.


SSTC> HCL Technologies Limited,

SSTC> Chennai-600058
SSTC> Tel: +91-44-43935000

SSTC> Extn-5029 Mobile-9884656906
SSTC> Email: [EMAIL PROTECTED]



SSTC> -Original Message-
SSTC> From: Teg [mailto:[EMAIL PROTECTED]
SSTC> Sent: Wednesday, November 15, 2006 3:26 AM
SSTC> To: Shivshankar Subramani - TLS , Chennai
SSTC> Subject: Re[2]: [sqlite] Re: Re: Handling null characters in blob data


SSTC> Hello Chennai,

SSTC> Tuesday, November 14, 2006, 9:20:10 AM, you wrote:


SSTC>> Hi,

SSTC>> Actually im trying to store a compressed value,which has lots of
SSTC>> null characters.When I try to store it into the database as a blob
SSTC>> item ,only the characters before null gets stored rest does not.I
SSTC>> want to store the entire compressed buffer. Is it possible?

SSTC>> BLOB Data stored in the table ->PK...

SSTC>> Expected data to be stored
SSTC>>   PK.
SSTC>>   ...Ɲn5
SSTC>>   n»(tm)g..
SSTC>>   .è.
SSTC>>   ..simpl
SSTC>>   e.datUT
SSTC>>   ...û.ZE
SSTC>>   û.ZEû.Z
SSTC>>   Eí'KNÃ0
SSTC>>   .?S6Hoe
SSTC>>   Eå}ã.6U
SSTC>>   "¤B¨HHå
SSTC>>   !õ..6n2
SSTC>>   ¤-.ÇÄN"
SSTC>>   r..ì8DO
SSTC>>   .â.E¤Í£
SSTC>>   [EMAIL PROTECTED]
SSTC>>   è÷÷..«
SSTC>>   Y.¬½EURHÒ
SSTC>>   Û¨«.¨.
SSTC>>   Ü.=Ê}.M
SSTC>>   Æg.ê;.
SSTC>>   .-fº|lg
SSTC>>   p-®N®;






SSTC>> Shivshankar Subramani - TLS , Chennai 
SSTC>> <[EMAIL PROTECTED]> wrote:
>>> I did follow the procedure to store the blob data.I have no problem
>>> in excuting it but my problem is that when my data is of the below
>>> type in memory

SSTC>> [snip]

>>> Where there are lots of null character in the buffer the sqlite fails
>>> to take them as values

SSTC>> What makes you think so? Exactly what are you doing, what results
SSTC>> do you expect, what results do you get, and how the two differ?

SSTC>> Igor Tandetnik


SSTC>>
SSTC> 

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

SSTC>> -
SSTC>> DISCLAIMER
SSTC>> The contents of this e-mail and any attachment(s) are confidential
SSTC>> and intended for the

SSTC>> named recipient(s) only. It shall not attach any liability on the
SSTC>> originator or HCL or its

SSTC>> affiliates. Any views or opinions presented in this email are 
SSTC>> solely those of the author and

SSTC>> may not necessarily reflect the opinions of HCL or its affiliates.
SSTC>> Any form of reproduction,

SSTC>> dissemination, copying, disclosure, modification, distribution and
SSTC>> / or publication of this

SSTC>> message without the prior written consent of the author of this
SSTC>> e-mail
SSTC> is strictly

SSTC>> prohibited. If you have received this email in error please delete
SSTC>> it and notify the sender

SSTC>> immediately. Before opening any mail and attachments please check
SSTC>> them for viruses and

SSTC>> defect.

SSTC>>
SSTC> 

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

SSTC> -

SSTC> I store JPG files in Sqlite DB's without any issues. You can't treat them
SSTC> like text though which is what it sounds like you're doing. You really 
have
SSTC> two choices, encode them as text using something like UU, Mime or Sqlite's
SSTC> built in encoding or used the advanced API calls to bind the binary data 
as
SSTC> blobs when inserting and retrieving them using the same mechanism.

SSTC> I use the later, compile the query with parameters and bind the blobs of
SSTC> binary data after the fact before I insert.

SSTC> It's all in the API documents. I can send you sample code if you wish.

here's a simplified one

   C

Re: [sqlite] autoincrement and integer primary key

[Mario Frasca]

just a few explainatory notes from me to myself, hoping that they might 
be useful for the community...


again about integer primary key autoincrement, with some details about 
last_insert_rowid() and sqlite_sequence.


---

each table has an integer primary key, that you explictly ask for it or 
not.  this field is called "oid" and possibly also in other ways...


if you explicitly declare a field "integer primary key" (literally!), 
the integer primary key (which would be created anyways) becomes also 
visible under the name you chose for the field.


the function last_insert_rowid() returns the value of the integer 
primary key of the last inserted record.  (the oid, which may coincide 
with your integer primary key).


if you declare a field "integer primary key autoincrement" (literally, 
non case sensitive, in this order!), some sort of sequence is created to 
insure that no value for primary key is ever reused.  real sequences do 
not exist in sqlite3, so the reserved table sqlite_sequence is used to 
associate a table name to the highest value already used.  the record 
relative to the table is created when the first record is inserted into 
the table.


when you insert a record into a table and leave the task of choosing the 
oid to sqlite, sqlite will look into the table to get the highest oid 
currently in use (or into the sqlite_sequence table if you used 
"autoincrement"), increment it by one and use this value (and possibly 
update the sqlite_sequence table).


just to make a silly example:

sqlite> CREATE TABLE test(
  ...> f INTEGER PRIMARY KEY AUTOINCREMENT,
  ...> v int default 0);
sqlite> INSERT INTO "test" VALUES(2, 0);
sqlite> INSERT INTO "test" VALUES(1, 1);
sqlite> select last_insert_rowid();
1
sqlite> SELECT seq FROM sqlite_sequence WHERE name='test';
2
sqlite>

---


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

Re: [sqlite] question about search string

[Florin Serbanescu]

Thank you for your answer. Indeed is my mistake for
doubling the backslash. The query submitted by you did
not work on my side. Normally it should work, the
similar query in mysql works without any problem, but
it seems that sqlite does not interpret it well...

Florin.

--- [EMAIL PROTECTED] wrote:

> Florin Serbanescu <[EMAIL PROTECTED]> wrote:
> > Hello,
> > 
> > I have a string that contains a full path of a
> file
> > for example. Also I have a table in Sqlite that
> > contains a column name 'command' that has only the
> > filename. My question is how I can create a query
> so I
> > can retrieve all rows where command field is a
> > substring of my string. In MySQL for example I can
> do
> > this:
> > 
> > SELECT * FROM mytable where 'c:\\Program
> Files\\a.exe'
> > like concat('%',command,'%').
> > 
> > But it seems that in Sqlite does not work. Would
> be
> > something like:
> > 
> > SELECT * FROM mytable where 'c:\\Program
> Files\\a.exe'
> > like '%'||command||'%'.
> > 
> 
> SELECT * FROM mytable
>  WHERE 'c:\Program Files\a.exe' LIKE
> '%'||command||'%';
> 
> Note that backslash "\" should not be escaped in
> standard
> SQL string literals.  The need to double the \
> characters
> is a quirk of MySQL.
> 
> --
> D. Richard Hipp  <[EMAIL PROTECTED]>
> 
> 
>
-
> To unsubscribe, send email to
> [EMAIL PROTECTED]
>
-
> 
> 



 

Sponsored Link

Online degrees - find the right program to advance your career.
Www.nextag.com

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

Re: [sqlite] SQLITE readonly Performance on a CD DB

[drh]

[EMAIL PROTECTED] wrote:
> I have 300K records in my database and the blob text file (invoices) in =
> a separate database. I query the database over a network or USB stick an=
> d the performance is good. However when I moved my database to CD media,=
>  the performance dropped drastically. It takes 2 minutes sometime to ret=
> reive the result. The CPU is not busy during the time, and the memory us=
> age increases slowly. 
> 
> When the user selects a row, the system queries the blob database, and h=
> ere I am able to retreive the text, convert it into a tiff file, and loa=
> d it into my program within 1.5sec or less from the CD. Copying the data=
> base to the hard drive and querying that resumes with normal performance=
> . Querying the CD version (immeadiately) and the performance lags agai=
> n. The LED on the CD rom blinking only occasionally, not constantly like=
>  when I copy a file.
> 
> Both databases have 300k records, the first(invoice no indexed) has 8 re=
> trieval fields, while the second has just the ID and blob. The average r=
> ow size is 60-80 bytes, while the blob has on average 500bytes. I am loo=
> king to improve the performance, and reasons for the failings. It does s=
> ound like cacheing mentioned in the thread ... 
> 

What happens if you run VACUUM on the database before you
put it on the CD?
--
D. Richard Hipp  <[EMAIL PROTECTED]>


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

Re: [sqlite] question about search string

[drh]

Florin Serbanescu <[EMAIL PROTECTED]> wrote:
> Hello,
> 
> I have a string that contains a full path of a file
> for example. Also I have a table in Sqlite that
> contains a column name 'command' that has only the
> filename. My question is how I can create a query so I
> can retrieve all rows where command field is a
> substring of my string. In MySQL for example I can do
> this:
> 
> SELECT * FROM mytable where 'c:\\Program Files\\a.exe'
> like concat('%',command,'%').
> 
> But it seems that in Sqlite does not work. Would be
> something like:
> 
> SELECT * FROM mytable where 'c:\\Program Files\\a.exe'
> like '%'||command||'%'.
> 

SELECT * FROM mytable
 WHERE 'c:\Program Files\a.exe' LIKE '%'||command||'%';

Note that backslash "\" should not be escaped in standard
SQL string literals.  The need to double the \ characters
is a quirk of MySQL.

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


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

Re: [sqlite] question about search string

[Marco Radaelli]

Nevermind, I'm wrong.



--- Marco Radaelli <[EMAIL PROTECTED]> ha scritto:

> Try
> 
> SELECT * FROM mytable where 'c:\\Program
> Files\\a.exe'
> like '%command%'
> 
> 
> --- Florin Serbanescu <[EMAIL PROTECTED]> ha
> scritto:
> 
> > Hello,
> > 
> > I have a string that contains a full path of a
> file
> > for example. Also I have a table in Sqlite that
> > contains a column name 'command' that has only the
> > filename. My question is how I can create a query
> so
> > I
> > can retrieve all rows where command field is a
> > substring of my string. In MySQL for example I can
> > do
> > this:
> > 
> > SELECT * FROM mytable where 'c:\\Program
> > Files\\a.exe'
> > like concat('%',command,'%').
> > 
> > But it seems that in Sqlite does not work. Would
> be
> > something like:
> > 
> > SELECT * FROM mytable where 'c:\\Program
> > Files\\a.exe'
> > like '%'||command||'%'.
> > 
> > In other words I need to search in a table a
> > substring
> > of my string. So is quite vice versa of the normal
> > string search with like.
> > 
> > Thank you for your help,
> > 
> > Florin.
> > 
> > 
> >  
> >
>

> > Do you Yahoo!?
> > Everyone is raving about the all-new Yahoo! Mail
> > beta.
> > http://new.mail.yahoo.com
> > 
> >
>
-
> > To unsubscribe, send email to
> > [EMAIL PROTECTED]
> >
>
-
> > 
> > 
> 
> 
> __
> Do You Yahoo!?
> Poco spazio e tanto spam? Yahoo! Mail ti protegge
> dallo spam e ti da tanto spazio gratuito per i tuoi
> file e i messaggi 
> http://mail.yahoo.it 
> 
>
-
> To unsubscribe, send email to
> [EMAIL PROTECTED]
>
-
> 
> 


__
Do You Yahoo!?
Poco spazio e tanto spam? Yahoo! Mail ti protegge dallo spam e ti da tanto 
spazio gratuito per i tuoi file e i messaggi 
http://mail.yahoo.it 

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

Re: [sqlite] question about search string

[Marco Radaelli]

Try

SELECT * FROM mytable where 'c:\\Program Files\\a.exe'
like '%command%'


--- Florin Serbanescu <[EMAIL PROTECTED]> ha scritto:

> Hello,
> 
> I have a string that contains a full path of a file
> for example. Also I have a table in Sqlite that
> contains a column name 'command' that has only the
> filename. My question is how I can create a query so
> I
> can retrieve all rows where command field is a
> substring of my string. In MySQL for example I can
> do
> this:
> 
> SELECT * FROM mytable where 'c:\\Program
> Files\\a.exe'
> like concat('%',command,'%').
> 
> But it seems that in Sqlite does not work. Would be
> something like:
> 
> SELECT * FROM mytable where 'c:\\Program
> Files\\a.exe'
> like '%'||command||'%'.
> 
> In other words I need to search in a table a
> substring
> of my string. So is quite vice versa of the normal
> string search with like.
> 
> Thank you for your help,
> 
> Florin.
> 
> 
>  
>

> Do you Yahoo!?
> Everyone is raving about the all-new Yahoo! Mail
> beta.
> http://new.mail.yahoo.com
> 
>
-
> To unsubscribe, send email to
> [EMAIL PROTECTED]
>
-
> 
> 


__
Do You Yahoo!?
Poco spazio e tanto spam? Yahoo! Mail ti protegge dallo spam e ti da tanto 
spazio gratuito per i tuoi file e i messaggi 
http://mail.yahoo.it 

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

[sqlite] SQLITE readonly Performance on a CD DB

[VisN]

I have 300K records in my database and the blob text file (invoices) in a 
separate database. I query the database over a network or USB stick and the 
performance is good. However when I moved my database to CD media, the 
performance dropped drastically. It takes 2 minutes sometime to retreive the 
result. The CPU is not busy during the time, and the memory usage increases 
slowly. 

When the user selects a row, the system queries the blob database, and here I 
am able to retreive the text, convert it into a tiff file, and load it into my 
program within 1.5sec or less from the CD. Copying the database to the hard 
drive and querying that resumes with normal performance. Querying the CD 
version (immeadiately) and the performance lags again. The LED on the CD rom 
blinking only occasionally, not constantly like when I copy a file.

Both databases have 300k records, the first(invoice no indexed) has 8 retrieval 
fields, while the second has just the ID and blob. The average row size is 
60-80 bytes, while the blob has on average 500bytes. I am looking to improve 
the performance, and reasons for the failings. It does sound like cacheing 
mentioned in the thread ... 

[sqlite] indexes in memory
> 
> Indexes will be loaded into the cache as needed. The whole SQLite database 
> is page based, and the cache caches the pages. The tables and indexes are 
> implemented as page based btrees, with nodes represented by pages.
> 
> The cache is unaware of the higher level structure of the btrees, and 
> there is no way to selectively bring load/unload tables or indexes from 
> memory. The page cache will manage itself on an LRU basis.
>

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

[sqlite] question about search string

[Florin Serbanescu]

Hello,

I have a string that contains a full path of a file
for example. Also I have a table in Sqlite that
contains a column name 'command' that has only the
filename. My question is how I can create a query so I
can retrieve all rows where command field is a
substring of my string. In MySQL for example I can do
this:

SELECT * FROM mytable where 'c:\\Program Files\\a.exe'
like concat('%',command,'%').

But it seems that in Sqlite does not work. Would be
something like:

SELECT * FROM mytable where 'c:\\Program Files\\a.exe'
like '%'||command||'%'.

In other words I need to search in a table a substring
of my string. So is quite vice versa of the normal
string search with like.

Thank you for your help,

Florin.


 

Do you Yahoo!?
Everyone is raving about the all-new Yahoo! Mail beta.
http://new.mail.yahoo.com

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

[sqlite] Sqlite books

[John Gunnarsson]

Hi,

I'm about to buy a book about Sqlite. I'm coding in C++ so I'm looking
for the best book to suite my needs.

In my local bookstore I have:
The Definitive Guide to SQLite, APress
http://www.apress.com/book/bookDisplay.html?bID=10130

and

SQLite, Sams
http://safari.samspublishing.com/067232685X

Have anyone of you a opinion of the books above, or maybe an even
better recommendation?

//John

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

Re: [sqlite] Handling null characters in blob data

[Vivien Malerba]

On 11/13/06, Shivshankar Subramani - TLS , Chennai <[EMAIL PROTECTED]> wrote:

Hi all,

> SQLite version 2.8 and earlier could not (easily) store binary
> data - data with embedded \000 characters.  Thus the encode/decode
> routines were provide to transform data so that it contained no
> \000 characters.
>
> SQLite version 3.0 can store binary data without difficulty.

This is what  I read in the site

http://www.mail-archive.com/sqlite-users@sqlite.org/msg04332.html  . but
i am having difficulty in storing data with null characters in it.Is there
any specific method in which i can solve this problem?



I personally use the X'AABBCCDD' syntax to store BLOBS where AABBCCDD
is the hexadecimal representation of my binary data (one byte
translated into ist 2 digits hexa equivalent). for example:
"insert into mytable values (1, X'AABBCCDD');"

Regards,

Vivien

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

RE: Re[2]: [sqlite] Re: Re: Handling null characters in blob data

[Shivshankar Subramani - TLS , Chennai]

Hey I guess you should know more of compression algorithm before u call one
stupid

With Regards,


SHIVSHANKER S.


HCL Technologies Limited,  

Chennai-600058
Tel: +91-44-43935000 

Extn-5029 Mobile-9884656906
Email: [EMAIL PROTECTED]



-Original Message-
From: Gussimulator [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, November 15, 2006 12:34 PM
To: sqlite-users@sqlite.org
Subject: Re: Re[2]: [sqlite] Re: Re: Handling null characters in blob data


What kind of compression algorithm leaves out null characters? every char 
will be taking a byte, its just stupid.



- Original Message - 
From: "Shivshankar Subramani - TLS , Chennai" <[EMAIL PROTECTED]>
To: 
Sent: Wednesday, November 15, 2006 2:02 AM
Subject: RE: Re[2]: [sqlite] Re: Re: Handling null characters in blob data


Hi Teg,

Can u please send me the sample code

Thanks in advance

With Regards,


SHIVSHANKER S.


HCL Technologies Limited,

Chennai-600058
Tel: +91-44-43935000

Extn-5029 Mobile-9884656906
Email: [EMAIL PROTECTED]



-Original Message-
From: Teg [mailto:[EMAIL PROTECTED]
Sent: Wednesday, November 15, 2006 3:26 AM
To: Shivshankar Subramani - TLS , Chennai
Subject: Re[2]: [sqlite] Re: Re: Handling null characters in blob data


Hello Chennai,

Tuesday, November 14, 2006, 9:20:10 AM, you wrote:


SSTC> Hi,

SSTC> Actually im trying to store a compressed value,which has lots of 
SSTC> null characters.When I try to store it into the database as a blob 
SSTC> item ,only the characters before null gets stored rest does not.I 
SSTC> want to store the entire compressed buffer. Is it possible?

SSTC> BLOB Data stored in the table ->PK...

SSTC> Expected data to be stored
SSTC>   PK.
SSTC>   ...Ɲn5
SSTC>   n»(tm)g..
SSTC>   .è.
SSTC>   ..simpl
SSTC>   e.datUT
SSTC>   ...û.ZE
SSTC>   û.ZEû.Z
SSTC>   Eí'KNÃ0
SSTC>   .?S6Hoe
SSTC>   Eå}ã.6U
SSTC>   "¤B¨HHå
SSTC>   !õ..6n2
SSTC>   ¤-.ÇÄN"
SSTC>   r..ì8DO
SSTC>   .â.E¤Í£
SSTC>   [EMAIL PROTECTED]
SSTC>   è÷÷..«
SSTC>   Y.¬½EURHÒ
SSTC>   Û¨«.¨.
SSTC>   Ü.=Ê}.M
SSTC>   Æg.ê;.
SSTC>   .-fº|lg
SSTC>   p-®N®;






SSTC> Shivshankar Subramani - TLS , Chennai 
SSTC> <[EMAIL PROTECTED]> wrote:
>> I did follow the procedure to store the blob data.I have no problem 
>> in excuting it but my problem is that when my data is of the below 
>> type in memory

SSTC> [snip]

>> Where there are lots of null character in the buffer the sqlite fails 
>> to take them as values

SSTC> What makes you think so? Exactly what are you doing, what results 
SSTC> do you expect, what results do you get, and how the two differ?

SSTC> Igor Tandetnik


SSTC>

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

SSTC> -
SSTC> DISCLAIMER
SSTC> The contents of this e-mail and any attachment(s) are confidential 
SSTC> and intended for the

SSTC> named recipient(s) only. It shall not attach any liability on the 
SSTC> originator or HCL or its

SSTC> affiliates. Any views or opinions presented in this email are 
SSTC> solely those of the author and

SSTC> may not necessarily reflect the opinions of HCL or its affiliates. 
SSTC> Any form of reproduction,

SSTC> dissemination, copying, disclosure, modification, distribution and 
SSTC> / or publication of this

SSTC> message without the prior written consent of the author of this 
SSTC> e-mail
is strictly

SSTC> prohibited. If you have received this email in error please delete 
SSTC> it and notify the sender

SSTC> immediately. Before opening any mail and attachments please check 
SSTC> them for viruses and

SSTC> defect.

SSTC>

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

-

I store JPG files in Sqlite DB's without any issues. You can't treat them
like text though which is what it sounds like you're doing. You really have
two choices, encode them as text using something like UU, Mime or Sqlite's
built in encoding or used the advanced API calls to bind the binary data as
blobs when inserting and retrieving them using the same mechanism.

I use the later, compile the query with parameters and bind the blobs of
binary data after the fact before I insert.

It's all in the API documents. I can send you sample code if you wish.

-- 
Best regards,
 Tegmailto:[EMAIL PROTECTED]



-
To unsubscribe, send email to [EMAIL PROTECTED]

-


-
To unsubscribe, send email to [EMAIL PROTECTED]

-


---