Re: [sqlite] Re: Basic query slower with index?
"Igor Tandetnik" <[EMAIL PROTECTED]> writes: > This is not unusual when you index a field with low cardinality > (number of distinct values). To illustrate, imagine that allele=0 in > all rows. A simple scan without using an index would look once at each > row, for a complexity of O(N) (where N is the number of rows in a > table). A scan using an index would visit every entry in the index > (there are N of them), obtain rowid of the corresponding row and then > look up the field "fid" based on this rowid (a log(N) operation), for > an overall complexity of O(N log N). Igor, thanks for that explaination. Makes sense. > In this particular case, you can improve performance of this specific > query if you declare fid as "integer primary key" (and drop UNIQUE > constraint, as it is implied by primary key). This will make fid a > synonym for rowid, and the query will be satisfied by scanning the > index alone, without ever referring to the table. > > In general, however, you should avoid indexing low cardinality fields > at all: it hurts more often than it helps. Are there any DB tricks you can point me to for dealing with low cardinality columns? If I need to access rows as quickly as possible according to low cardinality column value (e.g. allele = 0/1, strange = 0/1), would it make more sense to split these into separate tables? + seth - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Basic query slower with index?
Seth Falcon <[EMAIL PROTECTED]> wrote:
I'm working with a table with ~3 million rows and 7 columns.
Recently, I found that a simple query takes longer _after_ adding an
index. I find this surprising. Can someone provide some insight?
Here are some details:
The schema:
CREATE TABLE pmfeature (
fid integer not null,
strand integer,
allele integer,
fsetid integer not null references "featureSet" ("fsetid"),
pos integer,
x integer,
y integer,
UNIQUE("fid"));
CREATE INDEX foo on pmfeature ("allele");
The query:
select fid from pmfeature where allele = 0;
On my laptop, if I do (where basic.sql contains the above query):
time sqlite thedb.sqlite < basic.sql > /dev/null
it takes about 9 sec without the 'foo' index and 13 sec with it. The
query returns about half the records (1.5 million).
This is not unusual when you index a field with low cardinality (number
of distinct values). To illustrate, imagine that allele=0 in all rows. A
simple scan without using an index would look once at each row, for a
complexity of O(N) (where N is the number of rows in a table). A scan
using an index would visit every entry in the index (there are N of
them), obtain rowid of the corresponding row and then look up the field
"fid" based on this rowid (a log(N) operation), for an overall
complexity of O(N log N).
In this particular case, you can improve performance of this specific
query if you declare fid as "integer primary key" (and drop UNIQUE
constraint, as it is implied by primary key). This will make fid a
synonym for rowid, and the query will be satisfied by scanning the index
alone, without ever referring to the table.
In general, however, you should avoid indexing low cardinality fields at
all: it hurts more often than it helps.
Igor Tandetnik
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] Basic query slower with index?
Hi all,
I'm working with a table with ~3 million rows and 7 columns.
Recently, I found that a simple query takes longer _after_ adding an
index. I find this surprising. Can someone provide some insight?
Here are some details:
The schema:
CREATE TABLE pmfeature (
fid integer not null,
strand integer,
allele integer,
fsetid integer not null references "featureSet" ("fsetid"),
pos integer,
x integer,
y integer,
UNIQUE("fid"));
CREATE INDEX foo on pmfeature ("allele");
The query:
select fid from pmfeature where allele = 0;
On my laptop, if I do (where basic.sql contains the above query):
time sqlite thedb.sqlite < basic.sql > /dev/null
it takes about 9 sec without the 'foo' index and 13 sec with it. The
query returns about half the records (1.5 million).
I tried using EXPLAIN, but I'm not yet experienced enough to know how
to interpret the results (other than I can see that there are many
more ops when an index is present). Below is the explain output.
sqlite> explain select fid from pmfeature where allele = 0;
0|Goto|0|13|
1|Integer|0|0|
2|OpenRead|0|4|
3|SetNumColumns|0|3|
4|Rewind|0|11|
5|Column|0|2|
6|Integer|0|0|
7|Ne|356|10|collseq(BINARY)
8|Column|0|0|
9|Callback|1|0|
10|Next|0|5|
11|Close|0|0|
12|Halt|0|0|
13|Transaction|0|0|
14|VerifyCookie|0|10|
15|Goto|0|1|
16|Noop|0|0|
sqlite> create index foo on pmfeature ("allele");
sqlite> explain select fid from pmfeature where allele = 0;
0|Goto|0|25|
1|Integer|0|0|
2|OpenRead|0|4|
3|SetNumColumns|0|3|
4|Integer|0|0|
5|OpenRead|1|329850|keyinfo(1,BINARY)
6|Integer|0|0|
7|NotNull|-1|10|
8|Pop|1|0|
9|Goto|0|22|
10|MakeRecord|1|0|d
11|MemStore|0|0|
12|MoveGe|1|22|
13|MemLoad|0|0|
14|IdxGE|1|22|+
15|RowKey|1|0|
16|IdxIsNull|1|21|
17|IdxRowid|1|0|
18|MoveGe|0|0|
19|Column|0|0|
20|Callback|1|0|
21|Next|1|13|
22|Close|0|0|
23|Close|1|0|
24|Halt|0|0|
25|Transaction|0|0|
26|VerifyCookie|0|11|
27|Goto|0|1|
28|Noop|0|0|
This is with sqlite 3.3.8 on OSX ppc.
Thanks,
+ seth
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] beginner question
--- Dennis Cote <[EMAIL PROTECTED]> wrote: > Qiang wrote: > > hello, > > > > two questions.. > > > > 1. comparing select one or two columns from one > row > > from a bigger table ( 20,000 - 30,000 rows, 20 > columns > > ). > >with > >select the same column from one row from a > smaller > > table (20,000 - 30,000 rows, 2 columns). does it > make > > much different on performance? if yes, what makes > it? > > > > 2. Comparing select a sequence number first from a > > small table THEN select all rows from another > table > > that have the same sequence number ( hence two > selects > > ) > >with > >join select on this two tables where they have > the > > same sequence number. any difference here? > > > > > Qiang, > > Re 1: It will be slightly faster to get the data > from a table with fewer > columns if the required columns are at the end of > the row since sqlite > has to skip over the unused fields. But unless you > have large fields the > difference will be very small. It is more important > to reduce the number > of rows to scan, or provide an index so sqlite can > locate the correct > row more quickly. > > Re 2: I suspect the join will always be faster since > it essentially does > the same thing but without the overhead of executing > a second statement. > If you only return the columns from the second table > it will give the > same results. > thank you for the answer. the reason i asked because i want to combine few smaller table into one and save the time join all of them. your answer helped me see the the consequence. > HTH > Dennis Cote > Qiang Sponsored Link $200,000 mortgage for $660/ mo - 30/15 yr fixed, reduce debt - http://yahoo.ratemarketplace.com - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Question on JAVA and Sqlite DB
Benedetta.Turchi wrote: which are the recommended JAVA wrapper APIs/JDBC driver to be used to connect to an SQLite DB? I would happily recommend my own, though I may be a little biased http://www.zentus.com/sqlitejdbc There are a list of others on the SQLiteWrappers page of the main wiki. d - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Design of application using embedded database
[EMAIL PROTECTED] wrote: > Hi, > I would like a bit of advice before starting to make changes to my > application. > > I've written a program in C# for personnel departments and at present all of > the data is stored in memory until the user saves and then it is written to > disk as an XML file. > > I'd like to switch to an embedded SQLite database where the database becomes > the file format for the application. I plan to use the ADO.Net wrapper for > SQLite. > > Can anybody tell me if the following is a reasonable design for implementing > this- > > Upon loading a saved file into the application the database on filesystem is > loaded into an ADO.Net DataSet. This is used by the application until the user > saves to disk again when all of the changes to the DataSet are saved back to > the database on disk. > In all of the desktop apps that I write, I omit the File->Save option altogether. File->Open opens the SQLite database and there after all changes are saved to the disk as they are made. I find this works much better since you avoid losing all your work when the power goes off unexpectedly - or when you power down at the end of the day and forget to close out the file you were working on. Would that OpenOffice did the same... Another advange is that if you open the same file twice in two separate windows, the second one opened still sees all the latest changes. And you cannot trash work by doing File->Save in the wrong order. When your changes are saved to disk immediately, it is important to have a good undo/redo mechanism. There is some example code on the wiki showing how to implement undo/redo using triggers. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Design of application using embedded database
Hi, I would like a bit of advice before starting to make changes to my application. I've written a program in C# for personnel departments and at present all of the data is stored in memory until the user saves and then it is written to disk as an XML file. I'd like to switch to an embedded SQLite database where the database becomes the file format for the application. I plan to use the ADO.Net wrapper for SQLite. Can anybody tell me if the following is a reasonable design for implementing this- Upon loading a saved file into the application the database on filesystem is loaded into an ADO.Net DataSet. This is used by the application until the user saves to disk again when all of the changes to the DataSet are saved back to the database on disk. Cheers, Jon Leadbeater - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] select from commandprompt with output to file
Have tried this now and working beautifully. One thing I couldn't figure out is how to set the row delimiter. I can set the column delimiter fine with .mode csv, but couldn't see something similar for the rows. The delimiter for rows should be vbCrLf. As SQLite is written in C does it have the option to output to memory, say an array and produce the pointer to that memory, rather than a text file? RBS -Original Message- From: Dennis Cote [mailto:[EMAIL PROTECTED] Sent: 17 November 2006 15:04 To: sqlite-users@sqlite.org Subject: Re: [sqlite] select from commandprompt with output to file RB Smissaert wrote: > How would I run these 4 commands via a .bat file or via whatever means: > > cd c:\test\ReadCodes > c:\test\Program\sqlite3 c:\test\ReadCodes\ReadCode.db" > .output testfile.txt > select * from readcode where read_code glob 'G2*'; > > It must be simple, but I can't see it. > > RBS, You need to put all your sqlite shell commands in one file, say sqlitecmds.txt. This includes the dot commands and the SQL statements. So sqlitecmds.txt contains: .output testfile.txt select * from readcode where read_code glob 'G2*'; Then you need to put your DOS shell commands in another file, say readcode.bat. This file will contain the commnad to run sqlite and redirect its input to the file of sqlite commnads above. So readcode.bat contains: cd c:\test\ReadCodes c:\test\Program\sqlite3 c:\test\ReadCodes\ReadCode.db < sqlitecmds.txt Then you tell the DOS shell (actually cmd.exe) to run the commands in your readcode.bat file by typing the batch file name at the command prompt. C:\>readcode This will execute your batch file commands, which will run the sqlite shell program, which will read and execute the commands in the sqlite commands file, which will write its output to the file testfile.txt in the current directory (which will be C:\test\ReadCodes). HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] select from commandprompt with output to file
Dennis, Thanks for that. Will try it later and let you know. Definitely one I couldn't have figured out myself. RBS -Original Message- From: Dennis Cote [mailto:[EMAIL PROTECTED] Sent: 17 November 2006 15:04 To: sqlite-users@sqlite.org Subject: Re: [sqlite] select from commandprompt with output to file RB Smissaert wrote: > How would I run these 4 commands via a .bat file or via whatever means: > > cd c:\test\ReadCodes > c:\test\Program\sqlite3 c:\test\ReadCodes\ReadCode.db" > .output testfile.txt > select * from readcode where read_code glob 'G2*'; > > It must be simple, but I can't see it. > > RBS, You need to put all your sqlite shell commands in one file, say sqlitecmds.txt. This includes the dot commands and the SQL statements. So sqlitecmds.txt contains: .output testfile.txt select * from readcode where read_code glob 'G2*'; Then you need to put your DOS shell commands in another file, say readcode.bat. This file will contain the commnad to run sqlite and redirect its input to the file of sqlite commnads above. So readcode.bat contains: cd c:\test\ReadCodes c:\test\Program\sqlite3 c:\test\ReadCodes\ReadCode.db < sqlitecmds.txt Then you tell the DOS shell (actually cmd.exe) to run the commands in your readcode.bat file by typing the batch file name at the command prompt. C:\>readcode This will execute your batch file commands, which will run the sqlite shell program, which will read and execute the commands in the sqlite commands file, which will write its output to the file testfile.txt in the current directory (which will be C:\test\ReadCodes). HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Date and time comparison
Karthick V - TLS , Chennai wrote:
Hello everyone,
I am trying to retrieve some records from the database using date and time
comparison. The table has three columns, Row Id, Start Time and end Time.
I need to get the row id for a time which falls within the start and end
time.
I am using this query.
select RowID,
strftime('%Y-%m-%dT%H:%M:%S',starttime),
strftime('%Y-%m-%dT%H:%M',endtime) from History where
strftime('%Y-%m-%dT%H:%M',starttime) <
strftime('%Y-%m-%dT%H:%M','2006-11-17T12:17') and
strftime('%Y-%m-%dT%H:%M',endtime) >
strftime('%Y-%m-%dT%H:%M','2006-11-17T12:17');
This works well if there is only one matching row.
However if more than one row matches, I need to get the row whose start time
is closest to the given time. Say the start times are 12:10 and 12:20 and
the given time is 12:25, it should return the second record only.
I am trying the following query
strftime('%Y-%m-%dT%H:%M',endtime - starttime)
however this fails giving some negative numbers.
If anyone knows of a solution, kindly post a reply for this. Thanks in
advance.
With Regards
Karthick V
"The secret of being miserable is to have leisure to bother about whether
you are happy or not. The cure for it is occupation."
George Bernard Shaw (1856-1950)
DISCLAIMER
The contents of this e-mail and any attachment(s) are confidential and intended for the
named recipient(s) only. It shall not attach any liability on the originator or HCL or its
affiliates. Any views or opinions presented in this email are solely those of the author and
may not necessarily reflect the opinions of HCL or its affiliates. Any form of reproduction,
dissemination, copying, disclosure, modification, distribution and / or publication of this
message without the prior written consent of the author of this e-mail is strictly
prohibited. If you have received this email in error please delete it and notify the sender
immediately. Before opening any mail and attachments please check them for viruses and
defect.
What form do you use to store the date and time?
Do you use the Sqlite date format?
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Question on JAVA and Sqlite DB
Hi, which are the recommended JAVA wrapper APIs/JDBC driver to be used to connect to an SQLite DB? Thank you Benedetta Turchi Engineering Tools Engineering Tools Website - *** *** Symbian Software Ltd is a company registered in England and Wales with registered number 4190020 and registered office at 2-6 Boundary Row, Southwark, London, SE1 8HP, UK. This message is intended only for use by the named addressee and may contain privileged and/or confidential information. If you are not the named addressee you should not disseminate, copy or take any action in reliance on it. If you have received this message in error please notify [EMAIL PROTECTED] and delete the message and any attachments accompanying it immediately. Neither Symbian nor any of its Affiliates accepts liability for any corruption, interception, amendment, tampering or viruses occurring to this message in transit or for any message sent by its employees which is not in compliance with Symbian corporate policy. * *
Re: [sqlite] beginner question
Qiang wrote: hello, two questions.. 1. comparing select one or two columns from one row from a bigger table ( 20,000 - 30,000 rows, 20 columns ). with select the same column from one row from a smaller table (20,000 - 30,000 rows, 2 columns). does it make much different on performance? if yes, what makes it? 2. Comparing select a sequence number first from a small table THEN select all rows from another table that have the same sequence number ( hence two selects ) with join select on this two tables where they have the same sequence number. any difference here? Qiang, Re 1: It will be slightly faster to get the data from a table with fewer columns if the required columns are at the end of the row since sqlite has to skip over the unused fields. But unless you have large fields the difference will be very small. It is more important to reduce the number of rows to scan, or provide an index so sqlite can locate the correct row more quickly. Re 2: I suspect the join will always be faster since it essentially does the same thing but without the overhead of executing a second statement. If you only return the columns from the second table it will give the same results. HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] select from commandprompt with output to file
RB Smissaert wrote: How would I run these 4 commands via a .bat file or via whatever means: cd c:\test\ReadCodes c:\test\Program\sqlite3 c:\test\ReadCodes\ReadCode.db" .output testfile.txt select * from readcode where read_code glob 'G2*'; It must be simple, but I can't see it. RBS, You need to put all your sqlite shell commands in one file, say sqlitecmds.txt. This includes the dot commands and the SQL statements. So sqlitecmds.txt contains: .output testfile.txt select * from readcode where read_code glob 'G2*'; Then you need to put your DOS shell commands in another file, say readcode.bat. This file will contain the commnad to run sqlite and redirect its input to the file of sqlite commnads above. So readcode.bat contains: cd c:\test\ReadCodes c:\test\Program\sqlite3 c:\test\ReadCodes\ReadCode.db < sqlitecmds.txt Then you tell the DOS shell (actually cmd.exe) to run the commands in your readcode.bat file by typing the batch file name at the command prompt. C:\>readcode This will execute your batch file commands, which will run the sqlite shell program, which will read and execute the commands in the sqlite commands file, which will write its output to the file testfile.txt in the current directory (which will be C:\test\ReadCodes). HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Re: Date and time comparison
Thanks Igor. It works fine.
With Regards
Karthick V
"The secret of being miserable is to have leisure to bother about whether
you are happy or not. The cure for it is occupation."
George Bernard Shaw (1856-1950)
-Original Message-
From: Igor Tandetnik [mailto:[EMAIL PROTECTED]
Sent: Friday, November 17, 2006 6:37 PM
To: SQLite
Subject: [sqlite] Re: Date and time comparison
Karthick V - TLS , Chennai <[EMAIL PROTECTED]>
wrote:
> I need to get the row id for a time which falls within the start and
> end time.
>
> I am using this query.
>
> select RowID,
> strftime('%Y-%m-%dT%H:%M:%S',starttime),
> strftime('%Y-%m-%dT%H:%M',endtime) from History where
> strftime('%Y-%m-%dT%H:%M',starttime) <
> strftime('%Y-%m-%dT%H:%M','2006-11-17T12:17') and
> strftime('%Y-%m-%dT%H:%M',endtime) >
> strftime('%Y-%m-%dT%H:%M','2006-11-17T12:17');
>
> This works well if there is only one matching row.
>
> However if more than one row matches, I need to get the row whose
> start time is closest to the given time.
Just add
order by strftime('...', starttime) desc limit 1;
Observe that, since all starttimes are before the given time, the nearest
one is also the latest one.
Igor Tandetnik
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
DISCLAIMER
The contents of this e-mail and any attachment(s) are confidential and intended
for the
named recipient(s) only. It shall not attach any liability on the originator or
HCL or its
affiliates. Any views or opinions presented in this email are solely those of
the author and
may not necessarily reflect the opinions of HCL or its affiliates. Any form of
reproduction,
dissemination, copying, disclosure, modification, distribution and / or
publication of this
message without the prior written consent of the author of this e-mail is
strictly
prohibited. If you have received this email in error please delete it and
notify the sender
immediately. Before opening any mail and attachments please check them for
viruses and
defect.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] Re: Date and time comparison
Karthick V - TLS , Chennai <[EMAIL PROTECTED]>
wrote:
I need to get the row id for a time which falls within the start and
end
time.
I am using this query.
select RowID,
strftime('%Y-%m-%dT%H:%M:%S',starttime),
strftime('%Y-%m-%dT%H:%M',endtime) from History where
strftime('%Y-%m-%dT%H:%M',starttime) <
strftime('%Y-%m-%dT%H:%M','2006-11-17T12:17') and
strftime('%Y-%m-%dT%H:%M',endtime) >
strftime('%Y-%m-%dT%H:%M','2006-11-17T12:17');
This works well if there is only one matching row.
However if more than one row matches, I need to get the row whose
start time
is closest to the given time.
Just add
order by strftime('...', starttime) desc limit 1;
Observe that, since all starttimes are before the given time, the
nearest one is also the latest one.
Igor Tandetnik
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] Need help - "Missing extension for native"
Hi,
I'm working with Php4, SQLite and ADOdb. I'm trying to open a database.
See my code :
$db = ADONewConnection('sqlite');
$db->debug = true;
$db-> connect('','','','test.sdb');
$rs = $db->Execute('select * from table_test');
print_r($rs->getRows());
The instruction $db-> connect('','','','test.sdb'); returns an error :
"Missing extension for native".
Does anybody know what this means ?
Thank you.
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] select from commandprompt with output to file
Latest SQLite, 2000 to XP. I have the output to text working now when I type it at the command prompt, but I now need to figure out how to do the whole sequence without any user action. I has to run from VB or maybe a VBS file or anything that can be initiated from VB/VBA. RBS -Original Message- From: Dan Kennedy [mailto:[EMAIL PROTECTED] Sent: 17 November 2006 08:09 To: sqlite-users@sqlite.org Subject: Re: [sqlite] select from commandprompt with output to file What sqlite versions, OS etc.? This worked for me: $ sqlite3 SQLite version 3.2.8 Enter ".help" for instructions sqlite> create table abc(a, b, c); sqlite> .output out.txt sqlite> select * from sqlite_master; sqlite> .quit $ cat out.txt table|abc|abc|2|CREATE TABLE abc(a, b, c) > > How do I do this: > From the command prompt issue a simple select query to a specified database > and direct the output to a file. > I can see there is the .output FILENAME option, but nothing seems to happen. > .output stdout works fine with output to the screen. > Must be overlooking something simple here. > > RBS > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > Sponsored Link $200,000 mortgage for $660/ mo - 30/15 yr fixed, reduce debt - http://yahoo.ratemarketplace.com - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Date and time comparison
Hello everyone,
I am trying to retrieve some records from the database using date and time
comparison. The table has three columns, Row Id, Start Time and end Time.
I need to get the row id for a time which falls within the start and end
time.
I am using this query.
select RowID,
strftime('%Y-%m-%dT%H:%M:%S',starttime),
strftime('%Y-%m-%dT%H:%M',endtime) from History where
strftime('%Y-%m-%dT%H:%M',starttime) <
strftime('%Y-%m-%dT%H:%M','2006-11-17T12:17') and
strftime('%Y-%m-%dT%H:%M',endtime) >
strftime('%Y-%m-%dT%H:%M','2006-11-17T12:17');
This works well if there is only one matching row.
However if more than one row matches, I need to get the row whose start time
is closest to the given time. Say the start times are 12:10 and 12:20 and
the given time is 12:25, it should return the second record only.
I am trying the following query
strftime('%Y-%m-%dT%H:%M',endtime - starttime)
however this fails giving some negative numbers.
If anyone knows of a solution, kindly post a reply for this. Thanks in
advance.
With Regards
Karthick V
"The secret of being miserable is to have leisure to bother about whether
you are happy or not. The cure for it is occupation."
George Bernard Shaw (1856-1950)
DISCLAIMER
The contents of this e-mail and any attachment(s) are confidential and intended
for the
named recipient(s) only. It shall not attach any liability on the originator or
HCL or its
affiliates. Any views or opinions presented in this email are solely those of
the author and
may not necessarily reflect the opinions of HCL or its affiliates. Any form of
reproduction,
dissemination, copying, disclosure, modification, distribution and / or
publication of this
message without the prior written consent of the author of this e-mail is
strictly
prohibited. If you have received this email in error please delete it and
notify the sender
immediately. Before opening any mail and attachments please check them for
viruses and
defect.
RE: [sqlite] select from commandprompt with output to file
Yes, from a DOS command shell, but when I get it working I would like to avoid the DOD box. I have been trying your suggestion in various forms, but sofar no success yet. Something is happening though, so it looks like it might work. I get various error messages: path can't be found, incomplete SQL, cannot find unnamed pipe etc. Tried lots of things, but I know even less about DOS then you and I am just messing about in the dark. I will get it and thanks for the starting point. RBS -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 17 November 2006 01:15 To: sqlite-users@sqlite.org Subject: Re: [sqlite] select from commandprompt with output to file "RB Smissaert" <[EMAIL PROTECTED]> writes: > How would I run these 4 commands via a .bat file or via whatever means: > > cd c:\test\ReadCodes > c:\test\Program\sqlite3 c:\test\ReadCodes\ReadCode.db" > .output testfile.txt > select * from readcode where read_code glob 'G2*'; I haven't been tracking this thread so apologies if I'm misunderstanding what you want. Are you doing this from a DOS (Command) shell? I'm far from a DOS expert, but how about something like this: cd c:\test\ReadCodes echo "select * from readcode where read_code glob 'G2*'" | c:\test\Program\sqlite3 c:\test\ReadCodes\ReadCode.db > testfile.txt Derrell - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] select from commandprompt with output to file
What sqlite versions, OS etc.? This worked for me: $ sqlite3 SQLite version 3.2.8 Enter ".help" for instructions sqlite> create table abc(a, b, c); sqlite> .output out.txt sqlite> select * from sqlite_master; sqlite> .quit $ cat out.txt table|abc|abc|2|CREATE TABLE abc(a, b, c) > > How do I do this: > From the command prompt issue a simple select query to a specified database > and direct the output to a file. > I can see there is the .output FILENAME option, but nothing seems to happen. > .output stdout works fine with output to the screen. > Must be overlooking something simple here. > > RBS > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > Sponsored Link $200,000 mortgage for $660/ mo - 30/15 yr fixed, reduce debt - http://yahoo.ratemarketplace.com - To unsubscribe, send email to [EMAIL PROTECTED] -
