Reputation: 148
I need to open connection to system DB and then internally open connection for tenant DB. Currently i have implemented having different environment handle for each connection.
Connecting to system DB,
retCode = SQLAllocConnect(sqlEnvHandle , &sqlConnectionHandle);
retCode=SQLDriverConnect (sqlConnectionHandle,
NULL,
(SQLCHAR*)ConnString,
SQL_NTS,
retConnString,
BUFFER_SIZE,
NULL,
SQL_DRIVER_NOPROMPT);
Connecting to tenant DB where 'i' iterated to number of tenant DB and opens connection for each tenant DB,
retCode = SQLAllocConnect(TenantDBConnectionObj[i].sqlTenantEnvHandle, &TenantDBConnectionObj[i].sqlTenantConnectionHandle);
retCode=SQLDriverConnect(TenantDBConnectionObj[i].sqlTenantConnectionHandle,
NULL,
ConnString,
SQL_NTS,
retConnString,
BUFFER_SIZE,
NULL,
SQL_DRIVER_NOPROMPT);
Is it effective programming to open all these connection with just one environment handle sqlEnvHandle
?
What about connection pooling ? Can we implement in such scenario?
Upvotes: 0
Views: 307
Reputation: 2833
Normally people use just one environment and create many connection off that. I am not aware of any advantages of using multiple environments (unless you NEED different environment settings for different connections - e.g. you want different connection pooling settings to be in effect, set with the SQL_ATTR_CONNECTION_POOLING attribute at the environment level).
In addition, some ODBC drivers do not support multiple environments (see https://www-01.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/com.ibm.db2z11.doc.odbc/src/tpc/db2z_fnallochandle.dita for a statement to that effect for the DB2 ODBC 3.0 driver).
Upvotes: 1