The Gurus Guide to SQL Server Stored Procedures, XML, and HTML [Electronic resources] نسخه متنی

A Better Example

Now that you've seen how easy it is to put together a simple extended procedure, let's try one that's a little more complex. The code that follows in Listing 20-10 implements an extended procedure named xp_exec. As I mentioned in the chapter on UDFs, you aren't allowed to call EXEC() from within a UDF. Only extended procedures can be called, and only ones that begin with xp (sp_executesql isn't allowed even though it's an extended procedure because it doesn't begin with xp). Xp_exec works around this. You pass it a query you'd like to run, and it returns a result set, if there is one. Although you can't insert this result into a table variable (INSERT…EXEC isn't supported with table variables), you can manipulate tables and data, perform administrative tasks, and do most of the other things Transact-SQL allows you to do.

Because xp_exec needs to execute queries apart from the calling connection, it must initiate its own connection (over ODBC) to the server using the connection info from the caller. SQLAllocHandle() and SQLConnect() are the key functions we'll use. Here's the code:

Listing 20-10 Connecting from ODBC.

// STEP 1: Allocate an ODBC environment handle
sret = SQLAllocHandle(SQL_HANDLE_ENV, NULL, &henv);
if (sret != SQL_SUCCESS) {
handle_odbc_err("SQLAllocHandle:Env",
sret,
(DBINT) REMOTE_FAIL,
henv,
SQL_HANDLE_ENV,
srvproc);
return(XP_ERROR);
}
SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER) SQL_OV_ODBC3,
SQL_IS_INTEGER);
// STEP 2: Allocate an ODBC connection handle
sret = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
if (sret != SQL_SUCCESS) {
handle_odbc_err("SQLAllocHandle:Dbc",
sret,
(DBINT)REMOTE_FAIL,
henv,
SQL_HANDLE_ENV,
srvproc);
SQLFreeHandle(SQL_HANDLE_ENV, henv);
return(XP_ERROR);
}
// STEP 3: Check for integrated security.
if (strcmp(srv_pfield(srvproc, SRV_LSECURE, (int *)NULL), "TRUE") == 0) {
// Client has accessed using some form of integrated security
// Impersonate client and set SQL_INTEGRATED_SECURITY option
bImpersonated = srv_impersonate_client(srvproc);
// Connect to DSN using integrated security
SQLSetConnectAttr(hdbc, SQL_INTEGRATED_SECURITY,
(SQLPOINTER) SQL_IS_ON, SQL_IS_INTEGER);
_tcscpy(acUID, _T("));
_tcscpy(acPWD, _T("));
}
else {
// Client used standard login. Set the user name and password.
#ifdef UNICODE
MultiByteToWideChar(CP_ACP, 0, srv_pfield(srvproc, SRV_USER, NULL),
-1, acUID, MAXNAME);
MultiByteToWideChar(CP_ACP, 0, srv_pfield(srvproc, SRV_PWD, NULL),
-1, acPWD, MAXNAME);
#else
strncpy(acUID, srv_pfield(srvproc, SRV_USER, NULL),
MAXNAME);
strncpy(acPWD, srv_pfield(srvproc, SRV_PWD, NULL),
MAXNAME);
#endif
}
// STEP 4: Connect
if (!SQL_SUCCEEDED(
sret = SQLConnect(hdbc, (SQLTCHAR*) szDSN, SQL_NTS,
(SQLTCHAR*) acUID, SQL_NTS, (SQLTCHAR*) acPWD, SQL_NTS)
)) {
handle_odbc_err("SQLConnect",
sret,
(DBINT)REMOTE_FAIL,
hdbc,
SQL_HANDLE_DBC,
srvproc);
goto SAFE_EXIT;
}

As you can see, I've broken this down into four steps. First, we get an ODBC environment handle. We then use this handle to allocate an ODBC connection handle. Once we have a connection handle, we check the security of the calling connection and set ours to match it. Lastly, we take the connection handle and connect to the specified ODBC DSN (xp_exec assumes you've created a system DSN named LocalServer, but you can change that if you like). Once connected, we'll be able to execute SQL batches.

Obviously, starting a new connection within xp_exec complicates things a bit because it means that locks held by the caller will block xp_exec. To get around this, you can instruct xp_exec to call sp_bindsession to enlist in the caller's transaction block. This keeps xp_exec from blocking or being blocked by transactions initiated by the caller. You pass Y as xp_exec's second parameter to cause it to enlist in the caller's transaction. The code is presented in Listing 20-11:

Listing 20-11 Joining the caller's transaction block.

// STEP 1: Get the client session token
if ((szShareTran[0]=='Y') || (szShareTran[0]=='y')) {
rc = srv_getbindtoken(srvproc, acBindToken);
if (rc == FAIL) {
srv_sendmsg(srvproc,
SRV_MSG_ERROR,
EXECSQL_ERROR,
SRV_INFO,
(DBTINYINT) 0,
NULL,
0,
0,
"Error with srv_getbindtoken",
SRV_NULLTERM);
srv_senddone(srvproc, (SRV_DONE_ERROR | SRV_DONE_MORE), 0, 0);
return(XP_ERROR);
}
// STEP 2: Bind it as a param for the proc call
_tcscpy(szQuery, _T("{call sp_bindsession(?)}"));
sret = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_VARCHAR, 255, 0, acBindToken, 256, NULL);
if (sret != SQL_SUCCESS) {
handle_odbc_err("SQLBindParameter",
sret,
(DBINT)REMOTE_FAIL,
hstmt,
SQL_HANDLE_STMT,
srvproc);
return(XP_ERROR);
}
// STEP 3: Bind our session to the client's session
sret = SQLExecDirect(hstmt, (SQLTCHAR*) szQuery, SQL_NTS);
if (!((sret == SQL_SUCCESS) ||(sret == SQL_SUCCESS_WITH_INFO))) {
handle_odbc_err("SQLExecDirect",
sret,
(DBINT) EXECSQL_ERROR,
hstmt,
SQL_HANDLE_STMT,
srvproc);
return(XP_ERROR);
}
}

I've broken this down into three steps. First we get the token for the current client session by calling srv_getbindtoken(). Next we bind this value as an ODBC procedure parameter so that we can pass it to sp_bindsession. Last we call sp_bindsession and pass it the session token from the calling connection. This enlists the calling connection and our new ODBC connection in the same transaction. To verify that xp_exec has enlisted in the caller's transaction, you can begin a transaction in the caller and have xp_exec check @@TRANCOUNT, like this:

BEGIN TRAN
PRINT 'Joining the transaction of the caller'
EXEC master..xp_exec 'SELECT @@TRANCOUNT','Y','master'
PRINT 'Running independently'
EXEC master..xp_exec 'SELECT @@TRANCOUNT','N','master'
GO
ROLLBACK

(Results abridged)

Joining the transaction of the caller
-----------
1
Running independently
-----------
0

Xp_exec's third parameter specifies the database context in which to run your code. It's an optional parameter. The previous example specifies the master database as the target context.

Once we've decided whether to enlist in the caller's transaction, the next order of business is to execute the query. That code looks like this (Listing 20-12):

Listing 20-12 Using ODBC to execute the query.

// STEP 4: Execute the query
_tcscpy(szQuery, szTSQL);
sret = SQLExecDirect(hstmt, (SQLTCHAR*) szQuery, SQL_NTS);
if (!((sret == SQL_SUCCESS)||(sret == SQL_NO_DATA))) {
handle_odbc_err("SQLExecDirect",
sret,
(DBINT) EXECSQL_ERROR,
hstmt,
SQL_HANDLE_STMT,
srvproc);
return(XP_ERROR);
}

SQLExecDirect() is the key function here. We pass it a statement handle (allocated earlier), the query to execute, and the type of string (null-terminated) that contains the query.

Because it doesn't know what type of result set (if any) it might receive, xp_exec makes some ODS calls to determine the characteristics of the result set. The key function call is SQLColAttribte(). Here's the code (Listing 20-13):

Listing 20-13 You can use ODBC calls to interpret a result set.

for (nCol = 0; nCol < nCols; nCol++) {
// Get the column name, length, and data type.
SQLColAttribute(hstmt,
(SQLSMALLINT) (nCol + 1),
SQL_DESC_NAME,
(SQLTCHAR*) acColumnName, // returned column name
MAXNAME, // max length of rgbDesc buffer
&cbAttr, // number of bytes returned in rgbDesc
&iNumAttr);
SQLColAttribute(hstmt,
(SQLSMALLINT) (nCol + 1),
SQL_DESC_OCTET_LENGTH,
NULL,
0,
NULL,
&cbColData);
// Get the column's SQL Server data type, then reset the length
// of the data retrieved as required.
SQLColAttribute(hstmt,
(SQLSMALLINT) (nCol + 1),
SQL_CA_SS_COLUMN_SSTYPE,
NULL,
0,
NULL,
&eSQLType);
// Overwrite the column length returned by ODBC
// with the correct value to be used by ODS
switch( eSQLType ) {
case SQLMONEYN:
case SQLMONEY:
cbColData = sizeof(DBMONEY);
break;
case SQLDATETIMN:
case SQLDATETIME:
cbColData = sizeof(DBDATETIME);
break;
case SQLNUMERIC:
case SQLDECIMAL:
cbColData = sizeof(DBNUMERIC);
break;
case SQLMONEY4:
cbColData = sizeof(DBMONEY4);
break;
case SQLDATETIM4: //smalldatetime
cbColData = sizeof(DBDATETIM4);
break;
}
// ...

Once we've found out what columns we have in the result set, we can begin sending them back to the client. As with xp_listfile, we call srv_describe() to set up each result set column and srv_sendrow() to send each row back to the client (Listing 20-14):

Listing 20-14 Once we have the ODBC result set in hand, we use ODS to transmit it.

// Allocate memory for row data.
if ((ppData[nCol] = (PBYTE) malloc(cbColData)) == NULL)
goto SAFE_EXIT;
memset(ppData[nCol], 0, cbColData);
// Bind column
SQLBindCol(hstmt,
(SQLSMALLINT) (nCol + 1),
SQL_C_BINARY, // No data conversion.
ppData[nCol],
cbColData,
&(pIndicators[nCol]));
// Prepare structure that will be sent via ODS back to
// the caller of the extended procedure
srv_describe(srvproc,
nCol + 1,
acColumnName,
SRV_NULLTERM,
eSQLType, // Dest data type.
(DBINT) cbColData,// Dest data length.
eSQLType, // Source data type.
(DBINT) cbColData, // Source data length.
(PBYTE) NULL);
}
// Initialize the row counter
rows = 0;
// Get each row of data from ODBC
// until there are no more rows
while((sret = SQLFetch(hstmt)) != SQL_NO_DATA_FOUND) {
if (!SQL_SUCCEEDED(sret)) {
handle_odbc_err("SQLFetch",
sret,
(DBINT) EXECSQL_ERROR,
hstmt,
SQL_HANDLE_STMT,
srvproc);
goto SAFE_EXIT;
}
// For each data field in the current row,
// fill the structure
// that will be sent back to the caller.
for (nCol = 0; nCol < nCols; nCol++) {
cbColData = (pIndicators[nCol] == SQL_NULL_DATA ?
0 : pIndicators[nCol]);
srv_setcollen(srvproc, nCol+1, (int) cbColData);
srv_setcoldata(srvproc, nCol+1, ppData[nCol]);
}
// Send the data row back to SQL Server via ODS.
if (srv_sendrow(srvproc) == SUCCEED)
rows++;
}

As you can see, building even a moderately complex extended procedure is pretty straightforward.