[d2rq-dev] issue mapping a SQL server using jtds driver

Discussion:

Kai Hackemesser

2014-01-08 22:25:51 UTC

Hello,

I am fresh starting with d2rq V0.8.1. I am currently working myself through
http://d2rq.org/getting-started and try to create a mapping file using
http://d2rq.org/generate-mapping . The target SQL server is a MS SQL
Server 2008 instance. The target tables are in the dbo schema. The output
is a bit frustrating. First I get warnings on generation:

11:23:10 INFO generate_mapping :: Writing to mapping.ttl
11:23:10 INFO ConnectedDB :: Establishing JDBC connection to
jdbc:jtds:sqlserver://devsql03:1433
11:23:11 INFO ConnectedDB :: JDBC database product type:
Microsoft SQL Server
11:23:11 INFO ConnectedDB :: Using vendor class:
de.fuberlin.wiwiss.d2rq.sql.vendor.SQLServer
11:23:11 INFO MappingGenerator :: Generating d2rq:Database instance
11:23:11 INFO MappingGenerator :: Filter
'exclude(table(/dbo.spt_.*/0))' matches 5 total tables
11:23:11 INFO MappingGenerator :: Generating d2rq:ClassMap instance
for table dbo.spt_fallback_db
11:23:11 WARN MappingGenerator :: Sorry, I don't know which columns to
put into the uriPattern
11:23:11 WARN MappingGenerator :: for "dbo.spt_fallback_db"
because the table doesn't have a primary key.
11:23:11 WARN MappingGenerator :: Please specify it manually.
11:23:11 INFO MappingGenerator :: Generating d2rq:ClassMap instance
for table dbo.spt_fallback_dev
11:23:11 WARN MappingGenerator :: Sorry, I don't know which columns to
put into the uriPattern
11:23:11 WARN MappingGenerator :: for "dbo.spt_fallback_dev"
because the table doesn't have a primary key.
11:23:11 WARN MappingGenerator :: Please specify it manually.
11:23:11 INFO MappingGenerator :: Generating d2rq:ClassMap instance
for table dbo.spt_fallback_usg
11:23:11 WARN MappingGenerator :: Sorry, I don't know which columns to
put into the uriPattern
11:23:11 WARN MappingGenerator :: for "dbo.spt_fallback_usg"
because the table doesn't have a primary key.
11:23:11 WARN MappingGenerator :: Please specify it manually.
11:23:11 INFO MappingGenerator :: Generating d2rq:ClassMap instance
for table dbo.spt_monitor
11:23:11 WARN MappingGenerator :: Sorry, I don't know which columns to
put into the uriPattern
11:23:11 WARN MappingGenerator :: for "dbo.spt_monitor" because
the table doesn't have a primary key.
11:23:11 WARN MappingGenerator :: Please specify it manually.
11:23:11 INFO MappingGenerator :: Generating d2rq:ClassMap instance
for table dbo.spt_values
11:23:11 WARN MappingGenerator :: Sorry, I don't know which columns to
put into the uriPattern
11:23:11 WARN MappingGenerator :: for "dbo.spt_values" because the
table doesn't have a primary key.
11:23:11 WARN MappingGenerator :: Please specify it manually.
11:23:11 INFO MappingGenerator :: Done!
11:23:11 INFO ConnectedDB :: Closing connection to
jdbc:jtds:sqlserver://devsql03:1433

I have seen a mailing list entry from 2009 that mentioned this, but it
didn't help further. The main problem is that the produced mappings.ttl
file contains only these spt_* entries, not any of the actual user tables.
Ho can I get past this?

Cheers,
Kai

Richard Cyganiak

2014-01-08 23:02:44 UTC

Permalink

Kai, can you try whether you have better luck with this version?
http://download.d2rq.org/d2rq-r2rml-preview-v4.tar.gz

Best,
Richard

