HomePowerBuilderAppeon Documentation Center

Calling a Stored Procedure in PowerBuilder results in 'Invalid character value for cast specification' error

Symptom

Recently migrated application from PowerBuilder 11.1 (build 8123) to PowerBuilder 2017 on Windows 7.  The application is using Microsoft SQL Server and when calling a stored procedure using ODBC results in the error of "Invalid character value for cast specification".  

The following dbparms are being used in the database connection: 

  • StripParmNames='Yes'

  • CallEscape='No'.  

Environment

  1. PowerBuilder

  2. Windows 7

  3. Microsoft SQL Server

Reproducing the Issue

  1. Create a stored procedure that passes an integer in and returns two strings on a Microsoft SQL Server database.

  2. Create a PB Build 11.1 Build 8123 application that has a non visual user object with a function that calls a stored procedure and then migrate it to PB 2017 like so: 

    STRING ls_string1, ls_string2 
DECLARE MyProc PROCEDURE FOR dbo.usp_test :al_number :ls_string1, :ls_string2; 
EXECUTE MyProc;

    The script returns an error for the customer.

Cause

From the Connection Reference Manual, PBNewSPInvocation uses an alternative method to invoke a stored procedure. The values are:

  1. No - (Default) Use the standard method to invoke a stored procedure

  2. Yes - Use the alternative method to invoke a stored procedure

The application was using the default value of No for PBNewSPInvocation.

Solution

For the database connection, the following dbparms resolved the error: 

SQLCA.DBParm="ConnectString='DSN=MyAppDSN;UID=xxx;PWD=xxx',PBNewSPInvocation='Yes',CallEscape='No',StripParmNames='Yes'"
Prev  Up  Next
PowerBuilder using ADO.NET causing System.InvalidOperationException: This SQLTransaction has completed, it is no longer usable  Home  The IsNumber function returns the incorrect