[TUTORIAL] Connecting to MySQL database

Problems with WiFi, SQLite ,Bluetooth, WiMax, Proxies, etc...

[TUTORIAL] Connecting to MySQL database

Postby whitehat » Fri Dec 31, 2010 12:01 pm

The original tutorial can be found here:
http://www.helloandroid.com/tutorials/c ... l-database
On stackoverflow other users also werent able to get this running.
The guy's on there seem to improved the code becouse in eclipse i got errors all over with the first tutorial.
I borrowed some code from the stackoverflow site and fixed the eclipse errors with it.
Now i'm able to output the content on my mobile.


The java file
Code: Select all
package com.connector;

import java.io.BufferedReader;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.util.ArrayList;

import org.apache.http.HttpEntity;
import org.apache.http.HttpResponse;
import org.apache.http.NameValuePair;
import org.apache.http.client.HttpClient;
import org.apache.http.client.entity.UrlEncodedFormEntity;
import org.apache.http.client.methods.HttpPost;
import org.apache.http.impl.client.DefaultHttpClient;
import org.apache.http.message.BasicNameValuePair;
import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

import android.app.Activity;
import android.os.Bundle;
import android.util.Log;
import android.widget.LinearLayout;
import android.widget.TextView;


public class whitehat extends Activity {
/** Called when the activity is first created. */
   
   TextView txt;
@Override
public void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.main);
    // Create a crude view - this should really be set via the layout resources 
    // but since its an example saves declaring them in the XML. 
    LinearLayout rootLayout = new LinearLayout(getApplicationContext()); 
    txt = new TextView(getApplicationContext()); 
    rootLayout.addView(txt); 
    setContentView(rootLayout); 

    // Set the text and call the connect function. 
    txt.setText("Connecting...");
  //call the method to run the data retreival
    txt.setText(getServerData(KEY_121));



}
public static final String KEY_121 = "http://xx.xx.xxx.xxx/hellomysql/mysqlcon.php"; //i use my real ip here



private String getServerData(String returnString) {
   
   InputStream is = null;
   
   String result = "";
    //the year data to send
    ArrayList<NameValuePair> nameValuePairs = new ArrayList<NameValuePair>();
    nameValuePairs.add(new BasicNameValuePair("year","1970"));

    //http post
    try{
            HttpClient httpclient = new DefaultHttpClient();
            HttpPost httppost = new HttpPost(KEY_121);
            httppost.setEntity(new UrlEncodedFormEntity(nameValuePairs));
            HttpResponse response = httpclient.execute(httppost);
            HttpEntity entity = response.getEntity();
            is = entity.getContent();

    }catch(Exception e){
            Log.e("log_tag", "Error in http connection "+e.toString());
    }

    //convert response to string
    try{
            BufferedReader reader = new BufferedReader(new InputStreamReader(is,"iso-8859-1"),8);
            StringBuilder sb = new StringBuilder();
            String line = null;
            while ((line = reader.readLine()) != null) {
                    sb.append(line + "\n");
            }
            is.close();
            result=sb.toString();
    }catch(Exception e){
            Log.e("log_tag", "Error converting result "+e.toString());
    }
    //parse json data
    try{
            JSONArray jArray = new JSONArray(result);
            for(int i=0;i<jArray.length();i++){
                    JSONObject json_data = jArray.getJSONObject(i);
                    Log.i("log_tag","id: "+json_data.getInt("id")+
                            ", name: "+json_data.getString("name")+
                            ", sex: "+json_data.getInt("sex")+
                            ", birthyear: "+json_data.getInt("birthyear")
                    );
                    //Get an output to the screen
                    returnString += "\n\t" + jArray.getJSONObject(i);
            }
    }catch(JSONException e){
            Log.e("log_tag", "Error parsing data "+e.toString());
    }
    return returnString;
}   
   
}

###############################################################################
The php file:
Code: Select all
<?php

      mysql_connect("127.0.0.1","root","xxpasswordxx");

      mysql_select_db("peopledata");

      $q=mysql_query("SELECT * FROM people WHERE birthyear>'".$_REQUEST['year']."'");

      while($e=mysql_fetch_assoc($q))

              $output[]=$e;

           print(json_encode($output));
     
    mysql_close();
?>

########################################################
The sql table needed for your db:
Code: Select all
      CREATE TABLE people (
   
      id INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
   
      name VARCHAR( 100 ) NOT NULL ,
   
      sex BOOL NOT NULL DEFAULT '1',
   
      birthyear INT NOT NULL
   
      )

Manualy add some value's to it
#####################################################################
The androidmanifest.xml:

Code: Select all
<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
      package="com.connector"
      android:versionCode="1"
      android:versionName="1.0">
    <application android:icon="@drawable/icon" android:label="@string/app_name">
        <activity android:name=".whitehat"
                  android:label="@string/app_name">
            <intent-filter>
                <action android:name="android.intent.action.MAIN" />
                <category android:name="android.intent.category.LAUNCHER" />
            </intent-filter>
        </activity>

    </application>