Post by Kai Hackemesser
Hello,
11:23:10 INFO generate_mapping :: Writing to mapping.ttl
11:23:10 INFO ConnectedDB :: Establishing JDBC connection to jdbc:jtds:sqlserver://devsql03:1433
11:23:11 INFO ConnectedDB :: JDBC database product type: Microsoft SQL Server
11:23:11 INFO ConnectedDB :: Using vendor class: de.fuberlin.wiwiss.d2rq.sql.vendor.SQLServer
11:23:11 INFO MappingGenerator :: Generating d2rq:Database instance
11:23:11 INFO MappingGenerator :: Filter 'exclude(table(/dbo.spt_.*/0))' matches 5 total tables
11:23:11 INFO MappingGenerator :: Generating d2rq:ClassMap instance for table dbo.spt_fallback_db
11:23:11 WARN MappingGenerator :: Sorry, I don't know which columns to put into the uriPattern
11:23:11 WARN MappingGenerator :: for "dbo.spt_fallback_db" because the table doesn't have a primary key.
11:23:11 WARN MappingGenerator :: Please specify it manually.
11:23:11 INFO MappingGenerator :: Generating d2rq:ClassMap instance for table dbo.spt_fallback_dev
11:23:11 WARN MappingGenerator :: Sorry, I don't know which columns to put into the uriPattern
11:23:11 WARN MappingGenerator :: for "dbo.spt_fallback_dev" because the table doesn't have a primary key.
11:23:11 WARN MappingGenerator :: Please specify it manually.
11:23:11 INFO MappingGenerator :: Generating d2rq:ClassMap instance for table dbo.spt_fallback_usg
11:23:11 WARN MappingGenerator :: Sorry, I don't know which columns to put into the uriPattern
11:23:11 WARN MappingGenerator :: for "dbo.spt_fallback_usg" because the table doesn't have a primary key.
11:23:11 WARN MappingGenerator :: Please specify it manually.
11:23:11 INFO MappingGenerator :: Generating d2rq:ClassMap instance for table dbo.spt_monitor
11:23:11 WARN MappingGenerator :: Sorry, I don't know which columns to put into the uriPattern
11:23:11 WARN MappingGenerator :: for "dbo.spt_monitor" because the table doesn't have a primary key.
11:23:11 WARN MappingGenerator :: Please specify it manually.
11:23:11 INFO MappingGenerator :: Generating d2rq:ClassMap instance for table dbo.spt_values
11:23:11 WARN MappingGenerator :: Sorry, I don't know which columns to put into the uriPattern
11:23:11 WARN MappingGenerator :: for "dbo.spt_values" because the table doesn't have a primary key.
11:23:11 WARN MappingGenerator :: Please specify it manually.
11:23:11 INFO MappingGenerator :: Done!
11:23:11 INFO ConnectedDB :: Closing connection to jdbc:jtds:sqlserver://devsql03:1433
I have seen a mailing list entry from 2009 that mentioned this, but it didn't help further. The main problem is that the produced mappings.ttl file contains only these spt_* entries, not any of the actual user tables. Ho can I get past this?
Cheers,
Kai
------------------------------------------------------------------------------
Rapidly troubleshoot problems before they affect your business. Most IT
organizations don't have a clear picture of how application performance
affects their revenue. With AppDynamics, you get 100% visibility into your
Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics Pro!
http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk
_______________________________________________
d2rq-map-devel mailing list
https://lists.sourceforge.net/lists/listinfo/d2rq-map-devel

Kai Hackemesser

2014-01-08 23:16:34 UTC

Permalink

Hi, Richard,

The log output is slightly different but the result is basically the same.
Further analysis points to that the SQL Server JDBC driver is ignoring my
database (or databaseName) property in the URL and is defaulting to the
master database which seems to be the main problem. I tried that with the
jTDS drivers as well and got the same results.

Cheers,
Kai

