1. hoshea hashub
  2. PowerBuilder
  3. Friday, 10 November 2023 09:25 AM UTC

Windows 10
PB Version 6.5.1
Postgres 12
psql-ODBC 12.02.01 Connection


Hi,

Error in PB calling stored procedure, The error message is Procedure has already been executed.

I have implemented a minimal use case, see the attachment.


I created the following statement in PG.
Note: The calling statement passed in PB is call add_test_1( (1, 2) ); So must take the type as a parameter. PB 10 did not encounter this problem.

create type add_test_type as (
	a integer,
	b integer
);

CREATE or replace procedure add_test_1(add_test_param add_test_type)
LANGUAGE plpgsql
AS $$
declare 
	a integer := add_test_param.a;
	b integer := add_test_param.b;
	c integer;
BEGIN
   c := a + b;
END;
$$;

CREATE or replace procedure add_test_2(add_test_param add_test_type)
LANGUAGE plpgsql
AS $$
declare 
	a integer := add_test_param.a;
	b integer := add_test_param.b;
	c integer;
BEGIN
   c := a + b;
END;
$$;

 

The window is as follows.

The code of `button_1` is as follows. close p_add is commented.

string ls_err

long a = 1
long b = 1

declare p_add procedure for add_test_1(:a, :b);
execute p_add;
messagebox('sqlca.sqlcode',sqlca.sqlcode)

if sqlca.sqlcode < 0 then
	ls_err = sqlca.sqlerrtext
	rollback;
	MessageBox('Error', ls_err)
end if

// close p_add;

 

The code of `button_2` is as follows. 

string ls_err

long a = 1
long b = 1

declare p_add procedure for add_test_2(:a, :b);
execute p_add;
messagebox('sqlca.sqlcode',sqlca.sqlcode)

if sqlca.sqlcode < 0 then
	ls_err = sqlca.sqlerrtext
	rollback;
	MessageBox('Error', ls_err )
end if
close p_add;

 

There are several situations where an error can occur.
Case 1: Click `button_1` twice.
Case 2: Click `button_1` once, and then click `button_2`.


Now there are two solutions.
Solution 1: Do not comment the `close p_add` of `button_1`.
Solution 2: `button_1` and `button_2` are both called `p_add`, so change one name.


I also read the ODBC log of PB connecting PG. I guess PB mistook `p_add` for a global variable, because when I clicked `button_1` for the second time, PB called `SQLFreeHandle` of ODBC interface and didn't call `SQLAllocHandle` again. So report an error Procedure has already been executed.

 

But when I connected to Oracle with PB's built-in `073 Oracle 7.3`, there was no such problem.

Did PB do anything internally when connecting with Oracle?

 

Thanks!

 

 

Attachments (1)
Andreas Mykonios Accepted Answer Pending Moderation
  1. Friday, 10 November 2023 13:37 PM UTC
  2. PowerBuilder
  3. # 1

Hi.

Both Powerbuilder 6.5.1 and Oracle 7.3 are too old. Also you understand thar PB 6.5.1 was released sometime near 1997... PostgreSQL was released in 2019? Of course it may work, but it's a very bad idea to continue that way. Just my opinion.

Andreas.

Comment
  1. hoshea hashub
  2. Monday, 13 November 2023 01:51 AM UTC
Do you know which version of PB is more compatible with ODBC?
  1. Helpful
  1. Armeen Mazda @Appeon
  2. Monday, 13 November 2023 19:22 PM UTC
Try PB 2022 R2. Trial version available on Appeon Website: https://www.appeon.com/freetrial?product=pb&application=pb
  1. Helpful
  1. Roland Smith
  2. Monday, 13 November 2023 19:44 PM UTC
Even PB 10 is 18 years old. I have it on my Windows 11 laptop and it is very unstable. I had to set a compatibility level to get it to work properly.
  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.
We use cookies which are necessary for the proper functioning of our websites. We also use cookies to analyze our traffic, improve your experience and provide social media features. If you continue to use this site, you consent to our use of cookies.