Android Question ( RESOLVED ) Query Return Null if Russian

MarcoRome · Jun 14, 2014

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

B4X:

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

but if i use character Russian, example

B4X:

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 · Jun 14, 2014

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

For example

MarcoRome · Jun 14, 2014

Thank's Luca tomorrow i try ( now mondial )

MarcoRome · Jun 15, 2014

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

B4X:

......  
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 · Jun 15, 2014

Did you use

B4X:

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 · Jun 15, 2014

yes sure.

B4X:

<?
$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:

B4X:

"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 ):

B4X:

' 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 · Jun 15, 2014

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 · Jun 17, 2014

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:

B4X:

        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":

B4X:

        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")

B4X:

' 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")

B4X:

' 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 · Jun 17, 2014

Nobody have solution ?

Thank you all in advance

Informatix · Jun 17, 2014

MarcoRome said:
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 · Jun 17, 2014

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 · Jun 17, 2014

MarcoRome said:
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 · Jun 17, 2014

MarcoRome said:
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 · Jun 17, 2014

LucaMs said:
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 · Jun 17, 2014

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

1. When i have this query:

B4X:

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:

B4X:

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 · Jun 17, 2014

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

1. When i have this query:

B4X:

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:

B4X:

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 · Jun 17, 2014

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

B4X:

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 · Jun 17, 2014

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

moster67 · Jun 17, 2014

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

For instance, does this work?

B4X:

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 · Jun 17, 2014

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 ?

Android Question ( RESOLVED ) Query Return Null if Russian

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Attachments

Expert

Expert

Similar Threads

Privacy & Transparency

Privacy & Transparency