Java class in UDF for Trigger 1

[lespaul]

So, i've developed my java class and loaded it into the IFS. What I need to end up with is a trigger on a table that calls a function which calls the java class and runs the addCourtCost method of the class. (As is described on p. 325 of the 'Stored Procedures, Triggers and User Defined Functions on DB2 Redbook' )

Now I need to create a UDF that I can call from my trigger.

So, i'm trying to create the function and I keep getting errors.

CREATE FUNCTION LANDREWS.ASSESSDOCKETFEE ( 
	CASPRE varCHAR(2) , 
	CASNUM varCHAR(7) )
	RETURNS NULL
	EXTERNAL NAME 'java/utilities/Java_UDFs.addCourtCosts' 
	LANGUAGE JAVA ;

gives me error: [tt]Keyword EXTERNAL not expected. Valid tokens: ON[/tt]

I have tried moving the statements around, I have used examples from the web, I can't get it to create a function. I could get it to work as a procedure, but I'm pretty sure it needs to be a function to work like I want (if I am wrong please let me know!).

thanks for any insight!
Leslie

 

[MarcLodge]

Hi Leslie,

I think it may be your RETURNS NULL statement that it is objecting to. The manual that I looked at said:

RETURNS NULL ON NULL INPUT or CALLED ON NULL INPUT
This optional clause may be used to avoid a call to the external function if any of the arguments is null. If the user-defined function is defined to have no parameters, then of course this null argument condition cannot arise.
If RETURNS NULL ON NULL INPUT is specified and if at execution time any one of the function's arguments is null, the user-defined function is not called and the result is the empty table; that is, a table with no rows.

If CALLED ON NULL INPUT is specified, then at execution time regardless of whether any arguments are null, the user-defined function is called. It can return an empty table or not, depending on its logic. But responsibility for testing for null argument values lies with the UDF.

The value NULL CALL may be used as a synonym for CALLED ON NULL INPUT for backwards and family compatibility. Similarly, NOT NULL CALL may be used as a synonym for RETURNS NULL ON NULL INPUT.

The error message that you are getting seems to imply that it wants 'RETURNS NULL ON NULL INPUT' but I'm not sure, of course, if that is what you are after.

Hope this helps

Marc

 

[lespaul]

well, there is NO return, but from what I've read, the CREATE FUNCTION requires a return, so I just put null since there isn't one....I'll try your suggestion and see if I get anywhere!

Thanks,
Leslie

 

[lespaul]

Ok, so I made it a little further before it crashed!

Here's what I've got now:

CREATE FUNCTION LANDREWS.ASSESSDOCKETFEE ( 
	CASPRE varCHAR(2) , 
	CASNUM varCHAR(7) )
	RETURNS NULL ON NULL INPUT
	EXTERNAL NAME 'java/utilities/Java_UDFs.addCourtCosts' 
	LANGUAGE JAVA ;

and now the error message is:

[tt]Clause not correct for CREATE FUNCTION or CREATE PROCEDURE. Cause.....: A clause for CREATE PROCEDURE or CREATE FUNCTION is missing or not allowed. The reason code is 4. Reason codes and their meanings are: ....4 -- For CREATE PROCEDURE and CREATE FUNCTION, parameter style JAVA or DB2GENERAL can only be specified for LANGUAGE JAVA.[/tt]

any idea what that means?

Leslie

 

[Mercury2]

Look here.

 

[lespaul]

Thanks, I went to the IBM site before I posted....But just to humor myself, I took the example from that link:
[tt]Example 4: The following example defines a Java user-defined function that returns the position of the first vowel in a string. The user-defined function is written in Java, is to be run fenced, and is the FINDVWL method of class JAVAUDFS.[/tt]

CREATE FUNCTION FINDV (VARCHAR(32000))
RETURNS INTEGER
FENCED
LANGUAGE JAVA
PARAMETER STYLE JAVA
EXTERNAL NAME 'JAVAUDFS.FINDVWL'
NO EXTERNAL ACTION
CALLED ON NULL INPUT
DETERMINISTIC
NO SQL

tweaked it to my needs:

