|
 |
asp_databases thread: return values in stored procs on updates and inserts
Message #1 by dont worry <aspmailbox@y...> on Thu, 12 Oct 2000 13:14:53 -0700 (PDT)
|
|
I'm trying to learn how to get return values from ado
command and inside of stored procs so that in my
scripting I can say.
if returnvalue = 1 then
script completed
elseif returnvalue=0 then
script did not complete
end if
****************************************
code for command and store proc below.
(update works, just cant get the return value.)
Thank you
****************************************
Set p = cm.Parameters
cm.ActiveConnection = dbConn
cm.CommandText = "sp_valid"
cm.CommandType = adCmdStoredProc
p.Append cm.CreateParameter("retval",
adInteger,adParamReturnValue)
p.Append cm.CreateParameter("@user_email", adVarChar,
adParamInput,80)
p.Append cm.CreateParameter("@company_id", adInteger,
adParamInput)
cm("@user_email")=user_email
cm("@company_id")=company_id
Set cn = cm.Execute
Response.Write retval
Response.Write cm("retval")
++++++++++++++++++++++++++++++++++++++++++++++++
CREATE PROCEDURE sp_valid
@user_email varchar(80) = NULL,
@company_id int = NULL
AS
UPDATE USERS SET ACTIVE = 1,
ActiveDate=GetDate()
WHERE Email = @user_email AND ACTIVE = 0
AND
CompanyId =@company_id
Return 1
GRANT EXECUTE ON dbo.sp_valid TO PUBLIC AS dbo
Message #2 by Imar Spaanjaars <Imar@S...> on Thu, 12 Oct 2000 22:42:22 +0200
|
|
You have to retrieve the parameter from the parameter collection of the
command object.
There is no need to store the results in a recordset (cn??) since all you
are interested in, is the return value.
So this
Set cn = cm.Execute
is not necessary.
Do something like this:
cmdUpdate.Execute ' side tip: try to name your objects and variables so
they describe what they. CM can be anything
' Now get the returnvalue
iReturnValue = cmdUpdate.Parameters(0) or
cmdUpdate.Parameters("retval")
The difference here is that you "ask" the parameters collection for the value.
You tried to get it in a recordset kind of way, I guess.
HtH
Imar
At 01:14 PM 10/12/2000 -0700, you wrote:
>I'm trying to learn how to get return values from ado
>command and inside of stored procs so that in my
>scripting I can say.
>if returnvalue = 1 then
> script completed
>elseif returnvalue=0 then
> script did not complete
>end if
>****************************************
>code for command and store proc below.
>(update works, just cant get the return value.)
>Thank you
>****************************************
>Set p = cm.Parameters
>cm.ActiveConnection = dbConn
>cm.CommandText = "sp_valid"
>cm.CommandType = adCmdStoredProc
>p.Append cm.CreateParameter("retval",
>adInteger,adParamReturnValue)
>p.Append cm.CreateParameter("@user_email", adVarChar,
>adParamInput,80)
>p.Append cm.CreateParameter("@company_id", adInteger,
>adParamInput)
>cm("@user_email")=user_email
>cm("@company_id")=company_id
>Set cn = cm.Execute
>
>Response.Write retval
>Response.Write cm("retval")
>++++++++++++++++++++++++++++++++++++++++++++++++
>
>CREATE PROCEDURE sp_valid
>@user_email varchar(80) = NULL,
>@company_id int = NULL
>AS
>UPDATE USERS SET ACTIVE = 1,
>ActiveDate=GetDate()
> WHERE Email = @user_email AND ACTIVE = 0
>AND
>CompanyId =@company_id
>Return 1
>GRANT EXECUTE ON dbo.sp_valid TO PUBLIC AS dbo
>
>
>
>---
>FREE WEB DEVELOPMENT CODE, CONTENT, AND INSIGHTS
>IN YOUR INBOX!
>Get the latest and best HTML, XML, and JavaScript tips, tools, and
>developments from the experts. Sign up for one or more of EarthWeb?s
>FREE IT newsletters at http://www.earthweb.com today!
>---
Message #3 by dont worry <aspmailbox@y...> on Thu, 12 Oct 2000 14:27:31 -0700 (PDT)
|
|
Hey,
That helped , but can you double check the stored
proc, because the sql says where Active=0, so if it
runs once that is set to Active=1, and the sql should
not work again. Problem is that it returns 1 no
matter what happens?
thank you
--- Imar Spaanjaars <Imar@S...> wrote:
> You have to retrieve the parameter from the
> parameter collection of the
> command object.
> There is no need to store the results in a recordset
> (cn??) since all you
> are interested in, is the return value.
>
> So this
> Set cn = cm.Execute
> is not necessary.
>
> Do something like this:
>
> cmdUpdate.Execute ' side tip: try to name your
> objects and variables so
> they describe what they. CM can be anything
> ' Now get the returnvalue
> iReturnValue = cmdUpdate.Parameters(0) or
> cmdUpdate.Parameters("retval")
>
> The difference here is that you "ask" the parameters
> collection for the value.
> You tried to get it in a recordset kind of way, I
> guess.
>
> HtH
>
> Imar
>
>
>
> At 01:14 PM 10/12/2000 -0700, you wrote:
> >I'm trying to learn how to get return values from
> ado
> >command and inside of stored procs so that in my
> >scripting I can say.
> >if returnvalue = 1 then
> > script completed
> >elseif returnvalue=0 then
> > script did not complete
> >end if
> >****************************************
> >code for command and store proc below.
> >(update works, just cant get the return value.)
> >Thank you
> >****************************************
> >Set p = cm.Parameters
> >cm.ActiveConnection = dbConn
> >cm.CommandText = "sp_valid"
> >cm.CommandType = adCmdStoredProc
> >p.Append cm.CreateParameter("retval",
> >adInteger,adParamReturnValue)
> >p.Append cm.CreateParameter("@user_email",
> adVarChar,
> >adParamInput,80)
> >p.Append cm.CreateParameter("@company_id",
> adInteger,
> >adParamInput)
> >cm("@user_email")=user_email
> >cm("@company_id")=company_id
> >Set cn = cm.Execute
> >
> >Response.Write retval
> >Response.Write cm("retval")
> >++++++++++++++++++++++++++++++++++++++++++++++++
> >
> >CREATE PROCEDURE sp_valid
> >@user_email varchar(80) = NULL,
> >@company_id int = NULL
> >AS
> >UPDATE USERS SET ACTIVE = 1,
> >ActiveDate=GetDate()
> > WHERE Email = @user_email AND ACTIVE = 0
> >AND
> >CompanyId =@company_id
> >Return 1
> >GRANT EXECUTE ON dbo.sp_valid TO PUBLIC AS dbo
> >
> >
> >
Message #4 by Imar Spaanjaars <Imar@S...> on Fri, 13 Oct 2000 09:30:13 +0200
|
|
Hi there,
It returns 1 because you tell it to return 1. This is what happens:
CREATE PROCEDURE sp_valid
@user_email varchar(80) = NULL,
@company_id int = NULL
AS
UPDATE USERS SET
ACTIVE = 1,
ActiveDate=GetDate()
WHERE
Email = @user_email
AND
ACTIVE = 0
AND
CompanyId =@company_id
Return 1
GRANT EXECUTE ON dbo.sp_valid TO PUBLIC AS dbo
Basically what happens here is that every time the proc is called the user
with the supplied mail address and with an "active of 0" are set to 1 for
the company you supply.
In the end, you say: return 1
So your return value will always be 1, no matter what happens, because this
script is always run.
I am not sure when you expect to get 0 back. What exactly do you mean by
"script did not complete"??
Imar
At 02:27 PM 10/12/2000 -0700, you wrote:
>Hey,
>That helped , but can you double check the stored
>proc, because the sql says where Active=0, so if it
>runs once that is set to Active=1, and the sql should
>not work again. Problem is that it returns 1 no
>matter what happens?
>thank you
>
>
>--- Imar Spaanjaars <Imar@S...> wrote:
> > You have to retrieve the parameter from the
> > parameter collection of the
> > command object.
> > There is no need to store the results in a recordset
> > (cn??) since all you
> > are interested in, is the return value.
> >
> > So this
> > Set cn = cm.Execute
> > is not necessary.
> >
> > Do something like this:
> >
> > cmdUpdate.Execute ' side tip: try to name your
> > objects and variables so
> > they describe what they. CM can be anything
> > ' Now get the returnvalue
> > iReturnValue = cmdUpdate.Parameters(0) or
> > cmdUpdate.Parameters("retval")
> >
> > The difference here is that you "ask" the parameters
> > collection for the value.
> > You tried to get it in a recordset kind of way, I
> > guess.
> >
> > HtH
> >
> > Imar
> >
> >
> >
Message #5 by dont worry <aspmailbox@y...> on Fri, 13 Oct 2000 07:54:24 -0700 (PDT)
|
|
Imar,
1) I want a value to be returned to me to know if it
was updated or not. This way I know in the scripting
that it was updated and I can do....
++++++++++++++++++
if return value indicates updated then
response.write "Updated okay"
else
response.write "Did not update, you may be already
active or you may have bad email"
++++++++++++++++++
2) (basically, I used to use the below in scripting
and dynamically built sql strings...)
++++++++++++++
cn.begintrans, numa
if numa =1 then
cn.committrans
response.write "update"
else
cn.rollback
++++++++++++++
BUT I have since move to stored procs and I'm trying
to learn how to accomplish the same thing.
3) Does stored procs use begintrans, commit, rollback?
4) Someone told me it does it automatically?
5) If not auto then should I use it and how?
I hope this clears up what I'm trying to do, if not
just email me.
Thank you for you time and knowledge,
l
--- Imar Spaanjaars <Imar@S...> wrote:
> Hi there,
>
> It returns 1 because you tell it to return 1. This
> is what happens:
>
> CREATE PROCEDURE sp_valid
> @user_email varchar(80) = NULL,
> @company_id int = NULL
> AS
> UPDATE USERS SET
> ACTIVE = 1,
> ActiveDate=GetDate()
> WHERE
> Email = @user_email
> AND
> ACTIVE = 0
> AND
> CompanyId =@company_id
> Return 1
> GRANT EXECUTE ON dbo.sp_valid TO PUBLIC AS dbo
>
> Basically what happens here is that every time the
> proc is called the user
> with the supplied mail address and with an "active
> of 0" are set to 1 for
> the company you supply.
>
> In the end, you say: return 1
> So your return value will always be 1, no matter
> what happens, because this
> script is always run.
> I am not sure when you expect to get 0 back. What
> exactly do you mean by
> "script did not complete"??
>
> Imar
>
>
> At 02:27 PM 10/12/2000 -0700, you wrote:
> >Hey,
> >That helped , but can you double check the stored
> >proc, because the sql says where Active=0, so if it
> >runs once that is set to Active=1, and the sql
> should
> >not work again. Problem is that it returns 1 no
> >matter what happens?
> >thank you
> >
> >
> >--- Imar Spaanjaars <Imar@S...> wrote:
> > > You have to retrieve the parameter from the
> > > parameter collection of the
> > > command object.
> > > There is no need to store the results in a
> recordset
> > > (cn??) since all you
> > > are interested in, is the return value.
> > >
> > > So this
> > > Set cn = cm.Execute
> > > is not necessary.
> > >
> > > Do something like this:
> > >
> > > cmdUpdate.Execute ' side tip: try to name your
> > > objects and variables so
> > > they describe what they. CM can be anything
> > > ' Now get the returnvalue
> > > iReturnValue = cmdUpdate.Parameters(0) or
> > > cmdUpdate.Parameters("retval")
> > >
> > > The difference here is that you "ask" the
> parameters
> > > collection for the value.
> > > You tried to get it in a recordset kind of way,
> I
> > > guess.
> > >
> > > HtH
> > >
> > > Imar
> > >
> > >
> > >
>
>
>
> ---
> FREE WEB DEVELOPMENT CODE, CONTENT, AND INSIGHTS
> IN YOUR INBOX!
> Get the latest and best HTML, XML, and JavaScript
> tips, tools, and
> developments from the experts. Sign up for one or
> more of EarthWeb?s
> FREE IT newsletters at http://www.earthweb.com
> today!
Message #6 by Imar Spaanjaars <Imar@S...> on Fri, 13 Oct 2000 19:44:37 +0200
|
|
Hi there,
1. You can return almost any value form a sproc based on your own your
criteria.
The problem you are facing, is that updating a value that does not exist
does not generate an error. That is, as long as the datatype you pass in as
a parameter is correct, and the rest of your SQL statement is valid of course.
For instance: UPDATE USER SET ACTIVE = 1 WHERE userID =
'DoesNotExist@n...' and ACTIVE = 0 will complete successfully. No
error is generated, even if DoesNotExist@n... does not exist as an ID
What you could do is query for the user first, before you update
Something along these lines:
Dim sLocalConnectionString
Dim rsCheckUser
Dim sSQL
sLocalConnectionString = Application("myConnectionString")
sSQL = "SELECT COUNT(userID) FROM tblUser WHERE userID = '" & sEmailAddress
& "' AND userActive = 0
Set rsCheckUser = Server.CreateObject("ADODB.Recordset")
rsCheckUser.Open sSQL, sLocalConnectionString, adOpenForwardOnly,
adLockReadOnly, adCmdText
if CInt(rsCheckItems(0)) > 0 then' user is found, and active = 0 so update
the user.
Dim objUpdateUser ' as ADODB.Command
' Create parameteres here
' bla bla bla etc etc
objUpdateUser.Execute
' If you still need a returnvalue, get it here, before setting
objUpdateUser to nothing
Set objUpdateUser = Nothing
else
Response.Write("User ID not found, or already active")
end if
rsCheckUser .Close
Set rsCheckUser = Nothing
(Note: you could rewrite the example above, creating a sproc that does the
counting, and execute it through a command object. The number of records
could be the returnvalue of the sproc)
2. To answer your other questions: TMK, SQL Server does not start a
transaction automatically in a Stored Procedure. However, it will
participate in one when the calling page starts a new transaction (by
setting the TRANSACTION=[transactionType] directive at the beginning of the
page).
To have SQL start a new transaction, do it yourself by using BEGIN TRAN
[transactionName].
For example, consider the following stored procedures:
<SQL PROCEDURES>
Create Procedure spTest1
As
DECLARE @returnFromOtherSproc int
BEGIN TRAN myTransactionName
/* Do an insert here. We will undo this one when we roll back*/
INSERT INTO tblTest1 (Description) VALUES('Hello')
/* Now call another sproc */
EXEC @returnFromOtherSproc = spTest2
if @returnFromOtherSproc < 10 /* Just made this up. Should be
based on business logic of course */
begin
rollback tran
return 0 /* arbitrary value. Can be anything you want */
end
else
begin
COMMIT TRAN
return 1 /* arbitrary value. Can be anything you want */
end
spTest2 is a simple sproc that inserts a simple value in a table, and is
shown here for completeness only.
Create Procedure spTest2
As
INSERT INTO tblTest2 (Description) VALUES('Hello')
return @@identity
</sql procedures>
<ASP CODE>
Dim sLocalConnectionString
Dim cmdUpdate
Dim iNewID
sLocalConnectionString = Application("myConnectionString")
Set cmdUpdate= Server.CreateObject("ADODB.Command")
cmdUpdate.ActiveConnection = sLocalConnectionString
cmdUpdate.CommandType = adCmdStoredProc
cmdUpdate.CommandText = "spTest1"
cmdUpdate.Parameters.Append (objInsertOrUpdate.CreateParameter("Return",
adInteger, adParamReturnValue))
cmdUpdate.Execute
iNewID = cmdUpdate.Parameters(0) ' Return parameter . Holds 1 on
success, 0 on failure
Set cmdUpdate = Nothing
</asp code>
What we do here is the following:
From an ADO Command object spTest1 is called. In this sproc a value is
inserted in tblTest1. If the outcome of the second operation is not what we
want, we undo this insert later.
Then we call spTest2, our second operation. This simply inserts a value in
a second table, and returns the new ID. If the ID is smaller then 10, we
don't like the result, and rollback the current transaction. We return a
value of 0 to let the calling application know that things didn't go as
planned.
If the returnvalue equals 10 or is larger, everything is cool, so we commit
the transaction and return the value of 1.
I hope that this will bring some clarity on how to use sprocs and
transaction. The code above might not be the best for your situation, but
is just there as some sort of guidelines. I am sure it could be rewritten
to perform better or make more sense, but it's just an example.
To understand more of these subjects, I recommend "Professional SQL Server
7.0 programming" by Robert Vieira and "Professional VB6 MTS by Matthew
Bortniker", both from Wrox Press.
The latter is very much VB and MTS oriented, but it gives you great insight
in transactions etc.
HtH
Imar
At 07:54 AM 10/13/2000 -0700, you wrote:
>Imar,
>1) I want a value to be returned to me to know if it
>was updated or not. This way I know in the scripting
>that it was updated and I can do....
>++++++++++++++++++
>if return value indicates updated then
>response.write "Updated okay"
>else
>response.write "Did not update, you may be already
>active or you may have bad email"
>++++++++++++++++++
>2) (basically, I used to use the below in scripting
>and dynamically built sql strings...)
>++++++++++++++
>cn.begintrans, numa
>if numa =1 then
>cn.committrans
>response.write "update"
>else
>cn.rollback
>++++++++++++++
>BUT I have since move to stored procs and I'm trying
>to learn how to accomplish the same thing.
>
>3) Does stored procs use begintrans, commit, rollback?
>4) Someone told me it does it automatically?
>5) If not auto then should I use it and how?
>
>I hope this clears up what I'm trying to do, if not
>just email me.
>Thank you for you time and knowledge,
>l
Message #7 by dont worry <aspmailbox@y...> on Fri, 13 Oct 2000 13:43:42 -0700 (PDT)
|
|
Thank you
--- Imar Spaanjaars <Imar@S...> wrote:
> Hi there,
>
> 1. You can return almost any value form a sproc
> based on your own your
> criteria.
> The problem you are facing, is that updating a value
> that does not exist
> does not generate an error. That is, as long as the
> datatype you pass in as
> a parameter is correct, and the rest of your SQL
> statement is valid of course.
> For instance: UPDATE USER SET ACTIVE = 1 WHERE
> userID =
> 'DoesNotExist@n...' and ACTIVE = 0 will
> complete successfully. No
> error is generated, even if DoesNotExist@n...
> does not exist as an ID
> What you could do is query for the user first,
> before you update
> Something along these lines:
>
> Dim sLocalConnectionString
> Dim rsCheckUser
> Dim sSQL
> sLocalConnectionString
> Application("myConnectionString")
> sSQL = "SELECT COUNT(userID) FROM tblUser WHERE
> userID = '" & sEmailAddress
> & "' AND userActive = 0
> Set rsCheckUser
> Server.CreateObject("ADODB.Recordset")
> rsCheckUser.Open sSQL, sLocalConnectionString,
> adOpenForwardOnly,
> adLockReadOnly, adCmdText
> if CInt(rsCheckItems(0)) > 0 then' user is found,
> and active = 0 so update
> the user.
> Dim objUpdateUser ' as ADODB.Command
> ' Create parameteres here
> ' bla bla bla etc etc
> objUpdateUser.Execute
> ' If you still need a returnvalue, get it
> here, before setting
> objUpdateUser to nothing
> Set objUpdateUser = Nothing
> else
> Response.Write("User ID not found, or
> already active")
> end if
> rsCheckUser .Close
> Set rsCheckUser = Nothing
> (Note: you could rewrite the example above, creating
> a sproc that does the
> counting, and execute it through a command object.
> The number of records
> could be the returnvalue of the sproc)
>
> 2. To answer your other questions: TMK, SQL Server
> does not start a
> transaction automatically in a Stored Procedure.
> However, it will
> participate in one when the calling page starts a
> new transaction (by
> setting the TRANSACTION=[transactionType] directive
> at the beginning of the
> page).
> To have SQL start a new transaction, do it yourself
> by using BEGIN TRAN
> [transactionName].
>
> For example, consider the following stored
> procedures:
>
> <SQL PROCEDURES>
> Create Procedure spTest1
> As
> DECLARE @returnFromOtherSproc int
> BEGIN TRAN myTransactionName
> /* Do an insert here. We will undo this one
> when we roll back*/
> INSERT INTO tblTest1 (Description)
> VALUES('Hello')
> /* Now call another sproc */
> EXEC @returnFromOtherSproc = spTest2
>
> if @returnFromOtherSproc < 10 /* Just made
> this up. Should be
> based on business logic of course */
> begin
> rollback tran
> return 0 /* arbitrary value. Can be
> anything you want */
> end
> else
> begin
> COMMIT TRAN
> return 1 /* arbitrary value. Can be
> anything you want */
> end
>
> spTest2 is a simple sproc that inserts a simple
> value in a table, and is
> shown here for completeness only.
>
> Create Procedure spTest2
> As
> INSERT INTO tblTest2 (Description)
> VALUES('Hello')
> return @@identity
>
> </sql procedures>
>
> <ASP CODE>
> Dim sLocalConnectionString
> Dim cmdUpdate
> Dim iNewID
> sLocalConnectionString
> Application("myConnectionString")
>
> Set cmdUpdate= Server.CreateObject("ADODB.Command")
> cmdUpdate.ActiveConnection = sLocalConnectionString
> cmdUpdate.CommandType = adCmdStoredProc
>
> cmdUpdate.CommandText = "spTest1"
> cmdUpdate.Parameters.Append
> (objInsertOrUpdate.CreateParameter("Return",
> adInteger, adParamReturnValue))
> cmdUpdate.Execute
>
> iNewID = cmdUpdate.Parameters(0) ' Return
> parameter . Holds 1 on
> success, 0 on failure
> Set cmdUpdate = Nothing
> </asp code>
>
> What we do here is the following:
>
> From an ADO Command object spTest1 is called. In
> this sproc a value is
> inserted in tblTest1. If the outcome of the second
> operation is not what we
> want, we undo this insert later.
>
> Then we call spTest2, our second operation. This
> simply inserts a value in
> a second table, and returns the new ID. If the ID is
> smaller then 10, we
> don't like the result, and rollback the current
> transaction. We return a
> value of 0 to let the calling application know that
> things didn't go as
> planned.
>
> If the returnvalue equals 10 or is larger,
> everything is cool, so we commit
> the transaction and return the value of 1.
>
> I hope that this will bring some clarity on how to
> use sprocs and
> transaction. The code above might not be the best
> for your situation, but
> is just there as some sort of guidelines. I am sure
> it could be rewritten
> to perform better or make more sense, but it's just
> an example.
>
> To understand more of these subjects, I recommend
> "Professional SQL Server
> 7.0 programming" by Robert Vieira and "Professional
> VB6 MTS by Matthew
> Bortniker", both from Wrox Press.
> The latter is very much VB and MTS oriented, but it
> gives you great insight
> in transactions etc.
>
> HtH
>
> Imar
>
>
Message #8 by Imar Spaanjaars <Imar@S...> on Fri, 13 Oct 2000 23:37:37 +0200
|
|
You're welcome.
Did it work / help??
Imar
At 01:43 PM 10/13/2000 -0700, you wrote:
>Thank you
>--- Imar Spaanjaars <Imar@S...> wrote:
> > Hi there,
> >
|
|
|
View Cart
