Avatar of stockman001
stockman001
 asked on

Setting up a call to SQL Server 2000 Stored Procedure from Powerbuilder 10.0 Build 4510

I read the following solution....but can not seem to get it to work for me.

https://www.experts-exchange.com/Programming/Programming_Languages/PowerBuilder/Q_21863718.html

Here is what I did:

I have a user validation stored procedure in SQL Server that I want to call and return either 1 or 0 depending on if the passwords match.

Here is my SQL Server stored procedure, I tested in Query Analyzer...and it works:

CREATE PROCEDURE [dbo].[Validate_User]
                        @UID VarChar(16),
                        @PWD VarChar(16),
                 @RETURNVALUE INT OUTPUT       
AS

DECLARE @DB_PWD VarChar(16)

SELECT @DB_PWD = DBPassword FROM tblUser WHERE DBUserID = @UID
      
      BEGIN
      IF @DB_PWD = @PWD
            SET @RETURNVALUE = 1
      ELSE
            SET @RETURNVALUE = 0

      END

GO

I set up a standard class object for transactions and declared the following local external function:

FUNCTION integer sp_Validate_User(string ls_UID, string ls_PWD, integer li_returnvalue) RPCFUNC ALIAS FOR "dbo.Validate_User"

I saved the class object as u_transaction_database, and changed the application variables types for SQLCA to u_transaction_database

I put the following code on the login screen underneath the clicked event for my login buttion:

string   ls_UserID , ls_Password
integer li_Result

ls_UserID = 'myUID'  
ls_Password = 'mypassword'

DECLARE sp_InOutString PROCEDURE FOR sp_Validate_User @ls_UID = :ls_UserID,
                               @ls_PWD = :ls_Password,
                               @li_returnvalue = :li_Result OUTPUT,
                               Using sqlca ;

Execute sp_InOutString ;

messagebox("SP RESULT", li_Result)

All li_Result returns is 0....when I know the UID/PWD is correct and return 1.

Thanks!
Dan
Editors IDEs

Avatar of undefined
Last Comment
stockman001

8/22/2022 - Mon
stockman001

ASKER

I tried this....and still get 0 for output....

I set up a standard class object for transactions and declared the following local external function:
FUNCTION integer sp_Validate_User( string ls_UID, string ls_PWD, integer li_returnvalue ) RPCFUNC ALIAS FOR "~"dbo~".~"Validate_User;1~""

I tried this as script under the button:

string   ls_UserID , ls_Password
integer li_Result

ls_UserID = 'myUID'  
ls_Password = 'mypassword'

li_Result = sqlca.sp_Validate_User(ls_UserID, ls_Password,li_Result)

messagebox("SP RESULT", li_Result)
ASKER CERTIFIED SOLUTION
gajender_99

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
stockman001

ASKER
I changed the variable li_Result to double ld_Result and tweaked it to pull the values from the window, and it works:

string   ls_UserID , ls_Password
double ld_Result

ls_UserID = sle_uid.text
ls_Password = sle_pwd.text

sqlca.sp_Validate_User(ls_UserID, ls_Password,ld_Result)

IF SQLCA.SQLCode = -1 THEN
     MessageBox("SQL error", SQLCA.SQLErrText)
Else
      
      IF ld_Result = 1 THEN
              messagebox("SP RESULT", "Password Accepted")
      ELSE
              messagebox("SP RESULT", "Password does not match, try again.")
      END IF  

END IF

I would still like to know why the subroutine would work but my function would not.  It will bug me till I find out.

Thank you Gajender.

Dan
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck