Should I use a seperate ODBC environment for multiple connections?

Question

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?

score 1 · Answer 1 · answered Oct 20 '15 at 07:56

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).

Should I use a seperate ODBC environment for multiple connections?

1 Answers1