Defining the db using SMS2 and SQL

Support
1

Where do I put the db name when using SMS2 to define a SQL mapping file.

I have:

PREFIX rdf: <..>
PREFIX xsd: <...>
PREFIX myprefix: <...>
MAPPING
FROM SQL
{ select id, description from tableName where id >= 1757910 and id <= 1757920 }
TO
{ ?resId a myprefix:Resource ; myprefix:description ?description . }
WHERE
{ BIND (template("urn:Resource-{id}") AS ?resId) }

As my mapping file.

When I add this via Stardog Studio, I get the error 'Object 'tableName' not found (Internal Server Error)'

Thanks.
Andrea

2

The db name is specified in the JDBC URL in the properties. If the table is in a different schema, you can include it in the sql.schemas or sql.default.schema property.

3

Jess, There is NO DOCUMENTATION for a mappings.syntax option.

We are using SMS2.

Does that mean that we should include (in our options file) ... mappings.syntax=SMS2??

Andrea

4

Is your SMS2 file using a .ttl suffix? We auto detect the format in Studio/Java/CLI but not yet in the Python client library so it must be specified manually.

5

Jess, Where is sql.schemas documented? I am a bit confused.

Andrea

6

It's documented in the list of virtual graph properties. If the table is not in the default schema (usually the username), you would need to set this property to add to the schema search path.

7

OK... In Studio, I defined the table to include, but how do I construct the SQL select clause ...

It does not seem to like the where clause in "select id, description where id >= 1757910 and id <= 1757920"?

Andrea

8

What's the error it's returning?

9

Jess, When we use the select clause,

select id, description from tableName where id >= 1757910 and id <= 1757920

Then, the error is Object 'tableName' not found ... Even though we specified it using sql.schemas.

When we use the select clause,

select id, description where id >= 1757910 and id <= 1757920

Then, the error is Encountered: 'where' Was expecting: ....

Andrea

10

You still need FROM <tablename> in the SQL query. The second query there is not valid syntactically.

11

But the first form returned an error about the Object 'tableName' not found ... which is why we removed it.

12

Should I just use SMS?

Andrea

13

Removing a reference to the table you want to query is not the right solution here. You need to figure out why the table is not being found. That's why I pointed to the properties which allow configuring the schema search path.

14

Yes, we defined sql.schemas=tableName.

15

The hierarchy of containment in a database is the database/catalog at the top level which contains a set of schemas. Each schema contains a set of tables. There is a default schema, sometimes called public or the name of the user, eg dbo. If the table you are trying to query is not in the default schema, you will need to add the name of the schema containing it to the schema search path using the sql.schemas virtual graph option.

16

Jess, Thank you so much. Finally figured this out ... your last reply about the schema solved things for me!
Andrea

2 Likes
17

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