[jira] [Commented] (GUACAMOLE-1300) Implement the guacamole with Oracle DB

["Nick Couchman (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/GUACAMOLE-1300?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17290865#comment-17290865 ] 

Nick Couchman commented on GUACAMOLE-1300:
------------------------------------------

Implementing an Oracle JDBC module is a little bit tricky. Overall it should be roughly the same as any of the other modules, in that you would create a new JDBC module (guacamole-auth-jdbc-oracle, most likely) and then implement the Oracle-specific bits like configuration parameters, Oracle-specific and/or optimized queries, etc., to fit the Oracle environment.

However, I actually started to do this a couple of years ago around the same time as I worked on the SQL Server code, and ran into one issue that was going to be a lot of work to untangle. If I recall correctly, it had to do with Oracle not supporting multiple SQL statements in a single query. If you look at, for example, the select clause of the ConnectionMapper class:

https://github.com/apache/guacamole-client/blob/5da596137c9464902e4cc05f06d91d08cbc8834c/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/src/main/resources/org/apache/guacamole/auth/jdbc/connection/ConnectionMapper.xml#L124-L169

You'll see that there are multiple SELECT statements in that single query that get mapped to various result sets specified toward the top of that file:

https://github.com/apache/guacamole-client/blob/5da596137c9464902e4cc05f06d91d08cbc8834c/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/src/main/resources/org/apache/guacamole/auth/jdbc/connection/ConnectionMapper.xml#L26-L58

Which are in turn returned to the specified Java class:

https://github.com/apache/guacamole-client/blob/master/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/connection/ConnectionModel.java

However, the Oracle database - or, at the very least, the current version of the Oracle JDBC driver at the time I was trying to implement it - does not allow you to run multiple queries like that. Each of those "SELECT" statement within that code block would have to be broken out into its own individual statement so that it could be executed and returned separately. At the time I was looking at it I could not find a good way to make that work, and it looked like it was going to require some major reworking of the Java code in the base module to make it work, which I didn't want to do.

To be fair, I didn't spend a huge amount of time on it - I basically decided that, if Oracle was going to make it so difficult to do something that other leading RDBMS systems did without problem (MySQL, Postgres, and SQL Server), then it wasn't worth the time to implement an Oracle module. I did run into similar issues, I believe, trying to implement a SQLite JDBC module, as well, so Oracle wasn't the only one.

There may be some MyBatis magic that I missed when I was trying it that would help solve this problem, but, again, i didn't spend a huge amount of time trying to resolve it.

> Implement the guacamole with Oracle DB 
> ---------------------------------------
>
>                 Key: GUACAMOLE-1300
>                 URL: https://issues.apache.org/jira/browse/GUACAMOLE-1300
>             Project: Guacamole
>          Issue Type: Improvement
>          Components: guacamole
>         Environment: RHEL 7
>            Reporter: kathiresh
>            Priority: Minor
>
> Hi Team,
> We would like to configure the Guacamole service with the Oracle DB.
> I have checked in the official document but we didn't get the details.
> Please guide us to implement the same. 
>  



--
This message was sent by Atlassian Jira
(v8.3.4#803005)