Here comes a test that you can cut-and-paste. If it doesn't
work for you I guess there is some problem with your Driver,
and you'll have to get a better one...
----------------- java -----------------------
/*
* Created by IntelliJ IDEA.
* User: stefan
* Date: 2002-maj-07
* Time: 11:01:16
* To change template for new class use
* Code Style | Class Templates options (Tools | IDE Options).
*/
import java.sql.*;
public class Test {
private CallableStatement csmt = null;
public Test() {
try{
Connection con = getCon();
//specify the stored procedure to execute
csmt = con.prepareCall("{call sp_BHTTest(?, ?, ?, ?)}");
//set input and output parameters if any
csmt.setString(1, "1");
csmt.setString(2, "2");
csmt.setString(3, "3");
csmt.registerOutParameter(4, Types.VARCHAR);
//here you get what is "SELECT"-ed in your stored
//procedure (look at the last SELECT, before RETURN 0)
ResultSet res = csmt.executeQuery();
//get the output parameters if any
String out = csmt.getString(4);
System.out.println("out: " + out);
if (res.next()) {
System.out.println("ResultSet: " + res.getObject(1));
}
csmt.close();
}catch(Exception e){
System.out.println("Exception :"+e);
}
}
private Connection getCon() {
Connection con = null;
try {
String driver = "com.inet.tds.TdsDriver";
String url =
"jdbc:inetdae:localhost:1433?database=coinsp_prod&sql7=true";
Class.forName(driver);
con = DriverManager.getConnection(url, "sa", "");
} catch (Exception e) {
e.printStackTrace();
}
return con;
}
public static void main(String[] args) {
new Test();
}
}
-------------- end java ----------------------
cut-and-paste the following into your QueryAnalizer
to create a stored procedure named sp_BHTTest
------------------ SQL -----------------------
if exists (select * from sysobjects where id =
object_id(N'[dbo].[sp_BHTTest]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_BHTTest]
GO
SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
GO
CREATE PROCEDURE sp_BHTTest ( @param1 VARCHAR(32), @param2 VARCHAR(32),
@param3 VARCHAR(32), @param4 VARCHAR(32) OUTPUT ) AS
-- set OUTPUT -variable
SELECT @param4 = 'This is to OUTPUT -parameter'
-- This is what will be "returned" to the ResultSet in your java-code
SELECT 'Returned to ResultSet'
-- End processing
RETURN 0
GO
SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
GO
--------------- end SQL ----------------------
Hope it helps...
/stefan
-----Ursprungligt meddelande-----
Fr�n: Witold Iwaniec [mailto:[EMAIL PROTECTED]]
Skickat: den 11 juni 2002 15:27
Till: JDJList
�mne: [jdjlist] Re: SV: RE: SQL Server and stored procedure
Stefan
Thanks for the reply.
I have found my syntax error so can read the return value.
If I use your suggestion:
select '<return_value>'
how would I read it in java - would it be first parameter, something like:
csmt = con.prepareCall( "{? = call sp_BHT ?, ?, ?, ?}" );
and use like other out parameter?
csmt.registerOutParameter( 1, Types.VARCHAR );
...
String ret_val = cstmt.getString( 1 );
My code doesn't seem to read the value
Thanks
Witold
On 11 Jun 2002 at 12:05, Stefan Carlsson wrote:
>
> Hi Witold !
>
> You should not use the RETURN statement in your stored procedure. Use
> SELECT <your_value> to return what you want to fetch in you java code.
> RETURN is used to exit your statement (unconditionally). Try this
> instead: ---------------------------------------------------- CREATE
> PROCEDURE sp_BHT ( @var1 varchar(32), @var2 varchar(32), @var3
> varchar(32), @var4 varchar(32) output) AS -- -- some other stuff here
> -- -- set your OUTPUT -variabel value select @var4 =
> '<output_variabel_value>'
>
> -- set the value to be returned from the stored proc
> select '<return_value>'
>
> return 0
> ----------------------------------------------------
>
> Hope it helps...
>
> /stefan
>
>
>
>
>
> -----Ursprungligt meddelande-----
> Fr�n: Witold Iwaniec [mailto:[EMAIL PROTECTED]]
> Skickat: den 10 juni 2002 16:13
> Till: JDJList
> �mne: [jdjlist] RE: SQL Server and stored procedure
>
>
> Naveen
>
> Thanks for the reply.
>
> It works but it uses an OUT parameter, which is the fourth parameter
> passed to the procedure. I am looking for a return value which is not
> out parameter. According to the on-line help it would be something
> like:
>
> csmt = con.prepareCall("{? = Call sp_BHT(?, ?, ?, ?)}");
>
> you still would register it as out parameter.
> It works with Oracle but SQL Server driver doesn't like this, or few
> other variations of, syntax
>
> Witold
>
>
> On 8 Jun 2002 at 11:04, Naveen Joshi wrote:
>
> > Hi Iwaniec,
> >
> > Try this. It works for me.
> >
> > Connection con = null;
> > CallableStatement csmt = null;
> > try{
> > //specify the stored procedure to execute
> > csmt = con.prepareCall("{Call sp_BHT(?, ?, ?, ?)}");
> >
> > //set input and output parameters if any
> > csmt.setString(1,strEdi_tran_no);
> > csmt.setString(2,strTrans_set_cde);
> > csmt.setString(3,strHd_tl_id);
> > csmt.registerOutParameter(4,Types.VARCHAR);
> >
> > csmt.execute();
> >
> > //get the output parameters if any
> > strBHT = csmt.getString(4);
> >
> > csmt.close();
> >
> > }catch(Exception e){
> > out.println("Exception :"+e);
> > }
> >
> >
> > Thanks
> > Naveen
> >
> > ----------
> > From: Witold Iwaniec [SMTP:[EMAIL PROTECTED]]
> > Sent: Saturday, June 08, 2002 1:15 AM
> > To: JDJList
> > Subject: [jdjlist] SQL Server and stored procedure
> >
> > Hi all
> >
> > I am using the latest SQL Server 2000 JDBC driver from Microsoft,
> > obviously connecting to SQL Server, and have trouble getting the
> > return value from stored procedure.
> >
> > I have no problem to read OUT parameters. For example if I have a
> > procedure declared as:
> >
> > CREATE PROC TEST_PROC
> > @NEW_ID VARCHAR2(10)
> >
> > SELECT @NEW_ID = '123456'
> > RETURN 0
> >
> > I can call and read the value of NEW_ID.
> >
> > But if I have a procedure
> >
> > CREATE PROC TEST_PROC2
> >
> > DECLARE @RESULT
> > SELECT @RESULT = some_value
> >
> > RETURN @RESULT
> >
> > I can not read the returned value. The included documentation
> > recommends the syntax:
> >
> > {[?=]call procedure-name[([parameter][,[parameter]]...)]}
> > but the driver does not like it.
> > Does anyone know the proper syntax to get the returned value?
> >
> > Thanks
> >
> > Witold
> >
> > To change your membership options, refer to:
> > http://www.sys-con.com/java/list.cfm
> >
> >
> >
> > To change your membership options, refer to:
> > http://www.sys-con.com/java/list.cfm
>
>
>
> To change your membership options, refer to:
> http://www.sys-con.com/java/list.cfm
>
> To change your membership options, refer to:
> http://www.sys-con.com/java/list.cfm
==================================
Witold Iwaniec
Sr Software Developer
NovaLIS Technologies
[EMAIL PROTECTED]
http://www.novalistech.com
To change your membership options, refer to:
http://www.sys-con.com/java/list.cfm
To change your membership options, refer to:
http://www.sys-con.com/java/list.cfm
