[sqlite] data type problem

2007-03-19 Thread qinligeng
if you create a table use following statement (script generated from MS SQL Server 2000) CREATE TABLE [XTollData] ( [DutyID] [char] (32) NOT NULL , [CarNumber] [char] (10) NULL ); SQLite3_Column_decltype will treat DutyID as data type 'char' but not 'char(32)'

Re: [sqlite] a problem trying to invoke sqlite3 commands from a C application

2007-03-19 Thread Dan Kennedy
On Tue, 2007-03-20 at 02:44 +0200, Rafi Cohen wrote: > Hi, I'm calling system from a C application in order to invoke sqlite3 > database, then continue with commands: .separator "," and .import file > table, all in a single string as argument to system. > sqlite3 is indeed invoked with the correct

[sqlite] Anyone Having problems with SQLite 3.13 on WinMobile?

2007-03-19 Thread Chris Hodges
Hello: I recently upgraded to the latest version of SQLite 3.13 and ever since then I have been experiencing numerous SQLITE_MISUSE[21] error message. But when I run the same code under Win32 or the PocketPC Emulator the code works fine. Now previously I was using SQLite 3.08 or 3.09 and

Re: [sqlite] Question about speed of CASE WHEN

2007-03-19 Thread T
Hi RBS, I use SQLite as a data manipulator, not as a database. I get data from a server database, dump to SQLite, manipulate the data and finally dump to Excel. As this is reporting software speed is important, so I will go with the fastest method. OK, I have to ask. What do you then do

[sqlite] a problem trying to invoke sqlite3 commands from a C application

2007-03-19 Thread Rafi Cohen
Hi, I'm calling system from a C application in order to invoke sqlite3 database, then continue with commands: .separator "," and .import file table, all in a single string as argument to system. sqlite3 is indeed invoked with the correct database, but the problem is that the .import command is

[sqlite] Using sqlite-3.3.3 on linux in mod_perl environment

2007-03-19 Thread Thomas Kwan
Hi there, I want to see if there is anyone using sqlite-3.3.3 with perl-DBD-SQLite in a mod_perl environment where multiple threads will be present. The FAQ indicates the default sqlite3 does not have multi-threading option enabled. But I want to stick with the system library as much as

Re: [sqlite] Saving binary files

2007-03-19 Thread guenther
On Mon, 2007-03-19 at 01:46 +0200, Dimitris Servis wrote: > 2007/3/19, guenther <[EMAIL PROTECTED]>: > > On Sun, 2007-03-18 at 23:51 +0200, Dimitris Servis wrote: > > > in my wildest dreams... if you read carefully, *each* file is about > > > 100-200MB. I now end up wit ha collection of 100-200

Re: [sqlite] API enhancement

2007-03-19 Thread Ken
It should save some time. How much is questionable. Why would sqlite have to bind the Pointer bound variables? Isn't the strategy of binding to associate a variable with a statment? Why should I have to continually re-associate the bindings with a statement thats allready been prepared and

Re: [sqlite] API enhancement

2007-03-19 Thread drh
ken-33 <[EMAIL PROTECTED]> wrote: > Anyone thoughts? > I would rather not complicate the existing API unnecessarily by add bells and whistles that can be easily implemented using simple by wrappers. > > ken-33 wrote: > > > > Question for the list, > > > > I'd like to optimize my code,

Re: [sqlite] API enhancement

2007-03-19 Thread ken-33
Anyone thoughts? ken-33 wrote: > > Question for the list, > > I'd like to optimize my code, using the following pseudo code as an > example. > > === > int i = 0 ; > char str[20]; > > sqlite3_prepare_v2( "insert into t1 values (?,?)" ) >

Re: [sqlite] API enhancement

2007-03-19 Thread Scott Hess
I don't see how your modified version is any better than just putting the sqlite3_bind_int() inside the loop. You've superficially lifted some code out of the loop, but sqlite3_step() is going to have to go through and bind all of the "pointer bound" variables in your suggested API, so it won't

