Connect to MS Access from Java

This is not an scenario that I commonly find, but now with Android and with the spread of Linux base environments an devices you are just don't want to be limited to just one technology.

So I found this library which I found extremely useful and I can recommend: Jackcess

The following are some code samples from the Jackcess library

• Displaying the contents of a table:

  System.out.println(Database.open(new File("my.mdb")).getTable("MyTable").display());
  

• Iterating through the rows of a table:

  Table table = Database.open(new File("my.mdb")).getTable("MyTable");
  for(Map<String, Object> row : table) {
    System.out.println("Column 'a' has value: " + row.get("a"));
  }
  

• Searching for a row with a specific column value:

  Map<String, Object> row = Cursor.findRow(table, Collections.singletonMap("a", "foo"));
  if(row != null) {
    System.out.println("Found row where 'a' == 'foo': " + row);
  } else {
    System.out.println("Could not find row where 'a' == 'foo'");
  }
  

• Creating a new table and writing data into it:

  Database db = Database.create(new File("new.mdb"));
  Table newTable = new TableBuilder("NewTable")
    .addColumn(new ColumnBuilder("a")
               .setSQLType(Types.INTEGER)
               .toColumn())
    .addColumn(new ColumnBuilder("b")
               .setSQLType(Types.VARCHAR)
               .toColumn())
    .toTable(db);
  newTable.addRow(1, "foo");
  

• Copying the contents of a JDBC ResultSet (e.g. from an external database) into a new table:

  Database.open(new File("my.mdb")).copyTable("Imported", resultSet);

• Copying the contents of a CSV file into a new table:

  Database.open(new File("my.mdb")).importFile("Imported2", new File("my.csv"), ",");

Remember that if you want to use it android you use some tweaks:

"The following steps will make Jackcess compatible with the Android platform.

• Set the system property "com.healthmarketscience.jackcess.brokenNio=true"
• Set the system property "com.healthmarketscience.jackcess.resourcePath=/res/raw/"
• Copy the *.txt, *.mdb, and *.accdb files from the "com/healthmarketscience/jackcess/" directory in the Jackcess jar to the "/res/raw" Android application directory.
• Before executing any Jackcess code, set the current Thread's context classloader, e.g. "Thread.currentThread().setContextClassLoader(Database.class.getClassLoader())"."

Escape characters for SQLLoader

The LINC/EAE migration tool can automatically generate reports that can be used to extract your data from DMSII to your target database, for example Oracle.
In this scenarios the Oracle SQL Loader tool is used. However you might problems loading the data because the string values can contain the same characters you are using to enclose them.

Let’s see an example, taken from an oracle forum:

C:\ora>type buyer.ctl
LOAD DATA
INFILE 'buyer.data'
truncate into table BUYER
FIELDS TERMINATED BY ',' optionally enclosed by '"' TRAILING NULLCOLS
(
buyer_code,
BUYER_NAME
)

And suppose you have data like:

1,"XYZ IND"

2,"ABC"

3,"XYZ ABC"

4,"Your "offspring""

5,"ATUL"

How can you “escape” the enclosing characters. Well I found the answer in another forum:

If two delimiter characters are encountered next to each other, a single occurrence of the delimiter character is used in the data value. For example, 'DON''T' is stored as DON'T. However, if the field consists of just two delimiter characters, its value is null.

So just use something like:

1,"XYZ IND"

2,"ABC"

3,"XYZ ABC"

4,"Your ""offspring"""

5,"ATUL"

Easy way to see the Explain Plan in Oracle

Linc\EAE used profiles for their queries. Well the profile information is used by our migration tool to generate indexes.
In Java is easy to intercept all SQL statements used by the translated application and analyze them.

To analyse how a query is executed you have to study its explain plan. For go here an excellent guide on EXPLAIN PLAN.

After you read that page, you will find useful the following function, that will shorten the lines that you have to type to see the explain plan:

create OR REPLACE function  ShowPlan return sys_refcursor
  as
      c_test sys_refcursor;
