SET CONNECTION | Teradata Vantage - SET CONNECTION - Advanced SQL Engine - Teradata Database

Changes the existing session connection to a new connection.

ANSI Compliance

SET CONNECTION is ANSI/ISO SQL:2011-compliant.

Required Privileges

None.

Invocation

Executable.

Embedded SQL only.

Syntax

SET CONNECTION { connection_name | :connection_name_variable }

Syntax Elements

connection_name

Name of the connection variable to which the current connection is being changed.

:connection_name_variable

The host variable that contains the connection name.

The preceding colon character is mandatory.

Usage Notes

• SET CONNECTION is not valid in the following cases:
  • in single session mode because the current session does not have a connection name. A runtime error occurs and the current connection remains in effect.
  • within cursor requests specified by the DECLARE CURSOR statement.
  • within dynamic requests specified by the PREPARE or EXECUTE IMMEDIATE statement.

  If the attempted SET CONNECTION fails, then there is no current session unless the current connection does not have a connection_name.

  If the current connection is disconnected, then a SET CONNECTION statement must be executed to make a background connection the current one.

  Each connection name must be unique (up to 30 bytes) and is case-sensitive.

  :namevar must be a fixed or varying length character variable no longer than 30 bytes.

• SET CONNECTION and Multisession Programming

  SET CONNECTION is designed to be used with multisession programming, permitting applications to switch connections among multiple concurrent sessions.

Examples 1 - 3

The following RDTIN fields are important for these examples:

Additionally, the RdtX007 (RDTXCONM) structure must be included as one of the extension areas because it communicates the connection name.

Example: Establishing a Session Connection

The following example establishes a session connection using an explicitly specified connection name:

EXEC SQL SET CONNECTION SESSION1;

Lines Generated by C Preprocessor2 for Example 1

{
static struct {
       SQLInt32 RdtCType;
       SQLInt16 RdtVersn;
       SQLInt16 RdtDec;
       char     RdtUserid[8];
       SQLInt32 RdtEntty;
       char     *RdtCA;
       char     *RdtDAIn;
       char     *RdtDAOut;
       char     *RdtSql;
       char     *RdtRtCon;
       SQLInt32 RdtAux1;
       SQLInt32 RdtAux2;
       char     RdtLCS;
       char     RdtComit;
       char     RdtRelse;
       char     RdtExt;
       char     RdtSepBT;
       char     RdtUCStm;
       char     RdtCmpat;
       char     RdtComp;
       SQLInt16 RdtXTotL;
       char     RdtXFill[2];
       struct {
              SQLInt16 RdtXLen;
              SQLInt16 RdtXType;
              SQLInt32 RdtXCode;
              } RdtX005;
       struct {
              SQLInt16 RdtXLen;
              SQLInt16 RdtXType;
              SQLInt16 RdtXConL;
              char     RdtXConT[30];
              } RdtX007;
       } RDTIN013 =
       {150,10,0,{' '},0,0,0,0,0,0,0,0,'N','B','N','Y','N','N',' ','C',
       48,{' '},{8,5,255},{36,7,8,'S','E','S','S','I','O','N','1'}};
RDTIN013.RdtCA = (char *)(&sqlca);
RDTIN013.RdtRtCon = SQL_RDTRTCON;
TDARDI(&RDTIN013);
SQL_RDTRTCON = RDTIN013.RdtRtCon;
  }

Example 2

The following example establishes a session connection using a VARCHAR connection name passed to SET CONNECTION by means of a host variable named connamev:

EXEC SQL SET CONNECTION :CONNAMEV;

Lines Generated by C Preprocessor2 for Example 2

{
static struct {
       SQLInt32 RdtCType;
       SQLInt16 RdtVersn;
       SQLInt16 RdtDec;
       char     RdtUserid[8];
       SQLInt32 RdtEntty;
       char     *RdtCA;
       char     *RdtDAIn;
       char     *RdtDAOut;
       char     *RdtSql;
       char     *RdtRtCon;
       SQLInt32 RdtAux1;
       SQLInt32 RdtAux2;
       char     RdtLCS;
       char     RdtComit;
       char     RdtRelse;
       char     RdtExt;
       char     RdtSepBT;
       char     RdtUCStm;
       char     RdtCmpat;
       char     RdtComp;
       SQLInt16 RdtXTotL;
       char     RdtXFill[2];
       struct {
              SQLInt16 RdtXLen;
              SQLInt16 RdtXType;
              SQLInt32 RdtXCode;
              } RdtX005;
       struct {
              SQLInt16 RdtXLen;
              SQLInt16 RdtXType;
              SQLInt16 RdtXConL;
              char     RdtXConT[30];
              } RdtX007;
       } RDTIN014 =
       {150,10,0,{' '},0,0,0,0,0,0,0,0,'N','B','N','Y','N','N',' ','C',
       48,{' '},{8,5,255},{36,7,}};
RDTIN014.RdtX007.RdtXConL = CONNAMEV.len;
memcpy(RDTIN014.RdtX007.RdtXConT,CONNAMEV.arr,CONNAMEV.len);
RDTIN014.RdtCA = (char *)(&sqlca);
RDTIN014.RdtRtCon = SQL_RDTRTCON;
TDARDI(&RDTIN014);
SQL_RDTRTCON = RDTIN014.RdtRtCon;
  }

Example 3

The following example establishes a session connection using a CHAR connection name passed to SET CONNECTION by means of a host variable named connamef:

EXEC SQL SET CONNECTION :CONNAMEF;

Lines Generated by C Preprocessor2 for Example 3

{
static struct {
       SQLInt32 RdtCType;
       SQLInt16 RdtVersn;
       SQLInt16 RdtDec;
       char     RdtUserid[8];
       SQLInt32 RdtEntty;
       char     *RdtCA;
       char     *RdtDAIn;
       char     *RdtDAOut;
       char     *RdtSql;
       char     *RdtRtCon;
       SQLInt32 RdtAux1;
       SQLInt32 RdtAux2;
       char     RdtLCS;
       char     RdtComit;
       char     RdtRelse;
       char     RdtExt;
       char     RdtSepBT;
       char     RdtUCStm;
       char     RdtCmpat;
       char     RdtComp;
       SQLInt16 RdtXTotL;
       char     RdtXFill[2];
       struct {
              SQLInt16 RdtXLen;
              SQLInt16 RdtXType;
              SQLInt32 RdtXCode;
              } RdtX005;
       struct {
              SQLInt16 RdtXLen;
              SQLInt16 RdtXType;
              SQLInt16 RdtXConL;
              char     RdtXConT[30];
              } RdtX007;
       } RDTIN015 =
       {150,10,0,{' '},0,0,0,0,0,0,0,0,'N','B','N','Y','N','N',' ','C',
       48,{' '},{8,5,255},{36,7,}};
RDTIN015.RdtX007.RdtXConL = strlen(CONNAMEF);
memcpy(RDTIN015.RdtX007.RdtXConT,CONNAMEF,strlen(CONNAMEF));
RDTIN015.RdtCA = (char *)(&sqlca);
RDTIN015.RdtRtCon = SQL_RDTRTCON;
TDARDI(&RDTIN015);
SQL_RDTRTCON = RDTIN015.RdtRtCon;
  }