Friday, October 24, 2008

Parse a string of XML into a DOM Document object

Java code:

String xmlStr = "something ";
DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
DocumentBuilder builder = factory.newDocumentBuilder();

//This handles string containg special characters (UTF8):
Document doc = builder.parse(new InputSource(new StringReader(xmlStr)));

//This cannot handle UTF8
InputStream is = new ByteArrayInputStream(content.getBytes());
Document doc = builder.parse(is);

Thursday, October 16, 2008

UDF using RPG/RPGLE


(1) create service program

0001.00 H NOMAIN
0001.01 D* Prototype for procedure: ONHAND
0002.00 D ONHAND PR 11P 2
0003.00 D UPBAL 7P 0 CONST
0004.00 D ISSUE 7P 0 CONST
0005.00 D AJUST 7P 0 CONST
0005.01 *------------------------------------------------
0006.00 P ONHAND B EXPORT
0006.01 D ONHAND PI 11P 2
0007.00 D UPBAL 7P 0 CONST
0007.01 D ISSUE 7P 0 CONST
0007.02 D AJUST 7P 0 CONST
0007.04 *
0007.05 D ONHND S 11P 2
0007.06 /free
0007.07 ONHND = UPBAL + ISSUE + AJUST;
0007.08 RETURN ONHND;
0007.09 /end-free
0008.00 P ONHAND E

(2) CRTMOD
(3) CRTSRVPGM SRVPGM(MRCJAVALIB/ONHAND) EXPORT(*ALL)
(4) Create function

create function mrcjavalib/ONHAND (DEC (7,0),DEC (7,0), DEC (7,0)) returns DEC (11,2)
language rpgle
deterministic
no sql
returns null on null input
no external action allow parallel
simple call
external name 'MRCJAVALIB/ONHAND(ONHAND)'

(5) to use
SELECT PRDNO, OPBAL, ISSUE, RECPT, ADJST, onhand(opbal, issue,adjst)
FROM dmpmp100



Thursday, July 24, 2008

Use Java UDF on AS400/iSeries

1. Create a Java program and put it in /qibm/userdata/os400/sqllib/function
2. Define UDF function
3. Use it in SQL

Note this Java UDF is about 30 times slower than its RPG counterpart.

Example:

/*
* MrcUDFs.java Created on Jul 24, 2008, 10:57:29 AM
//
// The Java Regular Expressions library requires version 1.4 of
// the JDK to be installed AND active.
//
//
// For the iSeries, this class must be placed in folder:
// /QIBM/USERDATA/OS400/SQLLIB/FUNCTION
//
// To Compile (assuming UDFs.java is in folder /myfolder):
//
// 1. Start QShell (QSH)
// 2. Use the javac command as follows (the Java file name is case sensitive!):
// javac -d /qibm/userdata/os400/sqllib/function UDFs.java
//
// For optimal performance on the iSeries, issue the CRTJVAPGM
// command from the command line:
//
// CRTJVAPGM CLSF('/qibm/userdata/os400/sqllib/function/UDFs.class')
// OPTIMIZE(40)
*/
import COM.ibm.db2.app.*; // Class for using Java methods with SQL UDFs
//import com.ibm.db2.app.*; // this only compile on AS400
import java.math.BigDecimal; // For compatability with SQL DEC data type

public class MrcUDFs {

public MrcUDFs() {

}

public static BigDecimal convertNumber(BigDecimal number) {

double d = number.doubleValue();
double converted = 0;
if (d < 100000) {
converted = d + 11;
} else if (d < 100100) {
converted = d + 22;
} else if (d < 100200) {
converted = d + 33;
} else if (d < 100300) {
converted = d + 44;
} else if (d < 100800) {
converted = d + 88;
} else {
converted = d + 99;
}
return new BigDecimal(converted);
}
}

/*
CREATE FUNCTION MRCJAVALIB/cvt1(Amount DEC(6,0)) RETURNS DEC(6,0)
EXTERNAL NAME 'MrcUDFs.convertNumber'
LANGUAGE Java
PARAMETER STYLE Java
FENCED
NO SQL
RETURNS NULL ON NULL INPUT
Function CVT1 was created in MRCJAVALIB.

Now you can query it using:
SELECT ordnum, ORDLIN, PRDNO, MRCJAVALIB.CVT1(cusno) "
" FROM MRCJAVALIB.ORDERHIS2 "
+ " ORDER BY MRCJAVALIB.cvt1(cusno)"
+ " fetch first 20000 rows only";
*/

