Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2005
    Posts
    16

    Exclamation Unanswered: Use of alias instead of a column name in a cursor

    Hi Frnds,

    I have created one stored procedure Test123(?,?,?).
    In this stored procedure , i have declared one Cursor that fetches few records from a table as :

    DECLARE testCsr CURSOR WITH RETURN FOR
    SELECT schema_nm as sm_name from <Schema_Name>.<TableName> ;

    open testCsr;



    When i am accessing the Result set through a java program,

    I have to access it as :
    ResultSet.getString(1);




    But i want to access the result using column alias as :

    ResultSet.getString("sm_name");



    Please help me out people !

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What does the ResultSetMetadata getColumnName for column 1 give you?
    That is what you will need to pass to the ResultSet.getString(). Also
    remember that java is case sensitive, and DB2 returns column name in upper case.

    HTH

    Andy

  3. #3
    Join Date
    Dec 2005
    Posts
    16
    [QUOTE=ARWinner]What does the ResultSetMetadata getColumnName for column 1 give you?

    It gives me : 1 ....

    I tried passing "1" as an argument . it works.
    But i want to access the same column,using String "sm_name"

    Also, when i execute the this Stored Prc using Developemt center in Db2 8.0,
    i can see the column name as "1" and not as sm_name .

    Is there any attribute of the Cursor that enables column alias ???
    Is there any special attribute that i shld set when declaring cursor ??

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I assume that when you call the stored procedure from the CLP that it is also labeled "1". I have never had a problem aliasing column names from a cursor in a stored procedure. What OS and DB2 version? Also it might help better if you post the entire stored procedure definition.

    Andy

  5. #5
    Join Date
    Dec 2005
    Posts
    16
    CREATE PROCEDURE SYSPROC.VL001406
    (
    OUT OUT_SQLCODE INTEGER,
    OUT OUT_SQLSTATE CHAR(5),
    OUT OUT_MESSAGE VARCHAR(3000)
    )

    RESULT SET 1
    LANGUAGE SQL
    MODIFIES SQL DATA
    EXTERNAL NAME 'VL001406'
    COLLID MA1_VL
    ASUTIME LIMIT 100000
    WLM ENVIRONMENT DB2T
    RUN OPTIONS 'ALL31(ON),STACK(4096,4096,ANY,KEEP,65536,65536),M SGFILE(SYSOUT,FBA,133,0,ENQ)'
    COMMIT ON RETURN NO

    ------------------------------------------------------------------------
    -- SQL Stored Procedure
    ------------------------------------------------------------------------
    MAIN: BEGIN NOT ATOMIC
    --Declare Variables for Exception Handling
    DECLARE SQLconcat CHAR(16);
    DECLARE SQLCODE INT DEFAULT 0;
    DECLARE SQLSTATE CHAR(5) DEFAULT '00000';


    DECLARE testCsr CURSOR WITH RETURN FOR
    SELECT schema_nm as sm_name from mySchema.myTable;


    --Declare Handle for SQLEXCEPTION
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    IF (1=1) THEN
    SET SQLconcat = cast(SQLCODE as char(11)) CONCAT SQLSTATE;
    SET OUT_SQLCODE = cast(substr(SQLconcat,1,11) as integer);
    SET OUT_SQLSTATE = substr(SQLconcat,12,5);
    END IF ;

    SET OUT_MESSAGE = 'Hello World';
    open testCsr;

    END MAIN




    This is stored procedure .... and through java program , i want to access the column using an alias "sm_name" ..

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What OS and DB2 version?

  7. #7
    Join Date
    Dec 2005
    Posts
    16
    Server : Linux . Db2 8.0

    Actully my application on client box is in java ... So there must not be any issues with the same

  8. #8
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Is this on mainframe? These options do not exist on LUW:

    EXTERNAL NAME 'VL001406'
    COLLID MA1_VL
    ASUTIME LIMIT 100000
    WLM ENVIRONMENT DB2T
    RUN OPTIONS 'ALL31(ON),STACK(4096,4096,ANY,KEEP,65536,65536),M SGFILE(SYSOUT,FBA,133,0,ENQ)'

    If it is mainframe, I cannot help you.

    Andy

  9. #9
    Join Date
    Dec 2005
    Posts
    16
    ya .. sorry , it is on Main frame.

Posting Permissions

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