CODEWITHSUNDEEP
javasqlsql-serverstored-proceduresjdbc
nisha
nisha

Reputation: 711

Fetching value from a SQLServer procedure using java program

I have a procedure as

USE [ER]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[PKG_ER_SEL$sp_ERGetOrgDetails]  
   @v_OrgId int = NULL,
   @v_Email varchar(max) = NULL,


   @cv_1 varchar(8000)  OUTPUT
AS 
     BEGIN
      BEGIN TRY
         SET @cv_1 = NULL
         SELECT 
            o.ORGID, 
            o.NAME, 
            o.PHONE, 
            o.URL, 
            o.CURRENCY, 
            o.MEASUREMENT, 
            o.ISACTIVE, 
            u.FIRSTNAME, 
            u.LEVELID, 
            u.APPROVALLIMIT, 
            u.USERID, 
            u.MANAGERID
         FROM 
            dbo.ER_ORGANIZATIONS  AS o 
               JOIN dbo.ER_USERS  AS u 
               ON o.ORGID = u.ORGID
         WHERE 
            o.ORGID = @v_OrgId AND 
            u.EMAIL = @v_Email AND 
            u.ISACTIVE = 1
         ORDER BY u.LEVELID

      END TRY

      BEGIN CATCH
         BEGIN
            PRINT 'System Error: Unhandled error occured during execution of sp_ERGetOrgDetails procedure, Please contact your administrator'
         END
      END CATCH
   END

====================================================================

I am trying to fetch values from this procedure which returns 4 records.

My java prog is - 

public static void getOrgDetails1(int orgId, String email){
            Connection con =null;

            try{
                CallableStatement cs=null;
                con = SqlConnectionManager.getConnection();
                cs = con.prepareCall("{call PKG_ER_SEL$sp_ERGetOrgDetails(?,?,?)}");
                cs.setInt(1,orgId);
                cs.setString(2, email);
                cs.registerOutParameter(3, java.sql.Types.VARCHAR); //cv_1
                cs.execute();
                String resultSet=cs.getString(3);
                System.out.println(resultSet);


            }catch(Exception e){
                e.printStackTrace();
            }finally{
                SqlConnectionManager.releaseConnection(con);
            }

}

When i run this java prog i get output as null, where as i am expecting 4 records, hence some value in the string.

Can anyone help like how i can fetch value from a SQL Server procedure.

Upvotes: 0

Views: 1217

Answers (2)

Aniket
Aniket

Reputation: 379

If you are expecting 4 values as output then your procedure is incorrect and so is your Java Code. If you are expecting a ResultSet/or more than one value as the output, either return a cursor from your SQL procedure or increase the no. of out parameters to 4. You will have to set the values of your output variables as part of your procedure. The way you have written your Java class, you can only expect 1 value i.e The varchar(8000) Parameter that you have declared as OUT parameter in procedure. You are not even assigning any value to variable @cv_1 and hence you get null on Java side. Return a cursor from the procedure and use registerOutParameter(3, OracleTypes.CURSOR) instead of registerOutParameter(3, Types.String) and (ResultSet)getObject(3) instead of getString(3).

Upvotes: 0

Rob
Rob

Reputation: 6487

Try changing String resultSet=cs.getString(3); to ResultSet resultSet = cs.getResultSet();

You will then have to iterate over the result set to get each of the four rows.

Upvotes: 1

Related Questions