Solved

Dephi ADO - Stored procedures

Posted on 2013-01-28
8
468 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 37

Accepted Solution

by:
Geert Gruwez earned 333 total points
ID: 38826084
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
ID: 38826092
Try a parameter refresh before setting the parameter values:

DoesStandardUserExist.Parameters.Refresh;
0
 

Author Comment

by:soozh
ID: 38826253
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 37

Expert Comment

by:Geert Gruwez
ID: 38826273
why are you using return with a procedure ????

you use return with a function
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

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

Maybe that is wrong?
0
 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 38826460
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 37

Assisted Solution

by:Geert Gruwez
Geert Gruwez earned 333 total points
ID: 38826462
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
ID: 38826581
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

910 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

18 Experts available now in Live!

Get 1:1 Help Now