BEGIN
  open c_test for select 
  substr (lpad(' ', level-1) || operation || ' (' || options || ')',1,30 ) "Operation", 
  object_name "Object"
  from 
  sys.plan_table$ start with id = 0 connect by prior id=parent_id;
  return c_test;
END;
SQL>

explain plan for select * from MY_TABLE

SQL> variable rc refcursor
SQL> exec :rc := testfunc()

PL/SQL procedure successfully completed.

SQL> print rc

Operation                      Object
------------------------------ ------------------------------
SELECT STATEMENT ()
 TABLE ACCESS (FULL)           MY_TABLE

Get Java Version for Oracle Stored Procedures

If you have to write stored procedures for oracle is important
to notice which Java version is supported by your Oracle Database,

A common technique is create a JAVA stored procedure for that:

1. Create a function with an ORACLE SQL statement like:

CREATE OR REPLACE FUNCTION getJavaProperty(myprop IN VARCHAR2)
RETURN VARCHAR2 IS LANGUAGE JAVA
name ‘java.lang.System.getProperty(java.lang.String) return java.lang.String’;

2. Once you created the function you can use it to get the version:

SELECT getJavaProperty(‘java.version’) from dual;

You can see in the attached version that for my Oracle Database 10.1.0.4.2 the Java version is 1.4.2_04 :)

PGP in Java

Recenlty following a post in an AS400 Java Group, someone asked about a method for signing and verifying a file with PGP.
I though, "Damn, that seems like a very common thing, it shouldn't be that difficult", and started google'ing for it.
I found as the poster said that the Bouncy Castle API can be used but it was not easy.

Well so I learned a lot about PGP and the Bouncy Castle and thanks god, Tamas Perlaky posted a great sample that signs a file, so I didn't have to spend a lot of time trying to figure it out.

I'm copying Tamas Post because, I had problems accesing the site so here is the post just as Tamas published it:

"To build this you will need to obtain the following depenencies.  The Bouncy Castle versions may need to be different based on your JDK level.

  bcpg-jdk15-141.jar
  bcprov-jdk15-141.jar
  commons-cli-1.1.jar

Then you can try something like:
  java net.tamas.bcpg.DecryptAndVerifyFile -d test2_secret.asc -p secret -v test1_pub.asc -i test.txt.asc -o verify.txt
And expect to get a verify.txt that's the same as test.txt.  Maybe.
Here’s the download: PgpDecryptAndVerify.zip"

And this is the original link: http://www.tamas.net/Home/PGP_Samples.html

Thanks a lot Tamas

Active Directory, Password Expired

525 - user not found
52e - invalid credentials
530 - not permitted to logon at this time
532 - password expired
533 - account disabled
701 - account expired
773 - user must reset password

This is an extract of the Java Forum to handle these cases. Good Luck!

} catch (AuthenticationException e) {

String tempString;

StringTokenizer tokenizerTemp = new StringTokenizer(e.toString());

while (tokenizerTemp.hasMoreElements()) {

         tempString = tokenizerTemp.nextToken();

         if (tempString.equalsIgnoreCase("AcceptSecurityContext")) {

                 while (tokenizerTemp.hasMoreElements()) {

                          tempString = tokenizerTemp.nextToken();

                          if (tempString.startsWith("773"))

                                   setIsPasswordExpired(true);

                          if (tempString.startsWith("52e"))

                                   setIsPasswordWrong(true);

                          if (tempString.startsWith("533"))

                                   setIsAccountDisabled(true);

                 }

         }

}

throw new NamingException();

}

Active Directory LDAP and Java

It is common that after a migration to Java, specially coming from legacy platforms like LINC or COBOL, that our clients want to take advantage of new technologies. So it happens that they are now authenticating against an Active Directory or another LDAP server. And thanks to the new platforms it is really easy for us to help them integrate this new functionality.
This is sample program that show how to authenticate with for example a Windows Active Directory.

import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.util.Hashtable;

