Friday, February 29, 2008

Processing multiple sql statements with ColdFusion, JDBC and MS SQL - Part 2

In Part 1 we reviewed how jdbc objects can be used to execute and process the results of sql statements. In Part 2 we will apply this information in a ColdFusion example. Though the example uses an MS SQL database, it could also be used with other databases, with a few minor modifications.

First let us create a few test tables and populate them with sample data. Since this example is really about jdbc, I chose the wildly imaginative and descriptive table names: SourceTable and DestinationTable.


-- create test tables
CREATE TABLE DestinationTable (
DestinationTableID int identity(1,1),
Title varchar(100) NULL,
LastModified datetime NOT NULL
)

CREATE TABLE SourceTable (
SourceTableID int identity(1,1),
Title varchar(100) NULL,
DateModified datetime NOT NULL
)

-- populate table with sample data
INSERT INTO SourceTable (Title, DateModified)
SELECT 'Transact-SQL', getDate() UNION
SELECT 'ColdFusion 8 (CFWACK)', getDate() UNION
SELECT 'iText in Action', getDate()


Before we can run any sql statements, we must first open a connection to the database. MS SQL runs on port 1433 by default, but your database settings may differ.


<cfscript>
Class = createObject( "java", "java.lang.Class" );
Class.forName( "macromedia.jdbc.sqlserver.SQLServerDriver" );
manager = createObject( "java", "java.sql.DriverManager" );
connection = manager.getConnection(
"jdbc:macromedia:sqlserver://localhost:1433;databaseName=YourDatabaseName;",
"YourDatabaseUserName",
"YourDatabasePassword" );
</cfscript>


Then we construct a string containing a few sql statements. The statements I selected were only chosen to demonstrate the concept. So they are rather meaningless ;) Next we obtain a Statement object from our connection. Then use the Statement to execute our sql string and get information about the first set of results.

<cfscript>
sqlToExecute = " SET NOCOUNT OFF; "
& " INSERT INTO DestinationTable ( Title, LastModified ) "
& " SELECT TOP 1 Title, DateModified FROM SourceTable WHERE SourceTableID = 2; "

& " SELECT SCOPE_IDENTITY() AS DestinationTableID; "

& " SELECT SourceTableID, Title, DateModified FROM SourceTable; ";

statement = connection.createStatement();
isResultSet = statement.execute(sqlToExecute);
rowCount = statement.getUpdateCount();
results = arrayNew(1);
</cfscript>

Now you may have noticed the sql string above starts with SET NOCOUNT OFF. In my tests, the CF8 macromedia.jdbc.sqlserver.SQLServerDriver did not behave correctly unless I used set nocount. I suspect this may be a bug, but am not positive. I posted a test case in a previous entry. So if you happen to be bored and feel like running it with CF8, let me know if you get the same results. Anyway, back to the example.

We are now ready to process the results using a do / while loop. Inside the loop, we check if the current result is a row count or a resultset and save it accordingly. Then move the next set of results. The loop continues until there are no more results to process. According to the java API, there are no more results when: getMoreResults() returns false AND getUpdateCount() == -1.


<cfscript>
do {

if (isResultSet) {

// process and store the resultset ..

}
// this is a row count only
else if (rowCount >= 0) {

// store the row count ..

}

// move to the next result
isResultSet = statement.getMoreResults();
rowCount = statement.getUpdateCount();
}
// continue while there are still results to process
while (isResultSet OR rowCount GTE 0);

// ALWAYS close connections when finished!
statement.close();
connection.close();
</cfscript>


To save the resulsets, we simply loop through them and copy the values into a ColdFusion query object. It is not that different from looping through a CF query dynamically. The main difference is that jdbc does not provide a columnList variable to loop through. So you must construct a list, or array, of column names from the resultset metadata.


<cfscript>
// get the current resultset and metadata
resultset = statement.getResultSet();
metadata = resultset.getMetaData();
columnCount = metadata.getColumnCount();
columnNames = arrayNew(1);

// construct an array of query column names
for (col = 1; col LTE columnCount; col = col + 1) {
arrayAppend(columnNames, metadata.getColumnName( col ));
}

// convert the resultset to a CF query
row = 0;
rsQuery = queryNew( arrayToList(columnNames) );
while (resultset.next()) {
row = queryAddRow( rsQuery, 1 );
for (col = 1; col LTE columnCount; col = col + 1) {
rsQuery[ columnNames[col] ] [ row ] = resultset.getObject( javacast("int", col) );
}
}

resultset.close();

// store the current results
data = structNew();
data.recordCount = row;
data.isQuery = true;
data.query = rsQuery;
arrayAppend( results, data );
</cfscript>


The final result is an array of structures that should look like the image below. Each item in the array is a structure containing three elements:

  • isQuery (true/false)

  • recordCount (query record count or number of rows affected)

  • query (a query object or an empty string)





So there you have it. A basic example of using jdbc. Now, I am not suggesting you should start using this method instead of cfquery. Cfquery has a lot of advantages such as its simplicity, caching, connection pooling, etcetera. But if you ever do need to use jdbc directly, this simple example should give you a starting point.


Complete Code

<cfscript>
// database settings
dbIPAddress = "127.0.0.1";
dbPortNumber = "1433";
dbUsername = "username";
dbPassword = "password";
dbName = "YourDatabaseName";

// open a connection to the database
Class = createObject( "java", "java.lang.Class" );
Class.forName( "macromedia.jdbc.sqlserver.SQLServerDriver" );
manager = createObject( "java", "java.sql.DriverManager" );
connectionURL = "jdbc:macromedia:sqlserver://"& dbIPAddress &":"& dbPortNumber &";databaseName="& dbName &";";
connection = manager.getConnection( connectionURL, dbUsername, dbPassword );

// construct a string with a few meaningless sql statements
sqlToExecute = " SET NOCOUNT OFF; "
& " INSERT INTO DestinationTable ( Title, LastModified ) "
& " SELECT TOP 1 Title, DateModified FROM SourceTable WHERE SourceTableID = 2; "
& " SELECT SCOPE_IDENTITY() AS DestinationTableID; "
& " SELECT SourceTableID, Title, DateModified FROM SourceTable; ";

