The idea is to enhance the existing CREATE SERVER command to work better with CONNECT engine JDBC and other future extensions & improvements to CONNECT.
By having the URL as its own field you eliminate complicated escaping, and by putting all the CONNECT specific options in a single OTHER you have an easy to understand syntax.
Some notes on the above:
- For our needs, CREATE SERVER really needs to not require SUPER priv and be a DATABASE level priv so would need to be able to grant something like this:
- for our purposes, the drop server permission could automatically go with create server; we would mostly use CREATE OR REPLACE SERVER x .... and DROP SERVER x
- The URL string size limit needs to handle URLs with inline .pem SSL data, so 2k minimum but better if 6k or TEXT datatype to allow for other SSL files in future being passed in the URL itself
- in the above example I have "dbcp=1" as a synonym for wrapper=ApacheWrapper as I see this as the standard db connection pooling solution for connect jdbc - works great, no special driver needed.
Being able to issue a command like CREATE TABLE .... ENGINE=CONNECT SERVER=MyServerName means that we could change passwords, turn pooling on-off, edit the connection params, replace ssl files, etc without having to recreate each of the connect tables (customers have dozens).
This would be a REALLY great enhancement!