Re: [sqlite] shared cache
Richard Klein wrote: [EMAIL PROTECTED] wrote: John Stanton <[EMAIL PROTECTED]> wrote: Yes, each connection has a cache. A lot of concurrent connections means a lot of memory allocated to cache and potentially a lot of duplicated cached items. See shared cache mode for relief. Yes. But remember that shared cache mode has limitations: * When shared cache mode is enabled, you cannot use a connection in a thread other than the thread in which it was originally created. * Only connections opened in the same thread share a cache. The shared cache mode is designed for building a "server thread" that accepts connection requests and SQL statements via messages from "client threads", acts upon those requests, and returns the result. -- D. Richard Hipp <[EMAIL PROTECTED]> I suppose that I could accomplish almost the same thing in 2.8.17, even though shared cache mode is not available in that version. I could have a server thread that opens the database, and then accepts and processes SQL statements via messages from client threads. The only difference would be that the client threads could not send connection requests. There would be only one connection, and it would be opened implicitly by the server thread at system startup. The benefit would be that all the client threads would effectively share the same cache, since there would in fact be only one connection. The cost would be that each SQL statement would require an additional two context switches to execute. In my application (TiVo-like Personal Video Recorder functionality in a set-top box), the benefit of memory savings far outweighs the cost of a performance hit due to extra context switches. - Richard Upon further reflection, I realized that the scheme outlined above won't work. The problem can be summed up on one word: TRANSACTIONS. There needs to be a way to make sure that the SQL statements composing a trans- action in client thread 'A' aren't intermixed with those composing a transaction in client thread 'B'. The SQLite connection is the structure designed to keep track of state information such as whether or not a transaction is in progress. If client threads 'A' and 'B' share the same connection, then the burden of maintaining this state information falls on the server thread. Not a great idea. Therefore, it would appear that I have two options: (1) Have the server thread open separate connections for client threads 'A' and 'B', and enable shared cache mode so that the two connections can share cached items. This option requires upgrading to SQLite version 3.3.0 or higher. (2) Abandon the idea of a server thread; have threads 'A' and 'B' open their own connections and access SQLite directly. This option does *not* allow the sharing of cached items, but allows me to stay with SQLite version 2.8.17. - Richard - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] optimizer question
Hi If I have a table with a couple of indexed varchar fields e.g: CREATE TABLE t (id INT, a VARCHAR(20), b VARCHAR(20)...); CREATE INDEX ia ON t(a); CREATE INDEX ib ON t(b); then will the sqlite query optimizer use these indices in these SELECT's: 1. SELECT * FROM t WHERE a LIKE 'M%'; 2. SELECT * FROM t WHERE a LIKE 'M%' ORDER BY a; 3. SELECT * FROM t WHERE a LIKE 'M%' ORDER BY b; 4. SELECT * FROM t WHERE a LIKE 'M%' ORDER BY b,a; ...such that none of them will result in a table scan? Thx -- No virus found in this outgoing message. Checked by AVG. Version: 7.5.476 / Virus Database: 269.10.6/902 - Release Date: 15/07/2007 14:21 - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] SELECT ORDER BY failure
No, we are actually filling in the parameter with a valid integer value. I was just trying to say it was a parameter. > > Are you actually searching for records where F is the string "?" > > If so, why don't you try WHERE F="?" instead of leaving it with the > ? unquoted. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite on Mac
"Ahmed Sulaiman" <[EMAIL PROTECTED]> wrote: > Hi all, > > Does SQLite work on Mac, SQLite is built into the Mac. Apple uses it for many of the applications that come on the mac, such as the email reader and safari. Just open up a terminal window and type "sqlite3" and you will see. SQLite also compiles out-of-the-box on mac. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Ascii data in string treated as a num
"Michael Flum" <[EMAIL PROTECTED]> wrote: > I have a short program that requires storing of Ascii strings that > happen to be numbers. It seem that when I retrieve the data it has been > interrupted as a numeric value and is returned altered. I.E. "0E00" is > returned as "0", "" is returned as "0", "76E0" is returned as "76" > >SQL_Write( db, "CREATE TABLE P ( ENCODING INTEGER PRIMARY KEY UNIQUE, > ST STRING, EN STRING, SW STRING,DW STRING,BX STRING);" ); > Use TEXT instead of STRING as the column datatype. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite on Mac
At 11:45 AM -0400 7/19/07, Ahmed Sulaiman wrote: Hi all, Does SQLite work on Mac, and if yes, is there any Mac enabled version that I could download? Cheers SQLite just works on Mac OS X. If you have the Mac OS X Developer Tools intalled, you can just compile the normal SQLite source distro and it will work. Otherwise, if you have Mac OS X 10.4 Tiger or later, a version of SQLite is also built-in as part of "Core Data". -- Darren Duncan - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Ascii data in string treated as a num
Michael Flum wrote: I have a short program that requires storing of Ascii strings that happen to be numbers. It seem that when I retrieve the data it has been interrupted as a numeric value and is returned altered. I.E. "0E00" is returned as "0", "" is returned as "0", "76E0" is returned as "76" Thanks for any help in advance. SQL_Write( db, "CREATE TABLE P ( ENCODING INTEGER PRIMARY KEY UNIQUE, ST STRING, EN STRING, SW STRING,DW STRING,BX STRING);" ); Try defining the columns as TEXT rather than STRING. http://www.sqlite.org/datatype3.html HTH, Gerry - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite on Mac
On 19 Jul 2007, at 16:45, Ahmed Sulaiman wrote: Does SQLite work on Mac, and if yes, is there any Mac enabled version that I could download? SQLite is used on the Mac natively - for example Mail.app uses a SQLite DB to keep its message data straight. Try the sqlite3 shell command. Nigel. -- [ Nigel Metheringham [EMAIL PROTECTED] ] [ - Comments in this message are my own and not ITO opinion/policy - ] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] mailing list slow?
--- [EMAIL PROTECTED] wrote: > Joe Wilson <[EMAIL PROTECTED]> wrote: > > I noticed delays of an hour or so in posts hitting the mailing list > > recently. > > Or is it just my mail server? > > > > The server (www.sqlite.org) seems to be doing OK. > Load average is 0.13. Nothing unusual in the logs. > > There are currently 1281 people on the mailing list. The > mailing list manager (ezmlm) will send messages to at most > 20 people at at time. If each send requires 20 seconds, > that means about 1200 seconds to relay a message, or about > 20 minutes. > > Might be your mail server. Spam loads continue to increase. > My spam/email ratio is approaching 30:1. In other words, I > get about 30 spams for each legitimate email now. The last post to the list wasn't echoed for 3 hours, as confirmed by various sqlite mailing list archives. Last week it would take less than 15 minutes. Email to/from my email server otherwise appears to work immediately. Roger's post about email being deliberately delayed may be applicable: http://www.mail-archive.com/sqlite-users%40sqlite.org/msg26229.html Sick sense of humor? Visit Yahoo! TV's Comedy with an Edge to see what's on, when. http://tv.yahoo.com/collections/222 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite on Mac
Hi On 7/19/07, Ahmed Sulaiman <[EMAIL PROTECTED]> wrote: Hi all, Does SQLite work on Mac, and if yes, is there any Mac enabled version that I could download? While there are fink and darwin ports, I would suggest you to compile it from scratch. It should work well. In my case I just needed to deactivate TCL bindings. Cheers Alberto -- Alberto Simões - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] SQLite on Mac
> -Original Message- > From: Ahmed Sulaiman [mailto:[EMAIL PROTECTED] > Sent: Thursday, July 19, 2007 8:46 AM > To: sqlite-users@sqlite.org > Subject: [sqlite] SQLite on Mac > > Hi all, > > Does SQLite work on Mac, and if yes, is there any Mac enabled version > that I could download? There's no need to download it; Apple includes a version, and uses it. If you need a more recent version, you can build from source. -- James - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Duplicate Row check
> When i do a insert is there a way to know row already exists!! Not without querying. But you could do something like this: CREATE TABLE t1(a PRIMARY KEY, b, c); insert into t1 select 7, 'foo', 'bar' where not exists ( select null from t1 where a=7); which is similar to: INSERT OR IGNORE INTO t1 values(7, 'one', 'two'); except that the first insert form does not require any indexes to work, and you have more flexibility with the where clause. If you wish to insert a row if it does not exist, or update the row if it does exist you can use REPLACE INTO: REPLACE INTO t1 values(7, 'what', 'ever'); Get the Yahoo! toolbar and be alerted to new email wherever you're surfing. http://new.toolbar.yahoo.com/toolbar/features/mail/index.php - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite on Mac
On 7/19/07, Ahmed Sulaiman <[EMAIL PROTECTED]> wrote: Hi all, Does SQLite work on Mac, and if yes, is there any Mac enabled version that I could download? you must be new here, as they say on ./ Yes, SQLite works just fine on Mac. Just type the words Mac and SQLite in Google. -- Puneet Kishor http://punkish.eidesis.org/ Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/ Open Source Geospatial Foundation http://www.osgeo.org/education/ S Policy Fellow, National Academy of Sciences http://www.nas.edu/ - collaborate, communicate, compete = - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] optimizer question
Hi If I have a table with a couple of indexed varchar fields e.g: CREATE TABLE t (id INT, a VARCHAR(20), b VARCHAR(20)...); CREATE INDEX ia ON t(a); CREATE INDEX ib ON t(b); then will the sqlite query optimizer use these indices in these SELECT's: 1. SELECT * FROM t WHERE a LIKE 'M%'; 2. SELECT * FROM t WHERE a LIKE 'M%' ORDER BY a; 3. SELECT * FROM t WHERE a LIKE 'M%' ORDER BY b; 4. SELECT * FROM t WHERE a LIKE 'M%' ORDER BY b,a; ...such that none of them will result in a table scan? Thx -- No virus found in this outgoing message. Checked by AVG. Version: 7.5.476 / Virus Database: 269.10.6/902 - Release Date: 15/07/2007 14:21 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Duplicate Row check
You can put a Primary Key (unique index) on the table. Then when inserting a duplicate, an error will be generated. Then test for the error. Or if you want the new row to overwrite the original use insert or replace If you goal is to keep the existing use insert or ignore. see: http://www.sqlite.org/lang_conflict.html RaghavendraK 70574 <[EMAIL PROTECTED]> wrote: Q was incomplete. When i do a insert is there a way to know row already exists!! regrads ragha ** This email and its attachments contain confidential information from HUAWEI, which is intended only for the person or entity whose address is listed above. Any use of the information contained herein in any way (including, but not limited to, total or partial disclosure, reproduction, or dissemination) by persons other than the intended recipient(s) is prohibited. If you receive this e-mail in error, please notify the sender by phone or email immediately and delete it! * - Original Message - From: RaghavendraK 70574 Date: Thursday, July 19, 2007 5:17 pm Subject: [sqlite] Duplicate Row check > Hi, > > How can check if a row exists in the db or not without querying for > it? > regards > ragha > > > ** > This email and its attachments contain confidential information > from HUAWEI, which is intended only for the person or entity whose > address is listed above. Any use of the information contained > herein in any way (including, but not limited to, total or partial > disclosure, reproduction, or dissemination) by persons other than > the intended recipient(s) is prohibited. If you receive this e-mail > in error, please notify the sender by phone or email immediately > and delete it! > > * > > - > To unsubscribe, send email to [EMAIL PROTECTED] > > - > > - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Duplicate Row check
RaghavendraK 70574 <[EMAIL PROTECTED]> wrote: Q was incomplete. When i do a insert is there a way to know row already exists!! If you have uniquness constaints in place that prevent insertion of the duplicate row, your statement will fail with SQLITE_CONSTRAINT error. Igor tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Duplicate Row check
If you can, define one of the data entries in your table (Schema defination) as "unique" when you create the table. The engine will then set an error condition (call the callback function) and this should prevent you from entering duplicate data and hence duplicate rows. Michael -Original Message- From: RaghavendraK 70574 [mailto:[EMAIL PROTECTED] Sent: Thursday, July 19, 2007 8:00 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Duplicate Row check Q was incomplete. When i do a insert is there a way to know row already exists!! regrads ragha ** This email and its attachments contain confidential information from HUAWEI, which is intended only for the person or entity whose address is listed above. Any use of the information contained herein in any way (including, but not limited to, total or partial disclosure, reproduction, or dissemination) by persons other than the intended recipient(s) is prohibited. If you receive this e-mail in error, please notify the sender by phone or email immediately and delete it! * - Original Message - From: RaghavendraK 70574 <[EMAIL PROTECTED]> Date: Thursday, July 19, 2007 5:17 pm Subject: [sqlite] Duplicate Row check > Hi, > > How can check if a row exists in the db or not without querying for > it? > regards > ragha > > > ** > This email and its attachments contain confidential information > from HUAWEI, which is intended only for the person or entity whose > address is listed above. Any use of the information contained > herein in any way (including, but not limited to, total or partial > disclosure, reproduction, or dissemination) by persons other than > the intended recipient(s) is prohibited. If you receive this e-mail > in error, please notify the sender by phone or email immediately > and delete it! > > * > > - > To unsubscribe, send email to [EMAIL PROTECTED] > > - > > - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] SQL Challenge: select stack
Does anyone have ideas on how to implement a stack using sql Given the following tables and data: create table stack( id integer primary key, value integer); create table stackpop ( id integer primary key, value integer ); begin; insert into stack values (1, 1234); insert into stack values (2, 1234); insert into stack values (6, 1234); insert into stack values (9, 1234); commit; begin; insert into stackpop values (12, 1234) ; insert into stackpop values (14, 1234) ; insert into stackpop values (18, 1234) ; commit; Do you have any ideas for a select that will return the stackpop and stack I'ds paired as follows: 12 | 9 14 | 6 18 | 2 Thanks for your help! ken
Re: [sqlite] Duplicate Row check
On 7/19/07, RaghavendraK 70574 <[EMAIL PROTECTED]> wrote: Q was incomplete. When i do a insert is there a way to know row already exists!! what is the definition of "row already exists"? If you are concerned about a particular column, make that into a PK. If you are concerned about all the columns, make all the columns into a composite PK. Your program will croak accordingly. Else, just first do a SELECT before doing an INSERT. Use CONFLICT clause to handle conflicts. -- Puneet Kishor http://punkish.eidesis.org/ Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/ Open Source Geospatial Foundation http://www.osgeo.org/education/ S Policy Fellow, National Academy of Sciences http://www.nas.edu/ - collaborate, communicate, compete = - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Importing a big text file (CSV?)
"=?ISO-8859-1?Q?Alberto_Sim=F5es?=" <[EMAIL PROTECTED]> wrote: > Ok, for future reference (drh, please, it would be nice to add this to > the web site) That is why we have wiki (http://www.sqlite.org/cvstrac/wiki) so that you can add things like this yourself. I'm busy trying to fix database corruption bugs (like ticket #2518). ;-) > > To import: > 3 5 6 > 3 4 6 > > CREATE TABLE foo (v1,v2,v3); > .separator " " > .import "file.dat" foo > > -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SELECT ORDER BY failure
"Mark Brown" <[EMAIL PROTECTED]> wrote: > Hi- > > We have a query that is failing with SQLite error code 10: > > SELECT A, B, C, D, E, F, G > FROM Table1 > WHERE F=? > ORDER BY E > > but succeeds when the ORDER BY clause is removed. > > This database does not have any indicies on any of the tables. Is this why > it is failing? > When it does not have an index, SQLite sorts by creating a temporary index in a temporary database. The temp database is located in some standard place like /var/tmp. The sqlite3OsTempFileName() backend method names the temp database. Error code to is an I/O error. Likely something is wrong with the temp database file. Maybe the directly is read only or something like that. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SELECT ORDER BY failure
Mark Brown wrote: > Hi- > > We have a query that is failing with SQLite error code 10: > > SELECT A, B, C, D, E, F, G > FROM Table1 > WHERE F=? > ORDER BY E > > but succeeds when the ORDER BY clause is removed. > > This database does not have any indicies on any of the tables. Is this why > it is failing? Are you actually searching for records where F is the string "?" If so, why don't you try WHERE F="?" instead of leaving it with the ? unquoted. -- Scott Baker - Canby Telcom RHCE - System Administrator - 503.266.8253 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Importing a big text file (CSV?)
On 7/19/07, Veikko Mäkinen <[EMAIL PROTECTED]> wrote:
Alberto Simões wrote:
> Hi
>
> I have a file (big file with 16 000 000 lines) with records like
>
> 2 3 4
> 4 3 2
> 5 4 387
> 5 8 5473
> ...
>
> and I want to import this to an SQLite table.
> Although I can replace all this to INSERT commands very easily, I
> would like to ask first if there is any faster method.
I insert 150 000 records with a prepared statement in one single
transaction and it takes about 12 secs. I think that's fast enough :)
Mind you my table doesn't have indexes. Indexes make inserting notably
slower.
337 seconds on my Macbook Pro
lucknow:~/Data/temp punkish$ sqlite3 test.db
SQLite version 3.3.8
Enter ".help" for instructions
sqlite> create table test (a, b, c);
sqlite> .q
lucknow:~/Data/temp punkish$ vim test.pl
#!/usr/local/bin/perl -w
use strict; use DBI; use Benchmark;
my $t0 = new Benchmark;
my $dbh = DBI->connect(
"dbi:SQLite:dbname=test.db", "", "", { RaiseError => 1, AutoCommit => 0 }
);
my $sth = $dbh->prepare(qq{INSERT INTO test (a, b, c) VALUES (?, ?, ?)});
for (1 .. 1600) { $sth->execute($_, $_, $_); }
$dbh->commit;
my $t1 = new Benchmark;
print "This took " . timestr( timediff($t1, $t0) ) . "\n";
lucknow:~/Data/temp punkish$ ls
test.db test.pl
lucknow:~/Data/temp punkish$ perl test.pl
This took 337 wallclock secs (279.02 usr + 15.67 sys = 294.69 CPU)
lucknow:~/Data/temp punkish$ ls -l
-rw-r--r-- 1 punkish punkish 542086144 Jul 19 11:29 test.db
-rw-r--r-- 1 punkish punkish432 Jul 19 11:23 test.pl
lucknow:~/Data/temp punkish$ sqlite3 test.db
SQLite version 3.3.8
Enter ".help" for instructions
sqlite> select count(*) from test;
1600
sqlite>
--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
S Policy Fellow, National Academy of Sciences http://www.nas.edu/
-
collaborate, communicate, compete
=
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] Ascii data in string treated as a num
I have a short program that requires storing of Ascii strings that happen to be numbers. It seem that when I retrieve the data it has been interrupted as a numeric value and is returned altered. I.E. "0E00" is returned as "0", "" is returned as "0", "76E0" is returned as "76" Thanks for any help in advance. SQL_Write( db, "CREATE TABLE P ( ENCODING INTEGER PRIMARY KEY UNIQUE, ST STRING, EN STRING, SW STRING,DW STRING,BX STRING);" ); sprintf(sql_statment,"INSERT INTO P VALUES( %d,\'%s\',\'%s\',\'%s\',\'%s\',\'%s\');", evalue, pt[0], pt[1], pt[2], pt[3], pt[4]); Example: INSERT INTO P VALUES( 21,'0E00','','7E00','1234','NOVALUE'); Returned: 21, '0','0','7','1234','NOVALUE' Michael Flum Software Engineer Haas Automation, Inc.
[sqlite] SQLite on Mac
Hi all, Does SQLite work on Mac, and if yes, is there any Mac enabled version that I could download? Cheers - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Duplicate Row check
> How can check if a row exists in the db or not without querying for it? Isn't the very act of asking whether it exists a query unto itself? Looking for a deal? Find great prices on flights and hotels with Yahoo! FareChase. http://farechase.yahoo.com/ - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Duplicate Row check
RaghavendraK 70574 <[EMAIL PROTECTED]> wrote: How can check if a row exists in the db or not without querying for it? Or in other words: how can I read a book without opening it first? Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Duplicate Row check
Q was incomplete. When i do a insert is there a way to know row already exists!! regrads ragha ** This email and its attachments contain confidential information from HUAWEI, which is intended only for the person or entity whose address is listed above. Any use of the information contained herein in any way (including, but not limited to, total or partial disclosure, reproduction, or dissemination) by persons other than the intended recipient(s) is prohibited. If you receive this e-mail in error, please notify the sender by phone or email immediately and delete it! * - Original Message - From: RaghavendraK 70574 <[EMAIL PROTECTED]> Date: Thursday, July 19, 2007 5:17 pm Subject: [sqlite] Duplicate Row check > Hi, > > How can check if a row exists in the db or not without querying for > it? > regards > ragha > > > ** > This email and its attachments contain confidential information > from HUAWEI, which is intended only for the person or entity whose > address is listed above. Any use of the information contained > herein in any way (including, but not limited to, total or partial > disclosure, reproduction, or dissemination) by persons other than > the intended recipient(s) is prohibited. If you receive this e-mail > in error, please notify the sender by phone or email immediately > and delete it! > > * > > - > To unsubscribe, send email to [EMAIL PROTECTED] > > - > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Importing a big text file (CSV?)
Ok, for future reference (drh, please, it would be nice to add this to the web site) To import: 3 5 6 3 4 6 CREATE TABLE foo (v1,v2,v3); .separator " " .import "file.dat" foo Cheers Alberto On 7/19/07, Yusuke ITO <[EMAIL PROTECTED]> wrote: Hi, COPY command (like PostgreSQL) http://www.sqlite.org/lang_copy.html COPY tbl_foo (col1, col2, col3) FROM stdin; 2 3 4 4 3 2 5 4 387 5 8 5473 \. -- Yusuke ITO [EMAIL PROTECTED] On Thu, 19 Jul 2007 13:01:53 +0200 "Sylko Zschiedrich" <[EMAIL PROTECTED]> wrote: > We are using precompiled insert statements and bind the parameters. > The inserts were done in a transaction that is committed and reopened every > 1000 iterations. > > Ciao > Sylko > > -Urspr〓gliche Nachricht- > Von: Alberto Sim〓s [mailto:[EMAIL PROTECTED] > Gesendet: Donnerstag, 19. Juli 2007 11:57 > An: sqlite-users@sqlite.org > Betreff: [sqlite] Importing a big text file (CSV?) > > Hi > > I have a file (big file with 16 000 000 lines) with records like > > 2 3 4 > 4 3 2 > 5 4 387 > 5 8 5473 > ... > > and I want to import this to an SQLite table. > Although I can replace all this to INSERT commands very easily, I > would like to ask first if there is any faster method. > > Cheers > Alberto > -- > Alberto Sim〓s > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - - To unsubscribe, send email to [EMAIL PROTECTED] - -- Alberto Simões
[sqlite] sqlite3_callback called even for empty tables
Hello everyone, I hope this is not an FAQ... In my code I call sqlite3_exec with a sqlite3_callback. I noticed that when I do a select statement, then the callback is invoked once even if the table is empty, but with all argv strings passed to the callback being NULL (however with the correct argc value). Is this expected behaviour, so I always have to check in my callback if the string parameters being passed are non-NULL to guard against a select statement on an empty table, or is there a more elegant solution to the problem? Thanks for any help, -- Stefan Kuhr -- View this message in context: http://www.nabble.com/sqlite3_callback-called-even-for-empty-tables-tf4111001.html#a11689130 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] undefined reference to `sqlite3_open'
You don't seem to have the sqlite3 link library in your compile and link
command.
MaaSTaaR wrote:
Hello ...
firstly, sorry for my bad English.
i am using SQLite with C under Linux, i wrote small file which use Glade,
GTK and SQLite, but i have problem with SQLite.
this is the command which i used to compile the file : "gcc `pkg-config
--libs --cflags gtk+-2.0 libglade-2.0 sqlite` -o main main.c"
these the problems :
/tmp/ccxN97zv.o: In function `main':main.c:(.text+0x53): undefined
reference
to `sqlite3_open'
:main.c:(.text+0x161): undefined reference to `sqlite3_close'
collect2: ld returned 1 exit status
finally this is my code :
#include
#include
#include
#include
void start(GtkWidget *b,GtkWidget *t)
{
const gchar *text;
text = gtk_entry_get_text(GTK_ENTRY(t));
}
int main(int argc, char *argv[])
{
GladeXML *ui;
GtkWidget *w,*t,*b;
sqlite3 *db;
int rc;
rc = sqlite3_open("./db/def.db",);
gtk_init(,);
ui = glade_xml_new("./gui/main.glade",NULL,NULL);
w = glade_xml_get_widget(ui,"window1");
g_signal_connect(G_OBJECT(w),"delete-event",G_CALLBACK(gtk_main_quit),NULL);
t = glade_xml_get_widget(ui,"word"); // This is label
b = glade_xml_get_widget(ui,"start"); // This is button
g_signal_connect(G_OBJECT(b),"clicked",G_CALLBACK(start),(gpointer)t);
gtk_main();
sqlite3_close(db);
}
what is this error and how to solve it?
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] Importing a big text file (CSV?)
Regarding: "Meanwhile I found an '.import' command on SQLite, but I can't find a suitable documentation on how it works." It can be easy to miss page: http://www.sqlite.org/sqlite.html where this is documented. Basically, it sounds like you might want to invoke the command line utility, sqlite3, then : .separator ' ' .import myfile.txt mytable (Note that there must be no terminating semicolon on these dot commands) This assumes you have precisely ONE space between each and every value. With 16 million lines, you may have some subtler issues. You'll want to remove any indices and recreate them after the import, for example. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Importing a big text file (CSV?)
Alberto Simões wrote: Hi I have a file (big file with 16 000 000 lines) with records like 2 3 4 4 3 2 5 4 387 5 8 5473 ... and I want to import this to an SQLite table. Although I can replace all this to INSERT commands very easily, I would like to ask first if there is any faster method. I insert 150 000 records with a prepared statement in one single transaction and it takes about 12 secs. I think that's fast enough :) Mind you my table doesn't have indexes. Indexes make inserting notably slower. -veikko - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Duplicate Row check
Hi, How can check if a row exists in the db or not without querying for it? regards ragha ** This email and its attachments contain confidential information from HUAWEI, which is intended only for the person or entity whose address is listed above. Any use of the information contained herein in any way (including, but not limited to, total or partial disclosure, reproduction, or dissemination) by persons other than the intended recipient(s) is prohibited. If you receive this e-mail in error, please notify the sender by phone or email immediately and delete it! * - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] undefined reference to `sqlite3_open'
MaaSTaaR <[EMAIL PROTECTED]> wrote: > Hello ... > > firstly, sorry for my bad English. > > i am using SQLite with C under Linux, i wrote small file which use Glade, > GTK and SQLite, but i have problem with SQLite. > > this is the command which i used to compile the file : "gcc `pkg-config > --libs --cflags gtk+-2.0 libglade-2.0 sqlite` -o main main.c" > > these the problems : > /tmp/ccxN97zv.o: In function `main':main.c:(.text+0x53): undefined reference > to `sqlite3_open' > :main.c:(.text+0x161): undefined reference to `sqlite3_close' > collect2: ld returned 1 exit status > > > what is this error and how to solve it? My guess: Say "sqlite3" instead of "sqlite" in your pkg-config. "sqlite" usually refers to the older SQLite version 2 library whereas you are using the newer SQLite version 3 APIs. The usual naming convention for SQLite version 3 is "sqlite3". But I know nothing about pkg-config so this is only a guess. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: timestamp to date in a trigger
Charly Caulet <[EMAIL PROTECTED]>
wrote:
CREATE TRIGGER tstpTOdate1 AFTER INSERT ON contrat
BEGIN
UPDATE contrat SET date1=strftime("%d-%m-%Y", new.tstp) WHERE
UniqueID=new.UniqueID;
END;
But when strftime doesn't seem to work :
INSERT INTO contrat(tstp) VALUES("1184834152");
SELECT * FROM contrat;
1|1184834152|16-08-3239253
I'm not sure what 1184834152 is supposed to represent, but strftime
interprets it as a Julian day number. (1184834152 / 365) is somewhere
around 3246120, so the year looks about right.
You probably want
strftime('%d-%m-%Y', new.tstp, 'unixepoch')
For more details, see
http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions
Igor Tandetnik
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] timestamp to date in a trigger
"Charly Caulet" <[EMAIL PROTECTED]> wrote:
>
> But when strftime doesn't seem to work :
> >INSERT INTO contrat(tstp) VALUES("1184834152");
> >SELECT * FROM contrat;
> 1|1184834152|16-08-3239253
> -
>
SQLite uses the julian day number, not seconds since
1970. My guess is that julian day number 1184834152
really is sometime in the year 3239253...
If you say
strftime('%d-%m-%Y', new.tstp, 'unixepoch')
the extra "unixepoch" argument will convert from
seconds since 1970 into julian day number for you
and solve your problem.
--
D. Richard Hipp <[EMAIL PROTECTED]>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Importing a big text file (CSV?)
Hi, COPY command (like PostgreSQL) http://www.sqlite.org/lang_copy.html COPY tbl_foo (col1, col2, col3) FROM stdin; 2 3 4 4 3 2 5 4 387 5 8 5473 \. -- Yusuke ITO [EMAIL PROTECTED] On Thu, 19 Jul 2007 13:01:53 +0200 "Sylko Zschiedrich" <[EMAIL PROTECTED]> wrote: > We are using precompiled insert statements and bind the parameters. > The inserts were done in a transaction that is committed and reopened every > 1000 iterations. > > Ciao > Sylko > > -Urspr〓gliche Nachricht- > Von: Alberto Sim〓s [mailto:[EMAIL PROTECTED] > Gesendet: Donnerstag, 19. Juli 2007 11:57 > An: sqlite-users@sqlite.org > Betreff: [sqlite] Importing a big text file (CSV?) > > Hi > > I have a file (big file with 16 000 000 lines) with records like > > 2 3 4 > 4 3 2 > 5 4 387 > 5 8 5473 > ... > > and I want to import this to an SQLite table. > Although I can replace all this to INSERT commands very easily, I > would like to ask first if there is any faster method. > > Cheers > Alberto > -- > Alberto Sim〓s > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Importing a big text file (CSV?)
On 7/19/07, Alberto Simões <[EMAIL PROTECTED]> wrote: Hi I have a file (big file with 16 000 000 lines) with records like 2 3 4 4 3 2 5 4 387 5 8 5473 ... and I want to import this to an SQLite table. Although I can replace all this to INSERT commands very easily, I would like to ask first if there is any faster method. Try the .import command. That does the job very fast. -- Puneet Kishor http://punkish.eidesis.org/ Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/ Open Source Geospatial Foundation http://www.osgeo.org/education/ S Policy Fellow, National Academy of Sciences http://www.nas.edu/ - collaborate, communicate, compete = - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Importing a big text file (CSV?)
I have a file (big file with 16 000 000 lines) with records like 2 3 4 4 3 2 5 4 387 5 8 5473 ... and I want to import this to an SQLite table. Although I can replace all this to INSERT commands very easily, I would like to ask first if there is any faster method. Meanwhile I found an '.import' command on SQLite, but I can't find a suitable documentation on how it works. -- Alberto Simões - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] undefined reference to `sqlite3_open'
On Thu, 2007-07-19 at 12:52 +0300, MaaSTaaR wrote:
> Hello ...
>
> firstly, sorry for my bad English.
>
> i am using SQLite with C under Linux, i wrote small file which use Glade,
> GTK and SQLite, but i have problem with SQLite.
>
> this is the command which i used to compile the file : "gcc `pkg-config
> --libs --cflags gtk+-2.0 libglade-2.0 sqlite` -o main main.c"
"sqlite" is version 2. Specify "sqlite3" instead.
[EMAIL PROTECTED]:~> pkg-config --libs sqlite
-lsqlite
[EMAIL PROTECTED]:~> pkg-config --libs sqlite3
-lsqlite3
Dan.
>
> these the problems :
> /tmp/ccxN97zv.o: In function `main':main.c:(.text+0x53): undefined reference
> to `sqlite3_open'
> :main.c:(.text+0x161): undefined reference to `sqlite3_close'
> collect2: ld returned 1 exit status
>
> finally this is my code :
>
> #include
> #include
> #include
> #include
>
> void start(GtkWidget *b,GtkWidget *t)
> {
> const gchar *text;
>
> text = gtk_entry_get_text(GTK_ENTRY(t));
> }
>
> int main(int argc, char *argv[])
> {
> GladeXML *ui;
> GtkWidget *w,*t,*b;
> sqlite3 *db;
> int rc;
>
> rc = sqlite3_open("./db/def.db",);
>
> gtk_init(,);
> ui = glade_xml_new("./gui/main.glade",NULL,NULL);
>
> w = glade_xml_get_widget(ui,"window1");
>
> g_signal_connect(G_OBJECT(w),"delete-event",G_CALLBACK(gtk_main_quit),NULL);
>
> t = glade_xml_get_widget(ui,"word"); // This is label
>
> b = glade_xml_get_widget(ui,"start"); // This is button
> g_signal_connect(G_OBJECT(b),"clicked",G_CALLBACK(start),(gpointer)t);
>
> gtk_main();
>
> sqlite3_close(db);
> }
>
>
> what is this error and how to solve it?
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] Importing a big text file (CSV?)
We are using precompiled insert statements and bind the parameters. The inserts were done in a transaction that is committed and reopened every 1000 iterations. Ciao Sylko -Ursprüngliche Nachricht- Von: Alberto Simões [mailto:[EMAIL PROTECTED] Gesendet: Donnerstag, 19. Juli 2007 11:57 An: sqlite-users@sqlite.org Betreff: [sqlite] Importing a big text file (CSV?) Hi I have a file (big file with 16 000 000 lines) with records like 2 3 4 4 3 2 5 4 387 5 8 5473 ... and I want to import this to an SQLite table. Although I can replace all this to INSERT commands very easily, I would like to ask first if there is any faster method. Cheers Alberto -- Alberto Simões - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Importing a big text file (CSV?)
Hi I have a file (big file with 16 000 000 lines) with records like 2 3 4 4 3 2 5 4 387 5 8 5473 ... and I want to import this to an SQLite table. Although I can replace all this to INSERT commands very easily, I would like to ask first if there is any faster method. Cheers Alberto -- Alberto Simões - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] undefined reference to `sqlite3_open'
Hello ...
firstly, sorry for my bad English.
i am using SQLite with C under Linux, i wrote small file which use Glade,
GTK and SQLite, but i have problem with SQLite.
this is the command which i used to compile the file : "gcc `pkg-config
--libs --cflags gtk+-2.0 libglade-2.0 sqlite` -o main main.c"
these the problems :
/tmp/ccxN97zv.o: In function `main':main.c:(.text+0x53): undefined reference
to `sqlite3_open'
:main.c:(.text+0x161): undefined reference to `sqlite3_close'
collect2: ld returned 1 exit status
finally this is my code :
#include
#include
#include
#include
void start(GtkWidget *b,GtkWidget *t)
{
const gchar *text;
text = gtk_entry_get_text(GTK_ENTRY(t));
}
int main(int argc, char *argv[])
{
GladeXML *ui;
GtkWidget *w,*t,*b;
sqlite3 *db;
int rc;
rc = sqlite3_open("./db/def.db",);
gtk_init(,);
ui = glade_xml_new("./gui/main.glade",NULL,NULL);
w = glade_xml_get_widget(ui,"window1");
g_signal_connect(G_OBJECT(w),"delete-event",G_CALLBACK(gtk_main_quit),NULL);
t = glade_xml_get_widget(ui,"word"); // This is label
b = glade_xml_get_widget(ui,"start"); // This is button
g_signal_connect(G_OBJECT(b),"clicked",G_CALLBACK(start),(gpointer)t);
gtk_main();
sqlite3_close(db);
}
what is this error and how to solve it?
[sqlite] timestamp to date in a trigger
Hello.
I would like to convert a TIMESTAMP into date thanks to a trigger. I tried
with strftime but it doesn't work (see below). Is there an other solution
?
I have a table like this :
CREATE TABLE contrat(UniqueID INTEGER PRIMARY KEY, tstp TIMESTAMP NOT
NULL, date1 DATE NULL);
I have created a trigger that puts in date1 the date conversion of tstp :
CREATE TRIGGER tstpTOdate1 AFTER INSERT ON contrat
BEGIN
UPDATE contrat SET date1=strftime("%d-%m-%Y", new.tstp) WHERE
UniqueID=new.UniqueID;
END;
But when strftime doesn't seem to work :
>INSERT INTO contrat(tstp) VALUES("1184834152");
>SELECT * FROM contrat;
1|1184834152|16-08-3239253
-
Thank you,
Charly CAULET
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Prepared Statement (select * from x where y in ());
Long ago and far away, I build a database abstraction layer which used
?@ for this. So you'd say something like:
stmt = prepare("select * from table where xyz in (?@)");
bind_array(stmt, 0, arrayRef);
The library would take the array, quote each element, and separate
them with commas. It was pretty nice when you needed it.
Unfortunately, this wasn't for sqlite :-).
One option would be to write code to build the statement with the
right number of ?, then bind that many. Very annoying.
Another option would be to create a temporary table, run through the
array doing individual INSERT statements, then "select * from table
where xyz in (select * from temp_table)". Also very annoying.
-scott
On 7/18/07, RaghavendraK 70574 <[EMAIL PROTECTED]> wrote:
May be my Q is not clear,
Problem is as shown below,
SQL = "select * from table where xyz in (:abc);"
bind_text("'ab','xy','zx','123'")
bind_text will append '' at the begining and at end making it as one string
rather
than ab,xy,zx.
regards
ragha
**
This email and its attachments contain confidential information from HUAWEI,
which is intended only for the person or entity whose address is listed above.
Any use of the information contained herein in any way (including, but not
limited to, total or partial disclosure, reproduction, or dissemination) by
persons other than the intended recipient(s) is prohibited. If you receive this
e-mail in error, please notify the sender by phone or email immediately and
delete it!
*
- Original Message -
From: Bharath Booshan L <[EMAIL PROTECTED]>
Date: Thursday, July 19, 2007 11:56 am
Subject: Re: [sqlite] Prepared Statement (select * from x where y in ());
>
>
>
> > 1)How can we prepare a SQliteStatement for the following type of
> select,> select * from tablename where column in (?);
> > ?: we don;t know the length of this string.
>
>
> Pass any value less than zero as 3rd parameter to sqlite3_prepare
> function,in which case, characters up to the first null terminator
> will be
> considered.
>
> Cheers,
>
> Bharath Booshan L.
>
>
>
> ---
> Robosoft Technologies - Come home to Technology
>
> Disclaimer: This email may contain confidential material. If you
> were not an intended recipient, please notify the sender and
> delete all copies. Emails to and from our network may be logged
> and monitored. This email and its attachments are scanned for
> virus by our scanners and are believed to be safe. However, no
> warranty is given that this email is free of malicious content or
> virus.
>
>
>
> ---
> --
> To unsubscribe, send email to [EMAIL PROTECTED]
> ---
> --
>
>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