// run the sql statments and get the first results
statement = connection.createStatement();
isResultSet = statement.execute(sqlToExecute);
rowCount = statement.getUpdateCount();

// construct an array for storing the results
results = arrayNew(1);

do {
// the current result is a resultset
if (isResultSet) {

// get the resultset and metadata
resultset = statement.getResultSet();
metadata = resultset.getMetaData();
columnCount = metadata.getColumnCount();
columnNames = arrayNew(1);

// construct an array of query column names
for (col = 1; col LTE columnCount; col = col + 1) {
arrayAppend(columnNames, metadata.getColumnName( col ));
}

// convert the resultset to a CF query
row = 0;
rsQuery = queryNew( arrayToList(columnNames) );

// for each row of data ..
while (resultset.next()) {
row = queryAddRow( rsQuery, 1 );
// copy the values for each column into the CF query object
for (col = 1; col LTE columnCount; col = col + 1) {
rsQuery[ columnNames[col] ] [ row ] = resultset.getObject( javacast("int", col) );
}
}

// close the resultset to release the resources
resultset.close();

// store the results
data = structNew();
data.recordCount = row;
data.isQuery = true;
data.query = rsQuery;
arrayAppend( results, data );

}
// this is a row count only
else if (rowCount >= 0) {

// store the current row count
data = structNew();
data.recordCount = statement.getUpdateCount();
data.isQuery = false;
data.query = "";
arrayAppend( results, data );
}

// get the next set of results
isResultSet = statement.getMoreResults();
rowCount = statement.getUpdateCount();
}
// while there are still results to process
while (isResultSet OR rowCount GTE 0);

// ALWAYS close connection objects!
statement.close();
connection.close();
</cfscript>

<!--- display the results --->
<cfdump var="#results#">

...Read More

Processing multiple sql statements with ColdFusion, JDBC and MS SQL - Part 1

In a previous entry, I talked about why certain MS SQL queries, containing multiple sql statements, result in an undefined query variable when used under ColdFusion 8.

Is it possible to process the results of multiple sql statements?
Yes. Technically, cfquery could return the results of all sql statements within the cfquery tags. But in its current form it does not. Whether or not it should provide that ability is another question altogether. But to explain how it might do this, let us take a look at running sql statements at a slightly lower level: using jdbc directly. After all that is what your typical ColdFusion query uses behind the scenes anyway. If you are already familiar with jdbc, feel free to skip ahead.

About JDBC terms
Since JDBC operates at a lower level, it is capable of returning detailed information about each sql statement executed. That information is generally referred to as a result. The result may be the number of rows affected or it may be an actual resultset. Resultsets are similar to a ColdFusion query object (ie rows and columns of data).

JDBC 101
With jdbc you can execute a basic sql statement using either a Statement or PreparedStatement object. Both act as a kind of wrapper, passing your sql statements and any parameters to the database. There are differences between the two classes, and also some details that I am glossing over, like the fact that they are interfaces and not concrete classes ;) But you can read more about that on your own.

For now I will focus on three (3) methods of the Statement interface. While all of them can be used to execute a sql statement(s), each is used for a different type of statement.

  • Statement.executeQuery(...) is used to execute a sql statement that returns a single resultset. For example, a single SELECT statement.

  • Statement.executeUpdate(...) is used to run sql statements that return nothing or return a row count only. Examples are INSERT, UPDATE, DELETE or DDL statements.

  • Statement.execute(...) is used to run statements that may return multiple results. The results may be row counts, resultsets, or a combination of both.


Given that you can run almost any type of statement within a cfquery, or multiple statements, it seems likely that cfquery uses execute() internally. So that is what we will be using in our example.

JDBC 201
Though execute() is capable of capturing multiple results, the Statement object can only process one result at a time. So calling execute() runs all of the sql statements in the database, then positions the Statement object to process the first result. To process all of the results we must loop through them. Here are the methods we will use to accomplish this:

  • Statement.execute(stringOfSqlStatements) runs the given sql statements against the target database. It returns true if the first result is a resultset. Otherwise, it returns false.

  • Statement.getResultSet() returns the current resultset. If the current result is a a row count only, or there are no more results, it returns null.

  • Statement.getUpdateCount() returns the current result as a row count. If the current result is a resultset, or there are no more results, it returns -1.

  • Statement.getMoreResults() moves to the next result. It returns true if the next result is a resultset. If the next result is a row count only, or there are no more results, it returns false.

Phew! That is enough background for now. In Part 2 we will actually apply all of this good information and demonstrate how to use jdbc and ColdFusion to process the results of multiple sql statements.

...Read More

Thursday, February 28, 2008

OT: Why I refuse to prefix table names with "tbl"

I have been a bit busy lately and have not had much time to blog. But I came across a post today that reminded me of one of my firm rules. The post topic was naming conventions, and one of the conventions discussed was prefixing table names with "tbl". Now generally I prefer not to prefix variable and object names that way. I find it redundant, but I am not militant about the subject.

But I do remember seeing a post some time ago in which the asker used both a prefix and suffix. So their full table name was tblCustomerTable. Now, all stylistic preferences aside, I absolutely refuse to do that. Why? Because it might require me to have a conversation, in front of actual people, where I found myself uttering the words ".. and then do a select on the table customer table table". Say that three times fast. Can you imagine if the entire schema were like that? A minute or two of hearing ridiculous phrases like that and the entire room would start snickering. A minute after that I doubt even I could keep a straight face.

If I am ever unfortunate enough to work with such a schema, I can only hope all conversations take place via conference call .. and that everyone's mute button is working ;)

...Read More

Thursday, February 21, 2008

Underestimating the importance of troubleshooting

I subscribe to a variety of mailing lists and forums. Not only to find answers to my own questions, but because it is a great place to exchange information and ideas. On a good list, you get exposure to a wide array of technical people, with varying backgrounds and experiences. Some discussions can be quite interesting, and I often learn new tricks or approaches just by reading them.

