• You are here:  
  • Home
  • Q&A
  • Q&A
  • PowerBuilder
  • Reference string variables truncated when passed back from stored procedure

Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (4)

Resolved Reference string variables truncated when passed back from stored procedure

  1. Issue
0
Votes
Undo
  1. Karen Gray
    Karen Gray
  2. PowerBuilder
  3. Sunday, 8 August 2021 20:31 PM UTC

HI,

 

We are using PB 2017 R3 against a postgres database. We are in the process of porting our application from informix to postgres database.

I have an issue in that functions declared with reference variables - after retrieving the data - the strings are being truncated to 16 characters.

 

For example: this function public function integer

of_cpp_s04_getfirmname (string as_firm_id, ref string as_firm_name)

DECLARE cf_cpp_s04_getfirmName PROCEDURE FOR cf_cpp_s04_getFirmName
as_firm_id = :as_firm_id
USING itr_Database;

EXECUTE cf_cpp_s04_getfirmName;

// Check for an error when executing the stored procedure
ll_sql_result = itr_Database.of_CheckDBError(TRUE)
//
IF (ll_sql_result <> gi_SQL_SUCCESS) AND (ll_sql_result <> gi_SQL_NOT_FOUND) THEN
/RETURN FAILURE
END IF

// Perform the priming fetch of the name of the firm
FETCH cf_cpp_s04_getfirmName INTO :as_firm_name;

 

The result in as_firm_name returns only the first 16 or so characters.

I have hundreds of these functions with ref string variables and would like to know what is causing this and how to rectify without converting all of them to datastores?

  1. Function
  2. PowerBuilder (Appeon)
Not able to access TLS1.2 from HTTPClient object
ShareData error when sharing back to original sour...
Responses (4)
Kai Zhao @Appeon
Kai Zhao @Appeon Accepted Answer Pending Moderation
  1. Monday, 9 August 2021 07:45 AM UTC
  2. PowerBuilder
  3. # 1

Hi Karen,

I did not reproduce the issue on our end. Please provide the information below for more study. Thanks!
1. The Postgres database version.
2. The client driver type and version.
3. A simple PB case and SQL to create stored procedure, table, and data.

Regards,
ZhaoKai

Comment
There are no comments made yet.
Karen Gray
Karen Gray Accepted Answer Pending Moderation
  1. Tuesday, 10 August 2021 23:25 PM UTC
  2. PowerBuilder
  3. # 2

Hi ZhaoKai,

 

thanks for your reply. See attached zip fle created in 7zip with the test window , table and data.

The Postgres database version. Version 10.6
2. The client driver type and version is JDBC driver 42.2.14 postgresql

The attached zip fle created in 7zip with the test window , table and data.

Includes SQL to create stored procedure, table, and data.

There is a README file.

 

Any questions please let me know

 

 



Attachments (1)
PB_TEST_29113.zip
Comment
Kai Zhao @Appeon
  1. Kai Zhao @Appeon
  2. Wednesday, 11 August 2021 04:49 AM UTC
Hi Karen,



Thanks for providing the file. I got the error below when I tried to create FUNCTION cf_cpp_s04_getFirmName, please provide the SQL to create table crs_user to me, thanks.

ERROR: invalid type name "crs_user.corporate_name%TYPE"



And I got this error below when trying to import w_test_29113, please provide the related object.

test.pbl(w_test_29113).2: Error C0001: Illegal data type: w_sheet



Does it work well with ODBC driver? Please also provide the JDBC driver file, driver name, and sample URL to connect to the database which you are using, thanks.



Regards,

ZhaoKai

  1. Helpful
Karen Gray
  1. Karen Gray
  2. Wednesday, 11 August 2021 20:57 PM UTC
hi Zhao - sorry the table referred to should be crs_user_test.. not _crs_user. so the declaration should be - DECLARE ls_firm_name crs_user_test.corporate_name%TYPE;

Also yes it will work with ODBC driver but we are not using that, we are using a JDBC driver.

The w_sheet is a pfc library object contained in the pfemain library - have you used or are familiar with the pfc libraries?

I will supply the JDBC connection separately and w_sheet objects in another zip file
  1. Helpful
There are no comments made yet.
Karen Gray
Karen Gray Accepted Answer Pending Moderation
  1. Wednesday, 11 August 2021 21:39 PM UTC
  2. PowerBuilder
  3. # 3

Requested files in this zip attachment.

 

Any questions please ask.

Attachments (1)
PB_TEST_29113_2.zip
Comment
Kai Zhao @Appeon
  1. Kai Zhao @Appeon
  2. Thursday, 12 August 2021 07:37 AM UTC
Thanks for providing the file. I reproduced the issue with JDBC driver, please use ODBC driver to solve the issue.

  1. Helpful
There are no comments made yet.
Karen Gray
Karen Gray Accepted Answer Pending Moderation
  1. Wednesday, 11 August 2021 21:59 PM UTC
  2. PowerBuilder
  3. # 4

Hi  ZhaoKai,

in regards to the ODBC connection - to clarify - we used that for an INFORMIX database.

For business reasons, when porting to postgres we have to use a JDBC connection now.

Regards Karen

 

Comment
Armeen Mazda @Appeon
  1. Armeen Mazda @Appeon
  2. Wednesday, 11 August 2021 22:06 PM UTC
We only support Postgres through ODBC. Please refer to documentation: https://docs.appeon.com/pb2017r3/connecting_to_your_database/ch02s06.html
  1. Helpful
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Thursday, 12 August 2021 08:27 AM UTC
Hi,

We're using a JDBC driver with another Database. The only way to have no problems (for us), is by declaring the stored procedures and functions as external RPC Subroutines and Functions. (just do a "search" in the pb help on 'rpcfunc').

I'm not sure if it'll work well for Postgresql, but I think it should, as explained on this page by Bruce:

https://www.brucearmstrong.org/2018/02/powerbuilder-2017-r2-new-feature.html

regards
  1. Helpful
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Thursday, 12 August 2021 08:47 AM UTC
Also, if you have string variables by "ref" make sure you initialize them before doing the stored procedure / function call.

For example:



string ls_byRef

ls_byRef = space(60)

....
  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.