<uses-permission android:name="android.permission.INTERNET" />
</manifest>

##########################################################################
I spended many hours and had to configue my server to get access over my dynamic ip, to make sure my server is accesseble.
#############################################################################
NOTE: nameValuePairs.add(new BasicNameValuePair("year","1970"));
if you fill your db and want to see the output...make sure you insert birtdays AFTER 1970 OR change the date to your desired year.
Last edited by whitehat on Fri Dec 31, 2010 2:35 pm, edited 1 time in total.
whitehat
Freshman
Freshman
 
Posts: 4
Joined: Thu Dec 30, 2010 11:43 pm

Top

Re: [TUTORIAL] Connecting to MySQL database

Postby dearg » Sun Jan 02, 2011 4:15 pm

Hi,

Very good tutorial, I noted your comment:

whitehat wrote:##########################################################################
I spended many hours and had to configue my server to get access over my dynamic ip, to make sure my server is accesseble.
#############################################################################


and I was just wondering how you went about configuring your server to make this possible?
I am developing an app at the moment and am having difficulty doing this.

Thank you,
Dearg
dearg
Freshman
Freshman
 
Posts: 4
Joined: Sun Jan 02, 2011 3:52 pm

Re: [TUTORIAL] Connecting to MySQL database

Postby whitehat » Sun Jan 02, 2011 4:41 pm

I had to tell my router to use the dynamic ip (the ip you get from your isp) for my device,in my case a laptop.
When call the php file , i simply could use "localhost","root","mypass" to connect to phpmyadmin.
When create the app... use your real ip (the ip you get from your isp).

There must also be other methodes , for example connect with your mobile device true your local wifi network.
Try to connect to your server with for example 192.168.1.XX
Normaly it isnt so hard, just my hardware didnt want to corporate.
whitehat
Freshman
Freshman
 
Posts: 4
Joined: Thu Dec 30, 2010 11:43 pm

Re: [TUTORIAL] Connecting to MySQL database

Postby dearg » Wed Jan 05, 2011 1:46 am

Cheers for the reply, I got my problem sorted now:)
dearg
Freshman
Freshman
 
Posts: 4
Joined: Sun Jan 02, 2011 3:52 pm

Re: [TUTORIAL] Connecting to MySQL database

Postby Johanovski » Tue Jan 18, 2011 3:27 pm

Hi there!

It's a good tutorial for connecting to an online database, but... How can one connect with a local database? I've spent hours trying to do it but I don't know where to store the database file (I've read some things relating an "assets" directory but it didn't even exist in my project directory), how to link it within the application (AssetsManager? File?)... If someone knows how to do this and can explain me in a simple way it'll be a great help!

Thanks in advance! :)
User avatar
Johanovski
Freshman
Freshman
 
Posts: 8
Joined: Thu Jan 13, 2011 4:08 pm
Location: Vic (Barcelona), Spain

Re: [TUTORIAL] Connecting to MySQL database

Postby R3quiem » Thu Jan 20, 2011 4:59 pm

Your Code is super-useful, even for such a beginners like me :oops:
I've been playing around with your code and so far I have been able to understand pretty much everything.
However, the layout is "killing me". I have no idea how i can NOT display the information in the linear xml layout that you programmed. More explicit, how can i display individually the information (id, name, etc) each one in an "autoCompleteTextView" for example, or a TextView or something else.
Could you please give me a hand?.

Thanks a lot and once again, really well done!
R3quiem
Once Poster
Once Poster
 
Posts: 1
Joined: Thu Jan 20, 2011 4:49 pm

Top

Re: [TUTORIAL] Connecting to MySQL database

Postby codygreenhalgh » Fri Jan 21, 2011 1:04 am

Johanovski wrote:Hi there!

It's a good tutorial for connecting to an online database, but... How can one connect with a local database? I've spent hours trying to do it but I don't know where to store the database file (I've read some things relating an "assets" directory but it didn't even exist in my project directory), how to link it within the application (AssetsManager? File?)... If someone knows how to do this and can explain me in a simple way it'll be a great help!

Thanks in advance! :)


Probably not the best way, and you will want to add error checking, etc but here it goes:

Code: Select all
String SAMPLE_DB_NAME = "peopleDB";
String SAMPLE_TABLE_NAME = "people";
ArrayList<String> NameList = new ArrayList<String>();

//Create the Database
OSDB = this.openOrCreateDatabase(SAMPLE_DB_NAME, MODE_PRIVATE, null);
OSDB.execSQL("CREATE TABLE IF NOT EXISTS " +
             SAMPLE_TABLE_NAME +
             " (id INTEGER , name VARCHAR," +
             " sex INTEGER, birthyear VARCHAR);");

//BASIC INSERT
OSDB.execSQL("INSERT INTO " + SAMPLE_TABLE_NAME + " Values (1,'Fred Flintstone',1,'1950');");