But one thing I have noticed in some forum questions, by both beginner and intermediate programmers, is a lack of solid troubleshooting skills. I find this very puzzling. Like it or not, if you program, you are going to have to troubleshoot code problems eventually. Even a single line of code has the potential for bugs. So while a firm grasp of the language, well written code and testing will minimize the potential for errors, they do not eliminate it. How quickly you are able to diagnose problems usually depends on your ability to troubleshoot. So I think it is a very important and necessary skill to develop.

Now I am not an expert by any means, but I do consider troubleshooting one of my strengths. Occasionally, I get "tripped up" by stupid coding errors just like everyone else. However, I can usually spot the issue myself just by taking the time to think the problem through and doing a little debugging. Sometimes the solution comes quickly, other times not. But it is a necessary process, and one that I learn from. Though in a few cases the most important thing I learned is what not to do ... ever again ;) While it did nothing to improve my headache at the time, I think it ultimately improved both my coding and problem solving skills.

At times we all overlook the obvious or approach something from the wrong angle. But when I see questions in which the asker gives no evidence of trying to work the problem through, I have to shake my head. It is almost as if the asker saw an error message, or strange code behavior, got "freaked out" by it, and temporarily lost their common sense ;)

Granted, error messages are often generic or cryptic. Yet sometimes they actually do tell you the exact cause of the problem. That is if we bother to read them. But in situations where error messages are ambiguous, or there is no error at all, applying a bit of logic and basic common sense can go a long way towards solving the problem.

There is no magical solution or one-size-fits-all approach, but there are a few basics steps I have found helpful when debugging code. Some of them may seem insultingly obvious, but I think they are worth re-stating ;) and hopefully it might help someone.

Assume it is a programming error, not a bug
While there is always a small chance a problem is caused by an obscure software bug, it usually is not. So I always start with the assumption the problem is a programming error, until I have run enough tests to suggest or prove otherwise.

Read error messages
Sometimes error messages tell you everything you need to know to fix a problem. So read them. Thoroughly.

Google it
If you receive an obscure error, or one you do not understand, copy and paste it into google, minus any application specific information. Chances are you are not the first one to receive that error. The explanation and fix for many errors is often just a google away.

Identify the location
My first step is usually to identify the location of the problematic code. It may be as simple as reviewing an error message, or it may not. If there is no error, I pick a last known good state in the process and work from there. In other words, I pick a point in the code that works correctly. Then work forward, a few lines at a time, until I can identify where the code fails. Each time I move forward in the code, I re-check the state of my variables/objects. If the results are good, I repeat the process until I hit an area where the test fails. If it makes more sense to start from a first known bad state, then I simply reverse the process. Sometimes identifying the location also identifies the problem itself. If it does not, I continue debugging.

Isolate the conditions
Determine if a problem occurs all the time, or only under certain conditions. The answer often gives valuable clues about the cause of the problem and/or how to fix it.

Test the obvious first
Most of us like to believe we are above making simple mistakes, but we are not. So forget about ego and do not overlook the obvious ;) The simplest or most likely cause often turns out to be the cause. Since those conditions are often the quickest and easiest to test, check them first.

Do not make assumptions about the code
When debugging code, if you expect an if/else conditional to evaluate to true, verify that it actually does. Logic errors are easy to miss because they do not always cause an exception. So as you step through the code, always verify the expected results match the actual results.

My conclusion: While developing effective troubleshooting skills takes time, mostly it is just common sense.

...Read More

Tuesday, February 19, 2008

CF8 + MS SQL: Is this a bug I smell or just my brain overloading?

So I was happily writing up an example on using JDBC to process statements that return multiple results when I encountered a problem. It did not work. Now my first thought was that it must be a coding error. But having just successfully tested the code against MX7, I am thinking maybe not.

The goal was to execute a string containing multiple sql statements. Then use JDBC to return the results of each individual statement within the string. In the rather pointless example below, the first result would be the number of rows affected by the INSERT. The second would be the IDENTITY value created by the insert. The third a resultset of data returned by the final SELECT statement.


INSERT INTO MyTable ( Title, DateModified )
SELECT TOP 1 Title, getDate() FROM MyTable WHERE MyID = 2;

SELECT SCOPE_IDENTITY() AS NewlyInsertedID;

SELECT MyID, Title, DateModified FROM MyTable;


After much trial and error, I discovered that when I use the macromedia driver in ColdFusion 8, it does not process my statement correctly. It behaves as if the statement returns only one (1) result, when it actually returns (3). It does not work unless I first use SET NOCOUNT OFF. However, if I use the MS SQL Server driver it works as expected.

Can anyone else confirm they get the same results as I did with the code below? Just wondering if I should be reaching for a fire extinguisher or a bug report form ;)

Code

<!---
STEP 1: CREATE TEST TABLE. POPULATE WITH SAMPLE DATA
--->
CREATE TABLE MyTable (
MyID int identity(1,1),
Title varchar(100) NOT NULL,
DateModified datetime NULL
);

INSERT INTO MyTable ( Title, DateModified )
SELECT 'Transact-SQL', getDate() UNION
SELECT 'ColdFusion 8 (CFWACK)', getDate() UNION
SELECT 'iText in Action', getDate()

<!---
STEP 2: FILL IN DATABASE SETTINGS
--->

<cfset dbUserName = "my user name">
<cfset dbPassword = "my database password">
<cfset dbDriverClass = "macromedia.jdbc.sqlserver.SQLServerDriver">
<cfset dbJdbcURL = "jdbc:macromedia:sqlserver://localhost:1433;databaseName=MyDatabaseName;">


<!---
STEP 3: PROCESS STATEMENTS USING JDBC
--->
<cfscript>
// Open a database connection
Class = createObject("java", "java.lang.Class");
Class.forName( dbDriverClass );
manager = createObject("java", "java.sql.DriverManager");
connection = manager.getConnection( dbJdbcURL, dbUserName, dbpassword );

// Construct a few pointless sql statements to demonstrate
sqlToExecute = " INSERT INTO MyTable ( Title, DateModified ) "
& " SELECT TOP 1 Title, getDate() FROM MyTable WHERE MyID = 2; "
& " SELECT SCOPE_IDENTITY() AS NewlyInsertedID; "
& " SELECT MyID, Title, DateModified FROM MyTable; ";

