 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussion Groups Database Servers DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybaseDesktop Databases FileMakerFoxProMS AccessParadoxGeneral General DB TopicsDatabase TheoryRelated Topics Java Development.NET DevelopmentVB DevelopmentMore Topics ... |
Database Forum / DB2 Topics / September 2003How 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. > Hi, > I defined a stored procedure that outputs a SQLDA struct as following: [quoted text clipped - 21 lines] > it should be. > Any info about this? Thanks. Could you please describe what you want to do with that?  SignatureKnut Stolze Information Integration IBM Germany / University of Jena > 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. >>Could you please describe what you want to do with that? > [quoted text clipped - 8 lines] > not seem to have anything for SQLDA, then why they let you define a > stored procedure that returns a SQLDA struct? Please help. Thanks. 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 Gemini <wgemini@nowhere.com> wrote in message > 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 [quoted text clipped - 4 lines] > that way the client app doesn't need to change even if you decide to > connect to a different data source. 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. 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). >>Could you please describe what you want to do with that? > [quoted text clipped - 8 lines] > not seem to have anything for SQLDA, then why they let you define a > stored procedure that returns a SQLDA struct? Please help. Thanks. > 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 [quoted text clipped - 5 lines] > 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). 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. SignatureKnut Stolze Information Integration IBM Germany / University of Jena > 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. They are precisely what I need. Thanks! 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. Signature_____________________________________________________________________ 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 > Hi, > I defined a stored procedure that outputs a SQLDA struct as following: [quoted text clipped - 21 lines] > it should be. > Any info about this? Thanks. The only way you can pass a DA into a stored procedure is through parameter style db2dari. It's not supported/documented anymore in v8 as far as I know though. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.