Re: [sqlite] Is this Sorting order right?
"Mahalakshmi.m" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > I will call this loop 2 times for inserting 2 records at run time. Which part of "the exact code" is difficult to understand? Can you post the code that I can copy, paste, compile, run and see the results for myself? You omitted the most interesting part - how you prepare the data to be inserted. I have reasons to believe this is precisely where your problems lie. > memcpy(st_SQLITE_DB_Record.s8_ArtistName,tmp_str,strlen(tmp_str)*sizeof( > short)); strlen counts the number of char (bytes) to the nearest NUL byte, not the number of Unicode characters. You multiply this count by two, thus copying up to the NUL byte and then again as much. This doesn't look right regardless of what's in tmp_str (which you chose not to show, by the way). > u16_SearchResult=sqlite3_column_text16(insert,0); > printf(u16_SearchResult); The first parameter of printf is a char*, it expects a narrow string. It doesn't know how to print a Unicode string. You would just see garbage where each byte (each half of a Unicode codepoint) is interpreted and printed as a character in its own right. -- With best wishes, Igor Tandetnik With sufficient thrust, pigs fly just fine. However, this is not necessarily a good idea. It is hard to be sure where they are going to land, and it could be dangerous sitting under them as they fly overhead. -- RFC 1925 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Is this Sorting order right?
typedef struct
{
unsigned short s8_ArtistName[256];
}STRUCT_SQLITE_MY_RECORD;
UINT32 u32_Return;
const UINT16 *u16_SearchResult;
STRUCT_SQLITE_MY_RECORD st_SQLITE_DB_Record;
sqlite3* db;
sqlite3_stmt* insert;
u32_Return = sqlite3_open("mysqlite.db ", );
u32_Return = sqlite3_exec(db, "pragma encoding= UTF16", 0, 0, 0);
u32_Return = sqlite3_exec(db, "create table ARTIST(id integer primary key
not null , ArtistName test not null collate nocase )", 0, 0, 0);
u32_Return = sqlite3_prepare(db,"INSERT INTO ARTIST (ArtistName)
VALUES(?);",-1,,0);
{
I will call this loop 2 times for inserting 2 records at run time.
memset(_SQLITE_DB_Record,0,sizeof(STRUCT_SQLITE_MY_RECORD));
memcpy(st_SQLITE_DB_Record.s8_ArtistName,tmp_str,strlen(tmp_str)*sizeof(
short));
u32_Return = sqlite3_bind_text16(insert,1,(char *)
st_SQLITE_DB_Record.s8_ArtistName,-1,SQLITE_STATIC); -->input will be in
UTF16 format.If it is utf8 I will convert it to utf16 and then I will bind.
u32_Return = sqlite3_step(insert);
u32_Return = sqlite3_reset(insert);
}
u32_Return = sqlite3_finalize(insert);
u32_Return = sqlite3_prepare(db,"SELECT ArtistName from ARTIST order by
ArtistName;",-1,,0);
u32_Return = sqlite3_step(insert);
while( u32_Return == SQLITE_ROW )
{
u16_SearchResult=sqlite3_column_text16(insert,0);
printf(u16_SearchResult);
printf("\n");
u32_Return = sqlite3_step(insert);
}
u32_Return = sqlite3_finalize(insert);
u32_Return = sqlite3_close(db);
I tried like this and I didnt got .
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is this Sorting order right?
Mahalakshmi.m <[EMAIL PROTECTED]> wrote: > unsigned short ArtistName; > sqlite3_bind_text16(insert,1,ArtistName,-1,SQLITE_STATIC); Doesn't compile. The third parameter of sqlite3_bind_text16 is a void*, and you are passing unsigned short there. You might have meant , except that the length is wrong then: you do not have a NUL-terminated buffer. Also, I don't see you actually initialize ArtistName anywhere. Did you actually mean to put some random garbage value into the database? Also, you are only inserting a single row. How do you plan to demonstrate incorrect sorting order with just one row? > unsigned char * Name = sqlite3_column_text16(select, 0 ); sqlite3_column_text16 gives you a pointer to a Unicode (wide) string, not a char* (narrow) string. > For this I got wrong output.. Not at all surprising. -- With best wishes, Igor Tandetnik With sufficient thrust, pigs fly just fine. However, this is not necessarily a good idea. It is hard to be sure where they are going to land, and it could be dangerous sitting under them as they fly overhead. -- RFC 1925 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Is this Sorting order right?
Igor Tandetnik wrote:
>I'm not sure I understand. What exactly are you doing differently in these
>two cases? Can you quote the exact code that fails, in full?
#include
#include
int main()
{
sqlite3* db;
sqlite3_stmt* insert;
unsigned short ArtistName;
sqlite3_open(" mysqlite.db ", );
sqlite3_exec(db, "pragma encoding = UTF16", 0, 0, 0);
sqlite3_exec(db, "create table ARTIST(id integer primary key not null ,
ArtistName test not null collate nocase )", 0, 0, 0);
sqlite3_prepare(db,"INSERT INTO ARTIST (ArtistName)
VALUES(?);",-1,,0);
sqlite3_bind_text16(insert,1,ArtistName,-1,SQLITE_STATIC);
sqlite3_step(insert);
sqlite3_finalize(insert);
sqlite3_stmt* select;
sqlite3_prepare(db, " select ArtistName from ARTIST order by
ArtistName ", -1, , 0);
while (sqlite3_step(select) == SQLITE_ROW)
{
unsigned char * Name = sqlite3_column_text16(select, 0 );
printf("text= %s \n", Name );
}
sqlite3_finalize(select);
sqlite3_close(db);
return 0;
}
For this I got wrong output..
Thanks & Regards,
Mahalakshmi
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is this Sorting order right?
"Mahalakshmi.m" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Igor Tandetnik wrote >>> I can't reproduce this particular problem. Here's the test I wrote: >>> Figure out what you are doing differently. > > I too did the same using both UTF8 anf UTF16 .Its coming right for > UTF8 but its wrong for UTF16. I'm not sure I understand. What exactly are you doing differently in these two cases? Can you quote the exact code that fails, in full? Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Is this Sorting order right?
Igor Tandetnik wrote >> I can't reproduce this particular problem. Here's the test I wrote: >> Figure out what you are doing differently. I too did the same using both UTF8 anf UTF16 .Its coming right for UTF8 but its wrong for UTF16. For both encoding I used the below procedure, sqlite3_prepare(db, "insert into t(id, text) values (?, ?)", -1, , 0); unsigned short Name[64]; sqlite3_bind_int(insert, 1, 1); sqlite3_bind_text16(insert, 2, Name, -1, SQLITE_STATIC); sqlite3_step(insert); For UTF8 output is id=2 text=30A2 id=1 text=6B4C This sorting is as expected. For UTF16 output is id=1 text=6B4C id=2 text=30A2 This sorting is not expected. Where I was wrong in UTF16 encoding. Is there any reson for coming in UTF8 and not in UTF16? Thanks & Regards, Mahalakshmi ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is this Sorting order right?
Mahalakshmi.m
<[EMAIL PROTECTED]>
wrote:
> What about Japanese Kanji?
> Will sqlite sort all the Hiragana,Katakana,Kanji.
> I am having some records with starting Unicode as follows.
>
> 6B4C Kanji 25 1
> 30A2 Katakana
>
> I am storing it using sqlite3_bind_text16().
>
> But if I sort the above two the Unicode with 6B4C comes first but my
> desired output is
>
> 30A2 Katakana
> 6B4C Kanji 25 1
I can't reproduce this particular problem. Here's the test I wrote:
#include
#include
int main() {
sqlite3* db;
sqlite3_open(":memory:", );
sqlite3_exec(db, "create table t(id, text)", 0, 0, 0);
sqlite3_stmt* insert;
sqlite3_prepare(db, "insert into t(id, text) values (?, ?)", -1,
, 0);
sqlite3_bind_int(insert, 1, 1);
sqlite3_bind_text16(insert, 2, L"\u6B4C", -1, SQLITE_STATIC);
sqlite3_step(insert);
sqlite3_reset(insert);
sqlite3_bind_int(insert, 1, 2);
sqlite3_bind_text16(insert, 2, L"\u30A2", -1, SQLITE_STATIC);
sqlite3_step(insert);
sqlite3_finalize(insert);
sqlite3_stmt* select;
sqlite3_prepare(db, "select id, text from t order by text", -1,
, 0);
while (sqlite3_step(select) == SQLITE_ROW) {
int id = sqlite3_column_int(select, 0);
wchar_t* text = (wchar_t*)sqlite3_column_text16(select, 1);
printf("id=%d text=%X\n", id, (int)text[0]);
}
sqlite3_finalize(select);
sqlite3_close(db);
return 0;
}
This program prints
id=2 text=30A2
id=1 text=6B4C
meaning that sorting is as expected. Figure out what you are doing
differently.
Note that, in general, there's more to collation than a simple memcmp
that SQLite does. Consider for example U+30FD (Katakana Iteration Mark).
As I understand, a string containing it should sort as if the preceding
character was actually repeated: so ?? (U+30A2 U+30FD) should sort
before ?? (U+30A2 U+30A4). Also consider halfwidth letters (e.g. U+FF71
Halfwidth Katakana Letter A) which are expected to be sorted near their
fullwidth equivalents.
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is this Sorting order right?
Here's an example skeleton for a custom collation. (Using UTF8
encoding, change the declarations as necessary.)
// declarations
typedef int SQLiteCompare_t (void *, int, const void *, int,
const void *);
int MyCompare(void *userData, int str1Len, const UTF8 *str1, int
str2Len, const UTF8 *str2)
{
// whatever you want here, return < 0 if str1 < str2, 0
if equal, etc.
}
// connecting the function
int err = sqlite3_create_collation(db, "MyCompare", SQLITE_UTF8,
NULL, (SQLiteCompare_t *));
if (err != SQLITE_OK)
{
// do something to handle the err -- probably fix your
code, this should work
}
To use, 'CREATE TABLE foo(bar TEXT COLLATE MyCompare);'
Now you're set -- of course keep in mind that if you open the DB with a
different sqlite (e.g. the command line tool or a GUI browser), the
custom collation sequence won't be available. Sqlite is fairly robust,
it won't blow up and you can still do pretty much anything with the DB
that doesn't directly call the custom function, e.g. don't try to sort
by the "bar" column or use an index built on that column.
--Bob
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Mahalakshmi.m
Sent: Monday, May 12, 2008 7:21 AM
To: Sqlite User
Subject: [sqlite] Is this Sorting order right?
Mahalakshmi wrote
> What about Japanese Kanji?
> Will sqlite sort all the Hiragana,Katakana,Kanji.
Igor Tandetnik wrote
>> Not out of the box. You will have to implement a custom collation.
>> Or, you can build SQLite with ICU support, then it >> will use ICU
>> collation functions.
Thanks a lot.
But I didn't get the point.
Do I want to write my own string comparison code and use that as Custom
Collation .If so where can I find the examples.(or) I can use just
sqlite3_create_function( *gpst_SqliteInstance, "shellstatic", 0,
SQLITE_UTF16, 0, shellstaticFunc, 0, 0); My database has UTF16 encoding.
Even if the collation is UTF8, Sqlite will do memcmp() say 1st record
has the Unicode 6B4C and 2nd records has 30A2.If sqlite is comparing
byte by byte means then 30A2 only has to come as first record.Is it
right? But I am not getting.
Please help to clarify this.
Thanks & Regards,
Mahalakshmi
___
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] Is this Sorting order right?
Mahalakshmi wrote > What about Japanese Kanji? > Will sqlite sort all the Hiragana,Katakana,Kanji. Igor Tandetnik wrote >> Not out of the box. You will have to implement a custom collation. Or, >> you can build SQLite with ICU support, then it >> will use ICU collation >> functions. Thanks a lot. But I didn't get the point. Do I want to write my own string comparison code and use that as Custom Collation .If so where can I find the examples.(or) I can use just sqlite3_create_function( *gpst_SqliteInstance, "shellstatic", 0, SQLITE_UTF16, 0, shellstaticFunc, 0, 0); My database has UTF16 encoding. Even if the collation is UTF8, Sqlite will do memcmp() say 1st record has the Unicode 6B4C and 2nd records has 30A2.If sqlite is comparing byte by byte means then 30A2 only has to come as first record.Is it right? But I am not getting. Please help to clarify this. Thanks & Regards, Mahalakshmi ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is this Sorting order right?
"Mahalakshmi.m" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > What about Japanese Kanji? > > Will sqlite sort all the Hiragana,Katakana,Kanji. Not out of the box. You will have to implement a custom collation. Or, you can build SQLite with ICU support, then it will use ICU collation functions. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Is this Sorting order right?
What about Japanese Kanji? Will sqlite sort all the Hiragana,Katakana,Kanji. I am having some records with starting Unicode as follows. 6B4C Kanji 歌手生活25周却記念 北島三石1 30A2 Katakana アンテナ I am storing it using sqlite3_bind_text16(). But if I sort the above two the Unicode with 6B4C comes first but my desired output is 30A2 Katakanaアンテナ 6B4C Kanji 歌手生活25周却記念 北島三石1 Where I was wrong.Kindly advice. Thanks & Regards, Mahalakshmi ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
