SQL server compatibility

[martinbog]

Hi,

I would like to get R2RML Parser working with SQL Server. You've already explained how to modify sources:

"""

1. Change function findDatabaseType in gr.seab.r2rml.beans.UtilImpl to not exit when the database type is unknown:
   Replace:
   System.exit(1);
   with e.g.
   return DatabaseType.ORACLE;
2. Set the correct values in db.url, db.login, db.password, and db.driver in r2rml.properties

You will need a JDBC driver for SQL Server though. Place the jar in the lib folder.

1. You will have to replace table mappings into SQL queries. Instead of having for instance:
   rr:logicalTable [ rr:tableName '"mytable"'; ];
   you will have to change it to e.g.:
   rr:logicalTable [ rr:sqlQuery """SELECT * FROM mytable"""; ];

The difference among the various databases is in retrieving the table columns (see Parser.java, lines 635-641). If you do not have rr:tableName mappings in your R2RML file and have rr:sqlQuery instead, I believe you will not have a problem.
"""

After these changes, I would like to know how to compile properly sources in order to get the same configuration as the latest release (https://github.com/nkons/r2rml-parser/releases).

Thank you in advance,
Martin

[nkons]

Hi Martin,

1. First, you would have to install git and a git client locally (either https://windows.github.com/ or one of the many free available).
2. Then, as an IDE, I would recommend Eclipse. It also has a git client, you could use this one as well. Conveniently, it also has an embedded Maven runtime.
3. You will also need to install a Java JDK locally (a JDK, not a JRE).
4. Then, you will have to clone the repository locally (git clone).
5. Import the project into Eclipse (File -> Import -> Existing Maven projects)
6. Run the project using mvn package: Right click on project head -> Run as -> Maven build... -> package (in the Goals textbox). Once this finishes, the folder "target" will contain your version of the tool, which you will be able to execute via the command line. Alternatively, you can run the project from within Eclipse by right clicking on Main.java -> Run as -> Java application.

I hope this helps.

Before starting development, please note that you will need to be somewhat familiar with java development, maven (and git), so you might want to refresh on these a bit.

Now, to come to the actual problem: I couldn't find a "free" version of a jdbc driver for SQL server. If you could point me to one to include as a maven dependency, I could include it in the parser.

[charta77]

Hi Nikos,
thank you for instructions to compile the project!
Petr

[carmenvandeloo]

Hi,

you can use the following jdbc driver for SQL Server:

		<dependency>
		    <groupId>com.microsoft.sqlserver</groupId>
		    <artifactId>mssql-jdbc</artifactId>
		    <version>6.4.0.jre8</version>
		</dependency>

Check the support matrix here to see which version you need.

with database connectivity as following:

# MSSQL example
db.url=jdbc:sqlserver://localhost;instanceName=SQLEXPRESS;databaseName=yourdbname
db.login=yourlogin
db.password=yourpassword
db.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver

Some things to take into account that did the trick for me:

1. Make sure the SQL Server Browser windows service is running
2. Open SQL Server Configuration Manager and go to Protocols for SQLEXPRESS under SQL Server Network Configuration. Right-click on TCP/IP and choose Properties. Set Enabled = YES.
3. While you're there, click on IP Addresses tab and find the section IP All. Set TCP Port to 1433.
4. Make sure to change server authentication from Windows Authentication mode to SQL SERVER and Windows Authentication mode (mixed mode) in SQL Management Studio
5. Make sure to change Permissions for the Login Account you would like to grant permission to in SQL Management Studio