SQLSetEnvAttr - Set environment attribute

SQLSetEnvAttr() sets an environment attribute for the current environment.

Syntax

An environment attribute cannot be set if a connection handle has been allocated. In order for the attribute to apply to the entire CLI environment, the environment attributes must be in place before this initial connection is made. An HY010 error code is returned otherwise.

SQLRETURN SQLSetEnvAttr  (SQLHENV      henv,
                          SQLINTEGER   Attribute,
                          SQLPOINTER   Value,
                          SQLINTEGER   StringLength);

Function arguments

Table 1. SQLSetEnvAttr arguments
Data type Argument Use Description
SQLHEN henv Input Environment handle.
SQLINTEGER Attribute Input Environment attribute to set. Refer to Table 2 for more information.
SQLPOINTER Value Input Appropriate value for Attribute.
SQLINTEGER StringLength Input Length of Value in bytes if the attribute value is a character string; if Attribute does not denote a string, then DB2® for i CLI ignores StringLength.

Usage

In environments where the current application may exist in the same job as other applications using CLI, connections attributes should be used instead of environment attributes. Otherwise, setting environment attributes may cause the other application to behave unexpectedly. Ideally, the only environment attributes that should be used are SQL_ATTR_ENVHNDL_COUNTER and SQL_ATTR_SERVER_MODE.

Start of change
Table 2. Environment attributes
Attribute Contents
SQL_ATTR_DATE_FMT A 32-bit integer value:
  • SQL_FMT_ISO – The International Organization for Standardization (ISO) date format yyyy-mm-dd is used. This is the default.
  • SQL_FMT_USA – The United States date format mm/dd/yyyy is used.
  • SQL_FMT_EUR – The European date format dd.mm.yyyy is used.
  • SQL_FMT_JIS – The Japanese Industrial Standard date format yyyy-mm-dd is used.
  • SQL_FMT_MDY – The date format mm/dd/yy is used.
  • SQL_FMT_DMY – The date format dd/mm/yy is used.
  • SQL_FMT_YMD – The date format yy/mm/dd is used.
  • SQL_FMT_JUL – The Julian date format yy/ddd is used.
  • SQL_FMT_JOB – The job default is used.
SQL_ATTR_DATE_SEP A 32-bit integer value:
  • SQL_SEP_SLASH – A slash ( / ) is used as the date separator. This is the default.
  • SQL_SEP_DASH – A dash ( - ) is used as the date separator.
  • SQL_SEP_PERIOD – A period ( . ) is used as the date separator.
  • SQL_SEP_COMMA – A comma ( , ) is used as the date separator.
  • SQL_SEP_BLANK – A blank is used as the date separator.
  • SQL_SEP_JOB – The job default is used.

Separators only apply to the following SQL_ATTR_DATE_FMT attribute types:

  • SQL_FMT_MDY
  • SQL_FMT_DMY
  • SQL_FMT_YMD
  • SQL_FMT_JUL
SQL_ATTR_DECIMAL_SEP A 32-bit integer value:
  • SQL_SEP_PERIOD – A period ( . ) is used as the decimal separator. This is the default.
  • SQL_SEP_COMMA – A comma ( , ) is used as the decimal separator.
  • SQL_SEP_JOB – The job default is used.
SQL_ATTR_DEFAULT_LIB A character value that indicates the default library that is used for resolving unqualified file references.
SQL_ATTR_ENVHNDL_COUNTER A 32-bit integer value:
  • SQL_FALSE – DB2 for i CLI does not count the number of times the environment handle is allocated. Therefore, the first call to free the environment handle and all associated resources.
  • SQL_TRUE – DB2 for i CLI keeps a counter of the number of times the environment handle is allocated. Each time the environment handle is freed, the counter is decremented. Only when the counter reaches zero does the DB2 for i CLI actually free the handle and all associated resources. This allows nested calls to programs using the CLI that allocate and free the CLI environment handle.
SQL_ATTR_ESCAPE_CHAR A character value that indicates the escape character to be used when specifying a search pattern in either SQLColumns( ) or SQLTables( ).

SQL_ATTR_ESCAPE_CHAR is only honored if the connection attribute SQL_ATTR_OLD_MTADTA_BEHAVIOR is set to SQL_TRUE.
SQL_ATTR_FOR_FETCH_ONLY A 32-bit integer value:
  • SQL_TRUE – Cursors are read-only and cannot be used for positioned update or delete operations. This is the default.
  • SQL_FALSE – Cursors can be used for positioned updates or delete operations.

The attribute SQL_ATTR_FOR_FETCH_ONLY can also be set for individual statements using SQLSetStmtAttr().
SQL_ATTR_INCLUDE_NULL_IN_LEN A 32-bit integer value:
  • SQL_TRUE – If a null terminator exists, it will be included in the length value that is returned for output character information. To include the null terminator in the actual output string, the environment attribute SQL_ATTR_OUTPUT_NTS must be set to SQL_TRUE. This is the default.
  • SQL_FALSE – The null terminator, even if it exists, will not be included in the length value that is returned for output character information.
