ColdFusion + iText 5.0.0 (Good things come in *new* packages)

In case you were unaware, iText 5.0.0 was released a short while ago, and a new iText book scheduled for release in June. The new version of iText contains some significant changes, including a new license. But to ColdFusion users, the most signficant change may be the new package name.


Most everyone knows ColdFusion uses an older version of iText internally. So upgrading to a newer version (without breaking ColdFusion) requires a little fancy foot-work, such as using a custom class loader like JavaLoader.cfc. But with the new package name, that has all changed.

In previous versions, iText classes were packaged under com.lowagie.* . As of version 5.0.0, the classes are now packaged under a new name: com.itextpdf.*. This change is a huge benefit to ColdFusion users because it eliminates class path compatibility issues with MX7, CF8 and CF9. The new iText version should happily co-exist with the older iText versions shipped with MX7, CF8 and CF9.

Now obviously using version 5.0.0 means changing code to use the new package name, and being careful not to mix old and new. But it is now easier than ever before to use the latest version of iText under ColdFusion.

...Read More

How to Install ZXing in Eclipse (JavaSE Component Only)

Update: Apparently I had a serious concussion when I wrote this, as I completely overlooked the obvious. "Hello, McFly - svn? I hear Google code has it too." So do not bother using these instructions. It is much simpler pull the project from SVN.

When I researched the JavaSE component of ZXing, I compiled the jars I needed with Eclipse 3.4.1 . The set up was relatively simple. But there were several steps needed to get everything up and running properly. So I decided to write up a quick set of instructions.


Note: ZXing contains several components (javase, javame, servlet, android, etcetera). These instructions only cover the JavaSE component. You may notice I imported the full source. But to actually use the other components in the project, additional files are needed. For further instructions see the project's Getting Started section

Instructions

1. Download the latest source and extract the files.
As of this entry, the current version is ZXing-1.4.zip. (Your version may differ.)


2. Create a new project in Eclipse.

• Use whatever you project name you wish. Example: "ZXing"
• Make sure the project JVM is 1.5 or higher

3. Import the source files into the project

• Select File => Import => File System => Next

• Supply the source and destination

1. Navigate to the extracted files and select the ZXing-1.4 subfolder . 2. In the main window, check the ZXing-1.4 folder, so its contents are imported 3. Select the top level folder of your project (not "src") for Into Folder

• Make sure Create selected folders only is selected
• Finally, click Finish to import the files into the project
  

4. Next Configure the Build Path

After the source files are imported, you need to configure the project build path. Each sub-component in ZXing has its own separate src folder. Each of them must be added to the build path. ZXing also has a few jar dependencies. Those jars must be added to the project build path as well.

You could modify the path using Eclipse's "Configure Build Path" option. But a shortcut
is to modify the project's .classpath file manually.

• Find and open the project's .classpath file
• Paste in the new class path entries below and save the file.
• Close and re-open the project in Eclipse to apply the changes.

New Class Path Entries

