Stored Procedures With JScript ASP
Audience Level
Beginner and above.
Introduction
When using Stored Procedures (SPs) under Classic ASP it's surprising how little documentation there is for JScript ASP compared to VBScript. What follows is an example of an overly verbose processing page in JScript ASP that uses a stored procedure through ADO with an ADO transaction to insert some article text into a database. The transactional stored procedure is also included for reference.
The examples on this page have been re-written, though I don't currently have the time to test whether they actually work or not — so consider them to be something akin to pseudo code for the timebeing.
Source Code
<%@Language="JScript" @CodePage="65001" EnableSessionState=false%>
<!--#includevirtual="/includes/library.asp"-->
<%
/*
filename = process.asp
description = Contrived example of carefully saving an article into a database
author = Andrew Urquhart
lastUpdate = "31 January 2007 23:07 UTC";
*/
Response.Buffer = true;
// NOTE: See the "Script Library" page for definitions of functions such as RF(), RFI(), isNumber(), getDbConnection(), doRollback(), enumerateADOErrors(), enumerateCommandParameters(), etc that are used here but not declared.
// GET POSTED DATA
// ===============
var objPostData = {
pageId: RFI("pageid")
,authorId: RFI("authorId")
,title: RF("title")
,content: RF("content")
}
// SOME BASIC DATA VALIDATION AND ERROR REPORTING BACK TO THE CALLING PAGE
// =======================================================================
/* You should do more thorough checking than I've done and preferably return any submitted POST data back to the starting page so the user doesn't have to re-enter their information all over again. */
if (!isNumber(objPostData.pageId)) {
Response.Redirect("/createarticle/?error=1");
}
if (!isNumber(objPostData.authorId)) {
Response.Redirect("/createarticle/?error=2");
}
if (!objPostData.title) {
Response.Redirect("/createarticle/?error=3");
}
else if (objPostData.title.length > 255) {
Response.Redirect("/createarticle/?error=4");
}
if (!objPostData.content) {
Response.Redirect("/createarticle/?error=5");
}
// GET A DATABASE CONNECTION AND INVOKE OUR 'SAVE ARTICLE' ROUTINE
// ===============================================================
var objAdoConn;
try {
// Get an ADO database connection object - getDbConnection() is pseudo code in this example
objAdoConn = getDbConnection(adModeReadWrite, Application("DBCONNECTION"));
// Save the article and redirect to the edit page, or throw exception
saveArticle(objPostData, objAdoConn);
}
catch (err) {
// Save the critical error in the server log, but
// you should handle the error more gracefully than I have here
Response.AppendToLog("Exception: number=" + err.number + " description=" + err.description);
Response.Redirect("/createarticle/?error=6");
}
finally {
// Always close our connection objects regardless of what happpens
if (objAdoConn && objAdoConn.State != adStateClosed) {
objAdoConn.Close();
}
}
/*
Function: saveArticle()
Description: Insert a new article into the database
Returns: n/a (either throws an exception or issues a HTTP 302 redirect to a success display page)
History:
20060306 2033GMT v1 Andrew Urquhart Created
*/
function saveArticle(objPostData, objAdoConn) {
try {
if (typeof objPostData != "object") {
throw new Error(1, "Required parameter \"objPostData\" was not defined");
}
if (!objAdoConn) {
throw new Error(2, "Required parameter \"objAdoConn\" was not defined");
}
// Add an ADO-level transaction wrapper to the connection object
// that oversees our stored procedure
objAdoConn.BeginTrans();
try {
// doInitialise the SP invocation
// ============================
var objCmd = new ActiveXObject("ADODB.Command");
objCmd.CommandText = "usp_insert_article"; // Stored procedure name to execute
objCmd.NamedParameters = true;
objCmd.CommandType = adCmdStoredProc;
objCmd.ActiveConnection = objAdoConn;
// Add the required parameters to our stored procedure invocation
// ==============================================================
// NOTE: Any 'adParamReturnValue' parameters *MUST* be first in the list
objCmd.Parameters.Append(objCmd.CreateParameter("@returnCode", adInteger, adParamReturnValue));
objCmd.Parameters.Append(objCmd.CreateParameter("@pageId", adInteger, adParamInput, 6, objPostData.pageId));
objCmd.Parameters.Append(objCmd.CreateParameter("@authorId", adInteger, adParamInput, 6, objPostData.authorId));
objCmd.Parameters.Append(objCmd.CreateParameter("@datenow", adBigInt, adParamInput, 8, new Date().valueOf()));
objCmd.Parameters.Append(objCmd.CreateParameter("@title", adVarWChar, adParamInput, 255,objPostData.title));
objCmd.Parameters.Append(objCmd.CreateParameter("@articleId", adBigInt, adParamOutput));
// NText, as well as Text (adLongVarChar), need to be appended to parameters as chunks:
objCmd.Parameters.Append(objCmd.CreateParameter("@content", adLongVarWChar, adParamInput, (objPostData.content ? objPostData.content.length : 1)));
objCmd.Parameters.Item("@content").Attributes = adFldLong;
objCmd.Parameters.Item("@content").AppendChunk(objPostData.content);
// Finally, parse and execute the stored procedure in an ADO transaction without returning a recordset
objCmd.Execute(adExecuteNoRecords);
if (objAdoConn.Errors.Count > 0) {
throw new Error(3, objCmd.CommandText + " failed with ADO connection errors");
}
// Examine return code for success status
var intReturnCode = objCmd.Parameters("@returnCode").Value;
switch (intReturnCode) {
case 1 : {
// Successful insert performed, get new intArticleId and continue to next page
var intArticleId = objCmd.Parameters("@articleId").Value;
objAdoConn.CommitTrans(); // Commit the ADO operations since everything worked OK
// Redirect away to the next page, e.g. the page to display the saved article
Response.Redirect("/editarticle/?articleid=" + Server.URLEncode(intArticleId));
}
break;
case 1000 : {
// Handle invalid author error
Response.Redirect("/createarticle/?error=7");
}
break;
case 1001 : {
// Handle missing Page error
Response.Redirect("/createarticle/?error=8");
}
break;
case 1002 : {
// Handle duplicate article title error
Response.Redirect("/createarticle/?error=9");
}
break;
default : {
throw new Error(4, objCmd.CommandText + " failed with unexpected return code: \"" + intReturnCode + "\"");
}
}
}
catch (err) {
var strErrMsg = "Stored procedure execution failed with error #: " + err.number + ", description: " + err.description + ". ";
// See if our database connection object is still valid, if so
// inspect it for errors and rollback the ADO-level transaction
if (objAdoConn && objAdoConn.State != adStateClosed) {
doRollback(objAdoConn);
strErrMsg += "\r\n" + enumerateADOErrors(objAdoConn);
strErrMsg += "\r\n" + enumerateCommandParameters(objCmd);
}
throw new Error(err.number, strErrMsg); // Propagate error on up the call-chain
}
}
catch (err) {
propError(err, arguments);
}
}
%>
<!--#includevirtual="/includes/library.asp"-->
<%
/*
filename = process.asp
description = Contrived example of carefully saving an article into a database
author = Andrew Urquhart
lastUpdate = "31 January 2007 23:07 UTC";
*/
Response.Buffer = true;
// NOTE: See the "Script Library" page for definitions of functions such as RF(), RFI(), isNumber(), getDbConnection(), doRollback(), enumerateADOErrors(), enumerateCommandParameters(), etc that are used here but not declared.
// GET POSTED DATA
// ===============
var objPostData = {
pageId: RFI("pageid")
,authorId: RFI("authorId")
,title: RF("title")
,content: RF("content")
}
// SOME BASIC DATA VALIDATION AND ERROR REPORTING BACK TO THE CALLING PAGE
// =======================================================================
/* You should do more thorough checking than I've done and preferably return any submitted POST data back to the starting page so the user doesn't have to re-enter their information all over again. */
if (!isNumber(objPostData.pageId)) {
Response.Redirect("/createarticle/?error=1");
}
if (!isNumber(objPostData.authorId)) {
Response.Redirect("/createarticle/?error=2");
}
if (!objPostData.title) {
Response.Redirect("/createarticle/?error=3");
}
else if (objPostData.title.length > 255) {
Response.Redirect("/createarticle/?error=4");
}
if (!objPostData.content) {
Response.Redirect("/createarticle/?error=5");
}
// GET A DATABASE CONNECTION AND INVOKE OUR 'SAVE ARTICLE' ROUTINE
// ===============================================================
var objAdoConn;
try {
// Get an ADO database connection object - getDbConnection() is pseudo code in this example
objAdoConn = getDbConnection(adModeReadWrite, Application("DBCONNECTION"));
// Save the article and redirect to the edit page, or throw exception
saveArticle(objPostData, objAdoConn);
}
catch (err) {
// Save the critical error in the server log, but
// you should handle the error more gracefully than I have here
Response.AppendToLog("Exception: number=" + err.number + " description=" + err.description);
Response.Redirect("/createarticle/?error=6");
}
finally {
// Always close our connection objects regardless of what happpens
if (objAdoConn && objAdoConn.State != adStateClosed) {
objAdoConn.Close();
}
}
/*
Function: saveArticle()
Description: Insert a new article into the database
Returns: n/a (either throws an exception or issues a HTTP 302 redirect to a success display page)
History:
20060306 2033GMT v1 Andrew Urquhart Created
*/
function saveArticle(objPostData, objAdoConn) {
try {
if (typeof objPostData != "object") {
throw new Error(1, "Required parameter \"objPostData\" was not defined");
}
if (!objAdoConn) {
throw new Error(2, "Required parameter \"objAdoConn\" was not defined");
}
// Add an ADO-level transaction wrapper to the connection object
// that oversees our stored procedure
objAdoConn.BeginTrans();
try {
// doInitialise the SP invocation
// ============================
var objCmd = new ActiveXObject("ADODB.Command");
objCmd.CommandText = "usp_insert_article"; // Stored procedure name to execute
objCmd.NamedParameters = true;
objCmd.CommandType = adCmdStoredProc;
objCmd.ActiveConnection = objAdoConn;
// Add the required parameters to our stored procedure invocation
// ==============================================================
// NOTE: Any 'adParamReturnValue' parameters *MUST* be first in the list
objCmd.Parameters.Append(objCmd.CreateParameter("@returnCode", adInteger, adParamReturnValue));
objCmd.Parameters.Append(objCmd.CreateParameter("@pageId", adInteger, adParamInput, 6, objPostData.pageId));
objCmd.Parameters.Append(objCmd.CreateParameter("@authorId", adInteger, adParamInput, 6, objPostData.authorId));
objCmd.Parameters.Append(objCmd.CreateParameter("@datenow", adBigInt, adParamInput, 8, new Date().valueOf()));
objCmd.Parameters.Append(objCmd.CreateParameter("@title", adVarWChar, adParamInput, 255,objPostData.title));
objCmd.Parameters.Append(objCmd.CreateParameter("@articleId", adBigInt, adParamOutput));
// NText, as well as Text (adLongVarChar), need to be appended to parameters as chunks:
objCmd.Parameters.Append(objCmd.CreateParameter("@content", adLongVarWChar, adParamInput, (objPostData.content ? objPostData.content.length : 1)));
objCmd.Parameters.Item("@content").Attributes = adFldLong;
objCmd.Parameters.Item("@content").AppendChunk(objPostData.content);
// Finally, parse and execute the stored procedure in an ADO transaction without returning a recordset
objCmd.Execute(adExecuteNoRecords);
if (objAdoConn.Errors.Count > 0) {
throw new Error(3, objCmd.CommandText + " failed with ADO connection errors");
}
// Examine return code for success status
var intReturnCode = objCmd.Parameters("@returnCode").Value;
switch (intReturnCode) {
case 1 : {
// Successful insert performed, get new intArticleId and continue to next page
var intArticleId = objCmd.Parameters("@articleId").Value;
objAdoConn.CommitTrans(); // Commit the ADO operations since everything worked OK
// Redirect away to the next page, e.g. the page to display the saved article
Response.Redirect("/editarticle/?articleid=" + Server.URLEncode(intArticleId));
}
break;
case 1000 : {
// Handle invalid author error
Response.Redirect("/createarticle/?error=7");
}
break;
case 1001 : {
// Handle missing Page error
Response.Redirect("/createarticle/?error=8");
}
break;
case 1002 : {
// Handle duplicate article title error
Response.Redirect("/createarticle/?error=9");
}
break;
default : {
throw new Error(4, objCmd.CommandText + " failed with unexpected return code: \"" + intReturnCode + "\"");
}
}
}
catch (err) {
var strErrMsg = "Stored procedure execution failed with error #: " + err.number + ", description: " + err.description + ". ";
// See if our database connection object is still valid, if so
// inspect it for errors and rollback the ADO-level transaction
if (objAdoConn && objAdoConn.State != adStateClosed) {
doRollback(objAdoConn);
strErrMsg += "\r\n" + enumerateADOErrors(objAdoConn);
strErrMsg += "\r\n" + enumerateCommandParameters(objCmd);
}
throw new Error(err.number, strErrMsg); // Propagate error on up the call-chain
}
}
catch (err) {
propError(err, arguments);
}
}
%>
Download
Download the JScript ASP source directly.
Notes
This example shows how input and output parameters in conjunction with a stored procedure return code can be implemented under JScript ASP. The example uses an output parameter to return the new ArticleId to the ASP page in the event of receiving a nominal stored procedure return code and zero-length connection object error count. The example also shows the careful handling of transactions at the ADO level. The ADO level transaction should cause the stored procedure to be enrolled in a transaction too, although the T-SQL stored procedure included for reference also includes its own transaction declaration for example completeness.
The stored procedure in this example does not return a recordset. If you do use a stored procedure that returns
a recordset in conjunction with output parameters and/or return codes, then bear in mind that the recordset
must be fully consumed (read), or the “Close()” method invoked on the recordset,
before the output parameter(s) or return code can be read.
If you encounter syntax errors with the ADO constants (i.e. “adCmdStoredProc”) then
you need to add the ADODB Metadata Type library declaration to the top of your “global.asa”, e.g.:
<!--METADATA NAME="Microsoft ActiveX Data Objects 2.5 Library" TYPE="TypeLib" UUID="{00000205-0000-0010-8000-00AA006D2EA4}"-->
Note that I tend to use Big Integers to store date values akin to Unix timestamps rather than using the Date data types. This is because I prefer to record raw UTC time and not have the database server, or any interface inbetween, meddle with daylight saving and timezone conversions. The parameter sizes for Integers is not required.
Example Stored Procedure
The following T-SQL stored procedure is designed to be paired with the example JScript ASP code above for reference purposes.
USE [myDatabaseName];
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = object_id(N'[dbo].[usp_insert_article]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[usp_insert_article]; -- Just more convenient than CREATE and ALTER
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
/************************************************\
SP: usp_insert_article
Description: Inserts an article
HISTORY:
Date: Author: Description:
2006-03-06 21:14 GMT Andrew Urquhart Created
\************************************************/
CREATE PROCEDURE usp_insert_article
@articleId BigInt = NULL OUTPUT,
@pageId Integer,
@authorId Integer,
@datenow BigInt,
@title NVarchar (255),
@content NText
AS
SET NOCOUNT ON;
SET XACT_ABORT ON;
DECLARE @articleId Integer;
DECLARE @_ERROR Integer;
DECLARE @procName SysName;
-- DATA VALIDATION
-- ===============
IF (@pageId IS NULL)
BEGIN
SET @procName = OBJECT_NAME(@@PROCID);
RAISERROR ('%s() failed: Expected parameter @pageId was null', 16, @procName);
RETURN 2;
END
IF (@authorId IS NULL)
BEGIN
SET @procName = OBJECT_NAME(@@PROCID);
RAISERROR ('%s() failed: Expected parameter @authorId was null', 16, @procName);
RETURN 3;
END
IF (@datenow IS NULL)
BEGIN
SET @procName = OBJECT_NAME(@@PROCID);
RAISERROR ('%s() failed: Expected parameter @datenow was null', 16, @procName);
RETURN 4;
END
IF (@title IS NULL)
BEGIN
SET @procName = OBJECT_NAME(@@PROCID);
RAISERROR ('%s() failed: Expected parameter @title was null', 16, @procName);
RETURN 5;
END
-- ===============
-- DATA VALIDATION
-- SAVE THE ARTICLE
-- ================
BEGIN TRANSACTION InsertArticle;
-- WITHIN THE TRANSACTION, DO SOME REFERENTIAL DATA CHECKS
-- Check if valid author
IF NOT EXISTS (SELECT TOP 1 [A].[authorId] FROM dbo.Author [A] WHERE [A].[authorId] = @authorId)
BEGIN
-- OK, don't throw a raiserror, but return a special return code so that the application can handle this proble'
ROLLBACK TRANSACTION;
RETURN 1000; -- Author does not exist
END
-- Check if page exists that the article is to be added to
IF NOT EXISTS (SELECT TOP 1 [P].[pageId] FROM dbo.[Page] [P] WHERE [P].[pageId] = @pageId)
BEGIN
ROLLBACK TRANSACTION;
RETURN 1001; -- Page does not exist
END
-- Check if an article already exists with this title and exit if it does
IF EXISTS(SELECT TOP 1 [A].[articleId] FROM dbo.[Article] AS [A] WHERE [A].[title] LIKE @title)
BEGIN
ROLLBACK TRANSACTION;
RETURN 1002; -- Article already exists with this title
END
-- Create the Article record first as we need it's primary key I'
INSERT INTO dbo.[Article] (
pageId,
authorId,
dateCreated,
title,
content
) VALUES (
@pageId,
@authorId,
@datenow,
@title,
@content
);
SELECT @_ERROR = @@ERROR, @articleId = SCOPE_IDENTITY();
IF (@_ERROR != 0) GOTO ERROR;
-- Add to Author history
INSERT INTO dbo.[AuthorHistory] (
authorId,
articleId,
dateCreated
) VALUES (
@authorId,
@articleId,
@datenow
);
SET @_ERROR = @@ERROR;
IF (@_ERROR != 0) GOTO ERROR;
COMMIT TRANSACTION
-- ================
-- SAVE THE ARTICLE
RETURN 1; -- Success; Article added. Use 1 and not 0 as success return code since returning 0 wouldn't allow us to discover subtle execution branch error'
ERROR:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION;
SET @procName = OBJECT_NAME(@@PROCID);
RAISERROR ('%s() failed with error code %d', 16, @procName, @_ERROR);
RETURN @_ERROR;
GO
-- Ensure author is DBO, not current user
IF (SELECT 'notDBO' = CASE USER WHEN 'dbo' THEN 0 ELSE 1 END) = 1
EXEC sp_changeobjectowner @objname='usp_insert_article', @newowner='dbo';
GO
-- Grant permission to execute to various database users
GRANT EXECUTE ON usp_insert_article TO dbo, app_editor, app_manage;
GO
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = object_id(N'[dbo].[usp_insert_article]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[usp_insert_article]; -- Just more convenient than CREATE and ALTER
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
/************************************************\
SP: usp_insert_article
Description: Inserts an article
HISTORY:
Date: Author: Description:
2006-03-06 21:14 GMT Andrew Urquhart Created
\************************************************/
CREATE PROCEDURE usp_insert_article
@articleId BigInt = NULL OUTPUT,
@pageId Integer,
@authorId Integer,
@datenow BigInt,
@title NVarchar (255),
@content NText
AS
SET NOCOUNT ON;
SET XACT_ABORT ON;
DECLARE @articleId Integer;
DECLARE @_ERROR Integer;
DECLARE @procName SysName;
-- DATA VALIDATION
-- ===============
IF (@pageId IS NULL)
BEGIN
SET @procName = OBJECT_NAME(@@PROCID);
RAISERROR ('%s() failed: Expected parameter @pageId was null', 16, @procName);
RETURN 2;
END
IF (@authorId IS NULL)
BEGIN
SET @procName = OBJECT_NAME(@@PROCID);
RAISERROR ('%s() failed: Expected parameter @authorId was null', 16, @procName);
RETURN 3;
END
IF (@datenow IS NULL)
BEGIN
SET @procName = OBJECT_NAME(@@PROCID);
RAISERROR ('%s() failed: Expected parameter @datenow was null', 16, @procName);
RETURN 4;
END
IF (@title IS NULL)
BEGIN
SET @procName = OBJECT_NAME(@@PROCID);
RAISERROR ('%s() failed: Expected parameter @title was null', 16, @procName);
RETURN 5;
END
-- ===============
-- DATA VALIDATION
-- SAVE THE ARTICLE
-- ================
BEGIN TRANSACTION InsertArticle;
-- WITHIN THE TRANSACTION, DO SOME REFERENTIAL DATA CHECKS
-- Check if valid author
IF NOT EXISTS (SELECT TOP 1 [A].[authorId] FROM dbo.Author [A] WHERE [A].[authorId] = @authorId)
BEGIN
-- OK, don't throw a raiserror, but return a special return code so that the application can handle this proble'
ROLLBACK TRANSACTION;
RETURN 1000; -- Author does not exist
END
-- Check if page exists that the article is to be added to
IF NOT EXISTS (SELECT TOP 1 [P].[pageId] FROM dbo.[Page] [P] WHERE [P].[pageId] = @pageId)
BEGIN
ROLLBACK TRANSACTION;
RETURN 1001; -- Page does not exist
END
-- Check if an article already exists with this title and exit if it does
IF EXISTS(SELECT TOP 1 [A].[articleId] FROM dbo.[Article] AS [A] WHERE [A].[title] LIKE @title)
BEGIN
ROLLBACK TRANSACTION;
RETURN 1002; -- Article already exists with this title
END
-- Create the Article record first as we need it's primary key I'
INSERT INTO dbo.[Article] (
pageId,
authorId,
dateCreated,
title,
content
) VALUES (
@pageId,
@authorId,
@datenow,
@title,
@content
);
SELECT @_ERROR = @@ERROR, @articleId = SCOPE_IDENTITY();
IF (@_ERROR != 0) GOTO ERROR;
-- Add to Author history
INSERT INTO dbo.[AuthorHistory] (
authorId,
articleId,
dateCreated
) VALUES (
@authorId,
@articleId,
@datenow
);
SET @_ERROR = @@ERROR;
IF (@_ERROR != 0) GOTO ERROR;
COMMIT TRANSACTION
-- ================
-- SAVE THE ARTICLE
RETURN 1; -- Success; Article added. Use 1 and not 0 as success return code since returning 0 wouldn't allow us to discover subtle execution branch error'
ERROR:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION;
SET @procName = OBJECT_NAME(@@PROCID);
RAISERROR ('%s() failed with error code %d', 16, @procName, @_ERROR);
RETURN @_ERROR;
GO
-- Ensure author is DBO, not current user
IF (SELECT 'notDBO' = CASE USER WHEN 'dbo' THEN 0 ELSE 1 END) = 1
EXEC sp_changeobjectowner @objname='usp_insert_article', @newowner='dbo';
GO
-- Grant permission to execute to various database users
GRANT EXECUTE ON usp_insert_article TO dbo, app_editor, app_manage;
GO
Download
Download the SQL source directly.