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

MarcoRome · Jun 17, 2014

I use Tutorial Connect MySQL ( Erel ) http://www.b4x.com/android/forum/threads/connect-android-to-mysql-database-tutorial.8339/
work very well already 2 app without problem ( very fast also ) but this is first time that i work wirh ciryllic character.

B4X:

Sub Activity_Create(FirstTime As Boolean)
.......
'richiamo DB MySQL
ipserver = "62.149.150.XXX"
dbname = "Sql759130_1XX"
dbpw = "XXXX55566666"
userdb = "Sql233344"
.............
End Sub
 
 
Sub ExecuteRemoteQuery(Query As String, JobName As String)
    Dim job As HttpJob
    job.Initialize(JobName, Me)
    job.PostString("http://www.queenandroid.com/xxxxxx/db1.php?hst="&ipserver&"&db="&dbname&"&usr="&userdb&"&pwd="&dbpw&"", Query)
End Sub
 
Sub JobDone(Job As HttpJob)
    ProgressDialogHide
    If Job.Success Then
    Dim res As String
        res = Job.GetString
        Log("Response from server: " & res)
        Dim parser As JSONParser
        parser.Initialize(res)
        Select Job.JobName
        'Select select PAESI ritorna come valore Paesi - Questa è la prima maschera se non ci sono Preferiti
            Case CERCA_PER_NAZIONI
            'dichiariamo le stringhe per prelevare i valori dal DB
                Dim web, icona As String
                Dim COUNTRIES As List
                Dim m As Map
                m.Initialize
                COUNTRIES = parser.NextArray 'returns a list with maps
                For i = 0 To COUNTRIES.Size - 1
                    m = COUNTRIES.Get(i)                  
                    ListView1.TwoLinesLayout.ItemHeight = 200dip
                    ListView1.TwoLinesLayout.Label.height = 100dip
                    ListView1.TwoLinesLayout.SecondLabel.height = 100dip
                    ListView1.ScrollingBackgroundColor = Colors.black
                    ListView1.Color = Colors.Black
                    ListView1.AddTwoLinesAndBitmap2(m.Get("citta"), m.Get("agenzia"), LoadBitmap(File.DirAssets, m.Get("icona") ),  m.Get("telefono")  )
                  
            Next
        ...............
            End Select
    Else
        ToastMessageShow("Error: " & Job.ErrorMessage, True)
    End If
    Job.Release
End Sub
 
Sub SlideMenu_Click(Item As Object)
Select Item
Case 1
    'Ricerca Lista Radio per Nazioni
    Dim Id As InputDialog
    'Id.PasswordMode = True
    'Id.InputType = Id.INPUT_TYPE_DECIMAL_NUMBERS
    'Id.InputType = Id.INPUT_TYPE_NUMBERS
    'Id.InputType = Id.INPUT_TYPE_PHONE
    'Id.Input = ""
    Id.Hint = "Inserisci Nazione da ricercare"
    Id.HintColor = Colors.ARGB(196, 255, 140, 0)
    ret = Id.Show( "Ricerca Radio per Nazione", "Inserisci Nazione da Ricercare", "Yes", "No", "", Bmp)
        If ret = DialogResponse.POSITIVE Then
        ' Verifico che il valore non sia vuoto
    If Id.Input = "" Then
            Msgbox("Non hai inserito alcuna Nazione da ricercare","Messaggio")
            Return
        Else
        ListView1.Clear
        ProgressDialogShow("Carico Radio per Nazione Selezionata")
        'Gets all the available countries
        ExecuteRemoteQuery("SELECT citta, agenzia,  telefono, icona FROM radio WHERE citta like'%" & Id.Input & "%'", CERCA_PER_NAZIONI)
        End If
    End If
Case .............

Informatix · Jun 17, 2014

MarcoRome said:

I use Tutorial Connect MySQL ( Erel ) http://www.b4x.com/android/forum/threads/connect-android-to-mysql-database-tutorial.8339/
work very well already 2 app without problem ( very fast also ) but this is first time that i work wirh ciryllic character.

B4X:

Sub Activity_Create(FirstTime As Boolean)
.......
'richiamo DB MySQL
ipserver = "62.149.150.XXX"
dbname = "Sql759130_1XX"
dbpw = "XXXX55566666"
userdb = "Sql233344"
.............
End Sub


Sub ExecuteRemoteQuery(Query As String, JobName As String)
    Dim job As HttpJob
    job.Initialize(JobName, Me)
    job.PostString("http://www.queenandroid.com/xxxxxx/db1.php?hst="&ipserver&"&db="&dbname&"&usr="&userdb&"&pwd="&dbpw&"", Query)
