[sqlite] Importing a CSV file

[Charles Plessy]

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

[Neelamegam Appadurai]

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

[Chris Schirlinger]

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

[Neelamegam Appadurai]

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] BLOB problem

[Jay]

> 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

[Bielik, Robert]

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?

[Kurt Welgehausen]

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

[Bielik, Robert]

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?

[Luc Vandal]

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

[marco]

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] Write issues on some computers?

[Clay Dowling]

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 ?

[Clay Dowling]

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] more syntax errors ?

[D. Richard Hipp]

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 ?

[Dennis Cote]

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 ?

[Witold Czarnecki]

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

[Lloyd Thomas]

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 ?

[Richard Nagle]

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]

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 ?

[Ulrik Petersen]

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

[albert drent]

I use the api sqlite3_bind_blob.

Albert

Re: [sqlite] Syntax error ?

[Richard Nagle]

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

[sqlite] BLOB problem

[Bielik, Robert]

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

[Ulrik Petersen]

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] Functions

[marco]

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

[Bielik, Robert]

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