Solution 1 :
You don’t connect the Android to the tables on the server. While there are number of “similar” ODBC drives for Android? In this case jdbc drivers for Android? Such drivers still require you to install + setup some kind of web service on the web site. The basic reason is many, but some are:
A live active constant technology say like ODBC etc. is not practical, since web based (and most of Android software) is based around a web approach.
That means the technology stack has to handle disconnects – even small ones are to be expected. And that means you can’t maintain a socket connection to the database. So you don’t and can’t expect and keep a live connection open to the hosted database. So, you use web service calls, since just like web pages, they don’t care if the connection drops for a wee bit. (you assume as a develoepr that you re-connect for each request – just like you do when requesting a web page). So a live kept alive connection to some network resource on the web server like folders, or in this case some database server is not used.
Well, quite a few years ago, I decided to open up the ports to SQL server to the wild and crazy internet. I thus could make a direct connection to the database from a mobile device. (thus by-passing the web site). This was back in 2004 (16 years ago). In less then 20 minutes, I started to see “bots” attempting to logon to sql server. I saw:
logon: "sa", Password:"sa" logon: "sa", Password:"password" logon: "sa", Password:"12345678"
And so on!!!! That was AFTER ONLY 20 minutes, and way back in 2004! Can you imagine this now? There is 100 times more roving bots, looking to grab and connect to your sql server, and steal your data.
So, opening a database to direct OUTSIDE connections to the REALLY nasty and wild internet? It simply not done – too risky.
You can buy a book and connect to amazon.com web site. However, what you can’t do is BY-PASS the web server and connect to the database that drives the amazon site. So, zero external connections are allowed to the outside world for that database. So their web site can hit and direct connect to the database, but NOT the outside nor you or in this case your phone.
So, in near all cases? The web site DOES have rights and ability to interact with the database. So, to have a secure setup? You build a web service and expose that! That way you never expose the database to direct connections.
So, what a few enterprising people have done? Well, they built a jdBC driver that runs on Android, and it interacts with a web service that amounts to a sql like driver. The end result is a developer experience “similar” to a direct connection. A good number of these web service “drivers” are written in PHP, but they don’t have to be. I would suspect that some jdbc “web service” written in .net exists. So, you are by no means limited to PHP.
So, today? You will find that most web hosting providers do NOT allow outside database connections. I know one of mine stopped supporting this some years ago (too dangerous – too many data breaches).
Azure (from Microsoft – cloud OS) does still allow direct connections to the database (and thus by-pass the web site). However, this setup also tends to be restricted by IP address. (so once again, the wild west and any old IP address can’t connect. Say, only your fixed work location IP can be used).
It really comes down to how much interaction your Android requires with the database behind the web site you plan to use. A few web service calls, such as getorders, update a order or whatever tends to be all you require anyway.
Wiring out some web service calls in asp.net is really a dream. (you just mark a sub/function as public, and set it as a with a compiler directive. So, it amazing easy to do. You then make web service calls from Android. So, in most cases writing such Andriod applciatons has to interact with the web site anyway – might as well then use that for getting (or sending) data to/from the database.
So web service calls are used here – and the main reason is web technology can handle dis-connects, tends to be state-less, and that you in general don’t want to expose direct connections to the database. A direct connection to the database would also mean NOW the database has to handle secuirty, the logons and user rights as opposed to web severs which are VERY much designed to handle logons etc.
The web based security is FAR more battle hardened, and thus you get levels of security that all web servers tend to have built in.
So, you don’t connect to a database. You interact with a web service, and the Android development platforms are optimized for making web service calls, and any developer system for Android will be rich in its ability to interact with a web service as opposed to say desktop development where ADO.net or ODBC and direct connections to a database are common.
Such direct connections to a database are not common with Andriod. The only great support we see is for a local android database, and sqlite databases (so that direct connection concept is alive and well for a local database – and even then you often will use some form of a jdbc stack for this. That code and stack can often also use a jdbc exposed web service – which as noted is often a PHP package. Such a stack can be nice, since you thus do get to code somewhat similar to desktop software, but you are using a web service at the other end.
For most applications? A few web methods should suffice to get data back and forth to your android device.
I want to create a mobile app for a asp.net website that I have created.
how do I connect mobile app to SQL Server? Is PHP script the only way to link the mobile app to database?