Console Logging of ColdFusion ORM SQL

If you read my post on getting the ColdFusion Builder Console working and use ORM, you may have run into some further questions.

When generating ORM, it’s wise to monitor what SQL queries Hibernate is generating for you, both for performance and debugging reasons. (Wasn’t ORM supposed to make my life easier?).

To start logging ORM’s SQL set this property in your application.cfc:

<cfset this.ormsettings.logsql="true">

You’ll may notice however that the default configuration will not show DDL queries used for creating or updating tables nor will it show the parametrized values (just a ?).

To enable these things look at <cf_home>\lib\log4j.properties (in my case it’s C:\ColdFusion9\lib\log4j.properties).

To enable logging of parametrized values uncomment and change the value for log4j.logger.org.hibernate.type to look like this:

log4j.logger.org.hibernate.type=DEBUG

It seems like a little overkill on what this ends up returning because not only will it return your parametrized values but also what each column returns. I wish I could disable the latter.

To enable logging of exports and updates (DDL) uncomment and change the value for log4j.logger.org.hibernate.tool.hbm2ddl to look like this:

log4j.logger.org.hibernate.tool.hbm2ddl=DEBUG, HIBERNATECONSOLE

I placed an example snippet below. Thanks to Rupesh Kumar for providing this information.

Advertisements

#sql-queries

SQL to ColdFusion ORMType Reference

I have not been able to find a good reference chart out there that maps SQL Data Types to ColdFusion ORM Data Types. It’s always really been my best guess. So I’m going to start a reference chart here that as I figure it out I’ll update. If you have any input on this please comment and I will update. Thanks!

ORMType SQL MySQL
big_decimal DECIMAL, MONEY DECIMAL
binary BINARY, VARBINARY TINYBLOB
blob TINYBLOB
Boolean [SMALLINT], BIT BIT
clob LONGTEXT
date DATE DATE
double DOUBLE, MONEY, NUMERIC DOUBLE
character, char CHAR
float REAL, FLOAT FLOAT
integer, int INT INT
long BIGINT BIGINT
serializable TINYBLOB
short SMALLINT SMALLINT
string CHAR, NCHAR, VARCHAR, NVARCHAR VARCHAR
text TEXT, NTEXT LONGTEXT
timestamp DATETIME, SMALLDATETIME, TIMESTAMP DATETIME
true_false CHAR
yes_no CHAR

SSIS Import/Export Wizard: “Unexpected Unrecoverable Error”

Using Microsoft’s SQL Server Management Studio’s Import and Export Wizard, I would get “Unexpected Unrecoverable Error” message when I would click “Messages…” or view a report when an import failed. This was very annoying and I wasn’t getting any work done this way.

Thanks to the SQL Server Forums I was able to resolve this issue by installing the Extended .NET Framework 4. If you have the Client Profile version installed, you will still apparently get this error. Be sure to do a Windows Update after the install as there is a security patch for it.

BTW, this import Wizard is a big fail for Microsoft. SQL Server 2000’s version did much more – now my life is just plain more difficult thanks to their “upgrade” – just say’n.

I am running SQL Server Management Studio 2008 R2 64-Bit on Windows 7 64-Bit.

Can Not Edit Hierarchyid in MS SQL SMS Editor

I’m basing my current project around SQL 2008’s hierarchyid data type, which is completely new to me. One frustrating issue I have found is the inability to edit any hierarchyid type’s data as a string inside the Microsoft SQL Server Management Studio’s edit feature. Granted I can control the data via the New Query feature, but I tend to use the interactive editor for quick and easy access to my data during the development process.

Take the following example:

Notice the hierarchyid data is converted into a string to be shown. However if you where to try and update the current value of ‘/1/’ to ‘/1/1/’ or update the current NULL value to ‘/1/1/’ then you would receive an error stating “Invalid cast from ‘System.String’ to ‘Microsoft.SqlServer.Types.SqlHierarchyId'”.

I’m not sure what data would even be acceptable in the field. I tried entering ‘0x5AC0’ for the NULL data and still received an error.

So from what I can see is this data type is unchangeable inside the editor.

The workaround for this would be to run the following TSQL:

UPDATE Table1
SET Question_Hierarchy_ID = '/1/1/'
WHERE ID=1

SQL – Delete Orphans

In the 1930’s Miss Hannigan wanted to delete orphan Annie, but now we have child neglect laws that prohibit that. Good thing we now have SQL.

Lets say we have two SQL tables that contains names of team members. One is for softball and one is for baseball. We want to remove all players from softball that do not play baseball. You could do this in a loop or a subquery, but if you have team data from the past 100 years this may become quite costly.

Here’s a quick way to make this happen:

DELETE S
FROM Softball S
LEFT JOIN Baseball B ON (S.Name = B.Name)
WHERE S.Name IS NULL

What this is doing is matching the table Softball with Baseball. Any orphaned Softball players (no match with Baseball) end up getting deleted.

SQL VARCHAR(MAX): The New TEXT

Normally, if I’ve ever needed a large amount of text data stored in a database field, I would use a column type of text. I would usually do this when going beyond 256 characters. I think this stems back to use MS SQL 6.

Now as of SQL 7, the varchar type can hold around 8000 characters (8k) and nvarchar can hold around 4000 characters. This limitation stems from the 8KB internal page size SQL Server uses to save data to disk. But who really wants to deal with putting in client-side validation of 8k characters when you can just make it unlimited?

But the pain from using the type text comes in when trying to query against it. For example grouping by a text type is not possible.

Another downside to using text types is increased disk IO due to the fact each record now points to a blob (or file).

Most clients that I’ve worked on are still on SQL 2000, and maybe 2005 – so I haven’t really caught up to the nifty 2005 and 2008 changes yet. Reason? MS SQL Servers are freak’n expensive!

In SQL Server 2005 Microsoft added support for the varchar(max) and nvarchar(max) data types. The difference between varchar(8000) and varchar(max) is that it can now store up to 2GB of data (about 2,147,483,647 characters) instead of 8K. That seems to be plenty in my books for anything I need it for. Continue reading

SQL Management Studio 2008 Error: Saving changes is not permitted

Recently I came accross a blockade after upgrading my SQL Management Studio from 2005 to 2008. All of a sudden if I added a “no-null” option column in a table or inserted a column in the middle of the existing schema I could not save. At first I thought there was some hidden permission I was missing, but after futher research I find out it’s a setting in the Studio itself.

The full error is: Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created.

To get rid of this annoying “feature”, take these steps.

  1. Go to Tools > Options > Designers > Table and Database Designers via the menu
  2. Uncheck the option ‘Prevent saving changes that require table re-creation

Note: This is a safety feature and turning this off opens you up to possible loss of data, so make sure you know what you’re modifying or at least make a backup.