CREATE FUNCTION LANDREWS.ASSESSDOCKETFEE ( 
CASPRE varCHAR(2) , 
CASNUM varCHAR(7) )
RETURNS NULL ON NULL INPUT
FENCED
LANGUAGE JAVA
PARAMETER STYLE JAVA
EXTERNAL NAME 'java/utilities/Java_UDFs.addCourtCosts'
NO EXTERNAL ACTION
CALLED ON NULL INPUT
DETERMINISTIC
NO SQL

and I get the error message:
[tt]
SQL State: 42601
Vendor Code: -104
Message: [SQL0104] Token CALLED was not valid. Valid tokens: LANGUAGE GENERAL SIMPLE SPECIFIC NOT DETERMINISTIC CONTAINS NO READS MODIFIES. Cause . . . . . : A syntax error was detected at token CALLED. Token CALLED is not a valid token. A partial list of valid tokens is LANGUAGE GENERAL SIMPLE SPECIFIC NOT DETERMINISTIC CONTAINS NO READS MODIFIES. This list assumes that the statement is correct up to the token. The error may be earlier in the statement, but the syntax of the statement appears to be valid up to this point. Recovery . . . : Do one or more of the following and try the request again: -- Verify the SQL statement in the area of the token CALLED. Correct the statement. The error could be a missing comma or quotation mark, it could be a misspelled word, or it could be related to the order of clauses. -- If the error token is <END-OF-STATEMENT>, correct the SQL statement because it does not end with a valid clause.[/tt]

You would think that an example taken directly from the knowledge base would work, but as I have found in trying to learn the intricacies of the AS400 over the past 7 years, I have found that IBM doesn't always (read: never) succeeds at providing any assistance.

So, again, if someone knows what I can do to fix my CREATE FUNCTION process, I would appreciate your help.

Leslie

Leslie

Have you met Hardy Heron?

 

[Mercury2]

But you should use either RETURNS NULL ON NULL INPUT or CALLED ON NULL INPUT not both. In the IBM example above only one is declared not both. Give it a new try.

 

[lespaul]

Ok, so I tried both of these statements.

Removed RETURNS line 4:

CREATE FUNCTION LANDREWS.ASSESSDOCKETFEE ( 
CASPRE varCHAR(2) , 
CASNUM varCHAR(7) )
FENCED
LANGUAGE JAVA
PARAMETER STYLE JAVA
EXTERNAL NAME 'java/utilities/Java_UDFs.addCourtCosts'
NO EXTERNAL ACTION
CALLED ON NULL INPUT
DETERMINISTIC
NO SQL

Error message:[tt] Clause not correct for CREATE FUNCTION or CREATE PROCEDURE. Cause . . . . . : A clause for CREATE PROCEDURE or CREATE FUNCTION is missing or not allowed. The reason code is 1. Reason codes and their meanings are: 1 -- For CREATE FUNCTION, the RETURNS clause is required. [/tt]

added RETURNS clause back in and removed CALLED ON NULL INPUT:

CREATE FUNCTION LANDREWS.ASSESSDOCKETFEE ( 
CASPRE varCHAR(2) , 
CASNUM varCHAR(7) )
RETURNS NULL ON NULL INPUT
FENCED
LANGUAGE JAVA
PARAMETER STYLE JAVA
EXTERNAL NAME 'java/utilities/Java_UDFs.addCourtCosts'
NO EXTERNAL ACTION
DETERMINISTIC
NO SQL

Error message:[tt]Clause not correct for CREATE FUNCTION or CREATE PROCEDURE. Cause . . . . . : A clause for CREATE PROCEDURE or CREATE FUNCTION is missing or not allowed. The reason code is 1. Reason codes and their meanings are: 1 -- For CREATE FUNCTION, the RETURNS clause is required.[/tt]

Leslie

Have you met Hardy Heron?

 

[Mercury2]

Basically for an external scalar function the RETURNS clause is indeed required, f.e.

RETURNS VARCHAR(7) -- or whatever the function returns

This one works with either RETURNS NULL ON NULL INPUT or CALLED ON NULL INPUT but not both anyway.

CREATE FUNCTION LANDREWS.ASSESSDOCKETFEE ( 
       CASPRE VARCHAR(2),                            
       CASNUM VARCHAR(7) )                            
[b]RETURNS VARCHAR(7)[/b]                                    
LANGUAGE JAVA                                         
PARAMETER STYLE JAVA                                  
CALLED ON NULL INPUT                                  
DETERMINISTIC                                         
NO SQL                                                
NO EXTERNAL ACTION                                    
FENCED                                                
EXTERNAL NAME 'JAVA/UTILITIES/JAVA_UDFS.ADDCOURTCOSTS'

 

