B4A Library MSSQL with JDBC Library v1.00

Hello everyone,

This is my first library.

There are only three functions.

   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.

 

[Erel]

Do you get the NetworkOnMainThread error? If yes then you can workaround it: https://www.b4x.com/android/forum/threads/44760/#content

Though the correct solution is to avoid using the library.

 

[tznikos]

Hi,

On the version 5.2 the library MSSQL worked fine
when install the version 5.5 the libary freezing when run the command

db.setDatabase ( "example.server.gr:8080", "GR", "sa2","xxxxxxx")

If somebody have faced this problem and know how to help me, ι would appreciate it very much

if not resolve this issue Unfortunately I will keep the version 5.2 for the rest of my life

Thanks

 

[FabianJi]

Excellent I am connected now thankyou very much

 

[oscarsarrio]

I use this library a lot, buy the latest version of b4a (6.50) and apps that were running give me the error:

Error occurred on line: 649 (Main)
java.lang.NullPointerException: Attempt to invoke interface method 'void java.sql.Connection.rollback()' on a null object reference
at com.tomlost.MSSQL.MSSQL.QueryExecute(MSSQL.java:138)
at com.tomlost.MSSQL.MSSQL.Query(MSSQL.java:42)
at java.lang.reflect.Method.invoke(Native Method)
at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:708)
at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:337)
at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:247)
at java.lang.reflect.Method.invoke(Native Method)
at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:134)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:157)
at anywheresoftware.b4a.BA.raiseEvent(BA.java:153)
at anywheresoftware.b4a.objects.ViewWrapper$2.onLongClick(ViewWrapper.java:88)
at android.view.View.performLongClick(View.java)
at android.widget.TextView.performLongClick(TextView.java)
at android.view.View$CheckForLongPress.run(View.java)
at android.os.Handler.handleCallback(Handler.java)
at android.os.Handler.dispatchMessage(Handler.java)
at android.os.Looper.loop(Looper.java)
at android.app.ActivityThread.main(ActivityThread.java)
at java.lang.reflect.Method.invoke(Native Method)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java)

code:

a.setDatabase("xxxxx","xxxx","xx",xxxx)
L=a.Query ("select * from B_APARCAMIENTOS") (error in this line)

This code work in 3.60 B4A version.

If the library does not work in the newer versions I will have to request the download link from version 5.20 to Erel and stay in this one.

 

[Erel]

Do you get the same error in release mode?

 

[oscarsarrio]

No ...

(NetworkOnMainThreadException)
android.os.NetworkOnMainThreadException

 

[Erel]

This error means that you should avoid using this library if you want to implement a good and reliable solution.

Workaround: Workaround the NetworkOnMainThread exception

 

[oscarsarrio]

When write the line :

Sub DisableStrictModeDim jo AsJavaObject
jo.InitializeStatic("android.os.Build.VERSION")If jo.GetField("SDK_INT") > 9ThenDim policy AsJavaObject
policy = policy.InitializeNewInstance("android.os.StrictMode.ThreadPolicy.Builder", Null)
policy = policy.RunMethodJO("permitAll", Null).RunMethodJO("build", Null)Dim sm AsJavaObject
sm.InitializeStatic("android.os.StrictMode").RunMethod("setThreadPolicy", Array(policy))EndIf
End Sub

I have the initial error I had in debug mode also in release. No solve the problem:

Attempt to invoke interface method 'void java.sql.Connection.rollback()' on a null object reference

 

[Erel]

Sounds like a bug in the library. Anyway I've sent you a link to v5.20.

 

[hf]

MSSQL with JDBC Library v1.00
who have a jtds1.3.1 version？now is jtds1.2.5

 

[DonManfred]

NO NEED to write in BOLD
We can read it better in normal Font.

https://mvnrepository.com/artifact/net.sourceforge.jtds/jtds/1.3.1

 

[hf]

NO NEED to write in BOLD
We can read it better in normal Font.

https://mvnrepository.com/artifact/net.sourceforge.jtds/jtds/1.3.1

thanks
I use jtds 1.3.1.jar ,replace then file name jtds1.2.5.jar . the MSSQL lib work well。

 

[hf]

after replace，the MSSQL lib will use jtds1.3.1.jar to work.
I think ?

 

[DonManfred]

after replace，the MSSQL lib will use jtds1.3.1.jar to work.
I think ?

the best is to use the jtds1.3.1.jar and put it in your additional libs folder and then change

<dependsOn>jtds-1.2.5</dependsOn>

to

<dependsOn>jtds1.3.1</dependsOn>

inside the MSSQL.xml

 

[hf]

the best is to use the jtds1.3.1.jar and put it in your additional libs folder and then change

<dependsOn>jtds-1.2.5</dependsOn>

to

<dependsOn>jtds1.3.1</dependsOn>

inside the MSSQL.xml

Thanks DonManfred!


<dependsOn>jtds-1.3.1</dependsOn> it work very good.

 

[hf]

Add android:targetSdkVersion="19" to the mainfest editor (after minSdkVersion).(warning #26)


olny this，the MSSQL work very good, but if Add it, the setdatabase("","","","") don't connect database?

 

[sakissoft]

Hello everyone,

This is my first library.

There are only three functions.

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

I have one question about transactional query.

if select TRUE in db.ExecuteNonQuery command then:

if sql command error then autoexecute Rollback (=canceled alls changes in database ) and run Catch routine?

(sorry for my english)

Thanks

 

[hf]

try like this:

Dim L As List
Dim rows As List
L=db.Query("select xm as czyxm,shbh from v_shczy where yhbh='" & EditText1.Text & "' and yhmm='" & EditText2.Text & "'")
or L=db.Query("exec P_tmqh_mb '" & EditText1.Text & "','" & pubcs.P_shbh & "';select 'ok'")
If L.IsInitialized=False Then
Msgbox("error","error")
Return
Else
If L.Size=2 Then
Msgbox("succes！","info")
Else
Msgbox("error！","error")

End If

End If

 

[hf]

this is error: db.ExecuteNonQuery("update tablename set fieldname = 'forexample' where key=1",True)

 

[select]

Anyone know if this library could work on b4j??