Android Tutorial Connect Android to MySQL Database Tutorial

Discussion in 'Tutorials & Examples' started by Erel, Mar 21, 2011.

  1. arturataide

    arturataide Member Licensed User


    Thank you very mutch..
    I didn't check the code..
    It solves my problem :D
     
  2. Malark

    Malark Member Licensed User

    Charset problem with MS-SQL database

    Hello,

    Firts of all, congratulation for this RAD tool. It's very very good and useful.

    I have a big problem with the charsets. The example program (with few modifications for MSSQL connect instead MySQL) works perfectly with my MS-SQL database, but where there is a special character (e.g.: é á) in the database, the program cuts the remain of the string by these chars. I think it must be the json_encode the problem, because the charset of the database and the charset of the PHP server is the same (latin1 - ISO 8859-1) and when I use only the PHP script with a browser (without json_encode) it works perfectly, but when I use the print json_encode($rows); code, then it is wrong both my android phone and in browsers. Is there any other way to get the datas in the android side or I must use the JSON format? I am new in B4A and PHP programming so please help me, if somebody could. :sign0085:

    The PHP code:

    <?php
    include("adodb5/adodb.inc.php");
    $db = ADONewConnection('odbc_mssql');
    $dsn = "Driver={SQL Server};Server=xxxx;Database=xxxx;";
    $db->Connect($dsn,'xxxx','xxxx') or die('fail');
    if(!$db)
    {
    die('Something went wrong while connecting to MSSQL');
    }

    $query = file_get_contents("php://input");
    $db->SetFetchMode(ADODB_FETCH_ASSOC);
    $rs = $db->Execute($query);


    while ($array = $rs->FetchRow()) {
    $rows[] = $array;
    }
    print json_encode($rows);
    ?>
     
  3. pluton

    pluton Active Member Licensed User

    Did you try to put your database and tables in it in Unicode-character ??

    For you maybe in UTF8-Unicode. Maybe
     
  4. Malark

    Malark Member Licensed User

    Thanks for the reply. You were right. The json_encode wants utf8 instead ISO-8859-1. The right way:

    $rows = array();
    while($r = mysql_fetch_assoc($sth)) {
    $r['column_name'] = utf8_encode($r['column_name']);
    $rows[] = $r;
    }
    print json_encode($rows);
     
  5. buttons

    buttons Member Licensed User

    Mmm, I have a field in my table which is a BLOB which I've loaded an image into. This solution doesn't seem to want to json encode this field returning an empty/null string instead. Is there a way round this, and then can I load it into a bitmap? thanks!
     
    Argonc likes this.
  6. pluton

    pluton Active Member Licensed User

    For BLOB fields I recomend to you to see this:

    http://www.basic4ppc.com/forum/basi...ed-tutorials/6736-sql-tutorial.html#post39108

    There you have:

    Insert BLOB

    Code:
    Sub InsertBlob
        
    'convert the image file to a bytes array
        Dim InputStream1 As InputStream
        InputStream1 = 
    File.OpenInput(File.DirAssets, "smiley.gif")
        
    Dim OutputStream1 As OutputStream
        OutputStream1.InitializeToBytesArray(
    1000)
        
    File.Copy2(InputStream1, OutputStream1)
        
    Dim Buffer() As Byte 'declares an empty array
        Buffer = OutputStream1.ToBytesArray
        
        
    'write the image to the database
        SQL1.ExecNonQuery2("INSERT INTO table2 VALUES('smiley', ?)"Array As Object(Buffer))
    End Sub
    Read BLOB

    Code:
    Sub ReadBlob
        
    Dim Cursor1 As Cursor
        
    'Using ExecQuery2 is safer as it escapes special characters automatically.
        'In this case it doesn't really matter.
        Cursor1 = SQL1.ExecQuery2("SELECT image FROM table2 WHERE name = ?"Array As String("smiley"))
        Cursor1.Position = 
    0
        
    Dim Buffer() As Byte 'declare an empty byte array
        Buffer = Cursor1.GetBlob("image")
        
    Dim InputStream1 As InputStream
        InputStream1.InitializeFromBytesArray(Buffer, 
    0, Buffer.Length)
        
        
    Dim Bitmap1 As Bitmap
        Bitmap1.Initialize2(InputStream1)
        InputStream1.Close
        
    Activity.SetBackgroundImage(Bitmap1)
    End Sub
     
    Last edited: Dec 10, 2011
  7. buttons

    buttons Member Licensed User

    Thanks for the reply but my database is a MySQL database not the SQLlite on the Android device. I have already inserted the image into the MySQL database (using PhpMyAdmin/PHP), the problem is that the json.php code given in the tutorial appears to discard it/not return it to the Android process.
     
    Argonc likes this.
  8. buttons

    buttons Member Licensed User

    I've modded my code so that the image data is JSONed after I base64_encoded it, now how can I use that, I will want to bitmap it so I can put it in a listview?

    PHP:
    for ($i=0$i<sizeof($rows); $i++) {
    // send the base64 encoding, see if b4a can do anything with that
       
    $rows[$i]['image']=base64_encode($rows[$i]['image']);
    }


    print 
    json_encode($rows);
     
    Mashiane likes this.
  9. Erel

    Erel Administrator Staff Member Licensed User

    You can use StringUtils to decode the Base64 string. Once you get the bytes array you should follow the code in post #86.
     
  10. Philipp

    Philipp Member Licensed User

    Just skimmed through the threads. For our Final School (Evening-School) -Project we too have to interact with a mysql-db. We decided to use CodeIgniter as small & secure framework. We use it with xmlrpc but you could also use json.

    Both libraries are available for b4a. The xmlrpc-lib has been newly created by XverhelstX.

    I think it's well worth taking a look at it.
     
  11. buttons

    buttons Member Licensed User

    many thanks

    Thanks everyone, got it working now. Just in case anyone else needs similar...
    Code:
    Dim su As StringUtils 
       
    Dim ImageBytes() As Byte 
       
    Dim SQLImage As Bitmap
    Code:
    Sub items
       itemid = RecordMap.Get(
    "itemid")
       
    image = RecordMap.Get("image")
       
    If image=Null OR image="" Then
             
    ' no image in database so use the standard no image file defined in Globs
             SQLImage=ImAssets.I("noimage")
          
    Else
             ImageBytes=su.DecodeBase64(
    image)
             
    Dim InputStream1 As InputStream
             InputStream1.InitializeFromBytesArray(ImageBytes, 
    0, ImageBytes.Length)   
             SQLimage.Initialize2(InputStream1)
             InputStream1.Close 
       
    End If
       desc = RecordMap.Get(
    "description")
       cat = RecordMap.Get(
    "category")
       cals = RecordMap.Get(
    "cals")
       official = RecordMap.Get(
    "officialcambridge")
       approved = RecordMap.Get(
    "approved")
    End Sub
     
  12. jegadk

    jegadk Member Licensed User

    PHP to ASP

    Hi.

    Can :sign0163: convert the PHP code to ASP ? :)
     
  13. Erel

    Erel Administrator Staff Member Licensed User

  14. mystic7

    mystic7 Member

    Stupid question but is it safe to assume that, if I were to duplicate your example exactly, hypothetically speaking, only using asp code instead of php, then all of your code would be exactly the same except that I'd be fetching countries.ASP rather than countries.PHP? In other words no other changes to your app code would be necessary?
     
  15. Erel

    Erel Administrator Staff Member Licensed User

    That is correct.
     
  16. jegadk

    jegadk Member Licensed User

    Help with sample

    Hi all.

    Need help to a little sample.

    Having mysql with dbname android, table is person, columns is ID,name,address,mobile

    startButton call asp webservice file like http://localhost:1004/ws1.asp?searchword=jesper

    Return all match records in listview with id and name. Touch record in listview and show ID,name,address,mobile in respective labels
     
  17. jegadk

    jegadk Member Licensed User

    json result from my asp page

    [{"ID":1,"name":"Jesper Hossfeld Garb\u00F8l","address":"N\u00F8rrelunden 55","zipCode":"5260","city":"Odense S.","telephone":null,"mobile":"61104913"},{"ID":2,"name":"Jesper Garb\u00F8l","address":null,"zipCode":null,"city":null,"telephone":null,"mobile":null}]
     
  18. Erel

    Erel Administrator Staff Member Licensed User

    You should start a new thread for this question.
     
  19. mystic7

    mystic7 Member

    Short, sweet, and easy to understand. Thanks!
     
  20. alfalfa

    alfalfa New Member

    I want to download the file but I can't:sign0104:
     
Loading...