Re: [sqlite] UTF-8 and UTF-16

[a1rex]

Thank you for your help!

This call:
sqlite3_exec(handle,"PRAGMA encoding = \"UTF-16\"",NULL,NULL,);
works.


Samuel


- Original Message 
From: Igor Tandetnik 
To: sqlite-users@sqlite.org
Sent: Sun, January 31, 2010 6:12:27 PM
Subject: Re: [sqlite] UTF-8 and UTF-16

a1rex wrote:
> I am planning to store text in a database which has to accommodate a few 
> international languages.
> In this case I have to use UTF-16LE encoding for my TEXT fields.

Perhaps there are some external reasons, but there's nothing in SQLite that 
would force this. For example, UTF-8 is just as capable of representing any 
Unicode string as UTF-16.

> I know that once an encoding has been set for a database, it cannot be 
> changed.
> Do BLOBS are effected?

No.

> I guess I cannot mix TEXT  UTF-8 and UTF-16 columns in the same data base, 
> can I?

Why would you want to?

> Can I get away with a database encoded in UTF-8  by using 
> sqlite3_bind_text16() function for TEXT fields when I need UTF-16 text?

Yes. SQLite automatically converts between UTF-8 and UTF-16 as necessary.

> After opening empty database, I tried to execute:
> 
> sqlite3_exec(handle,"PRAGMA encoding = UTF-16",NULL,NULL,);
> 
> But I got the following error:
> PRAMA error: near "-": syntax error.  What did I do wrong?

It's PRAGMA encoding = "UTF-16". The parameter needs to be quoted.

Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



  __
Yahoo! Canada Toolbar: Search from anywhere on the web, and bookmark your 
favourite sites. Download it now
http://ca.toolbar.yahoo.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UTF-8 and UTF-16

[Kees Nuyt]

On Sun, 31 Jan 2010 18:12:27 -0500, "Igor Tandetnik"
 wrote:

>It's PRAGMA encoding = "UTF-16". The parameter needs to be quoted.

This is indeed the syntax description. Peculiarly, anywhere
else, string literals have to be single quoted.

Is this something to iron out in v4 ?
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UTF-8 and UTF-16

[Igor Tandetnik]

a1rex wrote:
> I am planning to store text in a database which has to accommodate a few 
> international languages.
> In this case I have to use UTF-16LE encoding for my TEXT fields.

Perhaps there are some external reasons, but there's nothing in SQLite that 
would force this. For example, UTF-8 is just as capable of representing any 
Unicode string as UTF-16.

> I know that once an encoding has been set for a database, it cannot be 
> changed.
> Do BLOBS are effected?

No.

> I guess I cannot mix TEXT  UTF-8 and UTF-16 columns in the same data base, 
> can I?

Why would you want to?

> Can I get away with a database encoded in UTF-8  by using 
> sqlite3_bind_text16() function for TEXT fields when I need UTF-16 text?

Yes. SQLite automatically converts between UTF-8 and UTF-16 as necessary.

> After opening empty database, I tried to execute:
> 
> sqlite3_exec(handle,"PRAGMA encoding = UTF-16",NULL,NULL,);
> 
> But I got the following error:
> PRAMA error: near "-": syntax error.  What did I do wrong?

It's PRAGMA encoding = "UTF-16". The parameter needs to be quoted.

Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] UTF-8 and UTF-16

[a1rex]

I am planning to store text in a database which has to accommodate a few 
international languages. 
In this case I have to use UTF-16LE encoding for my TEXT fields.   
I know that once an encoding has been set for a database, it cannot be changed. 
 
Do BLOBS are effected?

I guess I cannot mix TEXT  UTF-8 and UTF-16 columns in the same data base, can 
I?
 
Can I get away with a database encoded in UTF-8  by using sqlite3_bind_text16() 
function for TEXT fields when I need UTF-16 text?
 
After opening empty database, I tried to execute: 

sqlite3_exec(handle,"PRAGMA encoding = UTF-16",NULL,NULL,);
 
But I got the following error:
PRAMA error: near "-": syntax error.  What did I do wrong?
 
Thank you for reading. Any comment appreciated!
Samuel


  __
The new Internet Explorer® 8 - Faster, safer, easier.  Optimized for Yahoo!  
Get it Now for Free! at http://downloads.yahoo.com/ca/internetexplorer/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] system.data.sqlite & encryption

[Sylvain Pointeau]

Hi all,

I am using System.data.sqlite from my .NET project,
but I am planning to access my db from C/C++ and C# / C++/CI

The point is that I want an encryted database, and I plan to use the
extension from
http://www.hwaci.com/sw/sqlite/prosupport.html#crypto

how to use / implement this exension in system.data.sqlite?
or are they compatible both?

thank you in advance for your help.

Best regards,
Sylvain
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] negative numbers

[P Kishor]

