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

[a1rex]

>From: Jens Miltner 

>Sent: Wed, February 3, 2010 9:46:06 AM

>Just another thought to consider: depending on the amount of non-ASCII  
>(or non-roman) string data stored in your database, in may be more  
>efficient to use UTF-8 encoding rather than UTF-16 encoding:
>UTF-8 takes up less space for ASCII or roman text, whereas it may take  
>up more space for other characters. This really depends on the mix.

Thank you for your suggestions!. I will go with UTF-8 encoding.
Regards,
Samuel


  __
Make your browsing faster, safer, and easier with the new Internet Explorer® 8. 
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

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

[Jens Miltner]

Am 01.02.2010 um 00:02 schrieb 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.

Just another thought to consider: depending on the amount of non-ASCII  
(or non-roman) string data stored in your database, in may be more  
efficient to use UTF-8 encoding rather than UTF-16 encoding:
UTF-8 takes up less space for ASCII or roman text, whereas it may take  
up more space for other characters. This really depends on the mix.

However, we found that our database performance improved quite a bit  
when we switched back to UTF-8 (granted, most of the characters in our  
data can be represented by single-byte UTF-8 sequences), mainly due to  
the reduced overall database size (probably fitting more columns on a  
single page in the database, thus requiring less read operations to  
access a single record).

But as I said, this depends a lot on the data you will store, the  
total size of the database and the structure of your tables, so you'll  
have to evaluate yourself...




___
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

[a1rex]

Thank you for your help!

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


Samuel


- Original Message 
From: Igor Tandetnik <itandet...@mvps.org>
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