B4A Library SQL Server Library

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:

  ' 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 If

Insert/Update using ResultSet:

    ' 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.UpdateRow

Insert/Update using ExecuteUpdate2:

    ' 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:

    ' 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")}"$)
   Loop

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

 

[SNOUHyhQs2]

BTW, I only have tried it to MS SQL Server. I havent tried it to other server database but if JTDS supports it (any database) then it should work.

 

[Mashiane]

Brilliant, keep it up! will definately test this out!!

 

[KKCHAN]

BTW, I only have tried it to MS SQL Server. I havent tried it to other server database but if JTDS supports it (any database) then it should work.

Can you give us a sample project ?

 

[Venox]

Great job.
Run on debug mode, but on release mode an error occurs:
java.lang.NullPointerException: Attempt to invoke virtual method 'int java.lang.String.lenght()' on a null object reference

Any idea?

 

[oscarsarrio]

Same problem only works in debug mode

 

[CLEBER]

Hi, I'm having the same problem, have any solutions to the problem?
Thank you
java.lang.NullPointerException: Attempt to invoke virtual method 'int java.lang.String.lenght()

 

[Erel]

You should use JdbcSQL library.

 

[moh_samy_farag]

I had the same error when i run the app in Release mode
java.lang.NullPointerException: Attempt to invoke virtual method 'int java.lang.String.lenght()

It resolved when i call Initialize before OpenConnection