You should be pretty close at this point if you can connect and query from squirrel using the same JDBC driver and the same connection string.
I wonder if we are hitting issues either due to casing or reserved words. I'd try:
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]
}
@PaulJackson thanks for suggestion, did not fix issue.
Can someone look at the code to see whether this is coming from remote DB or stardog itself.
ERROR 2019-04-17 11:18:15,563 [stardog-user-8] 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
INFO 2019-04-17 11:26:31,505 [stardog-user-9] com.complexible.stardog.virtual.vega.calcite.VegaJdbcSchema:computeTables(263): No tables found in T_Topology.T_Topology
ERROR 2019-04-17 11:26:31,506 [stardog-user-9] org.apache.calcite.runtime.CalciteException:<init>(59): org.apache.calcite.sql.validate.SqlValidatorException: Object 'device' not found
ERROR 2019-04-17 11:26:31,507 [stardog-user-9] 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 11:26:31,509 [stardog-user-9] 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:?]
The line that concerns me is
INFO 2019-04-17 11:26:31,505 [stardog-user-9] com.complexible.stardog.virtual.vega.calcite.VegaJdbcSchema:computeTables(263): No tables found in T_Topology.T_Topology
T_Topology is the catalog/database. A catalog contains one more schemas. Did you add T_Topology to the sql.schemas virtual graph configuration property? If so, we are interpreting that entry as a schema in the current catalog. This T_Toplogy schema presumably doesn't exist.
The error refers directly to the device table which is referenced in your mapping. The line number should correspond with the line in your mapping where you reference this table. So the question this raises is whether or not there's a casing issue in the query. If the devices table is actually Devices, with a capital D, you will need to update your mappings.
We are planning to release a metadata diagnostic tool soon to assist in debugging these types of issues.
As mentioned the fully qualified name is T_topology.dbo.device (Database.schema.table). I tried to set the sql.schemas to dbo which I believe is the default.
Our in-house testing for SQL Server uses the Microsoft driver. I just did some testing with the JTDS driver and I saw the problem you were encountering. (That driver throws an exception when you call java.sql.Connection.getSchema() and we fall back to getCatalog() when that happens, which wasn't helping in this case.) The work-around was to set sql.default.schema=dbo.
I also noticed that you don't have the database set in your connection url. I think you should change it to: jdbc:jtds:sqlserver://BLMCIK:3701/T_topology;domain=MyDomain;username=serge.colle;password=mypassword
For posterity, and for anyone who finds this page, we have two properties:
sql.schemas, which is a comma-delimited list of schemas that the virtual graph will have access to (but you'll still need to qualify the tables in your mappings with the schema name), and sql.default.schema, which gives an option to override which schema to use for unqualified table names.