Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Returning java resultset from MSSQL stored Procedure

Posted on 2009-02-17
15
Medium Priority
?
1,033 Views
Last Modified: 2012-05-06
How to we return a java 'ResultSet' from a MSSQL Stored procedure.
0
Comment
Question by:tia_kamakshi
  • 5
  • 5
  • 4
  • +1
15 Comments
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 23660101
The example I posted was Oracle, but is same concept on the Java side.  To execute the stored procedure, though, in MS SQL you will need to use EXEC verb instead of CALL.

CallableStatement cstmt = dbConn.prepareCall("{exec stored_procedure_name()}");
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 23660212
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 26

Expert Comment

by:ksivananth
ID: 23660531
0
 

Author Comment

by:tia_kamakshi
ID: 23661498
I am writting

CallableStatement cstmt = conn.prepareCall("{exec sp_NumberOfNewUsers_PB}");

ResultSet rs = cstmt.getResultSet();

I am getting ResultSet as null value
???

Any help

Thanks
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 23661533
Have you called execute on the CS?
0
 

Author Comment

by:tia_kamakshi
ID: 23661574
Sorry I didnot understood CS??

I called my SP as
CallableStatement cstmt = conn.prepareCall("{exec sp_NumberOfNewUsers_PB}");

ResultSet rs = cstmt.getResultSet();

My SP is
Create procedure sp_NumberOfNewUsers_PB as
SELECT
            CLIENTTYPE,WEBSITE,COUNT(*) CT
      FROM
            RET_CLIENTS
      WHERE
            CLIENTTYPE IN ('RETAIL', 'INSTITUTIONAL')       AND
            YEAR(CREATIONDATE) = YEAR(GETDATE())

      GROUP BY
            CLIENTTYPE, WEBSITE
order by WEBSITE, CLIENTTYPE



0
 
LVL 86

Expert Comment

by:CEHJ
ID: 23661621
>>Sorry I didnot understood CS??

CS = CallableStatement. Looks like you didn't
0
 
LVL 86

Accepted Solution

by:
CEHJ earned 2000 total points
ID: 23661681
i.e.
CallableStatement cstmt = conn.prepareCall("{exec sp_NumberOfNewUsers_PB}");
boolean success = cstmt.execute();
ResultSet rs = cstmt.getResultSet();

Open in new window

0
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 23661694
CallableStatement cstmt = conn.prepareCall("{exec sp_NumberOfNewUsers_PB}");
'execute callable statement here
cstmt.execute();
ResultSet rs = cstmt.getResultSet();

Just like in example I posted link to earlier.  Think CEHJ's had same thing as well.
0
 

Author Comment

by:tia_kamakshi
ID: 23661706
>>Looks like you didn't
?? Sorry again

Can you please help me in fixing
Connection conn = DBConnection.getConnection();
        try
        {
Connection conn = DBConnection.getConnection();
 CallableStatement cstmt = conn.prepareCall("{exec sp_NumberOfNewUsers_PB}");
            ResultSet rs = cstmt.getResultSet();
while (rs.next())
             {
}
}catch(Exception e)
        {
            throw e;
        }finally
        {
              conn.close();
        }
0
 

Author Comment

by:tia_kamakshi
ID: 23661729
Connection conn = DBConnection.getConnection();
try
{
      Connection conn = DBConnection.getConnection();
      CallableStatement cstmt = conn.prepareCall("{exec sp_NumberOfNewUsers_PB}");
        ResultSet rs = cstmt.getResultSet();

      while (rs.next())
        {
      }
}catch(Exception e)
{
     throw e;
}finally
{
       conn.close();
}
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 23661730
0
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 23661737
See two previous posts for how to fix.  CEHJ's is nice as you can then use the boolean flag later in code.
0
 

Author Closing Comment

by:tia_kamakshi
ID: 31547788
Many Thanks. This works great
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An old method to applying the Singleton pattern in your Java code is to check if a static instance, defined in the same class that needs to be instantiated once and only once, is null and then create a new instance; otherwise, the pre-existing insta…
Basic understanding on "OO- Object Orientation" is needed for designing a logical solution to solve a problem. Basic OOAD is a prerequisite for a coder to ensure that they follow the basic design of OO. This would help developers to understand the b…
Viewers will learn about the regular for loop in Java and how to use it. Definition: Break the for loop down into 3 parts: Syntax when using for loops: Example using a for loop:
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
Suggested Courses
Course of the Month15 days, 4 hours left to enroll

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question