import javax.naming.Context;
import javax.naming.NamingEnumeration;
import javax.naming.NamingException;
import javax.naming.directory.Attributes;
import javax.naming.directory.SearchControls;
import javax.naming.directory.SearchResult;
import javax.naming.ldap.InitialLdapContext;
import javax.naming.ldap.LdapContext;

public class LDAPTest
{
       static class LDAP
      {
            static String ATTRIBUTE_FOR_USER = "sAMAccountName";
            public Attributes authenticateUser(String username, String password, String _domain, String host, String dn)
            {

                  String returnedAtts[] ={ "sn", "givenName", "mail" };
                  String searchFilter = "(&(objectClass=user)(" + ATTRIBUTE_FOR_USER + "=" + username + "))";
                  //Create the search controls

                  SearchControls searchCtls = new SearchControls();
                  searchCtls.setReturningAttributes(returnedAtts);
                  //Specify the search scope

                  searchCtls.setSearchScope(SearchControls.SUBTREE_SCOPE);
                  String searchBase = dn;
                  Hashtable environment = new Hashtable();
                  environment.put(Context.INITIAL_CONTEXT_FACTORY, "com.sun.jndi.ldap.LdapCtxFactory");
                  //Using starndard Port, check your instalation

                  environment.put(Context.PROVIDER_URL, "ldap://" + host + ":389");
                  environment.put(Context.SECURITY_AUTHENTICATION, "simple");

                  environment.put(Context.SECURITY_PRINCIPAL, username + "@" + _domain);
                  environment.put(Context.SECURITY_CREDENTIALS, password);
                  LdapContext ctxGC = null;
                  try
                  {
                        ctxGC = new InitialLdapContext(environment, null);
                        //    Search for objects in the GC using the filter

                        NamingEnumeration answer = ctxGC.search(searchBase, searchFilter, searchCtls);
                        while (answer.hasMoreElements())
                        {
                              SearchResult sr = (SearchResult)answer.next();
                              Attributes attrs = sr.getAttributes();
                              if (attrs != null)
                              {
                                    return attrs;
                              }
                        }

                   }
                  catch (NamingException e)
                  {
                        System.out.println("Just reporting error");
                        e.printStackTrace();
                  }
                  return null;
            }
     }

      public static void main(String[] args) throws Exception
      {
            InputStreamReader converter = new InputStreamReader(System.in);
            BufferedReader in = new BufferedReader(converter);
            System.out.println("Please type username:");
            String username = in.readLine();
            System.out.println("Please type password:");
            String password = in.readLine();
            LDAP ldap = new LDAP();

            //Yo specify in the authenticate user the attributes that you want returned

            //Some companies use standard attributes like 'description' to hold an employee ID

            //The ActiveDirectory data can be enhanced to add custom attributes like

            //printer

            // Some instalations usually have several ACtiveDirectoryServers, lets say

            // 192.150.0.8, 192.150.0.7 y 192.150.0.9 and they use a

            // DNS round robin to balance the load

            Attributes att = ldap.authenticateUser(username, password, "mydomain.com", "myactivedirectoryhost.com", "DC=mydomain,DC=com");
            if (att == null)
            {
                  System.out.println("Sorry your use is invalid or password incorrect");
            }
            else
            {
                  String s = att.get("givenName").toString();
                  System.out.println("GIVEN NAME=" + s);
            }
      }
}

Printing in Java

Sample Code to Print in Java

import java.io.ByteArrayInputStream;
import javax.print.Doc;
import javax.print.DocFlavor;
import javax.print.DocPrintJob;
import javax.print.PrintService;
import javax.print.PrintServiceLookup;
import javax.print.SimpleDoc;
import javax.print.attribute.HashPrintRequestAttributeSet;
import javax.print.attribute.PrintRequestAttributeSet;

