B4A Library MSSQL with JDBC Library v1.00

Hello everyone,

This is my first library.

There are only three functions.

Code: Copy
   Dim db As MSSQL
        Dim L As List

        db = setDatabase("Server IPNUMBER (not name)","databasename","username","password")

        Try
             'select query
                  L = db.Query("select * from tablename")  '--> return rows as a LIST
                  L = db.TableList '--> return table list in database

             ' or update query   (True: If need transactional query)
                  db.ExecuteNonQuery("update tablename set fieldname = 'forexample' where key=1",True)  
        Catch
              MsgBox(LastException.Message,"Error Occured")
        End Try
.
.
.


You can also write create, update or delete queries.
You must copy MSSQL.jar, MSSQL.xml and jtds-1.2.5.jar files to your addititonal library folder.

You must add Manifest
AddPermission("android.permission.INTERNET")
AddPermission("android.permission.ACCESS_NETWORK_STATE")​

I wish to be useful.

Note: Be carefully working with databases, responsibility for problems caused by this library is yours.
 

Attachments

Last edited:
Nice!! I think that working directly with database it is fine for closed apps. I mean apps that don't will be distributed on market because it can be disassembled and get the connection data. For that I prefer web services.

It will be useful for an enterprise app that don't need to be distributed for other people.
 
Hello cjguajardo,

You're absolutely right.
This library usefull for 'in firm' applications.

Because server IPNumber should be accessible through the network.

I forgot this warning, thank you for your post.
 
Good job,

Can you share a library for MYSQL ? I think that the only change will be the driver.
 
The SELECT statement returns a list of the rows where the data is separated by commas. What if you have a comma in one of the fields. Example:
[FRUIT, DESCRIPTION]
Peaches, not too sweet, good quality
Pears, too ripe
Grapes, sour, rotten

1.When you parse the data how are you going to separate a field from the other when there is a comma in one of them?
Do you have a solution?

2. All numeric values that have no decimals are shown with 1 decimal when you do a SELECT query. For instance if the number stored is 12, it shows it as 12.0. Of course, one can use Round to get rid of the decimal in the display, but can this be fixed where it shows the numbers are they are stored in the SQL table without forcing a decimal?
Thanks
 
Last edited:
Hello Mahares,

  1. Use the split function.
    Code: Copy
    .
    .
    .
    Dim y() as string, row as List
    for i=1 to L.Size-1
       row = L.Get(i)
       y = Regex.Split(",",row(0))
       for j=0 to row.Size-1
             Msgbox(y(j),j & ".Data")
       next j
    Next i
  2. My sample numeric data does not shown unnecessary decimal.
 
Hi Hasan:
Thank you very much for your response.
2.My sample numeric data does not shown unnecessary decimal.
I have a MS SQL database that, when I do a select query on from within MS SQL itself like SS Management Studios, it does not show the decimal. For instance, if the column is REAL and it has 15, it shows it as 15 like it should.
But, when I use your library and use SELECT to display the data, it shows 15.0. Try to have some columns that are REAL and display the data on the device via a listview, you will see the decimal. It happened on 2 tablets and 1 smart phone with OS 4.04. At any rate, it is not a big problem because I can just use Round(X) to get rid of the decimal. Perhaps someone else can confirm or disprove my finding.
 
Question

I want to ask you perhaps a stupid question.
Did you consider the instance name of the server?.
I mean for example: "192.168.0.1\instancename" ?
I can't connect to the server.
I don't know if the example is clear.
Thanks

Claudio
 
Hello hasanaydin52 ... i need you help ;)

I'm trying to use your library. but does not work. In your example I entered my data

a.setDatabase("88.198.43.24;instance=db4free.net","database_gst","xxxxx","xxxxx")

and it gives the message record not found.

Any suggestions?

Marco
 
Excellent job!

This is really an excellent job. Congratulations and thank you very much!
 
Hi Hasanaydin52,

I am using your excellent library but I got a problem.

I have no problem when I use the library on my HTC HD2 (win phone "cooked" to work with Android) and Samsung Galaxy Mini

I get problem when I try to install the app on Samsung Galaxy S2 and Samsung Galaxy Tab 10.1: it seem that the list (L) cannot be inizialize.

Do you have idea of what I can do to solve the problem?

Thak you very much,
Vittorio

Inviato dal mio GT-P7500 con Tapatalk 2
 
Just trying to help

For people who got trouble with List not being initialized, try look at your manifest and remove the "android:targetSdkVersion:##".
I had this problem too, but solved with that.
Hope this help.
 
Connect with a diferent port number

Hello everyone,



I'm trying to connect the mysql server with a diferent port number, but I'm not heving success


I'm using like this :

Code: Copy
Mysql.setDatabase(c.GetString("server")&":"&c.GetString("port"), c.GetString("db"), c.GetString("user"), c.GetString("pass") )

When I use the default port "3306" it's works fine. Does anyone know how to help me?

:sign0163:
 
Hello willbr2k,

I changed my own Mysql Server port to 3307.

I didnt change any code.

Same program accessed to mysql without an error.

so I can't see problem.

I will continue to find .
 
problem whit mssql library

hi, i'm tring to usa you great library ( i'm a newbye...) but the app give me always this error

error occured
an error has occured in sub:
main_btnconnetti_click(java line 233)
java.lang.runtimeException:
No message resource found
for message property prop.
servertype
Continue?

this is my code

Sub Globals


Dim a As MSSQL
Dim L As List
Dim btnconnetti As Button



End Sub



Sub Activity_Create(FirstTime As Boolean)

Activity.LoadLayout("prova")
End Sub



Sub btnconnetti_Click




a.setDatabase("xxx.xxx.xxx.xxx:68","android","xxxxxx","xxxxxxxxx")


L=a.Query("select prova from prova where id=1")


L.Initialize
If L.IsInitialized=False Then
Msgbox("non sei connesso", "")
Return
End If

If L.IsInitialized=True Then
Dim verifica As String
verifica= L
Msgbox("verifica", "")
Return
End If
end sub

Please help me!!
 
Back
Top