Re: A discussion about unixODBC programming model

On 20/11/14 02:40, xiaonan wrote:
> Hi Nick,
>
> 	Now our project uses unixODBC like this:
> 	
> 	(1) Use connection pool:
> 	/usr/local/etc/odbcinst.ini
> 	[ODBC]
> 	Trace=No
> 	Pooling=Yes
> 	
> 	(2) When program initiates, allocate a global environment handle:
> 	SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &g_env_handle));
> 	
> 	(3) Encapsulate a SQL operation function:
> 	sql_func()
> 	{
> 		/* Allocate connection handle */
> 		SQLAllocHandle(SQL_HANDLE_DBC, g_env_handle, &conn_handle);
> 		/* Connect database */
> 		SQLConnect(conn_handle, ...);
> 		
> 		/* Allocate statement handle */
> 		SQLAllocHandle(SQL_HANDLE_STMT, conn_handle, &stmt_handle);
> 		/* Execute statement */
> 		SQLExecDirect(stmt_handle, ...);
> 		/* Free statement handle */
> 		SQLFreeHandle(SQL_HANDLE_STMT, stmt_handle);
> 		
> 		/* Disconnect database */
> 		SQLDisconnect(conn_handle);
> 		/* Free connection handle */
> 		SQLFreeHandle(SQL_HANDLE_DBC, conn_handle);
> 	}
> 	
> 	So all the SQL related operations will be executed through this sql_func.
>
>
> 	This encapsulate is easy and clear, but now I consider whether this will lead low efficiency. Because every SQL operation will allocate connection handle, connect database (although the connection pool is used), allocate statement handle, free handles, etc.
> 	
> 	So I consider whether I can do like this:
> 	(1) This is the same as now: still use connection pool.
> 	
> 	(2) When program initiates, except the global environment handle, I still allocate a connection handle pool:
> 	create_connection_handle_pool()
> 	{
> 		for (...)
> 		{
> 			/* Allocate connection handle */
> 			SQLAllocHandle(SQL_HANDLE_DBC, g_env_handle, &conn_handle);
> 			/* Connect database */
> 			SQLConnect(conn_handle, ...);
> 		}
> 	}
> 	
> 	(3) So the SQL operation function can be:
> 	sql_func()
> 	{
> 		select a connection handle from connection handle pool;
> 		
> 		/* Allocate statement handle */
> 		SQLAllocHandle(SQL_HANDLE_STMT, conn_handle, &stmt_handle);
> 		/* Execute statement */
> 		SQLExecDirect(stmt_handle, ...);
> 		/* Free statement handle */
> 		SQLFreeHandle(SQL_HANDLE_STMT, stmt_handle);
> 		
> 	}
> 	
> 	This will not allocate connection handle and connect database every time. So I think this will improve efficiency.
> 	
> 	Nick, could you help to comment on this idea? Such as if it is worth to create a connection handle pool? Is there any risk of implementing this?
>
>          Thanks very much in advance!
> 	
> Best Regards
> Nan Xiao

Its how I would (and do) work. Only thing to be aware of is I have seen 
situations where if the connection sits idle for some time, and the 
connection to the database is via a firewall, then I have seen the 
firewall drop the connection. But on a LAN its all fine.

I would also check the SQLGetInfo( SQL_ACTIVE_STATEMENTS ) value for 
your database. You may only need one connection and then run multiple 
statements from that one connection.

-- 
Nick
> _______________________________________________
> unixODBC-dev mailing list
> [email protected]
> http://mailman.unixodbc.org/mailman/listinfo/unixodbc-dev

_______________________________________________
unixODBC-dev mailing list
[email protected]
http://mailman.unixodbc.org/mailman/listinfo/unixodbc-dev