This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
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 Android Security Essentials Live Lessons this week in the Android 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.
 
Consider Paul's rocket mass heater.
 
subject: invoke SQL server 2008 stored procedure from java and mapping pojo with Custom Database type
 
Similar Threads
Stored Procedure and Java
invoke SQL server 2008 stored procedure from java and mapping pojo with Custom Database type
Java to OODBMS code in Oracle --- Please help me out
Storing Objects using SQLData Interface
Passing Nested Type from Java to DB