SQL Server: ODBC Connection pooling / C API

I want to开发者_Go百科 clarify how to do connection pooling with SQL Server from C, using ODBC. I know this question is, like.... sooooo 1998, but... I've never done it in C, so... here goes:

First I think I have to set the attribute to enable pooling:

rc = SQLSetEnvAttr( NULL,  // make process level cursor pooling
                    SQL_ATTR_CONNECTION_POOLING,
                    (SQLPOINTER)SQL_CP_ONE_PER_DRIVER,
                    SQL_IS_INTEGER);

Then allocate the HENV and set it for ODBC3.0:

rc = SQLAllocHandle(SQL_HANDLE_ENV, NULL, &henv1);
rc = SQLSetEnvAttr(henv1, SQL_ATTR_ODBC_VERSION, (void *)SQL_OV_ODBC3, 0);

---

Questions

1. Is it correct that I can use the HENV allocated above, in multiple concurrent threads within a single process, as I call SQLAllocHandle to allocate db connections (HDBC)?

2. When I want to use connection from the pool, is it correct that the typical sequence is:
   1. SQLAllocHandle to get connection handle (HDBC)
   2. SQLDriverConnect/[SQLConnect to connect on that handle
   3. SQLExecute/SQLExecDirect + a series of SQLFetch, to use the connection
   4. SQLDisconnect
   5. SQLFreeConnect

3. Is there a significant latency benefit if I save the allocated HDBC handle, and re-use it across multiple SQLDriverConnect + SQLDisconnect calls? In other words, I'd skip steps 2.1 and 2.5, for each use of the connection. Or are steps 2.1 and 2.5 basically just malloc/free? (in which case, I don't think I care).

---

In this particular scenario, the C app will likely be the only application accessing the SQL Server from this box. But it's going to run within a IIS environment, and that means it will be potentially multi-process and each process will be multi-threaded.

I'll be getting and using that connection within the scope of a HTTP Request, so I'll want it to be as fast, efficient, and scalable as possible.

---

I did research this then writing the odbc-api bindings for Rust. It turns out it is (still) well documented here: https://learn.microsoft.com/en-us/sql/odbc/reference/develop-app/driver-manager-connection-pooling

Your code for activating and using ODBC connection pooling is correct. Now to your questions:

1. Is it correct that I can use the HENV allocated above, in multiple concurrent threads within a single process, as I call SQLAllocHandle to allocate db connections (HDBC)?

Yes, you can use the environment in multiple concurrent threads. Not only that, it is actually best practice to do so, and have only one ODBC environment for each process.

2. When I want to use connection from the pool, is it correct that the typical sequence is: [...]

Yes, the described sequence is reasonable. Of course all depends on the application.

3. Is there a significant latency benefit if I save the allocated HDBC handle, and re-use it across multiple SQLDriverConnect + SQLDisconnect calls? In other words, I'd skip steps 2.1 and 2.5, for each use of the connection. Or are steps 2.1 and 2.5 basically just malloc/free? (in which case, I don't think I care)

This depends both on your driver and your definition of 'significant'. Overall any ODBC call incurs some overhead due to it being a function call into a dynamic library (the driver manager). If that specific function call is driver specific (like SqlAllocHandle), it is then forwarded to the driver which is also a dynamically loaded library.

Yet if the driver has any sense it wont send any data around the network, so usually you would not care and it most likely boils down to a somewhat expensive call to malloc/free. So yeah, depends on your definition of 'significant'.