Can I access a remote SQL database (on a webserver) directly from an android program? i.e. simply open up the connection with all the required parameters and then perform an SQL query?
This is a private program (not available to the public) only available on specified handsets, so I am not worried about third parties getting a hold of database access.
If so - what libraries do i need in Java?
Thanks.
This question has popped up several times. You PROBABLY can connect your android device to the SQL server directly if you deployed the MSSQL JDBC drivers to your android device and then exposed your SQL server directly to the internet. If the MSSQL drivers would work properly on Android is a completely different problem.
That is how you might be able to do it. However here is why that is a bad idea.
You are exposing your SQL server directly to the internet. Unless you encrypt the data between your MSSQL server and android device it would be relatively easy for a determined hacker to sniff the TDS data stream between the device and MSSQL and reverse engineer it and steal your data. Encryption will probably make it much harder almost impossible for a attacker to steal your data. However an attacker could still launch a DOS/DDOS attack on your database directly. Not a good idea!
If you are planning to connect other mobile devices (iPhone, Symbian, BlackBerry and so on) you will need to be able to create a SQL connection from those devices as well. iPhone does not support Java natively(from my memory) for example so you would need to find a way to connect iPhone to the SQL server. BlackBerry might be easier but Symbian you are going to be out of luck with. Thus you will need to almost create a custom solution for each device connecting to your database. Bad Idea LOADS of maintenance
Create a webservice or custom TCP/IP server which can manipulate your database. Connect to this webservice/service from your device. Webservices are the way to go. More than 90% of devices these days are natively capable of doing a webservice call.
I think you have to use webservice to communicate with a SQL database. you can define general methods like runSomeScalarQuery or runOneTabularQuery and ... in that webservice and send database responses via self defined protocol like a self defined object.
It is a very bad practice but if you need it for testing purposes, or since you dont have any other option, it is still possible to connect to an external database. And it will work fine in most cases if the data access is not too heavy.
Once this is ready, we can go ahead with the programming. As we know, Android force us to do the network access in a separate thread, so we have to choose a way of creating a Thread, for instance we can use an AsynkTask, and add our code to the doInBackground method.
private static final String url = "jdbc:mysql://{URL_SERVER}/{BASE_DATOS}";
private static final String user = "usuario";
private static final String password = "contraseña";
int count=0;
try {
// The newInstance() call is a work around for some broken Java implementations
//this creates some static objects that we need.
Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection con = DriverManager.getConnection(url, user, password);
Statement st = con.createStatement();
ResultSet rs = st.executeQuery("QUERY");
while (rs.next()) {
//in every iteration we have a result
//if query is a Select, for instance
//"select one_column, another_column from ..."
//remember that rs has functions such as
//rs.getString(0)
//rs.getDouble(1)
//etc, that get the index of the columns in the select
//and cast it to the specific type
//here we do whatever we need with the results,
//and probably keep track of the progress with something like
// publishProgress(++count);
// end early if asynctask was cancel()
if (isCancelled())
break;
}
}
catch (Exception e) {
e.printStackTrace();
}
