When you take development code live, you need to make sure that the production database has all of the tables and columns it needs.
Fortunately, you can define the table structure that you need in a component (or elsewhere) and DataMgr (if you've read my blog before, you saw that coming) can make sure that the required tables and column exist in the database.
The same approach can be used for to self-install tables for an application (I used this on CodeCop).
Watch the presentation.
View other DataMgr presentations.
If you use SQL Server and you aren't interested in DataMgr or you need to synchronize more than tables and column, Red Gate's SQL Compare looks to be a very good tool (I have personally heard it highly recommended by two different users this week).
UPDATE
I just saw someone mention that they used AdeptSQL Diff for this purpose as well. So you might investigate that in addition to SQL Compare.

As a result of Ray Camden's coding contest, I made some changes to my DataMgr that have resulted in a new release.

Since Ray extended the deadline a day, I ended up wasting even more time on the project than I already had. Fortunately, I added a feature today to DataMgr and the project entry that I am very proud of.

I won't show my project entry until Ray has reviewed it, but I will talk about the new features in DataMgr.

Least exciting is a series of small bug fixes.

More exciting is that DataMgr has some new deployment options. It is no longer required that call DataMgr from com.sebtools (though I would still suggest it).

You can also pass the database in to DataMgr instead of having to call the DataMgr component for that database directly.

So, where you had to do this to load DataMgr using MS SQL with a datasource of request.dsn:
<cfset DataMgr = CreateObject("component","com.sebtools.DataMgr_MSSQL").init(request.dsn)>

You now have the option of doing this instead:
<cfset DataMgr = CreateObject("component","com.sebtools.DataMgr").init(request.dsn,"MSSQL")>


Added today is the ability to ask DataMgr what databases it supports in its current installation.
<cfset qDatabases = DataMgr.getSupportedDatabases()>

This will return a query of the databases supported by DataMgr.

It asks for this information from the DataMgr components in its directory. So, if you don't have DataMgr_MYSQL.cfc installed, it won't return MySQL in the query. If you add that file and reload the DataMgr component, it wil be in the query.

DataMgr will also now report the SQL it used if it fails to create or alter a table or tables.

Taken together, this allowed me put together an installation for my project that I am really proud of (more details on that later).

DataMgr is free and open source.

Free free to read about and download DataMgr or watch presentations on DataMgr.

What a fool I am, in three presentations on DataMgr I have yet to cover installation. This fourth one (2 minutes) remedies that. I have also organization the presentations and the documentation a little bit better.

Watch the presentation or go to all DataMgr presentations.

My third presentation (2 minutes) covers how to save a custom sort order from a comma-delimited list using DataMgr.

This presentation is the third in a series of short presentations on DataMgr.

Watch the presentation.

Feel free to download DataMgr and give it a try. It is free and open-source.

My second presentation (1.5 min this time) covers switching from CFINSERT/CFUPDATE to DataMgr and some of the advantages of doing so.

This presentation is the second in a series of short presentations on DataMgr.

Watch the presentation.

Feel free to download DataMgr and give it a try. It is free and open-source.

I just uploaded a presentation on how to switch from SQL for inserts and updates to using DataMgr.  I am hoping this will be the first of a series of presentations on using DataMgr. Each will be short (this one is two minutes long) and cover one aspect of DataMgr.

Watch the presentation.

(this supercedes my post on inserting data with DataMgr).

Feel free to download DataMgr and give it a try. It is free and open-source.

I would appreciate any feedback.

DataMgr 1.1 is out today. It fixes a few small bugs and adds a few enhancements that should make more common database interactions easier.

New methods in 1.1:

• getMaxRowsPrefix
• getMaxRowsSuffix
• getNewSortNum
• saveSortOrder

The first two methods help limit the rows returned from a query. Basically, getMaxRowsPrefix() returns "TOP X" for all databases except MySQL and getMaxRowsSuffix() returns "LIMIT X" for MySQL (where "X" is the maximum rows to return).

The saveSortOrder() method sets the values of a numeric field representing sort-order to the order you specifiy. Just pass the name of the table that you want to sort, the name of the column used to indicate the sort-order, and a comma delimited list of the primary key values in the desired order.

The getNewSortNum() method returns the value to use for the given numeric sort-order field to put a record after all other records. I use to put a new record after all existing records.

The getRecords method also has a new argument: "maxrows", which you can use to limit the number of results returned in the query (using - you guessed it - the first two methods mentioned earlier).

Additionally, I have added the ability to populate a table with data when using loadXML to ensure the existence of required tables and columns. This helps with easy deployment, but is beyond the scope of this post.

Support for MS Access is still somewhat limited. It can be used on ColdFusion MX 7 and higher as normal. It can only be used on previous version by loading tables via loadXML().

If you have any ideas for making DataMgr more useful, let me know and I would be glad to consider it.

Feel free to download DataMgr and try it out. It is free and open-source. You can also find documentation and links to other blog entries about using DataMgr.

DataMgr offers an easy way to save data describing many-to-many relationships. This normally tedious process is made easy with this free component.

Normally when you save relationship data, you have to add new records in a relationship table and remove records in the same table for relationships that no longer exist. In many cases, you don't want to delete and re-add records because of extra data that might also be stored in that table. Often this can involve writing tedious code with a lot of looping.

Fortunately, DataMgr can take care of all of that for you.

Let's see an example.

Suppose I have an "Admins" table (holding administrators for our site), a "Permissions" table (holding possible permissions for administrators) and an "Admins2Permissions" table (indicating which administrators have which permissions).

Let's say that "Admins2Permissions" has two fields "AdminID" and "PermissionID" referencing the primary key fields of the Admins table and the Permissions table, respectively.

Let's also say that we have a variable called "permissionslist", which holds a list of permissions for an admin (specifically, it holds a list of the primary key values from the Permissions table). This could be passed in from a checkbox field, for example. The primary key value for the admin is stored in a variable called "admin".

To save the relationship, we would use this code (assuming that you have the DataMgr component stored in variables.DataMgr):
<cfset variables.DataMgr.saveRelationList("Admins2Permissions","AdminID",admin,"PermissionID",permissionslist)>

Using that code, DataMgr will remove any records for that admin that are not included in the list and add any records that are in the list and not in the Admins2Permissions table for that admin.

The saveRelationList method takes five (5) arguments:

1. tablename: The name of the table holding the relationships.
2. keyfield: The name of the field holding the key value for the relationships.
3. keyvalue: The value of out primary key.
4. multifield: The field holding the "many" side of this relationship.
5. multilist: The list of values to be stored for the multifield as associated with the keyvalue in this table.

In order to instantiate DataMgr for use as in the example above, you need only initialize it with the datasouce. For example, if you wanted to initialize it to use a datasource of "MyDSN" (pointing to a MS SQL database), you would do this:
<cfset variables.DataMgr = CreateObject("component","com.sebtools.DataMgr_MSSQL").init("MyDSN")>

For a MySQL database, you would use this:
<cfset variables.DataMgr = CreateObject("component","com.sebtools.DataMgr_MySQL").init("MyDSN")>

You can also use Access (DataMgr_Access) or PostGreSQL (DataMgr_PostGreSQL). It should be easy to add support for other databases as well.

You can download DataMgr for free.


Version Note:
As of today (March 7, 2006), DataMgr is version 1.0.1. Unfortunately, support for MS Access prior to ColdFusion MX 7 still hasn't been fixed. I have, however, adjusted the error message to accurately reflect that. All errors thrown by DataMgr are also now of type "DataMgr". Additionally, a few small bug fixes have been made.

The DataMgr component for simplifying basic database interactions has now reached version 1.0. The component has been in use on production sites for about a year.

Recent changes include a handful of bug-fixes and small improvements (most of which are courtesy of Kenton Gray). One small enhancement is the ability to add "seed data" to a table upon its creation when using loadXML (for more information see the DataMgr documentation and the XML Schema for loadXML).

DataMgr currently supports MS Access, MySQL, PostGreSQL and SQL Server. It is easy to add isupport for other databases as well. I hope to add support for Oracle at some point in the future. I would be willing to help add support for other databases as well.

You can find documentation (including links to other blog entries) and the download for the DataMgr component on the ColdFusion Components page of the Bryant Web Consulting web site.

If you use MS SQL Server, you have seen this error. It is annoying enough that you are getting an error, but this is one that doesn't tell you which field is causing the problem - leaving you to guess for yourself.

The first and most obvious solution is the use of the maxlength attribute of <cfqueryparam>. This is helpful in as much as the error that you get comes from the cfqueryparam tag instead of from the database - making it easier to determine which field has the problem.

The simplest way to prevent the error is to wrap a Left() function around the incoming data for that field, to truncate it to the length of the allowable data.

The main problem with these solutions is that you have to specify the length and if you decide to change the length of the field in the database, you have to remember to change your code as well.

Another option is to use my free DataMgr component for your inserts and updates. The DataMgr will actually tell you what field the error is on without you needing to specify the field length.

A newly added feature to DataMgr will help prevent the error altogether. The new truncate() method will return a structure of data truncated to the allowable length of each given field. This will allow you to insert or update a record using that data knowing that you won't get an error for data that is too long.

To use truncate() simply pass in the name of the table and the structure that you want to truncate and you will get back that same structure, truncated to the length of the fields matching the keys of the structure (this is the same sort of structure used for the insertRecord and updateRecord methods of DataMgr).

So, if you wanted to insert a record to a table named "mytable" using a structure named "mydata" and you wanted to circumvent an errors for text that is too long, you could do this:
<cfset DataMgr.insertRecord('mytable',DataMgr.truncate('mytable',mydata))>

Regardless of what solution you use on the server-side, the maxlength attribute of the input field will help on the client-side. This will prevent the user from entering more text into the text field than your database field can handle. This doesn't obviate the need for a solution on the server-side, but does make the applicaion more user-friendly.

Again, you must change this number if you change the length of your field in the database. I don't have a solution for this (yet), but I hope to add this capability to my free sebForm custom tags soon (well, eventually).

Whether or not you decide to use the DataMgr component, this will hopefully help you handle this common database error.