I've been fighting the sql query parser for virtual graphs all day 5hrs+ now.
I've setup my .properties file so that:
jdbc.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
sql.functions=charindex,substring
And submitting the following query:
foo:{ID}\/address a schema:address;
schema:addressLocality "{City}";
schema:addressRegion "{Region}";
schema:addressCountry "{Country}";
schema:postalCode "{Zip}";
sm:map [
sm:query """
select
ID
,City
,Zip
, CASE WHEN CHARINDEX('_',StateProvID) > 0
THEN UPPER(SUBSTRING(StateProvID , 0 , CHARINDEX('_',StateProvID) ))
ELSE ''
END
as Country
, CASE WHEN CHARINDEX('_',StateProvID) > 0
THEN UPPER(SUBSTRING(StateProvID , CHARINDEX('_',StateProvID) + 1 , LEN(StateProvID)- CHARINDEX('_',StateProvID)))
ELSE ''
END
as Region
from foo
WHERE baz = 'TRUE'
"""
].
And I get this error:
com.complexible.stardog.cli.CliException: Unable to parse R2RML view query
[[
...
]]:
From line 8, column 21 to line 8, column 86:
Cannot apply 'SUBSTRING' to arguments of type 'SUBSTRING(<VARCHAR(8)> FROM <INTEGER> FOR <VARCHAR(1)>)'.
Supported form(s): 'SUBSTRING(<CHAR> FROM <INTEGER>)' 'SUBSTRING(<CHAR> FROM <INTEGER> FOR <INTEGER>)' 'SUBSTRING(<VARCHAR> FROM <INTEGER>)' 'SUBSTRING(<VARCHAR> FROM <INTEGER> FOR <INTEGER>)' 'SUBSTRING(<BINARY> FROM <INTEGER>)' 'SUBSTRING(<BINARY> FROM <INTEGER> FOR <INTEGER>)' 'SUBSTRING(<VARBINARY> FROM <INTEGER>)' 'SUBSTRING(<VARBINARY> FROM <INTEGER> FOR <INTEGER>)'
at com.complexible.stardog.cli.impl.ConnectionCommand.call(ConnectionCommand.java:73)
But I've got SubString added to sql.functions in my .properties file..?!
Ok.. I'll use a different function..
Properties file:
jdbc.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
sql.functions=charindex,substring,LEFT,RIGHT
but now..
ERROR 2017-07-27 15:20:49,865 [XNIO-1 task-1] com.complexible.stardog.virtual.DefaultVirtualGraphRegistry:_add(201): Cannot initialize virtual graph 0ee4daa6-30ae-490f-8399-befb4e3080fd
java.lang.IllegalArgumentException: Unable to parse R2RML view query [[
select
ID
,City
,Zip
, CASE WHEN CHARINDEX('_',StateProvID) > 0
THEN UPPER(LEFT(StateProvID , CHARINDEX('_',StateProvID) -1 ))
ELSE ''
END
as Country
, CASE WHEN CHARINDEX('_',StateProvID) > 0
THEN UPPER(RIGHT(StateProvID , LEN(StateProvID) - CHARINDEX('_',StateProvID)))
ELSE ''
END
as Region
from foo
WHERE baz = 'TRUE'
]]: Encountered "( LEFT" at line 8, column 20
at com.complexible.stardog.virtual.vega.VegaTriplesMap.parseViewQuery(VegaTriplesMap.java:190) ~[stardog-virtual-core-5.0.1.jar:?]
I did have "LEFT" in the .properties file... which the docs say:
sql.functions
A comma-separated list of SQL function names to register with the parser. If an R2RML view (using rr:sqlQuery) fails to parse, this option can be set to allow use of non-standard functions.
So that's not working..
So finally I decide to just dump all the data after its processed into a temp table...
select
ID
,City
,Zip
, CASE WHEN CHARINDEX('_',StateProvID) > 0
THEN UPPER(LEFT(StateProvID , CHARINDEX('_',StateProvID) -1 ))
ELSE ''
END
as Country
, CASE WHEN CHARINDEX('_',StateProvID) > 0
THEN UPPER(RIGHT(StateProvID , LEN(StateProvID) - CHARINDEX('_',StateProvID)))
ELSE ''
END
as Region
INTO ##MY_SHARED_TEMP_TABLE
from foo
WHERE baz = 'TRUE'
And then I change my virtual graph query to pull from the temp table. But that's not working either!
ERROR 2017-07-27 15:02:44,619 [XNIO-1 task-55] com.complexible.stardog.virtual.DefaultVirtualGraphRegistry:_add(201): Cannot initialize virtual graph d360f0ff-eee7-4b4b-b527-e78c79a01bf1
java.lang.IllegalArgumentException: Unable to parse R2RML view query [[
select
ID
,City
,Zip
,Country
,Region
from ##MY_SHARED_TEMP_TABLE
]]:
Lexical error at line 8, column 14. Encountered: "#" (35), after : ""
at com.complexible.stardog.virtual.vega.VegaTriplesMap.parseViewQuery(VegaTriplesMap.java:190) ~[stardog-virtual-core-5.0.1.jar:?]
I've been burning a lot of time on this and I can't understand why it wont just send my query as is to the jdbc driver and insists on rewriting it.
Other variations I've tried continue to generate invalid sql which causes the jdbc driver to throw and error, or it simply won't get passed the stardog sql parser.
eg: Invalid SQL sent to the JDBC driver
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: An expression of non-boolean type specified in a context where a condition is expected, near '('.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:217)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1655)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:885)
at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:778)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7505)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2445)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:191)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:166)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQuery(SQLServerStatement.java:677)
at com.complexible.stardog.virtual.VirtualGraphIterator.nextMapping(VirtualGraphIterator.java:121)
... 52 more
WARN 2017-07-27 14:56:38,126 [XNIO-1 task-38] com.complexible.common.rdf.rio.RDFStreamProcessor:setException(587):
Error during loading Memory stream (? triples):
Unable to execute query: SELECT ... CASE WHEN "charindex"('_', "StateProvID") > 0 ...
FROM "dbo"."foo"
WHERE "bar" = 'TRUE'
com.complexible.stardog.plan.eval.operator.OperatorException:
Unable to execute query: SELECT ... CASE WHEN "charindex"('_', "StateProvID") > 0 ...
FROM "dbo"."foo"
WHERE "bar" = 'TRUE'
at com.complexible.stardog.virtual.VirtualGraphIterator.computeNext(VirtualGraphIterator.java:228) ~[stardog-virtual-core-5.0.1.jar:?]