Trouble connecting to MSSQL

(Serge Colle) #1

What the recommended way to connect to MSSQL.

I first tried the jtds driver which I know work as I am using it in DBVisualiser. Using the same setting as in DB Visualiser I tried to connect to MSSQL from Stardog without luck

jdbc.url=jdbc:jtds:sqlserver://BLMCIK:3701;domain=MyDomain;DatabaseName=T_Topology;Instance=SQL2008
jdbc.driver=net.sourceforge.jtds.jdbc.Driver

which I get the following

Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.

I also tried using the Microsoft driver

jdbc.url=jdbc:sqlserver://BLMCIK;IntegratedSecurity=true;domain=MyDomain;DatabaseName=T_Topology;
jdbc.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver

And I get the following

The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: "SQL Server did not return a response. The connection has been closed. ClientConnectionId:636530a0-3776-4fba-92a0-2e55eb03fe37".

Any recommendation would be appreciated.

(Jess Balint) #2

Which version of SQL Server are you connecting to?

(Serge Colle) #3

I am trying to connect to SQL 2008.

Not sure if this will help, however since I posted, I got it to connect sort of by duplicating the username/password in the URL, however I am not able to query any table. I keep getting object not found (including sys.objects). So not sure if it counts as connected.

(Jess Balint) #4

That sounds odd indeed.

Are the tables in dbo? If not, you need to add the schemas to the sql.schemas virtual graph configuration property.

(Serge Colle) #5

Yes they are in the default schema. In DBVisualizer I am able to query it using

