B4J Question config.properties - MS Sql driver for JDBC

[Peekay]

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

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]

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]

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

My code now is:

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]

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]

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:

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]

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]

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:

/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]

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

 

[Peekay]

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]

How do I set up another type of authentication.

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';

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]

Enrique,

Thank you so much. The connection works.

PK

 

[EnriqueGonzalez]

Thank you so much. The connection works.

Awesome! glad to be of help!

 

[Peekay]

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]

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]

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]

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]

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