Using dynamic DB Schema name when defining procedure in esql

Question

I'm using AppConnect v12 and I'm trying to write a DB stored procedure from esql using the below piece of code:

CREATE PROCEDURE getCustomer_SP (
    IN custNo INT,
    INOUT responseCode CHAR)
RETURNS INT
LANGUAGE DATABASE
EXTERNAL NAME "DBSchemaDev.SPName";

where the DBSchema varies from environment to another, so I need it to be variable according to the environment and I will get its value from config. file For example, for test environment, it is called DBSchemaTest and so on.. I tried the below

DECLARE DBSchema CHARACTER 'DBSchemaDev';
CREATE PROCEDURE getCustomer_SP (
    IN custNo INT,
    INOUT responseCode CHAR)
RETURNS INT
LANGUAGE DATABASE
EXTERNAL NAME DBSchema || ".SPName";

but it is not working, I tried also below

DECLARE DBSchema CHARACTER 'DBSchemaDev';
CREATE PROCEDURE getCustomer_SP (
    IN custNo INT,
    INOUT responseCode CHAR)
RETURNS INT
LANGUAGE DATABASE
EXTERNAL NAME "{DBSchema}.SPName";

but it is not working as well, so I was wondering if there is possibility to use variables in the EXTERNAL NAME field

Kingo Mostafa · Accepted Answer · 2022-09-19 09:22:11Z

0

Solution found. I used to call the stored procedure as below

DECLARE DBSchema CHARACTER 'esb'; --where esb is retrieved from config. file
CALL getCustomer_SP(custNo, responseCode) IN Database.{DBSchema};

where I removed the schema name from the stored procedure definition as below

    CREATE PROCEDURE getCustomer_SP (
    IN custNo INT,
    INOUT responseCode CHAR)
RETURNS INT
LANGUAGE DATABASE
EXTERNAL NAME "SPName";

answered Sep 19, 2022 at 9:22

Kingo Mostafa

3476 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using dynamic DB Schema name when defining procedure in esql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related