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

2 comments:

Anonymous,  February 20, 2008 at 9:37 PM  

This may sound dumb, but are you SURE that you are allowing multiple statements in the CF Admin for that DSN? I *think* multiple statements is disabled by default.

cfSearching February 21, 2008 at 6:09 AM  

No, it is actually a good question. But yes, I believe ms sql does allow multiple statements by default. Normally, if a driver does not allow multiple statements it generates an error, rather than suppressing the results.

The statements do work using MS's jdbc driver. Which suggests the problem may be with the macromedia driver. The problem does not occur with any set of multiple statements. Just this one. Though there may be other problematic combinations.

I was just curious if anyone else got the same results out of the box. Again, that would suggest to me it is not a configuration problem, but an issue with the driver.

  © Blogger templates The Professional Template by Ourblogtemplates.com 2008

Header image adapted from atomicjeep