Topic
10 replies Latest Post - ‏2014-09-03T05:05:41Z by bjbbarker
bjbbarker
bjbbarker
17 Posts
ACCEPTED ANSWER

Pinned topic Calling API from SQL stored procedure.

‏2013-03-12T20:10:29Z |
Can someone help me figure out the data types for the parms of an API call?

I'm trying to call the QMHQRDQD API and it is defined as:
Required Parameter Group:

1 Receiver variable Output Char(*)
2 Length of receiver variable Input Binary(4)
3 Format name Input Char(8)
4 Qualified data queue name Input Char(20)

My question is how do I define a Binary(4) in SQL? I've tried dec(4), smallint, int. Here is how I currently have the stored procedure. When I run it I get an empty result.

create or replace procedure get_data_queue_size (
in #name varchar(10),
out #size bigint)

LANGUAGE SQL
modifies sql data

begin
declare #rcv char(100) default ' ';
declare #rcv_len dec(4,0) default 100;
declare #fmt_name char(8) default 'RDQD0100';
declare #queue char(20) default '';

set #queue = cast(upper(#name) as char(10)) || 'AWFILES'; -- AWFILES is the library the data queue exists in.
call QSYS.QMHQRDQD(#rcv, #rcv_len, #fmt_name, #queue);
  • insert into debug(data1,data2,data3,runsecs) values('queue',#queue,hex(#rcv),#rcv_len);
set #size = 1;
end
;
Updated on 2013-03-13T19:11:41Z at 2013-03-13T19:11:41Z by krmilligan
  • bjbbarker
    bjbbarker
    17 Posts
    ACCEPTED ANSWER

    Re: Calling API from SQL stored procedure.

    ‏2013-03-12T21:20:01Z  in response to bjbbarker
    Update: I think using "int" is working correctly as the data type for the parm. The problem seems to be that I'm getting back nothing in the output variable. I've tried using different values for the length of the receiver variable and I do get an error message if the value is less than 8.
    • krmilligan
      krmilligan
      446 Posts
      ACCEPTED ANSWER

      Re: Calling API from SQL stored procedure.

      ‏2013-03-13T02:23:17Z  in response to bjbbarker
      If you want to call an API, then you should be creating an external stored procedure instead of an SQL stored procedure.

      The SQL CALL statement should really only be used to call SQL stored procedure or external stored procedures that have been registered with the CREATE PROCEDURE statement.

      Even if you get this combination to work, there's no guarantee that it will continue to work since that API doesn't have an external stored procedure associated with it.
      • bjbbarker
        bjbbarker
        17 Posts
        ACCEPTED ANSWER

        Re: Calling API from SQL stored procedure.

        ‏2013-03-13T17:04:11Z  in response to krmilligan
        Thanks. I'll try that. Would you happen to have an example of how to set it up?
        • bjbbarker
          bjbbarker
          17 Posts
          ACCEPTED ANSWER

          Re: Calling API from SQL stored procedure.

          ‏2013-03-13T17:36:26Z  in response to bjbbarker
          Hmm. I still can't get it to return any information. This is how I have it now:

          create or replace procedure awfiles.QMHQRDQD (
          out #rcv varchar(100),
          in #rcv_len int,
          in #fmt_name char(8),
          in #queue char(20))

          NO SQL
          EXTERNAL NAME 'QSYS/QMHQRDQD'
          parameter style general
          ;

          create or replace procedure get_data_queue_size (
          in #name varchar(10),
          out #size bigint)

          LANGUAGE SQL
          modifies sql data

          begin
          declare #rcv char(100) default ' a'; -- putting an a in the data to see if it changes and it is blanked out.
          declare #fmt_name char(8) default 'RDQD0100';
          declare #queue char(20) default '';

          set #queue = cast(upper(#name) as char(10)) || '*LIBL';
          CALL awfiles.QMHQRDQD(#rcv,length(#rcv),'RDQD0100',#queue);
          insert into debug(data1,data2,data3,runsecs) values(#fmt_name,#queue,'~'||hex(#rcv), length(#rcv));
          set #size = 1;
          end
          ;

          call get_data_queue_size('nextid',null) ; -- If I change the name (nextid) to a name of a queue that doesn't exists I do get a message that the object *DTAQ not found.
          Any ideas on what I'm doing wrong?
          • krmilligan
            krmilligan
            446 Posts
            ACCEPTED ANSWER

            Re: Calling API from SQL stored procedure.

            ‏2013-03-13T19:11:41Z  in response to bjbbarker
            Sorry, I was implying that the creator of the QMHQRDQD program would need to be one that registers the program as an external stored procedure. The parameter style dictates how input and output values are passed back and forth between SQL and the program.

            Need to define an external stored procedure that calls the program via the API interface.
            • CRPence@vnet.ibm.com
              31 Posts
              ACCEPTED ANSWER

              Re: Calling API from SQL stored procedure.

              ‏2014-08-29T23:09:17Z  in response to krmilligan

                The Retrieve Data Queue Description (QMHQRDQD) API is publicly documented.  So just as one can code a HLL program to call that API [with consistent and predictable parameter definitions], they could similarly code a CREATE PROCEDURE to define the API [parameters] to the SQL, such that the SQL can then invoke that API by whatever name is desired\assigned to that external [service] program name.

                The issue is not that the interface can not be defined to the SQL.  The issue is that the QMHQRDQD [as do many other APIs] have an interface that is not very compatible with the SQL; effectively, CHAR(*) return-data parameters.  The SQL wants to work with scalar values, much like the SQL works with typed columns and variables.  Many APIs provide for return-data that is undescribed data; similar to what one might expect of flat-file data, where the data must be described by the program, because there is no metadata available to the SQL.  The SQL is [quite near] unable to generally redefine untyped data into typed data; while an overlay of a the data via a structure in a language such as PL1 or RPG, for example with based storage, makes doing that very easy in those languages, there is no equivalent capability with the SQL.

                In fact, in that regard, making an External UDF that takes the SUBSTR(RtnDta, 1, 4) and RETURNS INT can be defined by an RPG program to direct-map those four bytes of data as an integer (a 10I00 in RPG fixed-format type declarative), to see the "Bytes returned" of the RDQD0100 Format; one simply need redefine the data.  The CL at some point [v5r4 IIRC] was enhanced with *BASED and *DEFINED storage such that even the CL has that capability; again, the SQL can not, and trying to perform numeric arithmetic on data in the internal binary-form instead of on data defined as a true numeric data type with numeric values is just not very workable.  So for example I might define an External User Defined Function named MapChar4toInt such that I could code the following in my SQL routine; given DECLARE rdqd_Bytes_Returned INT:
                 Set rdqd_Bytes_Returned = MapChar4toInt(SUBSTR(RtnDta, 1, 4) ) ;

          • JonathanB
            JonathanB
            3 Posts
            ACCEPTED ANSWER

            Re: Calling API from SQL stored procedure.

            ‏2014-08-29T01:51:50Z  in response to bjbbarker

            Brent, did you ever get this to work?  I was doing something similar yesterday, using the QUSRMBRD API (in a SQL function, rather than a stored procedure), and I could swear it worked the first few times, then stopped.  I have the same problem you have:  the receiver variable comes back entirely blank (all x'40'.)  In addition, I was passing the error structure with a value of 16 in the bytes-provided segment, and that structure also came back all blanks.

            I do not believe the issue is that the API is not registered as a stored procedure.  In an IBM i Navigator SQL script window, I have issued SQL calls to other API programs (QUSCRTUS to create a user space; QUSLMBR to populate the user space with a list of file members), and both worked.  In both cases, I was not calling a registered stored procedure; I was calling the API programs directly.

            • krmilligan
              krmilligan
              446 Posts
              ACCEPTED ANSWER

              Re: Calling API from SQL stored procedure.

              ‏2014-08-29T03:45:15Z  in response to JonathanB

              You're asking for a lot of headaches trying to call the API programs directly from SQL routines.

              • JonathanB
                JonathanB
                3 Posts
                ACCEPTED ANSWER

                Re: Calling API from SQL stored procedure.

                ‏2014-08-29T20:57:18Z  in response to krmilligan

                Perhaps - I didn't think I was when I set out to do it, though.  Wouldn't be the first time I gave myself a headache.  Somehow, it just didn't look like such a difficult thing to do.

            • bjbbarker
              bjbbarker
              17 Posts
              ACCEPTED ANSWER

              Re: Calling API from SQL stored procedure.

              ‏2014-09-03T05:05:41Z  in response to JonathanB

              I wasn't able to directly.  I ended up creating a simple RPG program which contained the API call and then I had the SQL stored procedure call the RPG program.