SQLSetEnvAttr - Set environment attribute

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

SQLHENV henv Input Environment handle.
SQLINTEGER Attribute Input Environment attribute to set. Refer to Table 2 for more information.
SQLPOINTER pValue 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^® UDB CLI ignores StringLength.

Usage

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. This is not valid if the environment is using system naming mode.
SQL_ATTR_ENVHNDL_COUNTER A 32-bit integer value:

SQL_FALSE – DB2 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 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 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_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 UDB CLI uses the sort sequence that has been set for the job.

SQL_FALSE – DB2 UDB CLI uses the default sort sequence, which is *HEX.

SQL_ATTR_OUTPUT_NTS A 32-bit integer value:

SQL_TRUE – DB2 UDB CLI uses null termination to indicate the length of output character strings. This is the default.

SQL_FALSE – DB2 UDB 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 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 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 UDB CLI in server mode for more information.

SQL_ATTR_SYS_NAMING A 32-bit integer value:

SQL_TRUE – DB2 UDB CLI uses the i5/OS^® system naming mode. Files are qualified using the slash (/) delimiter. Unqualified files are resolved using the library list for the job.

SQL_FALSE – DB2 UDB 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() and SQLFetchScroll() return codes if truncation occurs.

SQL_FALSE – CLI does not return SQL_SUCCESS_WITH_INFO in the SQLFetch() 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).

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 UDB 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 UDB CLI functions

Data type	Argument	Use	Description
SQLHENV	henv	Input	Environment handle.
SQLINTEGER	Attribute	Input	Environment attribute to set. Refer to Table 2 for more information.
SQLPOINTER	pValue	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^® UDB CLI ignores StringLength.

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. This is not valid if the environment is using system naming mode.
SQL_ATTR_ENVHNDL_COUNTER	A 32-bit integer value: SQL_FALSE – DB2 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 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 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_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 UDB CLI uses the sort sequence that has been set for the job. SQL_FALSE – DB2 UDB CLI uses the default sort sequence, which is *HEX.
SQL_ATTR_OUTPUT_NTS	A 32-bit integer value: SQL_TRUE – DB2 UDB CLI uses null termination to indicate the length of output character strings. This is the default. SQL_FALSE – DB2 UDB 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 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 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 UDB CLI in server mode for more information.
SQL_ATTR_SYS_NAMING	A 32-bit integer value: SQL_TRUE – DB2 UDB CLI uses the i5/OS^® system naming mode. Files are qualified using the slash (/) delimiter. Unqualified files are resolved using the library list for the job. SQL_FALSE – DB2 UDB 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() and SQLFetchScroll() return codes if truncation occurs. SQL_FALSE – CLI does not return SQL_SUCCESS_WITH_INFO in the SQLFetch() 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).

SQLSTATE	Description	Explanation
HY009	Parameter value that is not valid	The specified Attribute is not supported by DB2 UDB 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.