Help:'ODBC columns informations'

speedman@mbox.vol.it
Thursday, November 21, 1996

Environment: MSVC 1.52,Win 95

How can retrive information about the columns of a database,like the
name and the datatype of each columns?
It's possible do this with an SQL command?
If yes which is the query that i must execute for retrive column's
informations.
I want to make a class derived from CRecordset able to exchanging data
with every
files so i must take columns information at run time ,I think it' s
possible only
with directly calls to ODBC but I'm not able to do this ,can you tell me
the way
to operate? wich ODBC functions i must use ?
Can you give me a simple example, if it's possible, I don't want ask too
much!
Tanks.

e-mail: speedman@mbox.vol.it

---

Christian Studer -- cstuder@access.ch
Saturday, November 23, 1996

[Mini-digest: 3 responses]

Hey Speedman,

the ODBC API function for this purpose is SQLColumns. It returns a result
set containing information about every column in a table. If you don't have
the ODBC SDK on your VC++ CD, you can get the 3.0 version from MSDN Online
at microsoft.com. The sample function takes the name of a table as an
argument. Hope this helps.

HRESULT GetColumns(UCHAR *szTableName) 
{
	HSTMT          hstmt;
	RETCODE     ret;
	UCHAR         szColumnName[129];
	SDWORD      cbColumnName = 128;
	SDWORD      pcbColumnName;
	SSHORT       iDataType;
	SDWORD      cbDataType = sizeof(SSHORT);
	SDWORD      pcbDataType;
	UINT             uRowsFetched;

// Allocate a statement handle for use with SQLColumns
// hdbc is the connection handle (if you use CDatabase, this is available
as
// a member variable)

	ret = SQLAllocStmt(hdbc, &hstmt);
	if (ret != SQL_SUCCESS)
	{
		return S_FALSE;
	}

// Get information on every column in the specified table

	ret = SQLColumns(hstmt, NULL, 0, NULL, 0, szTableName,
lstrlen(szTableName),
		NULL, 0);
	if (ret != SQL_SUCCESS)
	{
		SQLFreeStmt(hstmt, SQL_DROP);
		return S_FALSE;
	}

// Bind the COLUMN_NAME, DATA_TYPE columns in the returned result set
// (there are other columns in the result set. Look them up in the ODBC
SDK)

	ret = SQLBindCol(hstmt, 4, SQL_C_CHAR, szColumnName, cbColumnName,
		&pcbColumnName);
	if (ret != SQL_SUCCESS)
	{
		SQLFreeStmt(hstmt, SQL_DROP);
		return S_FALSE;
	}
	
	ret = SQLBindCol(hstmt, 5, SQL_C_SSHORT, &iDataType, cbDataType,
		&pcbDataType);
	if (ret != SQL_SUCCESS)
	{
		SQLFreeStmt(hstmt, SQL_DROP);
		return S_FALSE;
	}

// Fetch the data from the result set
	
	uRowsFetched = 0;

	while (TRUE)
	{
		ret = SQLFetch(hstmt);
		
		if (ret == SQL_NO_DATA_FOUND) // all rows fetched
		{
			uNumColumns = uRowsFetched;
			SQLFreeStmt(hstmt, SQL_DROP);
			return S_OK;
		}
		
		// put the data in the row into an array or something
		
		uRowsFetched += 1;
	}
}

Good luck!
Christian

------------------------------------------------------------               
                                                                           
 
Visit http://www.access.ch/beautiful_star         
------------------------------------------------------------               
                                                                           


> How can retrive information about the columns of a database,like the
> name and the datatype of each columns?
> It's possible do this with an SQL command?
> If yes which is the query that i must execute for retrive column's
> informations.
> I want to make a class derived from CRecordset able to exchanging data
> with every
> files so i must take columns information at run time ,I think it' s
> possible only
> with directly calls to ODBC but I'm not able to do this ,can you tell me
> the way
> to operate? wich ODBC functions i must use ?
> Can you give me a simple example, if it's possible, I don't want ask too
> much!
> Tanks.

-----From: Martin Bell 

Hi,

If you look for the SQLColumns command in the VC++ online books there is
an example of what you want to do.

Martin Bell

-----From: Amit Ramon 

You should use the ODBC function  SQLColumns(). It returns a dataset
that contains full information about columns in your database.


Amit.

---

| Вернуться в корень Архива |