// Execute the sql statements
statement = connection.createStatement();
isResultSet = statement.execute( sqlToExecute );
results = arrayNew(1);

// Process and dispay the results
do {

// The current result is a rowcount
if ( statement.getUpdateCount() GTE 0 ) {

WriteOutput("Processing rowCount = "& statement.getUpdateCount() &"<hr>");

}
// Otherwise, it is a resultset
else {

resultset = statement.getResultSet();
metadata = resultset.getMetaData();
columnCount = metadata.getColumnCount();
columnNames = arrayNew(1);

for (col = 1; col LTE columnCount; col = col + 1) {
arrayAppend(columnNames, metadata.getColumnName( javacast("int", col) ));
}

row = 0;
while (resultset.next()) {
row = row + 1;

WriteOutput("Processing row["& row &"]: ");
for (col = 1; col LTE columnCount; col = col + 1) {
// this is a no-no. but okay for demonstrating the problem
colValue = columnNames[col] &"="& resultset.getString( javacast("int", col) );
WriteOutput( colValue &" ");
}
WriteOutput("<br>");
}

WriteOutput("<hr>");
}

}
while (statement.getMoreResults() OR statement.getUpdateCount() NEQ -1);

statement.close();
connection.close();
</cfscript>


Actual Results
CF only displays (1) result

Processing rowCount = 1


Expected Results
CF should display (3) results

Processing rowCount = 1
--------------------------------------------------------------------------------
Processing row[1]: NewlyInsertedID=4
--------------------------------------------------------------------------------
Processing row[1]: MyID=1 Title=ColdFusion 8 (CFWACK) DateModified=2008-02-19 23:01:48.467
Processing row[2]: MyID=2 Title=iText in Action DateModified=2008-02-19 23:01:48.467
Processing row[3]: MyID=3 Title=Transact-SQL DateModified=2008-02-19 23:01:48.467
Processing row[4]: MyID=4 Title=iText in Action DateModified=2008-02-19 23:01:48.497

...Read More

Monday, February 18, 2008

SOT: SVG made easy with Inkscape

So I just discovered the wonders that are possible with SVG, thanks to an older entry on Barney Boisvert's blog. (Hey, better late than never ;) While the effects you can achieve are impressive, the SVG syntax is a bit daunting. I thought to myself there is no way people code this stuff by hand. Okay, maybe a few mutant programmers that write code in their sleep, are fluent in machine language, think notepad is the ultimate editor and that IDE's are for sissies. Needless to say I am not one of them, so off to google I went.

A quick search turned up a program called Inkscape. It has been around for a few years, so veterans may already be aware of it. But I just downloaded it this weekend and am very impressed with it. It is a graphical editor, like Illustrator, that makes handling SVG a breeze. The current stable release is 0.45.1, but the bleeding edge version 0.46 has a bunch of cool new features in it. So it is worth a look.

Now I freely confess I am "artistically challenged". But using only Inkscape and this Photoshop tutorial, I was able to create this cool aqua sphere in very little time. Admittedly an old look, but I like it.

It was a lot easier than I thought. Towards the end I even got bold and decided to play around with some of the effects ;) So lest you think I cannot follow instruction, that is why my image does not look exactly like the original ;)



You can also draw text on a path, will very little effort. Just draw a path, or a shape like a circle. Then draw some text. Select both the text and the path shape. Then select Text > Put On Path from the top menu. That is it. Save the result as an Plain .SVG file and you can modify it to work with dynamic text.




Two examples are drawing text on a circle or a spiral. While these examples are rather bland, you can see a more interesting use of text on a spiral on Jon Aquino's blog.



If you have not used Inkscape before, it is a great application. Definitely worth checking out!

ColdFusion Code (Based on code by Barney Boisvert)

See also Installing Batik 1.7 for instructions.


<!--- read in the file containing the svg text --->
<cffile action="read" file="#ExpandPath('spiralText.svg')#" variable="svgXML">

<!--- fill in the dynamic values --->
<cfset dynamicText = "Round and round we go. Where we stop, nobody knows. Round and round we go. ">
<cfset dynamicText = dynamicText & "Stop the ride. I am dizzy and want to get off. Round and round we go..">
<cfset svgXML = replaceNoCase(svgXML, "{fontSize}", "18px", "all")>
<cfset svgXML = replaceNoCase(svgXML, "{fontWeight}", "bold", "all")>
<cfset svgXML = replaceNoCase(svgXML, "{fontFamily}", "Arial", "all")>
<cfset svgXML = replaceNoCase(svgXML, "{displayText}", dynamicText, "all")>

<!--- get my javaloader instance from the server scope --->
<cfset javaLoader = server[MyUniqueKeyForJavaLoader]>

<!--- Based on code by Barney Boisvert --->
<!--- create an image from the svg text. save it to a file and display it --->
<cfset transcoder = javaLoader.create("org.apache.batik.transcoder.image.PNGTranscoder").init()>
<cfset inputStream = createObject("java", "java.io.StringBufferInputStream").init(svgXML)>
<cfset input = javaLoader.create("org.apache.batik.transcoder.TranscoderInput").init(inputStream)>
<cfset outputStream = createObject("java", "java.io.FileOutputStream").init(ExpandPath("spiralText.png"))>
<cfset output = javaLoader.create("org.apache.batik.transcoder.TranscoderOutput").init(outputStream)>
<cfset transcoder.transcode(input, output)>
<cfset outputStream.close()>
<img src="spiralText.png">


