Some of my colleague is lazy, and create the report in SQL and refuse to create an interface for user to execute the SQL. The SQL looks like below. The 'print' make the result in the SQL server management message window. So I'm thinking to make an interface but I do not want to change the SQL, so that is mean I need to get the result from the 'print'. The google told me it is possible to create it through register an call back fuction is CS, but I want an C++ interface. So I finally find ODBC can do it.
Code : Set @DueFromPBOC =(Select isnull(sum(isnull(ac.AccountBalance,0)),0) From View_Account_Log ac
Print 'Result = '+ Convert(varchar(50), @DueFromPBOC)
The process is just like below,
SQLAllocEnv
SQLAllocConnect
SQLConnect
SQLPrepare
SQLExecute
after that, using SQLGetDiagRec to find out the result from the SQL 'print'
Code :
i=1;
while ((rc2 = SQLGetDiagRec(SQL_HANDLE_STMT, hstmt, i, SqlState, &NativeError,
Msg, sizeof(Msg), &MsgLen)) != SQL_NO_DATA)
{
//DisplayError(SqlState,NativeError,Msg,MsgLen);
//printf("%s, %srn",SqlState, Msg);
printf("%srn",Msg);
i++;
// }
}
There are two points here,
1. The SQL sentence must have an zero (0x0) end, to let SQL server know the end of the sentense
2. Must use SQLExecute instead of SQLExecDirect, SQLExecDirect can not reture the entired message, and only reture first part if you have several select statement in one batch. |
