Android Question B4A how to writes commands to remotely connect to an SQL database

[yl0859]

B4A penetrates the remote connection to the SQL database through the peanut shell intranet, and I don't know the correct command format, I hope the teachers will point out!
(SQLException) java.sql.SQLException: The syntax of the connection URL 'jdbc:jtds:sqlserver:115.236.153.174,18241\\SQLEXPRESS;databaseName=MISDB;user=sa;password=123456;appname=SKMJL;wsid=TEST;loginTimeout=10;socketTimeout=10' is invalid.

How do you write it in the right format?

 

[aeric]

I think the jdbcUrl should look like jdbc:jtds:sqlserver://115.236.153.174:18241/MISDB;instance=SQLEXPRESS;user=sa;password=123456;

The recommended method to connect to remote databases is jRDC2: https://www.b4x.com/android/forum/threads/61801/#content

Connecting to the database directly has several disadvantages:

- Insecure - it will be quite simple for a hacker to get the username and password and directly access the database.
- Hard to maintain - changes to the database design will require updating the app.
- Server and client performance issues - The Jdbc drivers are not optimized for mobile usage.
- Reliability issues due to unstable connectivity.

JdbcSQL - Directly connect to remote databases

This is a port of B4J jSQL library. The SQL type was renamed to JdbcSQL and ResultSet was renamed to JdbcResultSet, this allows using it together with the SQL library. SQL library - local SQLite databases. JdbcSQL - Any remote SQL database with a supported JDBC driver. MySQL and MS SQL are...

www.b4x.com

 

[yl0859]

@aeric： I've tried it, but it doesn't work. Is there a better way?

 

[yl0859]

@aeric：I can connect to a remote database that is not accessible through the internal network using this format for reference.
a.setDatabase("101.34.213.13:1433","CBM123","CBM666","Cbm981209")

 

[yl0859]

@aeric：But I still can't use this method. Please help me to see what's going on.
a.setDatabase( "115.236.153.174:18241/SQLEXPRESS","MISDB","sa","123456")

 

[yl0859]

@aeric：My database is temporarily built on my office computer, through the peanut shell network penetration to get a fixed IP, if you want to test in my working time 8: 30-16: 40 test, other time the computer does not boot.

 

[aeric]

I can't see how you write the code to make the connection with the following code:

a.setDatabase( "115.236.153.174:18241/SQLEXPRESS","MISDB","sa","123456")

1. Please post a sample code / project.
2. Please post your error message.
3. It is better to use jRDC2 if you have full control of the server.

Not sure what you mean by peanut shell network penetration due to lost translation.
I guessed you mean Virtual Private Network (VPN).

 

[aeric]

I attach a sample project using jtds driver.
Run the project and click the screen to start connection.

 

[yl0859]

Yes, I used the example you provided for the test. Since I did not have a fixed IP, I obtained the fixed IP through the company's Intranet. Now my data is as follows: IP:115.236.153.174; Port number: 18241; SQL database instance name: SQLEXPRESS; DataBase=MISDB; Uid=sa; Pwd=123456; So now the question is, how to combine the above data into a correct connection command, or how to fill in the above parameters in a correct format to the routine interface you provided, please teach, thank you!

 

[aeric]

The sample project should works. It works for me. You just need to substitute the values if changed.