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 !

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

[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 ??

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

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" ..

What OS and DB2 version?

Server : Linux . Db2 8.0

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

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

ya .. sorry , it is on Main frame.