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,627
  • MSSQL_Library_1.01.zip
    5.9 KB · Views: 1,454
Last edited:

AtoZ

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

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

hasanaydin52

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

Attachments

  • mssqljava.txt
    5.1 KB · Views: 303

Rafal Galewski

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

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

New Member
Licensed User
Longtime User
Some small error

Hi
have some small problems when i do the following statment:
B4X:
("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

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

nitinb4a

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

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

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

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

TonyTango

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

TonyTango

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

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

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

Rafal Galewski

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

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