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.

 

[AtoZ]

"I have 30 querys" !!

Wow, seems you are timing out.

If you so heavy into db, I also recommend, REST/Web/N-Tier solution, a direct connect is high on bandwidth and computing resources but nice and easy to use.

 

[Rafal Galewski]

Please Hassan if You Could change library to:

- Communication should always be done in the background. (like Erel said, handles the network on the main thread- Otherwise your program will not work reliably. )
- include JDBC last version with many errors resolve

Thank You in advanced.

 

[nkdarak1974]

mssql store procedure

any update on store procedure

 

[hasanaydin52]

Hello Rafal,

I'm not very experienced on Java.

I have written enough to me.

I dont make any connection operation. All operation is on the jdbc driver.

I'm open to suggestions. Source is below.

 

[Rafal Galewski]

Hello Hassan,

I don't know Java, only Visual Basic.

If You could for first change JDBC version to last.
The another function have to be the same.

Please try it . Thank You

 

[Rafal Galewski]

MSSQL library

I have a problem.
Whem I use MSSQL library writed by HASSANADIN in realase mode all work correctly.
But When I try the same code in DEBUG MODE - all stop withount any errors on the last block.
Where is a problem

from LOG:

sending message to waiting queue (CallSubDelayed - UpdateStatus)

Sometimes work but offen no work . Stop like no stable version.

Please help.
Where is a problem
How to resolve it.

 

[uknut]

Some small error

Hi
have some small problems when i do the following statment:

("INSERT INTO Varer(`navn`,`epd`,`ean`) VALUES('roket laks','3067220','7035620003141');INSERT INTO Priser (`vare_id`,`kjede_id`,`pris_enhet`,`volum_enhet`,`pris_volum`,`hylledato`) VALUES(LAST_INSERT_ID(),'1','32.76','1','64.89','2012-09-12')")

get the following error message:
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT INTO Priser (`vare_id`,`kjede_id`,`pris_enhet`,`volum_enhet`,`pris_volum`' at line 1

Have tryed the statement in phpmyadmin and there it is a valied statement

When i only use the first half of the statement it work fine, with adding a new record to the Varer table

B4A:2.52
Mysql connector java:5.1.24

Any feedback would be nice

Knut

 

[nitinb4a]

MSSQL Library exception on insert query

Hi Erel,

I am working on MSSQL Library and trying to execute below insert sql query.

SQL1.Query("INSERT INTO A_LocMaster2 VALUES('"& varImei &"', "& varlon &" ,"& varlat &",'"& varLocaddress &"','"& VarUpdateDate &"','"& varTime &"')")

The query is inserting the record in to database but it is showing below exception.

java.sql.SQLException: java.sql.SQLException:
The executeQuery method must return a result set.

How to handle this exception ?

BR

Nitin:sign0085:

 

[hasanaydin52]

Hello,

You must use ExecuteNonQuery statement.

Unfortunately, I didnt write yet exception handle.

 

[nitinb4a]

MSSQL Library exception on insert query

Hi hasanaydin52,

Thanks a lot for u'r quick & valuable reply with guidance.

Now the record is saving without any error.

Regards
Nitin
:sign0142:

 

[Bozidar Frangov]

Hi Erel

I am working on MSSQL Library, jtds-1.2.5.jar, and trying to execute sql query.

a.ExecuteNonQuery("INSERT INTO Proba VALUES ('2', '2')", True)

java.lang.NullPointerException


How to handle this exception ?

 

[Rafal Galewski]

I am working on MSSQL Library, jtds-1.2.5.jar, and trying to execute sql query.

a.ExecuteNonQuery("INSERT INTO Proba VALUES ('2', '2')", True)

java.lang.NullPointerException


How to handle this exception ?

I can check it on my side.
Please share Your project code and library.

I will try help You.

 

[rpina]

Transactions

Hi,

I'm starting using this library, and I've seen than Transactions are managed directly inside ExecuteNonQuery.

As I need to do different Update/Insert, now I need to compose a very long sql statement to achieve it.

I would like to know if you can add Begin, Commit and Rollback as separate functions, other than an ExecuteNonQuery2 function without transaction parameter?

THX

Kind regards
Riccardo

 

[Rafal Galewski]

I am working on MSSQL Library, jtds-1.2.5.jar, and trying to execute sql query.

a.ExecuteNonQuery("INSERT INTO Proba VALUES ('2', '2')", True)

java.lang.NullPointerException


How to handle this exception ?

Please change in manifest min version SDK for 4

 

[TonyTango]

You must copy MSSQL.jar, MSSQL.xml and jtds-1.2.5.jar files to your addititonal library folder.

Newbie question. Where is your additional library folder? Is it inside your application folder or the Basic4Andriod folder such as C:\Program Files (x86)\Anywhere Software\Basic4android\Libraries? Do you mean referenced libraries?

Ahh, I found it under Tools >> Configure Paths >> Additional Libraries. I now see the MSSQL (version 1.00) library in the library list. And that works once I updated the XML to point to the updated JTDS version.

How can I specify a port different than the default 1433?

Thanks, Tony

 

[TonyTango]

How do I see any error messages generated by the MSSQL/JTDS library? I put in deliberately incorrect IP address for a non existent machine and the code just keeps on going past the db.setDatabase and the db.Query lines.

 

[padvou]

Thank you for sharing your work.
I've implemented it in test environment and seems to work fine.
We need to find a way to execute the network related stuff in a separate thread though...

 

[padvou]

I managed to send the network portion of the code to a separate thread using CSE (CallSubExtended) by Informatix, so now I run it on framework 11+

My new question is how to direct the libraries to depend on a newer version of jtds (1.3.1) that is released. Could it be exposed somehow?
I'm blind... It's two posts above...

----I tried it but just editing the MSSQL.xml is not enough...
So, my question still stands about the ability to update the jtds..
Thank you

 

[Rafal Galewski]

I managed to send the network portion of the code to a separate thread using CSE (CallSubExtended) by Informatix, so now I run it on framework 11+

My new question is how to direct the libraries to depend on a newer version of jtds (1.3.1) that is released. Could it be exposed somehow?
I'm blind... It's two posts above...

----I tried it but just editing the MSSQL.xml is not enough...
So, my question still stands about the ability to update the jtds..
Thank you

How to update this library to a new jtds (1.3.1)?

 

[swamisantosh]

I'm was trying to use your library. but did not work.
a.setDatabase("192.168.1.2;instance=SQLEXPRESS","SDS","USER1","PASS1")

and it gives the message record not found.

I even turned off the firewall.

Please reply
Any suggestions?