Link to home
Start Free TrialLog in
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/questions/21863718/Calling-SQL-Server-2000-Stored-Procedure-from-Powerbuilder-10-0-Build-4510.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
Avatar of stockman001
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
Avatar of gajender_99
gajender_99

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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