public class Class3 {

static String textToPrint = "Richard North Patterson's masterful portrayals of law and politics at the apex of power have made him one of our most important\n" +
"writers of popular fiction. Combining a compelling narrative, exhaustive research, and a sophisticated grasp of contemporary\n" +
"society, his bestselling novels bring explosive social problems to vivid life through characters who are richly imagined and\n" +
"intensely real. Now in Balance of Power Patterson confronts one of America's most inflammatory issues-the terrible toll of gun\n" +
"violence.\n\n" +
"President Kerry Kilcannon and his fiancée, television journalist Lara Costello, have at last decided to marry. But their wedding\n" +
"is followed by a massacre of innocents in a lethal burst of gunfire, challenging their marriage and his presidency in ways so shattering\n" +
"and indelibly personal that Kilcannon vows to eradicate gun violence and crush the most powerful lobby in Washington-the Sons of\n" +
"the Second Amendment (SSA).\n\n" +
"Allied with the President's most determined rival, the resourceful and relentless Senate Majority Leader Frank Fasano, the SSA\n" +
"declares all-out war on Kerry Kilcannon, deploying its arsenal of money, intimidation, and secret dealings to eviscerate Kilcannon's\n" +

"crusade and, it hopes, destroy his presidency. This ignites a high-stakes game of politics and legal maneuvering in the Senate,\n" +

"the courtroom, and across the country, which the charismatic but untested young President is determined to win at any cost. But in\n" +

"the incendiary clash over gun violence and gun rights, the cost to both Kilcannons may be even higher than he imagined.\n\n" +

"And others in the crossfire may also pay the price: the idealistic lawyer who has taken on the gun industry; the embattled CEO\n" +

"of America's leading gun maker; the war-hero senator caught between conflicting ambitions; the female senator whose career is at\n" +

"risk; and the grief-stricken young woman fighting to emerge from the shadow of her sister, the First Lady.\n\n" +

"The insidious ways money corrodes democracy and corrupts elected officials . . . the visceral debate between gun-rights and\n" +

"gun-control advocates . . . the bitter legal conflict between gun companies and the victims of gun violence . . . a\n" +

"ratings-driven media that both manipulates and is manipulated - Richard North Patterson weaves these engrossing themes into an\n" +

"epic novel that moves us with its force, passion, and authority.";

public static void main(String[] args) {

DocFlavor flavor = DocFlavor.INPUT_STREAM.AUTOSENSE;

PrintRequestAttributeSet aset = new HashPrintRequestAttributeSet();

/* locate a print service that can handle it */

/* create a print job for the chosen service */

int printnbr = 1;

try {

/* * Create a Doc object to hold the print data.

ByteArrayInputStream fis = new ByteArrayInputStream(textToPrint.getBytes());

Doc doc = new SimpleDoc(fis, flavor, null);

/* print the doc as specified */

catch (Exception ex){

Java and Active Directory

import java.util.Hashtable;
import javax.naming.Context;
import javax.naming.NamingException;
import javax.naming.directory.DirContext;
import javax.naming.directory.InitialDirContext;

 /**
  * @author mrojas
  */

public class Test1
{

      public static void main(String[] args)
      {

            Hashtable environment = new Hashtable();
            //Just change your user here       
            String myUser = "myUser";
            //Just change your user password here      

            String myPassword = "myUser"; 

            //Just change your domain name here

            String myDomain = "myDomain";

            //Host name or IP

            String myActiveDirectoryServer = "192.168.0.20";

            environment.put(Context.INITIAL_CONTEXT_FACTORY, "com.sun.jndi.ldap.LdapCtxFactory");
            environment.put(Context.PROVIDER_URL, "ldap://" + myActiveDirectoryServer + ":389");
            environment.put(Context.SECURITY_AUTHENTICATION, "simple");
            environment.put(Context.SECURITY_PRINCIPAL, "CN=" + myUser + ",CN=Users,DC=" + myDomain + ",DC=COM");
            environment.put(Context.SECURITY_CREDENTIALS, myPassword);
            try
            {
                  DirContext context = new InitialDirContext(environment);
                  System.out.println("Lluvia de exitos!!");
            }
            catch (NamingException e)
            {
                  e.printStackTrace();
            }
      }
}

Is .NET hotter that Java