Re: [sqlite] Temp table in which dir?

Hi, there is a pragma temp_store_directory which defines this. See http://www.sqlite.org/pragma.html#pragma_temp_store_directory Martin R S schrieb: Hi, I programatically create a Temp table for my DB. Does the Temp table reside in the same directory as my Database or in another directory

[sqlite] Re: iso8859-9 encoding problem

Veysel Harun Sahin <[EMAIL PROTECTED]> wrote: I have already started using sqlite. The version which i use is 3.2.7. I have no problems except sorting. When i query a table using the "order by" clause i can not get true sorting according to iso8859-9 charset. The special characters of my

Re: [sqlite] Concat?

El Martes, 25 de Octubre de 2005 17:08, Puneet Kishor escribió: > On Oct 25, 2005, at 6:08 PM, Alfredo Cole wrote: > > What is the equivalent of: > > > > select concat(col1,col2,col3) from table > > > > in SQlite? > > SELECT col1 || col2 || col3 FROM table > > should do, no? > > -- >

Re: [sqlite] .import null values

> Affinity Modes ... how do you activate these? I don't believe strict affinity has been implemented; someone correct me if that's not right. Regards

Re: [sqlite] Is this query optimized?

count() has always done a full table scan. As far as I know, nothing has been done to optimize it, as your observations seem to confirm. Regards

Re: [sqlite] Concat?

On Oct 25, 2005, at 6:08 PM, Alfredo Cole wrote: What is the equivalent of: select concat(col1,col2,col3) from table in SQlite? SELECT col1 || col2 || col3 FROM table should do, no? -- Puneet Kishor

[sqlite] Concat?

Hi: What is the equivalent of: select concat(col1,col2,col3) from table in SQlite? How can I achieve the same results? Thank you. -- Alfredo J. Cole Grupo ACyC

Re: [sqlite] Optimal page size

> For what it is worth I did some trials in matching page size to the > underlying virtual memory page size and was surprised to find that I did > not measure any significant performance change. My guess is that it is > the structure of the data in your application which would be sensitive >

Re: [sqlite] .import null values

It sounds like you loose some of the economy of sqlite by needing a more sophisticated front-end coded up to drive it (for example, to do type checking)... At any rate, my needs aren't too complicated. I was looking for a portable replacement for MS Access to hold my research results (long time

Re: [sqlite] Convert SQLite to Java

>>Java libraries linked with C (via JNI) generally have to run with the exact >>same version of Java they were built with. > > Uh-oh, Sun advertises something else. Hmm... I may have been confused in the matter by having had (unrelated) problems. I would go with what Sun advertises. Randy --

[sqlite] iso8859-9 encoding problem

Hi, I have already started using sqlite. The version which i use is 3.2.7. I have no problems except sorting. When i query a table using the "order by" clause i can not get true sorting according to iso8859-9 charset. The special characters of my encoding are seen at the end of the list. What can

[sqlite] iso8859-9 encoding problem

Hi, I have already started using sqlite. The version which i use is 3.2.7. I have no problems except sorting. When i query a table using the "order by -- Veysel Harun Sahin

Re: [sqlite] .import null values

David Finlayson wrote: Thanks for the link . In section 6 of this page there is mention of Affinity Modes, how do you activate these? See section 2.1 of that page. As it is now you can get completely irrational behavior with mathematical operators:

[sqlite] Temp table in which dir?

Hi, I programatically create a Temp table for my DB. Does the Temp table reside in the same directory as my Database or in another directory (/var/tmp I suppose). Thanks?

Re: [sqlite] built-in functrion suggestion: size of blob

The built-in Sqlite length() function works just fine on blobs: sqlite> select length(X'00'); 5 Why do you think it doesn't? --- Lloyd Dupont <[EMAIL PROTECTED]> wrote: > I look into the build in function of SQLite and saw there is a function to > know the length of a > string (in

Re: [sqlite] Is this query optimized?

Actually I have an index on both columnVal (a varchar) and insertTime (bigint). On 10/25/05, Martin Engelschalk <[EMAIL PROTECTED]> wrote: > > Hi, > > if the where clause in your subquery is not supported by an index (and i > suspect this is the case), sqlite has to do a full table scan. >

Re: [sqlite] Is this query optimized?

