Skip site navigation (1) Skip section navigation (2)

Error Retrieving Catalog Info

From: BGoebel <b(dot)goebel(at)prisma-computer(dot)de>
To: pgsql-odbc(at)postgresql(dot)org
Subject: Error Retrieving Catalog Info
Date: 2011-07-18 10:44:54
Message-ID: 1310985894068-4598955.post@n5.nabble.com (view raw or flat)
Thread:
Lists: 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.

Responses

pgsql-odbc by date

Next:From: BGoebelDate: 2011-07-18 10:49:56
Subject: Re: SQLDescribeCol – schema cache not being updated completely?
Previous:From: Hiroshi InoueDate: 2011-07-16 04:49:46
Subject: Re: Compiling 09.00.0300 on OS X Snow Leopard

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group