Purpose
Changes the existing session connection to a new connection.
Invocation
Executable.
Embedded SQL only.
Syntax
SET CONNECTION { connection_name | :connection_name_variable }
- 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.
ANSI Compliance
SET CONNECTION is ANSI/ISO SQL:2011-compliant.
Authorization
None.
Rules
- 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:
This RDTIN field … | Must … |
---|---|
RdtCType | be set to 150. |
RdtVersn | be set to 10. |
RdtExt | be set to ‘Y’ to indicate the existence of an extension area. |
RdtXTotL | include the size of the RDTXCONM extension area. |
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; }