SpiralText.svg

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!-- Created with Inkscape (http://www.inkscape.org/) -->
<svg
xmlns:svg="http://www.w3.org/2000/svg"
xmlns="http://www.w3.org/2000/svg"
xmlns:xlink="http://www.w3.org/1999/xlink"
version="1.0"
width="744.09448"
height="1052.3622"
id="svg2220">
<defs
id="defs2222" />
<g
id="layer3">
<path
d="M 191.62299,620.98969 C 203.24843,638.55675 195.73477,662.11559 178.69969,673.05495 C 156.04225,687.60481 125.98484,677.85885 112.21349,655.85044 C 94.285527,627.19923 106.67232,589.52943 134.54618,572.47742 C 170.16978,550.68446 216.65637,566.15584 237.47088,600.86248 C 263.64902,644.51256 244.61396,701.11114 202.03179,726.20328 C 149.22546,757.32016 81.128622,734.20688 51.210357,682.6308 C 14.567727,619.46258 42.30929,538.38991 104.07353,503.06359 C 178.88529,460.27469 274.50291,493.23021 315.85271,566.45287 C 365.44201,654.26609 326.65114,766.08946 240.6236,814.11178 C 184.43295,845.47855 115.25976,847.23777 56.70371,821.19779"
transform="matrix(-6.4631751e-2,-0.9979092,0.9979092,-6.4631751e-2,-287.68055,656.94182)"
style="font-size:18px;font-style:normal;font-variant:normal;font-weight:normal;font-stretch:normal;text-align:start;line-height:125%;writing-mode:lr-tb;text-anchor:start;fill:none;fill-rule:evenodd;stroke:none;stroke-width:1px;stroke-linecap:butt;stroke-linejoin:miter;stroke-opacity:1;font-family:Bitstream Vera Sans"
id="path2368" />
<text
transform="translate(-145.46196,-236.3757)"
style="font-size:18px;font-style:normal;font-variant:normal;font-weight:normal;font-stretch:normal;text-align:start;line-height:125%;writing-mode:lr-tb;text-anchor:start;fill:#000000;fill-opacity:1;stroke:none;stroke-width:1px;stroke-linecap:butt;stroke-linejoin:miter;stroke-opacity:1;font-family:Bitstream Vera Sans"
id="text2377"
xml:space="preserve"><textPath
xlink:href="#path2368"
id="textPath2381"><tspan
style="font-size:{fontSize};font-style:normal;font-variant:normal;font-weight:{fontWeight};font-stretch:normal;text-align:start;line-height:125%;writing-mode:lr-tb;text-anchor:start;font-family:{fontFamily}"
id="tspan2379">{displayText}</tspan></textPath></text>
</g>
</svg>

...Read More

Cool text effects with SVG and Batik

I was reading an older, but very cool entry about Batik and SVG, on Barney Boisvert's blog. He showed how you could can use Batik and ColdFusion to convert SVG content into different formats like png's, jpg's, etcetera. Having never used SVG myself, I was really impressed at how easy it is to convert what is basically text into some impressive graphics.

In my travels I stumbled across a cool text effect that can be achieved with SVG, on the Luxor site. So I decided to post an example of how you could modify it to work with dynamic text. For simplicity I stored the text in a file. Though if you prefer, you could simply build the SVG string using <cfsavecontent>.

The code below produces this image. But you can modify it to use whatever text, color, font-size and dimensions you want. Pretty cool, huh? The beauty of it is that it will work with MX7 too. In case you do not have access to ColdFusion 8's cool new image functions.



I made a few minor modifications but all credit and glory for the code, and the great idea, go to Barney Boisvert and the Luxor site for the example. (I do not know who wrote it). Note, the built in Batik classes in ColdFusion were not sufficient to run the example, so it uses the JavaLoader.cfc to load Batik 1.7. (See Installing Batik 1.7 for instructions).

ColdFusion Code


<!--- read in the file containing the svg text --->
<cffile action="read" file="#ExpandPath('coolText.svg')#" variable="svgXML">

<!--- fill in the dynamic values --->
<cfset svgXML = replaceNoCase(svgXML, "{x}", 20, "all")>
<cfset svgXML = replaceNoCase(svgXML, "{y}", 80, "all")>
<cfset svgXML = replaceNoCase(svgXML, "{height}", 125, "all")>
<cfset svgXML = replaceNoCase(svgXML, "{width}", 680, "all")>
<cfset svgXML = replaceNoCase(svgXML, "{fontSize}", 70, "all")>
<cfset svgXML = replaceNoCase(svgXML, "{backgroundColor}", "gold")>
<cfset svgXML = replaceNoCase(svgXML, "{displayText}", "ColdFusion Rocks!", "all")>

<!--- create an image from the svg text. save it to a file and display it --->
<cfset transcoder = javaLoader.create("org.apache.batik.transcoder.image.PNGTranscoder").init()>
<cfset inputStream = createObject("java", "java.io.StringBufferInputStream").init(svgXML)>
<cfset input = javaLoader.create("org.apache.batik.transcoder.TranscoderInput").init(inputStream)>
<cfset outputStream = createObject("java", "java.io.FileOutputStream").init(ExpandPath("savedImage.png"))>
<cfset output = javaLoader.create("org.apache.batik.transcoder.TranscoderOutput").init(outputStream)>
<cfset transcoder.transcode(input, output)>
<cfset outputStream.close()>
<img src="savedImage.png">


SVG Code
Source: http://luxor-xul.sourceforge.net/talk/jug-nov-2002/slides.html#svg-10

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE svg PUBLIC "-//W3C//DTD SVG 1.0//EN" "http://www.w3.org/TR/SVG/DTD/svg10.dtd">
<svg width="{width}" height="{height}">
<filter id="dropShadow" filterUnits="objectBoundingBox" width="1.4" height="1.4">
<feGaussianBlur in="SourceAlpha" stdDeviation="4" />
<feOffset dx="4" dy="4" />
<feComponentTransfer result="shadow">
<feFuncA type="linear" slope=".5" intercept="0" />
</feComponentTransfer>
</filter>

<filter id="emboss" >
<feGaussianBlur in="SourceAlpha" stdDeviation="2" result="blur"/>
<feSpecularLighting in="blur" surfaceScale="-3" style="lighting-color:white"
specularConstant="1" specularExponent="16" result="spec" kernelUnitLength="1" >
<feDistantLight azimuth="45" elevation="45" />
</feSpecularLighting>
<feComposite in="spec" in2="SourceGraphic" operator="in" result="specOut"/>
</filter>

<rect x="0" y="0" width="100%" height="100%" style="fill:{backgroundColor}" />
<g style="font-size:{fontSize}; font-weight:bold;">
<text x="{x}" y="{y}" style="filter:url(#dropShadow)">{displayText}</text>
<text x="{x}" y="{y}" style="fill: black;">{displayText}</text>
<text x="{x}" y="{y}" style="filter:url(#emboss)">{displayText}</text>
</g>
</svg>

...Read More

Installing Batik 1.7 with ColdFusion

I decided to write up a brief set of instructions on installing Batik 1.7 with ColdFusion, as a reference point for myself.


  1. Download and install the JavaLoader.cfc from riaforge.org.
    Example: I chose to install the JavaLoader directly beneath the webroot: C:\CFusionMX7\wwwroot\javaLoader\

  2. Download and install Batik 1.7 from apache.org.
    Example: My jars are installed in a folder named batik: C:\CFusionMX7\wwwroot\batik\batik-1.7



After installing the jar, restart the CF server.

Important note about using the JavaLoader.cfc

The article Using a Java URLClassLoader in CFMX Can Cause a Memory Leak recommends that you store a single instance of the JavaLoader in the server scope, rather than creating a new object each time. This prevents a memory leak caused by a bug in ColdFusion MX. I do not know if this bug exists in ColdFusion 8.

So in my Application.cfc I load the JavaLoader into the server scope, if it does not already exist. Then make the variable MyUniqueKeyForJavaLoader available to all pages in the OnRequest() function. Since I was not able to determine exactly which jars were needed I decided to use <cfdirectory> to load all jars in my /batik-1.7 directory.


<cfcomponent>
<cfset this.name = "BatikTests">
<cfset this.Sessionmanagement = true>
<cfset this.loginstorage = "session">


<cffunction name="onApplicationStart">
<!--- note, this is actually a harcoded UUID value --->
<cfset MyUniqueKeyForJavaLoader = "xxxxxxxx-xxxx-xxxx-xxxxxxxxxxxxxxxx">

<!--- if we have not already created the javaLoader --->
<cfif NOT StructKeyExists(server, MyUniqueKeyForJavaLoader)>

<!--- get a listing of all batik jars (windows O/S) --->
<cfdirectory directory="#ExpandPath('/batik/batik-1.7/')#" filter="*.jar" recurse="true" name="jars">
<cfquery name="jarList" dbtype="query">
SELECT Directory +'\'+ Name AS PathToJar
FROM jars
</cfquery>

<!--- create an array of jar paths --->
<cfset jarPaths = listToArray(valueList(jarList.PathToJar, "|"), "|")>

<!--- get an exclusive lock on the server scope --->
<cflock scope="server" type="exclusive" timeout="10">
<!--- re-verify the javaloader was not already created --->
<cfif NOT StructKeyExists(server, MyUniqueKeyForJavaLoader)>
<cfset server[MyUniqueKeyForJavaLoader] = createObject("component", "javaloader.JavaLoader").init(jarPaths)>
</cfif>
</cflock>

</cfif>
</cffunction>

</cfcomponent>

...Read More

ColdFusion 8: Query results are undefined when using INSERT + SELECT FROM with SCOPE_IDENTITY()


UPDATE: This entry applies to the behavior of the built-in ColdFusion 8 MS SQL Server driver. If you are using the MS SQL JDBC driver, you may observe different behavior. You can read more about some of the differences in these two entries: CF8 + MS JDBC 1.2 Driver - .. And for my next trick, I will make this query disappear! and CF8 + MS JDBC 1.2 Driver - Generated key issues. Curiouser and Curiouser.


Recently I have seen a few questions in the forums about why the following MS SQL statement works under MX7, but no longer works with ColdFusion 8. The posters seemed to think it is a bug, but after thinking about it, I disagree. The statement below uses TOP to insert a single record from one table, into another table. Then returns the generated IDENTITY value as a column in the defined query.


<cfquery name="create" datasource="#dsn#">
INSERT INTO OtherTable ( Title, DateModified )
SELECT TOP 1 Title, DateModified
FROM MyTable
WHERE MyID = <cfqueryparam value="2" cfsqltype="cf_sql_integer">
SELECT SCOPE_IDENTITY() AS OtherID
</cfquery>

<cfif IsDefined("create")>
<cfdump var="#create#">
<cfelse>
query <b>create</b> is not defined.
</cfif>


If you run the statement with MX7, it successfully returns the inserted identity value in the declared query variable. However, in ColdFusion 8 the query variable "create" is always undefined. To make the query work with ColdFusion 8, you must wrap the statements in a set nocount on/off. Once you do that the query variable will be defined.

<cfquery name="create" datasource="#dsn#">
SET NOCOUNT ON

INSERT INTO OtherTable ( Title, DateModified )
SELECT Title, DateModified
FROM MyTable
WHERE MyID = <cfqueryparam value="2" cfsqltype="cf_sql_integer">
SELECT SCOPE_IDENTITY() AS OtherID

SET NOCOUNT OFF
</cfquery>


The change in behavior is clearly related to the new cfquery features introduced in ColdFusion 8. Even as far back as MX, most database connections were handled using JDBC. JDBC has the ability to access detailed information about each sql statement executed. For example it can access things like


  • The output of PRINT statements

  • ANSI warnings

  • The number of rows affected by a statement

  • IDENTITY values generated by an INSERT



However, prior to ColdFusion 8, cfquery did not provide a direct method for accessing some of these values. With the introduction of ColdFusion 8, you can now access "rows affected" and IDENTITY values using cfquery's result attribute. It is a great feature, but it was bound to change some of cfquery's behavior.

If you are familiar with JDBC, you already know that most sql statements return some type of result. UPDATE, INSERT and DELETE statements return the number of rows affected by the statement. Whereas SELECT statements also return a Resultset (ie data query). But all of these statements return some result. This was not an issue in MX because cfquery did not return the rows affected for UPDATE, INSERT and DELETE statements. So it could ignore the row counts, and only return resultsets. That is why the original query works with MX7.

However, ColdFusion 8 changed all that. It now makes the number of "rows affected" available in the result attribute. Which means cfquery can no longer ignore statements that do not return a resultset. Otherwise you would not be able to obtain the number of rows affected if your query contained only an UPDATE, INSERT or DELETE statement.

Since cfquery cannot return multiple results, it is clearly returning the first "result" it encounters. In this case the first "result" is from the INSERT statement.

<cfquery name="create" datasource="#dsn#" result="insertResults">
INSERT INTO OtherTable ( Title, DateModified )
SELECT TOP 1 Title, DateModified
FROM MyTable
WHERE MyID = <cfqueryparam value="2" cfsqltype="cf_sql_integer">
SELECT SCOPE_IDENTITY() AS OtherID
</cfquery>

<cfif IsDefined("create")>
<cfdump var="#create#">
<cfelse>
query <b>create</b> is not defined.
</cfif>

<cfdump var="#insertResults#">

If you use the result attribute, and dump the data, you will see that one (1) record was inserted. Since INSERT's do not return a resultset, the query variable "create" is undefined, which makes sense. It is also consistent with how INSERT's are treated in MX7.



However, when you use set nocount on/off, it suppresses the row count from the INSERT. So ColdFusion sees only one "result": the result of the SELECT statement. With only one result to process, it works and the query variable is defined.

Now, perhaps it is just the java in me ;) but this behavior makes sense to me and I do not consider it a bug. While I do not know what the designers of cfquery intended, I suspect they did not mean for it to be a substitute for stored procedures. The fact that cfquery can only return a single resultset tends to support that theory. While the ability to run multiple sql statements in a cfquery is great, I still do not expect it to behave like a full-fledged stored procedure or like cfstoredproc. I imagine if it were intended to do that, cfquery name and result would return an array of queries and row counts, not a single value. But again, that is just my opinion. I fully expect some people to disagree with me ;)

