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

score 0 · Answer 1 · answered Sep 19 '22 at 09:22

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";

Using dynamic DB Schema name when defining procedure in esql

1 Answers1