Solved

Dephi ADO - Stored procedures

Posted on 2013-01-28
8
465 Views
Last Modified: 2013-03-24
Hello,

I am using Delphi 2010 and SQL Server 2012.

I have an SP as follows:  It checks to see if a given user/password combination exists and returns the UserId if one is found.  It also sets the return value to 1 to indicate a user has been found.

 ALTER PROCEDURE [dbo].[DoesStandardUserExist]

	-- Add the parameters for the stored procedure here
	@Username nvarchar(50),
	@Password nvarchar(50),
	@UserId integer = null output

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

  select @UserId = Users.Id from Users 
  where Users.UserId = @Username and Users.Password = @Password and Users.Status = 'A'
 -- If the login was found then return 1.  
  if not( @UserId is null) return 1 
  else return 0 ;
 
END

Open in new window

I have dropped a TADOStoreProc component on my DataModule and use it as follows:

  DoesStandardUserExist.Parameters.ParamByName('@UserName').Value := edtUserName.Text ;
  DoesStandardUserExist.Parameters.ParamByName('@Password').Value := edtPassword.Text ;
  DoesStandardUserExist.ExecProc ;

  if not VarIsNull( DoesStandardUserExist.Parameters.ParamByName('@UserId').Value ) then
  begin

Open in new window

BUT it appears that only the first time i execute the SP i get the correct return values.  After the first successful user/password match the values of @UserID and @Return_Value are always set to the last successful match.

They never get set to null (@UserId), or 0 (@Return_value)

The SP works as expected when called directly from Studio Manager.  Am i using ADOStoredProc correctly?
0
Comment
Question by:soozh
  • 4
  • 3
8 Comments
 
LVL 36

Accepted Solution

by:
Geert Gruwez earned 333 total points
Comment Utility
just to be sure you get the correct values:

use this for an intermediate test:
  if not( @UserId is null) return 100
  else return 999;

Open in new window


also reset the output parameter before executing second time:
DoesStandardUserExist.Parameters.ParamByName('@UserId').Value := Null;

Open in new window


you may be getting the number of rows affected in result
0
 
LVL 19

Assisted Solution

by:Thommy
Thommy earned 167 total points
Comment Utility
Try a parameter refresh before setting the parameter values:

DoesStandardUserExist.Parameters.Refresh;
0
 

Author Comment

by:soozh
Comment Utility
Hello,

Thanks for the replies.

Am i quite certain the sp returns the correct result but i will make the changes you suggest and test again.

I have tried setting the @userid parameter to null before calling but it still came back with the previous result.

This is obviously a coding error on my side but i just can see where.  Am i correct to assume that an ExecProc call should execute the SP each time it is made, and that the sp parameters should be updated correctly after the call?

I can have a look at refreshing the parameters but it does not seem to be logical ...
0
 
LVL 36

Expert Comment

by:Geert Gruwez
Comment Utility
why are you using return with a procedure ????

you use return with a function
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:soozh
Comment Utility
my understanding that the value of the tsql "return" statement is returned in the parameter "@Return_Value"

Maybe that is wrong?
0
 
LVL 36

Expert Comment

by:Geert Gruwez
Comment Utility
you program delphi ?
same logic applies for function/procedure

return is used for indicating succes or failure inside a proc
http://msdn.microsoft.com/en-us/library/ms187926.aspx

if you create the proc as below:
ALTER PROCEDURE [dbo].[DoesStandardUserExist]

	-- Add the parameters for the stored procedure here
	@Username nvarchar(50),
	@Password nvarchar(50),
	@UserId integer = null output

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

  select @UserId = Users.Id from Users 
  where Users.UserId = @Username and Users.Password = @Password and Users.Status = 'A'
END

Open in new window


does it work then ?
0
 
LVL 36

Assisted Solution

by:Geert Gruwez
Geert Gruwez earned 333 total points
Comment Utility
it's actually worse:
return in a proc actually generates an error

http://msdn.microsoft.com/en-us/library/ms174998.aspx
0
 

Author Comment

by:soozh
Comment Utility
Hello,

I am pretty certain my SP works.  I am more interested in if i am calling it correctly from Delphi.

/richard
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now