I was just playing with this on the weekend.
Right now, the drivers from Microsoft don’t work from Android. It has to do with wanting a SSL connection, but not making up its mind on the first connection.
However, the jtds driver does work.
You can find it here:
http://jtds.sourceforge.net/
(but I have attached it to this post)
And you also need the jDBC library for B4A
(I also attached it to this post
So, to use?
I assume you have SQL server on some network – assuming the same as the one the android wi-fi is on?
We assume that you launched SQL studio (SSMS). And can connect using the IP address and user/password. And ALSO very (but VERY!) important is that you ALWAYS are including the SQL instance here.
If you have all of the above working? (in other words you are 100% sure that say using a laptop with wi-fi can connect to the instance of sql server on your network using SSMS? (SQL management studio).
Ok next then it time to try your android on the same network – using wi-fi.
So, first up, install the jDBC library for B4A.
This can be found here:
https://www.b4x.com/android/forum/t...ly-connect-to-remote-databases.84016/#content
As noted, It’s tiny, so I have attached it to this post.
This is a standard B4A library install. (So all of the tons of great example libraries for B4A are installed this way).
You have two locations in which you “can” place the attached library, (jdbc), and the additional “driver” (jtds).
You can place these 3 files right into the B4A installed library folder:
That would be here on most computers:
C:\Program Files (x86)\Anywhere Software\Basic4android\Libraries
However, I can’t recommend using above (because it is a folder that requires elevated permissions).
So, you should use the folder for “extra” libraries. That is the folder here:
(Tools->configure paths).
So, create some library folder in which over time you will (and can and should) stuff in additional libraries that you download from this site.
In above, under “Additional Libraries” is the folder I am talking about. If you not done this yet – do make a folder some place on your computer and set the above to that location. (You likely done this, but that’s where the attached files will be placed).
So the 3 files I attached are to be placed in the above folder after you un-zip them.
At this point, you are ready to try a connection.
Ok, so you copied the above two files into your libraries folder. (In fact the 3 attached go in there).
So, unzip all these files – place them in that libraries folder. (And I would as a habit un-block the zip files before you un-zip them – gets rid of a nasty windows “security” attached to each file).
The only other significant tip and issue?
For about the last 5 years, SQL server very much “enforces” you to specify not only the server name (we will use IP address), but ALSO you must specify the SQL instance. Typical default name is SQLSERVER, or if using SQL express edition, then it very likely called SQLEXPRESS.
So when you setup the connection string – you “really” need to include the SQL instance, and the jtds driver has a “setting” for this.
It is VERY important to use the sql instance name in place of guessing what port to use. This also means that you will be running what is called the SQL “browser” service. This service mapps the dynamic IP and port that SQL server “now near always” “issues” to the running instance of SQL server. This means that in most cases you do NOT and will NOT have a default instance of SQL server setup for you (the standard installs don’t work this way anymore – you MUST specify the instance to connect to – the ports and IP address(s) are now dynamic behind the scenes!!!
This means that if you running SQL say on your DEV computer, then you want to ensure that you “always” are “always” ensure that the sql browser service is running. This is this service in the SQL config:
That service I what does the IP mapping to the running instance of SQL server. I now find that not even the SQL manager will connect UNLESS you specify the instance (just some years ago this was NOT the case!!!). And thus even sql studio often don't connect unless you running the browser service as per above.
If you are on a home network, and running sql express on your dev computer – then turn off your fire wall (you are behind a NAT anyway) on your PC. So get SSMS working and connecting to SQL server first - its too hard to debug a "un-sure" connection on your Andriod.
So, to sum all above up?
You “really” need to specify both the IP address of the sql server and ALSO the SQL instance name.
For the sake of avoiding rooms with padded walls, I specific use the supported “DatabaseName=MYdataBasename” since that goes some way to de-confusing how jtds examples specify the database name (they specify it as part of the server – and that is confusing since SQL server examples tend to use that SAME syntax to specify the sql instance – and that is beyond confusing to most people! Padded rooms anyone????
So, use DataBaseName=YourDatabase name to bring some reasonable amout of sanity here.
So, a few things:
You have to use the jtds driver – the one from MS don’t work. (I’ll look into this when I have more time).
I have found the jTDS driver works rather nice anyway.
So, place the attached 3 files into the Library folder.
So, you specify the jDBC driver here:
#Region Activity Attributes
#AdditionalJar: jtds-1.3.1
#FullScreen: False
#IncludeTitle: True
#End Region
Your connection string will look like this:
Public mysql As JdbcSQL
'jtds driver
Private driver As String = "net.sourceforge.jtds.jdbc.Driver"
Private jdbcUrl As String = "jdbc:jtds:sqlserver://192.168.0.251;DatabaseName=TESTDB3;instance=SQLEXPRESS"
Private Username As String = "TESTUSER3"
Private Password As String = "TESTPASSWORD3"
Note in above you can with jtds specify the DataBaseName and ALSO the instance as per above.
And above “driver” should have been called “driverClass”, but anyway, the above is the basic steps here.
Regards,
Albert D. Kallal
Edmonton, Alberta Canada
