USE THIS INSTEAD: https://www.b4x.com/android/forum/threads/jdbcsql-directly-connect-to-remote-databases.84016/
You can use this library to perform CRUD operations on your SQL Server.
What you can do;
1. Connect to sql server using different connection option
2. Perform Insert and Update using ResultSet
3. Perform Insert, Update, Delete using ExecuteUpdate
4. Perform Insert, Update, Delete using ExecuteUpdate2 (supports parameters)
5. Perform Select and get result using ResultSet
6. No middleman/3rd party application required
its a work in progress and will share it to you guys "as-is".
What i posted on my sample is what i have tried so far.
Here are the sample codes:
Insert/Update using ResultSet:
Insert/Update using ExecuteUpdate2:
Basic Reading of Data:
Have a problem?
1. If you are having a connection refuse, just disable your firewall or make sure that your firewall allows connection to your sqlserver port (1433).
2. Still cant connect? Enable SQL Server TCP/IP. Read more here on how to enable it: https://thusithamabotuwana.wordpress.com/2012/01/08/connecting-to-sql-server-using-jtds/
About Library:
Im using JTDS (version 1.2.5) and its included on the zip file.
1. Just extract all of it to your B4A Additional library folder.
2. Check the 'MSSQL' on your library to use it.
3. Refer to the codes/samples above.
PS: Donation is open for people would like to buy me a bread (not beer) lol!
Donate Now
You can use this library to perform CRUD operations on your SQL Server.
What you can do;
1. Connect to sql server using different connection option
2. Perform Insert and Update using ResultSet
3. Perform Insert, Update, Delete using ExecuteUpdate
4. Perform Insert, Update, Delete using ExecuteUpdate2 (supports parameters)
5. Perform Select and get result using ResultSet
6. No middleman/3rd party application required
its a work in progress and will share it to you guys "as-is".
What i posted on my sample is what i have tried so far.
Here are the sample codes:
B4X:
' open connection
sql.OpenConnection("jdbc:jtds:sqlserver://192.168.1.4/DR_MANAGER;instance_name=SQLEXPRESS;user=sa;password=systemadmin;")
' read columns
Dim res As MSSQLResultSet = sql.ExecuteQuery("SELECT count(*) FROM Table_3")
If res.MoveNext Then
Log(res.GetLong(1))
End IfInsert/Update using ResultSet:
B4X:
' open connection using different method
sql.OpenConnection1("192.168.1.4", "DR_MANAGER", "sa", "systemadmin")
' inserting using resultset, call MoveToInsertRow, update the field value and call InsertRow
Dim res As MSSQLResultSet = sql.ExecuteQuery1("SELECT * FROM Table_1", sql.TYPE_SCROLL_SENSITIVE, sql.CONCUR_UPDATABLE)
res.MoveToInsertRow
res.UpdateString2("first_name", "John")
res.UpdateString2("last_name", "cena")
res.InsertRow
' updating using resultset, just move to desired row, update the field value and call UpdateRow
sql.OpenConnection1("192.168.1.4", "DR_MANAGER", "sa", "systemadmin")
Dim res As MSSQLResultSet = sql.ExecuteQuery1("SELECT * FROM Table_1", sql.TYPE_SCROLL_SENSITIVE, sql.CONCUR_UPDATABLE)
res.MoveLast
res.UpdateString2("first_name", "Homer")
res.UpdateString2("last_name", "Simpson")
res.UpdateRowInsert/Update using ExecuteUpdate2:
B4X:
' open connection using different method
sql.OpenConnection1("192.168.1.4", "DR_MANAGER", "sa", "systemadmin")
' inserting (all text type)
Dim aff As Int = sql.ExecuteUpdate2("INSERT INTO Table_2 (first_name, middle_name, last_name) VALUES (?, ?, ?)", Array As String("a", "b", "c"))
Log(aff)
' inserting (real, int, text type)
Dim aff As Int = sql.ExecuteUpdate2("INSERT INTO Table_1 (_real, _int, _str) VALUES (?, ?, ?)", Array As String(13.4, 20, "hello"))
Log(aff)
' updating data
Dim aff As Int = sql.ExecuteUpdate2("UPDATE Table_2 SET middle_name = ? WHERE id = 10", Array As String("do'h!!"))
Log(aff)Basic Reading of Data:
B4X:
' open connection using different method
sql.OpenConnection2("jdbc:jtds:sqlserver://192.168.1.4/DR_MANAGER", "sa", "systemadmin")
' reading and looping result set
Dim res As MSSQLResultSet = sql.ExecuteQuery("SELECT * FROM tblDRHeader")
Do While res.MoveNext
Log($"${res.GetString2("DRNumber")} ${res.GetString2("RouteDescr")}"$)
LoopHave a problem?
1. If you are having a connection refuse, just disable your firewall or make sure that your firewall allows connection to your sqlserver port (1433).
2. Still cant connect? Enable SQL Server TCP/IP. Read more here on how to enable it: https://thusithamabotuwana.wordpress.com/2012/01/08/connecting-to-sql-server-using-jtds/
About Library:
Im using JTDS (version 1.2.5) and its included on the zip file.
1. Just extract all of it to your B4A Additional library folder.
2. Check the 'MSSQL' on your library to use it.
3. Refer to the codes/samples above.
PS: Donation is open for people would like to buy me a bread (not beer) lol!
Donate Now
Attachments
Last edited:
