Android Question ( RESOLVED ) Query Return Null if Russian

[MarcoRome]

Hi all. I have one query. All work if i utilize query with collation latin.
Example:

SELECT citta,compagnia,telefono,icona FROM taxi_mosca WHERE citta LIKE '%Rom%'

but if i use character Russian, example

SELECTcitta,compagnia,telefono,icona FROM taxi_mosca WHERE citta LIKE '%Петербург%'

Return Null.

So the code is good and work. Problem is character russian.
Any solution ???

Thank you in advance

 

[LucaMs]

You have to do conversions of the text. You can search the site to UTF-8.

For example

 

[MarcoRome]

Thank's Luca tomorrow i try ( now mondial )

 

[MarcoRome]

mmhh.. dont work so. To sure i wrong. This is my code example:

......  
If ret = DialogResponse.POSITIVE Then
        ' Verifico che il valore non sia vuoto
    If Id.Input = "" Then ' < -----------------------------------------------Here user input in Russian
            Msgbox(lingue.nonhainseritonazlin,lingue.messaggiolin)
            Return
    Else
        ' Converti in UTF8
        Dim b() As Byte = Id.Input.GetBytes("ISO-8859-1")
        Log(BytesToString(b, 0, b.Length, "UTF8")) ' <----- Here is null
        ExecuteRemoteQuery("SELECT citta, compagnia, telefono, icona FROM taxi_mosca WHERE icona like'%" & Id.Input & "%' order by compagnia", CERCA_PER_CITTA)

Where i wrong ??
Than you all in advance

 

[KMatle]

Did you use

mysql_query("SET CHARACTER SET utf8")

in the php script of your website?

I don't know if Russian is special, but I don't do any conversation in my app, only the chararcter set is utf8 in my php script.

 

[MarcoRome]

yes sure.

<?
$hst  = $_GET['hst'];
$db = $_GET['db'];
$usr = $_GET['usr'];
$pwd = $_GET['pwd'];
$conn_DB=mysql_connect($hst,$usr,$pwd) or die(mysql_error());
mysql_select_db($db) or die(mysql_error());

mysql_query("SET CHARACTER SET utf8");

$query = file_get_contents("php://input");

$sth = mysql_query($query);

if (mysql_errno()) {

header("HTTP/1.1 500 Internal Server Error");
echo $query.'n';
echo mysql_error();
}

else

{

    $rows = array();
    while($r = mysql_fetch_assoc($sth)) {
    $rows[] = $r;

}

    print json_encode($rows);

}

?>

if i write into app:

"SELECT citta, compagnia, telefono, icona FROM taxi_mosca order by compagnia"

all work and i have in my ListView all correct in Russian.
The problem is this ( that dont convert or similar ):

' Converti in UTF8
        Dim b() As Byte = Id.Input.GetBytes("ISO-8859-1")
        Log(BytesToString(b, 0, b.Length, "UTF8")) ' <----- Here is null
        ExecuteRemoteQuery("SELECT citta, compagnia, telefono, icona FROM taxi_mosca WHERE icona like'%" & Id.Input & "%' order by compagnia", CERCA_PER_CITTA)

i think that in Id.Input character ( put in russian ) exist problem
Thank Klaus

 

[LucaMs]

I'm not so sure, but if Id.Input is the text entered by user in cyrillic characters, you should do the opposite (Id.Input.GetBytes("UTF8")).

I'll try.

 

[MarcoRome]

Mhhh.. i try in all mode but nothing...is very strange all work only when we have query with parameter and russian characters. If i working with this query:

        ExecuteRemoteQuery("SELECT citta, compagnia, telefono, icona FROM taxi_mosca WHERE icona like '%" & Id.Input & "%' order by compagnia", CERCA_PER_CITTA)

where id.Input = "ico" all work. Work if i have iso characters.... and so, code is right. I want to understand how to pass the query with russian characters.

Look this movie:


http://www.queenandroid.com/download/test_taxi.mp4


I try with:

"normal mode":

        ExecuteRemoteQuery("SELECT citta, compagnia, telefono, icona FROM taxi_mosca WHERE citta like '%" & Id.Input & "%' order by compagnia", CERCA_PER_CITTA)

with GetBytes("ISO-8859-1")

' Converti in UTF8
        Dim b() As Byte = Id.Input.GetBytes("ISO-8859-1")
        Log(BytesToString(b, 0, b.Length, "UTF8")) ' <----- Here is null
        ExecuteRemoteQuery("SELECT citta, compagnia, telefono, icona FROM taxi_mosca WHERE citta like '%" & Id.Input & "%' order by compagnia", CERCA_PER_CITTA)

