PROP:SQL and CALL

1

Hello,

Is it possible to disable Clarion feature where ODBC_File{PROP:SQL} = 'CALL SomeProc(p1,p2);' is converted to SELECT * FROM SomeProce(p1,p2) in SQL?

I am using PSQL, and procedures do not return value in PSQL, so there is an error when executing previous statement.

EDIT:
You can overwrite it with ; at the beginning. Previous code to be executed successfully should be ODBC_File{PROP:SQL} = ';CALL SomeProc(p1,p2);'.

2

NORESULTCALL shoud work as well:
ODBC_File{PROP:SQL} = 'NORESULTCALL SomeProc(p1,p2);'

2 Likes
3

That is definitely more readable solution. :slightly_smiling_face:

Edit againt:
Actually it does not work, it is converted to SELECT *....

4

That’s not my experience with NORESULTCALL. I just resurrected code that I submitted as a bug back in 2020. PTSS 43126. (If you want to use it, note that “&para” got translated to a paragraph mark). I ran the ODBC trace, and the Clarion statement

odbc{PROP:SQL} = ‘noresultcall show_passed(’‘ODBC’‘,&param_in,&param_out)’

showed up as:

odbc_call 4074-3afc ENTER SQLExecDirect
HSTMT 0x067B10D0
UCHAR * 0x0150BAC8 [ -3] “{CALL show_passed(‘ODBC’,?,?)}\ 0”
SDWORD -3

No “select *” like you are reporting. Sure, I’m connecting to Oracle via ODBC, not Postgres, but the Clarion ODBC driver is not going to change what it generates depending what it is connected to (that’s not entirely true, but I really don’t think it would change for this).

Just for completeness: a CALL, rather than a NoResultCall sent exactly the same statement, and after execution it checked the number of result columns, found they were 0, and didn’t go any further after that.

5

Just in case anyone finds it useful…I notice the original poster shows the call as ‘CALL SomeProc(p1,p2);’ but it’s not clear if p1 and p2 are constants or bound variables, are presumably not passed by address.

The program I used looks like this:

PROGRAM

 MAP
 END

odbc FILE,DRIVER('ODBC'),OWNER(''),NAME('SYS.DUAL'),PRE(od)
record RECORD
dummy string(1)
END
END

oracle FILE,DRIVER('Oracle'),OWNER(''),NAME('sys.dual'),PRE(ora)
record RECORD
dummy string(1)
END
END

param_in cstring(20)
param_out cstring(80)

 CODE

 param_in = 'MyParam'
 bind('param_in',param_in)
 bind('param_out',param_out)
 open(odbc)
 if error() then message('error opening odbc ' & error()).
 open(oracle)
 if error() then message('error opening oracle ' & error()).
 odbc{PROP:SQL} = 'noresultcall show_passed(''ODBC'',&param_in [IN],&param_out [OUT])'
 message(param_out)
 oracle{PROP:SQL} = 'noresultcall show_passed(''Oracle'',&param_in [IN],&param_out [OUT])'
 message(param_out)

The problem it was designed to show has been fixed (the ODBC length reported is now the actual length of the cstring, not the declared length), same as for the Oracle call.
If you omit the [IN] designation on param_in, the parameter doesn’t actually get passed. That wasn’t the case back in 2020.

The Oracle procedure is defined as:

create or replace procedure show_passed(pass_method IN varchar2,param_in IN varchar2,param_out OUT varchar2) is
  begin
param_out := trim(pass_method) || to_char(length(param_in));
end;
6

And here’s what I got work wirth postgres becaue I had a few minutes to spare. I couldn’t get the procedure to work, but a function worked fine.

Function defined as:

CREATE OR REPLACE FUNCTION public.f_show_passed(
IN pass_method character varying,
IN param_in character varying) returns varchar
language plpgsql
AS $BODY$
begin
return trim(pass_method) || cast(length(param_in) as varchar);
end;
$BODY$;

Program:

  PROGRAM

  MAP
  END
client          FILE,DRIVER('ODBC'),OWNER('PostgreSQL30'),NAME('public.client'),PRE(cli)
client_pkey       KEY(cli:client_id),PRIMARY
record            RECORD
client_id           SHORT,NAME('"client_id"')
first_name          STRING(20),NAME('"first_name"')
last_name           STRING(20),NAME('"last_name"')
                  END
                END

