| title | description | author | ms.author | ms.date | ms.service | ms.subservice | ms.topic | monikerRange |
|---|---|---|---|---|---|---|---|---|
Natively compiled stored procedures - data access applications |
Find guidance for calling natively compiled stored procedures from data access applications, with an example that uses the SQL Server Native Client ODBC driver. |
MikeRayMSFT |
mikeray |
11/10/2022 |
sql |
in-memory-oltp |
conceptual |
=azuresqldb-current||>=sql-server-2016||>=sql-server-linux-2017||=azuresqldb-mi-current |
[!INCLUDE SQL Server Azure SQL Database Azure SQL Managed Instance]
This topic discusses guidance on calling natively compiled stored procedures from data access applications.
-
Cursors cannot iterate over a natively compiled stored procedure.
-
Calling natively compiled stored procedures from CLR modules, by using the context connection, is not supported.
-
For SqlClient, there is no distinction between prepared and direct execution. Execute stored procedures with SqlCommand with
CommandType = CommandType.StoredProcedure. -
SqlClient does not support prepared RPC procedure calls.
-
SqlClient does not support the retrieval of schema-only information (metadata discovery) about the result sets returned by a natively compiled stored procedure (
CommandType.SchemaOnly).- Instead, use sp_describe_first_result_set (Transact-SQL).
- Versions of [!INCLUDEssNoVersion] Native Client prior to [!INCLUDEssSQL11] do not support the retrieval of schema-only information (metadata discovery) about the result sets returned by a natively compiled stored procedure.
- Instead, use sp_describe_first_result_set (Transact-SQL).
- This sample was originally written for the SQL Server Native Client (sqlncli.h) but has been updated to use the Microsoft ODBC Driver for SQL Server (MSODBCSQL). [!INCLUDEsnac-removed-oledb-and-odbc]
The following recommendations apply to calls of natively compiled stored procedure using the ODBC driver in [!INCLUDEssNoVersion] Native Client.
Call one time: The most efficient way to call a stored procedure once is to issue a direct RPC call using SQLExecDirect and ODBC CALL clauses. Do not use the [!INCLUDEtsql] EXECUTE statement. If a stored procedure is called more than once, prepared execution is more efficient.
Call many times: The most efficient way to call a [!INCLUDEssNoVersion] stored procedure more than once is through prepared RPC procedure calls. Prepared RPC calls are performed as follows using the ODBC driver in [!INCLUDEssNoVersion] Native Client:
- Open a connection to the database.
- Bind the parameters by using SQLBindParameter.
- Prepare the procedure call by using SQLPrepare.
- Execute the stored procedure multiple times by using SQLExecute.
The following C code fragment shows prepared execution of a stored procedure to add line items to an order. SQLPrepare is called only once. And SQLExecute is called multiple times, once for each procedure execution.
// Bind parameters
// 1 - OrdNo
SQLRETURN returnCode = SQLBindParameter(
hstmt, 1, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 10, 0,
&order.OrdNo, sizeof(SQLINTEGER), NULL);
if (returnCode != SQL_SUCCESS && returnCode != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
// 2, 3, 4 - ItemNo, ProdCode, Qty
...
// Prepare stored procedure
returnCode = SQLPrepare(hstmt, (SQLTCHAR *) _T("{call ItemInsert(?, ?, ?, ?)}"),
SQL_NTS);
for (unsigned int i = 0; i < order.ItemCount; i++) {
ItemNo = order.ItemNo[i];
ProdCode = order.ProdCode[i];
Qty = order.Qty[i];
// Execute stored procedure
returnCode = SQLExecute(hstmt);
if (returnCode != SQL_SUCCESS && returnCode != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
}This sample shows how to bind parameters and execute stored procedures using the [!INCLUDEssNoVersion] Native Client ODBC driver. The sample compiles to a console application that inserts a single order using direct execution, and inserts the order details using prepared execution.
To run this sample:
-
Create a sample database with a memory-optimized data filegroup. For information on how to create a database with a memory-optimized data filegroup, see Creating a Memory-Optimized Table and a Natively Compiled Stored Procedure.
-
Create an ODBC data source called PrepExecSample that points to the database. Use the [!INCLUDEssNoVersion] Native Client driver. You could also modify the sample and use the Microsoft ODBC Driver for SQL Server.
-
Run the [!INCLUDEtsql] script (below) on the sample database.
-
Compile and run the sample.
-
Verify successful execution of the program by querying the contents of the tables:
SELECT * FROM dbo.Ord;
SELECT * FROM dbo.Item;
The following is the [!INCLUDEtsql] code listing that creates the memory-optimized database objects.
IF EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID=OBJECT_ID('dbo.OrderInsert'))
DROP PROCEDURE dbo.OrderInsert;
GO
IF EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID=OBJECT_ID('dbo.ItemInsert'))
DROP PROCEDURE dbo.ItemInsert;
GO
IF EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID=OBJECT_ID('dbo.Ord'))
DROP TABLE dbo.Ord;
GO
IF EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID=OBJECT_ID('dbo.Item'))
DROP TABLE dbo.Item;
GO
CREATE TABLE dbo.Ord
(
OrdNo INTEGER NOT NULL PRIMARY KEY NONCLUSTERED,
OrdDate DATETIME NOT NULL,
CustCode VARCHAR(5) NOT NULL)
WITH (MEMORY_OPTIMIZED=ON);
GO
CREATE TABLE dbo.Item
(
OrdNo INTEGER NOT NULL,
ItemNo INTEGER NOT NULL,
ProdCode INTEGER NOT NULL,
Qty INTEGER NOT NULL,
CONSTRAINT PK_Item PRIMARY KEY NONCLUSTERED (OrdNo,ItemNo))
WITH (MEMORY_OPTIMIZED=ON);
GO
CREATE PROCEDURE dbo.OrderInsert(
@OrdNo INTEGER, @CustCode VARCHAR(5))
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS BEGIN ATOMIC WITH
(TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = 'english')
DECLARE @OrdDate datetime = GETDATE();
INSERT INTO dbo.Ord (OrdNo, CustCode, OrdDate)
VALUES (@OrdNo, @CustCode, @OrdDate);
END;
GO
CREATE PROCEDURE dbo.ItemInsert(
@OrdNo INTEGER, @ItemNo INTEGER, @ProdCode INTEGER, @Qty INTEGER)
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS BEGIN ATOMIC WITH
(TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
INSERT INTO dbo.Item (OrdNo, ItemNo, ProdCode, Qty)
VALUES (@OrdNo, @ItemNo, @ProdCode, @Qty)
END
GO The following is the C code listing.
// compile with: user32.lib odbc32.lib
#pragma once
#define WIN32_LEAN_AND_MEAN // Exclude rarely-used stuff from Windows headers.
#include <stdio.h>
#include <stdlib.h>
#include <tchar.h>
#include <windows.h>
#include "sql.h"
#include "sqlext.h"
#include "msodbcsql.h"
// cardinality of order item related array variables
#define ITEM_ARRAY_SIZE 20
// struct to pass order entry data
typedef struct OrdEntry_struct {
SQLINTEGER OrdNo;
SQLTCHAR CustCode[6];
SQLUINTEGER ItemCount;
SQLINTEGER ItemNo[ITEM_ARRAY_SIZE];
SQLINTEGER ProdCode[ITEM_ARRAY_SIZE];
SQLINTEGER Qty[ITEM_ARRAY_SIZE];
} OrdEntryData;
SQLHANDLE henv, hdbc, hstmt;
void ODBCError(
SQLHANDLE henv, SQLHANDLE hdbc,
SQLHANDLE hstmt, SQLHANDLE hdesc,
bool ShowError)
{
SQLRETURN r = 0;
SQLTCHAR szSqlState[6] = {0};
SQLINTEGER fNativeError = 0;
SQLTCHAR szErrorMsg[256] = {0};
SQLSMALLINT cbErrorMsgMax = sizeof(szErrorMsg) - 1;
SQLSMALLINT cbErrorMsg = 0;
TCHAR text[1024] = {0}, title[256] = {0};
if (hdesc != NULL)
r = SQLGetDiagRec(SQL_HANDLE_DESC, hdesc, 1, szSqlState,
&fNativeError, szErrorMsg, cbErrorMsgMax, &cbErrorMsg);
else {
if (hstmt != NULL)
r = SQLGetDiagRec(SQL_HANDLE_STMT, hstmt, 1, szSqlState,
&fNativeError, szErrorMsg, cbErrorMsgMax, &cbErrorMsg);
else {
if (hdbc != NULL)
r = SQLGetDiagRec(SQL_HANDLE_DBC, hdbc, 1, szSqlState,
&fNativeError, szErrorMsg, cbErrorMsgMax, &cbErrorMsg);
else
r = SQLGetDiagRec(SQL_HANDLE_ENV, henv, 1, szSqlState,
&fNativeError, szErrorMsg, cbErrorMsgMax, &cbErrorMsg);
}
}
if (ShowError) {
_sntprintf_s(title, _countof(title), _TRUNCATE, _T("ODBC Error %i"),
fNativeError);
_sntprintf_s(text, _countof(text), _TRUNCATE, _T("[%s] - %s"),
szSqlState, szErrorMsg);
MessageBox(NULL, (LPCTSTR) text, (LPCTSTR) _T("ODBC Error"), MB_OK);
}
}
void connect() {
SQLRETURN r;
r = SQLAllocHandle(SQL_HANDLE_ENV, NULL, &henv);
// This is an ODBC v3 application
r = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER) SQL_OV_ODBC3, 0);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, NULL, NULL, NULL, true);
exit(-1);
}
r = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
// Run in ANSI/implicit transaction mode
r = SQLSetConnectAttr(hdbc, SQL_ATTR_AUTOCOMMIT,
(SQLPOINTER) SQL_AUTOCOMMIT_OFF, SQL_IS_INTEGER);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, NULL, NULL, NULL, true);
exit(-1);
}
TCHAR szConnStrIn[256] = _T("DSN=PrepExecSample");
r = SQLDriverConnect(hdbc, NULL, (SQLTCHAR *) szConnStrIn, SQL_NTS,
NULL, 0, NULL, SQL_DRIVER_NOPROMPT);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, NULL, NULL, true);
exit(-1);
}
}
void setup_ODBC_basics() {
SQLRETURN r;
r = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
}
void OrdEntry(OrdEntryData& order) {
// Simple order entry
SQLRETURN r;
SQLINTEGER ItemNo, ProdCode, Qty;
// Bind parameters for the Order
// 1 - OrdNo input
r = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER,
0, 0, &order.OrdNo, sizeof(SQLINTEGER), NULL);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
// 2 - Custcode input
r = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT,SQL_C_TCHAR, SQL_VARCHAR, 5, 0,
&order.CustCode, sizeof(order.CustCode), NULL);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
// Insert the order
r = SQLExecDirect(hstmt, (SQLTCHAR *) _T("{call OrderInsert(?, ?)}"),SQL_NTS);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
// Flush results & reset hstmt
r = SQLMoreResults(hstmt);
if (r != SQL_NO_DATA) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
r = SQLFreeStmt(hstmt, SQL_RESET_PARAMS);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
// Bind parameters for the Items
// 1 - OrdNo
r = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 10, 0,
&order.OrdNo, sizeof(SQLINTEGER), NULL);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
// 2 - ItemNo
r = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 10, 0,
&ItemNo, sizeof(SQLINTEGER), NULL);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
// 3 - ProdCode
r = SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 10, 0,
&ProdCode, sizeof(SQLINTEGER), NULL);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
// 4 - Qty
r = SQLBindParameter(hstmt, 4, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 10, 0,
&Qty, sizeof(SQLINTEGER), NULL);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
// Prepare to insert items one at a time
r = SQLPrepare(hstmt, (SQLTCHAR *) _T("{call ItemInsert(?, ?, ?, ?)}"),SQL_NTS);
for (unsigned int i = 0; i < order.ItemCount; i++) {
ItemNo = order.ItemNo[i];
ProdCode = order.ProdCode[i];
Qty = order.Qty[i];
r = SQLExecute(hstmt);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
}
// Flush results & reset hstmt
r = SQLMoreResults(hstmt);
if (r != SQL_NO_DATA) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
r = SQLFreeStmt(hstmt, SQL_RESET_PARAMS);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
// Commit the transaction
r = SQLEndTran(SQL_HANDLE_DBC, hdbc, SQL_COMMIT);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
}
void testOrderEntry() {
OrdEntryData order;
order.OrdNo = 1;
_tcscpy_s((TCHAR *) order.CustCode, _countof(order.CustCode), _T("CUST1"));
order.ItemNo[0] = 1;
order.ProdCode[0] = 10;
order.Qty[0] = 1;
order.ItemNo[1] = 2;
order.ProdCode[1] = 20;
order.Qty[1] = 2;
order.ItemNo[2] = 3;
order.ProdCode[2] = 30;
order.Qty[2] = 3;
order.ItemNo[3] = 4;
order.ProdCode[3] = 40;
order.Qty[3] = 4;
order.ItemCount = 4;
OrdEntry(order);
}
int _tmain() {
connect();
setup_ODBC_basics();
testOrderEntry();
}