Error Retrieving Catalog Info

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-schem a-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=pos tgres;Uid=postgres;Pwd=MyDB;UpdatableCursors=1;usedeclarefet ch=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-Cat alog-Info-tp4598955p4598955.html
Sent from the PostgreSQL - odbc mailing list archive at Nabble.com.

--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc