Re: [sqlite] Query to Find number of distinct records
Hi, What is the error? It seems to me that both of these should work: select count(DISTINCT Name) from TableA select count(DISTINCT Name) as nameCount from TableA Regards, Eugene Wee Bharath Booshan L wrote: > Thanks for your quick response, > >> select count(DISTINCT Name) nameCount from TableA > > This is not working :(. I am getting the same error. > > -- > Bharath ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query to Find number of distinct records
Yeah!! I got it right this time. Select count(*) from ( select DISTINCT Name from TableA); But what's not getting into my mind is the difference b/w the following two queries: Select count( Name) from TableA -- works fine Select count(DISTINCT Name) from TableA -- doesn't work, Any reason? On 2/27/08 12:34 PM, "Bharath Booshan L" <[EMAIL PROTECTED]> wrote: > Hello All, > > This might be simple question, but am not getting the SQL query right for > my problem. > > > For eg, consider following table > > NonUniqueNo Name > - > 23 A > 23 B > 24 C > 25 A > 23 E > > > How can I find the number of people for which an entry has been recorded in > my table? > > The answer I am expecting is 4 ( i.e. A,B,C,E) > > Using query "select count(DISTINCT Name) from TableA" results in an error. > > Could anyone please post a SQL query for my problem? > > Thanks in advance, > > Bharath > > > > --- > 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. > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > --- 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. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query to Find number of distinct records
Thanks for your quick response, > select count(DISTINCT Name) nameCount from TableA This is not working :(. I am getting the same error. -- Bharath On 2/27/08 12:38 PM, "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote: > Try > select count(DISTINCT Name) nameCount from TableA > > Shibu Narayanan > Consultant, PrimeSourcing Division, Investment Banking Group > Tel.Office: 91-80-2208-6270 or 91-80-6659-6270 > e-mail: [EMAIL PROTECTED] > The answer is 42. > > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Bharath Booshan L > Sent: Wednesday, February 27, 2008 12:35 PM > To: Discussion of SQLite Database > Subject: [sqlite] Query to Find number of distinct records > > Hello All, > > This might be simple question, but am not getting the SQL query right > for > my problem. > > > For eg, consider following table > > NonUniqueNo Name > - > 23 A > 23 B > 24 C > 25 A > 23 E > > > How can I find the number of people for which an entry has been recorded > in > my table? > > The answer I am expecting is 4 ( i.e. A,B,C,E) > > Using query "select count(DISTINCT Name) from TableA" results in an > error. > > Could anyone please post a SQL query for my problem? > > Thanks in advance, > > Bharath > > > > --- > 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. > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > DISCLAIMER: > This message contains privileged and confidential information and is intended > only for an individual named. If you are not the intended recipient, you > should not disseminate, distribute, store, print, copy or deliver this > message. Please notify the sender immediately by e-mail if you have received > this e-mail by mistake and delete this e-mail from your system. E-mail > transmission cannot be guaranteed to be secure or error-free as information > could be intercepted, corrupted, lost, destroyed, arrive late or incomplete or > contain viruses. The sender, therefore, does not accept liability for any > errors or omissions in the contents of this message which arise as a result of > e-mail transmission. If verification is required, please request a hard-copy > version. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > --- 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. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query to Find number of distinct records
Try select count(DISTINCT Name) nameCount from TableA Shibu Narayanan Consultant, PrimeSourcing Division, Investment Banking Group Tel.Office: 91-80-2208-6270 or 91-80-6659-6270 e-mail: [EMAIL PROTECTED] The answer is 42. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Bharath Booshan L Sent: Wednesday, February 27, 2008 12:35 PM To: Discussion of SQLite Database Subject: [sqlite] Query to Find number of distinct records Hello All, This might be simple question, but am not getting the SQL query right for my problem. For eg, consider following table NonUniqueNo Name - 23 A 23 B 24 C 25 A 23 E How can I find the number of people for which an entry has been recorded in my table? The answer I am expecting is 4 ( i.e. A,B,C,E) Using query "select count(DISTINCT Name) from TableA" results in an error. Could anyone please post a SQL query for my problem? Thanks in advance, Bharath --- 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. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users DISCLAIMER: This message contains privileged and confidential information and is intended only for an individual named. If you are not the intended recipient, you should not disseminate, distribute, store, print, copy or deliver this message. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete or contain viruses. The sender, therefore, does not accept liability for any errors or omissions in the contents of this message which arise as a result of e-mail transmission. If verification is required, please request a hard-copy version. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Query to Find number of distinct records
Hello All, This might be simple question, but am not getting the SQL query right for my problem. For eg, consider following table NonUniqueNo Name - 23 A 23 B 24 C 25 A 23 E How can I find the number of people for which an entry has been recorded in my table? The answer I am expecting is 4 ( i.e. A,B,C,E) Using query "select count(DISTINCT Name) from TableA" results in an error. Could anyone please post a SQL query for my problem? Thanks in advance, Bharath --- 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. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Encryption?
hi, yes i know, my changed my webserver to lighttpd but i had no time to finish the complete the installation because i'm on a business trip right now :-( but its still working if you know the right urls :-) please try http://greschenz.dyndns.org/index.php for the main page http://greschenz.dyndns.org/index.php?title=SQLITE for a usage sample http://greschenz.dyndns.org/download.php?id=100 for the compression code http://greschenz.dyndns.org/download.php?id=101 for the crypting code yes, css is not running at the moment, it looks very ugly, i know ;-) cu, gg > Hi Günter, > You mentioned on the sqlite newsgroups that you had some encryption > modules for sqlite, but I can't get to your website to take a look at > them. Would it be possible to make them available to me sometime; I'd > really appreciate the help. > Thanks, > - Dom ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] IF...THEN constructs
Hi everyone, I'm used to doing lengthy T-SQL programming in SQL Server, so this is kinda new to me. How does one replicate doing IF...THEN conditional blocks in SQLite 3? Is it all nested CASE statements within the SQL statement(s)? Or should I figure out the logic in my client (web, desktop, XUL, et al.) and then generate a SQL statement dynamically and send that to the DB? Thanks! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Concatenating values from multiple varchar fields
On 2/26/08, Jason Salas <[EMAIL PROTECTED]> wrote: > I'm new to SQLite, coming over from SQL Server. I often do string > concatenation like so: > > lastName + ', ' + firstName as [name] from myTable > pretty standard SQL way is lastName || ', ' || firstName AS name > But it tries to run a math computation and returns '0.0' for each > field. I've tried some other concat operators that I know of, but none > work. How is this achieved in SQLite? > > Thanks! > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Puneet Kishor http://punkish.eidesis.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Concatenating values from multiple varchar fields
Never mind folks...I got it! Double-pipes does the trick! "||" lastName || ', ' || firstName as [name] from myTable Awesome. Admittedly confusing at first glance, but awesome. :-) Jason Salas wrote: > I'm new to SQLite, coming over from SQL Server. I often do string > concatenation like so: > > lastName + ', ' + firstName as [name] from myTable > > But it tries to run a math computation and returns '0.0' for each > field. I've tried some other concat operators that I know of, but none > work. How is this achieved in SQLite? > > Thanks! > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Concatenating values from multiple varchar fields
Two pipes || Jason Salas wrote: > I'm new to SQLite, coming over from SQL Server. I often do string > concatenation like so: > > lastName + ', ' + firstName as [name] from myTable > > But it tries to run a math computation and returns '0.0' for each > field. I've tried some other concat operators that I know of, but none > work. How is this achieved in SQLite? > > Thanks! > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Concatenating values from multiple varchar fields
I'm new to SQLite, coming over from SQL Server. I often do string concatenation like so: lastName + ', ' + firstName as [name] from myTable But it tries to run a math computation and returns '0.0' for each field. I've tried some other concat operators that I know of, but none work. How is this achieved in SQLite? Thanks! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem to compile 3.5.6.
Looks like you have compiled Sqlite to use readline but don't have the link library. David Michal wrote: > Hello, > > I have problem to compile it on Slackware 11 x86_64 > > > > gcc -g -O2 -I. -I../sqlite-3.5.6/src -DNDEBUG -DSQLITE_THREADSAFE=1 > -DSQLITE_THREAD_OVERRIDE_LOCK=-1 -DSQLITE_OMIT_LOAD_EXTENSION=1 > -DHAVE_READLINE=1 -I/usr/include/readline -o .libs/sqlite3 > ../sqlite-3.5.6/src/shell.c ./.libs/libsqlite3.so -lpthread > > /tmp/cc9O4O7b.o: In function `process_input': > > ../sqlite-3.5.6/src/shell.c:288: undefined reference to `readline' > > ../sqlite-3.5.6/src/shell.c:290: undefined reference to `add_history' > > /tmp/cc9O4O7b.o: In function `main': > > ../sqlite-3.5.6/src/shell.c:2067: undefined reference to `read_history' > > ../sqlite-3.5.6/src/shell.c:2071: undefined reference to > `stifle_history' > > ../sqlite-3.5.6/src/shell.c:2072: undefined reference to `write_history' > > collect2: ld returned 1 exit status > > make: *** [sqlite3] Error 1 > > > > any idea of what I am missing on my system? > > > > Thanks, > > David > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Use of SUM() causes Alignment Traps on ARM arch
This is not necessarily a bug, but I thought I might point out that use of the SUM() function causes my program to generate alignment traps on an ARM system. They seem to be proportional to the amount of data in the table. While no harm is caused (I have the kernel set to fix these problems and recover), it does slow the execution of any SQL statements with SUM() considerably. Just I thought I would just throw it out there. -- Rich Rattanni ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Problem to compile 3.5.6.
Hello, I have problem to compile it on Slackware 11 x86_64 gcc -g -O2 -I. -I../sqlite-3.5.6/src -DNDEBUG -DSQLITE_THREADSAFE=1 -DSQLITE_THREAD_OVERRIDE_LOCK=-1 -DSQLITE_OMIT_LOAD_EXTENSION=1 -DHAVE_READLINE=1 -I/usr/include/readline -o .libs/sqlite3 ../sqlite-3.5.6/src/shell.c ./.libs/libsqlite3.so -lpthread /tmp/cc9O4O7b.o: In function `process_input': ../sqlite-3.5.6/src/shell.c:288: undefined reference to `readline' ../sqlite-3.5.6/src/shell.c:290: undefined reference to `add_history' /tmp/cc9O4O7b.o: In function `main': ../sqlite-3.5.6/src/shell.c:2067: undefined reference to `read_history' ../sqlite-3.5.6/src/shell.c:2071: undefined reference to `stifle_history' ../sqlite-3.5.6/src/shell.c:2072: undefined reference to `write_history' collect2: ld returned 1 exit status make: *** [sqlite3] Error 1 any idea of what I am missing on my system? Thanks, David ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problems with Floating Point
The problem is that on ARM there are two ways to represent 64bit floating point numbers when using software floating point emulation, FPA and VFE. FPA uses little endian byte order but big endian word order and is the default for most GCC configurations (why, I have no idea...). VFE is fully little endian and is required if your code may ever run on any of the chips with HW FP co-processors. If your system is setup to use FPA you can define the SQLITE_MIXED_ENDIAN_64BIT_FLOAT flag to get SQLite to properly construct floats/doubles that the rest of the system will understand. -Jeff On Tue, Feb 26, 2008 at 2:38 AM, Michael Penkov <[EMAIL PROTECTED]> wrote: > Hello, > > I'm attempting to use sqlite3 (version 3.5.6, cross-compiled using gcc > 2.95.3) on a Sharp Zaurus CL-3200. Kernel is Linux 2.4.20, armv5tel, > using NetWinder Floating Point Emulator V0.95. > > Overall, it works well, but I am encountering strange problems with > reading and writing floating point values. > > Here is a file to reproduce the problem: > http://repose.ath.cx/tracker/anki/file99/sqlite.tar.gz > > sqlite.tar.gz contains the databases created on a PC and a Zaurus. There is > only one table (float_test) with a single column (float) with a single row > (contains value 1.1). Data type for float is numeric(10,2). Run > test.sh to see how > differently 1.1 is represented between the two platforms. > > Could someone explain why this is so, and suggest what could be done > to solve the problem? Ideally, I would like the database to be > transferable between different platforms. > > A colleague using a Zaurus (albeit a different O/S) has also > reproduced the issue. His specs were: > > Linux kurobox 2.6.20 #1 PREEMPT Thu Aug 30 15:58:28 UTC 2007 armv5tel > GNU/Linux > NetWinder Floating Point Emulator V0.97 (double precision) > debian libsqlite3-0 3.4.2-2 > > A small discussion we've been having about the issue is located here: > http://repose.ath.cx/tracker/anki/issue436, in case I've missed > anything in this initial post. > > Looking forward to your replies. > > Cheers and regards, > Michael > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problems with Floating Point
Michael Penkov wrote: > Hello, > > I'm attempting to use sqlite3 (version 3.5.6, cross-compiled using gcc > 2.95.3) on a Sharp Zaurus CL-3200. Kernel is Linux 2.4.20, armv5tel, > using NetWinder Floating Point Emulator V0.95. > > Overall, it works well, but I am encountering strange problems with > reading and writing floating point values. > > Here is a file to reproduce the problem: > http://repose.ath.cx/tracker/anki/file99/sqlite.tar.gz > > sqlite.tar.gz contains the databases created on a PC and a Zaurus. There is > only one table (float_test) with a single column (float) with a single row > (contains value 1.1). Data type for float is numeric(10,2). Run > test.sh to see how > differently 1.1 is represented between the two platforms. > > Could someone explain why this is so, and suggest what could be done > to solve the problem? Ideally, I would like the database to be > transferable between different platforms. > > A colleague using a Zaurus (albeit a different O/S) has also > reproduced the issue. His specs were: > > Linux kurobox 2.6.20 #1 PREEMPT Thu Aug 30 15:58:28 UTC 2007 armv5tel > GNU/Linux > NetWinder Floating Point Emulator V0.97 (double precision) > debian libsqlite3-0 3.4.2-2 > > A small discussion we've been having about the issue is located here: > http://repose.ath.cx/tracker/anki/issue436, in case I've missed > anything in this initial post. > > Looking forward to your replies. > Michael, There have been various discussions of ARM floating point data in SQLite. You might find something useful in the archives. A search for ARM floating point int htis list at gmane returns lots of hits. http://search.gmane.org/?query=ARM+floating+point=gmane.comp.db.sqlite.general It seems ARM has some unusual handling of endianness that may be causing your problems. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Good tutorial/book on SQLite in French?
Hi I'm looking for a tutorial in French for my... 70-year-old father, who's also not quite at ease with relational databases and SQL. SQLite did the trick to get him to stop using flat-files with home made performance improvements, and move on to SQL. The few books on SQLite haven't been translated, and I haven't found much on the web. Does someone of such a thing? Thank you. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Spell
http://sqlite.org/34to35.html : 2.1 The Virtual File System Object The new OS interface for SQLite is built around an object named sqlite3_vfs. The "vfs" standard for "Virtual File System". stands ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Using SQLite API with Excel VBA
Hi,
I have a question for you.
I need use SQLite in my Excel application. Unfortunately from some
reasons I'm forced to
use pure SQLite API without any VBA wrappers.
So, I've written following declarations in VBA module:
Type SQLite3
End Type
Declare Function sqlite3_open Lib "C:\sqlite3_engine.dll" (ByVal dbName
as String, dbStruct as SQLite3)
The problem is I have no idea how to map sqlite3 struct into VBA code so
I can't invoke opening function.
Could you give me some tips? Or may you have done it? Below I pasted
sqlite3 struct definition from soruce code.
Thanks a lot,
mike
struct sqlite3 {
sqlite3_vfs *pVfs;/* OS Interface */
int nDb; /* Number of backends currently in use */
Db *aDb; /* All backends */
int flags;/* Miscellanous flags. See below */
int openFlags;/* Flags passed to sqlite3_vfs.xOpen() */
int errCode; /* Most recent error code (SQLITE_*) */
int errMask; /* & result codes with this before
returning */
u8 autoCommit;/* The auto-commit flag. */
u8 temp_store;/* 1: file 2: memory 0: default */
u8 mallocFailed; /* True if we have seen a malloc failure */
signed char nextAutovac; /* Autovac setting after VACUUM if >=0 */
int nTable; /* Number of tables in the database */
CollSeq *pDfltColl; /* The default collating sequence
(BINARY) */
i64 lastRowid;/* ROWID of most recent insert (see
above) */
i64 priorNewRowid;/* Last randomly generated ROWID */
int magic;/* Magic number for detect library misuse */
int nChange; /* Value returned by sqlite3_changes() */
int nTotalChange; /* Value returned by
sqlite3_total_changes() */
sqlite3_mutex *mutex; /* Connection mutex */
struct sqlite3InitInfo { /* Information used during initialization */
int iDb;/* When back is being initialized */
int newTnum;/* Rootpage of table being initialized */
u8 busy;/* TRUE if currently initializing */
} init;
int nExtension; /* Number of loaded extensions */
void **aExtension;/* Array of shared libraray handles */
struct Vdbe *pVdbe; /* List of active virtual machines */
int activeVdbeCnt;/* Number of vdbes currently executing */
void (*xTrace)(void*,const char*);/* Trace function */
void *pTraceArg; /* Argument to the trace
function */
void (*xProfile)(void*,const char*,u64); /* Profiling function */
void *pProfileArg;/* Argument to profile
function */
void *pCommitArg; /* Argument to xCommitCallback() */
int (*xCommitCallback)(void*);/* Invoked at every commit. */
void *pRollbackArg; /* Argument to xRollbackCallback() */
void (*xRollbackCallback)(void*); /* Invoked at every commit. */
void *pUpdateArg;
void (*xUpdateCallback)(void*,int, const char*,const char*,sqlite_int64);
void(*xCollNeeded)(void*,sqlite3*,int eTextRep,const char*);
void(*xCollNeeded16)(void*,sqlite3*,int eTextRep,const void*);
void *pCollNeededArg;
sqlite3_value *pErr; /* Most recent error message */
char *zErrMsg;/* Most recent error message (UTF-8
encoded) */
char *zErrMsg16; /* Most recent error message (UTF-16
encoded) */
union {
int isInterrupted; /* True if sqlite3_interrupt has been
called */
double notUsed1;/* Spacer */
} u1;
#ifndef SQLITE_OMIT_AUTHORIZATION
int (*xAuth)(void*,int,const char*,const char*,const char*,const char*);
/* Access authorization function */
void *pAuthArg; /* 1st argument to the access auth
function */
#endif
#ifndef SQLITE_OMIT_PROGRESS_CALLBACK
int (*xProgress)(void *); /* The progress callback */
void *pProgressArg; /* Argument to the progress callback */
int nProgressOps; /* Number of opcodes for progress
callback */
#endif
#ifndef SQLITE_OMIT_VIRTUALTABLE
Hash aModule; /* populated by sqlite3_create_module() */
Table *pVTab; /* vtab with active Connect/Create method */
sqlite3_vtab **aVTrans; /* Virtual tables with open transactions */
int nVTrans; /* Allocated size of aVTrans */
#endif
Hash aFunc; /* All functions that can be in SQL exprs */
Hash aCollSeq;/* All collating sequences */
BusyHandler busyHandler; /* Busy callback */
int busyTimeout; /* Busy handler timeout, in msec */
Db aDbStatic[2]; /* Static space for the 2 default
backends */
#ifdef SQLITE_SSE
sqlite3_stmt *pFetch; /* Used by SSE to fetch
Re: [sqlite] insert on windows server 2003 very slow
Hi, ok, my program used to run for hours, but when using begin and commit it takes only a few seconds. :-) Thank you so much for your help! Best regards, Angela Michael Ruck wrote: > Hi Angela, > > for every iteration of your while loop a new transaction is opened and > commited. You need to wrap the loop in manual transactions using BEGIN and > COMMIT statements. Creating transaction logs is an expensive operation, when > compared to the insert itself. > > Regards, > Michael > > -Ursprüngliche Nachricht- > Von: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] Im Auftrag von Angela Kramer > Gesendet: Dienstag, 26. Februar 2008 10:29 > An: Eugene Wee > Cc: General Discussion of SQLite Database > Betreff: Re: [sqlite] insert on windows server 2003 very slow > > Hi Eugene, > > thank you for your reply. > > Eugene Wee wrote: >> Hi Angela, >> >> Did you wrap the inserts in a transaction? > > I do not start a transaction manually. However a transaction is probably > started automatically. But: "Automatically started transactions are > committed at the conclusion of the command." > (http://www.sqlite.org/lang_transaction.html) > > My program executes only three queries: > 1. create table contacts ... > 2. "insert into contacts values (?, ?, ?, ?, ?, ?, ?, ?, ?);" > 3. "create index idx_last_name on contacts (lastName ASC)" > > The second query is run in a while loop (about 200.000 times). > >> Regards, >> Eugene Wee >> >> Angela Kramer wrote: >>> Hi, >>> >>> I've written a java programm which reads data from a file and inserts >>> them into a sqlite database. In order to speed up this process I use >>> PreparedStatement. >>> >>> On a computer running Windows XP inserting one line into the database >>> takes about 16 milliseconds. On a machine with Windows Server 2003 >>> inserting the same line takes 150 to 200 milliseconds. > I execute the same jar-file and use exactly the same data on both machines. >>> Where might this big difference come from? >>> >>> Thank you for your answers in advance! >>> Best regards > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] insert on windows server 2003 very slow
Hi Angela, for every iteration of your while loop a new transaction is opened and commited. You need to wrap the loop in manual transactions using BEGIN and COMMIT statements. Creating transaction logs is an expensive operation, when compared to the insert itself. Regards, Michael -Ursprüngliche Nachricht- Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Im Auftrag von Angela Kramer Gesendet: Dienstag, 26. Februar 2008 10:29 An: Eugene Wee Cc: General Discussion of SQLite Database Betreff: Re: [sqlite] insert on windows server 2003 very slow Hi Eugene, thank you for your reply. Eugene Wee wrote: > Hi Angela, > > Did you wrap the inserts in a transaction? I do not start a transaction manually. However a transaction is probably started automatically. But: "Automatically started transactions are committed at the conclusion of the command." (http://www.sqlite.org/lang_transaction.html) My program executes only three queries: 1. create table contacts ... 2. "insert into contacts values (?, ?, ?, ?, ?, ?, ?, ?, ?);" 3. "create index idx_last_name on contacts (lastName ASC)" The second query is run in a while loop (about 200.000 times). > Regards, > Eugene Wee > > Angela Kramer wrote: >> Hi, >> >> I've written a java programm which reads data from a file and inserts >> them into a sqlite database. In order to speed up this process I use >> PreparedStatement. >> >> On a computer running Windows XP inserting one line into the database >> takes about 16 milliseconds. On a machine with Windows Server 2003 >> inserting the same line takes 150 to 200 milliseconds. I execute the same jar-file and use exactly the same data on both machines. >> >> Where might this big difference come from? >> >> Thank you for your answers in advance! >> Best regards ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] insert on windows server 2003 very slow
Hi Eugene, thank you for your reply. Eugene Wee wrote: > Hi Angela, > > Did you wrap the inserts in a transaction? I do not start a transaction manually. However a transaction is probably started automatically. But: "Automatically started transactions are committed at the conclusion of the command." (http://www.sqlite.org/lang_transaction.html) My program executes only three queries: 1. create table contacts ... 2. "insert into contacts values (?, ?, ?, ?, ?, ?, ?, ?, ?);" 3. "create index idx_last_name on contacts (lastName ASC)" The second query is run in a while loop (about 200.000 times). > Regards, > Eugene Wee > > Angela Kramer wrote: >> Hi, >> >> I've written a java programm which reads data from a file and inserts >> them into a sqlite database. In order to speed up this process I use >> PreparedStatement. >> >> On a computer running Windows XP inserting one line into the database >> takes about 16 milliseconds. On a machine with Windows Server 2003 >> inserting the same line takes 150 to 200 milliseconds. I execute the same jar-file and use exactly the same data on both machines. >> >> Where might this big difference come from? >> >> Thank you for your answers in advance! >> Best regards ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Two processes (First is RW, second RO)
Hi,
I would want to use the sqlite database in my usb-ethernet(TCP/IP)
converter. It should be utmost realtime. I would like check the state of
converter via www. I write test application, if it is usable. But I
encounter a problem.
The first process selects configuration from databases, than reads data
from my usb device and stores them into the database file. The second
process only selects the data and prints them out. The problem is in that,
that the second process doesn't "view" all changes made by the first
process.
First process psedocode:
while (true) {
c = sql("select count(*) from t1");
sql("select cfg from t1");
for (i = 0; i < c; i++) {
cfg = sql_step();
data[i] = read_usb(cfg);
}
sql_finalize(); // select
for (i = 0; i < c; i++) {
sql("begin exclusive");
sql("update t1 data");
sql_finalize(); //update
sql("commit");
}
}
Second process pseudocode:
while (true) {
sql("select data from t1");
printf(data);
sql_finalize(); // select
sql_sleep(1);
}
Thank you for your recommendations, how to optimize this concept.
--
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Problems with Floating Point
Hello, I'm attempting to use sqlite3 (version 3.5.6, cross-compiled using gcc 2.95.3) on a Sharp Zaurus CL-3200. Kernel is Linux 2.4.20, armv5tel, using NetWinder Floating Point Emulator V0.95. Overall, it works well, but I am encountering strange problems with reading and writing floating point values. Here is a file to reproduce the problem: http://repose.ath.cx/tracker/anki/file99/sqlite.tar.gz sqlite.tar.gz contains the databases created on a PC and a Zaurus. There is only one table (float_test) with a single column (float) with a single row (contains value 1.1). Data type for float is numeric(10,2). Run test.sh to see how differently 1.1 is represented between the two platforms. Could someone explain why this is so, and suggest what could be done to solve the problem? Ideally, I would like the database to be transferable between different platforms. A colleague using a Zaurus (albeit a different O/S) has also reproduced the issue. His specs were: Linux kurobox 2.6.20 #1 PREEMPT Thu Aug 30 15:58:28 UTC 2007 armv5tel GNU/Linux NetWinder Floating Point Emulator V0.97 (double precision) debian libsqlite3-0 3.4.2-2 A small discussion we've been having about the issue is located here: http://repose.ath.cx/tracker/anki/issue436, in case I've missed anything in this initial post. Looking forward to your replies. Cheers and regards, Michael ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] insert on windows server 2003 very slow
Hi Angela, Did you wrap the inserts in a transaction? Regards, Eugene Wee Angela Kramer wrote: > Hi, > > I've written a java programm which reads data from a file and inserts > them into a sqlite database. In order to speed up this process I use > PreparedStatement. > > On a computer running Windows XP inserting one line into the database > takes about 16 milliseconds. On a machine with Windows Server 2003 > inserting the same line takes 150 to 200 milliseconds. > > Where might this big difference come from? > > Thank you for your answers in advance! > Best regards ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] insert on windows server 2003 very slow
Hi, I've written a java programm which reads data from a file and inserts them into a sqlite database. In order to speed up this process I use PreparedStatement. On a computer running Windows XP inserting one line into the database takes about 16 milliseconds. On a machine with Windows Server 2003 inserting the same line takes 150 to 200 milliseconds. Where might this big difference come from? Thank you for your answers in advance! Best regards ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