On Sun, Jan 31, 2010 at 9:42 AM, Jay A. Kreibich  wrote:
> On Sun, Jan 31, 2010 at 09:11:21AM -0600, P Kishor scratched on the wall:
>> On Sun, Jan 31, 2010 at 8:19 AM, james pruett  wrote:
>> > I am writing an open source program.
>> >
>> > I am having trouble getting any results using this query. This returns 
>> > none
>
>> > select * from signs where lon>-121 and lon<-119;
>
>> Given your data, you need lon >= -120 and lon < -119
>>
>> You are not comparing with >=. You are using only >
>
>
>  That's true, but he's using 121, not 120.
>


Good grief, I have no idea why I saw >-120.



-- 
Puneet Kishor
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] negative numbers

[Jay A. Kreibich]

On Sun, Jan 31, 2010 at 09:11:21AM -0600, P Kishor scratched on the wall:
> On Sun, Jan 31, 2010 at 8:19 AM, james pruett  wrote:
> > I am writing an open source program.
> >
> > I am having trouble getting any results using this query. This returns 
> > none

> > select * from signs where lon>-121 and lon<-119;

> Given your data, you need lon >= -120 and lon < -119
> 
> You are not comparing with >=. You are using only >


  That's true, but he's using 121, not 120.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] negative numbers

[P Kishor]

On Sun, Jan 31, 2010 at 8:19 AM, james pruett  wrote:
> I am writing an open source program.
>
> I am having trouble getting any results using this query. This returns 
> none
>
> select * from signs where lon>-121 and lon<-119;
>
> onCreate(SQLiteDatabase db) {
>      db.execSQL("CREATE TABLE " + TABLE_NAME + " (" + _ID
>            + " INTEGER PRIMARY KEY AUTOINCREMENT, " + TIME
>            + " VARCHAR(45), " + TAGG
>            + " VARCHAR(45), " + LAT
>            + " FLOAT      , " + LON
>            + " VARCHAR(45), " + COG
>            + " VARCHAR(45), " + MPH
>            + " VARCHAR(45), " + KPH
>            +  " TEXT NOT NULL);");
>   }
>
> Here is the database using RazorSQL v504
> select * from signs;
> _id                  time     tag      lat         lon    cog    mph   kph
> 1    1264890306692    test    20.0    -120.0    0.0    45.0    0.0
> 2    1264890311484    test    20.0    -120.0    0.0    45.0    0.0
> 3    1264890345263    test    20.0    -120.0    0.0    45.0    0.0
> 4    1264890346700    test    20.0    -120.0    0.0    45.0    0.0
>

Given your data, you need lon >= -120 and lon < -119

You are not comparing with >=. You are using only >

See more

sqlite> CREATE TABLE signs (id INTEGER PRIMARY KEY, lat REAL, lon REAL);
sqlite> INSERT INTO signs VALUES (1, 20.0, -120.0);
sqlite> INSERT INTO signs VALUES (2, 20.0, -120.0);
sqlite> INSERT INTO signs VALUES (3, 20.0, -120.0);
sqlite> INSERT INTO signs VALUES (4, 20.0, -119.5);
sqlite> SELECT * FROM signs;
id  lat lon
--  --  --
1   20.0-120.0
2   20.0-120.0
3   20.0-120.0
4   20.0-119.5
sqlite> SELECT * FROM signs WHERE lon > -120.0 AND lon < -119.0;
id  lat lon
--  --  --
4   20.0-119.5
sqlite> SELECT * FROM signs WHERE lon >= -120.0 AND lon < -119.0;
id  lat lon
--  --  --
1   20.0-120.0
2   20.0-120.0
3   20.0-120.0
4   20.0-119.5
sqlite> SELECT * FROM signs WHERE lon >= -120 AND lon < -119;
id  lat lon
--  --  --
1   20.0-120.0
2   20.0-120.0
3   20.0-120.0
4   20.0-119.5
sqlite>


> ---
> I have tried lon= varchar(45) and also as a FLOAT, and both don't seem
> to handle negative numbers correctly.
> Any help appreciated!
>
> Jim
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] negative numbers

[Simon Slavin]

On 31 Jan 2010, at 2:19pm, james pruett wrote:

