我想澄清如何使用 ODBC。我知道这个问题是，就像...... sooooo 1998，但是......我从来没有用C做过，所以......这里是：
首先我想我必须设置属性以启用池：
rc = SQLSetEnvAttr( NULL, // make process level cursor pooling
SQL_ATTR_CONNECTION_POOLING,
(SQLPOINTER)SQL_CP_ONE_PER_DRIVER,
SQL_IS_INTEGER);
然后分配HENV 并将其设置为 ODBC3.0：
rc = SQLAllocHandle(SQL_HANDLE_ENV, NULL, &henv1);
rc = SQLSetEnvAttr(henv1, SQL_ATTR_ODBC_VERSION, (void *)SQL_OV_ODBC3, 0);
问题
- 当我调用 SQLAllocHandle 来分配数据库连接 (HDBC) 时，我可以在单个进程内的多个并发线程中使用上面分配的 HENV 是否正确？
- 当我想使用池中的连接时，典型的顺序是否正确：
- SQLAllocHandle 获取连接句柄 (HDBC)
- SQLDriverConnect/[SQLConnect 连接到该句柄
- SQLExecute/SQLExecDirect +一系列SQLFetch，使用连接
- SQLDisconnect
- SQLFreeConnect
- 如果我保存分配的 HDBC 句柄并重新使用，是否会有显着的延迟优势它跨多个 SQLDriverConnect + SQLDisconnect 调用吗？换句话说，对于每次使用该连接，我都会跳过步骤 2.1 和 2.5。或者步骤 2.1 和 2.5 基本上只是 malloc/free 吗？ （在这种情况下，我认为我不在乎）。
在此特定场景中，C 应用程序可能是从此框中访问 SQL Server 的唯一应用程序。但它将在 IIS 环境中运行，这意味着它可能是多进程的，并且每个进程都是多线程的。
我将在 HTTP 请求的范围内获取并使用该连接，因此我希望它尽可能快速、高效且可扩展。
I want to 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
- 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)?
- When I want to use connection from the pool, is it correct that the typical sequence is:
- SQLAllocHandle to get connection handle (HDBC)
- SQLDriverConnect/[SQLConnect to connect on that handle
- SQLExecute/SQLExecDirect + a series of SQLFetch, to use the connection
- SQLDisconnect
- SQLFreeConnect
- 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.
发布评论
评论（1）
我对此进行了研究，然后为 Rust 编写了 odbc-api 绑定。事实证明，它（仍然）在这里有详细记录： https://learn.microsoft.com/en-us/sql/odbc/reference/develop-app/driver-manager-connection-pooling
用于激活和使用 ODBC 连接池的代码是正确的。现在回答你的问题：
是的，您可以在多个并发线程中使用该环境。不仅如此，这样做实际上是最佳实践，并且每个进程只有一个 ODBC 环境。
是的，所描述的顺序是合理的。当然，一切都取决于应用程序。
这取决于您的驱动程序和您对“重大”的定义。总的来说，任何 ODBC 调用都会产生一些开销，因为它是对动态库（驱动程序管理器）的函数调用。如果该特定函数调用是特定于驱动程序的（如 SqlAllocHandle），则它会被转发到驱动程序，该驱动程序也是动态加载的库。
然而，如果驱动程序有任何意识，它不会在网络上发送任何数据，所以通常您不会关心，它很可能归结为对 malloc/free 的调用有点昂贵。所以是的，取决于你对“重大”的定义。
I did research this then writing the
odbc-apibindings 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-poolingYour code for activating and using ODBC connection pooling is correct. Now to your questions:
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.
Yes, the described sequence is reasonable. Of course all depends on the application.
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'.