ODBC Wrappers¶

ODBC wrappers allow querying ODBC data sources.

Note

Creating ODBC data sources and wrappers in Virtual DataPort is deprecated. See more about this in the page ODBC Sources of the Administration Guide.

The statement CREATE WRAPPER ODBC follows the same structure as that defined for a JDBC wrapper. To create a wrapper of this type it is necessary to specify the ODBC data source, the table or SQL statement used to obtain the data from the source, and the output schema provided by the wrapper. For more information, see section JDBC Wrappers.

Syntax of the CREATE WRAPPER ODBC statement¶

CREATE [ OR REPLACE ] WRAPPER ODBC <name:identifier>
    [ FOLDER = <literal> ]
    DATASOURCENAME = <name:identifier>
    {
        [   SCHEMANAME = <name:literal> ] RELATIONNAME = <name:literal>
          | SQLSENTENCE = <literal>
    }
    [ OUTPUTSCHEMA ( <field> [, <field> ]* ) ]
    [ ALIASES ( <alias> [, <alias> ]* ) ]
    [ SOURCECONFIGURATION ( [ <source configuration property>
                            [, <source configuration property> ]* ] ) ]

<field> ::=
      <name:identifier> [ = <mapping:literal> ] : <type:literal>
         [ ( { OBL | OPT } ) ]
         [ ( <field property> = <literal>
             [, <field property> = <literal> ]* ) ]
         [ <inline constraints> ]*
    | <name:identifier> [ = <mapping:literal> ] :
         ARRAY OF ( <register field> )
         [ ( <field property> = <literal>
             [, <field property> = <literal> ]* ) ]
         [ <inline constraints> ]*
    | <name:register field>

<field property> ::=
      DEFAULTVALUE
    | SOURCETYPENAME
    | SOURCETYPEID
    | SOURCETYPESIZE
    | SOURCETYPEDECIMALS
    | SOURCETYPERADIX

<register field> ::=
    <name:identifier> [ = <mapping:literal> ] :
       REGISTER OF ( <field> [, <field> ]* )
         [ ( <field property> = <literal>
             [, <field property> = <literal> ]* ) ]
         [ <inline constraints> ]*

<inline constraint> ::=
      [ NOT ] NULL
    | [ NOT ] UPDATEABLE
    | { SORTABLE [ ASC | DESC ] | NOT SORTABLE }
    | ESCAPE
    | EXTERN
    | IS_AUTOINCREMENT

<alias> ::=
  <literal> = <literal>

<source configuration property> ::=
    ALLOWDELETE = { true | false | DEFAULT }
  | ALLOWINSERT = { true | false | DEFAULT }
  | ALLOWUPDATE = { true | false | DEFAULT }
  | DELEGATESQLSELECTION = { true | false | DEFAULT }
  | DATAINORDERFIELDSLIST = { ( <name:identifier> { ASC | DESC }
                          [, <name:identifier> { ASC | DESC } ]* ) | DEFAULT }
  | SUPPORTSDISTRIBUTEDTRANSACTIONS = { true | false | DEFAULT }

<register field> ::=
    <name:identifier> [ = <mapping:literal> ] :
       REGISTER OF ( <field> [, <field> ]* )
         [ ( <field property> = <literal>
             [, <field property> = <literal> ]* ) ]
         [ <inline constraints> ]*

<inline constraint> ::=
    [ NOT ] NULL
  | [ NOT ] UPDATEABLE
  | { SORTABLE [ ASC | DESC ] | NOT SORTABLE }
  | ESCAPE
  | EXTERN
  | IS_AUTOINCREMENT

<alias> ::=
  <literal> = <literal>

<source configuration property> ::=
    ALLOWDELETE = { true | false | DEFAULT }
  | ALLOWINSERT = { true | false | DEFAULT }
  | ALLOWUPDATE = { true | false | DEFAULT }
  | DELEGATESQLSELECTION = { true | false | DEFAULT }
  | DATAINORDERFIELDSLIST = { ( <name:identifier> { ASC | DESC }
                          [, <name:identifier> { ASC | DESC } ]* ) | DEFAULT }
  | SUPPORTSDISTRIBUTEDTRANSACTIONS = { true | false | DEFAULT }

The wrapper creation statement accepts the OR REPLACE modifier. Where specified, if there is already a wrapper with the same name, its definition is replaced by the new one.

You can specify certain wrapper properties in the SOURCECONFIGURATION clause that determine the operations that can be made on the wrapper. In the figure above you can see what properties can be indicated and the section Wrapper Configuration Properties explains their meaning.

Syntax of the ALTER WRAPPER ODBC statement¶

ALTER WRAPPER ODBC <name:identifier>
    [ DATASOURCENAME = <name:identifier> ]
    [
          [ SCHEMANAME = <name:literal> ] RELATIONNAME = <name:literal>
        | SQLSENTENCE = <literal>
    ]
    [ OUTPUTSCHEMA ( <field> [, <field>]* ) ]
    [ ALIASES ( <alias> [, <alias>]* ) ]
    [ SOURCECONFIGURATION ( [ <source configuration property>
    [, <source configuration property> ]* ]) ]

<field> ::= (see Syntax of the CREATE WRAPPER ODBC statement)

<alias> ::= (see Syntax of the CREATE WRAPPER ODBC statement)

<source configuration property> ::= (see Syntax of the CREATE WRAPPER ODBC statement)

USER MANUALS

ODBC Wrappers¶