Re: [sqlite] How to interleave calls to sqlite3_prepare_v2 and sqlite3_step?

[Dan Kennedy]

On Thu, 2007-03-01 at 05:35 +0100, daan wrote:
> I'm compiling a SQL statement (consisting of multiple statements separated
> by semicolons) using sqlite3_prepare_v2 into a series of sqlite3_stmt
> pointers, later to be executed with sqlite3_step().
> 
> A statement such as:
> 
>   DROP TABLE IF EXISTS test;
>   CREATE TABLE test (id INTEGER, name TEXT);
> 
> is compiled into two sqlite3_stmt pointers that are subsequently fed
> through sqlite3_step(): this works fine. That is, the first time I run the
> program (and the database didn't exist upon program start). When I run the
> program the 2nd time (and the database indeed exists upon program start)
> the prepare_v2 bails out with ERROR and error 'table test already exists'.
> 
> When the statement above is fed through sqlite3_exec() the error does not
> occur. When I had a look at the sqlite3_exec code it struck me that it
> interleaves the calls to prepare/step/reset in a different way:
> 
>   prepare/step/reset prepare/step/reset
> 
> whereas I am trying to
> 
>   prepare/prepare step/step reset/reset
> 
> When I change my code to a similar interleaving scheme as sqlite3_exec I
> dont have any problems.
> 
> My question is: is it indeed required to interleave the calls to
> sqlite3_prepare_v2 and sqlite3_step in a similar way as sqlite3_exec does,
> or should it be possible to fully precompile a series of statements which
> then later can be executed?

When you compile a statement, it is compiled against the current
database schema. In the second run it tried to compile 
"CREATE TABLE test" against a schema that already contained the table
"test". Hence the error message.

Precompiling a series of statements to be executed later will be fine,
provided you are not modifying the database schema.

Dan.



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] How to interleave calls to sqlite3_prepare_v2 and sqlite3_step?

[daan]

I'm compiling a SQL statement (consisting of multiple statements separated
by semicolons) using sqlite3_prepare_v2 into a series of sqlite3_stmt
pointers, later to be executed with sqlite3_step().

A statement such as:

 DROP TABLE IF EXISTS test;
 CREATE TABLE test (id INTEGER, name TEXT);

is compiled into two sqlite3_stmt pointers that are subsequently fed
through sqlite3_step(): this works fine. That is, the first time I run the
program (and the database didn't exist upon program start). When I run the
program the 2nd time (and the database indeed exists upon program start)
the prepare_v2 bails out with ERROR and error 'table test already exists'.

When the statement above is fed through sqlite3_exec() the error does not
occur. When I had a look at the sqlite3_exec code it struck me that it
interleaves the calls to prepare/step/reset in a different way:

 prepare/step/reset prepare/step/reset

whereas I am trying to

 prepare/prepare step/step reset/reset

When I change my code to a similar interleaving scheme as sqlite3_exec I
dont have any problems.

My question is: is it indeed required to interleave the calls to
sqlite3_prepare_v2 and sqlite3_step in a similar way as sqlite3_exec does,
or should it be possible to fully precompile a series of statements which
then later can be executed?

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Installing & Running Sqlite on Linux Webserver?

[John Stanton]

That is what I do.  You then have complete control.

Tom VP wrote:
Thank you, John. The problem with shared servers is the real and 
extensive restrictions on running apps and scripts - I'm considering 
hosting my own web server.


Tom :}
- Original Message - From: "John Stanton" <[EMAIL PROTECTED]>
To: 
Sent: Wednesday, February 28, 2007 12:07 PM
Subject: Re: [sqlite] Installing & Running Sqlite on Linux Webserver?


Sqlite is a library which is linked into your application.  Write your 
application using the Sqlite API and then load that program onto the 
web server.


Tom VP wrote:

I would like to install and run sqlite on my host web server. Does 
anyone know how I can do this? I downloaded:

sqlite-2.8.17.bin.gz and gunzipped it to: sqlite-2.8.17.bin.
Is this what I would install and how. My website is on a shared 
server, so is there a problem with running sqlite in such an 
environment?


Whatever assistance you can provide will be greatly appreciated.

Thnx,
Dr. Tom




- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 








- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 






-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] developers mailing list, ignored patches

[drh]

Adam Megacz <[EMAIL PROTECTED]> wrote:
> 
> I also printed out and signed the copyright papers and mailed them in.
> 

Your copyright release and your patches arrived in today's post.  
The postmark is smeared somewhat but it does appear to say 
"2? DEC 2006" (where the ? is illegible.)

So from Oakland, CA to Charlotte, NC in only 62 days.  And one
wonders why the nobody sends letters anymore

--
D. Richard Hipp  <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] How fast is the sqlite connection created?

[Eric S. Johansson]

Samuel R. Neff wrote:

Some of this performance gain is probably related to caching data and query
plan, not just opening the connection, but still that caching is connection
related and is lost when you close the connection so it's a very real-world
valid comparison.


no surprise that connect() is expensive but what is the cost of 
cursor()?  is it cheap or expensive?