<classpathentry kind="src" path="android/src"/>
<classpathentry kind="src" path="bug/src"/>
<classpathentry kind="src" path="core/src"/>
<classpathentry kind="src" path="core/test/src"/>
<classpathentry kind="src" path="javame/src"/>
<classpathentry kind="src" path="javase/src"/>
<classpathentry kind="src" path="rim/src"/>
<classpathentry kind="src" path="zxingorg/src"/>
<classpathentry kind="lib" path="bug/lib/osgi.jar"/>
<classpathentry kind="lib" path="core/lib/junit.jar"/>
<classpathentry kind="lib" path="zxingorg/web/BarcodeReader.jar"/>
<classpathentry kind="lib" path="zxingorg/web/basic/BarcodeReader.jar"/>
<classpathentry kind="lib" path="zxingorg/web/WEB-INF/lib/commons-codec-1.3.jar"/>
<classpathentry kind="lib" path="zxingorg/web/WEB-INF/lib/commons-fileupload-1.2.1.jar"/>
<classpathentry kind="lib" path="zxingorg/web/WEB-INF/lib/commons-io-1.4.jar"/>
<classpathentry kind="lib" path="zxingorg/web/WEB-INF/lib/commons-lang-2.4.jar"/>
<classpathentry kind="lib" path="zxingorg/web/WEB-INF/lib/commons-logging-api-1.1.1.jar"/>
<classpathentry kind="lib" path="zxingorg/web/WEB-INF/lib/httpclient-4.0.jar"/>
<classpathentry kind="lib" path="zxingorg/web/WEB-INF/lib/httpcore-4.0.1.jar"/>
<classpathentry kind="lib" path="zxingorg/web/WEB-INF/lib/httpcore-nio-4.0.1.jar"/>
<classpathentry kind="lib" path="zxingorg/web/WEB-INF/lib/httpmime-4.0.jar"/>
<classpathentry kind="lib" path="zxingorg/web/WEB-INF/lib/mailapi.jar"/>
<classpathentry kind="lib" path="zxingorg/web/WEB-INF/lib/pop3.jar"/>
<classpathentry kind="lib" path="zxingorg/web/WEB-INF/lib/smtp.jar"/>

.ClassPath File

.ClassPath File Contents

You should now be able to run both the core an javase components of the project from Eclipse


Building Jars
The project includes several ant build files to simplify the process of building jars. Again, each component subfolder contains its own separate build file. So you can easily build jars individually. Simply locate the build.xml file for the desired component. Then run the build task to generate the jar.

Example: To build the JavaSE Jar

1. Locate the javase/build.xml file
2. Run the build task in ant
   
3. Ant will generate a javase.jar file inside the javase/ folder

...Read More

OT: Anguished English

I came across this book today and it gave me quite a chuckle.

Anguished English: An Anthology of Accidental Assaults Upon the English Language

...Read More

Dance of the Dueling Class Loaders Discussion

If you have ever had the misfortune of witnessing a performance of the highly un-entertaining Dance of the Dueling Class Loaders (a.k.a. Jar Hell), there is an interesting thread by Fred Roeber over at javaLoader-dev:

Solving the "JAR hell" issue with different 3rd party JAR files

...Read More

All I wanted for Christmas was a CFSCRIPT FOR .. IN LOOP (For Arrays)

Okay, so Christmas is past and CF9 is long out of the beta stage. But my tiny wish remains. The reasonable part of me says, "It is really not that hard to construct an alternative". Especially with all the enhancements in CF8 and CF9. It comes much closer to perfection than in any previous versions.

<cfscript>
for (x = 1; x <= arrayLen(myArray); x++)
{
  WriteOutput(myArray[x] &"<hr />");
}
</cfscript>

But there is something classic and elegant about a for in loop. Simple, intuitive and just downright beautiful code. If it were any more beautiful, I just might break down and cry. {Sigh} If only it worked. If only ...

<cfscript>
for (x in myArray)
{
    WriteOutput(x &"<hr />");
}
</cfscript>

<childishWhine>
All the other languages have it? Why can't we?
</childishWhine>

;)

...Read More

CFQueryparam Matrix for MySQL 5

As I predominately use MS SQL, I frequently end up having to search for the correct cfsqltypes when using cfqueryparam and MySQL. While I am sure there is an unofficial matrix out there somewhere, I never manage to find it when I need it. So I decided to create my own as a point of reference.

Not being an MySQL guru, I spent some time reviewing the MySQL 5.0 manual and the JDBC references to get a better understanding of the data types and limits, both in MySQL and Java. First the matrix and then I will explain some of the decisions behind it. The matrix should be pretty accurate. But corrections are always welcome.

Note: The mappings are based on tests with CF9, MySQL 5.1.42 and Connector/J 5.1.7. There are some significant differences in the various versions of MySQL and Connector/J drivers. So if you are using different versions, your mileage may vary.

