[sqlite] Importing a CSV file
Dear list, I am willing to try sqlite, but I am stuck at the very beginning : I can not import my data. The reason seems to be that when I create a table with 23 columns, it treats it as if it had 24 : I created a table with the following instruction : CREATE TABLE All_Chr_CxCb_atleast1 ( TCIDTEXT, STRAND TEXT, CHROMOSOME TEXT, START_POS INTEGER, STOP_POSINTEGER, REPRESENT_POS INTEGER, TKIDINTEGER, REPRESENT TEXT, SYMBOL1 TEXT, SYMBOL2 TEXT, LOCUSLINK TEXT, GO, BLOB, CAA INTEGER, CAC INTEGER, CAE INTEGER, CAG INTEGER, CAI INTEGER, CAB INTEGER, CAD INTEGER, CAF INTEGER, CAH INTEGER, CAJ INTEGER, BC INTEGER ) ; Then, I tried to import the following table : [EMAIL PROTECTED] cgi-bin]$ head -n3 All_Chr_CxCb_atleast1 TCIDSTRAND CHROMOSOME START_POS STOP_POS REPRESENT_POS TKIDREPRESENT SYMBOL1 SYMBOL2LOCUSLINK GO CAA CAC CAE CAG CAI CAB CAD CAF CAH CAJ BC T19R039BA4C4R chr19 605318446053193760531908 175877 REFSEQ|XM_484819BC055757 BC029127214368 0005622,0003676,0006355 1 2 0 1 0 1 2 11 0 8 T19R039B6D50R chr19 605176936051771260517712 175877 REFSEQ|XM_484819BC055757 BC029127214368 0005622,0003676,0006355 0 0 0 0 0 0 0 00 0 1 and here is the result : sqlite> .separator '' sqlite> .import All_Chr_CxCb_atleast1 All_Chr_CxCb_atleast1 All_Chr_CxCb_atleast1 line 1: expected 24 columns of data but found 23 I do not understand why it expects 24 columns, as my table obviously has only 23... By the way, is there a way to automagically import a csv file using the first line as the columns names (I would not mind if everything were seen as text) ? -- Charles
Re: [sqlite] Re: sqlite performance variationin linux and windows
On 25 Feb 2005 at 10:33, Neelamegam Appadurai wrote: > But still, For the same application on windows, performance of sqlite > is slower compared to file system read or write which we were using > earlier. Hurm I cannot think of anything obvious. I know from experience that SQLite is faster than a multi-file fake DB, faster than pretty much anything on the PC frankly How much data do you have in your DB? Over 6 million records or about 200 meg DB I noticed a slight reduction in speed but nothing to be worried about How many DB's do you have? Opening and closeing a SQLite DB isn't slow, but if you have a LOT of them it may be an issue What exactly are you doing that's slow? Runing a select statement on 2 million rows, returning 2,000 records should be under a 1/4 of a second (not talking test data or systems here, this is what we get in our program including display of said data) Are you doing Updates? We get about 8000 records a second Insert/Edit/Delete on a 2 million row DB. This is with transactions which are VITAL (But I'd think vital for Linux too) I don't really know much about Linux unfortunatly so can't really think of any differences that would account for a speed difference
Re: [sqlite] Re: sqlite performance variationin linux and windows
Hi, Thanks for the quick response and the interest you're showing, I am testing the performance of linux and windows using a. same testing data for both. b. db schema is common for both. c. though the test is conducted on two different machines but the machine configurations are similar. Only consideration is at very few places jin the application, code is platform dependent ie the api calls may differ based on the type of OS, but this is also seldom used in our application. But still, For the same application on windows, performance of sqlite is slower compared to file system read or write which we were using earlier. Thanks once again for the reply, appadurai On Fri, 25 Feb 2005 15:28:23 +1100, Chris Schirlinger <[EMAIL PROTECTED]> wrote: > On 25 Feb 2005 at 9:38, Neelamegam Appadurai wrote: > > > Could anyone please give me reason for variation in performance > > between linux and windows. > > How are you testing this performance? Do you have a program written > in windows and another written for linux? If so, the code/mechanism > of DB access may be the issue > > Is it the same phisical DB? Same schema? Same machine specs? Same > amount of data? > > All those things could contribute. > >
Re: [sqlite] Re: sqlite performance variationin linux and windows
On 25 Feb 2005 at 9:38, Neelamegam Appadurai wrote: > Could anyone please give me reason for variation in performance > between linux and windows. How are you testing this performance? Do you have a program written in windows and another written for linux? If so, the code/mechanism of DB access may be the issue Is it the same phisical DB? Same schema? Same machine specs? Same amount of data? All those things could contribute.
[sqlite] Re: sqlite performance variationin linux and windows
Hi all, Thanks for your replies, I had changed the page_size to 4096 and followed the doc "performance tuning for windows" and made changes as per the doc. Though there was increase in performance, but the performance in windows when compared to linux is much low. Could anyone please give me reason for variation in performance between linux and windows. Once again thanks for the response. appadurai On Thu, 24 Feb 2005 19:08:26 +0530, Neelamegam Appadurai <[EMAIL PROTECTED]> wrote: > hi all, > I was using file in my application to store and read datas, Now i want > my application to use sqlite DB. > I compiled and installed sqlite db,version 3.0.8, in my linux system > as well as on a windows replacing the fileoperations . > The performance of sqlite against filesystem on linux machine was not > of much difference. > But in windows, the performance of sqlite against filesystem is > reduced by half, time taken to complete a task is doubled. > Can anyone please help me how to increase the performance on windows > enviroment and why the difference in performance > between linux and windows. > Thanks in advance > appadurai >
Re: [sqlite] sqlite performance variationin linux and windows
On 24 Feb 2005 at 19:08, Neelamegam Appadurai wrote: > Can anyone please help me how to increase the performance on windows > enviroment Have a look at this Wiki http://www.sqlite.org/cvstrac/wiki?p=PerformanceTuningWindows Some basic things to be aware of in there with regards to Speed and Windows
RE: [sqlite] BLOB problem
> Actually I can read 10 MByte from an Oracle DB (on another machine) > via ODBC in less than 2 seconds... I don't know if that's good or bad > though, you tell me ;) Pretty good! That works out to 5 megabyte per second. That's gotta be gigabit ethernet. I don't think the 10 meg stuff will sustain that rate. Big iron for the db box too. > > Anyway, the key idea I have for using SQLite is in the case I cannot > reach the main (Oracle) database. Then I want to use SQLite as a > temporary database, still communicating the same exact way as if I > had the "real" connection. Makes sense? Sounds nice, must be a pretty critical system for all that redundancy and nice hardware. __ Do you Yahoo!? Yahoo! Mail - now with 250MB free storage. Learn more. http://info.mail.yahoo.com/mail_250
RE: [sqlite] BLOB problem
Actually I can read 10 MByte from an Oracle DB (on another machine) via ODBC in less than 2 seconds... I don't know if that's good or bad though, you tell me ;) Anyway, the key idea I have for using SQLite is in the case I cannot reach the main (Oracle) database. Then I want to use SQLite as a temporary database, still communicating the same exact way as if I had the "real" connection. Makes sense? /Rob -Original Message- From: Jay [mailto:[EMAIL PROTECTED] Sent: Thu 2/24/2005 8:37 PM To: Bielik, Robert Cc: Subject: RE: [sqlite] BLOB problem Won't reading multi-megabyte objects through odbc be *horribly* slow? If you're not searching the contents of the blob it's probably better stored as files with a path in the database instead. --- "Bielik, Robert" <[EMAIL PROTECTED]> wrote: > Thanks Richard, > > Ah, I see. Well I did use those functions in the ODBC driver to make > writing a blob work. However, my > current problem is reading the blob. As long as there is no null > bytes it works. But I'm very unsure of the > calling sequence. Ok, say I got a table: > > create table T_GENERICDATA (ID INTEGER, DATA BLOB); > > and I want to get to a row by select: > > select ID,DATA from T_GENERICDATA where ID=(?) > > Now the ? will be replaced by %Q by the ODBC driver > (sqlite3_vmprintf), so the resulting SQL string > put into sqlite3_prepare will be 'select ID, DATA from T_GENERICDATA > where ID='1' ' (if ID param was 1). > Ok so far so good. Then what? Should I call sqlite3_step or > sqlite3_column_blob... ? > > Eh.. pretty confused... > > TIA > /Rob > > -Original Message- > From: D. Richard Hipp [mailto:[EMAIL PROTECTED] > Sent: Thu 2/24/2005 3:19 PM > To: sqlite-users@sqlite.org > Cc: > Subject: Re: [sqlite] BLOB problem > > > > On Thu, 2005-02-24 at 15:05 +0100, Bielik, Robert wrote: > > Hmmm... what is this? When using sqlite3_get_table it goes through > sqlite3_exec which extracts > > values from columns with sqlite3_column_text (!!!). What about > blobs? A blob can actually contain > > a number of null bytes! What is the recommended procedure to > extract a blob if sqlite3_exec doesn't cut it?? > > > > sqlite3_get_table() and sqlite3_exec() are legacy APIs in place to > support > older programs. They are not recommended for new code. Use > instead: > > sqlite3_prepare() > sqlite3_step() > sqlite3_finalize() > > > > > = - "Lord Tarlington gazed upon the crazed Egyptian hieroglyphics on the walls of the ancient tomb of the petrified pharaoh, he vowed there would be no curse on him like on that other Lord, unless you count his marriage to Lady Tarlington who, when the lost treasure was found, will be dumped faster than that basket in the bulrushes." Melissa Rhodes - The Castles of Dereth Calendar: a tour of the art and architecture of Asheron's Call http://www.lulu.com/content/77264 __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: [sqlite] Can SELECT queries lock the database for writing?
I'm not sure I understand exactly what's in your table; there are no integrity constraints, so it's hard to infer anything. I added a row to cover a case you didn't mention; maybe you think it can never happen, but there's nothing in your schema that prevents it. select * from history order by license, date desc, controlcode desc; ==> License ControlCode Date -- --- -- Lic1CC4 2004-01-03 Lic1CC2 2004-01-03 Lic1CC1 2004-01-01 Lic2CC3 2004-02-02 Lic2CC3 2004-02-01 > Any hints for making it at least as fast as Access? You can't just add indices on every column. I think this does what you want, and (to me) it's more readable: select distinct license, controlcode, date from (select h.license lic, h.controlcode cc, h.date dt from (select license, max(date) d from history group by license) lmax, history h where h.license = lmax.license and h.date = lmax.d) lcmax, history where license = lcmax.lic and date < lcmax.dt and (controlcode <> lcmax.cc or controlcode is null) ; ==> License ControlCode Date -- --- -- Lic1CC1 2004-01-01 This doesn't tell you that Lic1 was activated twice on Jan 3; for that you need a separate query: select license, controlcode, date from (select h.license lic, h.date dt from (select license, max(date) d from history group by license) lmax, history h where h.license = lmax.license and h.date = lmax.d group by lic, date having count(*) > 1) lmax2, history where license = lmax2.lic and date = lmax2.dt ; ==> License ControlCode Date -- --- -- Lic1CC2 2004-01-03 Lic1CC4 2004-01-03 There's a good explanation of indices in the slide show from the PHP conference (starting around slide 50); the link is on the main SQLite web page. I think I'd start with one index on (license, date); delete the others. > While the query executes, the whole database is locked! Yes, you really don't want somebody changing the database while you're trying to read it. If you want to see the reason for this, do a Google search on 'dirty read' (SQLite does not allow dirty reads). This point is stated pretty clearly in Section 3 of www.sqlite.org/lockingv3.html. Regards
RE: [sqlite] BLOB problem
Thanks Richard, Ah, I see. Well I did use those functions in the ODBC driver to make writing a blob work. However, my current problem is reading the blob. As long as there is no null bytes it works. But I'm very unsure of the calling sequence. Ok, say I got a table: create table T_GENERICDATA (ID INTEGER, DATA BLOB); and I want to get to a row by select: select ID,DATA from T_GENERICDATA where ID=(?) Now the ? will be replaced by %Q by the ODBC driver (sqlite3_vmprintf), so the resulting SQL string put into sqlite3_prepare will be 'select ID, DATA from T_GENERICDATA where ID='1' ' (if ID param was 1). Ok so far so good. Then what? Should I call sqlite3_step or sqlite3_column_blob... ? Eh.. pretty confused... TIA /Rob -Original Message- From: D. Richard Hipp [mailto:[EMAIL PROTECTED] Sent: Thu 2/24/2005 3:19 PM To: sqlite-users@sqlite.org Cc: Subject: Re: [sqlite] BLOB problem On Thu, 2005-02-24 at 15:05 +0100, Bielik, Robert wrote: > Hmmm... what is this? When using sqlite3_get_table it goes through sqlite3_exec which extracts > values from columns with sqlite3_column_text (!!!). What about blobs? A blob can actually contain > a number of null bytes! What is the recommended procedure to extract a blob if sqlite3_exec doesn't cut it?? > sqlite3_get_table() and sqlite3_exec() are legacy APIs in place to support older programs. They are not recommended for new code. Use instead: sqlite3_prepare() sqlite3_step() sqlite3_finalize()
RE: [sqlite] Write issues on some computers?
What if the database was not closed properly on exit, would that be enough to set the database to read-only? Luc Vandal Edovia Technologies Inc. [EMAIL PROTECTED] www.edovia.com -Original Message- From: Clay Dowling [mailto:[EMAIL PROTECTED] Sent: February 24, 2005 11:43 AM To: sqlite-users@sqlite.org Subject: RE: [sqlite] Write issues on some computers? Luc Vandal said: > Today a user told me that everything was fine yesterday and since today he > can't write to the database. What could cause that? The software closes > the > database on exit. I guess that the db is busy but what could cause the db > to > be busy for a large amount of time? You might want to ship him an updated program that reports the actual error. Making assumptions is dangerous, especially since there are multiple viable options for why he can't get in. Clay -- Lazarus Notes from Lazarus Internet Development http://www.lazarusid.com/notes/ Articles, Reviews and Commentary on web development ---
[sqlite] Questions
Hi *, Where I can find the list of the internal functions? for example: datetime() strftime() Where I find the documentation on as I can make in agreement INSERT/UPDATE the type of column (BOOLEAN/DATE) tks -- Marco Antonio J. Victor Fone: 11 6977-5406 Fax: 11 6973-9772 www.tactor.com.br
Re: [sqlite] tricky date time problem
Dennis Might be someting to do with version 2.8.15. I have tried as is and I get an error "sql error near 'Select': syntax error" Lloyd - Original Message - From: "Dennis Cote" <[EMAIL PROTECTED]> To: Sent: Thursday, February 24, 2005 1:31 AM Subject: Re: [sqlite] tricky date time problem On Wed, 23 Feb 2005 23:12:41 -, Lloyd Thomas <[EMAIL PROTECTED]> wrote: Dennis, Thanks for you help so far. I think it is easier for PHP to select the MAX event. The problem I now have is if there is no records for an hour, PHP will through up an error because MAX must have at least one record to process, even if it is 0. Lloyd, You can force a zero record if there no events using a union like this: select minutes.i as minute, count(*) as events from (select (event_time / 60) % 60 as begin_minute, ((event_time + duration) / 60) % 60 as end_minute from event_data) outer join integers60 as minutes where case when begin_minute <= end_minute then begin_minute <= minutes.i and minutes.i <= end_minute else begin_minute <= minutes.i or minutes.i <= end_minute end group by minute union select 0, 0 where not exists (select * from event_data) This will give a single row with a count of zero if there are no events, or the usual set of 60 rows if there are one or more events. Dennis Cote
RE: [sqlite] Write issues on some computers?
Luc Vandal said: > Today a user told me that everything was fine yesterday and since today he > can't write to the database. What could cause that? The software closes > the > database on exit. I guess that the db is busy but what could cause the db > to > be busy for a large amount of time? You might want to ship him an updated program that reports the actual error. Making assumptions is dangerous, especially since there are multiple viable options for why he can't get in. Clay -- Lazarus Notes from Lazarus Internet Development http://www.lazarusid.com/notes/ Articles, Reviews and Commentary on web development
Re: [sqlite] more syntax errors ?
Richard Nagle said: > sqlite> Create Table Company ( >...> Company Name Character (50) NOT NULL, >...> Contact Name Character (35), >...> Address1 Character (30), >...> Address2 Character (30), >...> City Character (30), >...> State Character (2), >...> Zip Character (10), >...> Phone1Character (13), >...> Phone2Character (13), >...> Fax Character (13) ) ; > SQL error: near ".": syntax error > sqlite> > > It would appear, the sqlite does not follow the sql rules of syntax ? > or is there something I'm doing wrong? It would appear you're into pain. Spaces in column names causes trouble on all fronts. Try Company_Name and Contact_Name and you'll be a lot happier. Or just Company and Contact. Spaces in table names require some variety of quoting or escaping, or they'll be interpretted as separate strings. Clay Dowling -- Lazarus Notes from Lazarus Internet Development http://www.lazarusid.com/notes/ Articles, Reviews and Commentary on web development
RE: [sqlite] Write issues on some computers?
Today a user told me that everything was fine yesterday and since today he can't write to the database. What could cause that? The software closes the database on exit. I guess that the db is busy but what could cause the db to be busy for a large amount of time? Is there a way to "reset" the db? Luc Vandal Edovia Technologies Inc. [EMAIL PROTECTED] www.edovia.com -Original Message- From: Clay Dowling [mailto:[EMAIL PROTECTED] Sent: February 23, 2005 1:22 PM To: sqlite-users@sqlite.org Subject: RE: [sqlite] Write issues on some computers? Luc Vandal said: > The database is stored in the [User]\Local Settings\Application Data\ > folder > for the current user. In that case it's worth looking at the folder and the file and making sure that both have write permission for the user in question. This isn't terribly easy to do if your users are remote, but if you have a copy in house that is misbehaving you should be able to get at it. Just remember to clear the Simple Sharing option so that you can see the full permissions and ACL for files and folders. Try installing it on a machine in house as an administrator and then running it as an unprivileged or restricted user. It still sounds like some of the deployment issues that I've run into. They're no fun at all, especially since you get to look foolish in front of your customers. Clay -- Lazarus Notes from Lazarus Internet Development http://www.lazarusid.com/notes/ Articles, Reviews and Commentary on web development ---
Re: [sqlite] more syntax errors ?
On Thu, 2005-02-24 at 10:40 -0500, Richard Nagle wrote: > It would appear, the sqlite does not follow the sql rules of syntax ? > or is there something I'm doing wrong? > SQLite follows standard syntax rules. I think you have a typo. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] more syntax errors ?
Richard Nagle wrote: fastmac:/applications/sqlite rn$ ./sqlite contacts SQLite version 2.8.13 Enter ".help" for instructions sqlite> Create Table Company ( ...> Company Name Character (50) NOT NULL, ...> Contact Name Character (35), ...> Address1 Character (30), ...> Address2 Character (30), ...> City Character (30), ...> State Character (2), ...> Zip Character (10), ...> Phone1Character (13), ...> Phone2Character (13), ...> Fax Character (13) ) ; SQL error: near ".": syntax error sqlite> It would appear, the sqlite does not follow the sql rules of syntax ? or is there something I'm doing wrong? TKS - Rick Richard, Your statement doesn't generate the same syntax error in newer versions of SQLite, so you should probably upgrade. However it doesn't do what you want either. You need to quote the column names that contain spaces. The table_info pragma will show you the column names of a table. As you can see, your Company Name and Contact Name columns are not being created as you expect. SQLite version 3.1.1beta Enter ".help" for instructions sqlite> Create Table Company ( ...> Company Name Character (50) NOT NULL, ...> Contact Name Character (35), ...> Address1 Character (30), ...> Address2 Character (30), ...> City Character (30), ...> State Character (2), ...> Zip Character (10), ...> Phone1Character (13), ...> Phone2Character (13), ...> Fax Character (13) ) ; sqlite> pragma table_info('Company'); cid nametype notnull dflt_value pk -- -- - -- -- -- 0 Company NameCharacter(50) 99 0 1 Contact NameCharacter(35) 0 0 2 Address1Character(30) 0 0 3 Address2Character(30) 0 0 4 CityCharacter(30) 0 0 5 State Character(2) 0 0 6 Zip Character(10) 0 0 7 Phone1 Character(13) 0 0 8 Phone2 Character(13) 0 0 9 Fax Character(13) 0 0 sqlite> The correct way to define these columns is with double quotes as shown below. sqlite> Create Table Company ( ...> "Company Name" Character (50) NOT NULL, ...> "Contact Name" Character (35), ...> Address1 Character (30), ...> Address2 Character (30), ...> City Character (30), ...> State Character (2), ...> Zip Character (10), ...> Phone1Character (13), ...> Phone2Character (13), ...> Fax Character (13) ) ; sqlite> pragma table_info('Company'); cid name type notnull dflt_value pk -- - -- -- -- 0 Company Name Character(50) 99 0 1 Contact Name Character(35) 0 0 2 Address1 Character(30) 0 0 3 Address2 Character(30) 0 0 4 City Character(30) 0 0 5 State Character(2) 0 0 6 Zip Character(10) 0 0 7 Phone1Character(13) 0 0 8 Phone2Character(13) 0 0 9 Fax Character(13) 0 0 sqlite> HTH Dennis Cote
Re: [sqlite] more syntax errors ?
Are there spaces in [Company Name] and [Contact Name] field names? Regards, Witold - Original Message - From: "Richard Nagle" <[EMAIL PROTECTED]> To: Sent: Thursday, February 24, 2005 4:40 PM Subject: [sqlite] more syntax errors ? fastmac:/applications/sqlite rn$ ./sqlite contacts SQLite version 2.8.13 Enter ".help" for instructions sqlite> Create Table Company ( ...> Company Name Character (50) NOT NULL, ...> Contact Name Character (35), ...> Address1 Character (30), ...> Address2 Character (30), ...> City Character (30), ...> State Character (2), ...> Zip Character (10), ...> Phone1Character (13), ...> Phone2Character (13), ...> Fax Character (13) ) ; SQL error: near ".": syntax error sqlite> It would appear, the sqlite does not follow the sql rules of syntax ? or is there something I'm doing wrong? TKS - Rick
Re: [sqlite] tricky date time problem
Dennis, syntax error somewhere. 'group by minute union select 0, 0 where not exists (select * from event_data)' Lloyd - Original Message - From: "Dennis Cote" <[EMAIL PROTECTED]> To: Sent: Thursday, February 24, 2005 1:31 AM Subject: Re: [sqlite] tricky date time problem On Wed, 23 Feb 2005 23:12:41 -, Lloyd Thomas <[EMAIL PROTECTED]> wrote: Dennis, Thanks for you help so far. I think it is easier for PHP to select the MAX event. The problem I now have is if there is no records for an hour, PHP will through up an error because MAX must have at least one record to process, even if it is 0. Lloyd, You can force a zero record if there no events using a union like this: select minutes.i as minute, count(*) as events from (select (event_time / 60) % 60 as begin_minute, ((event_time + duration) / 60) % 60 as end_minute from event_data) outer join integers60 as minutes where case when begin_minute <= end_minute then begin_minute <= minutes.i and minutes.i <= end_minute else begin_minute <= minutes.i or minutes.i <= end_minute end group by minute union select 0, 0 where not exists (select * from event_data) This will give a single row with a count of zero if there are no events, or the usual set of 60 rows if there are one or more events. Dennis Cote
[sqlite] more syntax errors ?
fastmac:/applications/sqlite rn$ ./sqlite contacts SQLite version 2.8.13 Enter ".help" for instructions sqlite> Create Table Company ( ...> Company Name Character (50) NOT NULL, ...> Contact Name Character (35), ...> Address1 Character (30), ...> Address2 Character (30), ...> City Character (30), ...> State Character (2), ...> Zip Character (10), ...> Phone1Character (13), ...> Phone2Character (13), ...> Fax Character (13) ) ; SQL error: near ".": syntax error sqlite> It would appear, the sqlite does not follow the sql rules of syntax ? or is there something I'm doing wrong? TKS - Rick
Re: [sqlite] Syntax error ?
Richard Nagle wrote: So you must have the PATH set first?, then start the sqlite with a syntax: $sqlite contacts ( this would make a new database called contacts? .database 0main 1temp 2contacts ? Why don't you just try. You can also do $sqlite /path/to/db/dbname HTH Ulrik P. -- Ulrik Petersen, MA, B.Sc. University of Aalborg, Denmark Homepage: http://ulrikp.org
Re: [sqlite] Syntax error ?
So you must have the PATH set first?, then start the sqlite with a syntax: $sqlite contacts ( this would make a new database called contacts? .database 0 main 1 temp 2 contacts ? Richard Ulrik Petersen <[EMAIL PROTECTED]> wrote: R>ichard Nagle wrote: Sorry, let me get this straight: WHAT syntax command do I use ? sqlite> Make new database ? sqlite>contacts; The problem there is no Contacts database created ? I would have to make a database first, before creating tables? please explain. Y>ou don't make the database explicitly. You give the name of the database on the commandline, before you enter the sqlite command shell: $ pwd />projects/test $> sqlite contacts S>QLite version 2.8.15 E>nter ".help" for instructions s>qlite> .database s>eq name file -
Re: [sqlite] Syntax error ?
Richard Nagle wrote: Sorry, let me get this straight: WHAT syntax command do I use ? sqlite> Make new database ? sqlite>contacts; The problem there is no Contacts database created ? I would have to make a database first, before creating tables? please explain. You don't make the database explicitly. You give the name of the database on the commandline, before you enter the sqlite command shell: $ pwd /projects/test $ sqlite contacts SQLite version 2.8.15 Enter ".help" for instructions sqlite> .database seq name file --- --- -- 0main /projects/test/contacts 1temp /var/tmp/sqlite_ZFSmuMGwKY4qVLY This is the same in SQLite 3. HTH Ulrik P. -- Ulrik Petersen, MA, B.Sc. University of Aalborg, Denmark
Re: [sqlite] BLOB problem
I use the api sqlite3_bind_blob. Albert
Re: [sqlite] Syntax error ?
Sorry, let me get this straight: WHAT syntax command do I use ? sqlite> Make new database ? sqlite>contacts; The problem there is no Contacts database created ? I would have to make a database first, before creating tables? please explain. Richard Please not using any C or C++ programing ( using sqlite as stand alone app ) Ulrik Petersen <[EMAIL PROTECTED]> wrote: R>ichard Nagle wrote: Last login: Thu Feb 24 00:08:34 on ttyp1 Welcome to Darwin! fastmac:~ rn$ /Applications/SQLite/sqlite; exit SQLite version 2.8.13 Enter ".help" for instructions sqlite> .database 0 main 1 temp sqlite> create database contacts; # This is standard sql ? SQL error: near "database": syntax error sqlite> So: at the command line: sqlite> what is the correct syntax command to create a NEW database, called Contacts??? T>here is no syntax command to do that. With the sqlite shell, you just give the name of the database on the command line. From within C, you explicitly open a connection to the database. There is a command to attach a database (which is SQLite-specific). However, from the docs it seems that this is: a) Not what you want, and b) Only for attaching preexisting databases. http://www.sqlite.org/lang_attach.html H>TH Ulrik Petersen
Re: [sqlite] BLOB problem
On Thu, 2005-02-24 at 15:05 +0100, Bielik, Robert wrote: > Hmmm... what is this? When using sqlite3_get_table it goes through > sqlite3_exec which extracts > values from columns with sqlite3_column_text (!!!). What about blobs? A blob > can actually contain > a number of null bytes! What is the recommended procedure to extract a blob > if sqlite3_exec doesn't cut it?? > sqlite3_get_table() and sqlite3_exec() are legacy APIs in place to support older programs. They are not recommended for new code. Use instead: sqlite3_prepare() sqlite3_step() sqlite3_finalize()
[sqlite] BLOB problem
Hmmm... what is this? When using sqlite3_get_table it goes through sqlite3_exec which extracts values from columns with sqlite3_column_text (!!!). What about blobs? A blob can actually contain a number of null bytes! What is the recommended procedure to extract a blob if sqlite3_exec doesn't cut it?? TIA /R > -Original Message- > From: Bielik, Robert > Sent: Thursday, February 24, 2005 09:25 > To: sqlite-users@sqlite.org > Subject: [sqlite] ODBC problem > > > Hi, > > I'm using the SQLite 3 ODBC driver at > http://www.ch-werner.de/sqliteodbc/ and DTL > (http://dtemplatelib.sourceforge.net/) and > all works fine until I try to store a BLOB. I've debugged it > so far that when the SQL C type is SQL_C_BINARY, no memory > is allocated in SQLPutData and of course subsequently it will fail. > > One of the problems is that when resolving parameters in the query: > INSERT INTO T_GENERICDATA (ID, DATA) VALUES((?), (?)) > > both parameters are resolved as strings (null terminated). > Now, doesn't SQLite have databinding functions that should be > able to work > binary BLOBs (that could have arbitrary length of zeros) ? > And try to use that instead of the ODBC driver's parameter binding?? > Specifically I don't want the ODBC driver to alloc its own > memory, since suppose the BLOB is of size 10 megabytes, then > the BLOB itself > is 10 MB, then the ODBC will alloc +10 MB, and THEN the query > is executed towards SQLite. I'd like zero-copy operation on BLOBs, but > I don't know enough of the core SQLite3 API to know how to > bind the BLOB to the SQL query. > > Help? > > TIA > /Rob >
Re: [sqlite] sqlite performance variationin linux and windows
Hi, Neelamegam Appadurai wrote: Can anyone please help me how to increase the performance on windows enviroment One thing you can do is to increase the page size from 1024 to 4096. That seems to match either Windows' swapfile-pagesize or the default page size of NTFS (there is disagreement over which it is that causes the speed increase). This has given a speed increase for me. Not a factor 2, mind you, but still a speed increase. Also, you might try increasing the SQLite page cache size on Windows. HTH Ulrik Petersen -- Ulrik Petersen, MA, B.Sc. University of Aalborg, Denmark
[sqlite] sqlite performance variationin linux and windows
hi all, I was using file in my application to store and read datas, Now i want my application to use sqlite DB. I compiled and installed sqlite db,version 3.0.8, in my linux system as well as on a windows replacing the fileoperations . The performance of sqlite against filesystem on linux machine was not of much difference. But in windows, the performance of sqlite against filesystem is reduced by half, time taken to complete a task is doubled. Can anyone please help me how to increase the performance on windows enviroment and why the difference in performance between linux and windows. Thanks in advance appadurai
[sqlite] Functions
Hi *, Where I can find the list of the internal functions? for example: datetime() strftime() tks -- Marco Antonio J. Victor Fone: 11 6977-5406 Fax: 11 6973-9772 www.tactor.com.br
[sqlite] ODBC problem
Hi, I'm using the SQLite 3 ODBC driver at http://www.ch-werner.de/sqliteodbc/ and DTL (http://dtemplatelib.sourceforge.net/) and all works fine until I try to store a BLOB. I've debugged it so far that when the SQL C type is SQL_C_BINARY, no memory is allocated in SQLPutData and of course subsequently it will fail. One of the problems is that when resolving parameters in the query: INSERT INTO T_GENERICDATA (ID, DATA) VALUES((?), (?)) both parameters are resolved as strings (null terminated). Now, doesn't SQLite have databinding functions that should be able to work binary BLOBs (that could have arbitrary length of zeros) ? And try to use that instead of the ODBC driver's parameter binding?? Specifically I don't want the ODBC driver to alloc its own memory, since suppose the BLOB is of size 10 megabytes, then the BLOB itself is 10 MB, then the ODBC will alloc +10 MB, and THEN the query is executed towards SQLite. I'd like zero-copy operation on BLOBs, but I don't know enough of the core SQLite3 API to know how to bind the BLOB to the SQL query. Help? TIA /Rob