--
Speech-recognition in use.  It makes mistakes, I correct some.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] How fast is the sqlite connection created?

[Martin Jenkins]

Samuel R. Neff wrote:

One last set of performance numbers for opening a connection.  :-)


All points on the curve. ;)


So the impact of open/closing connections on a real-world db really is huge.


You're right, that's a huge difference. Good bit of benchmarking there.


These tests with triggers is very different from tests without--are triggers
completely parsed and compiled every time a connection is opened?


I don't know. I'd assume that triggers were part of the "schema" and 
would be parsed when (or shortly after) a connection is opened.


Martin

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Installing & Running Sqlite on Linux Webserver?

[Tom VP]

Thanks for the info., Clark. I will consider.
Dr. Tom
- Original Message - 
From: "Clark Christensen" <[EMAIL PROTECTED]>

To: 
Sent: Wednesday, February 28, 2007 4:50 PM
Subject: Re: [sqlite] Installing & Running Sqlite on Linux Webserver?


I've had good results with Vizaweb.  They have PHP, and Perl by default, as 
well as MySQL and Postgres.  Plus, they've expressed their willingness to 
consider installing other stuff.  My guess is your part of the site runs in 
its own VM of some kind, so the impact to the host system, and other 
subscriber sites is limited.


I am able to compile SQLite, but the default tcl version is older than 
SQLite expects, so you'd have to get them to update it, or omit the SQLite 
tcl interface (but then you can't make test).


Anyway, with both PG and MySQL, you have a database option other than 
SQLite.


-Clark

- Original Message 
From: Tom VP <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Wednesday, February 28, 2007 11:23:06 AM
Subject: Re: [sqlite] Installing & Running Sqlite on Linux Webserver?

Thank you, John. The problem with shared servers is the real and extensive
restrictions on running apps and scripts - I'm considering hosting my own
web server.

Tom :}
- Original Message - 
From: "John Stanton" <[EMAIL PROTECTED]>

To: 
Sent: Wednesday, February 28, 2007 12:07 PM
Subject: Re: [sqlite] Installing & Running Sqlite on Linux Webserver?



Sqlite is a library which is linked into your application.  Write your
application using the Sqlite API and then load that program onto the web
server.

Tom VP wrote:

I would like to install and run sqlite on my host web server. Does anyone
know how I can do this? I downloaded:
sqlite-2.8.17.bin.gz and gunzipped it to: sqlite-2.8.17.bin.
Is this what I would install and how. My website is on a shared server,
so is there a problem with running sqlite in such an environment?

Whatever assistance you can provide will be greatly appreciated.

Thnx,
Dr. Tom



-
To unsubscribe, send email to [EMAIL PROTECTED]
-






-
To unsubscribe, send email to [EMAIL PROTECTED]
-





-
To unsubscribe, send email to [EMAIL PROTECTED]
-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] How fast is the sqlite connection created?

[Samuel R. Neff]

One last set of performance numbers for opening a connection.  :-)

This is after adding in check constraints for types and foreign key triggers
(which may only be used during development anyways).

Non-Pooled: 17,515.6 ms
Pooled with Reset :562.5 ms
Pooled without Reset  :109.4 ms
One Connection: 62.5 ms

So the impact of open/closing connections on a real-world db really is huge.

These tests with triggers is very different from tests without--are triggers
completely parsed and compiled every time a connection is opened?

Sam



---
We're Hiring! Seeking a passionate developer to join our team building
products. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Installing & Running Sqlite on Linux Webserver?

[Clark Christensen]

I've had good results with Vizaweb.  They have PHP, and Perl by default, as 
well as MySQL and Postgres.  Plus, they've expressed their willingness to 
consider installing other stuff.  My guess is your part of the site runs in its 
own VM of some kind, so the impact to the host system, and other subscriber 
sites is limited.

I am able to compile SQLite, but the default tcl version is older than SQLite 
expects, so you'd have to get them to update it, or omit the SQLite tcl 
interface (but then you can't make test).

Anyway, with both PG and MySQL, you have a database option other than SQLite.

 -Clark

- Original Message 
From: Tom VP <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Wednesday, February 28, 2007 11:23:06 AM
Subject: Re: [sqlite] Installing & Running Sqlite on Linux Webserver?

Thank you, John. The problem with shared servers is the real and extensive 
restrictions on running apps and scripts - I'm considering hosting my own 
web server.

Tom :}
- Original Message - 
From: "John Stanton" <[EMAIL PROTECTED]>
To: 
Sent: Wednesday, February 28, 2007 12:07 PM
Subject: Re: [sqlite] Installing & Running Sqlite on Linux Webserver?


> Sqlite is a library which is linked into your application.  Write your 
> application using the Sqlite API and then load that program onto the web 
> server.
>
> Tom VP wrote:
>> I would like to install and run sqlite on my host web server. Does anyone 
>> know how I can do this? I downloaded:
>> sqlite-2.8.17.bin.gz and gunzipped it to: sqlite-2.8.17.bin.
>> Is this what I would install and how. My website is on a shared server, 
>> so is there a problem with running sqlite in such an environment?
>>
>> Whatever assistance you can provide will be greatly appreciated.
>>
>> Thnx,
>> Dr. Tom
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>
> 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-





