BROWSE_CONNECT_FOREIGN_SERVER

Returns the set of options relevant to a foreign server created from a given wrapper. You can pass in a partial set of options via the proposed_server_options cursor parameter, which must have two columns (OPTION_NAME and OPTION_VALUE, in that order). This allows for an incremental connection interaction, starting with specifying no options, then some, then more, stopping once user and wrapper are both satisfied.

The result set is not fully normalized, because some options support a list of choices (such as for a dropdown selection UI widget). optional_choice_ordinal -1 represents the “current” choice (either proposed by the user or chosen as default by the wrapper). Other choice ordinals starting from 0 represent possible choices (if known).

Syntax

select * from table(SYS_BOOT.MGMT.BROWSE_CONNECT_FOREIGN_SERVER(
FOREIGN_WRAPPER_NAME,
PROPOSED_SERVER_OPTIONS))
;

Returns:

Field Type
option_ordinal integer
option_description varchar(4096)
option_choice_ordinal int

Example

Given a foreign data wrapper created with the following code:

create foreign data wrapper hsqldb_wrapper
library '${FARRAGO_HOME}/plugin/FarragoMedJdbc.jar'
language java
options(
  browse_connect_description 'Hypersonic',
  driver_class 'org.hsqldb.jdbcDriver',
  url 'jdbc:hsqldb:path/to/data'
);

And using the following select statement

select option_name, option_choice_ordinal, option_choice_value from table
  (sys_boot.mgmt.browse_connect_foreign_server(
  'HSQLDB_WRAPPER',
  cursor(
    values ('URL', 'jdbc:hsqldb:testcases/hsqldb/scott'),
       ('EXTENDED_OPTIONS', 'TRUE'))))

order by option_ordinal, option_choice_ordinal;

Returns:

'OPTION_NAME','OPTION_CHOICE_ORDINAL','OPTION_CHOICE_VALUE'
'DRIVER_CLASS','-1','org.hsqldb.jdbcDriver'
'URL','-1','jdbc:hsqldb:testcases/hsqldb/scott'
'USER_NAME','-1',''
'PASSWORD','-1',''
'EXTENDED_OPTIONS','-1','TRUE'
'EXTENDED_OPTIONS','0','FALSE'
'EXTENDED_OPTIONS','1','TRUE'
'SCHEMA_NAME','-1',''
'user','-1',''
'password','-1',''
'get_column_name','-1','true'
'get_column_name','0','true'
'get_column_name','1','false'
'ifexists','-1',''
'ifexists','0','true'
'ifexists','1','false'
'default_schema','-1',''
'default_schema','0','true'
'default_schema','1','false'
'shutdown','-1',''
'shutdown','0','true'
'shutdown','1','false'