Re: [sqlite] Strange performance behavior

2007-03-19 Thread Dennis Cote
Hubertus wrote: Well now, if this isn't something! fist I wann thank you for your quick reply. Finally I have to wait for my result satisfying 0.6 sec. This is great. Now I can add lots more data. What I did: - I added a second index for the column "campId" and did the analyze trick (I had

Re: [sqlite] API enhancement

2007-03-19 Thread Ken
Anyone Ken <[EMAIL PROTECTED]> wrote: Question for the list, I'd like to optimize my code, using the following pseudo code as an example. === int i = 0 ; char str[20]; sqlite3_prepare_v2( "insert into t1 values (?,?)" ) sqlite3_bind_int ( i )

Re: [sqlite] Avoiding use of temporary files

2007-03-19 Thread Alberto Simões
> Would it also be acceptable (assuming you want the protection offered > by the temporary tables) to do ... "PRAGMA temp_store = MEMORY" on > the database? > The temp_store pragma only effects the placement of temporary tables. But the rollback journal is not a table. The temp_store pragma

Re: [sqlite] Avoiding use of temporary files

2007-03-19 Thread Siebe Warners
[EMAIL PROTECTED] wrote: Siebe Warners <[EMAIL PROTECTED]> wrote: Hi all, I'm using sqlite on an embedded system with flash EEPROM as the medium. Performance of the flash is not too good, so i'm trying to avoid file system access where possible without running risk of database corruption.

Re: [sqlite] Avoiding use of temporary files

2007-03-19 Thread drh
"Rich Rattanni" <[EMAIL PROTECTED]> wrote: > On 3/19/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > > Siebe Warners <[EMAIL PROTECTED]> wrote: > > > > > > When i perform a sequence of commands like: > > > BEGIN; > > > UPDATE t SET col="val1" WHERE key="key1"; > > > . > > > UPDATE t SET

Re: [sqlite] Avoiding use of temporary files

2007-03-19 Thread Rich Rattanni
On 3/19/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: Siebe Warners <[EMAIL PROTECTED]> wrote: > Hi all, > > I'm using sqlite on an embedded system with flash EEPROM as the medium. > Performance of the flash is not too good, so i'm trying to avoid file > system access where possible without

Re: [sqlite] Strange performance behavior

2007-03-19 Thread John Stanton
Hubertus wrote: Dear John, You might also look at using the Sqlite date format rather than seperate columns for year, month etc. That was what I considered first. The problem was, that other people are also supposed to use this datbase. Some of them use Fortran and they said that it's easier

Re: [sqlite] Strange performance behavior

2007-03-19 Thread Ken
Did you ever determine the cardinality of the campID field? I'm guessing its pretty good since your query is now .6 seconds. Lets say your cardinality was low, ie say less than .3 (arbitrary number). Then using the index to perform you data lookups would probably be slower than just reading

Re: [sqlite] Strange performance behavior

2007-03-19 Thread Hubertus
Dear John, > You might also look at using the Sqlite date format rather than seperate > columns for year, month etc. That was what I considered first. The problem was, that other people are also supposed to use this datbase. Some of them use Fortran and they said that it's easier to compile the

Re: [sqlite] Strange performance behavior

2007-03-19 Thread Hubertus
Well now, if this isn't something! fist I wann thank you for your quick reply. Finally I have to wait for my result satisfying 0.6 sec. This is great. Now I can add lots more data. What I did: - I added a second index for the column "campId" and did the analyze trick (I had this column in the

Re: [sqlite] Strange performance behavior

2007-03-19 Thread P Kishor
On 3/19/07, Hubertus <[EMAIL PROTECTED]> wrote: Dear list, sorry to just come up with another performance question. I build a yet small database with one table. It has about 650.000 rows, 75 columns and has at the moment about 650 Mb. It runs on a Intel Pentium M with 2 GHz. The Laptop runs Suse