[lespaul]

so does it matter that there really isn't a return? Can I use this and it won't choke that nothing is returned?

Thanks,
Leslie

 

[Mercury2]

Not sure but you could declare a returned dummy value matching the UDF RETURNS keyword in the Java class. Never tried that.

I'm still wondering whether you do not need a stored procedure instead.

 

[lespaul]

We have not given up on a stored procedure, but we are trying to do as much as we can in Java so it's portable to the new case management system that will be implemented in the next year or so. The new system is a proprietary system on MS SQL, so if we go with a stored procedure we would have to redo this entire process when we convert whereas if we have a java program it can just be used.

In order to get the trigger to accept the function I did have to declare a return:

CREATE TRIGGER LANDREWS.PLEA_FIND_ASSESS_CC
AFTER UPDATE OF TRFIND, PLEACD ON LANDREWS.CMPCHGMF 
REFERENCING OLD o NEW n 
FOR EACH ROW  
MODE DB2ROW  
BEGIN ATOMIC
DECLARE returnValue VARCHAR(7);
    if o.Pleacd<>n.pleacd OR o.Trfind<>n.Trfind then
    	set returnValue = landrews.assessdocketfee(o.caspre, o.casnum);
     end if;    
END;

now the error message is:
[tt]ASSESSDOCKETFEE in LANDREWS type *N not found. Cause . . . . . : ASSESSDOCKETFEE in LANDREWS type *N was not found. If this is an ALTER TABLE statement and the type is *N, a constraint or partition was not found. If this is not an ALTER TABLE statement and the type is *N, a function, procedure, or trigger was not found. If a function was not found, ASSESSDOCKETFEE is the service program that contains the function. The function will not be found unless the external name and usage name do not match exactly. Examine the job log for a message that gives more details on which function name is being searched for and the name that did not match. Recovery . . . : Change the name and try the request again. If the object is a node group, ensure that the DB2 Multisystem product is installed on your system and create a nodegroup with the CRTNODGRP CL command. If an external function was not found, be sure that the case of the EXTERNAL NAME on the CREATE FUNCTION statement exactly matches the case of the name exported by the service program.[/tt]

pay particular attention to the bolded line. It won't be found unless the names DO NOT match? I don't get that...too many negatives! I did verify that the function was created in my library. I've looked in the job log and the only "details" available is this exact error message!

At this point it's looking like I'm going to have to have the java program, a delphi program AND a stored procedure!

Thanks for all your help!
Leslie

Leslie

Have you met Hardy Heron?

 

[Mercury2]

Hmm.. Try out this

- CHGCURLIB MyLibrary
(MyLibrary is where the function assessdocketfee resides)

- if o.Pleacd<>n.pleacd OR o.Trfind<>n.Trfind then
set returnValue = assessdocketfee(o.caspre, o.casnum);
(remove library name "landrews" on the stm above)

I gave a quick try on my V5R3 machine and that works like a breeze.

A side note.
BTW a stored procedure can be written in Java as well as any other supported HLL language. You should not need to return a value either.
Check out

http://tinyurl.com/3plglf

 

[lespaul]

BTW a stored procedure can be written in Java as well as any other supported HLL language.

Ok so how would I do that? I am trying to follow the directions from your link (of which I actually have the book with pages and everything!) and the first thing that I see it says is:

The compiled code must be loaded into the function directory, /QIBM/User Data/OS400/SQLLib/Function, of the iSeries server

I looked and we don't have that directory...at least not in the IFS...would it be some where else?

 

[Mercury2]

I looked and we don't have that directory...at least not in the IFS...

Are you really sure of it ?

I didn't read the related excerpt but if you are sure the directories are missing you have to create them with MKDIR command from the /QIBM Root file system from a green screen.
Using iSeries Navigator is likely the best way to do so though.

From your desktop open Iseries Nav>Relevant System Name>File Systems>Integrated File System>Root>QIBM>UserData>etc...
and load the SP in Function directory.

BTW does the function work w/o qualifying the Java Class ?

 

[lespaul]

BTW does the function work w/o qualifying the Java Class ?

