wood burning stoves 2.0*
The moose likes SOA and ESB and the fly likes invoke SQL server 2008 stored procedure from java and mapping pojo with Custom Database type Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Java » SOA and ESB
Bookmark "invoke SQL server 2008 stored procedure from java and mapping pojo with Custom Database type " Watch "invoke SQL server 2008 stored procedure from java and mapping pojo with Custom Database type " New topic
Author

invoke SQL server 2008 stored procedure from java and mapping pojo with Custom Database type

gauri phadtare
Greenhorn

Joined: Oct 04, 2012
Posts: 1
Hi Friends,

I have declared a UDT(User Defined Types) say CustomType in SQL server 2008.I m using this Data type as an IN parameter for a Stored
procedure.I want to call this procedure from a java class.
To do this i have done the following:
created a class called CustomType which implements SQLData


public class CustomType implements SQLData {

String name ;
int year ;
String sql_type = "CustomType_t";

public String getSQLTypeName() throws SQLException {
return sql_type;
}

public void readSQL(SQLInput arg0, String type) throws SQLException {
sql_type = type;
name = arg0.readString() ;
year = arg0.readInt() ;

}


public void writeSQL(SQLOutput arg0) throws SQLException {
arg0.writeString(name) ;
arg0.writeInt(year) ;

}



}



My main java class looks like this:


public class Proc2Test {


public static void main(String[] args) {
msmsqlTest() ;
}

public static void msmsqlTest()
{

Connection con = null ;
CallableStatement cStatement = null ;
ResultSet rs = null ;
String url = "jdbc:sqlserver://localhost:1433;databaseName=testDB;username=abc;password=xyz";
String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
try
{
Class.forName(driver);
con = DriverManager.getConnection(url);
java.util.Map map = con.getTypeMap();
map.put("CustomType",
Class.forName("com..test.CustomType"));


con.setTypeMap(map);



cStatement = con.prepareCall("{call abc2(?,?)}") ;

CustomType x = new CustomType() ;

x.name = "ABC" ;
x.year = 20 ;

cStatement.setObject("para1", x, java.sql.Types.OTHER);
cStatement.registerOutParameter(2,Types.INTEGER) ;
rs = cStatement.executeQuery();
}
catch(Exception ex)
{
ex.printStackTrace() ;
}


}
}


when i run this code i get the following exception:

com.microsoft.sqlserver.jdbc.SQLServerException: This operation is not supported.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.NotImplemented(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.setTypeMap(Unknown Source)
at com.mindcraft.test.Proc2Test.msmsqlTest(Proc2Test.java:97)
at com.mindcraft.test.Proc2Test.main(Proc2Test.java:22)



This exception occurs at con.setTypeMap(map)


My problem is how do i call a store proc of SQL server, whose input parameter is a custom Database type?
How do i map the a pojo with custom database type of SQL server 2008 and send that to the stored proc ?
Does sql server 2008 jdbc driver implement Connection.setTypeMap() method?

Roger Sterling
Ranch Hand

Joined: Apr 06, 2012
Posts: 426

Moderator : you might like to move this question to the database forum. I'm not sure the question is related to SOA or ESB.
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: invoke SQL server 2008 stored procedure from java and mapping pojo with Custom Database type
 
Similar Threads
Stored Procedure and Java
Java to OODBMS code in Oracle --- Please help me out
Storing Objects using SQLData Interface
invoke SQL server 2008 stored procedure from java and mapping pojo with Custom Database type
Passing Nested Type from Java to DB