Tuesday, April 15, 2008

Log4j appender file

### direct log messages to stdout and file ###
log4j.rootLogger=info, stdout, R

log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.Target=System.out
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%d %-5p %c:%L - %m%n

log4j.appender.R=org.apache.log4j.RollingFileAppender
log4j.appender.R.File=c:/temp/testlog4j.txt
log4j.appender.R.MaxFileSize=1MB
# Keep one backup file
log4j.appender.R.MaxBackupIndex=2
log4j.appender.R.layout=org.apache.log4j.PatternLayout
log4j.appender.R.layout.ConversionPattern=%d %-5p %c:%L - %m%n

log4j.logger.com.mrc=info

Tuesday, March 18, 2008

gmail and JSSE on AS400/iSeries

The following gmail program runs on PC without any problem but failed to run on AS400. I get "certificate container *SYSTEM could not be accessed" error.

To solve this problem. Start HTTP admin on port 2001. In Digital Certificate Manager use Create New Certificate Store to create a certificate for *SYSTEM. 2 files are created:

/QIBM/userdata/ICSS/Cert/Server/default.rdb

/QIBM/userdata/ICSS/Cert/Server/default.kdb

Change their object authorities to *RWX

That's it.

When creating the certificate I was given a Certificate Request which I did not have to use:

he certificate request data is shown below. Copy and paste the request data, including both the Begin request and End request lines, into the form that the Certificate Authority (CA) provided.

Warning: If you exit this page, the certificate request data is lost. Therefore, make sure you carefully copy and paste the data into the Certificate Authority (CA) form or into a file for later use.


-----BEGIN NEW CERTIFICATE REQUEST-----
MIIBjTCB9wIBADBOMQswCQYDVQQGEwJVUzERMA8GA1UECBMIaWxsaW5vaXMxEDAO
BgNVBAcTB2xvbWJhcmQxDDAKBgNVBAoTA21yYzEMMAoGA1UEAxMDbXJjMIGfMA0G
CSqGSIb3DQEBAQUAA4GNADCBiQKBgQCjTKF+oI03RpufNRDaM/9MvQlfOIp02uFG
QHPnuYieahMZ3xaXnatXzSXrT54dxUhRpVXixD1YdDygQyOzRt7YXAV3zsS4a8i1
ydGaN9hezU/UwLZjEoHGlPYIusQQhkxUiG5VKdPvqIZ7Xo/2amCVGr7VJVMZJg2b
E0RKB4ZCFQIDAQABoAAwDQYJKoZIhvcNAQEEBQADgYEAgmlPk//8FE2Rr/HcnLuQ
2whtyrKGd6aeNTTJ1DI4ic/CZwVzYsB2gLgz3+xlgfuWWYe023vJDzX8SXnyjZw2
lk7MZIsqZpuKQebLvSOMYACMWWA+UqkuxUX0Y1a+9NQ6JKwFlAAKTWOCmCx6IPD8
690VjDqnAW7gZ5L6kw5QTas=
-----END NEW CERTIFICATE REQUEST-----



package testmail;

import java.util.Properties;

import javax.mail.Authenticator;
import javax.mail.Message;
import javax.mail.PasswordAuthentication;
import javax.mail.Session;
import javax.mail.Transport;
import javax.mail.internet.InternetAddress;
import javax.mail.internet.MimeMessage;

