Friday, May 29, 2015

How could I execute SQL query in ESQL

Usually I used to call stored procedure rather than executing SQL query directly in message flow, but this round the use case is a bit different. Is it possible to submit query in ESQL just like the prepare statement we did in regular programming language? The answer is yes. I have to put a mark mention that I have no knowledge about message flow programming, this skill was taught by my senior. This is how I make the query call:
    DECLARE query CHARACTER 'select tabA.colA, tabA.colB from Table_A tabA';
    SET Environment.Variable.ResultSet[] = PASSTHRU(query);
    SET totalRow = CARDINALITY(Environment.Variable.ResultSet[]);
    IF (totalRow = 1) THEN
     SET OutputRoot.SOAP.Body.ns1:ServiceAResponse.ns1:Category.ns1:FieldA = Environment.Variable.ResultSet.colA;
     SET OutputRoot.SOAP.Body.ns1:ServiceAResponse.ns1:Category.ns1:FieldB = Environment.Variable.ResultSet.colB;
     SET I = 1;
     WHILE I < totalRow DO   
      SET OutputRoot.SOAP.Body.ns1:ServiceAResponse.ns1:Category[I].ns1:FieldA = Environment.Variable.ResultSet[I].colA;
      SET OutputRoot.SOAP.Body.ns1:ServiceAResponse.ns1:Category[I].ns1:FieldB = Environment.Variable.ResultSet[I].colB;
      SET I = I + 1;
    END IF;
The statement at line 4, PASSTHRU(query), is how I execute the query. The result will be store in ResultSet[]. It is an array. And then at line 11 is how I loop through the result set from DB

Take note also if the result set return just 1 row of record, I have to explicitly tell the program how it should be execute. Notice the difference between line 7 and line 12? This is because in ESQL, the assignment of only 1 value can't be done to a variable of array type.

Feeling stunt? Yes, I am.

No comments: