LINEKED TABLE contains a select query to a row name with slachs

[Sofiane CHELABI]

Hi all,

I'm trying to create an H2  link to an oracle database with this query :

CREATE LINKED TABLE MyLinekTable('oracle.jdbc.OracleDriver','jdbc:oracle:thin:@127.0.0.1:1521:orcl','login','password','(SELECT "/BIC/ZBSARK" as FLOW FROM MYTABLE)');

And i get this error : 

Error: ORA-01424: missing or illegal character following the escape character

; SQL statement:

CREATE LINKED TABLE P710_ParamFRS2('oracle.jdbc.OracleDriver','jdbc:oracle:thin:@18.218.41.104:1521:orcl','C##DEV09','bwdev09','(SELECT "/BIC/ZBSARK" as FLOW FROM ZPARAM_FRS)') [1424-197]

SQLState:  22025

ErrorCode: 1424

My remote table (on oracle) contains 2 columns : /BIC/ZBSARK and PLANT 

[create table MYTABLE(

 "/BIC/ZBSARK" varchar(20),

  PLANT varchar(20))

When i try with the PLANT  column instead the "/BIC/ZBSARK" this work well :

CREATE LINKED TABLE MyLinekTable('oracle.jdbc.OracleDriver','jdbc:oracle:thin:@127.0.0.1:1521:orcl','login','password','(SELECT  "PLAN as FLOW FROM MYTABLE)'); 

I tried to escape the "/" with "\" and [] and \Q..\E but no sucess !

Can some one help or has the same problem ?

Thanks a lot !

[Evgenij Ryazanov]

Hello.

It looks like the problem is caused by H2's attempt to fetch metadata of the source table. H2 tries to pass its name as is even when it definitely is a query and not a plain name.

I filled an issue about it on GitHub:

https://github.com/h2database/h2database/issues/2073

I don't think that there are workarounds if you really need to use that query for a some reason, but you can try to create a link to the whole table by specifying its name instead of such query.

Do you really need to have a column with such unusual name? It's just asking for trouble.

[Sofiane CHELABI]

Hello,

Thank you for your reply.

Yes it seems like a H2 issue (thanks for the opend issue on github ;) )

Yes i need exactlly to do this (customer need), my solution allow to link from H2 to a remote data , and the user can  choose the kind of the remote data (table ro a result from a select querry ..).

Thank you again for you help.

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/e20cd825-5957-4700-90b3-bcdbdcfca153%40googlegroups.com.

--

Best regards,

Sofiane Chelabi | IT Engineer

Developpement & Consulting

ESB/ETL/BPM/BigData - JavaEE

Mob : +33 (0)7 60 85 09 95

Lyon - FRANCE

-----------------------------------------------------------------------------------

[Evgenij Ryazanov]

Could you try to build H2 from its current sources and re-test your code?

Sources are here:

https://github.com/h2database/h2database

Building instructions are here:

https://h2database.com/html/build.html#building

You need the jar target.

[Sofiane CHELABI]

Hi Ryazanov,

Good news ! 

I re-tested with the latest master version from the https://github.com/h2database/h2database and it's working well.

Thank you a lot for your help.

Could you please tel me when and in witch H2 standard version the issue https://github.com/h2database/h2database/issues/2073 will be integrated ?  (i prefere use a standad version instead of my local build one ^^)

Have a nice week.