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,923
  • MSSQL_Library_1.01.zip
    5.9 KB · Views: 1,816
Last edited:
Click to expand...
S

swamisantosh

Member
Licensed User
Longtime User
Hello hasanaydin52 ... I need you help ;)

I'm trying to use your library. but did not work.

a.setDatabase("192.168.1.2;instance=SQLEXPRESS","SDS","USER1","PASS1")

I Displays Message Record not found.

Any suggestions?

Santosh
 
S

sdarali

New Member
Licensed User
Longtime User
It took me two days to figure out that my Activity was receiving "No Records", "Records not found" (or NullException if you're not checking for Initialized List as I see in many posts before), was all because MS SQL Server 2012 Express (and previous versions as well) First-Time installation has by default Disabled TCP/IP protocol, which is required for communication over WiFi between the Android device and the SQL server running on the PC.

Basically my database testing was working from the local PC, but not working from my Android App through "WiFi". So beware that you have to check this setting by running the "SQL Server Configuration Manager" and following these menus:
"SQL Server Network Configuration" --> "Protocols to SQLEXPRESS" --> "Protocol Name: TCP/IP". (This must be set ENABLED.)

Hopefully that will solve many problems.
 
S

Softflow Systems

Member
Licensed User
Longtime User
Hi ,
I checked everything as suggested in above posts but still the same Nullexception and "Records Not found" is coming

This is the code
a.setDatabase("192.168.2.104\SQL2","dbname","username","xyz")
(SQL2 is the instance, I even tried ;instance=SQL2)


Try
a.ExecuteNonQuery("update z_par_0001 set party_nm = 'RUN' where acc_id = 351; select 'ok' ",True)
Catch
Msgbox(LastException.Message,"Error Occured")
EndTry

Nullexception is coming for this query .
 
Victor Pavlov

Victor Pavlov

Member
Licensed User
Longtime User
SoftFlow i see that you using SQLExpress named instance. You are connecting successfully to named instance on local machine because Express edition support only local named instances. If you using Standard/Enterprise edition and turn on Service: SQL Browser you will connect from remote machine/Android. Your best solution is to install MS Sql server to default instance (MSSQLServer)
 
jsanchezc

jsanchezc

Member
Licensed User
Longtime User
Softflow Systems said:
Hi ,
I checked everything as suggested in above posts but still the same Nullexception and "Records Not found" is coming

This is the code
a.setDatabase("192.168.2.104\SQL2","dbname","username","xyz")
(SQL2 is the instance, I even tried ;instance=SQL2)


Try
a.ExecuteNonQuery("update z_par_0001 set party_nm = 'RUN' where acc_id = 351; select 'ok' ",True)
Catch
Msgbox(LastException.Message,"Error Occured")
EndTry

Nullexception is coming for this query .
Click to expand...

You can connect to instance by using port:
Go to SQL Server Cofiguration, Networkm TCP/IP,
it must be active.
Open properties and then set port to a free port, if it is the only instance
you can assign default port for SQL: 1433 if not you can assign for example 1450 ... or 40000 ...
leave blank dynamic ports.

Reset sql server
Disable or adjust Windows firewall settings.
If you tru to connect by internet, the you need to configure router.

Now you can connect (for example port 1433):
a.setDatabase("192.168.2.104:1433","dbname","username","xyz")
Hope this help, if need more detailed info feel free to ask
 
jsanchezc

jsanchezc

Member
Licensed User
Longtime User
quaker said:
Hi thanks for your library.

If there any chance to execute "stored procedures" with parameters ,and returning info(in case of select in stored procedure).

If it could be done,i gloud very glad to thanks to that person the effort.I need something like that and i think more people do.

Happy Programming! ^^
Click to expand...

There is a way to execute STORED PROCEDURES with parameters:
here 2 parametrs , you can add many parameters.

StrQuery="EXEC MYSTOREDPROCEDURE '" & PARAM1VALUE & "','" & PARAM2VALUE & "'"

This way has a problem with data with character ' , you should replace it,
also in Spanish you can must verify decimal numbers, place . not , 123.33 , not 123,33

Remember stored procedure can return values to fill a List.

If you insert a new register into a table with autoin ID you can get Id inserted, place this after
insertion:
query="insert into xxxxx (fieldxxxx,fieldxxxx,fieldxxx) values ('valuexxxx','valuexxxx','valuexxxxxxxx'); select @@identity;"

select @@identity contains last id inserted inside your connection

If need a GUID ID you can get it:
Select newid() as MyNewGuidId
 
tznikos

tznikos

Member
Licensed User
Longtime User
i spend lot of time and i can't use this library.
i follow all instructions of this thread without result.


My network settings on my MSSQL is ok because many users connected form the Internet using windows software, which made by me, also i can connect remotely using Microsoft SQL Server Management Studio.


The script which I was used is


Dim db As MSSQL
'Via WiFi
db.setDatabase("192.168.0.205:9205 ,"GR","sa","xxxxxx")

'or Via 3G

db.setDatabase("62.38.249.10:9205 ,"GR","sa","xxxxxx")
Dim l As List
l = db.TableList
Msgbox("Total Tables is " & l.Size ,"")

I was able to connect with my MSSQL only in Debug (rapid) mode and only WiFi (intranet)

If i tried to connect in release mode or Debug (legacy) mode always i take Exceptions both of Wifi and 3G.


I was disappointed because i found it easy and clever to use

I have attach the test project please check it and tell me what is wrong

Thanks
 

Attachments

  • test.zip
    307 KB · Views: 453
padvou

padvou

Active Member
Licensed User
Longtime User
tznikos said:
i spend lot of time and i can't use this library.
i follow all instructions of this thread without result.


My network settings on my MSSQL is ok because many users connected form the Internet using windows software, which made by me, also i can connect remotely using Microsoft SQL Server Management Studio.


The script which I was used is


Dim db As MSSQL
'Via WiFi
db.setDatabase("192.168.0.205:9205 ,"GR","sa","xxxxxx")

'or Via 3G

db.setDatabase("62.38.249.10:9205 ,"GR","sa","xxxxxx")
Dim l As List
l = db.TableList
Msgbox("Total Tables is " & l.Size ,"")

I was able to connect with my MSSQL only in Debug (rapid) mode and only WiFi (intranet)

If i tried to connect in release mode or Debug (legacy) mode always i take Exceptions both of Wifi and 3G.


I was disappointed because i found it easy and clever to use

I have attach the test project please check it and tell me what is wrong

Thanks
Click to expand...
Can you post the exceptions?
 
tznikos

tznikos

Member
Licensed User
Longtime User
Sorry for the post but i solved,
I changed the manifest file as your sample project and now working fine
Your library is exactly that i want, Great Job
 
tznikos

tznikos

Member
Licensed User
Longtime User
Hi Again,

I face the following problem

When the signal of the phone is poor and i call the db.query("Select....") the SQL Server did not response as result the application seem to freeze, do you have any solution

i want to ask you first. Before start to Solve the problem with others ways.

Thanks
 
tznikos

tznikos

Member
Licensed User
Longtime User
Hi, hasanaydin52, padvou

Your library is exactly that i want, Great Job

It is possible to put a timeout property,

because when i send the db.query("Select....") and the SQL Server not responding or not have 3g signal, after 10 or 15 seconds your library stop the request.

Thanks
 
S

sakissoft

Member
Licensed User
Longtime User
Hi,
I tried this MSSQL library and i have one problem:

I use: ServerMSSQL.Query("INSERT into orders ......) then created new record in ms sql server normally but after 2-3sec
i have this error:"The execute Querymethod must return a result set"

I tried and L=ServerMSSQL.ExecuteNonQuery(strSQLCommand,False) but b4a debuger say "cannot assign void value"

I tried and "; select 'ok'" after .Query but freeze my program

Any idea for fix that error?
 
DonManfred

DonManfred

Expert
Licensed User
Longtime User
sakissoft said:
I tried and L=ServerMSSQL.ExecuteNonQuery(strSQLCommand,False) but b4a debuger say "cannot assign void value"
Click to expand...
try
B4X: 
ServerMSSQL.ExecuteNonQuery(strSQLCommand,False)
ExecuteNonQuery does not return anything...
 
S

sakissoft

Member
Licensed User
Longtime User
I have 20 updates sql queries:

UPDATE orders set Column1=Column1+1 WHERE Id=X; (when X is every int variable)
UPDATE orders set Column2=Column2+1 WHERE Id=Z; (when Z is every int variable)
.........
.........
UPDATE orders set Column20=Column+1 WHERE Id=Y; (when Y is every int variable)


How send alls UPDATE commands in one SQL query as one?

Thanks
 
Devendra

Devendra

Member
Licensed User
Longtime User
Hi Please correct me if i am wrong, Please find my Database Connection String Below Is it Right

a.setDatabase("IPADDRESS\SQLEXPRESS","DATABASENAME","USERNAME","PASSWORD")

Or do you have to add the port, How to get the port from SQL Server
 
Last edited:
azzam223

azzam223

Active Member
Licensed User
Longtime User
Devendra said:
Hi Please correct me if i am wrong, Please find my Database Connection String Below Is it Right

a.setDatabase("IPADDRESS\SQLEXPRESS","DATABASENAME","USERNAME","PASSWORD")

Or do you have to add the port, How to get the port from SQL Server
Click to expand...
Hello Devendra

No I don't Add The port because the port is default port (1433) if you have another port you must add it like this

B4X: 
a.setDatabase("IPADDRESS\SQLEXPRESS:port","DATABASENAME","USERNAME","PASSWORD")
 
You must log in or register to reply here.
Top