Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » Calling a stored procedure with BLOB input parameter
Calling a stored procedure with BLOB input parameter [message #754186] Tue, 01 November 2011 09:18 Go to next message
Ronny is currently offline RonnyFriend
Messages: 10
Registered: July 2009
Junior Member
I have a store procedure in DB2

CREATE OR REPLACE PROCEDURE INSERT_MSG (
I_MSG_ID INTEGER,
I_MSG_DATA BLOB
)
DYNAMIC RESULT SETS 0
LANGUAGE SQL
BEGIN
INSERT INTO MSG_TABLE(MSG_ID, MSG_DATA)
VALUES (I_MSG_ID, I_MSG_DATA);
END

and xml

<named-stored-procedure-query
name="INSERT_MSG"
procedure-name="INSERT_MSG"
returns-result-set="false"
call-by-index="true">
<parameter
name="I_MSG_ID"
direction="IN"
query-parameter="I_MSG_ID"
type="Integer"
/>
<parameter
name="I_MSG_DATA"
direction="IN"
query-parameter="I_MSG_DATA"
type="byte[]"
/>
</named-stored-procedure-query>

java code:
Query q = em.createNamedQuery("INSERT_MSG");
q.setParameter("I_MSG_ID", msgId);
q.setParameter("I_MSG_DATA", msgData);
q.executeUpdate();

I got the DB2 SQLERROR

Input data type mismatch, see chained exceptions; will retry execution using describe input information.
Please change application to use an input data type that matches the database column type as required by JDBC semantics. ERRORCODE=4461, SQLSTATE=01000:01000 - com.ibm.db2.jcc.b.SqlException: DB2 SQL Error: SQLCODE=-301 ....

Though record can be inserted but how do I resolve this error message, do I need to tell Blob in parameter?

Thanks in advance.
Re: Calling a stored procedure with BLOB input parameter [message #755208 is a reply to message #754186] Mon, 07 November 2011 16:39 Go to previous message
James Sutherland is currently offline James SutherlandFriend
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

Try setting the type to java.sqlBlob

<parameter
name="I_MSG_DATA"
direction="IN"
query-parameter="I_MSG_DATA"
type="java.sql.Blob"
/>

you can also set the JDBC type,

<parameter
name="I_MSG_DATA"
direction="IN"
query-parameter="I_MSG_DATA"
type="byte[]"
jdbc-type="2004"
/>


James : Wiki : Book : Blog : Twitter
Re: Calling a stored procedure with BLOB input parameter [message #755209 is a reply to message #754186] Mon, 07 November 2011 16:39 Go to previous message
James is currently offline JamesFriend
Messages: 272
Registered: July 2009
Senior Member
Try setting the type to java.sqlBlob

<parameter
name="I_MSG_DATA"
direction="IN"
query-parameter="I_MSG_DATA"
type="java.sql.Blob"
/>

you can also set the JDBC type,

<parameter
name="I_MSG_DATA"
direction="IN"
query-parameter="I_MSG_DATA"
type="byte[]"
jdbc-type="2004"
/>
--
James : http://wiki.eclipse.org/EclipseLink : http://en.wikibooks.org/wiki/Java_Persistence : http://java-persistence-performance.blogspot.com/
Previous Topic:Audit trail
Next Topic:mysterious logging
Goto Forum:
  


Current Time: Mon Dec 22 00:52:37 GMT 2014

Powered by FUDForum. Page generated in 0.01530 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software