B4J Question config.properties - MS Sql driver for JDBC

Peekay · Jun 10, 2020

I am trying to change the tutorial code for mysql to MS Sql, but I have it wrong. This is my code:

MS Sql connection:

DriverClass=com.Drivermicrosoft.sqlserver.jdbc.SQLServerDriver
JdbcUrl=jdbc:sqlserver://localhost/test?characterEncoding=utf8

I could not find the correct connection string elsewhere on the forum.

Thanks
PK

Philip Chatzigeorgiadis · Jun 10, 2020

Check the solution here:

[SOLVED] JRDC2 Error

Hi I try to access my MSSQL Server with RDC2 and I get the above error. Any help? HTTP ERROR 500 Problem accessing /rdc. Reason: com.mchange.v2.resourcepool.TimeoutException: A client timed out while waiting to acquire a resource from...

www.b4x.com

Peekay · Jun 10, 2020

I get the following response, which is a bad character I am using:

My code now is:

B4J server side code:

DriverClass=com.microsoft.sqlserver.jdbc.SQLServerDriver
JdbcUrl=jdbc:sqlserver://192.168.1.10:17178;databaseName=Test;integratedSecurity=true;/test?characterEncoding=utf8
User=sa
Password=****
#Java server port
ServerPort=17178

Here is the server response:

EnriqueGonzalez · Jun 10, 2020

Hi... You should not use integrated security. That requires extra jars and dlls. And that will mean that the info in user and password in your config file will not be taken into account.

Peekay · Jun 12, 2020

I have tried many different connection types.
I have also tried Erel's suggestion from this link: Connection link
I am quite sure there are a lot of members here using my type of application which is using Sql Server 2012 on my development machine with a database named test.

Here is my latest try which returns error 404:

JDBC connection:

DriverClass=net.sourceforge.jtds.Driver
JdbcUrl=jdbc:jtds:sqlserver://localhost/test?characterEncoding=utf8
User=JDBC
Password=****
#Java server port
ServerPort=17178
Debug=true

PK

EnriqueGonzalez · Jun 12, 2020

Hi peekay
I use sql server as my daily database with over 5 server using b4j connections daily.

Enter into sql server configuration manager and open tcp connections. It is worth adding the accept all in the ports. 1433 is default. And restart the server

Next step is make sure sql server browser is on. If not you will not be able to connect

Last step is make sure you have mixed security with sql servers login active.
(SSMS)
Once you do Tha your connection information will be:

DriverClass=com.microsoft.sqlserver.jdbc.SQLServerDriver
JdbcUrl=jdbc:sqlserver://192.168.1.10:1433;databaseName=Test

Dont use jtds.

Peekay · Jun 14, 2020

Thanks Enrique.

1. I have done all you said and also opened the port 1433 in the firewall, and used http://192.168.1.10:1433/test in the browser, but it says:

This page isn’t working
192.168.1.10 didn’t send any data.
ERR_EMPTY_RESPONSE

In my log it says 'Address already in use', but the compile and run is successful.

2. Should I not add to the browser this text at the end:

Browser text:

/test?characterEncoding=utf8

3. I have changed the ServerPort lower down in the config.property file to 1433 or must I keep it to 17178?

4. What do I do when I have to address two different databases with two different Apps?

Thanks
PK

EnriqueGonzalez · Jun 14, 2020

Peekay

You are confusing Database port with server port, to test your url you have call it with the server port.
server url: http://192.168.1.10:17178

in the config properties file you actually have the server port, the database port goes directly in to the url connector.
JDBC url: JdbcUrl=jdbc:sqlserver://localhost:1433;databasename=test

when connecting from browser or from android you will use the server url.

Peekay said:
'Address already in use'

this is because B4J cant use port 1433 as it is the port that your database is using.

Peekay said:
2. Should I not add to the browser this text at the end:

no, it is not necesary

Peekay said:
. I have changed the ServerPort lower down in the config.property file to 1433 or must I keep it to 17178?

