Retrieving metadata from sqlserver including column and table comments does not work

[Lothar Behrens]

Hi,

I am struggling with several database systems to get the remarks per
column. Thus I tried SQL Server 2000. I'll test SQL Server 2005 later.

I have added log messages for each row of the result set from
SQLColumns, but I never see the remarks from - say - the Northwind
sample database.
(I have added a remark in Employees.EmployeeID)

A log line: Pid 2116 :2009.10.19 - 13:12:34 Datei: lbDB.cpp Zeile:
6319 Message: Got remarks for column: , table = Employees, column =
EmployeeID

Other fields I need to grab are ok.

The code was tested against a SQL Server 2000 Database without getting
table comments and column comments.
The code was tested against a MySQL database with getting table
comments but no column comments.

Is there anything wrong with my code?

#define TAB_LEN 128+1
#define REM_LEN 254+1

SQLCHAR szCatalog[TAB_LEN], szSchema[TAB_LEN];
SQLCHAR szTableName[TAB_LEN], szColumnName[TAB_LEN];
SQLCHAR szTypeName[TAB_LEN], szRemarks[REM_LEN];
SQLCHAR szColumnDefault[TAB_LEN], szIsNullable[TAB_LEN];
SQLINTEGER ColumnSize, BufferLength, CharOctetLength,
OrdinalPosition;
SQLSMALLINT DataType, DecimalDigits, NumPrecRadix, Nullable;
SQLSMALLINT SQLDataType, DatetimeSubtypeCode;
SQLRETURN retcode;
SQLHSTMT hstmt;

/* Declare buffers for bytes available to return */

SQLINTEGER cbCatalog, cbSchema, cbTableName, cbColumnName;
SQLINTEGER cbDataType, cbTypeName, cbColumnSize, cbBufferLength;
SQLINTEGER cbDecimalDigits, cbNumPrecRadix, cbNullable, cbRemarks;
SQLINTEGER cbColumnDefault, cbSQLDataType, cbDatetimeSubtypeCode,
cbCharOctetLength;
SQLINTEGER cbOrdinalPosition, cbIsNullable;

retcode = SQLAllocStmt(hdbc, &hstmt); /* Statement handle */

retcode = SQLColumns(hstmt, NULL, 0, NULL, 0, NULL, 0, NULL, 0);

if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
/* Bind columns in result set to buffers */

//SQLBindCol(hstmt, 1, SQL_C_CHAR, szCatalog, TAB_LEN,&cbCatalog);
//SQLBindCol(hstmt, 2, SQL_C_CHAR, szSchema, TAB_LEN, &cbSchema);
SQLBindCol(hstmt, 3, SQL_C_CHAR, szTableName,
TAB_LEN,&cbTableName);
SQLBindCol(hstmt, 4, SQL_C_CHAR, szColumnName, TAB_LEN,
&cbColumnName);
//SQLBindCol(hstmt, 5, SQL_C_SSHORT, &DataType, 0, &cbDataType);
SQLBindCol(hstmt, 6, SQL_C_CHAR, szTypeName, TAB_LEN, &cbTypeName);
SQLBindCol(hstmt, 7, SQL_C_SLONG, &ColumnSize, 0, &cbColumnSize);
//SQLBindCol(hstmt, 8, SQL_C_SLONG, &BufferLength, 0,
&cbBufferLength);
//SQLBindCol(hstmt, 9, SQL_C_SSHORT, &DecimalDigits, 0,
&cbDecimalDigits);
//SQLBindCol(hstmt, 10, SQL_C_SSHORT, &NumPrecRadix, 0,
&cbNumPrecRadix);
SQLBindCol(hstmt, 11, SQL_C_SSHORT, &Nullable, 0, &cbNullable); //
SQL_NO_NULLS = 0, SQL_NULLABLE = 1, SQL_NULLABLE_UNKNOWN = 2
SQLBindCol(hstmt, 12, SQL_C_CHAR, szRemarks, REM_LEN, &cbRemarks);
//SQLBindCol(hstmt, 13, SQL_C_CHAR, szColumnDefault, TAB_LEN,
&cbColumnDefault);
//SQLBindCol(hstmt, 14, SQL_C_SSHORT, &SQLDataType, 0,
&cbSQLDataType);
//SQLBindCol(hstmt, 15, SQL_C_SSHORT, &DatetimeSubtypeCode, 0,
&cbDatetimeSubtypeCode);
//SQLBindCol(hstmt, 16, SQL_C_SLONG, &CharOctetLength, 0,
&cbCharOctetLength);
//SQLBindCol(hstmt, 17, SQL_C_SLONG, &OrdinalPosition, 0,
&cbOrdinalPosition);
//SQLBindCol(hstmt, 18, SQL_C_CHAR, szIsNullable, TAB_LEN,
&cbIsNullable);

while(retcode == SQL_SUCCESS) {
szRemarks[0] = 0; // Seems to be needed due to get rubbish
otherwise.
retcode = SQLFetch(hstmt);

_LOG << "Got remarks for column: " << (char*) szRemarks <<
", table = " << (char*) szTableName << ", column = " << (char*)
szColumnName LOG_

Thanks

Lothar

[Dmitriy Ivanov]

"Lothar Behrens" <lothar....@lollisoft.de> wrote:

LB> I am struggling with several database systems to get the remarks
LB> per column. Thus I tried SQL Server 2000. I'll test SQL Server 2005
LB> later.
LB>
LB> I have added log messages for each row of the result set from
LB> SQLColumns, but I never see the remarks from - say - the Northwind
LB> sample database.
LB> (I have added a remark in Employees.EmployeeID)
LB>
LB> A log line: Pid 2116 :2009.10.19 - 13:12:34 Datei: lbDB.cpp Zeile:
LB> 6319 Message: Got remarks for column: , table = Employees, column =
LB> EmployeeID
LB>
LB> Other fields I need to grab are ok.
LB>
LB> The code was tested against a SQL Server 2000 Database without
LB> getting table comments and column comments.
LB> The code was tested against a MySQL database with getting table
LB> comments but no column comments.

Your code looks OK to me though I would also print the cbRemarks value.
IMHO, misfunction of the SQL Server driver is very possible.
No wonder.
The Access driver returns wrong length (twice as it should be) in the
REMARKS column.
--
Sincerely,
Dmitriy Ivanov
Common Lisp ODBC interface - lisp.ystok.ru/ysql/

[Pak-Ming Cheung - MSFT]

In SQL Server ODBC driver, it is using the SProc sp_columns (sqlsrv32) or
sp_columns_100 (SNAC 10) to obtain the column meta data. However, the column
REMARKS is always set to NULL. Please see:
http://msdn.microsoft.com/en-us/library/ms176077.aspx. Therefore, you cannot
obtain the remark information with the API SQLColumns.

Instead, you can use the following system view to obtain the column
description:
SELECT *
FROM sys.extended_properties
WHERE class = 1 AND
major_id = object_id(N'dbo.Course') AND
name = 'MS_Description'

where "dbo.Course" is one of the tables in the database.

You can have more detail at:
sys.extended_properties:
http://msdn.microsoft.com/en-us/library/ms177541.aspx; and
object_id: http://msdn.microsoft.com/en-us/library/ms190328(SQL.90).aspx

Thanks,
Ming.
P.S. We recommend customers to use the forum to ask questions in the future,
where you can obtain a faster response (Forum is at:
http://social.msdn.microsoft.com/forums/en-US/sqldataaccess/threads/)

"Dmitriy Ivanov" wrote:

> .
>