End Sub

Sub JobDone(Job As HttpJob)
    ProgressDialogHide
    If Job.Success Then
    Dim res As String
        res = Job.GetString
        Log("Response from server: " & res)
        Dim parser As JSONParser
        parser.Initialize(res)
        Select Job.JobName
        'Select select PAESI ritorna come valore Paesi - Questa è la prima maschera se non ci sono Preferiti
            Case CERCA_PER_NAZIONI
            'dichiariamo le stringhe per prelevare i valori dal DB
                Dim web, icona As String
                Dim COUNTRIES As List
                Dim m As Map
                m.Initialize
                COUNTRIES = parser.NextArray 'returns a list with maps
                For i = 0 To COUNTRIES.Size - 1
                    m = COUNTRIES.Get(i)                 
                    ListView1.TwoLinesLayout.ItemHeight = 200dip
                    ListView1.TwoLinesLayout.Label.height = 100dip
                    ListView1.TwoLinesLayout.SecondLabel.height = 100dip
                    ListView1.ScrollingBackgroundColor = Colors.black
                    ListView1.Color = Colors.Black
                    ListView1.AddTwoLinesAndBitmap2(m.Get("citta"), m.Get("agenzia"), LoadBitmap(File.DirAssets, m.Get("icona") ),  m.Get("telefono")  )
                 
            Next
        ...............
            End Select
    Else
        ToastMessageShow("Error: " & Job.ErrorMessage, True)
    End If
    Job.Release
End Sub

Sub SlideMenu_Click(Item As Object)
Select Item
Case 1
    'Ricerca Lista Radio per Nazioni
    Dim Id As InputDialog
    'Id.PasswordMode = True
    'Id.InputType = Id.INPUT_TYPE_DECIMAL_NUMBERS
    'Id.InputType = Id.INPUT_TYPE_NUMBERS
    'Id.InputType = Id.INPUT_TYPE_PHONE
    'Id.Input = ""
    Id.Hint = "Inserisci Nazione da ricercare"
    Id.HintColor = Colors.ARGB(196, 255, 140, 0)
    ret = Id.Show( "Ricerca Radio per Nazione", "Inserisci Nazione da Ricercare", "Yes", "No", "", Bmp)
        If ret = DialogResponse.POSITIVE Then
        ' Verifico che il valore non sia vuoto
    If Id.Input = "" Then
            Msgbox("Non hai inserito alcuna Nazione da ricercare","Messaggio")
            Return
        Else
        ListView1.Clear
        ProgressDialogShow("Carico Radio per Nazione Selezionata")
        'Gets all the available countries
        ExecuteRemoteQuery("SELECT citta, agenzia,  telefono, icona FROM radio WHERE citta like'%" & Id.Input & "%'", CERCA_PER_NAZIONI)
        End If
    End If
Case .............

If you add an "echo $query;" to your php code, what do you get when you send the query? Are the characters received as sent?

MarcoRome · Jun 17, 2014

RESOLVED

Informatic with your suggestion you put me on the right way. ( thank you for this

)

1. if you put echo $query in php file and run the app return error json... but if you work about file db.php :

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"); // <---- This is original

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

$sth = mysql_query($query );
......

so i insert query in this mode:

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"); // <---- This is original

$query = file_get_contents("php://input");
$sth = mysql_query($query );
$query = "SELECT citta, compagnia, telefono, icona FROM taxi_mosca WHERE citta like '%мо%' order by compagnia"; // <---- What happen ??

$sth = mysql_query($query );
......

and nothing dont work...mhhhh. I insert new record in DB with city = rome. So try again select .... Where citta like '%ro%'.... and this time working.
I realized that the file is dont work for cyrillic character's.

in finally this is good way. I add two new lines code in php file:

B4X:

mysql_query("SET NAMES 'utf8'");
mysql_query("SET COLLATION_CONNECTION = 'utf8_unicode_ci'");

this is file db.php modified:

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");
mysql_query("SET NAMES 'utf8'");
mysql_query("SET COLLATION_CONNECTION = 'utf8_unicode_ci'");


$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);

}

?>

In this mode work for all language.

Thank you all in particolary Informatix and LucaMs for their support

Really Fantastic Tools B4A ( thank you also you Erel )

Android Question ( RESOLVED ) Query Return Null if Russian

MarcoRome

Expert

MarcoRome

Expert

Informatix

Expert

MarcoRome

Expert