B4A Library SQL Server Library

Discussion in 'Additional libraries, classes and official updates' started by SNOUHyhQs2, Oct 17, 2016.

  1. SNOUHyhQs2

    SNOUHyhQs2 Member Licensed User

    Im still new in creating/wrapping library and im going to share what i have done so far.

    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:

    Code:
    ' 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:
    Code:
    ' 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:
    Code:
    ' 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.420"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:
    Code:
    ' 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
     

    Attached Files:

  2. SNOUHyhQs2

    SNOUHyhQs2 Member Licensed User

    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.
     
  3. Mashiane

    Mashiane Well-Known Member Licensed User

    Brilliant, keep it up! will definately test this out!!
     
  4. KKCHAN

    KKCHAN New Member Licensed User

    Can you give us a sample project ?
     
  5. Venox

    Venox New Member Licensed User

    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?
     
  6. oscarsarrio

    oscarsarrio Member Licensed User

    Same problem only works in debug mode :(
     
  7. CLEBER

    CLEBER New Member Licensed User

    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()
     
  8. Erel

    Erel Administrator Staff Member Licensed User

    You should use JdbcSQL library.
     
    SNOUHyhQs2 likes this.
Loading...