Bookmarks

Yahoo Gmail Google Facebook Delicious Twitter Reddit Stumpleupon Myspace Digg

Search queries

sqldatasource dal, wwwxxxenden, convert raid5 to raid 10 mdadm, apache force chunked, nrao wwwxxx, xxxxxdup, procmail change subject header, wwwXxx not20, Wwwxxx.doks sas, linux raid resync after reboot

Links

XODOX
Impressum

#1: Error Retrieving Catalog Info

Posted on 2011-07-18 12:44:54 by BGoebel

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

Report this message