param_in cstring(20)
param_out cstring(80)
meth cstring(20)

    CODE
        meth = 'ODBC'
 param_in = 'MyParam'
 bind('param_in',param_in)
        bind('param_out',param_out)
        bind('meth',meth)
 open(client)
 if error() then message('error opening odbc ' & error()).
 client{PROP:SQL} = ' &param_out = Noresultcall f_show_passed(&meth [INOUT],&param_in [INOUT])'
 message(param_out)

The ODBC trace shows the call as:
testpostgres 5484-52cc ENTER SQLExecDirect
HSTMT 0x00C87F50
UCHAR * 0x00E9BF50 [ -3] “{?=CALL f_show_passed(?,?)}\ 0”
SDWORD -3

Not sure why I had trouble with the procedure version.

7

That is because procedure in PSQL does not return value, functions do. And Clarion converts CALL to SELECT *. You should check LOG file in PSQL.

To me, it is most important that I got it to work with ;. Thank you for all for help. Also, I am not using latest version of Clarion 11, it is 13815. Maybe in newer version this is fixed…

8

The procedure reported an “Error in Execution”, and I’m not sure why, but it had nothing to do with not returning a value. Procedure worked from PgAdmin. The procedure was called exactly the same way as the function was called. I showed from the SQL trace what was called for the function:

“{?=CALL f_show_passed(?,?)}\ 0”

For the procedure it was {CALL show_passed(?,?,?)}, i.e. three parameters in the call, not two, and no return.

Nothing I did showed (in the ODBC trace) Clarion sending select * to the back end. Maybe that is something that Postgres did later on? Maybe you should check the ODBC trace to see what Clarion is sending as opposed to the psql log to see what PG tried to execute? How is your procedure defined? Is it a procedure or a function that returns void? What language is it, SQL or plpgsql?

9

It is a procedure, and the language is plpgsql.

10

I’ve played a little bit more with the procedure, and I think the problem is that there is nothing that indicates to postgres that it should be dealing with plpgsql, and therefore there should be no difference between call procname and select procname, and that procname must be a function, because you wouldn’t use a procedure like that.

In Oracle (the Oracle driver), when you issue a CALL procname, what is actually sent is BEGIN procname; END; Postgres I think would need something similar ( a DO $$begin …; end$$;). I think your method will probably only work passing constants as parameters, but that adding the “;” makes postgres think that it is dealing with a DO block, since there is more than one statement.

What I would suggest is that you create any procedures in PostGres as functions that return void. That way postgres will be happy executing them as “select funcname”. Also, on the Clarion side make sure you put [IN] markers on whatever parameters you are passing in (assuming they are variables), because otherwise PG will ignore them.

For example, if I send noresultcall show_passed(&m,&i,&o) from Clarion then postgres will give me the error “function show_passed() does not exist”.

Note that a) it thinks it must be a function, and b) it thinks it has no parameters. And I think you are right that postgres thinks it is a function because it is being called as select show_passed(), but I think that is because of how PostGres has chosen to interpret “CALL show_passed”, which is what Clarion sent.

If I declare the first two parameters as [IN] and the last as [OUT], then PostGres also errors, but this time thinks it is dealing with a procedure with two parameters. If I change the last parameter to [inout] then it thinks it is dealing with a function again, this time with three parameters.

11

Here’s a post that talks about the same problem with the JDBC driver: java - "x is a procedure, use "call"" when I am already using call - Stack Overflow

see particularly:

“So calls like this still have to get translated to the native SQL by the JDBC driver. It happens that the Postgres driver, by default, treats such statements as function calls and translates them to SELECT reduceStock(?, ?) SQL query. This is not how stored procedures shall be called in Postgres. In Postgres a stored procedure call SQL is call reduceStock(?, ?) .”

Looks like you should be able to set the “escapeSyntaxCallMode” property so that the calls are handled more sensibly.

12

escapeSyntaxCallMode is an option for the JDBC driver. It doesn’t look like it is available for the ODBC driver.

13

Procedures are a relatively new addition to Postgres, they were only added at Ver 11 (current is 16).
I must admit I’ve never used Stored Procedures, but I make a lot of use of Functions, if it doesn’t really do anything I just return a value of 0.

Sounds like we might have to contact the ODBC Driver writer to find out what the deal is?