-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] OT: suitable web host (was Re: [sqlite] Installing & Running Sqlite on Linux Webserver?)

[Tom VP]

Hi Puneet,

Thank you for the tip - I will definitely look into it - will let you know 
if I decide to change.


Regards,
Dr. Tom
- Original Message - 
From: "P Kishor" <[EMAIL PROTECTED]>

To: 
Sent: Wednesday, February 28, 2007 2:29 PM
Subject: [sqlite] OT: suitable web host (was Re: [sqlite] Installing & 
Running Sqlite on Linux Webserver?)




On 2/28/07, Tom VP <[EMAIL PROTECTED]> wrote:
Thank you, John. The problem with shared servers is the real and 
extensive

restrictions on running apps and scripts - I'm considering hosting my own
web server.


I am happy with Dreamhost. For $8 a month, I get more space and
bandwidth than I can use. I get shell access, and I can compile my own
programs. Basically, I can run anything that is not a daemon (hence,
no PostGres, but yes, to SQLite based web sites). Let me know (off
list) if you are interested, and I believe I can refer you to
Dreamhost whereby gaining some brownie points at no cost to you.



- Original Message -
From: "John Stanton" <[EMAIL PROTECTED]>
To: 
Sent: Wednesday, February 28, 2007 12:07 PM
Subject: Re: [sqlite] Installing & Running Sqlite on Linux Webserver?


> Sqlite is a library which is linked into your application.  Write your
> application using the Sqlite API and then load that program onto the 
> web

> server.
>
> Tom VP wrote:
>> I would like to install and run sqlite on my host web server. Does 
>> anyone

>> know how I can do this? I downloaded:
>> sqlite-2.8.17.bin.gz and gunzipped it to: sqlite-2.8.17.bin.
>> Is this what I would install and how. My website is on a shared 
>> server,

>> so is there a problem with running sqlite in such an environment?
>>
>> Whatever assistance you can provide will be greatly appreciated.
>>
>> Thnx,
>> Dr. Tom
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>
>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-





--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
-
collaborate, communicate, compete
=

-
To unsubscribe, send email to [EMAIL PROTECTED]
-






-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] OT: suitable web host (was Re: [sqlite] Installing & Running Sqlite on Linux Webserver?)

[P Kishor]

On 2/28/07, Tom VP <[EMAIL PROTECTED]> wrote:

Thank you, John. The problem with shared servers is the real and extensive
restrictions on running apps and scripts - I'm considering hosting my own
web server.


I am happy with Dreamhost. For $8 a month, I get more space and
bandwidth than I can use. I get shell access, and I can compile my own
programs. Basically, I can run anything that is not a daemon (hence,
no PostGres, but yes, to SQLite based web sites). Let me know (off
list) if you are interested, and I believe I can refer you to
Dreamhost whereby gaining some brownie points at no cost to you.



- Original Message -
From: "John Stanton" <[EMAIL PROTECTED]>
To: 
Sent: Wednesday, February 28, 2007 12:07 PM
Subject: Re: [sqlite] Installing & Running Sqlite on Linux Webserver?


> Sqlite is a library which is linked into your application.  Write your
> application using the Sqlite API and then load that program onto the web
> server.
>
> Tom VP wrote:
>> I would like to install and run sqlite on my host web server. Does anyone
>> know how I can do this? I downloaded:
>> sqlite-2.8.17.bin.gz and gunzipped it to: sqlite-2.8.17.bin.
>> Is this what I would install and how. My website is on a shared server,
>> so is there a problem with running sqlite in such an environment?
>>
>> Whatever assistance you can provide will be greatly appreciated.
>>
>> Thnx,
>> Dr. Tom
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>
>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-





--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
-
collaborate, communicate, compete
=

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] String concatenation disallowed within a RAISE function?

[Samuel R. Neff]

It seems I can't use string concatenation within a RAISE call.  This code:

SELECT RAISE(ROLLBACK, 'test');

Works fine but this code

SELECT RAISE(ROLLBACK, 'test' || 'ing');

Gives an error

SQL error: near "||": syntax error

Is this correct behavior?  I found a work-around by doing this:

SELECT RAISE(ROLLBACK, ERROR_TEXT) FROM (SELECT 'test' || 'ing'
ERROR_TEXT);

but I would think || should work within RAISE directly.

Thanks,

Sam


---
We're Hiring! Seeking a passionate developer to join our team building
products. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Installing & Running Sqlite on Linux Webserver?

[Tom VP]

Thank you, John. The problem with shared servers is the real and extensive 
restrictions on running apps and scripts - I'm considering hosting my own 
web server.


Tom :}
- Original Message - 
From: "John Stanton" <[EMAIL PROTECTED]>

To: 
Sent: Wednesday, February 28, 2007 12:07 PM
Subject: Re: [sqlite] Installing & Running Sqlite on Linux Webserver?


