Using Scott Klement's JDBCR4 service program

Hi, I am having a problem using the JDBCR4 service program to execute a Microsoft SQL Server stored procedure on a 2005 Server. I have a CL program setting up the Java environment which works just fine, the code is below:

RMVENVVAR ENVVAR(JAVA_HOME) LEVEL(*JOB)
MONMSG CPF0000

RMVENVVAR ENVVAR(CLASSPATH) LEVEL(*JOB)
MONMSG CPF0000

ADDENVVAR ENVVAR(JAVA_HOME) +
VALUE('/QIBM/ProdData/Java400/jdk6') +
LEVEL(*JOB)

ADDENVVAR ENVVAR(CLASSPATH) +
VALUE('.:/home/SOURCEB/bin:/home/SOURCEB+
/bin/sqljdbc4.jar') +
LEVEL(*JOB)


I have no problems in the RPG program until... I can Connect to the MSSQL database using the necessary credentials. I also have no problem executing the PrepCall. But when I try to execute the stored procedure, the fourth parameter is an XML type Output parameter. I get an Error saying the Xml data type cannot be converted to nvarchar. Does anyone know how to describe an Xml data type with the Jdbc_registeroutparameter() procedure. I included the code that I wrote for proof of concept. It will be enhanced after knowing I can get some row sets back. I also included the As400 error message below.

// - - - - - - - - - - - - - - - - - - - - -
// Load Variables for Connection to Database
// - - - - - - - - - - - - - - - - - - - - -
UserId = 'uuuuuuuuu’;
Password = 'pppppppp';
DataBase = 'CargasEnergySandbox';
//
Driver = 'com.microsoft.sqlserver.jdbc.SQLServerDriver';
Url = 'jdbc:sqlserver://SQLSERVER';
//
prop = JDBC_Properties();
JDBC_setProp(prop: 'User' : %trim(UserId));
JDBC_setProp(prop: 'Password': %trim(PassWord));
JDBC_setProp(prop: 'DatabaseName': %trim(Database));

conn = JDBC_ConnProp( %Trim(Driver)
: %trim(Url)
: Prop );
JDBC_freeProp(prop);

if (conn = *NULL);
return;
endif;

//- - - - - - - - - - - - - - - - -
// Prepare the stored-proc statement
//- - - - - - - - - - - - - - - - -

Sql = 'API.UpdateTicketMessage ?,?,?,?';

stmt = jdbc_PrepCall( conn
: %Trim(Sql)
);

if (stmt = *NULL);
return;
endif;
//
parm1 = 'WACHE';
//parm2 = '0';
parm3 = 'Test Ticket Message From JDBC4';
// jdbc_setChar (stmt: 1: parm1);
jdbc_setString (stmt: 1: parm1);
jdbc_setInt (stmt: 2: 363355);
jdbc_setString (stmt: 3: parm3);
jdbc_setString (stmt: 4: parm4);
jdbc_registerOutParameter (stmt: 4: 12); // 12 is Type.VARCHAR

rc = jdbc_ExecCall(stmt);


jdbc_commit(conn);

jdbc_freeCallStmt(stmt);

jdbc_close(conn);

return;

/end-free

I get the following As400 error when calling this program:

Message ID . . . . . . : RNX0301 Severity . . . . . . . : 50
Message type . . . . . : Escape
Date sent . . . . . . : 10/14/13 Time sent . . . . . . : 08:22:07

Message . . . . : Java exception received when calling Java method.
Cause . . . . . : RPG procedure JDBC_EXECC in program WACHE/JDBCR4 received
Java exception "com.microsoft.sqlserver.jdbc.SQLServerException: Implicit
conversion from data type xml to nvarchar" when calling method "execute"
with signature "()Z" in class "java.sql.CallableStatement".
Recovery . . . : Contact the person responsible for program maintenance to
determine the cause of the problem.
Technical description . . . . . . . . : If the exception indicates that the
Java class was not found, ensure the class for the method is in the class
path. If the exception indicates that the Java method was not found, check
the method name and signature. If the signature is not correct, change the
RPG prototype for the method, or change the Java method, so that the return
More...
Press Enter to continue.



Thanks in advance,


Wayne Achenbaum
MIS Department
Slomin's Inc.
516-932-7024 Ext. 2212
wache@xxxxxxxxxxx



This email message is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. This email message does not constitute a legal and binding agreement unless signed and authorized by an executive of the company. If you have received this email in error, please contact our email administrator directly by sending an email to Postmaster@xxxxxxxxxxx<mailto:Postmaster@xxxxxxxxxxx>.

Licensed by: NYS Dept of State License # 12000021686, CT # 00106209 , NJ # 34BF00014900, PA # 004462, DE # 95-107, MD # 107548, DC # 39936122, VA DCJS# 11-3014, GA # LVA205715.