You have to think of SQL server as the big rig truck. You climb inside, fire up the big diesel truck, and get it running.
So, first on the list is have you setup SQL server to allow external connections? In effect, this has little to do with B4A.
If you are on a home network or some kind of common network? Then if your phone is on the same network, then this can work rather easy.
I have included a working sample. I am on a home network – using my cable modem + router from my internet provider. You don’t even have to open any ports or do anything to the router (assuming on your local/home network).
Then your phone on the SAME network can connect rather easy. The B4A part is relative easy.
the sample has a config screen like this:
So, you can enter all of your settings. And the two sql windows?
Well, the first window is for the main display grid. So, it should have 4 columns, the FIRST being ID (PK), and then the next 3 columns of your choosing. SELECT * from MyTable would and should work if the first column is ID.
It would be relative easy to tweak my sample to pull the first column and use that in place of “ID”. But this is a quick and dirty test application.
The sql window below that is the details view. SELECT * from tblTest should work.
So, once the “test” connection button above works, then back key to the main display.
Hit the connect button. If it connects, then the show data button will appear. Hit that, and you
see this:
If you tap on a row (or hit the view button on each row, then the whole record will display like this:
(it using the 2nd sql from the config screen).
So, you can give the sample a try.
You have to copy the 2 extra files attached here (the jdbc library, and the driver) to your library folder that you setup in B4A (you setup one - right?).
Assumptions for above:
You are directly connecting to SQL server.
Your android phone is on your same network. (Wi-Fi) – say on your home network.
You have SQL server running (likely SQLEXPRESS)
You have the SQL server browser service running. (You REALLY need to ensure that is running).
edit: Note that even for "min" installs and setup - the sql browser service is running.
so, you don't have to run ANYTHING else other then sql server. The browser" service is this one:
Edit#2:
Note that this is a jdbc type 4 connection. However, the connecting to a jdbc2 system should and could work with min or VERY few mods. So, I might try and post a example. Such an example would require the jdbc2 type "server" running on your computer, but that would enable say connecting to Excel, or even MS Access. But, I think the above SQL server setup is the LEAST amount of effort, since nothing more then a MOST basic install of SQL (even free express edition) is all that is required here. I am using this setup to sync a sqlite database with SQL server this way (my time and billing app). So, I was lazy, and did not want to worry about having to setup anything else on my computer other then a free edition of SQL server.
The above "browser" service is what tranlates your computer IP address to the running instance of SQL express. So, this means you can (and should) just enter the IP address of your computer in the config, and the above service is what "translates" SQLEXPRESS in my example to the IP address running SQL server. Note that you can "try" to use a port number, but these days, you REALLY need to have the above browser service (a IP translater) running. If you don't run that service, then you have nothing but problems connecting to SQL server. Recent editions of SQL server quite much now requite the above to be running. To my knowledge, quite sure the above is setup to run by default - but I am noting the above to save you some grey hairs).
You setup a SQL server logon and password (you can’t use windows authentication for the logon).
I would STRONG suggest that you FIRST test from another computer on the same network that you can connect to the running instance of SQL server.
So, really, 99% of this quest on your part will be setting up SQL server. 99% of your questions will thus be about SQL server, and not B4A.
So, you can give this sample a try. You can enter quite much any legitimate command in the SQL window, but the idea is that you display 4 columns in a grid. And a tap on any row will then launch a form that displays all of the records data, based on the 2nd SQL query.
And, I do have a IP scanner – that could be added to list out the computers the android sees on the network. (so a scan for computers beside the server button would be a nice addition).
So, “as is”, the test connect button will tell you if you can connect. But, as noted, the android part is not really your challenge at all – it is your SQL server skills, and do you have SQL server setup in a way that other computers on the network can connect. If other computers can connect, then this sample application should connect, and let you enter sql statements against that database.
Have fun!
Edit - March 03, 2020 - I re-uploaded the sample to show the connection string - and the select table chooser now works.
Regards,
Albert D. Kallal
Edmonton, Alberta Canada