Re: [sqlite] Strange performance behavior

2007-03-19 Thread John Stanton
First, you have your double and single quotes mixed up. SQL uses single quotes for literals. Second, you are performing row scans and not using any of your indices. You will do better if you have an index on the column you specify in your search. You might also look at using the Sqlite

Re: [sqlite] Avoiding use of temporary files

2007-03-19 Thread drh
Siebe Warners <[EMAIL PROTECTED]> wrote: > Hi all, > > I'm using sqlite on an embedded system with flash EEPROM as the medium. > Performance of the flash is not too good, so i'm trying to avoid file > system access where possible without running risk of database corruption. > So the database

Re: [sqlite] Avoiding use of temporary files

2007-03-19 Thread Siebe Warners
I'll give 3.3.13 a try but it will be for debugging only for now. The project using the database is in the stage of avoiding all risk, so i'm not sure i will be able to convince the people to swap databases at this point. So were there any updates wrt transactions and temporary files?

Re: [sqlite] Avoiding use of temporary files

2007-03-19 Thread drh
Siebe Warners <[EMAIL PROTECTED]> wrote: > I'm using sqlite 3.2.7. Have you tried 3.3.13? -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED]

[sqlite] Avoiding use of temporary files

2007-03-19 Thread Siebe Warners
Hi all, I'm using sqlite on an embedded system with flash EEPROM as the medium. Performance of the flash is not too good, so i'm trying to avoid file system access where possible without running risk of database corruption. So the database file and the journal files need to be written to the

Re: [sqlite] Strange performance behavior

2007-03-19 Thread Dennis Cote
Hubertus wrote: This is the schema: CREATE TABLE 'data'('nr' INTEGER PRIMARY KEY, 'year' INTEGER, 'month' INTEGER, 'day' INTEGER, 'sec' REAL, 'campId' TEXT, 'flightNr' INTEGER, '1' REAL, ... '71' REAL ); You should use double quotes around column and table names. Single

Re: [sqlite] Strange performance behavior

2007-03-19 Thread Christian Smith
Hubertus uttered: Dear list, sorry to just come up with another performance question. I build a yet small database with one table. It has about 650.000 rows, 75 columns and has at the moment about 650 Mb. It runs on a Intel Pentium M with 2 GHz. The Laptop runs Suse 10.2 and does basicly

Re: [sqlite] Strange performance behavior

2007-03-19 Thread Ken
Looks like it is going to do a full scan of the entire database to complete that querry based upon your where clause. Are you always accessing the data by campID? What is the cardinality of campId data? Depending upon that it might be worth while putting and index on CampID. Hubertus

[sqlite] Strange performance behavior

2007-03-19 Thread Hubertus
Dear list, sorry to just come up with another performance question. I build a yet small database with one table. It has about 650.000 rows, 75 columns and has at the moment about 650 Mb. It runs on a Intel Pentium M with 2 GHz. The Laptop runs Suse 10.2 and does basicly nothing but this

[sqlite] Re: 2 questions concerning select statement

2007-03-19 Thread Igor Tandetnik
Rafi Cohen <[EMAIL PROTECTED]> wrote: 1. When I prepare a select statement for later execution, may I use a question mark instead of a table and later "bind" different table names No. You can only use a parameter where an expression would be valid. 2. In "order by" clause may I use a column

[sqlite] 2 questions concerning select statement

2007-03-19 Thread Rafi Cohen
Hi, 1. When I prepare a select statement for later execution, may I use a question mark instead of a table and later "bind" different table names with the same cxonstruct according rto some condition? for example, sqlite3_prepare_v2(db, "select * from ? where.",...); 2. In "order by" clause

Re: [sqlite] Saving binary files

2007-03-19 Thread Dimitris Servis
Hello John, this is extremely helpful. Thanks a lot!!! Dimitris