Difficulties with complex schema names in SMS2 mapping

Support
1

Hi all,

am trying to map an existing database using a virtual graph. When I use something like "integrated.dummy" as a table name, I consistently get a server error, that this object was not found.

My actual table names however are something like integrated.il$all$control and I am getting strange syntax errors, even if I enclose them in double quotes.

Any idea on how to proceed ?

Lars

2

The double quotes should work. Can you share the query in the mapping, which DB you're using and the error from the server log?

3

If integrated is the schema name, the query should look like select * from integrated."il$all$control". I just tested it and don't see any particular issue with dollar signs.

4

Hi Jess,

thanks for helping out. This is supposed to work against SQL Server. The general connection seems to work as something like integrated.dummy gives a reasonable error message. However, integrated."dummy" gives an UncheckedSqlParseException at "."" so it does not seem to like the quotes. Nothing suspicious in the logs, though.

Query is like this:

MAPPING <grt:controls>
FROM SQL {
    SELECT * FROM integrated."il$all$control"
}
TO {
    ?controliri a grt:Control .
} 
WHERE {
  BIND (template("http://www.abc.com/graph#Control/{ID_CONTROL}") AS ?controliri)
}

Lars

5

You will need to use the square brackets with SQL Server by default. Eg select * from integrated.[il$all$control]. If you want to use double quotes, you can set parser.sql.quoting=ANSI in your virtual graph options (properties file from command line or the option in Stardog Studio).

6

GREAT ! That did the trick immediately - so the SELECT statement is a verbatim SQL Server syntax and we can use all of its bells and whistles in the mapping ?

Lars

1 Like
7

We parse the queries using db-specific rules and aim to support a reasonably large subset of each system's native syntax. The most common requirement is the use of unrecognized functions which Stardog doesn't allow by default. Unrecognized functions must be declared using the sql.functions virtual graph option.

8

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.