I may write up an entry later on how the results of multiple statements are processed using JDBC. Just to give some insight into the process (and my opinions on the topic ;-) So if that aspect interests you, stay tuned.

As always comments, suggestions, corrections are welcome ;)

...Read More

Thursday, February 14, 2008

Profiler for SQL Server Express 2005

I have been working with SQL Server Express 2005 lately. While it is great that it is free, I often miss some of the tools available in enterprise versions. Today I came across an open source project that provides a Profiler tool for Express. I confess I have not done much with it yet, beyond verifying it works. But if you have been looking for a Profiler tool you might want to check it out.

http://sqlprofiler.googlepages.com/

...Read More

OT: On the subject of nothing, why did someone create the blink tag?

While perusing a site on CSS I noticed it contained a CSS version of the old <blink> tag. Let me just say that in the few moments it took to me read the pertinent section I remembered exactly what is so annoying about <blink>. Every second or so my eyes would jump, almost involuntarily, to the irritating movement in the corner of my eye. Maybe it was an instinctive fear response: the blink tag is coming to get me! ;) Whatever the reason, it was very distracting.

For a moment there I started to daydream. Imagining the creator of the blink tag locked in a large room, filled with hundreds and hundreds of neon signs. Each sign a different size and color. All of the signs blinking at alternate rates, twenty-four hours a day. All spelling out:


    B -- L -- I -- N -- K