No, I haven't been able to get the function to work. I wasn't quite sure where in the function the CHGCURLIB call should go, I've tried it before the BEGIN and in the function,

CREATE TRIGGER LANDREWS.PLEA_FIND_ASSESS_CC 
AFTER UPDATE OF TRFIND, PLEACD ON LANDREWS.CMPCHGMF 
REFERENCING OLD o NEW n 
FOR EACH ROW  
MODE DB2ROW
BEGIN ATOMIC
     chgcurlib landrews ;
     DECLARE returnValue VARCHAR(7);
      if o.Pleacd<>n.pleacd OR o.Trfind<>n.Trfind then
     	set returnValue = assessdocketfee(o.caspre, o.casnum);
     end if;    
END;

but no matter where it is I get the error:
[tt]Token LANDREWS was not valid. Valid tokens: :. Cause . . . . . : A syntax error was detected at token LANDREWS. Token LANDREWS is not a valid token. A partial list of valid tokens is :. This list assumes that the statement is correct up to the token. The error may be earlier in the statement, but the syntax of the statement appears to be valid up to this point. Recovery . . . : Do one or more of the following and try the request again: -- Verify the SQL statement in the area of the token LANDREWS. Correct the statement. The error could be a missing comma or quotation mark, it could be a misspelled word, or it could be related to the order of clauses. -- If the error token is <END-OF-STATEMENT>, correct the SQL statement because it does not end with a valid clause.
[/tt]

I was able to find the QIBM folder in the IFS Root directory. Now is there anything special I need to do to my java class? Here's the .java file:

package javaUDF;

import java.sql.*;

public class Java_UDFs {
	private static jdbcAccess datasource;
	private static Connection conn;
	private static Statement sqlstmt;
	private static String library = "CMLIB.";
	private static String library2 = "LANDREWS.";
	
	public static void addCourtCosts(String CasPre, String CasNum) {

		try {
			datasource = new jdbcAccess();
			datasource.connect();
			conn = datasource.getConnection();
			sqlstmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

			String stmt = "SELECT COUNT(*) As DocketFee FROM " + library + "CMPSNTNC WHERE DKTTYP = 'CC' AND CASPRE = '" + CasPre + "' AND CASNUM = " + CasNum;		    
			ResultSet rs = sqlstmt.executeQuery(stmt);
			rs.next();


			if (rs.getString("DocketFee").equalsIgnoreCase("0")) //add check for felony charge exit if found
			{
				System.out.println(CasPre + CasNum);
				System.out.println("No fee assessed - checking next criteria");
				stmt = "SELECT COUNT(*) As NonPAChgs FROM " + library + "CMLDKTCC C INNER JOIN " + library + "CMPSTAMFE E ON C.STATUT = E.STATUT AND C.CHGABV = E.CHGABV WHERE PENALTY_ASSESS <> 'Y' AND CASPRE = '" + CasPre + "' AND CASNUM = " + CasNum;				
				rs = sqlstmt.executeQuery(stmt);
				rs.next();
				if (!rs.getString("NonPAChgs").equalsIgnoreCase("0")) {
					AssessFee(CasPre, CasNum);
					return;
				}
				else {				
					stmt = "SELECT * FROM " + library + "CMPCHGMF C INNER JOIN " + library + "CMPSTAMFE E ON C.STATUT = E.STATUT AND C.CHGABV = E.CHGABV WHERE PENALTY_ASSESS = 'Y' AND MADEUPCHG <> 'Y' AND CASPRE = '" + CasPre + "' AND CASNUM = " + CasNum;				
					rs = sqlstmt.executeQuery(stmt);
					while (rs.next())
					{
						System.out.println("Checking all charge records");
						if ((!rs.getString("TRFIND").equalsIgnoreCase("DI")) && (!rs.getString("TRFIND").equalsIgnoreCase("NP")) && (!rs.getString("TRFIND").equalsIgnoreCase("")) && (!rs.getString("TRFIND").equalsIgnoreCase("NA"))){
							if (rs.getString("PENALTY_ASSESS").equalsIgnoreCase("Y")) {
								if (rs.getString("PLEACD").equalsIgnoreCase("NG")){
									AssessFee(CasPre, CasNum);
									return;
								}
							}
							else {
								if (!rs.getString("TRFIND").equalsIgnoreCase("")) {
									AssessFee(CasPre, CasNum); 
								}
							}

						}
					}
				}

			}
			else {
				System.out.println("Fee already assessed on case");
				return;
			}
		}
		catch (SQLException sql)
		{
			System.out.println("addCourtCosts - Sql Exception: " + sql.getMessage());
		}
	}