SQL_ATTR_JOB_SORT_SEQUENCE A 32-bit integer value:
  • SQL_TRUE – DB2 for i CLI uses the sort sequence that has been set for the job.
  • SQL_FALSE – DB2 for i CLI uses the default sort sequence, which is *HEX.
SQL_ATTR_NON_HEXCCSID A 32-bit integer value:
  • SQL_TRUE – DB2 for i CLI set the job CCSID to the job default CCSID if the job CCSID is set to 65535.
  • SQL_FALSE – DB2 for i CLI does not change the job CCSID. This is the default.
SQL_ATTR_OUTPUT_NTS A 32-bit integer value:
  • SQL_TRUE – DB2 for i CLI uses null termination to indicate the length of output character strings. This is the default.
  • SQL_FALSE – DB2 for i CLI does not use null termination.

The CLI functions affected by this attribute are all functions called for the environment (and for any connections allocated under the environment) that have character string parameters.
SQL_ATTR_REQUIRE_PROFILE A 32-bit integer value:
  • SQL_TRUE – If in server mode, then a profile and password are required when running SQLConnect() and SQLDriverConnect() functions.
  • SQL_FALSE – If profile is omitted on the SQLConnect() or SQLDriverConnect() function, then connection is made using current user profile. This is the default.
SQL_ATTR_SERVER_MODE A 32-bit integer value:
  • SQL_FALSE – DB2 for i CLI processes the SQL statements of all connections within the same job. All changes compose a single transaction. This is the default mode of processing.
  • SQL_TRUE – DB2 for i CLI processes the SQL statements of each connection in a separate job. This allows multiple connections to the same data source, possibly with different user IDs for each connection. It also separates the changes made under each connection handle into its own transaction. This allows each connection handle to be committed or rolled back, without impacting pending changes made under other connection handles. See Running DB2 for i CLI in server mode for more information.
SQL_ATTR_SYS_NAMING A 32-bit integer value:
  • SQL_TRUE – DB2 for i CLI uses the IBM® i system naming mode. Files are qualified using the slash (/) delimiter. Unqualified files are resolved using the library list for the job.
  • SQL_FALSE – DB2 for i CLI uses the default naming mode, which is SQL naming. Files are qualified using the period (.) delimiter. Unqualified files are resolved using either the default library or the current user ID.
SQL_ATTR_TIME_FMT A 32-bit integer value:
  • SQL_FMT_ISO – The International Organization for Standardization (ISO) time format hh.mm.ss is used. This is the default.
  • SQL_FMT_USA – The United States time format hh:mmxx is used, where xx is a.m. or p.m.
  • SQL_FMT_EUR – The European time format hh.mm.ss is used.
  • SQL_FMT_JIS – The Japanese Industrial Standard time format hh:mm:ss is used.
  • SQL_FMT_HMS – The hh:mm:ss format is used.
SQL_ATTR_TIME_SEP A 32-bit integer value:
  • SQL_SEP_COLON – A colon ( : ) is used as the time separator. This is the default.
  • SQL_SEP_PERIOD – A period ( . ) is used as the time separator.
  • SQL_SEP_COMMA – A comma ( , ) is used as the time separator.
  • SQL_SEP_BLANK – A blank is used as the time separator.
  • SQL_SEP_JOB – The job default is used.
SQL_ATTR_TRUNCATION_RTNC A 32-bit integer value:
  • SQL_TRUE – CLI returns SQL_SUCCESS_WITH_INFO in the SQLFetch(),SQLExtendedFetch(), and SQLFetchScroll() return codes if truncation occurs.
  • SQL_FALSE – CLI does not return SQL_SUCCESS_WITH_INFO in the SQLFetch(), SQLExtendedFetch() , and SQLFetchScroll() return codes if truncation occurs. This is the default.
SQL_ATTR_UTF8 A 32-bit integer value:
  • SQL_FALSE – Character data is treated as being in the default job coded character set identifier (CCSID). This is the default.
  • SQL_TRUE – Character data is treated as being in the UTF–8 CCSID (1208).
End of change

Return codes

  • SQL_SUCCESS
  • SQL_SUCCESS_WITH_INFO
  • SQL_ERROR
  • SQL_INVALID_HANDLE

Diagnostics

Table 3. SQLSetEnvAttr SQLSTATEs
SQLSTATE Description Explanation
HY009 Parameter value that is not valid The specified Attribute is not supported by DB2 for i CLI.

Given specified Attributevalue, the value specified for the argument Value is not supported.

The argument pValue is a null pointer.
HY010 Function sequence error Connection handles are already allocated.
Parent topic: DB2 for i CLI functions