[sqlite] Insertion and Search at a same time is it possible?

2007-09-20 Thread Sreedhar.a
Hi Everyone,
 
I am implementing server database using sqlite.
 
I will be having the multiple clients browsing the database.
At the same time the database can also be updated.
 
I can copy the database into the local memory of my system and can perform
search.
Can i implement inserting the records at the same time into the same file.
while searching
 
Your suggestions will help me a lot in my project.
 
Best Regards,
A.Sreedhar.
 
 


[sqlite] Inserting Problem : More number of records at a single stretch

2007-09-20 Thread Sreedhar.a
Hi,

 

I am working in Sqlite 3.3.6

 

I want to insert more records[like 20,000.] at a single stretch.

 

I did the following in shell.c,

 

Static void process_input (struct callback_data *p)

{

  char *zLine;

  int nLine;

  nLine = 200;

  zLine = malloc( nLine );

 

Strcpy ([n],"create table MUSIC (Id integer primary key, Album text
not null collate nocase);"

  "Insert into MUSIC (Album)
values ('Deshamuduru');"

..);

While (zLine[n])

{ 

n++; 

}

 

ZLine = realloc (zLine, n+1 );

}

 

 

Inside process_input I am doing strcpy where I will insert all my records.

But my means of this I can able to insert only some 200 records. even if
increase the nLine value I can not able to insert more than that at a single
stretch.

I want 4 records to be in my database, for that each time I can only
insert 200.

Can any one help me to solve this problem by some other way?

I read in this forum like we can insert 20,000 . at a single stretch. How it
can be done. Where we have to change in the code? 

 

Best Regards,

Sreedhar. 

 



Re: [sqlite] dates and times in sqlite

2007-09-20 Thread John Stanton
First make sure that you store the date in Julian format by using the 
julianday function.  Then you should be able to get the day of the week 
by take modulo 7 of the julian date.  You can use the strftime function 
to do that -


**strftime( FORMAT, TIMESTRING, MOD, MOD, ...)
**
** Return a string described by FORMAT.  Conversions as follows:
**
**   %d  day of month
**   %f  ** fractional seconds  SS.SSS
**   %H  hour 00-24
**   %j  day of year 000-366
**   %J  ** Julian day number
**   %m  month 01-12
**   %M  minute 00-59
**   %s  seconds since 1970-01-01
**   %S  seconds 00-59
**   %w  day of week 0-6  sunday==0
**   %W  week of year 00-53
**   %Y  year -
**   %%  %
*/

Mark Wyszomierski wrote:

Hi,

I've made a text field called "timestamp" which has dates in the form:

-MM-DD HH:MM:SS

I want to test if the day portion is a Tuesday for example - something like:

SELECT * FROM my_table WHERE DAY(timestamp) = TUESDAY

is something like that at all possible? I've looked at the extended
functions here:

http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions

but am unsure of how to take advantage of them.

Thank you,
Mark

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




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



Re: [sqlite] Re: dates and times in sqlite

2007-09-20 Thread Mark Wyszomierski
Oh I forgot to put quotes around the result, thanks Igor,

Mark

On 9/20/07, Igor Tandetnik <[EMAIL PROTECTED]> wrote:
> Mark Wyszomierski <[EMAIL PROTECTED]>
> wrote:
> > I've made a text field called "timestamp" which has dates in the form:
> >
> >-MM-DD HH:MM:SS
> >
> > I want to test if the day portion is a Tuesday for example -
> > something like:
> >
> >SELECT * FROM my_table WHERE DAY(timestamp) = TUESDAY
>
> select * from my_table
> where strftime('%w', timestamp) = '2';
>
> Igor Tandetnik
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>

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



[sqlite] Re: dates and times in sqlite

2007-09-20 Thread Igor Tandetnik

Mark Wyszomierski <[EMAIL PROTECTED]>
wrote:

I've made a text field called "timestamp" which has dates in the form:

   -MM-DD HH:MM:SS

I want to test if the day portion is a Tuesday for example -
something like:

   SELECT * FROM my_table WHERE DAY(timestamp) = TUESDAY


select * from my_table
where strftime('%w', timestamp) = '2';

Igor Tandetnik 



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



[sqlite] dates and times in sqlite

2007-09-20 Thread Mark Wyszomierski
Hi,

I've made a text field called "timestamp" which has dates in the form:

-MM-DD HH:MM:SS

I want to test if the day portion is a Tuesday for example - something like:

SELECT * FROM my_table WHERE DAY(timestamp) = TUESDAY

is something like that at all possible? I've looked at the extended
functions here:

http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions

but am unsure of how to take advantage of them.

Thank you,
Mark

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



Re: [sqlite] SQLite and html character entities

2007-09-20 Thread P Kishor
On 9/20/07, Trevor Talbot <[EMAIL PROTECTED]> wrote:
> On 9/20/07, P Kishor <[EMAIL PROTECTED]> wrote:
>
> > sqlite> select * from tbl;
> > the first record
> > é ç ñ î
> > more from 3rd row
> > row four
> > these "volunteered" activities
> > <á ø ã ü î & others>
> > -
> > sqlite> .mode csv
> > sqlite> .output foo.csv
> > sqlite> select * from tbl;
> > sqlite> .q
> > Lucknow:~/Data/ecoservices punkish$ less foo.csv
> > "the first record"
> > "\351 \347 \361 \356"
> > "more from 3rd row"
> > "row four"
> > "these \223volunteered\224 activities"
> > "<\341 \370 \343 \374 \356 & others>"
> > foo.csv (END)
> > -
>
> Note that this is *not* UTF-8.  If you're still using this as test
> data, you need to get rid of it and use UTF-8 encoded data instead.
>

this is where I lost you... when you say "this" is not UTF8, what is
"this"? All I want is that I want (1) the user to be able to type ç in
the web form, and (2) I want to be able to save ç in the db. (3) Then
when I look at that data, either on the command line, but definitely
back on the web, I want it to appear as ç. (4) If I export it, I
should still be able to see it as ç and not something else.

Seems like I was able to do 1, 2, and 3 with my test case, but not 4
(I got \347 instead ç).

Also, in my production case, 1,2, and 3 are not very reliable. Are you
saying my data above are not UTF8? If so, I would like to know how you
can tell that, so I can recognize it in the future myself. Also, I
would like to know how I can do what you are suggesting I should do,
that is, how can I ensure that I "use UTF8 encoded data"?

Many thanks,


-- 
Puneet Kishor
http://punkish.eidesis.org/
Nelson Institute for Environmental Studies
http://www.nelson.wisc.edu/
Open Source Geospatial Foundation (OSGeo)
http://www.osgeo.org/
Summer 2007 S Policy Fellow, The National Academies
http://www.nas.edu/

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



Re: [sqlite] Multi-User confusion