ColdFusion 9	MySQL 5.1.42 Connector/J 5.1.7
CF_SQL_ARRAY	-
CF_SQL_BIGINT	bigint (signed), int (unsigned)
CF_SQL_BINARY	binary,tinyblob
CF_SQL_BIT	bit, bool
CF_SQL_BLOB	blob
CF_SQL_CHAR	char
CF_SQL_CLOB	-
CF_SQL_DATE	date
CF_SQL_DECIMAL	decimal
CF_SQL_DISTINCT	-
CF_SQL_DOUBLE	double, double precision, real
CF_SQL_FLOAT	-
CF_SQL_IDSTAMP	-
CF_SQL_INTEGER	mediumint (signed and unsigned), int (signed)
CF_SQL_LONGVARBINARY	mediumblob,longblob,tinyblob
CF_SQL_LONGVARCHAR	text,mediumtext,longtext
CF_SQL_MONEY	-
CF_SQL_MONEY4	-
CF_SQL_NULL	-
CF_SQL_NUMERIC	numeric, bigint(unsigned)
CF_SQL_OTHER	-
CF_SQL_REAL	float
CF_SQL_REFCURSOR	-
CF_SQL_SMALLINT	smallint (signed or unsigned), tinyint (signed)
CF_SQL_STRUCT	-
CF_SQL_TIME	**
CF_SQL_TIMESTAMP	datetime,timestamp
CF_SQL_TINYINT	tinyint (unsigned)
CF_SQL_VARBINARY	varbinary
CF_SQL_VARCHAR	varchar, tinytext, enum, set

Data Types 101
Since cfsqltypes are essentially a wrapper of java.sql.Types, I started with the MySQL documentation on Java, JDBC and MySQL Types and worked from there. The mappings were not always as straight-forward as I expected. In particular the mappings of the numeric data types.

In MySQL the range of allowed values can vary depending on whether the column is signed or unsigned. So unlike MS SQL, more than one cfsqltype was required to represent all possible values for certain types.

The MySQL INT type is a prime example. If the INT is signed, the range of allowed values is -2147483648 through 2147483647. For an unsigned INT,the range is 0 through 4294967295. Why does this matter? As with most things in CF, it relates back to java.

When you use CF_SQL_INTEGER you are using the java sql type INTEGER which "represents a 32-bit signed integer value ranging between -2147483648 and 2147483647". So obviously it does not have the capacity to represent the upper ranges of an unsigned INT. For values larger than 2147483647 you would need to use CF_SQL_BIGINT instead.

Decisions, Decisions ..
So why not just use CF_SQL_BIGINT for both types? Well, technically you could. But then you lose out on cfqueryparam's built in validation for signed INT's. That is one of the reasons I decided to use separate cfsqltypes for signed and unsigned numbers. Another reason is that the mappings are more intuitive this way, and more closely aligned with the standards (in my opinion). So you get the benefits of as much validation as possible, while using the smallest sql type necessary. Here is a quick breakdown of the various types and allowed ranges.



TINYINT
ColdFusion enforces the standard range: 0 to 255

CF_SQL_SMALLINT - Signed columns (Min: -128 / Max: 127)
CF_SQL_TINYINT - UnSigned columns (Min: 0 / Max: 255)

SMALLINT
ColdFusion does not stricly enforce the standard range: -32768 to 32767.
So you can use a single type for both signed and unsigned columns.

CF_SQL_SMALLINT - Signed (Min: -32768 / Max: 32767)
CF_SQL_SMALLINT - Unsigned (Min: 0 / Max: 65535)

MEDIUMINT
This seems to be a non standard size.The closest mapping is INTEGER

CF_SQL_INTEGER - Signed (Min: -8388608 / Max: 8388607)
CF_SQL_INTEGER - Unsigned (Min: 0 / Max: 16777215)

INT
ColdFusion enforces the standard range: -2147483648 to 2147483647.

CF_SQL_INTEGER - Signed (Min: -2147483648 / Max: 2147483647)
CF_SQL_BIGINT - Unsigned (Min: 0 / Max: 4294967295)

BIGINT
ColdFusion enforces the standard range: -9223372036854775808 to 9223372036854775807. I decided to use CF_SQL_NUMERIC (with a scale of 0), for unsigned values. Though CF_SQL_DECIMAL would probably work as well.

