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:
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:
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:
I placed an example snippet below. Thanks to Rupesh Kumar for providing this information.
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!
||DOUBLE, MONEY, NUMERIC
||CHAR, NCHAR, VARCHAR, NVARCHAR
||DATETIME, SMALLDATETIME, TIMESTAMP
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.
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:
SET Question_Hierarchy_ID = '/1/1/'
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:
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.
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
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.
- Go to Tools > Options > Designers > Table and Database Designers via the menu
- 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.