2007-09-20 Thread Zbigniew Baniewski
Yes, found it: no support for that in 3.3.8. :( Must compile newer version.
-- 
pozdrawiam / regards

Zbigniew Baniewski

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



Re: [sqlite] SQLite and html character entities

2007-09-20 Thread Trevor Talbot
On 9/20/07, P Kishor <[EMAIL PROTECTED]> wrote:

> sqlite> select * from tbl;
> the first record
> é ç ñ î
> more from 3rd row
> row four
> these "volunteered" activities
> <á ø ã ü î & others>
> -
> sqlite> .mode csv
> sqlite> .output foo.csv
> sqlite> select * from tbl;
> sqlite> .q
> Lucknow:~/Data/ecoservices punkish$ less foo.csv
> "the first record"
> "\351 \347 \361 \356"
> "more from 3rd row"
> "row four"
> "these \223volunteered\224 activities"
> "<\341 \370 \343 \374 \356 & others>"
> foo.csv (END)
> -

Note that this is *not* UTF-8.  If you're still using this as test
data, you need to get rid of it and use UTF-8 encoded data instead.

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



Re: [sqlite] Precompiled 2.x for Windows?

2007-09-20 Thread epankoke
Actually, it compiled, but it appears that I was not successful.  The compile I 
made did not work for what I wanted.  However, the DLL at the link you provided 
for me did work, so thanks for pointing that out.

--
Eric Pankoke
Founder / Lead Developer
Point Of Light Software
http://www.polsoftware.com/

 -- Original message --
From: Kees Nuyt <[EMAIL PROTECTED]>
> On Thu, 20 Sep 2007 20:42:30 +, you wrote:
> 
> > Where?  sqlite-2_8_17.zip is just an executable,
> > and the other zip for Windows is just the source. 
> > As it stands, however, I was able to recompile
> > the DLL after all and it didn't make a difference.
> 
> Oh, sorry, I should have checked.
> The link isn't on the page anymore, but (as with many old
> versions) the download is still available: 
> 
> http://www.sqlite.org/sqlitedll-2_8_17.zip 
> 
> I'm glad you managed to compile it by yourself.
> -- 
>   (  Kees Nuyt
>   )
> c[_]
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 


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



Re: [sqlite] Precompiled 2.x for Windows?

2007-09-20 Thread Kees Nuyt
On Thu, 20 Sep 2007 20:42:30 +, you wrote:

> Where?  sqlite-2_8_17.zip is just an executable,
> and the other zip for Windows is just the source. 
> As it stands, however, I was able to recompile
> the DLL after all and it didn't make a difference.

Oh, sorry, I should have checked.
The link isn't on the page anymore, but (as with many old
versions) the download is still available: 

http://www.sqlite.org/sqlitedll-2_8_17.zip 

I'm glad you managed to compile it by yourself.
-- 
  (  Kees Nuyt
  )
c[_]

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



Re: [sqlite] sqlite3_open_v2 and SQLITE_OPEN_READONLY

2007-09-20 Thread Liam Healy
No, I meant like in http://sqlite.org/capi3ref.html#sqlite3_open,
adding a small logo  or box "new in 3.5.0" adjacent to the description
of _v2.  Then it is clear what version(s) support the feature.

On 9/20/07, Kees Nuyt <[EMAIL PROTECTED]> wrote:
>
> On Thu, 20 Sep 2007 13:57:58 -0400, Liam wrote:
>
> > It would be nice if the documentation gave a
> > brief indication when a feature is added
> > ("new in 3.5.0" unobtrusively somewhere).
>
> Like in http://www.sqlite.org/34to35.html ?
> --
>   (  Kees Nuyt
>   )
> c[_]
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>

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



Re: [sqlite] sqlite3_open_v2 and SQLITE_OPEN_READONLY

2007-09-20 Thread Kees Nuyt

On Thu, 20 Sep 2007 13:57:58 -0400, Liam wrote:

> It would be nice if the documentation gave a
> brief indication when a feature is added
> ("new in 3.5.0" unobtrusively somewhere).

Like in http://www.sqlite.org/34to35.html ?
-- 
  (  Kees Nuyt
  )
c[_]

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



Re: [sqlite] Precompiled 2.x for Windows?

2007-09-20 Thread Kees Nuyt
On Thu, 20 Sep 2007 17:09:27 +, you wrote:

> Would anyone happen to have a precompiled DLL
> for Windows of the latest 2.x version of SQLite?
> I don't have the facilities to make one myself
> where I'm at right now, but I need one to test
> out a problem with an application I'm trying
> to support.  
> Thank you to anyone who might be able to provide this for me.

It's on the bottom of the download page:
http://www.sqlite.org/download.html
-- 
  (  Kees Nuyt
  )
c[_]

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



Re: [sqlite] SQLite and html character entities

2007-09-20 Thread Clark Christensen
It's a nightmare.  I can think of some ugly client-side scripting things to 
investigate, but I think you'd be on the way down a bunny hole :-))

If you submit the data, and do nothing but print it back to the browser, does 
it display correctly?

If you submit, store, retrieve and send it back to the browser, does it display 
correctly?

 -Clark


- Original Message 
From: P Kishor <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Thursday, September 20, 2007 9:12:30 AM
Subject: Re: [sqlite] SQLite and html character entities

On 9/20/07, Clark Christensen <[EMAIL PROTECTED]> wrote:
> The META tag you include looks correct to me.
>
> Does perl get the chars right after CGI decodes them?

This is what I am not completely sure of.

>
> The browser, ultimately, will escape the accented characters into
 urlencoded chars based on the utf-8 charset you specify in the HTML META
  tag.  Then Perl (via CGI) is going to decode those back into
 characters, probably using the host's default charset.  Seems like there's a
 chance for perl to mangle the accented chars during this step.
>

1. The following is one scenario I have experienced:

(a) SQLite has accented data
(b) I retrieve that data and display it in web form, and it show
accented and correctly
(c) I update some text in that form (not necessarily the accented
text) and update the entire record
(d) viewing the same data shows that it has gotten clobbered.

2. I have also experienced the following scenario:

(a) My Cocoa-based text editor writes scripts in utf8. So, stuff that
shows up at accented in the text editor, shows up funky in vim
(b) the same text shows up fine in the browser both ways, to and from
the browser, without any special encoding or decoding
(c) However, if I stick some accented text in a scalar and then
display it in the browser, it shows up clobbered in the browser even
though it shows up fine in the editor (see (a) and (b) above... fine
in the editor, screwed up in vim).
(d) I know (a) is true because if I add "use utf8" to the script then
all is well.

I don't care so much about the 2nd scenario except that my application
itself is in at least 3 languages (for now). That is, the interface
text, not just the data from SQLite, is also in Spanish and
Portuguese. Well, I have created separated templates and used properly
escaped entities in the interface text ( for é and so on) so
the interface text is fine and reliable. I just want to make sure that
nothing is clobbered in the data during its roundtrip from and to the
database, and assuming that the users will update the text with
correct accents instead of with escaped entities (that is, the users
will type é instead of ).

Puneet.


>
> - Original Message 
> From: P Kishor <[EMAIL PROTECTED]>
> To: Nuno Lucas <[EMAIL PROTECTED]>
> Cc: sqlite-users@sqlite.org
> Sent: Thursday, September 20, 2007 6:43:37 AM
> Subject: Re: [sqlite] SQLite and html character entities
>
> Thanks Nuno. Since I am raw in this matter, could I ask you for a
> little more hand-holding as specified below --
>
> On 9/20/07, Nuno Lucas <[EMAIL PROTECTED]> wrote:
> > You have to know the encoding of the user input. To do that, all
 your
> > html forms _MUST_ have proper  tags, and as you will be using
> > SQLite, the obvious encoding choice will be UTF-8 (because that way
> > you don't need to do any conversions when feeding/retrieving data
> > to/from SQLite).
>
> So, what is the proper meta tag? Is the following sufficient?
>
>  "http://www.w3.org/TR/html4/loose.dtd;>
> 
>   
> 
> 
>   
>   
>
> And, other than the above, I don't have to do anything else? Just a
> straight ahead INSERT with bind vars is enough?
>
> Many thanks in advance,
>
> Puneet.
>
>
> >
> > Then there is the problem of non-compliant browsers, but that is
> > another history...
> >
> >
> > Best regards,
> > ~Nuno Lucas
> >
> >
> > On 9/20/07, P Kishor <[EMAIL PROTECTED]> wrote:
> > > Folks,
> > >
> > > I come to ask you a question that may be basic for many of you
 but
