Professional Web Applications Themes

How to define a SQLDA output parameter in CREATE PROCEDURE statement? - IBM DB2

Hi, I defined a stored procedure that outputs a SQLDA struct as following: -------------------- extern "C" SQL_API_RC SQL_API_FN getParamGroups(struct sqlda *, sqlint32 *, char*); -------------------- It compiled fine (the SP is implemented with C++ and Embedded SQL). But how do I declare the data type for it in the CREATE PROCEDURE statement to register the SP with the database? I tried the following: --------------- CREATE PROCEDURE GET_GROUPS (OUT output_sqlda STRUCT SQLDA, OUT errorCode INTEGER, OUT errorMsg CHAR(32)) DYNAMIC RESULT SETS 1 LANGUAGE C PARAMETER STYLE GENERAL NO DBINFO FENCED NOT THREADSAFE READS SQL DATA PROGRAM TYPE SUB EXTERNAL NAME 'dbaccesssvr!getGroups' ...

  1. #1

    Default How to define a SQLDA output parameter in CREATE PROCEDURE statement?

    Hi,
    I defined a stored procedure that outputs a SQLDA struct as following:
    --------------------
    extern "C" SQL_API_RC SQL_API_FN getParamGroups(struct sqlda *,
    sqlint32 *, char*);
    --------------------
    It compiled fine (the SP is implemented with C++ and Embedded SQL).
    But how do I declare the data type for it in the CREATE PROCEDURE
    statement to register the SP with the database? I tried the following:
    ---------------
    CREATE PROCEDURE GET_GROUPS (OUT output_sqlda STRUCT SQLDA, OUT
    errorCode INTEGER, OUT errorMsg CHAR(32))
    DYNAMIC RESULT SETS 1
    LANGUAGE C
    PARAMETER STYLE GENERAL
    NO DBINFO
    FENCED NOT THREADSAFE
    READS SQL DATA
    PROGRAM TYPE SUB
    EXTERNAL NAME 'dbaccesssvr!getGroups'
    -----------------
    And of course it failed because I made up this data type STRUCT SQLDA,
    hoping to hit it. I cannot find in the SQL reference on what data type
    it should be.
    Any info about this? Thanks.
    minjie Guest

  2. #2

    Default Re: How to define a SQLDA output parameter in CREATE PROCEDURE statement?

    minjie <com> wrote:
     

    Could you please describe what you want to do with that?

    --
    Knut Stolze
    Information Integration
    IBM Germany / University of Jena
    Knut Guest

  3. #3

    Default Re: How to define a SQLDA output parameter in CREATE PROCEDURE statement?

    We don't suport what you are trying to do.

    There is an obsolete form of procedure that takes just an SQLDA as its
    arguments, but you cannot mix an SQLDA with other explicit parameters.

    The preferred form of creating a procedure has each parameter explcitly
    specified.
    --
    __________________________________________________ ___________________
    Doug Doole
    DB2 Universal Database Development
    IBM Toronto Lab

    Visit the DB2 UDB and DB2 Connect Online Support site at:
    http://www.ibm.com/software/data/db2/udb/winos2unix/support
    Douglas Guest

  4. #4

    Default Re: How to define a SQLDA output parameter in CREATE PROCEDURE statement?

    minjie wrote: 

    The only way you can pass a DA into a stored procedure is through
    parameter style db2dari. It's not supported/doented anymore in v8 as
    far as I know though.

    No Guest

  5. #5

    Default Re: How to define a SQLDA output parameter in CREATE PROCEDURE statement?

    > Could you please describe what you want to do with that?

    I'm trying to get a row of data using a stored procedure, which
    returns a SQLDA struct, so the client can get the column names and
    their corresponding values. I know I can return a result set from a
    stored procedure and use a CLI client to receive the result set. But
    that requires the explicit binding of columns to get each value from
    each column. If I use SQLDA, I don't need to change the client app
    when I add or drop a column from a table. I'm now stuck on this issue,
    because the parameter data types in CREATE PROCEDURE declaration do
    not seem to have anything for SQLDA, then why they let you define a
    stored procedure that returns a SQLDA struct? Please help. Thanks.
    minjie Guest

  6. #6

    Default Re: How to define a SQLDA output parameter in CREATE PROCEDURE statement?

    minjie wrote: 
    >
    >
    > I'm trying to get a row of data using a stored procedure, which
    > returns a SQLDA struct, so the client can get the column names and
    > their corresponding values. I know I can return a result set from a
    > stored procedure and use a CLI client to receive the result set. But
    > that requires the explicit binding of columns to get each value from
    > each column. If I use SQLDA, I don't need to change the client app
    > when I add or drop a column from a table. I'm now stuck on this issue,
    > because the parameter data types in CREATE PROCEDURE declaration do
    > not seem to have anything for SQLDA, then why they let you define a
    > stored procedure that returns a SQLDA struct? Please help. Thanks.[/ref]

    They did not let you define a stored procedure that returns a SQLDA
    structure. You wrote a C function, not a stored procedure. And as the
    CREATE PROCEDURE statement showed you, you can't define a stored
    procedure that returns a SQLDA structure. I am not sure why you want to
    return the SQLDA as your client app doesn't need to change a thing if
    you do it correctly with a resultset. Best way is probably through JDBC,
    that way the client app doesn't need to change even if you decide to
    connect to a different data source.

    W Guest

  7. #7

    Default Re: How to define a SQLDA output parameter in CREATE PROCEDURE statement?

    One comment...you don't need the DA to protect you from changes to the
    table altering your result set data...just explicitly list the columns
    you want returned on the mrsp cursor open in the SP. Parm style dari
    (which others have mentioned) is bad for many reasons (including
    performance in v8)...I would strongly suggest not using it.

    I'm not a CLI whiz, but would expect there's some describe like function
    you could call on the result set (I know for certain that cursor names,
    types etc are returned to the client along with the row data).

    minjie wrote: 
    >
    >
    > I'm trying to get a row of data using a stored procedure, which
    > returns a SQLDA struct, so the client can get the column names and
    > their corresponding values. I know I can return a result set from a
    > stored procedure and use a CLI client to receive the result set. But
    > that requires the explicit binding of columns to get each value from
    > each column. If I use SQLDA, I don't need to change the client app
    > when I add or drop a column from a table. I'm now stuck on this issue,
    > because the parameter data types in CREATE PROCEDURE declaration do
    > not seem to have anything for SQLDA, then why they let you define a
    > stored procedure that returns a SQLDA struct? Please help. Thanks.[/ref]

    Sean Guest

  8. #8

    Default Re: How to define a SQLDA output parameter in CREATE PROCEDURE statement?

    Sean McKeough <ca.ibm.com> wrote:
     

    You have the SQLGetMoreResults(), SQLNextResult(). The CLI reference
    describes how those functions wark and how to use SQLNumResultCols(),
    SQLDescribeCol(), SQLColAttribute(), and other functions with the different
    result sets.

    --
    Knut Stolze
    Information Integration
    IBM Germany / University of Jena
    Knut Guest

  9. #9

    Default Re: How to define a SQLDA output parameter in CREATE PROCEDURE statement?

    W Gemini <com> wrote in message 

    Thanks! I did not find the right functions to do what I want before.
    Since you said '... as your client app doesn't need to change a thing
    if you do it correctly with a resultset', I looked more carefully
    through CLI reference and found that I can use SQLNumResultCols(),
    SQLDescribeCol(), SQLColAttribute() to do SELECT with unkown number of
    columns. In that case I do not need to use SQLDA.
    minjie Guest

  10. #10

    Default Re: How to define a SQLDA output parameter in CREATE PROCEDURE statement?

    Knut Stolze <ibm.com> wrote in message news:<bl9aus$mh6$rz.uni-jena.de>...
     

    They are precisely what I need. Thanks!
    minjie Guest

Similar Threads

  1. Replies: 0
    Last Post: January 16th, 12:54 PM
  2. #40143 [NEW]: how to get parameter values of stored procedure.?
    By hd4_all at yahoo dot co dot in in forum PHP Bugs
    Replies: 0
    Last Post: January 16th, 12:02 PM
  3. populate datagrid with stored procedure w/parameter
    By Machelle Chandler in forum ASP.NET Data Grid Control
    Replies: 4
    Last Post: October 17th, 03:33 PM
  4. Stored Procedure/Parameter problem
    By ElmoWatson in forum ASP.NET General
    Replies: 1
    Last Post: August 6th, 01:19 AM
  5. Store Procedure plus parameter where condition..
    By Boris Condarco in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: July 8th, 01:54 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139