It really makes you wonder what would possess someone to create a blinking tag. Let alone include it in the CSS specifications! Now there may actually be a good use for blink out there. But while I was trying to read the CSS article, for the second time, I sure as heck could not think of it.

...Read More

CF_SQL_DATE versus CreateODBCDate

I was highly tempted to file this entry under a "Duh!" category ;) Why? Because I discovered today that I have been laboring under a misconception about cfqueryparam and the cf_sql_date type. While I have always liked cfqueryparam, and its many features, I could never understand why it did not handle date/time values like the CreateODBCDate function. Surprise! Today I discovered that it actually does.

When using CreateODBCDate in a query, the debug output clearly shows that it truncates the time portion and sends a date only to the database. This behavior comes in handy when querying columns that contain both date and time. You can easily to retrieve all records for a specific date range, because you do not have to worry about the time portion.


<cfset dateTimeNow = createDateTime(2008, 2, 14, 9, 0, 0)>

<--- original query --->
<cfquery name="getRecords" datasource="#dsn#">
SELECT TestID, ModifiedDate
FROM Test
WHERE ModifiedDate >= #CreateODBCDate(dateTimeNow)#
</cfquery>

<--- query debug output --->
SELECT TestID, ModifiedDate
FROM Test
WHERE ModifiedDate >= {d '2008-02-14'}


However, when using cfqueryparam it appears to send both the date and time value, but it does not.


<--- original query --->
<cfset dateTimeNow = createDateTime(2008, 2, 14, 9, 0, 0)>
<cfquery name="getRecords" datasource="#dsn#">
SELECT TestID, ModifiedDate
FROM Test
WHERE ModifiedDate >= <cfqueryparam value="#dateTimeNow#" cfsqltype="cf_sql_date">
</cfquery>


<--- query debug output --->
SELECT TestID, ModifiedDate
FROM Test
WHERE ModifiedDate >= ?

Query Parameter Value(s) -
Parameter #1(cf_sql_date) = {ts '2008-02-14 09:00:00'}


Using the MS SQL Profiler, against a SQL 2000 database, I was able to see that the actual value passed to the database is a date only.


exec sp_execute 2, 'Feb 14 2008 12:00AM'


So the parameter in the debug output is the value passed into the query. It is not the final value that is used by the database. If you think about it, it makes sense. But it did leave me with the mistaken impression that cfqueryparam was not handling the date values correctly.

Now I do not know if you can use cf_sql_date with date/time columns against an MS SQL 2005 database. But you can use it with MySQL as you can see from the results below. Added to that the Connector/J documentation says a date/time data type can always be converted to either a java.sql.Date or java.sql.Timestamp. So I feel comfortable using cf_sql_date with MySQL date/time columns.



I guess this all goes to show you do not always know something as well as you think you do ;)

...Read More

Friday, February 8, 2008

Weird behavior with ImageDrawText and optional Font Attributes

A few weeks ago I noticed some strange behavior when using ImageDrawText without supplying all three of the major text attributes: font, size and style. According to the documentation, the default font size is 10 pt. If you do not supply a font when using the function, text will be drawn in the default system font.