>  is
> > > leaving me completely bewildered. My work environment is a Mac OS
 X
> > > (Tiger) computer, and I use a Cocoa-based text editor, and am
>  writing
> > > a Perl-based web app. Data are in several different languages,
> > > predominantly English, but with Portuguese, Spanish, and other
> > > languages mixed in... hence, have accent marks (diacritics).
> > >
> > > Goal: To reliably and consistently show the retrieved data in a
 web
> > > page or a web form with the correct diacritics, and when the user
> > > edits and updates that data, reliably and consistently update the
> > > database.
> > >
> > > Summary of problem: Data with diacritics show up fine in web
 forms,
> > > but on updating, they get clobbered with gibberish and
 subsequently
> > > show up incorrectly.
> > >
> > > So, I decided to do a little test. I created a small table, wrote
 a
> > > script, and inserted a few records from the web. See the output
 of
>  my
> > > 

Re: [sqlite] sqlite3_open_v2 and SQLITE_OPEN_READONLY

2007-09-20 Thread Liam Healy
OK thanks, I'll upgrade when it hits Debian unstable which I expect
will be fairly soon.  It would be nice if the documentation gave a
brief indication when a feature is added ("new in 3.5.0" unobtrusively
somewhere).

Liam

On 9/20/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> "Liam Healy" <[EMAIL PROTECTED]> wrote:
> > I tried to use sqlite3_open_v2 and SQLITE_OPEN_READONLY as described in
> > http://sqlite.org/capi3ref.html#sqlite3_open, but the symbol
> > SQLITE_OPEN_READONLY is unknown, even though I have included
> > sqlite3.h.  I am using version 3.4.2.  Was this symbol not added until
> > a later version, or am I supposed to use the definition given on the
> > web page?
> >
>
> Sqlite3_open_v2() and SQLITE_OPEN_READONLY and a whole bunch
> of other stuff is all new to 3.5.0.  Version 3.5.0 is stable.
> It has lots of cool stuff.  Older versions are not supported
> (except for paying customers) - by which we mean that if any
> bugs are discovered they will be fixed in 3.5.0 only, not
> in branches.  You (and a lot of other people) really need
> to upgrade.
> --
> D. Richard Hipp <[EMAIL PROTECTED]>
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>

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



Re: [sqlite] sqlite3_open_v2 and SQLITE_OPEN_READONLY

2007-09-20 Thread drh
RaghavendraK 70574 <[EMAIL PROTECTED]> wrote:
> Hi,
> 
> Is the performance same,better or 
> 

Perhaps a little better for some special cases.
Otherwise about the same.
--
D. Richard Hipp <[EMAIL PROTECTED]>


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



Re: [sqlite] sqlite3_open_v2 and SQLITE_OPEN_READONLY

2007-09-20 Thread RaghavendraK 70574
Hi,

Is the performance same,better or 

regards
ragha

**
 This email and its attachments contain confidential information from HUAWEI, 
which is intended only for the person or entity whose address is listed above. 
Any use of the information contained herein in any way (including, but not 
limited to, total or partial disclosure, reproduction, or dissemination) by 
persons other than the intended recipient(s) is prohibited. If you receive this 
e-mail in error, please notify the sender by phone or email immediately and 
delete it!
 
*

- Original Message -
From: [EMAIL PROTECTED]
Date: Thursday, September 20, 2007 10:40 pm
Subject: Re: [sqlite] sqlite3_open_v2 and SQLITE_OPEN_READONLY

> "Liam Healy" <[EMAIL PROTECTED]> wrote:
> > I tried to use sqlite3_open_v2 and SQLITE_OPEN_READONLY as 
> described in
> > http://sqlite.org/capi3ref.html#sqlite3_open, but the symbol
> > SQLITE_OPEN_READONLY is unknown, even though I have included
> > sqlite3.h.  I am using version 3.4.2.  Was this symbol not added 
> until> a later version, or am I supposed to use the definition 
> given on the
> > web page?
> > 
> 
> Sqlite3_open_v2() and SQLITE_OPEN_READONLY and a whole bunch
> of other stuff is all new to 3.5.0.  Version 3.5.0 is stable.
> It has lots of cool stuff.  Older versions are not supported
> (except for paying customers) - by which we mean that if any
> bugs are discovered they will be fixed in 3.5.0 only, not
> in branches.  You (and a lot of other people) really need
> to upgrade.
> --
> D. Richard Hipp <[EMAIL PROTECTED]>
> 
> 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> 
> -
> 
> 

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



[sqlite] Re: sqlite3_open_v2 and SQLITE_OPEN_READONLY

2007-09-20 Thread Igor Tandetnik

Liam Healy 
wrote: 

I tried to use sqlite3_open_v2 and SQLITE_OPEN_READONLY as described
in http://sqlite.org/capi3ref.html#sqlite3_open, but the symbol
SQLITE_OPEN_READONLY is unknown, even though I have included
sqlite3.h.  I am using version 3.4.2.


sqlite3_open_v2 is new in 3.5.0. See http://sqlite.org/changes.html

Igor Tandetnik

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



Re: [sqlite] sqlite3_open_v2 and SQLITE_OPEN_READONLY

2007-09-20 Thread drh
"Liam Healy" <[EMAIL PROTECTED]> wrote:
> I tried to use sqlite3_open_v2 and SQLITE_OPEN_READONLY as described in
> http://sqlite.org/capi3ref.html#sqlite3_open, but the symbol
> SQLITE_OPEN_READONLY is unknown, even though I have included
> sqlite3.h.  I am using version 3.4.2.  Was this symbol not added until
> a later version, or am I supposed to use the definition given on the
> web page?
> 

Sqlite3_open_v2() and SQLITE_OPEN_READONLY and a whole bunch
of other stuff is all new to 3.5.0.  Version 3.5.0 is stable.
It has lots of cool stuff.  Older versions are not supported
(except for paying customers) - by which we mean that if any
bugs are discovered they will be fixed in 3.5.0 only, not
in branches.  You (and a lot of other people) really need
to upgrade.
--
D. Richard Hipp <[EMAIL PROTECTED]>


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



[sqlite] Precompiled 2.x for Windows?

2007-09-20 Thread epankoke
Would anyone happen to have a precompiled DLL for Windows of the latest 2.x 
version of SQLite?  I don't have the facilities to make one myself where I'm at 
right now, but I need one to test out a problem with an application I'm trying 
to support.  Thank you to anyone who might be able to provide this for me.

--
Eric Pankoke
Founder / Lead Developer
Point Of Light Software
http://www.polsoftware.com/

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



[sqlite] sqlite3_open_v2 and SQLITE_OPEN_READONLY

2007-09-20 Thread Liam Healy
I tried to use sqlite3_open_v2 and SQLITE_OPEN_READONLY as described in
http://sqlite.org/capi3ref.html#sqlite3_open, but the symbol
SQLITE_OPEN_READONLY is unknown, even though I have included
sqlite3.h.  I am using version 3.4.2.  Was this symbol not added until
a later version, or am I supposed to use the definition given on the
web page?

Thanks

Liam

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



Re: [sqlite] SQLite and html character entities

2007-09-20 Thread P Kishor
On 9/20/07, Clark Christensen <[EMAIL PROTECTED]> wrote:
> The META tag you include looks correct to me.
>
> Does perl get the chars right after CGI decodes them?