	private static void AssessFee(String CasPre, String CasNum) {
		try {
			System.out.println("Going to AssessFee to " + CasPre+CasNum);
			String stmt = "SELECT DISTINCT CASPRE, CASNUM, DEFSEQ, CHGSEQ, HERTYP ,HERDAT, STSCOD, STSDAT, 'CC', 20, NDDATE, " +
			"LUPDAT, 'JAVA_CC', LUPDATE, HERNGDAT, STATSDAT, ENDDATE FROM " + library + "CMPSNTNC WHERE CASPRE = '" + 
			CasPre + "' AND CASNUM = " + CasNum + " AND DATE(SUBSTRING(HERNGDAT, 5, 2) || '/' || SUBSTRING" +
			"(HERNGDAT, 7, 2) || '/' || SUBSTRING(HERNGDAT, 1, 4)) = CURDATE()";
			ResultSet rs = sqlstmt.executeQuery(stmt);
			if (!rs.last()){
				stmt = "INSERT INTO " + library2 + "CMPSNTNC (CASPRE, CASNUM, DEFSEQ, CHGSEQ, HERTYP ,HERDAT, STSCOD, STSDAT, DKTTYP, AMTSET, NDDATE, LUPDAT, TUSER, LUPDATE, HERNGDAT, STATSDAT, ENDDATE) (SELECT DISTINCT CASPRE, CASNUM, DEFSEQ, CHGSEQ, HERTYP ,HERDAT, STSCOD, STSDAT, 'CC', 20, NDDATE, " +
			"LUPDAT, 'JAVA_CC', LUPDATE, HERNGDAT, STATSDAT, ENDDATE FROM " + library + "CMPSNTNC WHERE CASPRE = '" + 
			CasPre + "' AND CASNUM = " + CasNum + " AND DATE(SUBSTRING(HERNGDAT, 5, 2) || '/' || SUBSTRING" +
			"(HERNGDAT, 7, 2) || '/' || SUBSTRING(HERNGDAT, 1, 4)) = CURDATE())";
			sqlstmt.executeUpdate(stmt);
			}
			else {
				stmt = "INSERT INTO " + library2 + "CMPSNTNC (CASPRE, CASNUM, DEFSEQ, CHGSEQ, HERTYP ,HERDAT, STSCOD, " +
						"STSDAT, DKTTYP, AMTSET, NDDATE, LUPDAT, TUSER, LUPDATE, HERNGDAT, STATSDAT, ENDDATE) " +
						"(SELECT CASPRE, CASNUM, DEFSEQ, MIN(CHGSEQ), HERTYP, HERDAT, 1, HERDAT, 'CC', 20, 0, HERDAT, " +
						"'JAVA_CC', HERNGDAT, HERNGDAT, HERNGDAT, 0 FROM " + library + "CMPHERMF WHERE CASPRE = '" + 
						CasPre + "' AND CASNUM = " + CasNum + "AND DATE(SUBSTRING(HERNGDAT, 5, 2) || '/' || " +
						"SUBSTRING(HERNGDAT, 7, 2) || '/' || SUBSTRING(HERNGDAT, 1, 4)) = CURDATE() GROUP BY CASPRE, " +
						"CASNUM, DEFSEQ, HERTYP, HERDAT, 1, HERDAT, 'CC', 20, 0, HERDAT, 'JAVA_CC', HERNGDAT, HERNGDAT" +
						", HERNGDAT, 0) ";
				sqlstmt.executeUpdate(stmt);
			}
		}
		catch (SQLException sql)
		{
			System.out.println("AssesingFee - Sql Exception: " + sql.getMessage());
		}
	}

}

Thanks again for all your assistance, I am not (obviously) an AS400 expert and we are under a time deadline to get this working!

Leslie

 

[Mercury2]

Use "chgcurlib landrews" on a green screen command line in the same iSeries job/session as the function is running.

Now is there anything special I need to do to my java class?

You're WAY better in JAVA then I ever will be.
Give it a try, check out again the previous mentionned Redbook to know the know-how and ... go back here with a list of error messages B-)