Easier Inserts

When I do a database inserts, a few issues come up repeatedly.

I need to...

• get the primary key of the inserted record
• make sure I only insert data for fields that I have
• only use data of the correct type for my fields
• check for duplicate entries
  

None of these issues is particularly difficult, but it is tiresome to code for these issues each time.

So, I built some (free) Data Manager components to handle these issues. I have given an overview of these in a previous blog entry.

I recently updated the components to enhance database inserts. They will now return the primary key of the inserted record and check for duplicate records.

To instantiate a Data Manager component, you need to pass in the datasource in the init() method.

<cfset Application.DataMgr = CreateObject("component","com.sebtools.DataMgr_MSSQL").init("mydsn")>

This example uses the DataMgr for MS SQL with a datasource of "mydsn" ("DataMgr_MSSQL" is for MS SQL Server, it could be replaced as needed - with "DataMgr_Access" for MS Access, for example). The Data Manager components must be in com.sebtools. This can be in a com/sebtools directory under your CustomTags directory or under the root of your web site.

To insert a record, call the insertRecord() method. The method has three arguments:

1: tablename (required): Simply the name of the table into which you want to insert data.
2: data (required): a structure of the data that you want to insert, each key representing a field of the same name.
3: OnExists (optional): what to do if a matching record already exists.

The Data Manager component needs to know the structure of the table in order to act on it. For most databases, it will be able to determine the structure on its own. For MS Access, the Data Manager will try its best to determine the structure, but it must guess on the primary key and is unable recognize an autonumber field on its own (the loadXML() method can be used for this).

When you insert a record, DataMgr will do its best to return the primary key (returning an empty string if it is unable to determine the primary key). In practice, this works well, but it does have some limitations.

Data Manager returns the primary key...

• reliably if it was included in the insert
  
• reliably for SQL Server GUID and Identity fields
  
• normally for Acess Autonumber fields, but with some risk of an inaccurate result in very high loads with no locking.
• normally for MySQL Counter fields, but with some risk of an inaccurate result in very high loads with no locking.
• reliably if if all of the filterable fields are unique to that record.

In any other event, it may return an empty string if it fails to determine the primary key.

By "filterable fields", I mean those fields that you can test in the WHERE clause of your query.

Since duplicate records can be problematic, the OnExists argument of insertRecord() has the following possible values to handle duplicate records (where all filterable fields match):

• insert (default): insert the duplicate record (returns the primary key only if passed in or with an incrementing field or a SQL Server GUID).
• error: throw an error stating that the record already exists.
• update: update the existing record (returns the primary key of the updated record)
  
• skip: don't do anything (returns the primary key of the already existing record)

The Data Manager component currently comes in three flavors (each of which needs DataMgr.cfc to work):

• DataMgr_Access.cfc for Microsoft Access
• DataMgr_MSSQL.cfc for Microsoft SQL Server
• DataMgr_MYSQL.cfc for MySQL

I am working on DataMgr_PostGreSQL.cfc for PostGreSQL as well and I would love to get access to Oracle and Informix databases to make versions for those databases.

Feel free to download the Datamgr components and try them out. I would love to get any feedback on them.

Good luck!