-
hoshea hashub
- PowerBuilder
- 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!
Find Questions by Tag
Helpful?
If a reply or comment is helpful for you, please don’t hesitate to click the Helpful button. This action is further confirmation of their invaluable contribution to the Appeon Community.