Post by Richard Cyganiak
Kai, can you try whether you have better luck with this version?
http://download.d2rq.org/d2rq-r2rml-preview-v4.tar.gz
Best,
Richard
Hello,
I am fresh starting with d2rq V0.8.1. I am currently working myself
through http://d2rq.org/getting-started and try to create a mapping file
using http://d2rq.org/generate-mapping . The target SQL server is a MS
SQL Server 2008 instance. The target tables are in the dbo schema. The
11:23:10 INFO generate_mapping :: Writing to mapping.ttl
11:23:10 INFO ConnectedDB :: Establishing JDBC connection to
jdbc:jtds:sqlserver://devsql03:1433
11:23:11 INFO ConnectedDB :: JDBC database product type: Microsoft SQL Server
de.fuberlin.wiwiss.d2rq.sql.vendor.SQLServer
11:23:11 INFO MappingGenerator :: Generating d2rq:Database instance
11:23:11 INFO MappingGenerator :: Filter
'exclude(table(/dbo.spt_.*/0))' matches 5 total tables
11:23:11 INFO MappingGenerator :: Generating d2rq:ClassMap instance
for table dbo.spt_fallback_db
11:23:11 WARN MappingGenerator :: Sorry, I don't know which columns
to put into the uriPattern
11:23:11 WARN MappingGenerator :: for "dbo.spt_fallback_db"
because the table doesn't have a primary key.
11:23:11 WARN MappingGenerator :: Please specify it manually.
11:23:11 INFO MappingGenerator :: Generating d2rq:ClassMap instance
for table dbo.spt_fallback_dev
11:23:11 WARN MappingGenerator :: Sorry, I don't know which columns
to put into the uriPattern
11:23:11 WARN MappingGenerator :: for "dbo.spt_fallback_dev"
because the table doesn't have a primary key.
11:23:11 WARN MappingGenerator :: Please specify it manually.
11:23:11 INFO MappingGenerator :: Generating d2rq:ClassMap instance
for table dbo.spt_fallback_usg
11:23:11 WARN MappingGenerator :: Sorry, I don't know which columns
to put into the uriPattern
11:23:11 WARN MappingGenerator :: for "dbo.spt_fallback_usg"
because the table doesn't have a primary key.
11:23:11 WARN MappingGenerator :: Please specify it manually.
11:23:11 INFO MappingGenerator :: Generating d2rq:ClassMap instance
for table dbo.spt_monitor
11:23:11 WARN MappingGenerator :: Sorry, I don't know which columns
to put into the uriPattern
11:23:11 WARN MappingGenerator :: for "dbo.spt_monitor" because
the table doesn't have a primary key.
11:23:11 WARN MappingGenerator :: Please specify it manually.
11:23:11 INFO MappingGenerator :: Generating d2rq:ClassMap instance
for table dbo.spt_values
11:23:11 WARN MappingGenerator :: Sorry, I don't know which columns
to put into the uriPattern
11:23:11 WARN MappingGenerator :: for "dbo.spt_values" because
the table doesn't have a primary key.
11:23:11 WARN MappingGenerator :: Please specify it manually.
11:23:11 INFO MappingGenerator :: Done!
11:23:11 INFO ConnectedDB :: Closing connection to
jdbc:jtds:sqlserver://devsql03:1433
I have seen a mailing list entry from 2009 that mentioned this, but it
didn't help further. The main problem is that the produced mappings.ttl
file contains only these spt_* entries, not any of the actual user tables.
Ho can I get past this?
Cheers,
Kai
------------------------------------------------------------------------------
Rapidly troubleshoot problems before they affect your business. Most IT
organizations don't have a clear picture of how application performance
affects their revenue. With AppDynamics, you get 100% visibility into your
Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics Pro!
http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk
_______________________________________________
d2rq-map-devel mailing list
https://lists.sourceforge.net/lists/listinfo/d2rq-map-devel

Richard Cyganiak

2014-01-14 12:47:36 UTC

Permalink

Kai,

Can you provide the following additional information:

1. Any JDBC connection URLs you’ve tried

2. The full command line you’re using

3. Some information about your database — what schema are the tables in, etc.

I’ve done some more experiments with jTDS and SQL Server, and it did pick up the database from the URL, so I currently can’t reproduce the problem.

Thanks,
Richard

Post by Kai Hackemesser
Hi, Richard,
The log output is slightly different but the result is basically the same. Further analysis points to that the SQL Server JDBC driver is ignoring my database (or databaseName) property in the URL and is defaulting to the master database which seems to be the main problem. I tried that with the jTDS drivers as well and got the same results.
Cheers,
Kai
Kai, can you try whether you have better luck with this version?
http://download.d2rq.org/d2rq-r2rml-preview-v4.tar.gz
Best,
Richard

------------------------------------------------------------------------------
Rapidly troubleshoot problems before they affect your business. Most IT
organizations don't have a clear picture of how application performance
affects their revenue. With AppDynamics, you get 100% visibility into your
Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics Pro!
http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk_______________________________________________
d2rq-map-devel mailing list
https://lists.sourceforge.net/lists/listinfo/d2rq-map-devel