Re: [sqlite] Problem getting utf-8 text out of a text column

2013-03-21 Thread Richard Hipp 
On Thu, Mar 21, 2013 at 6:52 PM, Tim Streater  wrote:

> I have a column defined as TEXT and I insert utf-8 text into it. In
> particular, there are byte sequences that might look as follows:
> ... 74 6F 20 C2 A3 32 35 30 ... (in hex)
> which represents:
> ... to £250 ...
> I used Navicat for SQLite Lite to peer at the data as hex and text in the
> column, and it looks as I've presented it.
> The problem comes when I try to retrieve the data using a PHP script. I
> want the data back as bytes because I'm going to encode it as quoted
> printable, with decimal byte values greater than 127 being separately
> encoded. This would give the above as:
> ... to =C2=A3250 ...
> But: the byte stream I get back on doing a SELECT is always *missing* the
> C2 byte. What am I missing here? I'm using the PHP PDO interface.
> Thanks,
>

What do you get back when you do select "hex(column)" instead of just
"column" using PHP.  If the C2 byte is there, then I'm going to put the
blame on PHP in this case.


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



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

Re: [sqlite] Problem getting utf-8 text out of a text column

2013-03-21 Thread Tim Streater 
Simon,

Thanks for the suggestions. I should have mentioned that I'm running under OS X 
so I have the sqlite3 application and can easily do the tests you suggest. But 
that'll have to be tomorrow - time for shuteye now :-)

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

Re: [sqlite] Problem getting utf-8 text out of a text column

2013-03-21 Thread Simon Slavin 

On 21 Mar 2013, at 10:52pm, Tim Streater  wrote:

> I have a column defined as TEXT and I insert utf-8 text into it. In 
> particular, there are byte sequences that might look as follows:
> ... 74 6F 20 C2 A3 32 35 30 ... (in hex)
> which represents:
> ... to £250 ...
> I used Navicat for SQLite Lite to peer at the data as hex and text in the 
> column, and it looks as I've presented it.

Thanks for the detailed report which saves us a lot of time asking questions.  
Can you use the sqlite shell tool, rather than Navicat, to look at the database 
file and see the contents ?  You can download compiled versions for most 
platforms from



In particular please use something like

SELECT myField,length(myField) FROM myTable

to check how long it thinks the field is.

> The problem comes when I try to retrieve the data using a PHP script. I want 
> the data back as bytes because I'm going to encode it as quoted printable, 
> with decimal byte values greater than 127 being separately encoded. This 
> would give the above as:
> ... to =C2=A3250 ...
> But: the byte stream I get back on doing a SELECT is always *missing* the C2 
> byte. What am I missing here? I'm using the PHP PDO interface.

Again, can you try retreiving the length of the field as part of the same 
SELECT and see whether the field length agrees with the C2 being missing ?  And 
also compare that with the result of doing strlen(theString) as a PHP command 
and find out whether all three agree ?

You could try doing same thing using the sqlte3 interface which is closer to 
'pure sqlite3' if you think that'll help.  Using that library will involve a 
cleaner path with fewer conversions and processes.  It's less likely to give 
you such problems.

