CODEWITHSUNDEEP
javasql-serverstored-proceduresjdbc
ЭмЭрИкс_007
ЭмЭрИкс_007

Reputation: 156

send and read VARCHAR to a procedure in SQLSERVER from jdbc

I have a procedure with the following structure, as on the picture. it accepts and issues varchar.

enter image description here

I do in java code like this:

public static void main(String[] args) {
        Connection con = null;
        CallableStatement cstmt;
        try{
            Class.forName(JDBC_DRIVER);

            System.out.println("connect...");
            String connectionUrl = "jdbc:sqlserver://0.0.0.0:0000;" +"databaseName=blabla-blabla;user=blabla;password=blabla";
            con = DriverManager.getConnection(connectionUrl);

            cstmt = con.prepareCall("{call xml_parser(?)}");
            cstmt.setString (1, "<xml><action>login</action><login>test</login><password>147852</password></xml>");
            cstmt.registerOutParameter(1, java.sql.Types.VARCHAR);
            cstmt.execute();   

            //here I get always 0
            String s = cstmt.getString(1);
            System.out.println(s);
            cstmt.close();
        }catch(SQLException se){
            se.printStackTrace();
        }
        System.out.println("exit......!");
    }

I always get 0. And I do not even know if the data is going away. lines from the procedure : 

CREATE PROCEDURE [dbo].[xml_parser] 
@request varchar(max) =  '<xml>
    <action>login</action>
    <login>1</login>
    <password>1</password>
</xml>',
@responce varchar(max) = '' output
AS

begin

Upvotes: 0

Views: 525

Answers (1)

Mark Rotteveel
Mark Rotteveel

Reputation: 108988

Your stored procedure has two parameters, one IN and one OUT, not a single IN/OUT parameter.

In other words, you need to use {call xml_parser(?,?)} and use setString on index 1 and registerOutParameter on index 2 (and getString on index 2).

Upvotes: 1

Related Questions