Problem with way to access all recordsets returned by sp_spaceused using ODBC APIs on C++

Hi all,
I have a problem executing a sp_spaceused on a database.
Please execute sp_spaceused on your local database so that further
discussion will be more easier to understand.
The code snippet is as follows :
SQLCHAR database_size[15];
SQLCHAR unused[15];
The database_size is member of first record set and
unused is member of second recordset returned by the sp_spaceused.
SQLINTEGER rlength;
retcode = SQLAllocHandle(SQL_HANDLE_STMT,hDBC,&hStmt);
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)
{
retcode = SQLExecDirect(hStmt,(unsigned char *)sqlCommand,
SQL_NTS);
retcode = SQLBindCol(hStmt,2, SQL_CHAR, (SQLPOINTER) &database_size,
sizeof(database_size),&rlength);
//Problem - was not able to bind any
column of the second recordset so not able to fetch the unused space.
if (retcode != SQL_SUCCESS )
{
AfxMessageBox("Error occured while binding");
}
CString csDatabase_size;
//The while loop below returns after the first iteration as only first
recordset is available and nothing is accessible of second recordset so
cant get 'unused' field
while ((retcode = SQLFetch(hStmt)) == SQL_SUCCESS)
{
stored the return
values of database_size and unused.
}
SQLFreeHandle(SQL_HANDLE_STMT, hStmt);
}
else
{
AfxMessageBox("error while allocating a statement handle");
}
So the Problem is :
Is there any way to access all recordsets returned by sp_spaceused
using ODBC APIs on C++?
Please reply.
Thanks in advance
-Ashutosh
exec sp_spaceused returns:
Result set 1:
database_name
database_size
unallocated space
Result set 2:
reserved
data
index_size
unused
So, from your description you are trying to get database_size (col2) from
first result set, and unused (col4) from second result set.
You have to:
SQLExecDirect()
SQLBindCol( database_name, col2 )
SQLFetch() until all records read from first result set
SQLMoreResults() if returns SQL_SUCESS then you have another result set, so
...
SQLFreeStmt( SQL_UNBIND )
SQLBindCol( unused, col4 )
SQLFetch() until all records read from second result set
cmk
|||Thanx alot for your help !
Chris Kushnir wrote:
> exec sp_spaceused returns:
> Result set 1:
> database_name
> database_size
> unallocated space
> Result set 2:
> reserved
> data
> index_size
> unused
> So, from your description you are trying to get database_size (col2) from
> first result set, and unused (col4) from second result set.
> You have to:
> SQLExecDirect()
> SQLBindCol( database_name, col2 )
> SQLFetch() until all records read from first result set
> SQLMoreResults() if returns SQL_SUCESS then you have another result set, so
> ...
> SQLFreeStmt( SQL_UNBIND )
> SQLBindCol( unused, col4 )
> SQLFetch() until all records read from second result set
>
> cmk