select * from T_Topology.dbo.device, but no luck when trying from stardog.`

I will try to add sql.schemas=T_Topology in my property file later.

(Jess Balint) #6

which driver and version are you using now?

(Serge Colle) #7

jdts 1.3.1 with the following

jdbc.url=jdbc:jtds:sqlserver://BLMCIK:3701;username=serge.colle;password=mypasswd;domain=myDomain;DatabaseName=T_Topology;Instance=SQL2008;IntegratedSecurity=true
jdbc.driver=net.sourceforge.jtds.jdbc.Driver
(Serge Colle) #8

I tried

but did not work.

(zachary.whitley) #9

Try setting IntegratedSecurity=false

(Serge Colle) #10

Same output. From line 11, column 18 to line 11, column 23: Object 'Device' not found

could it be that this error message is coming from Stardog and not the remote database. Ie you are running query to discover objects in the remote database to which I may not have permission too.

(Jess Balint) #11

do you have a mapping over the Device table? can you share the full log output from the server?

(Serge Colle) #12
FROM SQL {
    select 
        device.device_unique_id as id,
        device.device_name as name,
        device.device_clli as clli,
        device.device_type as type, 
        device.device_subtype as subtype, 
        device_port.port_unique_id as port_id
    FROM device
    INNER JOIN device_port ON device.device_unique_id=device_port.device_unique_id
}

TO {
    ?deviceiri a loc:Device ;
        loc:name ?name ;
        loc:clli ?clli;
        loc:type ?type;
        loc:subtype ?subtype;
        loc:hasPort ?portiri .
    

}
WHERE {  
    BIND(template("http://topology/asset/device/{id") as ?deviceiri)
    BIND(template("hINFO  2019-04-17 10:41:17,196 [stardog-user-3] 

log

com.complexible.stardog.virtual.vega.calcite.VegaJdbcSchema:computeTables(263): No tables found in T_Topology.T_Topology
ERROR 2019-04-17 10:41:17,196 [stardog-user-3] org.apache.calcite.runtime.CalciteException:<init>(59): org.apache.calcite.sql.validate.SqlValidatorException: Object 'device' not found
ERROR 2019-04-17 10:41:17,197 [stardog-user-3] org.apache.calcite.runtime.CalciteException:<init>(61): org.apache.calcite.runtime.CalciteContextException: From line 18, column 10 to line 18, column 15: Object 'device' not found
ERROR 2019-04-17 10:41:17,198 [stardog-user-3] com.complexible.stardog.virtual.DefaultVirtualGraphRegistry:createVirtualGraph(312): Cannot initialize virtual graph device
java.lang.IllegalArgumentException: com.complexible.stardog.virtual.vega.UncheckedSqlParseException: org.apache.calcite.runtime.CalciteContextException: From line 18, column 10 to line 18, column 15: Object 'device' not found
	at com.complexible.stardog.virtual.vega.rdbms.RdbmsMappings.buildTriplesMapsFromSms2Mapping(RdbmsMappings.java:197) ~[stardog-virtual-core-6.1.2.jar:?]
	at java.util.stream.ReferencePipeline$3$1.accept(Unknown Source) ~[?:1.8.0_201]
	at java.util.ArrayList$ArrayListSpliterator.forEachRemaining(Unknown Source) ~[?:1.8.0_201]
	at java.util.stream.AbstractPipeline.copyInto(Unknown Source) ~[?:1.8.0_201]
	at java.util.stream.AbstractPipeline.wrapAndCopyInto(Unknown Source) ~[?:1.8.0_201]
	at java.util.stream.ReduceOps$ReduceOp.evaluateSequential(Unknown Source) ~[?:1.8.0_201]
	at java.util.stream.AbstractPipeline.evaluate(Unknown Source) ~[?:1.8.0_201]
	at java.util.stream.ReferencePipeline.collect(Unknown Source) ~[?:1.8.0_201]
	at com.complexible.stardog.virtual.vega.rdbms.RdbmsMappings.parseSms2Mappings(RdbmsMappings.java:175) ~[stardog-virtual-core-6.1.2.jar:?]
	at com.complexible.stardog.virtual.vega.rdbms.RdbmsMappings.parseOrGenerateMappings(RdbmsMappings.java:122) ~[stardog-virtual-core-6.1.2.jar:?]
	at com.complexible.stardog.virtual.vega.rdbms.RdbmsMappings.parseOrGenerateMappings(RdbmsMappings.java:107) ~[stardog-virtual-core-6.1.2.jar:?]
	at com.complexible.stardog.virtual.vega.rdbms.RdbmsMappings.parseOrGenerateMappings(RdbmsMappings.java:92) ~[stardog-virtual-core-6.1.2.jar:?]
	at com.complexible.stardog.virtual.vega.rdbms.RdbmsVirtualGraphFactory.create(RdbmsVirtualGraphFactory.java:76) ~[stardog-virtual-core-6.1.2.jar:?]
	at com.complexible.stardog.virtual.vega.rdbms.RdbmsVirtualGraphFactory.create(RdbmsVirtualGraphFactory.java:44) ~[stardog-virtual-core-6.1.2.jar:?]
	at com.complexible.stardog.virtual.DefaultVirtualGraphRegistry.createVirtualGraph(DefaultVirtualGraphRegistry.java:309) ~[stardog-virtual-core-6.1.2.jar:?]
	at com.complexible.stardog.virtual.DefaultVirtualGraphRegistry.createVirtualGraph(DefaultVirtualGraphRegistry.java:295) ~[stardog-virtual-core-6.1.2.jar:?]
	at com.complexible.stardog.virtual.DefaultVirtualGraphRegistry.add(DefaultVirtualGraphRegistry.java:169) ~[stardog-virtual-core-6.1.2.jar:?]
	at com.complexible.stardog.virtual.SecuredVirtualGraphRegistry.add(SecuredVirtualGraphRegistry.java:133) ~[stardog-virtual-core-6.1.2.jar:?]
	at com.complexible.stardog.protocols.http.server.virtual.admin.VirtualGraphHttpService.addVG(VirtualGraphHttpService.java:344) ~[stardog-virtual-protocols-http-server-6.1.2.jar:?]
	at com.complexible.stardog.protocols.http.server.virtual.admin.VirtualGraphHttpService.add(VirtualGraphHttpService.java:144) ~[stardog-virtual-protocols-http-server-6.1.2.jar:?]
	at com.stardog.http.server.undertow.jaxrs.ExtractRoutes.lambda$handleIt$5(ExtractRoutes.java:192) ~[stardog-protocols-http-server-6.1.2.jar:?]
	at org.apache.shiro.subject.support.SubjectRunnable.doRun(SubjectRunnable.java:120) [shiro-core-1.2.3.jar:1.2.3]
	at org.apache.shiro.subject.support.SubjectRunnable.run(SubjectRunnable.java:108) [shiro-core-1.2.3.jar:1.2.3]
	at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) [?:1.8.0_201]
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) [?:1.8.0_201]
	at java.lang.Thread.run(Unknown Source) [?:1.8.0_201]
Caused by: com.complexible.stardog.virtual.vega.UncheckedSqlParseException: org.apache.calcite.runtime.CalciteContextException: From line 18, column 10 to line 18, column 15: Object 'device' not found
	at com.complexible.stardog.virtual.vega.calcite.RelSqlParser.parseToRelNode(RelSqlParser.java:98) ~[stardog-virtual-core-6.1.2.jar:?]
	at com.complexible.stardog.virtual.vega.rdbms.RdbmsMappings.lambda$parseOrGenerateMappings$1(RdbmsMappings.java:90) ~[stardog-virtual-core-6.1.2.jar:?]
	at com.complexible.stardog.virtual.vega.rdbms.RdbmsMappings.buildTriplesMapsFromSms2Mapping(RdbmsMappings.java:190) ~[stardog-virtual-core-6.1.2.jar:?]
	... 25 more
Caused by: org.apache.calcite.tools.ValidationException: org.apache.calcite.runtime.CalciteContextException: From line 18, column 10 to line 18, column 15: Object 'device' not found
	at com.complexible.stardog.virtual.vega.calcite.RelSqlParser.sqlAstToValidatedAst(RelSqlParser.java:75) ~[stardog-virtual-core-6.1.2.jar:?]
	at com.complexible.stardog.virtual.vega.calcite.RelSqlParser.parseToRelNode(RelSqlParser.java:95) ~[stardog-virtual-core-6.1.2.jar:?]
	at com.complexible.stardog.virtual.vega.rdbms.RdbmsMappings.lambda$parseOrGenerateMappings$1(RdbmsMappings.java:90) ~[stardog-virtual-core-6.1.2.jar:?]
	at com.complexible.stardog.virtual.vega.rdbms.RdbmsMappings.buildTriplesMapsFromSms2Mapping(RdbmsMappings.java:190) ~[stardog-virtual-core-6.1.2.jar:?]
	... 25 more
Caused by: org.apache.calcite.runtime.CalciteContextException: From line 18, column 10 to line 18, column 15: Object 'device' not found
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[?:1.8.0_201]
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source) ~[?:1.8.0_201]
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source) ~[?:1.8.0_201]
	at java.lang.reflect.Constructor.newInstance(Unknown Source) ~[?:1.8.0_201]
	at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:463) ~[calcite-core-1.16.0.jar:1.16.0]
	at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:803) ~[calcite-core-1.16.0.jar:1.16.0]
	at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:788) ~[calcite-core-1.16.0.jar:1.16.0]
	at org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:4706) ~[calcite-core-1.16.0.jar:1.16.0]
	at org.apache.calcite.sql.validate.IdentifierNamespace.resolveImpl(IdentifierNamespace.java:172) ~[calcite-core-1.16.0.jar:1.16.0]
	at org.apache.calcite.sql.validate.IdentifierNamespace.validateImpl(IdentifierNamespace.java:177) ~[calcite-core-1.16.0.jar:1.16.0]
	at org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:84) ~[calcite-core-1.16.0.jar:1.16.0]
	at org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:947) ~[calcite-core-1.16.0.jar:1.16.0]
	at org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:928) ~[calcite-core-1.16.0.jar:1.16.0]
	at org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom(SqlValidatorImpl.java:2975) ~[calcite-core-1.16.0.jar:1.16.0]
	at org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom(SqlValidatorImpl.java:2960) ~[calcite-core-1.16.0.jar:1.16.0]
	at org.apache.calcite.sql.validate.SqlValidatorImpl.validateJoin(SqlValidatorImpl.java:3011) ~[calcite-core-1.16.0.jar:1.16.0]
	at org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom(SqlValidatorImpl.java:2969) ~[calcite-core-1.16.0.jar:1.16.0]
	at org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3219) ~[calcite-core-1.16.0.jar:1.16.0]
	at org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:60) ~[calcite-core-1.16.0.jar:1.16.0]
	at org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:84) ~[calcite-core-1.16.0.jar:1.16.0]
	at org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:947) ~[calcite-core-1.16.0.jar:1.16.0]
	at org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:928) ~[calcite-core-1.16.0.jar:1.16.0]
	at org.apache.calcite.sql.SqlSelect.validate(SqlSelect.java:226) ~[calcite-core-1.16.0.jar:1.16.0]
	at org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:903) ~[calcite-core-1.16.0.jar:1.16.0]
	at org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:613) ~[calcite-core-1.16.0.jar:1.16.0]
	at com.complexible.stardog.virtual.vega.calcite.RelSqlParser.sqlAstToValidatedAst(RelSqlParser.java:72) ~[stardog-virtual-core-6.1.2.jar:?]
	at com.complexible.stardog.virtual.vega.calcite.RelSqlParser.parseToRelNode(RelSqlParser.java:95) ~[stardog-virtual-core-6.1.2.jar:?]
	at com.complexible.stardog.virtual.vega.rdbms.RdbmsMappings.lambda$parseOrGenerateMappings$1(RdbmsMappings.java:90) ~[stardog-virtual-core-6.1.2.jar:?]
	at com.complexible.stardog.virtual.vega.rdbms.RdbmsMappings.buildTriplesMapsFromSms2Mapping(RdbmsMappings.java:190) ~[stardog-virtual-core-6.1.2.jar:?]
	... 25 more
Caused by: org.apache.calcite.sql.validate.SqlValidatorException: Object 'device' not found
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[?:1.8.0_201]
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source) ~[?:1.8.0_201]
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source) ~[?:1.8.0_201]
	at java.lang.reflect.Constructor.newInstance(Unknown Source) ~[?:1.8.0_201]
	at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:463) ~[calcite-core-1.16.0.jar:1.16.0]
	at org.apache.calcite.runtime.Resources$ExInst.ex(Resources.java:572) ~[calcite-core-1.16.0.jar:1.16.0]
	at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:803) ~[calcite-core-1.16.0.jar:1.16.0]
	at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:788) ~[calcite-core-1.16.0.jar:1.16.0]
	at org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:4706) ~[calcite-core-1.16.0.jar:1.16.0]
	at org.apache.calcite.sql.validate.IdentifierNamespace.resolveImpl(IdentifierNamespace.java:172) ~[calcite-core-1.16.0.jar:1.16.0]
	at org.apache.calcite.sql.validate.IdentifierNamespace.validateImpl(IdentifierNamespace.java:177) ~[calcite-core-1.16.0.jar:1.16.0]
	at org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:84) ~[calcite-core-1.16.0.jar:1.16.0]
	at org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:947) ~[calcite-core-1.16.0.jar:1.16.0]
	at org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:928) ~[calcite-core-1.16.0.jar:1.16.0]
	at org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom(SqlValidatorImpl.java:2975) ~[calcite-core-1.16.0.jar:1.16.0]
	at org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom(SqlValidatorImpl.java:2960) ~[calcite-core-1.16.0.jar:1.16.0]
	at org.apache.calcite.sql.validate.SqlValidatorImpl.validateJoin(SqlValidatorImpl.java:3011) ~[calcite-core-1.16.0.jar:1.16.0]
	at org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom(SqlValidatorImpl.java:2969) ~[calcite-core-1.16.0.jar:1.16.0]
	at org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3219) ~[calcite-core-1.16.0.jar:1.16.0]
	at org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:60) ~[calcite-core-1.16.0.jar:1.16.0]
	at org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:84) ~[calcite-core-1.16.0.jar:1.16.0]
	at org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:947) ~[calcite-core-1.16.0.jar:1.16.0]
	at org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:928) ~[calcite-core-1.16.0.jar:1.16.0]
	at org.apache.calcite.sql.SqlSelect.validate(SqlSelect.java:226) ~[calcite-core-1.16.0.jar:1.16.0]
	at org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:903) ~[calcite-core-1.16.0.jar:1.16.0]
	at org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:613) ~[calcite-core-1.16.0.jar:1.16.0]
	at com.complexible.stardog.virtual.vega.calcite.RelSqlParser.sqlAstToValidatedAst(RelSqlParser.java:72) ~[stardog-virtual-core-6.1.2.jar:?]
	at com.complexible.stardog.virtual.vega.calcite.RelSqlParser.parseToRelNode(RelSqlParser.java:95) ~[stardog-virtual-core-6.1.2.jar:?]
	at com.complexible.stardog.virtual.vega.rdbms.RdbmsMappings.lambda$parseOrGenerateMappings$1(RdbmsMappings.java:90) ~[stardog-virtual-core-6.1.2.jar:?]
	at com.complexible.stardog.virtual.vega.rdbms.RdbmsMappings.buildTriplesMapsFromSms2Mapping(RdbmsMappings.java:190) ~[stardog-virtual-core-6.1.2.jar:?]
	... 25 more
ERROR 2019-04-17 10:41:17,201 [stardog-user-3] com.complexible.stardog.protocols.http.server.StardogHttpServiceLoader:accept(232): An exception was handled by the server: com.complexible.stardog.virtual.vega.UncheckedSqlParseException: org.apache.calcite.runtime.CalciteContextException: From line 18, column 10 to line 18, column 15: Object 'device' not found
ttp://topology/asset/port/{port_id") as ?portiri)
    }


INFO 2019-04-17 10:41:17,196 [stardog-user-3] com.complexible.stardog.virtual.vega.calcite.VegaJdbcSchema:computeTables(263): No tables found in T_Topology.T_Topology

ERROR 2019-04-17 10:41:17,196 [stardog-user-3] org.apache.calcite.runtime.CalciteException:<init>(59): org.apache.calcite.sql.validate.SqlValidatorException: Object 'device' not found

ERROR 2019-04-17 10:41:17,197 [stardog-user-3] org.apache.calcite.runtime.CalciteException:<init>(61): org.apache.calcite.runtime.CalciteContextException: From line 18, column 10 to line 18, column 15: Object 'device' not found

ERROR 2019-04-17 10:41:17,198 [stardog-user-3] com.complexible.stardog.virtual.DefaultVirtualGraphRegistry:createVirtualGraph(312): Cannot initialize virtual graph device

java.lang.IllegalArgumentException: com.complexible.stardog.virtual.vega.UncheckedSqlParseException: org.apache.calcite.runtime.CalciteContextException: From line 18, column 10 to line 18, column 15: Object 'device' not found

at com.complexible.stardog.virtual.vega.rdbms.RdbmsMappings.buildTriplesMapsFromSms2Mapping(RdbmsMappings.java:197) ~[stardog-virtual-core-6.1.2.jar:?]

at java.util.stream.ReferencePipeline$3$1.accept(Unknown Source) ~[?:1.8.0_201]

at java.util.ArrayList$ArrayListSpliterator.forEachRemaining(Unknown Source) ~[?:1.8.0_201]

at java.util.stream.AbstractPipeline.copyInto(Unknown Source) ~[?:1.8.0_201]

at java.util.stream.AbstractPipeline.wrapAndCopyInto(Unknown Source) ~[?:1.8.0_201]

at java.util.stream.ReduceOps$ReduceOp.evaluateSequential(Unknown Source) ~[?:1.8.0_201]

at java.util.stream.AbstractPipeline.evaluate(Unknown Source) ~[?:1.8.0_201]

at java.util.stream.ReferencePipeline.collect(Unknown Source) ~[?:1.8.0_201]

at com.complexible.stardog.virtual.vega.rdbms.RdbmsMappings.parseSms2Mappings(RdbmsMappings.java:175) ~[stardog-virtual-core-6.1.2.jar:?]

at com.complexible.stardog.virtual.vega.rdbms.RdbmsMappings.parseOrGenerateMappings(RdbmsMappings.java:122) ~[stardog-virtual-core-6.1.2.jar:?]

at com.complexible.stardog.virtual.vega.rdbms.RdbmsMappings.parseOrGenerateMappings(RdbmsMappings.java:107) ~[stardog-virtual-core-6.1.2.jar:?]

at com.complexible.stardog.virtual.vega.rdbms.RdbmsMappings.parseOrGenerateMappings(RdbmsMappings.java:92) ~[stardog-virtual-core-6.1.2.jar:?]

at com.complexible.stardog.virtual.vega.rdbms.RdbmsVirtualGraphFactory.create(RdbmsVirtualGraphFactory.java:76) ~[stardog-virtual-core-6.1.2.jar:?]

at com.complexible.stardog.virtual.vega.rdbms.RdbmsVirtualGraphFactory.create(RdbmsVirtualGraphFactory.java:44) ~[stardog-virtual-core-6.1.2.jar:?]

at com.complexible.stardog.virtual.DefaultVirtualGraphRegistry.createVirtualGraph(DefaultVirtualGraphRegistry.java:309) ~[stardog-virtual-core-6.1.2.jar:?]

at com.complexible.stardog.virtual.DefaultVirtualGraphRegistry.createVirtualGraph(DefaultVirtualGraphRegistry.java:295) ~[stardog-virtual-core-6.1.2.jar:?]

at com.complexible.stardog.virtual.DefaultVirtualGraphRegistry.add(DefaultVirtualGraphRegistry.java:169) ~[stardog-virtual-core-6.1.2.jar:?]

at com.complexible.stardog.virtual.SecuredVirtualGraphRegistry.add(SecuredVirtualGraphRegistry.java:133) ~[stardog-virtual-core-6.1.2.jar:?]

at com.complexible.stardog.protocols.http.server.virtual.admin.VirtualGraphHttpService.addVG(VirtualGraphHttpService.java:344) ~[stardog-virtual-protocols-http-server-6.1.2.jar:?]

at com.complexible.stardog.protocols.http.server.virtual.admin.VirtualGraphHttpService.add(VirtualGraphHttpService.java:144) ~[stardog-virtual-protocols-http-server-6.1.2.jar:?]

at com.stardog.http.server.undertow.jaxrs.ExtractRoutes.lambda$handleIt$5(ExtractRoutes.java:192) ~[stardog-protocols-http-server-6.1.2.jar:?]

at org.apache.shiro.subject.support.SubjectRunnable.doRun(SubjectRunnable.java:120) [shiro-core-1.2.3.jar:1.2.3]

at org.apache.shiro.subject.support.SubjectRunnable.run(SubjectRunnable.java:108) [shiro-core-1.2.3.jar:1.2.3]

at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) [?:1.8.0_201]

at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) [?:1.8.0_201]

at java.lang.Thread.run(Unknown Source) [?:1.8.0_201]

Caused by: com.complexible.stardog.virtual.vega.UncheckedSqlParseException: org.apache.calcite.runtime.CalciteContextException: From line 18, column 10 to line 18, column 15: Object 'device' not found

at com.complexible.stardog.virtual.vega.calcite.RelSqlParser.parseToRelNode(RelSqlParser.java:98) ~[stardog-virtual-core-6.1.2.jar:?]

at com.complexible.stardog.virtual.vega.rdbms.RdbmsMappings.lambda$parseOrGenerateMappings$1(RdbmsMappings.java:90) ~[stardog-virtual-core-6.1.2.jar:?]

at com.complexible.stardog.virtual.vega.rdbms.RdbmsMappings.buildTriplesMapsFromSms2Mapping(RdbmsMappings.java:190) ~[stardog-virtual-core-6.1.2.jar:?]

... 25 more

Caused by: org.apache.calcite.tools.ValidationException: org.apache.calcite.runtime.CalciteContextException: From line 18, column 10 to line 18, column 15: Object 'device' not found

at com.complexible.stardog.virtual.vega.calcite.RelSqlParser.sqlAstToValidatedAst(RelSqlParser.java:75) ~[stardog-virtual-core-6.1.2.jar:?]

at com.complexible.stardog.virtual.vega.calcite.RelSqlParser.parseToRelNode(RelSqlParser.java:95) ~[stardog-virtual-core-6.1.2.jar:?]

at com.complexible.stardog.virtual.vega.rdbms.RdbmsMappings.lambda$parseOrGenerateMappings$1(RdbmsMappings.java:90) ~[stardog-virtual-core-6.1.2.jar:?]

at com.complexible.stardog.virtual.vega.rdbms.RdbmsMappings.buildTriplesMapsFromSms2Mapping(RdbmsMappings.java:190) ~[stardog-virtual-core-6.1.2.jar:?]

... 25 more

Caused by: org.apache.calcite.runtime.CalciteContextException: From line 18, column 10 to line 18, column 15: Object 'device' not found

at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[?:1.8.0_201]

at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source) ~[?:1.8.0_201]

at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source) ~[?:1.8.0_201]

at java.lang.reflect.Constructor.newInstance(Unknown Source) ~[?:1.8.0_201]

at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:463) ~[calcite-core-1.16.0.jar:1.16.0]

at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:803) ~[calcite-core-1.16.0.jar:1.16.0]

at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:788) ~[calcite-core-1.16.0.jar:1.16.0]

at org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:4706) ~[calcite-core-1.16.0.jar:1.16.0]

at org.apache.calcite.sql.validate.IdentifierNamespace.resolveImpl(IdentifierNamespace.java:172) ~[calcite-core-1.16.0.jar:1.16.0]

at org.apache.calcite.sql.validate.IdentifierNamespace.validateImpl(IdentifierNamespace.java:177) ~[calcite-core-1.16.0.jar:1.16.0]

at org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:84) ~[calcite-core-1.16.0.jar:1.16.0]

at org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:947) ~[calcite-core-1.16.0.jar:1.16.0]

at org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:928) ~[calcite-core-1.16.0.jar:1.16.0]

at org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom(SqlValidatorImpl.java:2975) ~[calcite-core-1.16.0.jar:1.16.0]

at org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom(SqlValidatorImpl.java:2960) ~[calcite-core-1.16.0.jar:1.16.0]

at org.apache.calcite.sql.validate.SqlValidatorImpl.validateJoin(SqlValidatorImpl.java:3011) ~[calcite-core-1.16.0.jar:1.16.0]

at org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom(SqlValidatorImpl.java:2969) ~[calcite-core-1.16.0.jar:1.16.0]

at org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3219) ~[calcite-core-1.16.0.jar:1.16.0]

at org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:60) ~[calcite-core-1.16.0.jar:1.16.0]

at org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:84) ~[calcite-core-1.16.0.jar:1.16.0]

at org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:947) ~[calcite-core-1.16.0.jar:1.16.0]

at org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:928) ~[calcite-core-1.16.0.jar:1.16.0]

at org.apache.calcite.sql.SqlSelect.validate(SqlSelect.java:226) ~[calcite-core-1.16.0.jar:1.16.0]

at org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:903) ~[calcite-core-1.16.0.jar:1.16.0]

at org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:613) ~[calcite-core-1.16.0.jar:1.16.0]

at com.complexible.stardog.virtual.vega.calcite.RelSqlParser.sqlAstToValidatedAst(RelSqlParser.java:72) ~[stardog-virtual-core-6.1.2.jar:?]

at com.complexible.stardog.virtual.vega.calcite.RelSqlParser.parseToRelNode(RelSqlParser.java:95) ~[stardog-virtual-core-6.1.2.jar:?]

at com.complexible.stardog.virtual.vega.rdbms.RdbmsMappings.lambda$parseOrGenerateMappings$1(RdbmsMappings.java:90) ~[stardog-virtual-core-6.1.2.jar:?]

at com.complexible.stardog.virtual.vega.rdbms.RdbmsMappings.buildTriplesMapsFromSms2Mapping(RdbmsMappings.java:190) ~[stardog-virtual-core-6.1.2.jar:?]

... 25 more

Caused by: org.apache.calcite.sql.validate.SqlValidatorException: Object 'device' not found

at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[?:1.8.0_201]

at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source) ~[?:1.8.0_201]

at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source) ~[?:1.8.0_201]

at java.lang.reflect.Constructor.newInstance(Unknown Source) ~[?:1.8.0_201]

at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:463) ~[calcite-core-1.16.0.jar:1.16.0]

at org.apache.calcite.runtime.Resources$ExInst.ex(Resources.java:572) ~[calcite-core-1.16.0.jar:1.16.0]

at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:803) ~[calcite-core-1.16.0.jar:1.16.0]

at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:788) ~[calcite-core-1.16.0.jar:1.16.0]

at org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:4706) ~[calcite-core-1.16.0.jar:1.16.0]

at org.apache.calcite.sql.validate.IdentifierNamespace.resolveImpl(IdentifierNamespace.java:172) ~[calcite-core-1.16.0.jar:1.16.0]

at org.apache.calcite.sql.validate.IdentifierNamespace.validateImpl(IdentifierNamespace.java:177) ~[calcite-core-1.16.0.jar:1.16.0]

at org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:84) ~[calcite-core-1.16.0.jar:1.16.0]

at org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:947) ~[calcite-core-1.16.0.jar:1.16.0]

at org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:928) ~[calcite-core-1.16.0.jar:1.16.0]

at org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom(SqlValidatorImpl.java:2975) ~[calcite-core-1.16.0.jar:1.16.0]

at org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom(SqlValidatorImpl.java:2960) ~[calcite-core-1.16.0.jar:1.16.0]

at org.apache.calcite.sql.validate.SqlValidatorImpl.validateJoin(SqlValidatorImpl.java:3011) ~[calcite-core-1.16.0.jar:1.16.0]

at org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom(SqlValidatorImpl.java:2969) ~[calcite-core-1.16.0.jar:1.16.0]

at org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3219) ~[calcite-core-1.16.0.jar:1.16.0]

at org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:60) ~[calcite-core-1.16.0.jar:1.16.0]

at org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:84) ~[calcite-core-1.16.0.jar:1.16.0]

at org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:947) ~[calcite-core-1.16.0.jar:1.16.0]

at org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:928) ~[calcite-core-1.16.0.jar:1.16.0]

at org.apache.calcite.sql.SqlSelect.validate(SqlSelect.java:226) ~[calcite-core-1.16.0.jar:1.16.0]

at org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:903) ~[calcite-core-1.16.0.jar:1.16.0]

at org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:613) ~[calcite-core-1.16.0.jar:1.16.0]

at com.complexible.stardog.virtual.vega.calcite.RelSqlParser.sqlAstToValidatedAst(RelSqlParser.java:72) ~[stardog-virtual-core-6.1.2.jar:?]

at com.complexible.stardog.virtual.vega.calcite.RelSqlParser.parseToRelNode(RelSqlParser.java:95) ~[stardog-virtual-core-6.1.2.jar:?]

at com.complexible.stardog.virtual.vega.rdbms.RdbmsMappings.lambda$parseOrGenerateMappings$1(RdbmsMappings.java:90) ~[stardog-virtual-core-6.1.2.jar:?]

at com.complexible.stardog.virtual.vega.rdbms.RdbmsMappings.buildTriplesMapsFromSms2Mapping(RdbmsMappings.java:190) ~[stardog-virtual-core-6.1.2.jar:?]

... 25 more

ERROR 2019-04-17 10:41:17,201 [stardog-user-3] com.complexible.stardog.protocols.http.server.StardogHttpServiceLoader:accept(232): An exception was handled by the server: com.complexible.stardog.virtual.vega.UncheckedSqlParseException: org.apache.calcite.runtime.CalciteContextException: From line 18, column 10 to line 18, column 15: Object 'device' not found

Too bad SQLite is not supported it would allow me a temporary workaround other than using CSV's

(Serge Colle) #13

@jess, @zachary.whitley

This line caught my attention.

If I used the full qualified name of the table (database.schema.table) it would be T_Topology.dbo.device, so really weird that it has T_Topology.T_Topology

(zachary.whitley) #14

My suggestion was in response to your error

@jess might be doing better than me but I'm having trouble following along with where you are at with your trouble shooting and what errors you're getting. I'm guessing you've worked past the jdbc connection issue.

(Serge Colle) #15

Initially I could not connect, but if you look at Trouble connecting to MSSQL we got it to connect (I think) but it can't find any tables. That what me @jess were debugging by end of day yesterday.

(Serge Colle) #16

On a slightly different topic but somewhat relevant. I am going to use CSV as a short term workaround until we resolve the issue, I was hoping that the only part I would need to change would be the FROM. For example

FROM CSV {
filename: /blah
}

This would allow people to switch source quickly when needed. Now I need to switch to SMS or R2RML.

Just my 2 cents.

(Matthias Autrata) #17

Try d2rq as a stopgap?

Matthias Autrata

201-305-0042

(Serge Colle) #18

Thanks @matthias,

Looks interesting and I will definition have a look. While tedious I had already done the conversion, since I need to validate the model ASAP

(zachary.whitley) #19

You just referred to this thread. Did you mean to reference some other thread? It’s hard to tell exactly what you’ve done, what settings you’ve tried and if you’ve set them back or left them that way when you move on. It makes it difficult to know what state you’re in.

Are you really running sqlserver on port 3701? I don’t think that’s the default port. That’s fine but one of your connection strings was using 3701 and the other the default.

Are you using integrated security or username and password? Integrated security is a little more difficult to setup with jdbc. Does the jtds driver support integrated security? The MS driver requires that you install a dll. Not sure what is required with Linux. You should probably work that out first rather than just trying options trying to find one that seems to work.

Do you really have an instance called SQL2008? Again one of your connection strings connected to this instance and the other the default.

(Serge Colle) #20

Here a recap

I am using the string mention in the initial post with a slight adjustment since has mentioned to be able to connect to MSSQL from stardog, I needed both the username/password in the url string as well as setting the jdbc.username and jdbc.password properties. I am not running on Linux at the moment but on my own Windows laptop. The only special consideration is that I am not on the same Domain as the MSSQL server thus the reason for the domain=MyDomain in the URL string.

This seem to connect thought I would like to know why username/password is needed in the URL string, however the issue is that none of the query will work as it can't find any table which are definitely there and I am able to access via SQuirreL using the same connection url/settings with the same driver/dll's. When I run the query, it says it can't find T_Topology.T_Topology. Why is it looking for that. I currently have this definition

PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#> 
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#> 
PREFIX loc: <http://topology/location#> 
PREFIX sm: <tag:stardog:api:mapping:> 
PREFIX wgs: <http://www.w3.org/2003/01/geo/wgs84_pos#> 
PREFIX geo: <http://www.opengis.net/ont/geosparql#> 
PREFIX : <http://blog.stardog.com/geons/> 

MAPPING <urn:device>
FROM SQL {
    select 
        device.device_unique_id as id,
        device.device_name as name,
        device.device_clli as clli,
        device.device_type as type, 
        device.device_subtype as subtype, 
        device_port.port_unique_id as port_id
    FROM device
    INNER JOIN device_port ON device.device_unique_id=device_port.device_unique_id
}

TO {
    ?deviceiri a loc:Device ;
        loc:name ?name ;
        loc:clli ?clli;
        loc:type ?type;
        loc:subtype ?subtype;
        loc:hasPort ?portiri .
    

}
WHERE {  
    BIND(template("http://topology/asset/device/{id") as ?deviceiri)
    BIND(template("http://topology/asset/port/{port_id") as ?portiri)
}

and when I run

serge@CA-L7W9PJM2:~/Documents/git/rdf-workspace$ stardog-admin virtual add --format sms2 device.properties device.ttl
From line 18, column 10 to line 18, column 15: Object 'device' not found

I also tried using the fully qualified name for the table T_Topology.dbo.device same result and same weird statement of it looking for T_topology.T_topology object in the log.

The driver I am using is jdts 1.3.1 and the stack trace is available in a previous post.

Thanks