LOCK TABLE can only be used in transaction blocks

Question

I am upgrading PostgreSQL from 8.1.3 to 9.2.9 for our product. We use unixODBC 2.2.11 to access database and it is written by C++ language. I got the following error after upgrade:

"LOCK TABLE can only be used in transaction blocks".

I checked the release note for PostgreSQL and found it disallow LOCK TABLE outside a transaction block.

I checked code and found we locked some tables in transation, sample code:

db_connect.BeginTrans();
rtn_code = LockTable(db_connect, Setting_Filter.m_backup_tables, LOCK_SHARE);
...
db_connect.Commit();

We use following api to create implicit transaction in BeginTrans():

int BeginTrans()
{
    return SQLSetConnectAttr( m_ConHandle,SQL_ATTR_AUTOCOMMIT,(SQLPOINTER)SQL_AUTOCOMMIT_OFF, 0 );
}

We execute the following lock table sql in LockTable() function:

lock table %s in share mode

My question is:

1. Does this mean that the BeginTrans() doesn't create transaction successfully in Postgres 9.2?

2. I notice that we often use 'BEGIN TRANSATION;' in PL/SQL, is there any corresponding API in unixODBC to start transaction explicitly?

Answer 1

This issue has been fixed by explicit call for transaction, for example:

    int Commit()
    {
        Execute("commit");
        int rt=SQLEndTran( SQL_HANDLE_DBC, m_ConHandle, SQL_COMMIT );
        SQLSetConnectAttr( m_ConHandle,SQL_ATTR_AUTOCOMMIT,(SQLPOINTER)SQL_AUTOCOMMIT_ON, 0 );

        return rt;
    }

    int Rollback()
    {
        Execute("rollback");
        int rt=SQLEndTran( SQL_HANDLE_DBC, m_ConHandle, SQL_ROLLBACK );
        SQLSetConnectAttr( m_ConHandle,SQL_ATTR_AUTOCOMMIT,(SQLPOINTER)SQL_AUTOCOMMIT_ON, 0 );
        return rt;
    }

    int BeginTrans()
    {
        SQLSetConnectAttr( m_ConHandle,SQL_ATTR_AUTOCOMMIT,(SQLPOINTER)SQL_AUTOCOMMIT_OFF, 0 );
        return Execute("begin transaction");
    }