在使用unixodbc站点的UNIX-ODBC库时,我遇到了SQLDriverConnect api的问题.
如果我尝试连续两次连接到我的数据库,首先使用不正确的DSN数据(数据源名称数据,通常放在/etc/odbc.ini中)&第二次使用正确的数据,第二次连接尝试也失败了.失败的原因似乎是SQLDriverConnect似乎在第一次运行时使用了不正确的数据.
在网上搜索任何提及缓存数据的内容之后,似乎没有其他人遇到过这个特定问题(或者我的搜索不充分).
我的用例是我提供了一个GUI,用户可以手动填写包含所有参数的表单,然后单击“测试连接”按钮.这将把详细信息写入(或覆盖)到/etc/odbc.ini文件中,然后尝试使用unixodbc apis连接到数据库.如果测试成功,则从SQLDriverConnect返回的连接字符串将填充到GUI上.如果失败,GUI将显示失败并允许用户编辑表单上的数据,然后再次单击“测试连接”按钮.
问题：如果用户输入了一些不正确的数据(例如端口号),则测试失败,用户会纠正数据.当用户现在尝试测试连接时,它应该通过,因为所有数据都是正确的,并且还正确地填充到odbc.ini文件中.疯狂地,它没有进行第二次重新测试.但是,有时第三次或第四次重新测试可以正确连接.
请注意,重新连接理想情况下应该在程序的单次运行中,因为重新运行程序似乎不会出现问题.这对我来说很重要,测试将从服务器端触发,并且不会有重新启动的奢侈.
系统细节
以下是用于以后开发和执行样本的系统的详细信息：
Developement Machine
CentOS release 6.1 (Final)
2.6.32-131.0.15.el6.i686 {32bit machine}
Deployment Machine (CentOS)
Linux release 6.6 (Final)
2.6.32-573.el6.x86_64 {64bit machine}
unixODBC 2.2.14
/usr/lib/psqlodbcw.so: ELF 32-bit LSB shared object, Intel 80386, version 1 (SYSV), dynamically linked, stripped
.
.
示例代码
使用libodbc构建代码,如下所示：
g++ -g -o code code.cpp -lodbc
请注意,您可能必须确保包含的文件和库已就位.
#include "../boost_1_52_0/boost/property_tree/ptree.hpp"
#include "../boost_1_52_0/boost/property_tree/ini_parser.hpp"
#include <iostream>
#include <string>
#include <vector>
#include <utility>
#include <unistd.h>
#include "../unixODBC-2.3.4/include/sql.h"
#include "../unixODBC-2.3.4/include/sqlext.h"
#include "../unixODBC-2.3.4/include/odbcinst.h"
using boost::property_tree::ptree;
using namespace std;
void PopulateINI(const string& iniName, vector<pair<string, string> >& data);
bool TestConnection(const string& connStringIn, string& connStringOut);
static void extract_error(char *fn, SQLHANDLE handle, SQLSMALLINT type);
void PrintIniFile(const string& iniName, const string& sDSN);
int main(int argc, char* argv[])
{
if (argc != 2)
{
cout << "Enter the choice of\n\t1 : Full run:- \n\t\t\tpopulate incorrect data\n\t\t\tattempt to connect\n\t\t\tpopulate CORRECT data\n\t\t\twait 15 secs\n\t\t\tattempt to connect\n\t2 : attempt to connect with existing ini data" << endl;
return 0;
}
int iCh = atoi(argv[1]);
if(iCh != 1 && iCh != 2)
{
cout << "Invalid choice !!\nAcceptable values are - '1' OR '2' only" << endl;
return 0;
}
string sDSN = "PostgresTest01";
string sConnStrIn, sConnStrOut;
sConnStrIn.append("DSN=").append(sDSN.c_str()).append(1, ';');
string iniName = "/etc/odbc.ini";
if (iCh == 1)
{
//Incorrect DSN data
vector<pair<string, string> > vData;
vData.push_back(make_pair(sDSN + ".Description", "Description"));
vData.push_back(make_pair(sDSN + ".Driver", "PostgreSQL"));
vData.push_back(make_pair(sDSN + ".Database", "dvdrental"));
vData.push_back(make_pair(sDSN + ".Servername", "192.168.45.217"));
vData.push_back(make_pair(sDSN + ".Port", "1234")); //INCORRECT PORT NUMBER; '1234' instead of '5432'
vData.push_back(make_pair(sDSN + ".UserName", "postgres"));
vData.push_back(make_pair(sDSN + ".Password", "postgres"));
vData.push_back(make_pair(sDSN + ".Trace", "Off"));
vData.push_back(make_pair(sDSN + ".TraceFile", "stderr"));
vData.push_back(make_pair(sDSN + ".Protocol", "7.0"));
vData.push_back(make_pair(sDSN + ".ReadOnly", "No"));
vData.push_back(make_pair(sDSN + ".RowVersioning", "No"));
vData.push_back(make_pair(sDSN + ".ShowSystemTables", "No"));
vData.push_back(make_pair(sDSN + ".ShowOidColumn", "No"));
vData.push_back(make_pair(sDSN + ".FakeOidIndex", "No"));
vData.push_back(make_pair(sDSN + ".ConnSettings", ""));
//Populate ini with Incorrect data
PopulateINI(iniName, vData);
sleep(5); //Just so I can see the ini file changing
//First run - Call SQLDriverConnect
PrintIniFile(iniName, sDSN);
sConnStrOut.clear();
if(TestConnection(sConnStrIn, sConnStrOut))
{
cout << "Test connection succeeded.\nConnection String is [" << sConnStrOut << "]" << endl;
}
else
{
cout << "Test connection failed for sConnStrIn[" << sConnStrIn << "]" << endl;
}
cout << "\n\n====================================================================" << endl;
cout << "Updating ini file with correct data..." << endl;
vData[4].second = "5432"; //CORRECT PORT NUMBER
PopulateINI(iniName, vData); //WRITE TO INI FILE
cout << "\n\nWaiting for 15 secs" << endl;
sleep(15); //15, so that I could manually change the odbc.ini, as I had some suspicions about ptree, read_ini() & write_ini()
cout << "\n\n====================================================================" << endl;
}
//Second run - Call SQLDriverConnect
PrintIniFile(iniName, sDSN);
sConnStrOut.clear();
if(TestConnection(sConnStrIn, sConnStrOut))
{
cout << "Test connection succeeded.\nConnection String is [" << sConnStrOut << "]" << endl;;
}
else
{
cout << "Test connection failed for sConnStrIn[" << sConnStrIn << "]" << endl;
}
return 0;
}
void PrintVector(const string& label, vector<pair<string, string> >& data)
{
cout << "\n\n " << label << "\n" << endl;
for(vector<pair<string, string> >::iterator it = data.begin(); it != data.end(); ++it)
{
cout << "\t\t" << it->first << " : " << it->second << endl;
}
cout << "\n===================================================" << endl;
}
void PopulateINI(const string& iniName, vector<pair<string, string> >& data)
{
ptree pt;
read_ini(iniName.c_str(), pt);
for(vector<pair<string, string> >::iterator it = data.begin(); it != data.end(); ++it)
{
pt.put(it->first.c_str(), it->second.c_str());
}
write_ini(iniName.c_str(), pt);
}
bool TestConnection(const string& connStringIn, string& connStringOut)
{
bool fRC = false;
SQLRETURN retcode;
SQLHENV env=NULL;
SQLHDBC dbc=NULL;
SQLSMALLINT siOutConnStrLen;
connStringOut.resize(2048);
SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void *) SQL_OV_ODBC3, 0);
SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);
retcode = SQLDriverConnect(dbc, NULL, (SQLCHAR*)connStringIn.c_str(), SQL_NTS, (SQLCHAR*)&connStringOut.at(0), 2048, &siOutConnStrLen, SQL_DRIVER_NOPROMPT);
if(SQL_SUCCEEDED(retcode))
{
connStringOut.resize(siOutConnStrLen);
fRC = true;
if(retcode == SQL_SUCCESS_WITH_INFO)
{
cout << "Driver reported the following diagnostics:" << endl;
extract_error("SQLDriverConnect", dbc, SQL_HANDLE_DBC);
}
SQLDisconnect(dbc);
}
else
{
cout << "Failed to connect:" << endl;
extract_error("SQLDriverConnect", dbc, SQL_HANDLE_DBC);
}
SQLFreeHandle(SQL_HANDLE_DBC, dbc);
SQLFreeHandle(SQL_HANDLE_ENV, env);
return fRC;
}
void extract_error(char *fn, SQLHANDLE handle, SQLSMALLINT type)
{
SQLINTEGER i = 0;
SQLINTEGER native;
SQLCHAR state[ 7 ];
SQLCHAR text[256];
SQLSMALLINT len;
SQLRETURN ret;
fprintf(stderr, "\nThe driver reported the following diagnostics whilst running %s\n\n", fn);
do
{
ret = SQLGetDiagRec(type, handle, ++i, state, &native, text, sizeof(text), &len );
if (SQL_SUCCEEDED(ret))
printf("%s:%ld:%ld:%s\n", state, i, native, text);
}
while( ret == SQL_SUCCESS );
}
void PrintIniFile(const string& iniName, const string& sDSN)
{
ptree pt;
read_ini(iniName.c_str(), pt);
cout << "\n\n[" << sDSN << "]" << endl;
cout << "Description : " << pt.get<string>((sDSN + "." + "Description").c_str()) <<endl;
cout << "Driver : " << pt.get<string>((sDSN + "." + "Driver").c_str()) <<endl;
cout << "Database : " << pt.get<string>((sDSN + "." + "Database").c_str()) <<endl;
cout << "Servername : " << pt.get<string>((sDSN + "." + "Servername").c_str()) <<endl;
cout << "Port : " << pt.get<string>((sDSN + "." + "Port").c_str()) <<endl;
cout << "UserName : " << pt.get<string>((sDSN + "." + "UserName").c_str()) <<endl;
cout << "Password : " << pt.get<string>((sDSN + "." + "Password").c_str()) <<endl;
cout << "Trace : " << pt.get<string>((sDSN + "." + "Trace").c_str()) <<endl;
cout << "TraceFile : " << pt.get<string>((sDSN + "." + "TraceFile").c_str()) <<endl;
cout << "Protocol : " << pt.get<string>((sDSN + "." + "Protocol").c_str()) <<endl;
cout << "ReadOnly : " << pt.get<string>((sDSN + "." + "ReadOnly").c_str()) <<endl;
cout << "RowVersioning : " << pt.get<string>((sDSN + "." + "RowVersioning").c_str()) <<endl;
cout << "ShowSystemTables : " << pt.get<string>((sDSN + "." + "ShowSystemTables").c_str()) <<endl;
cout << "ShowOidColumn : " << pt.get<string>((sDSN + "." + "ShowOidColumn").c_str()) <<endl;
cout << "FakeOidIndex : " << pt.get<string>((sDSN + "." + "FakeOidIndex").c_str()) <<endl;
cout << "ConnSettings : " << pt.get<string>((sDSN + "." + "ConnSettings").c_str()) <<endl;
cout << "\n\n" << endl;
}
.
.
执行
程序只需一个参数,’1’或’2′.
1 : Full run:-
populate incorrect data
attempt to connect
populate CORRECT data
wait 15 secs
attempt to connect
2 : attempt to connect with existing ini data
例如
./code 1
要么
./code 2
输出
对于完整运行./code 1,下面是输出.
请注意,在第二次尝试连接之前,odbc.ini已被修改并读取以显示正确的“端口号”.
[PostgresTest01]
Description : Description
Driver : PostgreSQL
Database : dvdrental
Servername : 192.168.45.217
Port : 1234
UserName : postgres
Password : postgres
Trace : Off
TraceFile : stderr
Protocol : 7.0
ReadOnly : No
RowVersioning : No
ShowSystemTables : No
ShowOidColumn : No
FakeOidIndex : No
ConnSettings :
Failed to connect:
The driver reported the following diagnostics whilst running SQLDriverConnect
./code 1
:1:101:[unixODBC]Could not connect to the server;
Connection refused [192.168.45.217:1234]
Test connection failed for sConnStrIn[DSN=PostgresTest01;]
====================================================================
Updating ini file with correct data...
Waiting for 15 secs
====================================================================
[PostgresTest01]
Description : Description
Driver : PostgreSQL
Database : dvdrental
Servername : 192.168.45.217
Port : 5432
UserName : postgres
Password : postgres
Trace : Off
TraceFile : stderr
Protocol : 7.0
ReadOnly : No
RowVersioning : No
ShowSystemTables : No
ShowOidColumn : No
FakeOidIndex : No
ConnSettings :
Failed to connect:
The driver reported the following diagnostics whilst running SQLDriverConnect
./code 1
:1:101:[unixODBC]Could not connect to the server;
Connection refused [192.168.45.217:1234]
Test connection failed for sConnStrIn[DSN=PostgresTest01;]
.
.
请注意,在第二次尝试中,虽然ini反映了在尝试连接之前15秒打印的正确数据,但错误消息显示连接被拒绝端口“1234”.
Connection refused [192.168.45.217:1234]
.
.
.
.
对于快速运行./code 2,在第一次运行之后,ini保持正确的数据,下面是输出.
它成功地连接起来.
[PostgresTest01]
Description : Description
Driver : PostgreSQL
Database : dvdrental
Servername : 192.168.45.217
Port : 5432
UserName : postgres
Password : postgres
Trace : Off
TraceFile : stderr
Protocol : 7.0
ReadOnly : No
RowVersioning : No
ShowSystemTables : No
ShowOidColumn : No
FakeOidIndex : No
ConnSettings :
Test connection succeeded.
Connection String is [DSN=PostgresTest01;DATABASE=dvdrental;SERVER=192.168.45.217;PORT=5432;UID=postgres;PWD=postgres;SSLmode=disable;ReadOnly=No;Protocol=7.0;FakeOidIndex=No;ShowOidColumn=No;RowVersioning=No;ShowSystemTables=No;ConnSettings=;Fetch=100;Socket=4096;UnknownSizes=0;MaxVarcharSize=255;MaxLongVarcharSize=8190;Debug=0;CommLog=0;Optimizer=0;Ksqo=1;UseDeclareFetch=0;TextAsLongVarchar=1;UnknownsAsLongVarchar=0;BoolsAsChar=1;Parse=0;CancelAsFreeStmt=0;ExtraSysTablePrefixes=dd_;;LFConversion=0;UpdatableCursors=1;DisallowPremature=0;TrueIsMinus1=0;BI=0;ByteaAsLongVarBinary=0;UseServerSidePrepare=0;LowerCaseIdentifier=0;]
.
.
问题
在这里重复这些问题.
>为什么./code 1导致两个连接测试失败？
> SQLDriverConnect是否以某种方式缓存数据,尽管如此
在连接尝试之间释放句柄？
>如何清除这个假定的缓存,以便让第二次后续尝试成功？
>如果它确实是一个错误,是否有一种解决方法可以在同一个程序运行中的后续测试中实现所需的结果(请记住,测试必须从服务器触发,无法重启)？
.
.
解决方法:
我会考虑尝试后一版本的驱动程序管理器,2.2.14是从2008年开始的.它可能无法修复尝试以后构建的问题,但是在缓存代码中肯定有修复.
此外,在构建2.3.x时,我会在配置中添加–enable-inicaching = no.这可能是您所看到的问题的原因.