Android Question How to Connect to MS-SQL Server 2016 in B4A

LBor

Member
Hi to all , Please let someone explain to me with an example, and without referring to other links, the method of connecting to a database MS SQL server that is on my computer (local) I was confused by the traffic between these links.
(I was read many link in this forum about this item and test all of them but i could not connect to my local server
For Example (Items that i remember) :
https://www.b4x.com/android/forum/threads/jdbcsql-directly-connect-to-remote-databases.84016
https://www.b4x.com/android/forum/threads/solved-using-windows-auth-to-connect-to-a-mssql-db.82346/
https://www.b4x.com/android/forum/threads/how-to-connect-to-mssql-with-jdbcsql.95389/
https://www.b4x.com/android/forum/threads/mssql-jdbc-minimalistic-example.90548/
https://www.b4x.com/android/forum/threads/starting-with-mssql-with-jtds-1-3-1.107576/
https://www.b4x.com/android/forum/threads/solved-jdbcsql-and-sql-server.98554/
best link :
https://www.b4x.com/android/forum/threads/how-to-connect-to-sql-server-using-jtds-1-3-1-jar.111446/
)
Exactly what I want:
1- connect to my database in B4A (MS SQL Server 2016 and Name of database for example : totalsystem
2- run command query or stored procedure
thanks a lot
 

Erel

Administrator
Staff member
Licensed User
There are two options:
- Connect to the database directly with JdbcSQL
- Run jRDC2 server on the PC as a middleware.

In all cases you need to configure Windows firewall and allow incoming connections on the relevant port.

This thread looks like a good start: https://www.b4x.com/android/forum/threads/how-to-connect-to-mssql-with-jdbcsql.95389/
There is no reason that we will writing the same content again. You should explain what you are doing and what isn't working.
 
Upvote 0

LBor

Member
...
There is no reason that we will writing the same content again. You should explain what you are doing and what isn't working.
Hi ,
Dear Friend , at first thanks for replay .
yes , you right , but after reading what I mentioned, I wouldn't need a new post if I could connect to the database. So, my friend, despite all the tips that were out there and the sample programs that were attached, I couldn't connect to the database and so I asked for help. Referencing those links will not help my servant.

thank you
 
Upvote 0

Peter Simpson

Expert
Licensed User
Hi to all , Please let someone explain to me with an example, and without referring to other links, the method of connecting to a database MS SQL server that is on my computer (local) I was confused by the traffic between these links.
(I was read many link in this forum about this item and test all of them but i could not connect to my local server
For Example (Items that i remember) :
https://www.b4x.com/android/forum/threads/jdbcsql-directly-connect-to-remote-databases.84016
https://www.b4x.com/android/forum/threads/solved-using-windows-auth-to-connect-to-a-mssql-db.82346/
https://www.b4x.com/android/forum/threads/how-to-connect-to-mssql-with-jdbcsql.95389/
https://www.b4x.com/android/forum/threads/mssql-jdbc-minimalistic-example.90548/
https://www.b4x.com/android/forum/threads/starting-with-mssql-with-jtds-1-3-1.107576/
https://www.b4x.com/android/forum/threads/solved-jdbcsql-and-sql-server.98554/
best link :
https://www.b4x.com/android/forum/threads/how-to-connect-to-sql-server-using-jtds-1-3-1-jar.111446/
)
Exactly what I want:
1- connect to my database in B4A (MS SQL Server 2016 and Name of database for example : totalsystem
2- run command query or stored procedure
thanks a lot
Really, you missed one, and here is the link that you do not want to it 😁
Even though it's code for B4J, you should find that the section of code that you need to MS-SQL should also work in B4A.

I'm not going to sit here for ages explaining something (as you have requested) when the code is already in the link above...

Enjoy...
 
Last edited:
Upvote 0

Alex_197

Active Member
Licensed User
Hi to all , Please let someone explain to me with an example, and without referring to other links, the method of connecting to a database MS SQL server that is on my computer (local) I was confused by the traffic between these links.
(I was read many link in this forum about this item and test all of them but i could not connect to my local server
For Example (Items that i remember) :
https://www.b4x.com/android/forum/threads/jdbcsql-directly-connect-to-remote-databases.84016
https://www.b4x.com/android/forum/threads/solved-using-windows-auth-to-connect-to-a-mssql-db.82346/
https://www.b4x.com/android/forum/threads/how-to-connect-to-mssql-with-jdbcsql.95389/
https://www.b4x.com/android/forum/threads/mssql-jdbc-minimalistic-example.90548/
https://www.b4x.com/android/forum/threads/starting-with-mssql-with-jtds-1-3-1.107576/
https://www.b4x.com/android/forum/threads/solved-jdbcsql-and-sql-server.98554/
best link :
https://www.b4x.com/android/forum/threads/how-to-connect-to-sql-server-using-jtds-1-3-1-jar.111446/
)
Exactly what I want:
1- connect to my database in B4A (MS SQL Server 2016 and Name of database for example : totalsystem
2- run command query or stored procedure
thanks a lot
create ASP.NET page that connected to your sql server and call this page from your app by using httpjob and send criteria to this page.

Something like this

Connect to SQL:
Dim j As HttpJob
            j.Initialize("", Me)
        
            j.PostString(modFun.URL,Params)
        
            Wait For (j) JobDone(j As HttpJob)
            If j.Success Then
            
                response =J.GetString
                IsOk=DoApplyResponse(response)
                Log("ParamsNotes Sent")
            
            Else
                ProgressDialogHide
                IsOk=False
                Msgbox("Server unreachable, try again","HCMS")
            End If
        
            j.Release
 
Upvote 0

Peter Simpson

Expert
Licensed User
create ASP.NET page that connected to your sql server and call this page from your app by using httpjob and send criteria to this page.
What makes you think that @LBor knows any ASP.Net, he might do but I personally don't know?

Granted ASP.net is easy enough technology to use but still, he should be trying to work with solutions that are already on this community. If there is ASP.Net code already on here for him to use then that's fare enough, but I've not seen any. In that case he might as well be using jRDC2 instead of ASP.Net as there are plenty of examples of jRDC2 in action fully working already on this forum. He wanted actual code, but I gave him a link to all the code that he will ever need on it 😁

Cheers...
 
Upvote 0

Albert Kallal

Active Member
Licensed User
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:
1584033170852.png



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:

1584033280867.png



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).
1584033330574.png



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:

1584035022370.png


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
 

Attachments

  • jtds-ds.zip
    300.6 KB · Views: 269
  • jtds-1.3.1.jar
    310.4 KB · Views: 244
  • SQLTEST.zip
    22.2 KB · Views: 284
Last edited:
Upvote 0

LBor

Member
Hi all friends and thank you for your kind and patient reply and guidance. As you know, in most programming languages, connecting to a database is one of the first and is elementary tasks. This should be the case here as well. I have read and tested all your tips. In the end I realized that the problem was somewhere else that I didn't even think about. Collation of my database is Persian_100_CI_AS and therefore connection was not established yet and all the problem was there and all your programs dear ones were working properly. Thank you all.
 
Upvote 0
Top