DonKronos
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=Utilit yCabinet'"
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!
--------------------------
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=Utilit
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi,
did u try executing the procedure in Query Ananlyser...
Does it give the required result or the same error...
Cheers,
Rosh
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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',Deli mitIdentif ier='No',M sgTerse='Y es',Format ArgsAsExp= 'N'
There has to be another setting somewhere that is preventing the stored procedure call from executing. :( :( :(
I have also tried all combinations of the following DBParm settings.
,StripParmNames='Yes',Deli
There has to be another setting somewhere that is preventing the stored procedure call from executing. :( :( :(
ASKER
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.
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.
ASKER
Any other ideas???