Sqlite is a library which is linked into your application.  Write your 
application using the Sqlite API and then load that program onto the web 
server.


Tom VP wrote:
I would like to install and run sqlite on my host web server. Does anyone 
know how I can do this? I downloaded:

sqlite-2.8.17.bin.gz and gunzipped it to: sqlite-2.8.17.bin.
Is this what I would install and how. My website is on a shared server, 
so is there a problem with running sqlite in such an environment?


Whatever assistance you can provide will be greatly appreciated.

Thnx,
Dr. Tom



-
To unsubscribe, send email to [EMAIL PROTECTED]
-






-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Importing data without a primary key

[Griggs, Donald]

-Original Message-
From: Sebastian Tennant [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, February 28, 2007 12:56 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] Importing data without a primary key

Hi all,

I have a CSV file of data that doesn't include a column suitable to be
the primary key.  Should I edit the CSV data file before importing it (a
pain to do), or is there an easy way to create (and
populate) a primary key column once the data has been imported?

Any suggestions much appreciated.

Sebastian
==
Hi Sebastian,

There should not be a problem.
The imported table will implicitly have its row number become a primary
key.
If you need to, though, you can later add a column to your table using
the ALTER TABLE command.

To use the row number, though, here's an excerpt from: 
   http://www.sqlite.org/lang_expr.html

A column name can be any of the names defined in the CREATE TABLE
statement or one of the following special identifiers: "ROWID", "OID",
or "_ROWID_". These special identifiers all describe the unique random
integer key (the "row key") associated with every row of every table.
The special identifiers only refer to the row key if the CREATE TABLE
statement does not define a real column with the same name. Row keys act
like read-only columns. A row key can be used anywhere a regular column
can be used, except that you cannot change the value of a row key in an
UPDATE or INSERT statement. "SELECT * ..." does not return the row key. 


[opinions expressed are my own, not those of my company]

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Importing data without a primary key

[Sebastian Tennant]

Hi all,

I have a CSV file of data that doesn't include a column suitable to be the 
primary key.  Should I 
edit the CSV data file before importing it (a pain to do), or is there an easy 
way to create (and 
populate) a primary key column once the data has been imported?

Any suggestions much appreciated.

Sebastian

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] UTF16 Encoding

[Nuno Lucas]

On 2/28/07, Pavan <[EMAIL PROTECTED]> wrote:

>But I'm thinking you really want to do this anyway:

Well, I have a requirement to store/retrieve UTF16 strings.  The current
discussion thread
is based on that.  Pls let me know if my understanding of using
sqlite3_open16 is correct
to store and retrieve utf16 strings.


A sqlite database stores text as UTF-8 text, by default, meaning you
can use any sqlite UTF-16 function to retrieve and/or store UNICODE
data, even on a database no open using sqlite3_open16().

The only difference is that if you use sqlite3_open16 to create a new
database, internally the database will store text as UTF-16, instead
of UTF-8, but to the user, the result will be exactly the same.

As Dan pointed out, creating a database using sqlite3_open16() is
exactly the same as using sqlite3_open() followed by executing
"PRAGMA encoding=UTF-16", but all functions, having or not the "16"
suffix will return exactly the same data, though with different
encodings.

If you need to get UTF-16 strings, you have 2 options: either use the
UTF-8 sqlite functions and latter convert to UTF-16 or use the UTF-16
functions, but the data in the database is the same.

It depends on your application what makes more sense to you. On Linix,
UTF-8 makes more sense than UTF-16 because wchar_t can't be used to
store UTF-16 strings, while on windows, you can just use wchar_t
strings and call the UTF-16 functions.

You decide what it's better...

Regards,
~Nuno Lucas

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Installing & Running Sqlite on Linux Webserver?

[John Stanton]

Sqlite is a library which is linked into your application.  Write your 
application using the Sqlite API and then load that program onto the web 
server.


Tom VP wrote:

I would like to install and run sqlite on my host web server. Does anyone know 
how I can do this? I downloaded:
sqlite-2.8.17.bin.gz and gunzipped it to: sqlite-2.8.17.bin.
Is this what I would install and how. My website is on a shared server, so is 
there a problem with running sqlite in such an environment?

Whatever assistance you can provide will be greatly appreciated.

Thnx,
Dr. Tom 



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Installing & Running Sqlite on Linux Webserver?

[P Kishor]

On 2/28/07, Tom VP <[EMAIL PROTECTED]> wrote:

Dear Puneet,

Thanks for your response. I don't have compile capability on my shared
webserver. Therefore, I'd like to install pre-compiled/executable sqlite
(much as on a pc) - is there a download that has the precompiled code that
will execute on a Unix server? Is the one I mentioned -  sqlite-2.8.17.bin -
what I need. If it is, how to I install it (what does one do with a .bin
file as opposed to an .exe). If not, which download is precompiled and
executable under Unix?


I have no idea what version of Linux you are running (there is no such
thing as a "Unix server" -- I mean, there is, but you have to specify
what operating system), but the following might fit your bill



otherwise, look at other precompiled binaries for Linux at


Also, ask your web server company... in all likelihood they already
have sqlite compiled and sitting under /usr/local/bin for you to use.




- Original Message -
From: "P Kishor" <[EMAIL PROTECTED]>
To: 
Sent: Wednesday, February 28, 2007 10:37 AM
Subject: Re: [sqlite] Installing & Running Sqlite on Linux Webserver?


> On 2/28/07, Tom VP <[EMAIL PROTECTED]> wrote:
>> I would like to install and run sqlite on my host web server. Does anyone
>> know how I can do this? I downloaded:
>> sqlite-2.8.17.bin.gz and gunzipped it to: sqlite-2.8.17.bin.
>> Is this what I would install and how. My website is on a shared server,
>> so is there a problem with running sqlite in such an environment?
>>
>> Whatever assistance you can provide will be greatly appreciated.
>
>
> First, let's assume you have shell access on your web server, and you
> can compile programs.
>
> Second, why bother with 2.8.17... get the latest unless there is some
> pressing reason not to.
>
> download the source to your directory, and then configure with a
> prefix for where you want to put the program. Then make and make
> install.
>
> --
> Puneet Kishor http://punkish.eidesis.org/
> Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
> Open Source Geospatial Foundation http://www.osgeo.org/education/
> -
> collaborate, communicate, compete
> =
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>
>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-





--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
-
collaborate, communicate, compete
=

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Installing & Running Sqlite on Linux Webserver?

[Tom VP]

Dear Puneet,

Thanks for your response. I don't have compile capability on my shared 
webserver. Therefore, I'd like to install pre-compiled/executable sqlite 
(much as on a pc) - is there a download that has the precompiled code that 
will execute on a Unix server? Is the one I mentioned -  sqlite-2.8.17.bin - 
what I need. If it is, how to I install it (what does one do with a .bin 
file as opposed to an .exe). If not, which download is precompiled and 
executable under Unix?


Please advise - whatever you - or ANYONE - can offer will be appreciated.

Regards,
Dr. Tom

- Original Message - 
From: "P Kishor" <[EMAIL PROTECTED]>

To: 
Sent: Wednesday, February 28, 2007 10:37 AM
Subject: Re: [sqlite] Installing & Running Sqlite on Linux Webserver?



On 2/28/07, Tom VP <[EMAIL PROTECTED]> wrote:
I would like to install and run sqlite on my host web server. Does anyone 
know how I can do this? I downloaded:

sqlite-2.8.17.bin.gz and gunzipped it to: sqlite-2.8.17.bin.
Is this what I would install and how. My website is on a shared server, 
so is there a problem with running sqlite in such an environment?


Whatever assistance you can provide will be greatly appreciated.



First, let's assume you have shell access on your web server, and you
can compile programs.

Second, why bother with 2.8.17... get the latest unless there is some
pressing reason not to.

download the source to your directory, and then configure with a
prefix for where you want to put the program. Then make and make
install.

--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
-
collaborate, communicate, compete
=

-
To unsubscribe, send email to [EMAIL PROTECTED]
-






-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Installing & Running Sqlite on Linux Webserver?

[P Kishor]

On 2/28/07, Tom VP <[EMAIL PROTECTED]> wrote:

I would like to install and run sqlite on my host web server. Does anyone know 
how I can do this? I downloaded:
sqlite-2.8.17.bin.gz and gunzipped it to: sqlite-2.8.17.bin.
Is this what I would install and how. My website is on a shared server, so is 
there a problem with running sqlite in such an environment?

Whatever assistance you can provide will be greatly appreciated.



First, let's assume you have shell access on your web server, and you
can compile programs.

Second, why bother with 2.8.17... get the latest unless there is some
pressing reason not to.

download the source to your directory, and then configure with a
prefix for where you want to put the program. Then make and make
install.

--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
-
collaborate, communicate, compete
=

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Insert

[Xavier RAYNAUD]

Why not ?

Christian POMPIER a écrit :

Could i make to insert 10 000 row in my table with a loop ?

thx

  



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] how to insert and select a date column

[Dennis Cote]

Rafi Cohen wrote:
 
Hi, I apologize in advance for the probably very basic question as I'm a

newbie to sqlite and integrating sql in C applications.
One of my tables in my database requires a date column. I understand
from a tutorial not related to C/C++ api that insering date columns is
not trivial and triggers need to be used.
As I am not knowledgeable about triggers, I would like to ask how can I
insert a date column in a C application and later retrieve this column
or compare it with current date in a select statement?
I need to insert just any date in this column and not only the current
date.
If somebody can send me an example off list to implement this, I'll much
appreciate it.
Thanks, Rafi.

  

Rafi,

SQLite does not support SQL standard date and time types. It does 
however have a very flexible set of date and time functions that use 
strings (i.e. -MM-DD and/or HH:MM:SS etc), integer numbers (i.e. 
unix epoch times, seconds since 1970-01-01), or floating point numbers 
(i.e julian day numbers) which can all be stored in the database. These 
function also allow these formats to be easily converted from one type 
to another, in particular, from integer to string or floating point to 
string for display. These numeric formats make date comparisons very 
simple. For more information see 
http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions


Once you have decided how you want to store your dates you can insert 
them easily using a default value for your column or supplying a date in 
the required format. Lets assume you decide to store strings since they 
are human readable, even if somewhat inefficient from a storage point of 
view.


   create table t (id, data, created default current_date);

   insert into t (id, data) values (1, 'test');
   insert into t (id, data) values (2, 'more');

   select created from t;
   2007-02-28
   2007-02-28

You can also provide an explicit value that overrides the default value 
when you add a record.


   insert into t values (3, 'explicit', '2007-02-14');
   insert into t values (3, 'explicit', date('now', '+2 days'));

And you can update these dates using the date and time functions

   update t set created = date(created, '-1 month') where id = 2;

Finally you can use the date and time functions to convert the dates for 
display.


   select created, julianday(created) from t;

HTH
Dennis Cote

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Insert

[P Kishor]

On 2/28/07, Christian POMPIER <[EMAIL PROTECTED]> wrote:

Could i make to insert 10 000 row in my table with a loop ?




Christian,

This kind of a question is likely to either not result in an answer,
or in an answer that is unsatisfactory to you -- either way, bandwidth
wasted. Ask better questions, get better answers. Specify whether you
are looking for SQL help, or help with a specific programming
language. Either way, specify what you have tried already, unless it
is something that is so difficult to try first that it merits asking
first.

Why even bother asking for "10,000" rows... if you know how to insert
more than 1 row, you can insert a million rows. You might want to ask
about efficiency of process, optimization, etc.

We have to guess from your question -- your real question is on using
a loop, but you don't specify any language. Are you programming in C,
or a scripting language, or Java?

Below is Perl-ish psuedo code (because Perl is what I know), which you
can adapt to your language --

# set db autocommit to false, so inserts are really fast
my dbh = (blah blah { autocommit => false } );

# prepare the statement in advance, so it doesn't
# have to be prepared for each row. Use bind variables
my statement = dbh("INSERT INTO tbl (a, b) VALUES (?, ?)");

# get an array of your 10,000 values. Here, it is an array of arrays
my array_of_arrays = (row1 .. row1);

# loop over the array and insert
foreach my row (array_of_arrays) {
 statement->execute(row[0], row[1]);
}

# commit to the db
dbh->commit;

Writing all of the above should take you 5 mins. Inserting it should
take a fraction of a second.

Hope this helps.

--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
-
collaborate, communicate, compete
=

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] UTF16 Encoding

[Pavan]

But I'm thinking you really want to do this anyway:


Well, I have a requirement to store/retrieve UTF16 strings.  The current
discussion thread
is based on that.  Pls let me know if my understanding of using
sqlite3_open16 is correct
to store and retrieve utf16 strings.

Thanks,
Pavan.



On 2/28/07, Dan Kennedy <[EMAIL PROTECTED]> wrote:


On Wed, 2007-02-28 at 13:13 +0100, Pavan wrote:
> Hi Lucas,
>
> >Shouldn't it be "sqlite3_open16(L"test.db",)" ?
>
> I tried this call. It works. But, the db file is created only with the
first
> character name
> and the extenstion is also missing. (its like 't')
>
> Also, can you point to me at some documentation which explains what does
> the 'L' mean in the API ?

It means "string literal", and is used for building an array of wchar_t
elements (instead of regular 8-bit char). The thing is, C doesn't
say how big wchar_t is supposed to be. Some compilers make it 32-bit
(yours could be one of these), and others make it 16-bit. There
is, AFAIK, no portable way to declare UTF-16 string constants using C.

Please inform me if I'm wrong about that, it's something I'd like to
know how to do :)

But I'm thinking you really want to do this anyway:

sqlite3_open("test.db", );
sqlite3_exec(db, "PRAGMA encoding = UTF-16", 0, 0, 0);

sqlite3_open16() just converts the argument back to UTF-8 and does
exactly the above anyway.

Dan.


> Thanks,
> Pavan.
>
>
> On 2/28/07, Nuno Lucas <[EMAIL PROTECTED]> wrote:
> >
> > On 2/28/07, Pavan <[EMAIL PROTECTED]> wrote:
> > > Hi,
> > >
> > > When i tried to create the db file using API sqlite3_open16("test.db
> > ",)
> > > it creats the file name with some junk
> > > characters.
> >
> > Shouldn't it be "sqlite3_open16(L"test.db",)" ?
> >
> > Regards,
> > ~Nuno Lucas
> >
> > > The file name is correct if i use sqlite3_open("test.db",).  Does
it
> > mean
> > > sqlite3 does not support
> > > UTF16 encoding ?
> > > or
> > > Am i missing some package ?
> > >
> > > Thanks,
> > > Pavan.
> >
> >
> >
-
> > To unsubscribe, send email to [EMAIL PROTECTED]
> >
> >
-
> >
> >
>
>



-
To unsubscribe, send email to [EMAIL PROTECTED]

-





--
'
Always finish stronger than you start
*

Re: [sqlite] UTF16 Encoding

[Dan Kennedy]

On Wed, 2007-02-28 at 13:13 +0100, Pavan wrote:
> Hi Lucas,
> 
> >Shouldn't it be "sqlite3_open16(L"test.db",)" ?
> 
> I tried this call. It works. But, the db file is created only with the first
> character name
> and the extenstion is also missing. (its like 't')
> 
> Also, can you point to me at some documentation which explains what does
> the 'L' mean in the API ?

It means "string literal", and is used for building an array of wchar_t
elements (instead of regular 8-bit char). The thing is, C doesn't
say how big wchar_t is supposed to be. Some compilers make it 32-bit
(yours could be one of these), and others make it 16-bit. There
is, AFAIK, no portable way to declare UTF-16 string constants using C.

Please inform me if I'm wrong about that, it's something I'd like to 
know how to do :)