This is what I am not completely sure of.

>
> The browser, ultimately, will escape the accented characters into urlencoded 
> chars based on the utf-8 charset you specify in the HTML META  tag.  Then 
> Perl (via CGI) is going to decode those back into characters, probably using 
> the host's default charset.  Seems like there's a chance for perl to mangle 
> the accented chars during this step.
>

1. The following is one scenario I have experienced:

(a) SQLite has accented data
(b) I retrieve that data and display it in web form, and it show
accented and correctly
(c) I update some text in that form (not necessarily the accented
text) and update the entire record
(d) viewing the same data shows that it has gotten clobbered.

2. I have also experienced the following scenario:

(a) My Cocoa-based text editor writes scripts in utf8. So, stuff that
shows up at accented in the text editor, shows up funky in vim
(b) the same text shows up fine in the browser both ways, to and from
the browser, without any special encoding or decoding
(c) However, if I stick some accented text in a scalar and then
display it in the browser, it shows up clobbered in the browser even
though it shows up fine in the editor (see (a) and (b) above... fine
in the editor, screwed up in vim).
(d) I know (a) is true because if I add "use utf8" to the script then
all is well.

I don't care so much about the 2nd scenario except that my application
itself is in at least 3 languages (for now). That is, the interface
text, not just the data from SQLite, is also in Spanish and
Portuguese. Well, I have created separated templates and used properly
escaped entities in the interface text ( for é and so on) so
the interface text is fine and reliable. I just want to make sure that
nothing is clobbered in the data during its roundtrip from and to the
database, and assuming that the users will update the text with
correct accents instead of with escaped entities (that is, the users
will type é instead of ).

Puneet.