Hi, if the where clause in your subquery is not supported by an index (and i suspect this is the case), sqlite has to do a full table scan. Therefore, the execution time is you observe is to be expected. I think thar you can change your query to something like (i didn't test it,) select

Re: [sqlite] .import null values

Thanks for the link . In section 6 of this page there is mention of Affinity Modes, how do you activate these? As it is now you can get completely irrational behavior with mathematical operators: sqlite> create table temp (a INTEGER); sqlite> insert into

[sqlite] Is this query optimized?

Hi, I am trying to use this query and notice that the execution time increasing linearly as the Table size increases. select totalCount from (select count(*) as totalCount from myTable where (insertionTime BETWEEN and ) and columnVal > '0' group by columnVal) where totalCount > 10; Diff between

Re: [sqlite] sqlite on SunOS 5.6 (Solaris Sparc 6)

> Can anyone guide me to where "fdatasync" should be? (maybe) Regards

[sqlite] begin transaction and commit

Hello, Do I need the 'begin transaction' and 'commit' ? It seems it works without them also. I'm not sure I fully understeand wy I need them. If someone can explain in simple English ? I understeand that if I use them, and some serious error occures then there is a rollback so that my

Re[2]: [sqlite] getting only count of records

Hi Guillaume, > if restricting in the where clause on VehicleID, then you should create > an index on VehicleID. I' not sure I understeand correct. Does this mean that if I uses 'where' clauses it is not needed to make indexes and it make index itself when needed ? By now I have made an index

Re: [sqlite] SQLite 3.2.5 and Mac OSX network folders

What are those reasons and is there any expectation that they can be made available as patches for folks who build SQLite privately? I cannot use the libsqlite3.dylib that comes with Mac OSX 10.4 since my application needs to run on 10.3.9 too and there is no equivalent static version that I can

Re: [sqlite] sqlite on SunOS 5.6 (Solaris Sparc 6)

Thanks, changing my path order and replacing awk with gawk solved the (first) problem. Now I am getting: gcc -g -O2 -DOS_UNIX=1 -DHAVE_USLEEP=1 -I. -I./src -DNDEBUG - DTHREADSAFE=0 -DSQLITE_OMIT_CURSOR -DHAVE_READLINE=1 - I/usr/local/include/readline -o .libs/sqlite3 ./src/shell.c

Re: [sqlite] FYI: Fortran interface to SQLite

Hello, Very interesting, I planned to do some work (not done yet) to provide my division with some examples using sqlite with fortran 90/95 (to replace a home pivot format in numerical computing : we use f90/95). So, if you have a release, you want to share, I will be happy to test it on our

Re: [sqlite] .import null values

> >sqlite> create table t (a, b integer, c real); > this is a great explanation, but, why does 1,2,3 return as > text,integer,integer and not integer,integer,integer? I suspect that it's because no type was specified for field 'a', so everything is interpreted as text. Brad

Re: [sqlite] .import null values

On Oct 25, 2005, at 10:02 AM, Dennis Cote wrote: .. The following shows what happens when some sample data is imported into tables with different column types (note the spaces after the commas in the last three rows). 1,2,3 3.14,1.414,2.718 5+6,7-8,9*9 1, 2, 3 3.14, 1.414, 2.718 5+6, 7-8,

Re: [sqlite] Optimal page size

For what it is worth I did some trials in matching page size to the underlying virtual memory page size and was surprised to find that I did not measure any significant performance change. My guess is that it is the structure of the data in your application which would be sensitive to page

Re: [sqlite] Convert SQLite to Java

On Tue, Oct 25, 2005 at 10:05:31AM -0500, John Stanton wrote: > I've been worn down by my quest for a comparable product to SQLite > written in pure Java and have come to the conclusion that it doesn't > exit. I've looked at Derby, QED, Axion, blah blah and I'm not too > impressed by any of them.

Re: [sqlite] Convert SQLite to Java

Fred injects a big dose of common sense, and a principle which goes back almost a thousand years - Occam's Razor also known as K.I.S.S. How often do we see people laboring over a high level "simple" solution to a low level problem and in exasperation jump in and solve it in a very short time

Re: [sqlite] Convert SQLite to Java

On 10/25/05, Clay Dowling <[EMAIL PROTECTED]> wrote: > No matter if JVM versions do or don't have to be matched, using a native > DLL is clearly not a good solution for the original poster. It does > completely defeat the compile once run anywhere goal of Java. Presumably > if he was willing to

Re: [sqlite] C++ wrapper for SQLite C API

El Martes, 25 de Octubre de 2005 08:55, Martin Engelschalk escribió: > Hello Alfredo, > > i used a Wrapper from CodeProject by Rob Groves: > http://www.codeproject.com/database/CppSQLite.asp > I changed the code a bit (mostly deleted stuff i do not need), and i am > not sure if all features

Re: [sqlite] Convert SQLite to Java

I've been worn down by my quest for a comparable product to SQLite written in pure Java and have come to the conclusion that it doesn't exit. I've looked at Derby, QED, Axion, blah blah and I'm not too impressed by any of them. None of them are as good as SQLite and they all use multiple files

Re: [sqlite] .import null values

David Finlayson wrote: OK, I can deal with the Nulls that in SQL...now, what do you mean that "even numeric data" is imported as text? I loaded about 85k records into a database, all numeric, and didn't notice any problems using max() or stdev(). Should I be concerned about something?

Re: [sqlite] C++ wrapper for SQLite C API

Hello Alfredo, i used a Wrapper from CodeProject by Rob Groves: http://www.codeproject.com/database/CppSQLite.asp I changed the code a bit (mostly deleted stuff i do not need), and i am not sure if all features of SQLite are wrapped, but the code is easy to understand and can easily be

Re: [sqlite] sqlite on SunOS 5.6 (Solaris Sparc 6)

Inline. On Tue, 25 Oct 2005 [EMAIL PROTECTED] wrote: >Has anyone sucessfully built sqlite 3.2.7 on a SunOS 5.6 (Solaris >sparc 6) box? > >I had no problems with sqlite 2, but can't seem to get sqlite3 to >build. > >Using the ./configure ; make route, it dies with: > >./lemon -DSQLITE_OMIT_CURSOR

Re: [sqlite] How to determine if a column is autoincremented?

Thomas Briggs wrote: See http://www.sqlite.org/autoinc.html - INTEGER PRIMARY KEY will autoincrement only until you delete a row from the table. The above seems overstated. INTEGER PRIMARY KEYs with or without the AUTOINCREMENT keyword behave identically unless 1) the last row is deleted

[sqlite] C++ wrapper for SQLite C API

Hi: I am looking for a C++ wrapper that will support all features of SQLite. I am using sqlitewrapped-1.0 but does not work correctly when trying to read from a table and insert to another one within the same method. Also, it seems the author is not working on it any longer. I would

RE: [sqlite] getting only count of records

>Is this the right way to do or is there a better way ? In addition to the comments others have made, I would suggest you use: 'select count(*) from tx where VehicleID = ' + QuotedStr(VehicleID) 'count(*)' is a special case for the count function that returns "count of records". In terms of

Re: [sqlite] Receive error: database is full

R S <[EMAIL PROTECTED]> wrote: > with Return Value 13. > I checked my partition space and its usage is just 2% (Platform is Linux > using SQLite 3.2.2). > Also I am using Temp tables and periodically move data into my Main Table. I > wondered if my Temp Table is full because strace on my process

Re: [sqlite] Are DELETE TRIGGERS recursive or not?

Ralf Junker <[EMAIL PROTECTED]> wrote: > I wonder if a DELETE TRIGGER should trigger itself recursively Not at this time. Though work is underway to change this. We need recusive delete triggers in order to implement cascadinig deletes for referential integrity. -- D. Richard Hipp <[EMAIL

Re: [sqlite] sqlite tuning for db writes?

Guy Hindell wrote: The majority of SQL commands issued are INSERTs and DELETEs, and so it is these that I would like to optimise. Performance at the moment is respectable, although I would prefer to see DELETEs go a bit faster. In my application I use transaction for multiple inserts and

[sqlite] sqlite tuning for db writes?

Hi I'm relatively new to sqlite and this list but I have recently used it (v3.2.7) as an alternative to the db backend of an application which previously used SQLServer or Access as part of a proposed port to Unix/Linux. So far so good - the app works just as it did before with SQLServer.

Re: [sqlite] Are DELETE TRIGGERS recursive or not?

If the trigger were recursive, it would invoke itself forever, even if it's not doing anything because the where clause fails. Regards

RE: [sqlite] How to determine if a column is autoincremented?

See http://www.sqlite.org/autoinc.html - INTEGER PRIMARY KEY will autoincrement only until you delete a row from the table. -Tom > -Original Message- > From: Mario Gutierrez [mailto:[EMAIL PROTECTED] > Sent: Monday, October 24, 2005 11:05 PM > To: sqlite-users@sqlite.org >

Re: [sqlite] getting only count of records

Wilfried Mestdagh wrote: > > Hi Arjen, > > AM> No, they will probably not all do the same internally: the result > AM> may be the same, but the "virtual machine" that runs the SQL statements > AM> will very probably do different things - this depends on optimisation > AM> and so on. But for most

Re: [sqlite] getting only count of records

Wilfried Mestdagh a écrit : AM> No, they will probably not all do the same internally: the result AM> may be the same, but the "virtual machine" that runs the SQL statements AM> will very probably do different things - this depends on optimisation AM> and so on. But for most of us it is mainly

Re: [sqlite] .import null values

> "even numeric data" is imported as text Column types in Sqlite are not strictly enforced. If you import data that 'look' numeric from your text file into a column with a numeric type, the data will be stored in a numeric format; if a particular value cannot be converted to a numeric format, it

Re: [sqlite] Optimal page size

* Chris Schirlinger: > 4096 in Win32 machines and 1024 on nix ones (I think, I am no expert > on Unix style OS's) This depends on the file system. On Linux on x86, it's typically 4096 bytes. According to my performance measurements, switching from page size of 1024 bytes to 4096 bytes gives a

Re: [sqlite] sqlite on SunOS 5.6 (Solaris Sparc 6)

This looks like a problem that has come up several times before on the list. If I remember correctly, it's usually that the awk shipped by Sun doesn't support the extended syntax that Sqlite expects. Try installing gawk if it's not there already, and do whatever is required to use it in place of

Re: [sqlite] SQLite 3.2.5 and Mac OSX network folders

Steve Palmer <[EMAIL PROTECTED]> wrote: > I'm currently investigating a problem with my application, SQLite > 3.2.5 and a database located on a Mac OSX Server network share that > does not seem to repro with the SQLite 3.1.3 that ships with Mac OSX > 4.1. Specifically if I place a SQLite

Re: [sqlite] sqlite on SunOS 5.6 (Solaris Sparc 6)

[EMAIL PROTECTED] wrote: > > Has anyone sucessfully built sqlite 3.2.7 on a SunOS 5.6 (Solaris > sparc 6) box? > > I had no problems with sqlite 2, but can't seem to get sqlite3 to > build. > > Using the ./configure ; make route, it dies with: > > ./lemon -DSQLITE_OMIT_CURSOR parse.y

Re: [sqlite] getting only count of records

Hi Guillaume and Martin, Thanks for quick responce. Studying the count() function if I use the method of Guillaume I dont need the 'where' clause: 'select count(VehicleID) from tx' But I assume all 3 methods will do internal exact the same. Result is also 1 field with the count in it. ---

RE: [sqlite] Optimal page size

There doesn't appear to be any real documentation over what page size to use. I think it is more of a case of experimenting and determining which is best for your system/application. In the archive I found an article stating that for optimum performance on Win32 to match the page size with

Re: [sqlite] Convert SQLite to Java

* Randy Ray: > Java libraries linked with C (via JNI) generally have to run with the exact > same version of Java they were built with. Uh-oh, Sun advertises something else. I can understand that you must exactly match JVM versions for certified configurations, but this isn't true even if you

Re: [sqlite] Optimal page size

Hi Paolo, i met the same problem. I have experimented with the page size and found that the cluster size of the file system brought the best results. However, the gain in speed was not very big (5%) Martin Zibetti Paolo schrieb: I could not find a document explaining how to find the

[sqlite] Optimal page size

I could not find a document explaining how to find the optimal value for the "page size" parameter. Should I set the page size to match the allocation size (cluster size) of the file system ? Should I set it so that each page contains exactly a given number of records (i.e. no record is split

Re: [sqlite] getting only count of records

Wilfried Mestdagh a écrit : Hello, I want to have only the count of records for a particular 'where clause', need no fields. It works using null as argument: 'select null from tx where VehicleID = ' + QuotedStr(VehicleID) Is this the right way to do or is there a better way ? Hi Wilfried,

Re: [sqlite] getting only count of records

Hello Wilfried, your query will have as many result - Rows as meet your where-clause. A Better way will be select count(*) from tx where VehicleID = ' + QuotedStr(VehicleID) This will have one result row with the number of records in the first (and only) column. Martin Wilfried Mestdagh

[sqlite] getting only count of records

Hello, I want to have only the count of records for a particular 'where clause', need no fields. It works using null as argument: 'select null from tx where VehicleID = ' + QuotedStr(VehicleID) Is this the right way to do or is there a better way ? --- Mvg, Wilfried http://www.mestdagh.biz

[sqlite] fragmented tables

Hello, I read in FAQ that SQLite will re-use deleted record space. But what about fragmenting? I mean suppose a record of 1 kb is deleted and a while later a record of 2 kb is add, whill this record then append to the file, or will it fragment partly into the free space ? A question related: To

[sqlite] FYI: Fortran interface to SQLite

Hello, my (almost) first experience with SQLite was the sample code that Al Danial published to show how to use SQLite in a FORTRAN program. I was inspired by his work to develop a more comprehensive interface, this time in Fortran 90/95. I am pleased to say that this was fairly easy to do and

Re: [sqlite] .import null values

On 10/24/05, Dennis Cote <[EMAIL PROTECTED]> wrote: > The .import command in sqlite always imports data as text (even numeric > data into numeric columns). There is no way to import a NULL value. The > closest you can get is an empty string or some other sentinel value, > like the string NULL

[sqlite] sqlite on SunOS 5.6 (Solaris Sparc 6)

Has anyone sucessfully built sqlite 3.2.7 on a SunOS 5.6 (Solaris sparc 6) box? I had no problems with sqlite 2, but can't seem to get sqlite3 to build. Using the ./configure ; make route, it dies with: ./lemon -DSQLITE_OMIT_CURSOR parse.y cat parse.h ./src/vdbe.c | awk -f