Commons Net FTP and Active vs. Pasv connections

In my current project at work I needed to programmatically FTP some XML documents to a remote system and so in my continuing quest to try and use every open source Java library that exists in the wild (not really, but it seems like it sometimes), this week I present to you Jakarta Commons Net. This project “.. implements the client side of many basic Internet protocols. The purpose of the library is to provide fundamental protocol access, not higher-level abstractions.” which of course includes FTP.

It’s fairly easy to get started since the JavaDocs for the FTPClient class include a couple examples, so I won’t illustrate that. The reason I’m writing is because I ran into an issue where the tests I wrote and executed on my desktop didn’t work once I deployed them to the server. Active and Passive mode are well documented elsewhere, but they don’t manifest themselves very clearly when you’re looking at a stack trace. For instance, the code to connect to an FTP server and send a file using Commons Net looks like this:

String server = "ftp.yoursite.com";
FTPClient client = new FTPClient();
client.connect(server);
client.login("username", "password");
File file = new File("testfile.txt");
InputStream is = new FileInputStream(file);
client.storeFile("testfile.txt", is);
client.disconnect();

Straightforward and it worked fine from my desktop. The same code moved to a server in the DMZ at work didn’t work and threw a java.net.SocketException, saying java.net.SocketException: Connection reset“. The FTPClient class, as a subclass of the SocketClient, contains a method isConnected(), which, when invoked, returned true. So I could login, change the working directory and disconnect with no errors thrown, but as soon as I tried to send a file I got an SocketException. The simple solution was to change the mode from Active to Passive using the enterLocalPassiveMode() method, which would have been obvious if I had known a bit more about the FTP protocol. In short, active mode FTP means that your client connects to the FTP server on the command port and then the FTP attempts to make a connection to your client for the data port, which isn’t going to work in a completely locked down environment. Changing to passive mode means that the client connects to the server for both command and data port transmission.

Hibernate: Returning N records in a random order

Hibernate: Returning N records in a random order

I was pleasantly surprised by Hibernate yesterday when I tried need to have a web page show a listing of 3 randomly chosen articles. Using SQL Server, you’d write a query like this:
SELECT top 3 * FROM mytable ORDER BY newid()
The ‘top n’ syntax is specific to SQL Server, which makes it non portable. I saw that the Query class has a method that allows you to restrict the number of rows that are returned from a query:

String query = "SELECT * FROM mytable ORDER BY newid()";
Query q = session.createQuery(query);
q.setMaxResults(3);

but I thought that would be inefficient because Hibernate would have to retrieve a large resultset and then only return the first n rows. Turns out that Hibernate actually figures out that SQL Server is the underlying platform and uses the ‘TOP’ keyword [look at the getLimitString() method]. Hibernate has made what used to be proprietary SQL Server sql, portable across any database platform that Hibernate supports. Case in point — if you use the same method q.setMaxResults(3) when using MySQL, Hibernate will automatically use the MySQLDialect and substitute the limit keyword that MySQL uses.

Of course, Hibernate can’t do everything… yet. The second part of my query ordered the results in a random fashion. You can do that in SQL Server using the newid() function; MySQL accomplishes the same thing with the rand() function.

UBL 1.0

Last week Tim Bray mentioned the May 1st release of UBL 1.0, which he defines as “… a set of general-purpose XML-encoded business documents: orders, acknowledgments, packing slips, invoices, receipts.” He goes on to compare UBL to HTML, saying that because it (UBL) is a generic format rather than a format made for a particular industry (just like HTML was a generic, simpler subset of SGML), it has a chance to become the HTML of the business document world (read: explosive growth, eventual ubitquity). Tim quotes an email from Jon Bosak on some of the other reasons for the creation of UBL:

· Developing and maintaining multiple versions of common business documents like purchase orders and invoices is a major duplication of effort.
· Creating and maintaining multiple adapters to enable trading relationships across domain boundaries is an even greater effort.
· The existence of multiple XML formats makes it much harder to integrate XML business messages with back-office systems.
· The need to support an arbitrary number of XML formats makes tools more expensive and trained workers harder to find.

My current project, which should be released soon, utilizes software from many different companies: tax software, credit card software, shipping rate software, custom software written by the company that manages the distribution of product, etc.. Obviously having a single format to work with would decrease the time I spend a) digging through each companies documentation trying to understand their format and b) wiring up the custom documents for each format, so I’m definitely looking forward to the day when I can use UBL.

For anyone interested, it looks like there is a smattering of support for UBL out there in the Java world: http://softml.net/jedi/ubl/sw/java/, https://jwsdp.dev.java.net/ubl/, http://www.sys-con.com/story/?storyid=37553&DE=1. For further information regarding UBL, see the OASIS UBL TC web page at:
http://www.oasis-open.org/committees/tc_home.php?wg_abbrev=ubl

ColdFusion MX and java.sql

Last week a reader sent me an interesting problem. When deleting a record from a database using a standard <cfquery> tag you don’t get any indication back that let’s you know whether or not the row or rows in question actually existed, nor do you get back any indication as to how many rows might have been affected. He wanted to be able run a single query that deleted a record AND let him know whether or not the record existed. He thought (correctly) that there was a way to do this in Java and wondered if I could write a CFX tag to do it for him. I could, but it would be much easier to do it in <cfscript>. In Java, you would write this:

// replace the package/class name of your db driver
Class.forName("org.gjt.mm.mysql.Driver");
// replace w/ your server, database name, username & password
String conurl = "jdbc:mysql://server/db?user=u&password=p";
Connection con = DriverManager.getConnection(conurl);
PreparedStatement ps;
ps = con.prepareStatement("DELETE from table_name where id = 3045");
int result = ps.executeUpdate();
con.close();

In short, you load the appropriate database driver (I’m using MySQL in the above example), obtain a connection to the database using the DriverManager class, create a PreparedStatement, and then call the executeUpdate() method. This method returns an int: “… either (1) the row count for INSERT, UPDATE, or DELETE statements or (2) 0 for SQL statements that return nothing.”

Using the above sample it’s pretty easy to do the same thing in ColdFusion using <cfscript>:

<cfscript>
clazz = CreateObject("java", "java.lang.Class");
// replace the package/class name of your db driver
clazz.forName("org.gjt.mm.mysql.Driver");
driverManager = CreateObject("java", "java.sql.DriverManager");
// replace w/ your server, database name, username & password
conurl = "jdbc:mysql://server/db?user=u&password=p";
connection = driverManager.getConnection(conurl);
query = "DELETE from category where id = 3045";
preparedStatement = connection.prepareStatement(query);
result = preparedStatement.executeUpdate();
WriteOutput("result = " & result);
connection.close();
</cfscript>

Further, you can test the value of the result variable so that you can show a custom message to a end user or perform some other type of business logic:

if (result == "0") {
  WriteOutput("No records were deleted.");
} else if (result == "1") {
  WriteOutput("One record was deleted.");
} else {
  WriteOutput(result & " records were deleted.");
}