[I] [Feature Request]: Database plugin query comment injection (hop)

["tpickle-amazon (via GitHub)" <gi...@apache.org>]

tpickle-amazon opened a new issue, #3261:
URL: https://github.com/apache/hop/issues/3261

   ### What would you like to happen?
   
   Hello, opening this feature request as a follow-up to this thread: https://chat.project-hop.org/hop/pl/sx5ye8z5ntf9xyu9eab1ibgqwy. My team is developing our own Hop database plugin (i.e. extending `BaseDatabaseMeta`) and have a use case in which we would like to include a comment in every SQL query sent using our custom database plugin. Ideally, this functionality would be specified in the `IDatabase` interface and available to implement/override in our plugin implementation. This would be similar to the existing: `getConnectSql()` method of the `IDatabase` interface. So something like:
   ```
   interface IDatabase {
       String getSqlComment();
   }
   ```
   Then this `getSqlComment()` method could be called by the `Database` class when executing queries, similar to the `Database` class calling `getConnectSql()` when connecting. Happy to answer any questions, thanks for the consideration!
   
   ### Issue Priority
   
   Priority: 3
   
   ### Issue Component
   
   Component: Database


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: issues-unsubscribe@hop.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

Re: [I] [Feature Request]: Database plugin query comment injection (hop)

["tpickle-amazon (via GitHub)" <gi...@apache.org>]

tpickle-amazon commented on issue #3261:
URL: https://github.com/apache/hop/issues/3261#issuecomment-1832436972

   >Is this needed for insert/update/delete/lookup/... as well?
   we are mostly concerned with read queries, so insert/update/delete are probably not needed for now, but lookup would be ideal. 


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: issues-unsubscribe@hop.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

Re: [I] [Feature Request]: Database plugin query comment injection (hop)

["tpickle-amazon (via GitHub)" <gi...@apache.org>]

tpickle-amazon commented on issue #3261:
URL: https://github.com/apache/hop/issues/3261#issuecomment-1832460760

   One potential approach might be to use the `ClientInfo` functionality of the JDBC: https://docs.oracle.com/javase/8/docs/api/java/sql/Connection.html#setClientInfo-java.util.Properties. Based on [these docs](https://dev.mysql.com/doc/connector-j/en/connector-j-connp-props-connection.html)(see `clientInfoProvider`) it seems like JDBC Driver implementations (such as Mysql) can support `ClientInfo` in different ways. Based on the above doc, the default `clientInfoProvider` for MySql is `com.mysql.cj.jdbc.CommentClientInfoProvider` which injects the `ClientInfo` properties as comments into the query. So wondering if it would better align with the JDBC interface/library to use `ClientInfo` properties instead of `getSqlComment()`. 
   
   Rather than adding:
   ```
   interface IDatabase {
       String getSqlComment();
   }
   ```
   
   could have:
   ```
   interface IDatabase {
       Properties getClientInfoProperties();
   }
   ```
   
   then during the `connect()` routine of the main `Database.java` have something like:
   ```
   if databasePlugin.getClientInfo() != null {
       connection.setClientInfo(databasePlugin.getClientInfoProperties());
   }
   ```
   and let the JDBC driver implementation handle the specifics (in our case this would be Mysql + `CommentClientInfoProvider`).


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: issues-unsubscribe@hop.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

Re: [I] [Feature Request]: Database plugin query comment injection (hop)

["mattcasters (via GitHub)" <gi...@apache.org>]

mattcasters commented on issue #3261:
URL: https://github.com/apache/hop/issues/3261#issuecomment-1811185139

   Sounds like a cool idea.  Perhaps we could make this configurable in the GUI as well so that it works for all databases?  After all, the hard work is the injection of the comment before the SQL in `Database:openQuery`, not the interface change.
   We have a command to execute after connecting. We could have a section below called `Inject the following comment in database queries:`.
   Is this needed for insert/update/delete/lookup/... as well?


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: issues-unsubscribe@hop.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org