DataFaucet ORM

Insert Statement

The insert statement should be reasonably self-explanatory. It inserts data into a table. Contents of the insertData property (a structure) are mapped directly to columns in the database. So for example if the database table has a "productid" column and the insertData structure contains a "productid" structure key, the value associated with that key will be inserted into the productid column. Instead of throwing an error like CFINSERT, structure keys which do not map to columns in the table are ignored.

The Insert object has these properties:

PropertyDefaultDescription
tablen/aThe table from which data is returned
insertDatan/aa structure containing data to insert into the table

Autonumber / Identity Columns

Historically getting the value of the last inserted record from an autonumber or identity column with ColdFusion has been quite a hassle. There have been several different approaches to the problem ranging from the use of CFTRANSACTION and/or CFLOCK to creating a trigger on the tables. One thing that all of these "workaround" solutions had in common is that they were all problematic. The CFLOCK / CFTRANSACTION solution was not scalable and might not return the correct value, the trigger solution might produce errors during multi-record inserts, etc.

ColdFusion 8 made it much easier to get the inserted value from an autonumber or identity column, eliminating the need for all these problematic workarounds. Unfortunately this new solution, while extraordinarily helpful remains platform-dependent! Ugh! The DataFaucet insert statement object removes this platform dependency by providing you with a consistent column name in the structure returned when the statement is executed. The column returned is named df_insertid. Although the structure will also contain the original, platform-dependent key name, it's recommended that you use df_insertid so that if necessary, your application can be switched to an alternate database without needing to modify your code.

Example:

<cfscript>
  // get the datasource
  ds = request.DataFaucet.getDatasource(); 
  // insert the record 
  result = ds.insert("mytable",form); 
  
  // get the id of the newly created record 
  my_id = result.df_insertid 
</cfscript>