opposite - GetBytes("UTF8")

' Converti in UTF8
        Dim b() As Byte = Id.Input.GetBytes("UTF8")
        Log(BytesToString(b, 0, b.Length, "ISO-8859-1")) ' <----- Here is null
        ExecuteRemoteQuery("SELECT citta, compagnia, telefono, icona FROM taxi_mosca WHERE citta like '%" & Id.Input & "%' order by compagnia", CERCA_PER_CITTA)

Help me please

 

[MarcoRome]

Nobody have solution ?

Thank you all in advance

 

[Informatix]

Nobody have solution ?

Thank you all in advance

Did you try "COLLATE UNICODE" or "COLLATE LOCALIZED" in your WHERE clause ?
Are you aware of the following bug (from the official documentation of Sqlite) ?

(A bug: SQLite only understands upper/lower case for ASCII characters by default. The LIKE operator is case sensitive by default for unicode characters that are beyond the ASCII range. For example, the expression 'a' LIKE 'A' is TRUE but 'æ' LIKE 'Æ' is FALSE.)

 

[MarcoRome]

Hi Informatix i use MySql. The result in "select" without where clausole is ok and all work fine. The problem is when i work with WHERE clausole ( and only if i working with russian character )
Look the movie:

http://www.queenandroid.com/download/test_taxi.mp4

Thank you in advance for your support.

 

[Informatix]

Hi Informatix i use MySql.

Sorry, it was not obvious. In this case, you should ask your question in a forum dedicated to MySQL. You'll get probably more relevant answers.

 

[LucaMs]

Hi Informatix i use MySql. The result in "select" without where clausole is ok and all work fine. The problem is when i work with WHERE clausole ( and only if i working with russian character )
Look the movie:

http://www.queenandroid.com/download/test_taxi.mp4

Thank you in advance for your support.

I found this (stackoverflow):

String comparison works only with ascii characters (sort, upper, lower and like). If you are using non-ascii chars you should install plugin, which is like whole SQLite in size. Proof

 

[Informatix]

I found this (stackoverflow):

String comparison works only with ascii characters (sort, upper, lower and like). If you are using non-ascii chars you should install plugin, which is like whole SQLite in size. Proof

You should read the first sentence of the message that you quoted.

 

[MarcoRome]

i dont think that forum dedicated to MySQL resolve the problem, for this reason:

1. When i have this query:

SELECT * FROM taxi_mosca

i have correct result also in my app android ( as you see in movie ). The query about MySql and result in ListView is RIGHT

The problem is only when i have query with parameter, example:

SELECT citta,compagnia,telefono,icona FROM taxi_mosca WHERE citta LIKE '%Петербург%'

so i think ( almost i'm sure ) that this character's "Петербург" ( o similar ) have problem.

Colleagues who have already worked in this direction ??

 

[Informatix]

i dont think that forum dedicated to MySQL resolve the problem, for this reason:

1. When i have this query:

SELECT * FROM taxi_mosca

i have correct result also in my app android ( as you see in movie ). The query about MySql and result in ListView is RIGHT

The problem is only when i have query with parameter, example:

SELECT citta,compagnia,telefono,icona FROM taxi_mosca WHERE citta LIKE '%Петербург%'

so i think ( almost i'm sure ) that this character's "Петербург" ( o similar ) have problem.

Colleagues who have already worked in this direction ??

If your query is sent to a MySQL engine, then it's the MySQL engine that provides the result. The LIKE keyword is interpreted by the MySQL engine. Android has nothing to do here. Or I don't understand what you are talking about (I cannot see your video at work).

 

[MarcoRome]

oppsss... now understand ( that you cant see movie ).
If i have some query and working in MySQL engine , example:

SELECT citta,compagnia,telefono,icona FROM taxi_mosca WHERE citta LIKE '%Петербург%'

i have right result, so Mysql engine work fine, comeback 10 records ( right result ).

 

[LucaMs]

I was developing the code that I've attached. Now I read your posts. Perhaps you have already solved

 

[moster67]

Do you get same problems if you don't use LIKE?

For instance, does this work?

SELECT citta,compagnia,telefono,icona FROM taxi_mosca WHERE citta = 'Петербург'

Above suggestion is meant to see if LIKE could be the problem and perhaps to narrow down the problem

 

[Informatix]

Ah OK ! What library do you use to access your DB (I found at least 3 different libs in the forum) ? Did you try another library? Do you know what parameters are used by the lib to establish the connection ?