Trouble connecting to MSSQL

serge · April 16, 2019, 5:42pm

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 · April 16, 2019, 7:41pm

Which version of SQL Server are you connecting to?

serge · April 16, 2019, 8:45pm

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 · April 16, 2019, 8:50pm

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 · April 16, 2019, 8:54pm

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 · April 16, 2019, 8:59pm

which driver and version are you using now?

serge · April 16, 2019, 9:01pm

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 · April 17, 2019, 12:49pm

I tried

but did not work.

zachary.whitley · April 17, 2019, 1:10pm

Try setting IntegratedSecurity=false

serge · April 17, 2019, 1:27pm

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 · April 17, 2019, 2:00pm

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

serge · April 17, 2019, 2:51pm

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 · April 17, 2019, 3:45pm

@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 · April 17, 2019, 4:15pm

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 · April 17, 2019, 5:45pm

Initially I could not connect, but if you look at Trouble connecting to MSSQL - #3 by serge 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 · April 17, 2019, 5:49pm

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 · April 17, 2019, 6:03pm

Try d2rq as a stopgap?

Matthias Autrata

201-305-0042

serge · April 17, 2019, 7:34pm

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 · April 18, 2019, 10:28am

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 · April 18, 2019, 12:58pm

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

Topic		Replies	Views
Trouble connecting to MSSQL part 2 Support	15	143	January 16, 2025
Create virtual graph from db in MSSQLSERVER Support	2	517	May 31, 2018
Virtual graph based on a SQL database Support	2	696	May 31, 2018
Stardog in Linux Support	3	585	May 8, 2018
Music.properties example for SQL Server 12.0 Support	2	380	August 1, 2019

Trouble connecting to MSSQL

Related topics