Connect to MS Access from Java

21. January 2013 11:13 by Mrojas in   //  Tags: , , , , ,   //   Comments (0)

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

9. February 2010 18:49 by Mrojas in General  //  Tags: , , , ,   //   Comments (0)

 

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

3. February 2010 13:07 by Mrojas in   //  Tags: , , , ,   //   Comments (0)

 

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

1. February 2010 06:17 by Mrojas in General  //  Tags: , , ,   //   Comments (0)

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 :)

image

PGP in Java

23. October 2008 03:41 by Mrojas in General  //  Tags: ,   //   Comments (0)

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

14. May 2007 08:46 by Mrojas in General  //  Tags:   //   Comments (0)
There are a list of situations you might want to handle with Active Directory:

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

14. May 2007 08:29 by Mrojas in General  //  Tags:   //   Comments (0)

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

2. February 2007 11:20 by Mrojas in General  //  Tags:   //   Comments (0)

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 */

PrintService[] pservices = PrintServiceLookup.lookupPrintServices(flavor, aset);

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

int printnbr = 1;

DocPrintJob pj = pservices[printnbr].createPrintJob();

try {

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

* Since the data is postscript located in a disk file,

* an input stream needs to be obtained

* BasicDoc is a useful implementation that will if requested

* close the stream when printing is completed.

*/

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

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

/* print the doc as specified */

pj.print(doc, aset);

}

catch (Exception ex){

ex.printStackTrace();

}

}}

 

TIP: If you are just testing create a Printer. Just go to Add Printers, select FILE for port and Manufacturer Generic and TEXT Only

Java and Active Directory

10. January 2007 11:25 by Mrojas in General  //  Tags:   //   Comments (0)
   Active Directory is also LDAP. So to integrate with the Active Directory you can use code very similar to that one used with other LDAP servers.

To connect to the Active Directory you can do something like this:

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

21. December 2006 11:44 by Mrojas in General  //  Tags: ,   //   Comments (0)
This is a very controversial topic. Recently I have seen several blogs that state that the VB6 Programmers are moving to other platforms like PHP or Java instead of VB.NET
For example see:
Number of VB Developerts Declining in US
By Steve Graegert
“They’re also leaving VB.NET; which is down by 26%. This means Java now holds the market penetration lead at 45% (with developers using Java during some portion of their time), followed by C/C++ at 40%, and C# at 32%.”

I also remember an Article I read in 2005 in JDJ (Java Developers Journal) that expressed that C# was a language created similar to C++ to aid the C++ programmers move to the .NET Framework, argument that I do not share.

I have no evidence but I do not feel that it is that way. I'm am a Java Developer too. And both platforms have their merits. C# is a nice language, very similar to Java and C++ no doubt but it deserves its own place. Visual Studio has still a path to follow. But VS2005 provides some useful refactorings and the incredibly awaited feature that allows you to edit the code that you're running :)

Maybe the 1.0 and 1.1 frameworks were not enterprise ready, but 2.0 and 3.0 frameworks are an excellent improvent.

Java as well had to go thru a lot of JDK releases. They have just released the 1.6 and what about the J2EE releases, the Java Enterprise Beans fiasco. You can confirm that by looking at the rise of POJO (Plain Old Java Object) frameworks like spring.

In general everything requires time to grow. I think that Java has been more time in the market and it has finally provided mechanisms that allow the development of enterprise services "easier" and it is giving it momentum.

.NET components industry is common, there are lots of components and somethings are easier. So I'll wait some time, maybe a couple of year to really find out which is the hotter platform.

Categories