>
> - Original Message 
> From: P Kishor <[EMAIL PROTECTED]>
> To: Nuno Lucas <[EMAIL PROTECTED]>
> Cc: sqlite-users@sqlite.org
> Sent: Thursday, September 20, 2007 6:43:37 AM
> Subject: Re: [sqlite] SQLite and html character entities
>
> Thanks Nuno. Since I am raw in this matter, could I ask you for a
> little more hand-holding as specified below --
>
> On 9/20/07, Nuno Lucas <[EMAIL PROTECTED]> wrote:
> > You have to know the encoding of the user input. To do that, all your
> > html forms _MUST_ have proper  tags, and as you will be using
> > SQLite, the obvious encoding choice will be UTF-8 (because that way
> > you don't need to do any conversions when feeding/retrieving data
> > to/from SQLite).
>
> So, what is the proper meta tag? Is the following sufficient?
>
>  "http://www.w3.org/TR/html4/loose.dtd;>
> 
>   
> 
> 
>   
>   
>
> And, other than the above, I don't have to do anything else? Just a
> straight ahead INSERT with bind vars is enough?
>
> Many thanks in advance,
>
> Puneet.
>
>
> >
> > Then there is the problem of non-compliant browsers, but that is
> > another history...
> >
> >
> > Best regards,
> > ~Nuno Lucas
> >
> >
> > On 9/20/07, P Kishor <[EMAIL PROTECTED]> wrote:
> > > Folks,
> > >
> > > I come to ask you a question that may be basic for many of you but
>  is
> > > leaving me completely bewildered. My work environment is a Mac OS X
> > > (Tiger) computer, and I use a Cocoa-based text editor, and am
>  writing
> > > a Perl-based web app. Data are in several different languages,
> > > predominantly English, but with Portuguese, Spanish, and other
> > > languages mixed in... hence, have accent marks (diacritics).
> > >
> > > Goal: To reliably and consistently show the retrieved data in a web
> > > page or a web form with the correct diacritics, and when the user
> > > edits and updates that data, reliably and consistently update the
> > > database.
> > >
> > > Summary of problem: Data with diacritics show up fine in web forms,
> > > but on updating, they get clobbered with gibberish and subsequently
> > > show up incorrectly.
> > >
> > > So, I decided to do a little test. I created a small table, wrote a
> > > script, and inserted a few records from the web. See the output of
>  my
> > > investigation below. I ask you, what is it that I have to do to
> > > achieve my goal above? (output of test follows; I have separated
> > > logical sections with a "---" line, and my comments start with
>  #)
> > >
> > > Lucknow:~/Data/ecoservices punkish$ sqlite3 entities.sqlite
> > > SQLite version 3.3.8
> > > Enter ".help" for instructions
> > > sqlite> .s
> > > CREATE TABLE tbl (a text);
> > > sqlite> select * from tbl;
> > > the first record
> > > é ç ñ î
> > > more from 3rd row
> > > row four
> > > these "volunteered" activities
> > > 

Re: [sqlite] SQLite and html character entities

2007-09-20 Thread Clark Christensen
The META tag you include looks correct to me.

Does perl get the chars right after CGI decodes them?

The browser, ultimately, will escape the accented characters into urlencoded 
chars based on the utf-8 charset you specify in the HTML META  tag.  Then Perl 
(via CGI) is going to decode those back into characters, probably using the 
host's default charset.  Seems like there's a chance for perl to mangle the 
accented chars during this step.

 -Clark

- Original Message 
From: P Kishor <[EMAIL PROTECTED]>
To: Nuno Lucas <[EMAIL PROTECTED]>
Cc: sqlite-users@sqlite.org
Sent: Thursday, September 20, 2007 6:43:37 AM
Subject: Re: [sqlite] SQLite and html character entities

Thanks Nuno. Since I am raw in this matter, could I ask you for a
little more hand-holding as specified below --

On 9/20/07, Nuno Lucas <[EMAIL PROTECTED]> wrote:
> You have to know the encoding of the user input. To do that, all your
> html forms _MUST_ have proper  tags, and as you will be using
> SQLite, the obvious encoding choice will be UTF-8 (because that way
> you don't need to do any conversions when feeding/retrieving data
> to/from SQLite).

So, what is the proper meta tag? Is the following sufficient?

http://www.w3.org/TR/html4/loose.dtd;>

  


  
  

And, other than the above, I don't have to do anything else? Just a
straight ahead INSERT with bind vars is enough?

Many thanks in advance,

Puneet.


>
> Then there is the problem of non-compliant browsers, but that is
> another history...
>
>
> Best regards,
> ~Nuno Lucas
>
>
> On 9/20/07, P Kishor <[EMAIL PROTECTED]> wrote:
> > Folks,
> >
> > I come to ask you a question that may be basic for many of you but
 is
> > leaving me completely bewildered. My work environment is a Mac OS X
> > (Tiger) computer, and I use a Cocoa-based text editor, and am
 writing
> > a Perl-based web app. Data are in several different languages,
> > predominantly English, but with Portuguese, Spanish, and other
> > languages mixed in... hence, have accent marks (diacritics).
> >
> > Goal: To reliably and consistently show the retrieved data in a web
> > page or a web form with the correct diacritics, and when the user
> > edits and updates that data, reliably and consistently update the
> > database.
> >
> > Summary of problem: Data with diacritics show up fine in web forms,
> > but on updating, they get clobbered with gibberish and subsequently
> > show up incorrectly.
> >
> > So, I decided to do a little test. I created a small table, wrote a
> > script, and inserted a few records from the web. See the output of
 my
> > investigation below. I ask you, what is it that I have to do to
> > achieve my goal above? (output of test follows; I have separated
> > logical sections with a "---" line, and my comments start with
 #)
> >
> > Lucknow:~/Data/ecoservices punkish$ sqlite3 entities.sqlite
> > SQLite version 3.3.8
> > Enter ".help" for instructions
> > sqlite> .s
> > CREATE TABLE tbl (a text);
> > sqlite> select * from tbl;
> > the first record
> > é ç ñ î
> > more from 3rd row
> > row four
> > these "volunteered" activities
> > <á ø ã ü î & others>
> > -
> > sqlite> .mode csv
> > sqlite> .output foo.csv
> > sqlite> select * from tbl;
> > sqlite> .q
> > Lucknow:~/Data/ecoservices punkish$ less foo.csv
> > "the first record"
> > "\351 \347 \361 \356"
> > "more from 3rd row"
> > "row four"
> > "these \223volunteered\224 activities"
> > "<\341 \370 \343 \374 \356 & others>"
> > foo.csv (END)
> > -
> > sqlite> .mode html
> > sqlite> .output foo.html
> > sqlite> select * from tbl;
> > sqlite> .q
> > Lucknow:~/Data/ecoservices punkish$ less foo.html
> > "foo.html" may be a binary file.  See it anyway?
> > the first record
> > 
> >
> > 
> > more from 3rd row
> > 
> > row four
> > 
> > these <93>volunteered<94> activities
> > 
> >   others>
> > 
> > foo.html (END)
> > -
> > # below foo.html in my Cocoa-based text editor
> > the first record
> > 
> > È Á Ò Ó
> > 
> > more from 3rd row
> > 
> > row four
> > 
> > these ìvolunteeredî activities
> > 
> > · ¯ „ ¸ Ó  others>
> > 
> > -
> > # below foo.html in Safari; I added  tags to format
 correctly
> > the first record
> > é ç ñ î
> > more from 3rd row
> > row four
> > these "volunteered" activities
> > <á ø ã ü î & others>
> >
> >
 -
> > To unsubscribe, send email to [EMAIL PROTECTED]
> >
 -
> >
> >
>


-- 
Puneet Kishor
http://punkish.eidesis.org/
Nelson Institute for Environmental Studies
http://www.nelson.wisc.edu/
Open Source Geospatial Foundation (OSGeo)
http://www.osgeo.org/
Summer 2007 S Policy Fellow, The National Academies
http://www.nas.edu/

-
To unsubscribe, send email to [EMAIL 

RE: [sqlite] SQLite.Net

2007-09-20 Thread Samuel R. Neff

Funny, the first "bug" you pointed out is a question I asked which was
answered same day and was a report of an inconsistency with how
System.Data.SQLite tries to map strong typing on top of SQLite.  Certainly
wouldn't call it big issue and I worked around it in just as much time as it
took to post the question (the post was only to point out the
inconsistency).

Anyways, we're getting a bit off topic and from your earlier post I think we
both agree on recommending System.Data.SQLite which was the important part.

Best regards,

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: Yves Goergen [mailto:[EMAIL PROTECTED] 
Sent: Thursday, September 20, 2007 9:31 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] SQLite.Net

On 20.09.2007 15:06 CE(S)T, Samuel R. Neff wrote:
> can you be more specific?  Thread links.. 

http://sqlite.phxsoftware.com/forums/t/731.aspx
"SQLiteDataReader.GetValue() not returning DateTime"

http://sqlite.phxsoftware.com/forums/t/795.aspx
"Cannot retrieve data from a column; inconsistent results"

Another bug I reported was indeed replied quickly, but the forum didn't
notify me although specified. (I'll now do what the reply says...) So
the new forum software has other bugs, too.

http://sqlite.phxsoftware.com/forums/t/866.aspx
"Something's not cleaned up correctly"


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



Re: [sqlite] Group by without aggregates

2007-09-20 Thread Joe Wilson
> How does SQLite choose which row to return from a group?

http://www.mail-archive.com/sqlite-users@sqlite.org/msg17778.html



   

Take the Internet to Go: Yahoo!Go puts the Internet in your pocket: mail, news, 
photos & more. 
http://mobile.yahoo.com/go?refer=1GNXIC

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



[sqlite] Re: Group by without aggregates

2007-09-20 Thread Igor Tandetnik

Prakash Reddy Bande 
wrote:

select SMD_cid, uname, version from users group by version;

SMD_cid|uname|version

66c6263d-e792-f94e-8f57-fd49c2e7b6a6|auser|0

b0b520e9-54b3-224d-8183-09ef08501451|auser|1



I was excepting an error saying columns SMD_cid and uname are not
aggregate. But the query did give some results.

How does SQLite choose which row to return from a group?


Essentially at random, in an unpredictable way. Whichever row falls 
naturally out of its traversal algorithm. Why do you care?


Igor Tandetnik 



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



Re: [sqlite] Moving database connections across threads

2007-09-20 Thread John Stanton
By making THREADSAFE=0 you have not affected the locking, just other 
threadsafe capabilities.  If you are launching multiple threads I would 
make THREADSAFE=1 so that you can be certain that thread safe versions 
of library routines are used.


As I see it Sqlite works around POSIX thread restrictions by associating 
a connection with a thread so that the process-wide POSIX locks can be 
handled by multiple threads.


Our simple way of avoiding these issues was to replace fcntl with an 
dummy macro so that it no-ops.  Currently we are reworking the os_xxx.c 
files to produce a version which uses locks constructed from mutexes, 
semaphores and events depending upon the use of Unix or Windows.  The 
need to make more elaborate locks is to implement the Sqlite RESERVED 
lock which gives better concurrency by splitting a write lock into a 
lock on the journal and a lock on writing the journal to the database file.


We have an Sqlite-based application server which benefits from having 
optimal locking for maximum productivity.  Using locking based on the 
POSIX model in this multi-threaded application is detrimental, 
particularly when there are much more appropriate synchronization 
primitives available.


In an earlier workstation-based multi-threaded application we found it
perfectly adequate to just place a mutex around the Sqlite calls to 
synchronize access and avoid the dreaded BUSY.


Incidentally the removal of the POSIX locks makes for a version of 
Sqlite more appropriate to embedded use by lessening file system 
implementation requirements, simplifying application logic and improving 
performance.


Mark Brown wrote:

Thanks, John, for your reply.  We also had the same idea about wanting to
avoid the SQLITE_BUSY possibilities, and just have callers wait.

So, I think if I understand your response correctly, by having THREADSAFE=0
we have effectively commented out any code that would be trying to use
fcntrl(), and hence, we should have no problem (from a SQLite point of view)
of having multiple threads use the same connection, as long as we are
correctly controlling access to this connection with our own mutexes.

Is that right?

Thanks,
Mark



-Original Message-
From: John Stanton [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, September 19, 2007 6:54 PM

To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Moving database connections across threads


The underlying issue with thread misbehaviour is that POSIX 
file locks 
are process specific, not thread specific requiring some 
tormented logic 
.  You only need the POSIX locks if you have multiple access 
to the same 
database and are writing to the database.


If you have a multi-threaded application and synchronize access using 
pthread rwlocks or on Windows read/write locks made from a mutex and 
event or semaphore then you can ignore the POSIX locks.  If 
your traffic 
rate is not very high you can just use a mutex around the 
Sqlite calls 
for synchronization.


In our threaded applications we use either rwlocks or a mutex 
and have 
the bonus of not having to add application logic to handle a 
BUSY state 
and endure busy waits and other polling indignities since the threads 
just block when there is contention.


If you have network connected files (like SMB or NFS) or multiple 
processes on one machine you will need the POSIX locks.







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




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



[sqlite] Group by without aggregates

2007-09-20 Thread Prakash Reddy Bande
Hi,

 

I have the following table:

 

CREATE TABLE users (SMD_cid varchar(50), uname varchar(50), version
integer);

 

select SMD_cid, uname, version from users;

2a946e78-0659-4746-aab7-6486ae9a26d3|auser|1

b0b520e9-54b3-224d-8183-09ef08501451|auser|1

9272f455-2c2a-7e4d-a140-c30fac34ab09|auser|0

66c6263d-e792-f94e-8f57-fd49c2e7b6a6|auser|0

 

select SMD_cid, uname, version from users group by version;

SMD_cid|uname|version

66c6263d-e792-f94e-8f57-fd49c2e7b6a6|auser|0

b0b520e9-54b3-224d-8183-09ef08501451|auser|1

 

I was excepting an error saying columns SMD_cid and uname are not
aggregate. But the query did give some results.

How does SQLite choose which row to return from a group?

 

Regards,

 

Prakash Reddy Bande

Altair Engg. Inc,

Troy, MI

 



[sqlite] In-Memory Database - problem with DROP TABLE

2007-09-20 Thread Babu, Lokesh
Dear All,

I have a piece of code which is shown below, Im doing In-Memory database
operation

I have done these settings in SQLite, in btree.h SQLITE_DEFAULT_AUTOVACUUM =
1, TEMP_STORE=2 in sqliteint.h (this should be made compulsory, if
concentrating only on in-memorydatabase, where no disk is available)

Now the problems I'm facing
---
1) Last two queries, i.e., DROP TABLE query, since AUTOVACUUM is on
(overriding pBt->autoVacuum=SQLITE_DEFAULT_AUTOVACUUM in Btree.c in
sqlite3BtreeOpen function), while dropping the table, the memory increases
double the original table occupied. I have observed at sqlite3BtreeClearTable
in btree.c. why is this?
2) I'm afraid, If I do the DROP TABLE in sequence i.e., how I created my
tables, memory doesn't get deleted. It is expecting stack basis. ie., if I
DROP recently created TABLE then it works(memory frees perfectly). why is
this unexpected behaviour? If you see my code below, as it is code doesn't
work for DROP (memory is freed only for the last TABLE). If the two queries
are interchanged, memory free happens correctly.

any reply's are welcome.

Thanks in advance.


#define SCHEMA_TWO_USED
#ifdef SCHEMA_TWO_USED

static char *database_name = ":memory:";
static sqlite3* db_handle;
static char queryString[1024];

#define PRINT_TIME \
{ \
unsigned long millisec = clock(); \
printf("milliseconds = %ld\n", millisec); \
}

static const char *SchemaTwo[] = {
"CREATE TABLE testTbl_1 (t_id INTEGER, t_id2 INTEGER, t_name TEXT,
t_desc TEXT, PRIMARY KEY (t_id2));",

"CREATE TABLE testTbl_2 (t_id INTEGER, t_id2 INTEGER, t_name TEXT,
t_desc TEXT, PRIMARY KEY (t_id2));",

"INSERT INTO testTbl_1 (t_id,t_id2,t_name,t_desc) VALUES
(%d,%d,'%s','%s');",

"INSERT INTO testTbl_2 (t_id,t_id2,t_name,t_desc) VALUES
(%d,%d,'%s','%s');",
};

static int callback(void *NotUsed, int argc, char **argv, char **azColName)
{
int i;
for (i = 0; i < argc; i++)
{
printf("%s  ", argv[i]);
}
printf("\n");
return 0;
}

int main(int argc, char *argv[])
{
char *zErrMsg = NULL;
int status, i = 0;

/* open the database */
status = sqlite3_open(database_name, _handle);
if (status)
{
printf("%d", status);
return 0;
}

/* Create Table 1 */
PRINT_TIME
status = sqlite3_exec(db_handle, SchemaTwo[0], NULL, 0, );
if (SQLITE_OK == status)
{
char name[30];
char desc[50];

for (i = 0; i < 1 && status == SQLITE_OK; i++)
{
sprintf(name,"TableName""%d", i);
sprintf(desc,"Moves the selected control or dialog down""%d",
i);
sprintf(queryString,
SchemaTwo[2], i, i+125436, name, desc);
status = sqlite3_exec(db_handle, queryString, NULL, 0,
);
}
}
PRINT_TIME
/* Create Table 2 */
status = sqlite3_exec(db_handle, SchemaTwo[1], NULL, 0, );
if (SQLITE_OK == status)
{
char name[30];
char desc[50];

for (i = 0; i < 1 && status == SQLITE_OK; i++)
{
sprintf(name,"TableName""%d", i);
sprintf(desc,"Moves the selected control or dialog down""%d",
i);
sprintf(queryString,
SchemaTwo[3], i, i+125436, name, desc);
status = sqlite3_exec(db_handle, queryString, NULL, 0,
);
}
}
PRINT_TIME

sprintf(queryString,
"SELECT * FROM sqlite_master;");
status = sqlite3_exec(db_handle, queryString, callback, 0, );

printf("Before Start of SELECT *: "); PRINT_TIME
sprintf(queryString,
"SELECT * FROM testTbl_1;");
status = sqlite3_exec(db_handle, queryString, callback, 0, );
printf("After Complete of SELECT *: "); PRINT_TIME

sprintf(queryString,
"DROP TABLE IF EXISTS testTbl_1;");
status = sqlite3_exec(db_handle, queryString, NULL, NULL, NULL);

sprintf(queryString,
"DROP TABLE IF EXISTS testTbl_2;");
status = sqlite3_exec(db_handle, queryString, NULL, NULL, NULL);

return 0;
}
#endif /* SCHEMA_TWO_USED */


Re: [sqlite] SQLite and html character entities

2007-09-20 Thread P Kishor
Thanks Nuno. Since I am raw in this matter, could I ask you for a
little more hand-holding as specified below --

On 9/20/07, Nuno Lucas <[EMAIL PROTECTED]> wrote:
> You have to know the encoding of the user input. To do that, all your
> html forms _MUST_ have proper  tags, and as you will be using
> SQLite, the obvious encoding choice will be UTF-8 (because that way
> you don't need to do any conversions when feeding/retrieving data
> to/from SQLite).

So, what is the proper meta tag? Is the following sufficient?

http://www.w3.org/TR/html4/loose.dtd;>

  


  
  

And, other than the above, I don't have to do anything else? Just a
straight ahead INSERT with bind vars is enough?

Many thanks in advance,

Puneet.


>
> Then there is the problem of non-compliant browsers, but that is
> another history...
>
>
> Best regards,
> ~Nuno Lucas
>
>
> On 9/20/07, P Kishor <[EMAIL PROTECTED]> wrote:
> > Folks,
> >
> > I come to ask you a question that may be basic for many of you but is
> > leaving me completely bewildered. My work environment is a Mac OS X
> > (Tiger) computer, and I use a Cocoa-based text editor, and am writing
> > a Perl-based web app. Data are in several different languages,
> > predominantly English, but with Portuguese, Spanish, and other
> > languages mixed in... hence, have accent marks (diacritics).
> >
> > Goal: To reliably and consistently show the retrieved data in a web
> > page or a web form with the correct diacritics, and when the user
> > edits and updates that data, reliably and consistently update the
> > database.
> >
> > Summary of problem: Data with diacritics show up fine in web forms,
> > but on updating, they get clobbered with gibberish and subsequently
> > show up incorrectly.
> >
> > So, I decided to do a little test. I created a small table, wrote a
> > script, and inserted a few records from the web. See the output of my
> > investigation below. I ask you, what is it that I have to do to
> > achieve my goal above? (output of test follows; I have separated
> > logical sections with a "---" line, and my comments start with #)
> >
> > Lucknow:~/Data/ecoservices punkish$ sqlite3 entities.sqlite
> > SQLite version 3.3.8
> > Enter ".help" for instructions
> > sqlite> .s
> > CREATE TABLE tbl (a text);
> > sqlite> select * from tbl;
> > the first record
> > é ç ñ î
> > more from 3rd row
> > row four
> > these "volunteered" activities
> > <á ø ã ü î & others>
> > -
> > sqlite> .mode csv
> > sqlite> .output foo.csv
> > sqlite> select * from tbl;
> > sqlite> .q
> > Lucknow:~/Data/ecoservices punkish$ less foo.csv
> > "the first record"
> > "\351 \347 \361 \356"
> > "more from 3rd row"
> > "row four"
> > "these \223volunteered\224 activities"
> > "<\341 \370 \343 \374 \356 & others>"
> > foo.csv (END)
> > -
> > sqlite> .mode html
> > sqlite> .output foo.html
> > sqlite> select * from tbl;
> > sqlite> .q
> > Lucknow:~/Data/ecoservices punkish$ less foo.html
> > "foo.html" may be a binary file.  See it anyway?
> > the first record
> > 
> >
> > 
> > more from 3rd row
> > 
> > row four
> > 
> > these <93>volunteered<94> activities
> > 
> >   others>
> > 
> > foo.html (END)
> > -
> > # below foo.html in my Cocoa-based text editor
> > the first record
> > 
> > È Á Ò Ó
> > 
> > more from 3rd row
> > 
> > row four
> > 
> > these ìvolunteeredî activities
> > 
> > · ¯ „ ¸ Ó  others>
> > 
> > -
> > # below foo.html in Safari; I added  tags to format correctly
> > the first record
> > é ç ñ î
> > more from 3rd row
> > row four
> > these "volunteered" activities
> > <á ø ã ü î & others>
> >
> > -
> > To unsubscribe, send email to [EMAIL PROTECTED]
> > -
> >
> >
>


-- 
Puneet Kishor
http://punkish.eidesis.org/
Nelson Institute for Environmental Studies
http://www.nelson.wisc.edu/
Open Source Geospatial Foundation (OSGeo)
http://www.osgeo.org/
Summer 2007 S Policy Fellow, The National Academies
http://www.nas.edu/

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



Re: [sqlite] SQLite.Net

2007-09-20 Thread Yves Goergen
On 20.09.2007 15:06 CE(S)T, Samuel R. Neff wrote:
> can you be more specific?  Thread links.. 

http://sqlite.phxsoftware.com/forums/t/731.aspx
"SQLiteDataReader.GetValue() not returning DateTime"

http://sqlite.phxsoftware.com/forums/t/795.aspx
"Cannot retrieve data from a column; inconsistent results"

Another bug I reported was indeed replied quickly, but the forum didn't
notify me although specified. (I'll now do what the reply says...) So
the new forum software has other bugs, too.

http://sqlite.phxsoftware.com/forums/t/866.aspx
"Something's not cleaned up correctly"

-- 
Yves Goergen "LonelyPixel" <[EMAIL PROTECTED]>
Visit my web laboratory at http://beta.unclassified.de

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



RE: [sqlite] Moving database connections across threads

2007-09-20 Thread Mark Brown
Thanks, John, for your reply.  We also had the same idea about wanting to
avoid the SQLITE_BUSY possibilities, and just have callers wait.

So, I think if I understand your response correctly, by having THREADSAFE=0
we have effectively commented out any code that would be trying to use
fcntrl(), and hence, we should have no problem (from a SQLite point of view)
of having multiple threads use the same connection, as long as we are
correctly controlling access to this connection with our own mutexes.

Is that right?

Thanks,
Mark

> -Original Message-
> From: John Stanton [mailto:[EMAIL PROTECTED] 
> Sent: Wednesday, September 19, 2007 6:54 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Moving database connections across threads
> 
> 
> The underlying issue with thread misbehaviour is that POSIX 
> file locks 
> are process specific, not thread specific requiring some 
> tormented logic 
> .  You only need the POSIX locks if you have multiple access 
> to the same 
> database and are writing to the database.
> 
> If you have a multi-threaded application and synchronize access using 
> pthread rwlocks or on Windows read/write locks made from a mutex and 
> event or semaphore then you can ignore the POSIX locks.  If 
> your traffic 
> rate is not very high you can just use a mutex around the 
> Sqlite calls 
> for synchronization.
> 
> In our threaded applications we use either rwlocks or a mutex 
> and have 
> the bonus of not having to add application logic to handle a 
> BUSY state 
> and endure busy waits and other polling indignities since the threads 
> just block when there is contention.
> 
> If you have network connected files (like SMB or NFS) or multiple 
> processes on one machine you will need the POSIX locks.
> 



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



[sqlite] SQLite and html character entities

2007-09-20 Thread P Kishor
Folks,

I come to ask you a question that may be basic for many of you but is
leaving me completely bewildered. My work environment is a Mac OS X
(Tiger) computer, and I use a Cocoa-based text editor, and am writing
a Perl-based web app. Data are in several different languages,
predominantly English, but with Portuguese, Spanish, and other
languages mixed in... hence, have accent marks (diacritics).

Goal: To reliably and consistently show the retrieved data in a web
page or a web form with the correct diacritics, and when the user
edits and updates that data, reliably and consistently update the
database.

Summary of problem: Data with diacritics show up fine in web forms,
but on updating, they get clobbered with gibberish and subsequently
show up incorrectly.

So, I decided to do a little test. I created a small table, wrote a
script, and inserted a few records from the web. See the output of my
investigation below. I ask you, what is it that I have to do to
achieve my goal above? (output of test follows; I have separated
logical sections with a "---" line, and my comments start with #)

Lucknow:~/Data/ecoservices punkish$ sqlite3 entities.sqlite
SQLite version 3.3.8
Enter ".help" for instructions
sqlite> .s
CREATE TABLE tbl (a text);
sqlite> select * from tbl;
the first record
é ç ñ î
more from 3rd row
row four
these "volunteered" activities
<á ø ã ü î & others>
-
sqlite> .mode csv
sqlite> .output foo.csv
sqlite> select * from tbl;
sqlite> .q
Lucknow:~/Data/ecoservices punkish$ less foo.csv
"the first record"
"\351 \347 \361 \356"
"more from 3rd row"
"row four"
"these \223volunteered\224 activities"
"<\341 \370 \343 \374 \356 & others>"
foo.csv (END)
-
sqlite> .mode html
sqlite> .output foo.html
sqlite> select * from tbl;
sqlite> .q
Lucknow:~/Data/ecoservices punkish$ less foo.html
"foo.html" may be a binary file.  See it anyway?
the first record

   

more from 3rd row

row four

these <93>volunteered<94> activities

  others>

foo.html (END)
-
# below foo.html in my Cocoa-based text editor
the first record

È Á Ò Ó

more from 3rd row

row four

these ìvolunteeredî activities

· ¯ „ ¸ Ó  others>

-
# below foo.html in Safari; I added  tags to format correctly
the first record
é ç ñ î
more from 3rd row
row four
these "volunteered" activities
<á ø ã ü î & others>

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



RE: [sqlite] SQLite.Net

2007-09-20 Thread Samuel R. Neff

Reports of two bugs would not have any bearing on my assertion of the
quality of SQLite.Data.NET, but can you be more specific?  Thread links.. 

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: Yves Goergen [mailto:[EMAIL PROTECTED] 
Sent: Thursday, September 20, 2007 8:34 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] SQLite.Net

On 19.09.2007 17:54 CE(S)T, Samuel R. Neff wrote:
> However, I do use the one Robert suggested heavily
> and can attest to the fact that it is extremely well written and works
> without errors.

Ehm, wait, I know of two bugs that have been reported in their forum and
were not even replied to for weeks. The one bug should have been fixed,
but isn't. But these errors are not critical and can be worked around in
the application code (probably decreasing the performance).

Still, this is my recommendation for using SQLite in .NET.


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



Re: [sqlite] SQLite.Net

2007-09-20 Thread Yves Goergen
On 19.09.2007 17:54 CE(S)T, Samuel R. Neff wrote:
> However, I do use the one Robert suggested heavily
> and can attest to the fact that it is extremely well written and works
> without errors.

Ehm, wait, I know of two bugs that have been reported in their forum and
were not even replied to for weeks. The one bug should have been fixed,
but isn't. But these errors are not critical and can be worked around in
the application code (probably decreasing the performance).

Still, this is my recommendation for using SQLite in .NET.

-- 
Yves Goergen "LonelyPixel" <[EMAIL PROTECTED]>
Visit my web laboratory at http://beta.unclassified.de

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



Re: [sqlite] Formatting numbers

2007-09-20 Thread Kees Nuyt
On Thu, 20 Sep 2007 17:41:54 +1000, you wrote:

>Hi All,
>
>What's the best way to format a number with a fixed
>number of decimal places?
>
>For instance, if I have an Amount that I want to 
>format as dollars and cents, I'm currently using:
>
>begin immediate;
>create temporary table Invoice( Amount real );
>insert into Invoice values( 123.4 );
>select '$' || cast( Amount as integer ) || '.'
>   || substr( cast( Amount * 100 + 100 as integer ), -2, 2 )
>from Invoice;
>rollback;
>
>which gives:
>
>$123.40
>
>Is there a better way? I can't see any number formatting function in  
>SQLite's repertoire.

Round comes closest, but is not exacly what you need.
select '$' || round( Amount ,2) from Invoice;

Two remarks:
Formatting and presentation is usually considered a task of the
host language, not of SQL.
Valuta are best stored in integers (as cents).

>Thanks,
>Tom
-- 
  (  Kees Nuyt
  )
c[_]

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



Re: [sqlite] multiple databases

2007-09-20 Thread nadiap

Thanky Mr. Kees!
This help a lot. This is the way i was thinking too.
I have lot things to do though, i am still learning the sqlite!
Thank you once again!

-- 
View this message in context: 
http://www.nabble.com/multiple-databases-tf4480719.html#a12794210
Sent from the SQLite mailing list archive at Nabble.com.


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



Re: [sqlite] DBIException: The name 'bdate' is not a valid index.

2007-09-20 Thread Kees Nuyt
On Thu, 20 Sep 2007 00:53:01 -0400, you wrote:

>
>Greetings!
>
>I have this db containing this table:
>
>CREATE TABLE LSOpenJobs
>(
> id integer primary key, ProjID integer, subProjID integer, parent, 
>children, login, cust, proj, PClass, PSubClass, bdate, ddate, edate, pm, 
>pmuk, lang, vendor, vEmail, invoice, ProjFund, PMTime, A_No, wDir, BiliDir, 
>TMDir, DeliveryDir, paid, paidDate, notes, status, pages, ta
>);
>
>I am trying to make a report and I am calling this select statement,
>
> select ProjID, cust, proj, bdate, ddate, edate, sum(invoice), sum(ProjFund)
>  from LSOpenJobs where ProjID = 423 AND PClass!='Quote' group by ProjID;
>
>There are more than one record where ProjID = 423 and PClass != "Quote". 
>But when I run the program, I get,
>
>DBIException: The name 'invoice' is not a valid index.

[...]

>Any help would be greatly appreciated.

Does it work when you use those queries in the sqlite3 command
line utility? My guess is that there's something wrong with the
quoting of the query string you offer to your wrapper.
Another guess is that try to sum strings, bcz the colums aren't
defined as numeric in the table definition.

>thanks,
>
>josé 

Hope this helps,
-- 
  (  Kees Nuyt
  )
c[_]

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



Re: [sqlite] multiple databases

2007-09-20 Thread Kees Nuyt
On Thu, 20 Sep 2007 02:03:59 -0700 (PDT), you wrote:

>
>Hello again!
>I was really surprised and relieved to see that so many people replied to my
>question! I really thank you all!
>I would love to come to the conference but i am from Greece.
>I am going to explain in details what i am trying to do. 
>I am indeed using the network simulator 2 in order to simulate a wireless
>network of nodes. I want each node to have its own database which will NOT
>be a copy of a local db. Each database will be different and independent for
>each node. This is and my basic worry. Can i do that?
>Additionally, each node will send some messages to other nodes. These
>messages will trigger some 'actions' to the local dbs. Let's say that one
>node nA wants to make a query to the db of the node nB. Then nA sends a
>serialized message (which contain the query) to the node nB and then node nB
>deserialises the received message and 'reads' the query and access its
>database. This is what i want to simulate. So, can this be done? 
>Can i attach so many different dbs as the nodes are?
>I would prefer to do it through c++ and not through Tcl. 
>Thank you so much!

Assuming each node is an object (class instance) in the
simulation program it should be possible to instantiate a
database connection (open) in the constructor of the node class
and cose it in the destructor. The constructor should check if
the database has been initialized before, and if not, define a
proper schema.
The class methods for receiving messages could contain sqlite3
calls that use the database in some way.
You have to take care that every database should have a unique
filename, and that a specific node in the simulated network uses
the same name all the time.

I hope this helps, good luck.
-- 
  (  Kees Nuyt
  )
c[_]

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



Re: [sqlite] multiple databases

2007-09-20 Thread nadiap

Hello again!
I was really surprised and relieved to see that so many people replied to my
question! I really thank you all!
I would love to come to the conference but i am from Greece.
I am going to explain in details what i am trying to do. 
I am indeed using the network simulator 2 in order to simulate a wireless
network of nodes. I want each node to have its own database which will NOT
be a copy of a local db. Each database will be different and independent for
each node. This is and my basic worry. Can i do that?
Additionally, each node will send some messages to other nodes. These
messages will trigger some 'actions' to the local dbs. Let's say that one
node nA wants to make a query to the db of the node nB. Then nA sends a
serialized message (which contain the query) to the node nB and then node nB
deserialises the received message and 'reads' the query and access its
database. This is what i want to simulate. So, can this be done? 
Can i attach so many different dbs as the nodes are?
I would prefer to do it through c++ and not through Tcl. 
Thank you so much!


-- 
View this message in context: 
http://www.nabble.com/multiple-databases-tf4480719.html#a12793407
Sent from the SQLite mailing list archive at Nabble.com.


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



[sqlite] Formatting numbers

2007-09-20 Thread T

Hi All,

What's the best way to format a number with a fixed number of decimal  
places?


For instance, if I have an Amount that I want to format as dollars  
and cents, I'm currently using:


begin immediate
;
create temporary table Invoice( Amount real )
;
insert into Invoice values( 123.4 )
;
select '$' || cast( Amount as integer ) || '.'
|| substr( cast( Amount * 100 + 100 as integer ), -2, 2 )
from Invoice
;
rollback
;

which gives:

$123.40

Is there a better way? I can't see any number formatting function in  
SQLite's repertoire.


Thanks,
Tom


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