Error Retrieving Catalog Info - Mailing list pgsql-odbc

Hello,

I found an error when retrieving catalog information.
A workaround is also provided.

The comprehensive example is written in Delphi. Don't hesitate to send me a
message if something is unclear.

A similiar problem was posted @
http://postgresql.1045698.n5.nabble.com/SQLDescribeCol-schema-cache-not-being-updated-completely-tt4372956.html

regards
BGoebel

---------------  Code

procedure TForm1.Button3Click(Sender: TObject);
   type
      TDataBinding = record
         colname : String;
         DataType: SqlSmallint;
         columnSize: SQLUInteger;
         decimalDigits: SQLSmallint;
         nullAble: SQLSmallint;
      end;

   function getColumnName(const connectHandle: SQLHandle): string;
   var
      aStmtHandle: SqlHandle;
      aRes: integer;
      aInfo: TDataBinding;
      aTmpName: string;
      aLen: SQLSMALLINT;
   begin
      //Create new handle for select command
      aRes:= SQLAllocHandle(SQL_HANDLE_STMT, connectHandle, aStmtHandle);
      checkSqlReturn(aRes, SQL_HANDLE_DBC, connectHandle);

      //Statment Attributes

      //
      // ERROR using SQL_CONCUR_ROWVER
      //
      // WORKAROUND: If the following to lines are ommitted the query get
      // correct results.
      //
      aRes := SQLSetStmtAttr(aStmtHandle,
                             SQL_ATTR_CONCURRENCY,
                             pointer(SQL_CONCUR_ROWVER),
                             sizeof(SQLSmallint));
      checkSqlReturn(aRes, SQL_HANDLE_STMT, aStmtHandle);


      aRes := sqlSetStmtAttr(aStmtHandle,
                             SQL_ATTR_CURSOR_TYPE,
                             pointer(SQL_CURSOR_KEYSET_DRIVEN),
                             sizeof(SQLSmallint));
      checkSqlReturn(aRes, SQL_HANDLE_STMT, aStmtHandle);

      //Execute SQL Command
      aRes := SQLexecDirect(aStmtHandle,
                            pchar('SELECT * FROM test'),
                            SQL_NTS);
      checkSqlReturn(aRes, SQL_HANDLE_STMT, aStmtHandle);

      //get column information via SQLDescribeCol
      setLength(aTmpName, SQL_MAX_ID_LENGTH);
      aRes := SQLDescribeCol(aStmtHandle,
                             1,                    //ColumnNumber
                             @aTmpName[1],         //ColumnName
                             length(aTmpName),     //BufferLength
                             aLen,                 //NameLengthPtr
                             aInfo.DataType,       //DataTypePtr,
                             aInfo.columnSize,     //ColumnSizePtr
                             aInfo.scale,          //Decimal scale Ptr,
                             aInfo.nullable);      //NullablePtr
      checkSQLReturn(aRes, SQL_HANDLE_STMT, aStmtHandle);
      aInfo.ColName := copy(aTmpName, 1, aLen);

      //Free the statement
      SQLFreeHandle(SQL_HANDLE_STMT, aStmtHandle);

      result := aInfo.ColName;
   end;

Var aRes:Integer;
    hStmtCreate,hStmtSelect,fEnvHandle,fConnectHandle:SQLHandle;
    aSQLSmallInt:SQLSmallInt;
    aConnectString:String;
    aInfo: TDataBinding;
    i: integer;
    aColName_1, aColName_2: string;
Begin

   fEnvHandle := 0;
   fConnectHandle := 0;

   aRes := SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, fEnvHandle);
   checkSqlReturn(aRes, SQL_HANDLE_ENV, SQL_NULL_HANDLE);

   aRes := SQLAllocHandle(SQL_HANDLE_DBC, fEnvHandle, fConnectHandle);
   checkSqlReturn(aRes, SQL_HANDLE_ENV, fEnvHandle);

   aSqlSmallint := 0;
   aConnectString :=

'Driver={PostgreSQL};Server=127.0.0.1;Port=5432;Database=postgres;Uid=postgres;Pwd=MyDB;UpdatableCursors=1;usedeclarefetch=1;fetch=50'+
                            '';
   aRes := SQLDriverConnect(fConnectHandle,
                            GetDesktopWindow,
                            @aConnectString[1],
                            length(aConnectString),
                            nil,
                            0,
                            aSqlSmallint,
                            0);
   checkSqlReturn(aRes, SQL_HANDLE_ENV, fEnvHandle);

   aRes:= SQLAllocHandle(SQL_HANDLE_STMT, fConnectHandle, hStmtCreate);
   checkSqlReturn(aRes, SQL_HANDLE_DBC, fConnectHandle);

   //drop and create table
   aRes := SQLExecDirect(hStmtCreate, pchar('DROP TABLE IF EXISTS test'),
SQL_NTS);
   checkSqlReturn(aRes, SQL_HANDLE_STMT, hStmtCreate);

   aRes := SQLExecDirect(hStmtCreate,
                         pchar('CREATE TABLE test(integerCol integer)'),
                         SQL_NTS);
   checkSqlReturn(aRes, SQL_HANDLE_STMT, hStmtCreate);

   //Get the Columnname
   aColName_1 := getColumnName(fConnectHandle);

   //drop table test and create a new one with a DIFFERENT COLUMNNAME
   aRes := SQLExecDirect(hStmtCreate, pchar('DROP TABLE IF EXISTS test'),
SQL_NTS);
   checkSqlReturn(aRes, SQL_HANDLE_STMT, hStmtCreate);

   aRes := SQLExecDirect(hStmtCreate,
                         pchar('CREATE TABLE test(newcreateCol integer)'),
                         SQL_NTS);
   checkSqlReturn(aRes, SQL_HANDLE_STMT, hStmtCreate);

   //Get the columnname
   aColName_2 := getColumnName(fConnectHandle);

   //
   // This message shows if columname of table "test" has changed
   // If getColumnname uses SQLSetAttr(...,SQL_CONCUR_ROWVER
   // the changed columnname will NOT be retrieved
   //
   ShowMessage('first created columnname: ' + aColName_1 + #13#10 +
               'second created columnname: ' + aColName_2);
end;


--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Error-Retrieving-Catalog-Info-tp4598955p4598955.html
Sent from the PostgreSQL - odbc mailing list archive at Nabble.com.