keep it 17178 and test with it on the browser
serverurl: http://192.168.1.10:17178/test

Peekay said:
What do I do when I have to address two different databases with two different Apps?

no problem, first app will be 17178 and second app will be 17179
serverurl2 : http://192.168.1.10:17179/test

Peekay · Jun 14, 2020

Enrique,

Thanks.
It says it cannot log into the database.
I have not worked with authentication other than Windows Authentication.
How do I set up another type of authentication.
If I go to the Test database, expand the tree and select Security, it asks me for a Schema, name and Type, but does not ask for a password.
I can do passwords for the whole Sql Server instance, which I have done, but that does not work apparently.

Thanks
PK

EnriqueGonzalez · Jun 14, 2020

Peekay said:
How do I set up another type of authentication.

Enrique Gonzalez R said:
Last step is make sure you have mixed security with sql servers login active.

(SSMS)

after you have don this step then run the following query

CREATE LOGIN [LoginName] WITH PASSWORD = 'SomePassword';

then run this other one:
EXEC sp_addsrvrolemember 'LoginName', 'sysadmin';

Peekay said:
I can do passwords for the whole Sql Server instance, which I have done, but that does not work apparently.

with this 2 queries, it will work to log in the database. just pass [loginName] and 'SomePassword' to the config.properties file.

Peekay · Jun 14, 2020

Enrique,

Thank you so much. The connection works.

PK

EnriqueGonzalez · Jun 14, 2020

Peekay said:
Thank you so much. The connection works.

Awesome! glad to be of help!

Peekay · Jun 15, 2020

Do I have to write all the Sql queries in the config.properties file or can I enter them under the ExecuteQuery=(Command ...) routine?

EnriqueGonzalez · Jun 15, 2020

In vanilla jrdc you have to add them in the config.properties file.

That doesn't mean you can't modify it to better suit your needs. If you tend to write multifloor queries. It may be better to write them else where for example the ide and load them from there.

OliverA · Jun 15, 2020

Peekay said:
Do I have to write all the Sql queries in the config.properties file or can I enter them under the ExecuteQuery=(Command ...) routine?

It's a limitation, but also the whole purpose of jRDC2. The client has no direct SQL access to your server, therefore reducing your footprint for SQL injection attacks to near none (I'm just leaving a little wiggle room - just in case. I know of no cases/nor techniques on SQL injection attacks against unmodified jRDC2). If the goal is to have the client query the DB directly, then jRDC2 may not be the right solution. With direct SQL access, you also have deal with the possibility of clients behaving badly against your SQL server (via SQL injection attacks, improper security, etc.).

Erel · Jun 16, 2020

Both posts above are correct. If you understand the risks with accepting queries from the clients then it should be very simple to modify jRDC2 and let the client send the query together with the parameters.

Peekay · Jun 16, 2020

Erel, I understand SQL injection, and it is fine like this. Thanks.
PK

B4J Question config.properties - MS Sql driver for JDBC

Peekay

Active Member

Philip Chatzigeorgiadis

Active Member

[SOLVED] JRDC2 Error

Peekay

Active Member

EnriqueGonzalez

Well-Known Member

Peekay

Active Member

EnriqueGonzalez

Well-Known Member

Peekay

Active Member

EnriqueGonzalez

Well-Known Member

Peekay

Active Member

EnriqueGonzalez

Well-Known Member

Peekay

Active Member

EnriqueGonzalez

Well-Known Member

Peekay

Active Member

EnriqueGonzalez

Well-Known Member

OliverA

Expert

Erel

B4X founder

Peekay

Active Member

Similar Threads

B4J Question config.properties - MS Sql driver for JDBC

Active Member

Active Member

Active Member

Well-Known Member

Active Member

Well-Known Member

Active Member

Well-Known Member

Active Member

Well-Known Member

Active Member

Well-Known Member

Active Member

Well-Known Member

Expert

B4X founder

Active Member

Similar Threads

Privacy & Transparency

Privacy & Transparency