public class GMail {

String emailHost = "smtp.gmail.com";
String portNumber = "465";
String from = "bruce@mrc-productivity.com";
String pswd = "password";
String to = "bruce@mrc-productivity.com";
String subj = "emai subject";
String text = "email text is here.";

public GMail() {
Properties props = new Properties();
props.put("mail.smtp.user", from);
props.put("mail.smtp.host", emailHost);
props.put("mail.smtp.port", portNumber);
props.put("mail.smtp.starttls.enable", "true");
props.put("mail.smtp.auth", "true");
props.put("mail.smtp.debug", "true");
props.put("mail.smtp.socketFactory.port", portNumber);
props.put("mail.smtp.socketFactory.class","javax.net.ssl.SSLSocketFactory");
props.put("mail.smtp.socketFactory.fallback", "false");
//SecurityManager security = System.getSecurityManager();

try {
Authenticator auth = new SMTPAuthenticator();
Session session = Session.getInstance(props, auth);
session.setDebug(true);
MimeMessage msg = new MimeMessage(session);
msg.setText(text);
msg.setSubject(subj);
msg.setFrom(new InternetAddress(from));
msg.addRecipient(Message.RecipientType.TO,new InternetAddress(to));

Transport.send(msg);
} catch (Exception mex) {
mex.printStackTrace();
}
System.out.println("Done.");
}

public static void main(String[] args) {
new GMail();
}

private class SMTPAuthenticator extends javax.mail.Authenticator {
public PasswordAuthentication getPasswordAuthentication() {
return new PasswordAuthentication(from, pswd);
}
}
}

Friday, March 14, 2008

Create User in Oracle

1. download DbVisualizer Free 6.0.8

2. Set up Oracle connection

3. In SQL Commander tab run these SQL statements to create a user

CREATE USER jwnickol IDENTIFIED BY marathon DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP PROFILE DEFAULT;

ALTER USER jwnickol QUOTA 0 ON SYSTEM;

ALTER USER jwnickol QUOTA UNLIMITED ON USERS;

GRANT CREATE SESSION TO jwnickol;

GRANT ALTER SESSION TO jwnickol;

GRANT SELECT ANY TABLE TO jwnickol;

grant create table to jwnickol;

After I added this I could create schema etc.

grant all privileges to jwnickol;

Wednesday, January 09, 2008

Array to List, List to Array, Sort List, anonymous class


public static void p( ) {
String[] s = new String[4];
s[0] = "C";
s[1] = "NTM01";
s[2] = "NNTM01";
s[3] = "A";

//Array to List
List sl = Arrays.asList(s);
//anonymous class
Collections.sort(sl, new Comparator() {
//sort list by string length desceding
// --> acdx, asd,cc,cf,s
public int compare(Object o1, Object o2) {
String s1 = (String)o1;
String s2 = (String)o2;
return (s2.length() - s1.length());
}
});

//List to Array
String[] s2 = (String[]) sl.toArray(new String[0]);
System.out.println(s2);
}

Wednesday, November 28, 2007

Run java class in jar file without manifest file

1. Write class test.testspring.java and compile.
2. jar it to testspring.jar (without manifest file)
3. put it under home folder of AS400 IFS.
4. Run this AS400 command. Note other jar files used is in /mrcwebgui/WEB-INF/lib folder so we used java.ext.dirs property.

JAVA CLASS(test.testspring) CLASSPATH('/home/testspring.jar') PROP((java.ext.dirs '/mrcwebgui/WEB-INF/lib'))


Or you can put testspring.jar in /mrcwebgui/WEB-INF/lib. Then you only need to run this command:

JAVA CLASS(test.testspring) PROP((java.ext.dirs '/mrcwebgui/WEB-INF/lib'))

Tuesday, November 13, 2007

IBM Toolbox for Java JDBC properties

AS400 toolbox connection properties

All date values are stored by DB2 in the same internal format regardless of the DATFMT that is specified on the column or field definition. When that data is read from the SQL Table it will be converted to the output date format specified by the application -- in your case the *USA format.

You can add property “date format” to JDBC connection so that the output will be in your desired format:
jdbc:as400://192.168.0.170;translate binary=true; libraries=; date format=iso

Reference:
IBM Toolbox for Java JDBC properties.

Wednesday, October 10, 2007

Retrieve All Http Request Parms


Enumeration enumeration = req.getParameterNames();
for (; enumeration.hasMoreElements();) {
String name = (String) enumeration.nextElement();
if (!name.startsWith("rtn_")) {
continue;
}
String safld = req.getParameter(name);
String tafld = name.substring(4);
returnData.add(new ReturnDataField(safld,tafld));
}