The first version of IBM App Connect Enterprise to provide a built-in ODBC database driver for communicating with PostgreSQL databases was 12.0.10.0. The latest mod release of ACE 13.0.2.0 extends this support to enable users to invoke stored procedures on PostgreSQL which return dynamic result sets. For users familiar with using Compute node ESQL to invoke stored procedures on other database types, you may notice a slight difference in the syntax we are promoting for PostgreSQL. For PostgreSQL, a dummy cursor value must be supplied in the ESQL CALL statement for each intended results set.
For this example, consider a PostgreSQL database table which is created and populated with some rows of data like this:
CREATE TABLE mix_table ( id SERIAL PRIMARY KEY, name VARCHAR(50), email VARCHAR(50), ts TIMESTAMP, luckynumber NUMERIC );
INSERT INTO mix_table VALUES (4, 'Ali', 'ali@example.com','1999-01-08 04:05:06', 40 );
INSERT INTO mix_table VALUES (5, 'Bobby', 'bobby@example.com', '2000-01-08 04:05:06', 50 );
INSERT INTO mix_table VALUES (6, 'Charles', 'charles@example.com','2001-01-08 04:05:06', 60 );
The resulting populated table would look like this:
id name email ts luckynumber
===========================================================================
4 Ali ali@example.com 1999-01-08 04:05:06 40
5 Bobby bobby@example.com 2000-01-08 04:05:06 50
6 Charles charles@example.com 2001-01-08 04:05:06 60
Consider further, a stored procedure called return_mixed_results which is defined like this:
CREATE OR REPLACE PROCEDURE return_mixed_results (
IN username1 character varying,
IN username2 character varying,
IN username3 character varying,
INOUT user_cursor1 refcursor,
INOUT user_cursor2 refcursor,
INOUT user_cursor3 refcursor
)
language plpgsql
as $$
begin
OPEN user_cursor1 FOR
SELECT id, name, email, luckynumber
FROM mix_table
WHERE name = username1;
OPEN user_cursor2 FOR
SELECT id, name, email
FROM mix_table
WHERE name = username2;
OPEN user_cursor3 FOR
SELECT id, name, email, ts
FROM mix_table
WHERE name = username3;
end;$$;
A Compute node's ESQL would then define a CREATE PROCEDURE statement that specifies three IN parameters, three INOUT parameters and specifies three dynamic result sets:
CREATE PROCEDURE my_return_mix(IN username1 CHAR, IN username2 CHAR, IN username3 CHAR, INOUT ResultSet01 CHAR, INOUT ResultSet02 CHAR, INOUT ResultSet03 CHAR)
LANGUAGE DATABASE
DYNAMIC RESULT SETS 3
EXTERNAL NAME "return_mixed_results";
Consider then invoking the my_return_mix procedure and then using its results to construct an output message using Compute node ESQL like this:
DECLARE ResultSet01 ROW;
DECLARE ResultSet02 ROW;
DECLARE ResultSet03 ROW;
DECLARE username1 CHAR 'Charles';
DECLARE username2 CHAR 'Bobby';
DECLARE username3 CHAR 'Ali';
DECLARE dummy_cursor1 CHAR;
DECLARE dummy_cursor2 CHAR;
DECLARE dummy_cursor3 CHAR;
CALL my_return_mix(username1, username2, username3, dummy_cursor1, dummy_cursor2, dummy_cursor3, ResultSet01.data[], ResultSet02.data[], ResultSet03.data[]) ;
SET OutputRoot.XMLNSC.Results.Data1.UserID = ResultSet01.data.id;
SET OutputRoot.XMLNSC.Results.Data1.UserName = ResultSet01.data.name;
SET OutputRoot.XMLNSC.Results.Data1.UserEmail = ResultSet01.data.email;
SET OutputRoot.XMLNSC.Results.Data1.ts = ResultSet01.data.ts;
SET OutputRoot.XMLNSC.Results.Data1.luckynumber = ResultSet01.data.luckynumber;
SET OutputRoot.XMLNSC.Results.Data2.UserID = ResultSet02.data.id;
SET OutputRoot.XMLNSC.Results.Data2.UserName = ResultSet02.data.name;
SET OutputRoot.XMLNSC.Results.Data2.UserEmail = ResultSet02.data.email;
SET OutputRoot.XMLNSC.Results.Data2.ts = ResultSet02.data.ts;
SET OutputRoot.XMLNSC.Results.Data2.luckynumber = ResultSet02.data.luckynumber;
SET OutputRoot.XMLNSC.Results.Data3.UserID = ResultSet03.data.id;
SET OutputRoot.XMLNSC.Results.Data3.UserName = ResultSet03.data.name;
SET OutputRoot.XMLNSC.Results.Data3.UserEmail = ResultSet03.data.email;
SET OutputRoot.XMLNSC.Results.Data3.ts = ResultSet03.data.ts;
SET OutputRoot.XMLNSC.Results.Data3.luckynumber = ResultSet03.data.luckynumber;
This would generate an output message like this:
<Results>
<Data1>
<UserID>6</UserID>
<UserName>Charles</UserName>
<UserEmail>charles@example.com</UserEmail>
<luckynumber>60</luckynumber>
</Data1>
<Data2>
<UserID>5</UserID>
<UserName>Bobby</UserName>
<UserEmail>bobby@example.com</UserEmail>
</Data2>
<Data3>
<UserID>4</UserID>
<UserName>Ali</UserName>
<UserEmail>ali@example.com</UserEmail>
<ts>1999-01-08T04:05:06</ts>
</Data3>
</Results>