[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3846
  • Last Modified:

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!
0
DonKronos
Asked:
DonKronos
3 Solutions
 
tr1l0b1tCommented:
I guess you've tested using StripParmNames = 'Yes' in DBParm :

SQLCA.DBParm = "ConnectString='DSN=PC-CMS;UID=sa;PWD=;',StripParmNames = 'Yes'"

Have u tried to declare the procedure without parameter names ? something like :

DECLARE sp_InOutString PROCEDURE FOR usp_InOutString :ls_Action, :ls_String, :ls_Result OUTPUT, USING SQLCA ;
0
 
DonKronosAuthor Commented:
Yes, I tried both of those with the same error message result.  :( :(

Any other ideas???

0
 
diasroshanCommented:
Hi,

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

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


Cheers,
Rosh
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
diasroshanCommented:
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
0
 
batchakamalCommented:
check this links

http://www.dbforums.com/t901656.html


otherwise post ur stored procedure, so that we can work around.

0
 
DonKronosAuthor Commented:
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.
0
 
diasroshanCommented:
Hi,

just for the heck of it... can u try making a small change in ur procedure...

change,
Set @ll_Return = 0

to,
Set @ll_Return = '0'

try putting zero into single quotes...

see if the error persists...

Cheers,
Rosh
0
 
DonKronosAuthor Commented:
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.  :(  :(  :(
0
 
DonKronosAuthor Commented:
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.

 
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now