But I'm thinking you really want to do this anyway:

sqlite3_open("test.db", );
sqlite3_exec(db, "PRAGMA encoding = UTF-16", 0, 0, 0);

sqlite3_open16() just converts the argument back to UTF-8 and does
exactly the above anyway.

Dan.


> Thanks,
> Pavan.
> 
> 
> On 2/28/07, Nuno Lucas <[EMAIL PROTECTED]> wrote:
> >
> > On 2/28/07, Pavan <[EMAIL PROTECTED]> wrote:
> > > Hi,
> > >
> > > When i tried to create the db file using API sqlite3_open16("test.db
> > ",)
> > > it creats the file name with some junk
> > > characters.
> >
> > Shouldn't it be "sqlite3_open16(L"test.db",)" ?
> >
> > Regards,
> > ~Nuno Lucas
> >
> > > The file name is correct if i use sqlite3_open("test.db",).  Does it
> > mean
> > > sqlite3 does not support
> > > UTF16 encoding ?
> > > or
> > > Am i missing some package ?
> > >
> > > Thanks,
> > > Pavan.
> >
> >
> > -
> > To unsubscribe, send email to [EMAIL PROTECTED]
> >
> > -
> >
> >
> 
> 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] UTF16 Encoding

[Brownie]

I tried this call. It works. But, the db file is created only with the first
character name
and the extenstion is also missing. (its like 't')


I think that your compiler's wchar_t is 32bit.
But sqlite3_open16() requires 16bit characters.

Try the following code.

sqlite3_open16( "t\0e\0s\0t\0.\0d\0b\0\0",  );

Regards,

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] How fast is the sqlite connection created?

[Samuel R. Neff]

I implemented connection pooling in the .NET wrapper and using the pool it
takes 90ms for 1,000 connections or 500ms if I clear out attached databases
and temp tables each time.

Is there a really quick way to reset attached/temp tables on a connection?
Right now I do a 

pragma database_list; 

followed by a 

select type, name from sqlite_temp_master;

and loop through the results which apparently takes 400us when there are no
attached tables and temp is empty (longer if it actually has to detach/drop
something).

Thanks,

Sam



---
We're Hiring! Seeking a passionate developer to join our team building
products. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
 