CF_SQL_BIGINT - Signed (Min: -9223372036854775808 / Max: 9223372036854775807)
CF_SQL_NUMERIC - Unsigned (Min: 0 / Max: 18446744073709551615)



Tonight at Comedy Central: Aproximate Numeric Types
Suffice it to say the collective documentation on approximate numeric types is a bit like a Who's on First routine. From everything I have read, the relationship between the java sql types and the MySQL data types is as follows: A REAL is a MySQL FLOAT, a DOUBLE is a MySQL REAL, and a FLOAT is .. well none of the MySQL data types corresponds to a java FLOAT. Are you with me so far? No? Good. Why should I be the only one with a headache.

The confusion is due in part to what some of the older java api's charmingly call "a possibly misguided attempt at consistency with previous database APIs" (in relation to the FLOAT type). Now throw in the fact that same terms have different meanings, depending on the context, and things get even murkier.

Conceptually, the easiest way to think of it is that approximate numbers fall into two categories: single and double precision. To trivialize the differences, both types can store large floating point numbers. But as the names imply, double precision can represent larger numbers than single precision. Pretty simple, right?

Unfortunately, from everything I have read, the mappings between java.sql.Types and MySQL data types are not nearly that logical. In fact they almost seem reversed. The java.sql.Types use REAL to represent single precision and FLOAT/DOUBLE to represent double precision numbers. Whereas MySQL uses FLOAT for single precision and REAL/DOUBLE for double precision numbers. See what I mean?

So why am I explaining all this? (Just to torture you.) But seriously, in part to explain why the cfqueryparam mappings for these types are likely different than what you expected. Also, to explain why I avoided any use of CF_SQL_FLOAT. The java api's apologetically mention the sql type FLOAT is often a source of confusion. The recommendation is that you not use this type, but use DOUBLE instead. FLOAT and DOUBLE are equivalent. (After reading the documentation, I wholeheartedly concur).

So without further ado, here are the mappings for the approximate types:



FLOAT
ColdFusion enforces the standard range: -3.40282347E+38F to 3.40282347E+38F. Note: All ranges are approximate and may not be exact.

CF_SQL_REAL - Signed (Min: -3.40282347E+38F / Max: 3.40282347E+38F)
CF_SQL_REAL - Unsigned (Min: 0 / Max: 3.40282347E+38F)

DOUBLE, DOUBLE PRECISION, REAL (Synonyn for DOUBLE)
ColdFusion enforces the standard range: -1.79E+308 to 1.79E+308. Note: All ranges are approximate and may not be exact.

CF_SQL_DOUBLE - Signed (Min: -1.79E+308 / Max: 1.79E+308)
CF_SQL_DOUBLE - Unsigned (Min: 0 / Max: 1.79E+308)



Open Questions
The one data type I could not figure out how to properly map was time. At least not easily. From a few cursory searches it seems problematic. As I do not make much use of this data type anyway, I decided not investigate it further. If anyone has figured out the correct mapping for this type, feel free to enlighten me.


Final Conclusions: Does the CFSQLType Really Matter?
As you can see from the list of types and ranges above there is some definite overlapping. For example, the number 126 could be submitted using CF_SQL_SMALLINT, CF_SQL_INTEGER or even CF_SQL_BIGINT. Obviously the latter two would be overkill for a TinyInt column. But since all of them work, does the type really matter?

Yes. Obviously the type must have the capacity to store the value you are submitting. If it is too small, the query may fail or the value may be truncated. But even if you select a type that has ample capacity to store your value, that value may be too large or invalid for your target column. So selecting the correct cfsqltype gives you an extra layer of validation in CF, before the SQL is ever submitted to the database. Finally, the selected type should interpret the value correctly according to the target column. To use an ridiculous example, you could insert the number zero (0) into a VARCHAR column, using CF_SQL_DATE. Technically, it will work. But the value actually inserted into the database would not be zero (0). So selecting the proper cfsqltype for each column does matter.

As always, comments and corrections are welcome.

...Read More