R2RML change in stardog 5?

Support
1

I took a working cli command from version 4.

./stardog-admin virtual import CRAB2 /path/to/x.properties /path/to/mappings/x.ttl --format r2rml -v

but getting in 5 following error message

Unable to parse R2RML view query [[SELECT a.ObjectId, a.VersieId, a.StraatnaamObjectId, g.ObjectId as gemeenteID, a.Huisnummer, a.Busnummer, a.Postcode, a.Officieus, a.AdresStatus FROM Adres a LEFT JOIN Straatnaam s on a.StraatnaamObjectId=s.ObjectId LEFT JOIN Gemeente g on s.GemeenteObjectId=g.ObjectId ]]: From line 3, column 6 to line 3, column 10: Table 'ADRES' not found

Table ‘Adres’ is available.

Forgot to mention: connecting to SQLserver (Microsoft).

2

You might want to try quoting the table name. The default is to use MySql quoting with the backtick but you can change that to double quote with the new option parser.mysql.quoting.

parser.mysql.quoting

If unspecified, R2RML views (using rr:sqlQuery) will be parsed using the MySQL convention of treating backtick as the identifier quote character. If set to ANSI, the ANSI SQL convention of treating a double quote as the identifier quote character will be used.

3

No, tried all possible variations but no success so far.
Will look at it again with a colleague next Thursday.

4

I do not have access to mysql, but the same works on PostgresQL.
Something Microsoft SQL specific?

5

Possibly. I'm guessing it has to do with the default collation. I'm basing this completely on the table being named 'Adres' but the error saying it can't find 'ADRES'.

You might be able to work around it if you are able to change the collation for your database to be case insensitive. See this StackOverflow question for how to do that.

6

Paul,
Which JDBC driver version are you using? Can you try quoting the table name with [], e.g. select ... from [Adres]?
Jess

7

Zach,
The parser.mysql.quoting option only applies when connecting to MySQL. I’ll update the docs to make this clear.

Jess

8

Thanks. For some reason I thought it was the difference between ANSI quoting and whatever the hell quoting that particular db uses. I also didn’t thing brackets preserved case but only special chars like spaces but I’m probably wrong on that. I haven’t used SQL Server in a long time.

9

sqljdbc42.jar is the driver

with []

./stardog-admin virtual import CRAB2 /Users/Paul/Desktop/Projects/CRAB/stardog/oslo2.properties /Users/Paul/Desktop/Projects/CRAB/stardog/mappings/Adres2.ttl --format r2rml -v
Unable to parse R2RML view query [[SELECT * FROM [Adres]  LEFT JOIN Straatnaam s on a.StraatnaamObjectId=s.ObjectId LEFT JOIN Gemeente g on s.GemeenteObjectId=g.ObjectId ]]: Encountered "FROM [" at line 1, column 10
The detailed stack trace for the error is:
com.complexible.stardog.cli.CliException: Unable to parse R2RML view query [[SELECT * FROM [Adres]  LEFT JOIN Straatnaam s on a.StraatnaamObjectId=s.ObjectId LEFT JOIN Gemeente g on s.GemeenteObjectId=g.ObjectId ]]: Encountered "FROM [" at line 1, column 10
	at com.complexible.stardog.cli.impl.ConnectionCommand.call(ConnectionCommand.java:73)
	at com.complexible.stardog.cli.CLIBase.execute(CLIBase.java:55)
	at com.complexible.stardog.cli.admin.CLI.main(CLI.java:182)
Caused by: com.complexible.stardog.StardogException: Unable to parse R2RML view query [[SELECT * FROM [Adres]  LEFT JOIN Straatnaam s on a.StraatnaamObjectId=s.ObjectId LEFT JOIN Gemeente g on s.GemeenteObjectId=g.ObjectId ]]: Encountered "FROM [" at line 1, column 10
	at com.complexible.stardog.protocols.http.client.BaseHttpClient.checkResponseCode(BaseHttpClient.java:490)
	at com.complexible.stardog.protocols.http.client.BaseHttpClient.execute(BaseHttpClient.java:322)
	at com.complexible.stardog.protocols.http.client.BaseHttpClient.executeHttpPost(BaseHttpClient.java:638)
	at com.complexible.stardog.protocols.http.client.HttpVirtualGraphAdminClientImpl.addOrUpdate(HttpVirtualGraphAdminClientImpl.java:233)
10

When you said:

Did you mean with regards to quoting? Does it work if you use double quotes? e.g. SELECT * FROM "Adres"

11

Looks like the parser doesn’t like the bracket at all. It’s stuff like this that makes you really love SPARQL.

12

You can try using the double quote in MSSQL if you run 'SET QUOTED_IDENTIFIER on'

13

Zach,
The parsing failure is coming from Stardog, not the database server.

Jess

14

Yes, with regards to quoting, tried many possibilities but with no succes.

15

Yes,

The query as such works directly on the database using Datagrip and Calcite sqlline.

16

Saw that but figured the Stardog parsing might be ok with double quote quoting and let it through but the database server would have to accept the quoting style as well.

17

Zach,
The query is not executed verbatim on the database server. It’s reformulated and quoted separately.

18

No additional things I need to check out or try?

Paul

19

Paul,
This appears to be a new bug related to the schema handling that’s only affecting SQL Server. It’s not related to table casing or quoting but the lack of metadata available for the table due to a missing schema search path. We will fit it in the next 5.0 release.
Jess

20

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