[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1453
  • Last Modified:

Acquiring Resultset from CallableStatement

Hi Experts...

I am trying to acquire a ResultSet from CallableStatement...

ResultSet rst;
CallableStatement cstmt;

String strSQL = "(call MY_PROC(?))";
String MyParam = "28900003";

cstmt = conn.prepareCall(strSQL, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
cstmt.setString(1, MyParam);
cstmt.execute();
rst = (ResultSet) cstmt.getObject(1);


Here is my MS SQL Stored Procedure :

PROCEDURE MY_PROC
@MyParam varchar(20)
AS
SELECT Field2,Field3 FROM Orders WHERE Field1=@MyParam


However I am getting this error on line cstmt.execute()...

java.sql.SQLException: [aveConnect][JDBC SQL Server Driver]  Line 1: Incorrect s
yntax near '28900003'.
        at net.avenir.jdbc2.k.q(Protocol.java)
        at net.avenir.jdbc2.k.eb(Protocol.java)
        at net.avenir.jdbc2.k.ib(Protocol.java)
        at net.avenir.jdbc2.k.query(Protocol.java)
        at net.avenir.jdbc2.a.b(Statement.java)
        at net.avenir.jdbc2.y.a(CallableStatement.java)
        at net.avenir.jdbc2.y.execute(CallableStatement.java)

Hope you could help me...   Thanks...   =)
0
aris_datuin
Asked:
aris_datuin
  • 2
1 Solution
 
CEHJCommented:
That looks to me like an incorrect syntax problem in the SP itself, and hence is not a Java problem yet
0
 
sbockelmanCommented:

You need to register an OUT paramter to hold the rs.

You need to use {} not () around your proc.

Read this article for details:

 http://www.enterprisedt.com/publications/oracle/result_set.html

You may need to adjust some of the things for SQLServer vs. Oracle, but principles are similar.

http://www.mcse.ms/archive90-2004-4-594262.html

0
 
aris_datuinAuthor Commented:
Hi sbockelman...


This line solved my problem :

            You need to use {} not () around your proc.


Thanks..  =)
0
 
sbockelmanCommented:
cool!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now