2

While using the UNIX-ODBC libraries from unixodbc site, I'm facing a problem with the SQLDriverConnect api. If I try to connect to my database twice in a row, first with incorrect DSN data (Data Source Name data, placed in the /etc/odbc.ini generally) & second with correct data, the second attempt to connect also fails. The reason for the failure seems to be that the SQLDriverConnect seems to use the incorrect data fed to it in the first run.

After scouring the net for any mention of it caching the data, it seems no one else has come across this particular problem (or my search was inadequate).

The use-case for me is that I'm providing a GUI where the user can manually fill the form with all the parameters and then click on a 'Test Connection' button. This will write (or overwrite) the details into the /etc/odbc.ini file, and then attempt to connect to the database using the unixodbc apis. If the test succeeds, the connection string returned from the SQLDriverConnect will be populated onto the GUI. If it fails the GUI will display the failure and allow the user to edit the data on the form and click 'Test Connection' button again.

Problem: If user enter some incorrect data (say port number), the test fails and the user rectifies the data. When the user now tries to test the connection it should pass, as all the data is correct and is also populating into the odbc.ini file properly. Maddeningly, it fails for the second retest. However, sometimes a third or fourth retest onwards it is able to connect properly.

Note that the reconnects should ideally be in the single run of the program as a re-run of the program doesn't seem to surface the problem. This is important for me at the tests will be triggered from server-side and won't have the luxury of restarting.


System Details

Below are the details of the systems used for development and execution of the sample later:

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

. .

Sample code

Build the code with the libodbc, as below:

g++ -g -o code code.cpp -lodbc

Note that you may have to ensure that the included files and the libraries are in place.

#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;
}

. .

Execution

Program takes a single argument, '1' or '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

e.g.

./code 1

OR

./code 2

Outputs

For the full run ./code 1, below is the output. Notice that before the second attempt to connect, the odbc.ini has been modified and read to display the correct 'port number'.

[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

08001: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

08001:1:101:[unixODBC]Could not connect to the server;
Connection refused [192.168.45.217:1234]
Test connection failed for sConnStrIn[DSN=PostgresTest01;]

. . Notice that in the second attempt, although the ini reflects the correct data printed 15 secs before the connection is attempted, the error message shows that the connection is refused to port '1234'.

Connection refused [192.168.45.217:1234] . .


. .

For the quick run ./code 2, immediately following the first run, after which the ini holds the correct data, below is the output. It succeeds in connecting.

[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;]

. .

Questions

Reiterating the questions here.

  1. Why does ./code 1 result in both connection tests to fail?
  2. Is SQLDriverConnect somehow caching the data inspite of proper deallocation of handles between the connect attempts?
  3. How can this supposed cache be cleared, so as to let the second subsequent attempt succeed?
  4. If it is indeed a bug, is there a workaround to achieve the desired result in subsequent test within the same run of the program (remember that the tests have to be triggered from a server, which can't restart)?

. .

  • It seems like I remember that the UNIX ODBC driver sets an environment variable. Take a look at the output from printenv and see if there is a DSN variable or something that gets set. – 111--- Dec 8 '16 at 16:12
  • @datUser : Yes, you mean SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void *) SQL_OV_ODBC3, 0); I suppose. Three things baffle me though: 1. It is to set the ODBC version to use. 2. It is set before the DSN data is provided, so the possibility of it caching is very low. 3. As per documentation, if the handles are deallocated properly, then this would be cleared as well. This is why I have taken it off the list of suspects for now. Do you think, I'm missing something or do you even mean a completely different var? – Ramakant Dec 8 '16 at 16:30
  • @datUser : tried printenv, no variable with DSN data present. Anything else I could look at? – Ramakant Dec 8 '16 at 16:51
1

I would look at trying a latter version of the driver manager, 2.2.14 is from 2008. It may not fix the problem trying a later build, but there were certainly fixes added to the caching code.

Also, when building 2.3.x, I would add --enable-inicaching=no to the configure. That may well be the cause of the problem you are seeing.

  • Configuring with --enable-inicaching=no solved the problem for me. I assume that it is defaulted to 'yes' for performance reasons. Two follow-up queries are : 1. Why does the libodbc.so & 'libodbcinst.so` still show version as 2.0.0 and not incremented? Is it intentional? 2. Are 32 bit binary package for 2.3.4, with --enable-inicaching=no published some where I could download as rpm package, as that would help a hassle free deployment for me, as opposed to deploying individual binaries? Appreciate your insight. – Ramakant Dec 9 '16 at 13:12
  • 1
    There is no way to clear the cache in the current code, however, I have just added to 2.3.5pre to clear the cache when SQLWritePrivateProfileString is called. This will do what you need I think. – Nick Gorham Dec 12 '16 at 12:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.