The behavior I observed on windows is a bit different:


  • If no font attributes were supplied, the text looks like Arial 12pt

  • If only a size was specified, the text looks like Times New Roman

  • If only a style was specified, the text looks like Times New Roman 10pt

  • If only a font was specified, the text looks like 10pt





As I understand it, this behavior is bug related. I did submit a question about this to livedocs. Unfortunately it was not approved due to a policy about bug information in the comments. So I thought I would post it here in case it is helpful to anyone. My conclusion: for the most consistent results, its probably best to always specify all three text characteristics: font, size, style.

Test Code

<!--- no attributes produces > Arial 12pt --->
<cfset img = ImageNew( "", 300, 80, "rgb", "lightgray" )>
<cfset ImageSetDrawingColor( img, "black" )>
<cfset ImageDrawText( img, "A string with no font attributes", 15, 15)>
<cfset ImageSetDrawingColor( img, "##006633" )>
<cfset attr = { font="arial", style="plain", size="12" }>
<cfset ImageDrawText( img, "Looks like Arial 12pt", 15, 35, attr)>
<cfimage action="writeToBrowser" source="#img#">

<!--- size only produces > Times New Roman 12pt --->
<cfset img = ImageNew( "", 300, 80, "rgb", "lightgray" )>
<cfset ImageSetDrawingColor( img, "black" )>
<cfset attr = { size=12 } >
<cfset ImageDrawText( img, "A string with size=12 only", 15, 15, attr)>
<cfset ImageSetDrawingColor( img, "##006633" )>
<cfset attr = { font="Times New Roman", style="plain", size="12" }>
<cfset ImageDrawText( img, "Looks like Times New Roman 12pt", 15, 35, attr)>
<cfimage action="writeToBrowser" source="#img#">

<!--- style only produces > Times New Roman 10pt --->
<cfset img = ImageNew( "", 300, 80, "rgb", "lightgray" )>
<cfset ImageSetDrawingColor( img, "black" )>
<cfset attr = { style="plain" } >
<cfset ImageDrawText( img, "A string with style='plain'", 15, 15, attr)>
<cfset ImageSetDrawingColor( img, "##006633" )>
<cfset attr = { font="Times New Roman", style="plain", size="10" }>
<cfset ImageDrawText( img, "Looks like Times New Roman 10pt", 15, 35, attr)>
<cfimage action="writeToBrowser" source="#img#">

<!--- font only produces > correct font size 10pt --->
<cfset img = ImageNew( "", 300, 80, "rgb", "lightgray" )>
<cfset ImageSetDrawingColor( img, "black" )>
<cfset attr = { font="Arial" } >
<cfset ImageDrawText( img, "A string with font='Arial'", 15, 15, attr)>
<cfset ImageSetDrawingColor( img, "##006633" )>
<cfset attr = { font="Arial", style="plain", size="10" }>
<cfset ImageDrawText( img, "Looks like Arial 10pt", 15, 35, attr)>
<cfimage action="writeToBrowser" source="#img#">

<!--- size and style produces > Times New Roman (size) (style)--->
<cfset img = ImageNew( "", 300, 80, "rgb", "lightgray" )>
<cfset ImageSetDrawingColor( img, "black" )>
<cfset attr = { size="12", style="bold" } >
<cfset ImageDrawText( img, "A string with size=12, style='plain'", 15, 15, attr)>
<cfset ImageSetDrawingColor( img, "##006633" )>
<cfset attr = { font="Times New Roman", style="bold", size="12" }>
<cfset ImageDrawText( img, "Looks like Times New Roman 12pt", 15, 35, attr)>
<cfimage action="writeToBrowser" source="#img#">

<!--- font and style produces > Arial (style) 10pt --->
<cfset img = ImageNew( "", 300, 80, "rgb", "lightgray" )>
<cfset ImageSetDrawingColor( img, "black" )>
<cfset attr = { font="Arial", style="plain" } >
<cfset ImageDrawText( img, "A string with font='arial', style='plain'", 15, 15, attr)>
<cfset ImageSetDrawingColor( img, "##006633" )>
<cfset attr = { font="Arial", style="plain", size="10" }>
<cfset ImageDrawText( img, "Looks like Arial 10pt", 15, 35, attr)>
<cfimage action="writeToBrowser" source="#img#">

...Read More

Using ColdFusion 8's ImageGetBlob Function with GIF's

I responded to a question yesterday on the adobe forums that reminded me of a problem I had with ImageGetBlob a few weeks ago. I was surprised to discover the function does not seem to work with GIF images. Now perhaps I overlooked it, but I do not remember the documentation mentioning that ImageGetBlob does not support GIF's. Anyway, when I tried using ImageGetBlob on a GIF image ColdFusion returned this error


An error occured in converting the image to base64 in the required image encoding. Base64 images can be created with ColdFusion in BMP,JPEG,PNG,PNM,and TIFF formats


The solution I came up with was to use the ImageIO class to write the data to a ByteArrayOutputStream and then use the toByteArray() method to grab the image bytes.


<cfscript>
// read in a sample image
pathToImage = ExpandPath("MyGIFImage.gif");
imageExtension = listLast(pathToImage, ".");
img = ImageNew( pathToImage );

// extract underlying BufferedImage
buffered = ImageGetBufferedImage(img);

// write out image bytes
ImageIO = createObject("java", "javax.imageio.ImageIO");
outStream = createObject("java", "java.io.ByteArrayOutputStream").init();
ImageIO.write( buffered, imageExtension, outStream );
imageBytes = outStream.toByteArray();
</cfscript>


Note, this method requires that a compatible image writer is registered. You can view the informal format names understood by the registered writers using static methods of the ImageIO class.


<cfset ImageIO = createObject("java", "javax.imageio.ImageIO")>
<cfdump var="#ImageIO.getWriterFormatNames()#">
<cfdump var="#ImageIO.getWriterFileSuffixes()#">


While this works, I would be interested to hear if anyone else has come up with a different or simpler method.

...Read More

  © Blogger templates The Professional Template by Ourblogtemplates.com 2008

Header image adapted from atomicjeep