-Original Message-
From: Samuel R. Neff [mailto:[EMAIL PROTECTED] 
Sent: Monday, February 26, 2007 11:58 PM
To: 'sqlite-users@sqlite.org'
Subject: RE: [sqlite] How fast is the sqlite connection created?


I ran some tests and received fairly drastic results.  Our schema has 67
tables right now (once we add in the history tables, it'll have about double
that) and 116 indexes, excluding the automatic primary key indexes.

I ran 1,000 simple select statements

SELECT COUNT(*) FROM USERS;

and the USERS table is empty (actually, the whole db is empty, only
structure created for the tests).

One statement per connection  : 2906.3 ms
One connection for all statements :   62.5 ms

Some of this performance gain is probably related to caching data and query
plan, not just opening the connection, but still that caching is connection
related and is lost when you close the connection so it's a very real-world
valid comparison.

Best regards,

Sam



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Singular Sqlite3Explorer 2.0 question

[Cariotoglou Mike]

suppose you have a text file that contains :
 
Myname,yourname hisname
Myname,yourname hisname
Myname,yourname hisname
Myname,yourname hisname
Myname,yourname hisname
Myname,yourname hisname
Myname,yourname hisname

 
notice that this is an irregular format, because the first column
delimiter is a comma, and the second is a space. this kind of file
cannot be parsed easily using "standard" formats like CSV. A regular
expression allows you to break down the lines into columns in an ad hoc
fashion. the expression :
 
(\w+),(\w+) (\w+)
 
will break lines into three columns, for example, based on a comma and
space delimiter. the parentheses are essential, to provide subgroups
that map to the columns. try the example above, it should become
clearer.
if you do not now how to use Perl regular expressions, you need to do
some background reading first.
 
regards,mike
 



From: Noah Hart [mailto:[EMAIL PROTECTED]
Sent: Tue 27/2/2007 6:59 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] Singular Sqlite3Explorer 2.0 question



Has anyone used this program?

I am trying to use the "Import using Regular Expression" option.
Itis looking for some type of regular expression to parse the columns.

Any suggestions or sample expressions you can offer?

Regards,

Noah Hart



CONFIDENTIALITY NOTICE:
This message may contain confidential and/or privileged information. If
you are not the addressee or authorized to receive this for the
addressee, you must not use, copy, disclose, or take any action based on
this message or any information herein. If you have received this
message in error, please advise the sender immediately by reply e-mail
and delete this message. Thank you for your cooperation.





-
To unsubscribe, send email to [EMAIL PROTECTED]

-





-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: Re: Insert

[Igor Tandetnik]

Christian POMPIER <[EMAIL PROTECTED]> wrote:

De : Igor Tandetnik [mailto:[EMAIL PROTECTED]
Christian POMPIER
<[EMAIL PROTECTED]> wrote:

Could i make to insert 10 000 row in my table with a loop ?


Yes.


But what is the good syntax ?


The syntax of the INSERT statement is described here:

http://sqlite.org/lang_insert.html

The syntax for creating a loop can be found in a manual for your 
programming language of choice.


Igor Tandetnik 



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Re: Insert

[Christian POMPIER]

But what is the good syntax ?

-Message d'origine-
De : Igor Tandetnik [mailto:[EMAIL PROTECTED] 
Envoyé : mercredi 28 février 2007 13:37
À : SQLite
Objet : [sqlite] Re: Insert

Christian POMPIER <[EMAIL PROTECTED]> wrote:
> Could i make to insert 10 000 row in my table with a loop ?

Yes.

Igor Tandetnik

-
To unsubscribe, send email to [EMAIL PROTECTED]
-


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: Insert

[Igor Tandetnik]

Christian POMPIER <[EMAIL PROTECTED]> wrote:

Could i make to insert 10 000 row in my table with a loop ?


Yes.

Igor Tandetnik

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] UTF16 Encoding

[Pavan]

Hi Lucas,


Shouldn't it be "sqlite3_open16(L"test.db",)" ?


I tried this call. It works. But, the db file is created only with the first
character name
and the extenstion is also missing. (its like 't')

Also, can you point to me at some documentation which explains what does
the 'L' mean in the API ?

Thanks,
Pavan.


On 2/28/07, Nuno Lucas <[EMAIL PROTECTED]> wrote:


On 2/28/07, Pavan <[EMAIL PROTECTED]> wrote:
> Hi,
>
> When i tried to create the db file using API sqlite3_open16("test.db
",)
> it creats the file name with some junk
> characters.

Shouldn't it be "sqlite3_open16(L"test.db",)" ?

Regards,
~Nuno Lucas

> The file name is correct if i use sqlite3_open("test.db",).  Does it
mean
> sqlite3 does not support
> UTF16 encoding ?
> or
> Am i missing some package ?
>
> Thanks,
> Pavan.


-
To unsubscribe, send email to [EMAIL PROTECTED]

-





--
'
Always finish stronger than you start
*

[sqlite] Insert

[Christian POMPIER]

Could i make to insert 10 000 row in my table with a loop ?

 

thx

Re: [sqlite] UTF16 Encoding

[Nuno Lucas]

On 2/28/07, Pavan <[EMAIL PROTECTED]> wrote:

Hi,

When i tried to create the db file using API sqlite3_open16("test.db",)
it creats the file name with some junk
characters.


Shouldn't it be "sqlite3_open16(L"test.db",)" ?

Regards,
~Nuno Lucas


The file name is correct if i use sqlite3_open("test.db",).  Does it mean
sqlite3 does not support
UTF16 encoding ?
or
Am i missing some package ?

Thanks,
Pavan.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] UTF16 Encoding

[Pavan]

Hi,

When i tried to create the db file using API sqlite3_open16("test.db",)
it creats the file name with some junk
characters.
The file name is correct if i use sqlite3_open("test.db",).  Does it mean
sqlite3 does not support
UTF16 encoding ?
or
Am i missing some package ?

Thanks,
Pavan.

--
'
Always finish stronger than you start
*

Re: [sqlite] Crosscompiled sqlite was: developer mailing list

[Martin Jenkins]

Jakub Ladman wrote:

But there is not tcl library at the time and when I tried to build sqlite 
without -DNO_TCL it failed.


I think you will have fewer problems building Tcl than you did building 
SQLite because the automake code will be more up to date. You'v ebuilt 
SQLite so you're an expert now. ;)


I can test it on the sh4 cpu for the community, of course, if you think it 
will be usefull.


I'd certainly try it if I was in your position. Getting Tcl built might 
take you half a day or a day, but if you do that and and can test SQLite 
then you'll get the benefit of years of testing in half an hour. If you 
test the library and it passes, you can draw a line at the interface and 
build on a solid foundation. If you don't test and you get odd bugs a 
month down the line you'll never know if it's a porting problem or a bug 
in your code or...


Martin

-
To unsubscribe, send email to [EMAIL PROTECTED]
-