B4J Question config.properties - MS Sql driver for JDBC

Peekay

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

Active Member
Licensed User
Longtime User
Check the solution here:
 
Upvote 0

Peekay

Active Member
Licensed User
Longtime User
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:
ServerJRDC.jpg
 
Upvote 0

EnriqueGonzalez

Well-Known Member
Licensed User
Longtime User
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.
 
Upvote 0

Peekay

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

EnriqueGonzalez

Well-Known Member
Licensed User
Longtime User
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
1591971597623.png

1591971634412.png

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

Last step is make sure you have mixed security with sql servers login active.
1591971743930.png
(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.
 
Last edited:
Upvote 0

Peekay

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

EnriqueGonzalez

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

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

2. Should I not add to the browser this text at the end:
no, it is not necesary

. 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

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
 
Upvote 0

Peekay

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

EnriqueGonzalez

Well-Known Member
Licensed User
Longtime User
How do I set up another type of authentication.

Last step is make sure you have mixed security with sql servers login active.
1591971743930.png
(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';


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.
 
Upvote 0

Peekay

Active Member
Licensed User
Longtime User
Do I have to write all the Sql queries in the config.properties file or can I enter them under the ExecuteQuery=(Command ...) routine?
 
Upvote 0

EnriqueGonzalez

Well-Known Member
Licensed User
Longtime User
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.
 
Upvote 0

OliverA

Expert
Licensed User
Longtime User
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.).
 
Upvote 0
Top