Android Question How to read/write from MS SQL server database

[Fayez Boulos]

Hi,
I am very new to B4A, And I purchased the full version with a hope to be able to read, display, and write simple data to an MS SQL database located on my host.
I know the connection string to my database, however, I could not find yet a clear example on how to connect to the MS SQL database using B4A. Even the example that I downloaded from the web sites and forms, does not compile.
I would appreciate if someone sends me a sample code to do that or at least a guide where to find this information.
Since I am not familiar at all with the libraries, Please provide the list of all what I need to make the code compile.
I feel that this is the main show stopper in front of me to open a gate to do what I want with B4A... Please help. Thanks

Fayez

 

[Computersmith64]

https://www.b4x.com/android/forum/threads/jdbcsql-directly-connect-to-remote-databases.84016/

- Colin.

 

[Fayez Boulos]

Hi Colin,
I unzipped the 2 attached files (from the link) into one folder, and when I tried to run the example I got

B4A Version: 8.80
Parsing code. Error
Error parsing program.
Error description: Unknown type: jdbcsql
Are you missing a library reference?
Error occurred on line: 7 (Starter)
Public mysql As JdbcSQL

I am not really familiar with where to unzip the different files within the project (It is new environment to me as a Visual studio oriented person). Please provide a picture of the list of folders and files to be included in the project which makes it compile.
What libraries should I include?

Where is the library of JdbcSQL, I do not find it in the library list. where to get it from and where to save it in the project.

Thanks Colin

 

[DonManfred]

Error description: Unknown type: jdbcsql
Are you missing a library reference?

If you then use the Forumsearch for jdbcsql you´ll get to the right library needed.

 

[Fayez Boulos]

Hi DonManfred,
What is the library? is it a file with .lib extension?, is it a .bas file?, is it only one file or may be more?. Most importantly, if I find it where to copy it in the project folder tree?. and how to reference it from the project?

Is there a book that I can buy and read to define all these concepts for me rather than depending on searching through questions and posts?. Thanks

 

[DonManfred]

1. Check the Documentation: https://www.b4x.com/b4a.html#documentation
2. Check the Tutorial videos: https://www.b4x.com/etp.html

 

[Fayez Boulos]

Thanks for the links DonManfred
I used the documentation to roughly target the jdbcSQL as the missing library in the original code I am trying to make work. And I downloaded jdbc Driver for SQL server from Microsoft site. and after running the setup here is the folders tree

Here is the project folder tree

Currently, this is what I get when I run the project

Now to make the project compile which files should be copied and how to reference them in the project?

I appreciate your help

 

[josejad]

Search how to add library?

 

[DonManfred]

My last help:

Check the Library in the Librarytab

Again: Check Documentation and the videos.

 

[OliverA]

Maybe off topic: Just a heads up, the MSSQL driver's may not work with Android (they did not in the past, things may have changed). Just keep that in mind. In case they don't, the alternative would be to use the jTDS drivers (http://jtds.sourceforge.net/). Those, on the other hand, may not work with the latest and greatest MSSQL.

On topic: Any libraries that you download should be placed in the Additional Libraries folder that you set up in Tools->Configure Paths. This way you do not have to touch the internal library folder that is located under the Program Files (32bit OS) or Program Files (x86) (64 bit OS) folder. Note: some libraries are just a collection of one or more .jar files, some include an .xml file (as does JdbcSQL), and the newer ones have the extension .b4xlib

You need to enable the JdbcSQL library as per @DonManfred above

In the Main module, you need to replace
#AdditionalJar: mysql-connector-java-5.1.34-bin
With whatever MSSQL driver you are using (and the associated .jar file(s) should be located in your Additional Libraries folder).

In the Starter module, you need to
1) adjust all the Private variables in Process_Globals to match your MSSQL server's needs
2) in the ListAnimals sub, you need to
a) change the query to something that exists on your MSSQL server
b) change the log output log the columns of the adjusted query

 

[Fayez Boulos]

Thanks A lot Jose, and DonManfred
knowing about the location of additional libraries, and by downloading jdbcSQL, and knowing that the libraries will show in the library list if jar and xml files are in that folder helped in getting the application compile successfully.

That is a good conceptual starting point. I will take it from there, and try to connect to sql server real database. I really appreciate your help. Thanks again

 

[Fayez Boulos]

OliverA,
Sorry I sent my previous reply before I read your post... It is Awesome post, and touched exactly my problems, in a simple and direct way.... I really appreciate your help. Thanks

 

[Fayez Boulos]

Sorry Guys, Hopefully the last problem
I changed the parameters and variables to fit MS SQL server (To the best of my knowledge), and tried to test the connection and I got error

java.sql.SQLException: Unknown server host name 'Host is unresolved: 192.82.254.147,1533'.

Here is a screenshot of my code, and I am sure the server IP address is correct, it is working fine for years with all my windows, and web applications... What Am I missing... Please help

 

[DonManfred]

Post code as code (text), not screenshots.

Are you sure the port after the ip must be prefixed by a comma? Try ":" instead of ","

Additionally you should create a new Thread for a new Question.

 

[Fayez Boulos]

Thanks DonManfred,
I changed the IP address to contain ":" instead of "," and I did not get the error in starting the connection. This will allow me to go on with my learning, trying to access objects in the databases.

However, as a side note, the connection string in my windows, and web applications is used with "," not ":", but as per your advise, it removed the error.

I will keep on in my training, and I will surely need help but will create new threads for further help.

Once again, thank you soooo much DonManfred

 

[Computersmith64]

However, as a side note, the connection string in my windows, and web applications is used with "," not ":", but as per your advise, it removed the error.

In my experience, port numbers in URLs are always separated from the IP address with a ":".

- Colin.

 

[Fayez Boulos]

Thanks Colin