> I am having trouble getting any results using this query. This returns 
> none
> 
> select * from signs where lon>-121 and lon<-119;
> 
> onCreate(SQLiteDatabase db) {
>  db.execSQL("CREATE TABLE " + TABLE_NAME + " (" + _ID
>+ " INTEGER PRIMARY KEY AUTOINCREMENT, " + TIME
>+ " VARCHAR(45), " + TAGG
>+ " VARCHAR(45), " + LAT
>+ " FLOAT  , " + LON

SQLite does not have a 'FLOAT' type (or indeed a VARCHAR type).  See

http://www.sqlite.org/datatype3.html

and define your 'lon' column as REAL.  Also see further down the page: sqlite 
does not actually do column types: you could define a column as REAL but still 
put a string into that column of one row.  So make sure that your numbers are 
actually being stored as numbers, not strings, by testing the method you use to 
create a row.  You can do this by creating a row, then using the function 
'typeof(lon)'.  If you did it right, that should return 'real'.  If you didn't, 
it might return 'text'.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] negative numbers

[D. Richard Hipp]

On Jan 31, 2010, at 9:19 AM, james pruett wrote:

> I am writing an open source program.
>
> I am having trouble getting any results using this query. This  
> returns none
>
> select * from signs where lon>-121 and lon<-119;
>
> onCreate(SQLiteDatabase db) {
>  db.execSQL("CREATE TABLE " + TABLE_NAME + " (" + _ID
>+ " INTEGER PRIMARY KEY AUTOINCREMENT, " + TIME
>+ " VARCHAR(45), " + TAGG
>+ " VARCHAR(45), " + LAT
>+ " FLOAT  , " + LON
>+ " VARCHAR(45), " + COG
>+ " VARCHAR(45), " + MPH
>+ " VARCHAR(45), " + KPH
>+  " TEXT NOT NULL);");
>   }
>
> Here is the database using RazorSQL v504
> select * from signs;
> _id  time tag  lat loncog 
> mph   kph
> 11264890306692test20.0-120.00.045.00.0
> 21264890311484test20.0-120.00.045.00.0
> 31264890345263test20.0-120.00.045.00.0
> 41264890346700test20.0-120.00.045.00.0
>
> ---
> I have tried lon= varchar(45) and also as a FLOAT, and both don't seem
> to handle negative numbers correctly.

FLOAT should work.  Are you sure you tried it correctly?

> Any help appreciated!
>
> Jim
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

D. Richard Hipp
d...@hwaci.com



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] negative numbers

[james pruett]

I am writing an open source program.

I am having trouble getting any results using this query. This returns none

select * from signs where lon>-121 and lon<-119;

onCreate(SQLiteDatabase db) {
  db.execSQL("CREATE TABLE " + TABLE_NAME + " (" + _ID
+ " INTEGER PRIMARY KEY AUTOINCREMENT, " + TIME
+ " VARCHAR(45), " + TAGG
+ " VARCHAR(45), " + LAT
+ " FLOAT  , " + LON
+ " VARCHAR(45), " + COG
+ " VARCHAR(45), " + MPH
+ " VARCHAR(45), " + KPH
+  " TEXT NOT NULL);");
   }

Here is the database using RazorSQL v504
select * from signs;
_id  time tag  lat loncogmph   kph
11264890306692test20.0-120.00.045.00.0
21264890311484test20.0-120.00.045.00.0
31264890345263test20.0-120.00.045.00.0
41264890346700test20.0-120.00.045.00.0

---
I have tried lon= varchar(45) and also as a FLOAT, and both don't seem
to handle negative numbers correctly.
Any help appreciated!

Jim
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] parallelizing an update

[Sylvain Pointeau]

Hello,

it is not aesthetic, it groups all update in a single transaction that speed
up the processing.
Using multi thread or multi-process is not efficient, it is at the end a
single process that can write to the database (a single file).

Grouping all the update in a single transaction is the only way to speed up
your program,

Cheers,
Sylvain

On Sun, Jan 31, 2010 at 1:15 AM, Robert Citek wrote:

> Sure.  This script can use a lot of aesthetic improvement, but it
> highlights processing the data in a single process.
>
> The question would be, how to modify the script to process the data in
> with parallel processes?
>
> Regards,
> - Robert
>
> On Sat, Jan 30, 2010 at 4:36 AM, Sylvain Pointeau
>  wrote:
> > a good thing would have been to generate one file with all the
> statements...
> > if you do that then you run sqlite with this file surrounded by
> transaction
> > begin/commit
> >
> > echo "begin transaction" >> update.sql
> >
> > sqlite3 -separator $'\t' sample.db 'select rowid, item from foo;' |
> > while read rowid item ; do
> >  status=$(long_running_process "${item}" )
> >  echo "update foo set status=${status} where rowid=${rowid} ;" >>
> update.sql
> > done
> >
> > echo "commit transaction" >> update.sql
> >
> > sqlite3 sample.db < update.sql
> >
> > Best regards,
> > Sylvain
> >
> > On Sat, Jan 30, 2010 at 12:04 AM, Robert Citek  >wrote:
> >
> >> Are there some white papers or examples of how to do updates in
> >> parallel using sqlite?
> >>
> >> I have a large dataset in sqlite that I need to process outside of
> >> sqlite and then update the sqlite database.  The process looks
> >> something like this:
> >>
> >> sqlite3 -separator $'\t' sample.db 'select rowid, item from foo;' |
> >> while read rowid item ; do
> >>  status=$(long_running_process "${item}" )
> >>  sqlite3 sample.db "update foo set status=${status} where rowid=${rowid}
> ;"
> >> done
> >>
> >> Because long_running_process takes a long time, I could speed up the
> >> overall time by running more than one long_running_process at the same
> >> time.  One way to do this would be to segment the data and run a
> >> separate process on each segment.  For the update each process would
> >> collect the status data "outside" of the sample.db, e.g in a separate
> >> database.  When all the processes have finished, the parent process
> >> would attach the separate databases and update the original database.
> >> When all is done, the parent process would clean up the ancillary
> >> databases.
> >>
> >> I was just wondering if there are other ways to do this that I may be
> >> overlooking.
> >>
> >> Thanks in advance to pointers to any references.
> >>
> >> Regards,
> >> - Robert
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users@sqlite.org
> >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >>
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users