//BASIC SELECT and loop through results
Cursor OSRS = OSDB.rawQuery("SELECT name,birthyear FROM " + SAMPLE_TABLE_NAME, null);
           
if (OSRS != null ) {
     if  (OSRS.moveToFirst()) {
        do
        {
           String name = OSRS.getString(OSRS.getColumnIndex("name"));
           String birthyear = OSRS.getString(OSRS.getColumnIndex("birthyear"));
                                       
           //Do something with the data like add to a list
           NameList.add("" + name + " - " + birthyear);
        }
        while (OSRS.moveToNext());
    }
}                   


This way works for me, I am sure there are better ways, but this might get you started.
codygreenhalgh
Once Poster
Once Poster
 
Posts: 1
Joined: Fri Jan 21, 2011 12:47 am

Re: [TUTORIAL] Connecting to MySQL database

Postby ef em el » Sat Jan 29, 2011 8:14 pm

This was extremely helpful - would it be possible to get help sending data to these same tables? Instead of searching the database for entries after that year etc, could you possibly (please) show how to create a new entry with the 4 variables?
ef em el
Junior Developer
Junior Developer
 
Posts: 18
Joined: Wed Dec 22, 2010 9:16 am

Re: [TUTORIAL] Connecting to MySQL database

Postby ajvan » Tue Feb 01, 2011 9:21 pm

One more tutorial (actually 5) can be found at: http://computersight.com/computers/andr ... -database/

tutorials describe setting eclipse IDE, creating database and insert some records, creating modal screen for user data and more...
ajvan
Junior Developer
Junior Developer
 
Posts: 10
Joined: Sat Mar 13, 2010 10:36 am

Re: [TUTORIAL] Connecting to MySQL database

Postby schofeild » Thu Feb 17, 2011 10:32 am

hey,
hey i tried this code
it really helped me understand how to connect extrnal database with android but it is not working for me
It doent show any errors but on emulator only the url that i gave is appearing!
im using mysql
n working on localhost

my connection string was:
public static final String KEY_121 = "http://10.0.2.2/instructions/mysqlcon.php";
(i used 10.0.2.2 as i am working on local host)

when i checked my logcat it showed me dis:

launch timeout has expired, giving up a wake lock!!
error parsing data org.json.JsonException

i really need help in dis as it is my final year project....any help will be really appreciated...
waiting for ur reply soon!
schofeild
Developer
Developer
 
Posts: 40
Joined: Wed Jul 21, 2010 12:08 pm

Re: [TUTORIAL] Connecting to MySQL database

Postby snoopy007 » Sat Feb 26, 2011 5:10 am

Hello, I used your code and its working very well. Thanks
Last edited by snoopy007 on Sat Feb 26, 2011 6:38 am, edited 1 time in total.
snoopy007
Freshman
Freshman
 
Posts: 5
Joined: Sat Feb 26, 2011 4:57 am

Re: [TUTORIAL] Connecting to MySQL database

Postby snoopy007 » Sat Feb 26, 2011 5:15 am

Hi schofeild, Try to create a new avd. It worked for me
snoopy007
Freshman
Freshman
 
Posts: 5
Joined: Sat Feb 26, 2011 4:57 am

Re: [TUTORIAL] Connecting to MySQL database

Postby schofeild » Sat Feb 26, 2011 6:03 am

hey snoopy,
thanks a lot for the suggestion.....can u plzzz tell me which android version are u using...i am currently using android sdk 2.2 and did u try connect to connect to localhost or a web server...i am currently trying to connect to a localhost using special aliase 10.0.2.2...waiting for reply :|
schofeild
Developer
Developer
 
Posts: 40
Joined: Wed Jul 21, 2010 12:08 pm

Re: [TUTORIAL] Connecting to MySQL database

Postby snoopy007 » Sat Feb 26, 2011 6:37 am

Yes schofeild am using android 2.2, working on localhost, and using 10.0.2.2
Yesterday i tried the whole day to connect to my DB but only the URL was showing on the screen.
Then I deleted the avd and created a new one and OLAA it worked: Am still dancing :D
I know it doesn't make any sense !! I hope it will be magical for you too. Good luck ;)
snoopy007
Freshman
Freshman
 
Posts: 5
Joined: Sat Feb 26, 2011 4:57 am

Re: [TUTORIAL] Connecting to MySQL database

Postby schofeild » Sat Feb 26, 2011 9:10 am

no buddy it didnt work for me...i am still getting the url on my screen...dont know what the problem is?? :roll:

hey snoopy if u dont mind could u plzzz post ur php file and also provide the address of the php file...
Last edited by schofeild on Sat Feb 26, 2011 9:19 am, edited 1 time in total.
schofeild
Developer
Developer
 
Posts: 40
Joined: Wed Jul 21, 2010 12:08 pm

Top
Next

Return to Networking & Database Problems

Who is online

Users browsing this forum: Yahoo [Bot] and 7 guests