$dbConnection = new SQLite3('testdb.sqlite');
$dbConnection->exec("INSERT INTO myTable VALUES ('to £250'");  // should really 
check return value
$retrieved = $dbConnection->querySingle('SELECT theValue,length(theValue) FROM 
myTable', true);
echo $retrieved[0], $retrieved[1], strlen($retrieved[0]);

If that works, you could write with one and read with the other and use that to 
figure out whether the fault is in the bit that stores or retrieves the value.

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

[sqlite] Problem getting utf-8 text out of a text column

2013-03-21 Thread Tim Streater 
I have a column defined as TEXT and I insert utf-8 text into it. In particular, 
there are byte sequences that might look as follows:
... 74 6F 20 C2 A3 32 35 30 ... (in hex)
which represents:
... to £250 ...
I used Navicat for SQLite Lite to peer at the data as hex and text in the 
column, and it looks as I've presented it.
The problem comes when I try to retrieve the data using a PHP script. I want 
the data back as bytes because I'm going to encode it as quoted printable, with 
decimal byte values greater than 127 being separately encoded. This would give 
the above as:
... to =C2=A3250 ...
But: the byte stream I get back on doing a SELECT is always *missing* the C2 
byte. What am I missing here? I'm using the PHP PDO interface.
Thanks,
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to install sqlite 3.7.11

2013-03-21 Thread Ryan Johnson 

On 21/03/2013 11:47 AM, Simon Slavin wrote:

On 21 Mar 2013, at 4:43am, Pratik Patodi  wrote:


I want to install sqlite 3.7.11 in my ubuntu 10.04.
Got the source code but no the makefile/config file.

 From where can ,I Download the set-up and how to install it.

SQLite is not something you install into your computer, where it is then used 
by lots of programs.  Instead the person writing each program which needs it 
includes it as part of their sourcecode.
Perhaps OP conflated the stand-alone sqlite CLI shell with sqlite 
itself? Installing the CLI should be as simple as:


1. Download the source amalgam
2. gcc -O2 sqlite3.c shell.c -o sqlite3
3. Copy the `sqlite3' binary to wherever it will be most convenient to use

Ryan

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

Re: [sqlite] How to install sqlite 3.7.11

2013-03-21 Thread Clemens Ladisch 
Simon Slavin wrote:
> On 21 Mar 2013, at 4:43am, Pratik Patodi  wrote:
>> I want to install sqlite 3.7.11 in my ubuntu 10.04.
>> Got the source code but no the makefile/config file.
>
> SQLite is not something you install into your computer, where it is
> then used by lots of programs.

But this is what most distributions do:
http://packages.ubuntu.com/lucid/libsqlite3-0

Pratik, if you want a shared library, you must get a newer package from
Ubuntu, or backport the corresponding package from a more recent Ubuntu
version yourself.

But it is indeed recommended to do this:

> Instead the person writing each program which needs it includes it as
> part of their sourcecode.


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

Re: [sqlite] How to install sqlite 3.7.11

2013-03-21 Thread Simon Slavin 

On 21 Mar 2013, at 4:43am, Pratik Patodi  wrote:

> I want to install sqlite 3.7.11 in my ubuntu 10.04.
> Got the source code but no the makefile/config file.
> 
> From where can ,I Download the set-up and how to install it.

SQLite is not something you install into your computer, where it is then used 
by lots of programs.  Instead the person writing each program which needs it 
includes it as part of their sourcecode.

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

[sqlite] atof1 and sysfault test failures

2013-03-21 Thread alw 
I am having below test failures on ARM board with linux

atof1.test:
--
Expected: [1]
 Got: [0]
atof1-1.1520.1... Ok
atof1-1.1520.2... Ok
atof1-1.1521.1...{3bf01ed5b1b47f07 3bf01ed5b1b47f06}
5.461820304745700866657409448094520528310601164636732323884316e-20
5.461820304745702070363624690116928688296815280216306410197846e-20
5.461820304745700866657409448094520528310601164636732323884316e-20

sysfault tests : sysfault-2.1-vfsfault-transient tests fails with "disk I/O
Error".

Thanks in advance 



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/atof1-and-sysfault-test-failures-tp67827.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How to install sqlite 3.7.11

2013-03-21 Thread Pratik Patodi 
Hello all,
I want to install sqlite 3.7.11 in my ubuntu 10.04.
Got the source code but no the makefile/config file.

>From where can ,I Download the set-up and how to install it.

Please reply soon. Thanks in advance.

   Regards

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

[sqlite] spelling in sqlite3.c version 3.7.16

2013-03-21 Thread Mark Brand 
For what it's worth, I thought I'd forward the spelling errors reported by 
qt-project's automatic review system. The ones with the asterisk depend on 
which national spelling standard you follow.


src/3rdparty/sqlite/sqlite3.c:
sytem -> system (occurs twice)
existance -> existence (occurs twice)
occurrance -> occurrence
occured -> occurred (occurs twice)
behaviour -> behavior [*] (occurs thrice)
seperately -> separately
precendence -> precedence
pased -> passed
implemenation -> implementation
retreives -> retrieves
initialise -> initialize [*]
occurence -> occurrence

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

Re: [sqlite] Speeding up the UPSERT by using ISNULL instead of COALESCE in the subselect

2013-03-21 Thread Clemens Ladisch 
Frank Chang wrote:
> Could anyone tell me if I should replace COALESCE with ISNULL in the
> subselect

A COALESCE with two parameters behaves identical to IFNULL.

> in order the improve the speed

There will be no difference.
(Except that IFNULL saves two characters in the SQL command string.  ;-)


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

[sqlite] Speeding up the UPSERT by using ISNULL instead of COALESCE in the subselect

2013-03-21 Thread Frank Chang 
Good morning,
Could anyone tell me if I should replace COALESCE with ISNULL in the
subselect in order the improve the speed of the following UPSERT:

INSERT OR REPLACE INTO FREQMARY(Value, NumericValue, DateTimeValue, Count,
Length) VALUES ('Braintree Road',NULL,NULL, COALESCE((SELECT Count+1 FROM
FREQMARY ), 1), 14)?
Thank you for your help.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users