Link to home
Start Free TrialLog in
Avatar of DonKronos
DonKronosFlag for United States of America

asked on

Calling SQL Server 2000 Stored Procedure from Powerbuilder 10.0 Build 4510

I have the following code in the OPEN event of the application.  The parameters @as_Action. @as_String and @as_Result are declared as VarChar(50) in the stored procedure.

----------------------------------------------
string        ls_Action , ls_String , ls_Result
DECLARE sp_InOutString PROCEDURE FOR usp_InOutString @as_Action = :ls_Action,
                                    @as_String = :ls_String,
                                    @as_Result = :ls_Result OUTPUT,
                                    Using sqlca ;
      

sqlca.DBMS = 'ODBC'
sqlca.DBParm = "ConnectString='DSN=UtilityCabinet'"
sqlca.AutoCommit = True
CONNECT USING sqlca ;

if sqlca.sqlcode <> 0 then
    messagebox("No Database","Could not connect to the database")
    return
end if

ls_Action = 'IN'
ls_String = '1234'
ls_Result = '                                                  '
Execute sp_InOutString ;

----------------------------------

I get this error returned.

SQLSTATE = 22005
[Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification

I've even modified the stored procedure so all it does is set @as_Result = @as_String and I still get the error.  I searched here in EE and found suggestions to add additional values to the DBParm, but they didn't help.

Help Please!
SOLUTION
Avatar of tr1l0b1t
tr1l0b1t

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
Avatar of DonKronos

ASKER

Yes, I tried both of those with the same error message result.  :( :(

Any other ideas???

Hi,

did u try executing the procedure in Query Ananlyser...

Does it give the required result or the same error...


Cheers,
Rosh
Hi,

check this link...
http://experts.about.com/q/1047/768320.htm

If nothing seems to help then plz post ur Stored Procedure here...


Cheers,
Rosh
SOLUTION
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've tried the different DBParm settings you referenced here.  Still no luck.  I do get a different error "...  Invalid syntax near the word OUTPUT ..."

Here is the stored procedure:

USE UtilityCabinet
GO

If EXISTS (SELECT name
       FROM   sysobjects
       WHERE  name = N'usp_InOutString'
       AND    type = 'P')
    DROP PROCEDURE usp_InOutString
GO

CREATE PROCEDURE usp_InOutString
            (@as_Action VarChar(50),
             @as_String VarChar(50),
             @as_Result VarChar(50) OUTPUT)
AS

Declare @ll_Return         As Int
Declare @ls_OutString    As VarChar(50)

Set @ll_Return = 0

Set @ls_OutString = @asString

Set @as_Result = @ls_OutString

Return @ll_Return

GO

Thanks for your responses.  This is starting to reach the critical point where I HAVE to get this working.
ASKER CERTIFIED SOLUTION
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
OK, I did that.  Got the same results.  I believe the error occurs before the stored procedure even starts to execute.

I have also tried all combinations of the following DBParm settings.

,StripParmNames='Yes',DelimitIdentifier='No',MsgTerse='Yes',FormatArgsAsExp='N'

There has to be another setting somewhere that is preventing the stored procedure call from executing.  :(  :(  :(
I ended up doing this project in VB, but I did figure out what was causing the error in the PowerBuilder version.

The stored procedure has three arguments (@as_Action, @as_String, @as_Result OUTPUT), which are parameters 1, 2 and 3 respectively.  However, it has a return value which is parameter 0.  So, when PowerBuilder builds the parameters for the stored procedure it is taking :ls_Action (which is a string) and trying to convert it to an integer for the return!!!

So, I made the return an explicit output argument (@as_Action, @as_String, @as_Result OUTPUT, @al_Return OUTPUT) and removed the return statement from the stored procedure and everything works fine.  :)

I'm going to split the points between the individuals who were kind enough to respond to my question.