Re: Scott's JDBCR4 program from RPG

1. The SQL Return statement in the SQL Stored Procedure.
IF (
-- Company Must exist
(@CompanyId IS NULL)
-- Violation Class must be valid and a manual class (No system classes
allowed)
OR (NOT @violationClass in (SELECT Code FROM rl_violation_class WHERE
ViolationType = 'M' AND CompanyId = @CompanyId))
-- Violation Sub Class must be valid
OR (NOT @violationSubClass in (SELECT SubClassCode FROM
rl_violation_sub_class WHERE CompanyId = @CompanyId AND ViolationClassCode =
@ViolationClass AND SubClassCode = @ViolationSubClass))
-- Must have start and finish times
OR (@violationStart IS NULL) OR (@violationStart = '')
OR (@violationFinish IS NULL) OR (@violationFinish = '')
-- Must have comment
OR (@violationComment IS NULL) OR (@violationComment = '')
)
RETURN -1 -- FAILURE


2. I am getting this error which is the same as the one mentioned in the
link.



Message ID . . . . . . : RNX0301 Severity . . . . . . . :
50
Message type . . . . . :
Escape
Date sent . . . . . . : 06/11/09 Time sent . . . . . . :
16:38:43


Message . . . . : Java exception received when calling Java
method.
Cause . . . . . : RPG procedure JDBC_Execu in program ALANC/XVJDBC
received
Java exception "com.microsoft.sqlserver.jdbc.SQLServerException: String
or
binary data would be truncated." 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


Statement = JDBC_PrepareCall(Connection
:
'{call
UpsertViolation(?,?,?,?,?,?,?)}');


JDBC_SetString(Statement :
1 :
%Trim(InCompany));
JDBC_SetString(Statement :
2 :
%Trim(InDriver ));
JDBC_SetString(Statement :
3 :
%Trim(InStartDate));
JDBC_SetString(Statement :
4 :
%Trim(InFinishDate));
JDBC_SetString(Statement :
5 :
%Trim(InClass ));
JDBC_SetString(Statement :
6 :
%Trim(InSubClass));
JDBC_SetString(Statement :
7 :
%Trim(InComment));

JDBC_ExecuteCall(Statement);


On Thu, Jun 11, 2009 at 4:36 PM, Scott Klement <rpg400-l@xxxxxxxxxxxxxxxx>wrote:

Alan Campin wrote:

1. If the SQL Server stored procedure returns a value with a Return 0
statement in the stored procedure how do you get the value of the

parameter?

Do you mean the RPG REturn op-code? Or the SQL Return statement?

An RPG return op-code can't be used from a stored procedure, it can only
be used from a UDF. If you are writing a UDF, then the output it
returned to an SQL statement that called the UDF (such as a SELECT
statement) and you'd get the output by reading the results of that SELECT.

If it's the SQL RETURN statement, then you use the GET DIAGNOSTICS SQL
statement to retrieve the result. Personally, I've never used that
feature (but that's what the manual says.)

2. I am running into problems trying to call a stored procedure with
parameters coming from variables.

You didn't say what the problem is. Just that you're having problems,
and that you found out that there's some sort of a bug in the service
program...

Kindly tell us what the problem is, and what the bug is in the service
program, and I'll see if I can help.

--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.