B4A Library MSSQL with JDBC Library v1.00

Hello everyone,

This is my first library.

There are only three functions.

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

  • LibraryMsSQLSample.zip
    296.7 KB · Views: 1,922
  • MSSQL_Library_1.01.zip
    5.9 KB · Views: 1,813
Last edited:
Click to expand...
C

cjguajardo

Member
Licensed User
Longtime User
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.
 
H

hasanaydin52

Member
Licensed User
Longtime User
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.
 
avacondios

avacondios

Active Member
Licensed User
Longtime User
Good job,

Can you share a library for MYSQL ? I think that the only change will be the driver.
 
M

Mahares

Expert
Licensed User
Longtime User
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:
H

hasanaydin52

Member
Licensed User
Longtime User
Hello Mahares,

  1. Use the split function.
    B4X: 
    .
.
.
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.
 
M

Mahares

Expert
Licensed User
Longtime User
Hi Hasan:
Thank you very much for your response.
2.My sample numeric data does not shown unnecessary decimal.
Click to expand...
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.
 
C

claudioger

New Member
Licensed User
Longtime User
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
 
marco.canta

marco.canta

Active Member
Licensed User
Longtime User
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
 
Vittorio

Vittorio

Member
Licensed User
Longtime User
Excellent job!

This is really an excellent job. Congratulations and thank you very much!
 
Vittorio

Vittorio

Member
Licensed User
Longtime User
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
 
D

dewif

Member
Licensed User
Longtime User
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.
 
W

willbr2k

Member
Licensed User
Longtime User
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 :

B4X: 
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:
 
H

hasanaydin52

Member
Licensed User
Longtime User
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 .
 
C

corvo

Member
Licensed User
Longtime User
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!!
 
You must log in or register to reply here.
Top