Thursday, December 6, 2007

Javascript Substring() Function Cross-Browser Differences

I found something interesting while working on some Javascript form submission code. The problem was that the form was only submitting in IE and not in FireFox. After some troubleshooting I was able to determine that the Substring function is different between the two browsers. Here is an example if you want to give it a try.


<script language='javascript' type='text/javascript'>

function controlSubmit(obj,act) {
   obj.action = act;
   alert(obj.action.substring(0,13));
   if (obj.action.substring(0,13) == 'action.cfm') obj.submit();
}
</script>
<form>
   <input type="button" value="one" onclick="controlSubmit(this.form,'admin.cfm');">
   <input type="button" value="two" onclick="controlSubmit(this.form,'action.cfm');">
</form>


You will notice that the first button will only display an alert on both browsers and the second button will display an alert on both browsers, but it will also do a form submit only on IE. Moral of the story, be careful when using the Substring() javascript function when aiming for multiple browser support.

Friday, November 9, 2007

Combining Select and Insert into the same CFQuery tag

So you want to grab some values and insert them into a table.
Here is the hard way.

<cfquery name="selectQuery" datasource="#application.dsn.db2#">
    SELECT Setting1,Setting2,Setting3
    FROM defaultSettings
    WHERE ID = 101
</cfquery>

<cfquery name="insertQuery" datasource="#application.dsn.db#">
INSERT INTO Settings
    (TS,
    Name,
    Setting1,
    Setting2,
    Setting3)
VALUES(GetDate(),
    'Test',
    <cfqueryparam cfsqltype="cf_sql_varchar" value="#selectQuery.Setting1#">,
    <cfqueryparam cfsqltype="cf_sql_varchar" value="#selectQuery.Setting2#">,
    <cfqueryparam cfsqltype="cf_sql_varchar" value="#selectQuery.Setting3#">)
</cfquery>


Here is the easier way, combining both Select and Insert in the same CFQuery block.

<cfquery name="insertQuery" datasource="#application.dsn.db#">
INSERT INTO Settings
    (TS,
    Name,
    Setting1,
    Setting2,
    Setting3)
SELECT GetDate(),
    'Test',
    DS.Setting1,
    DS.Setting2,
    DS.Setting3
FROM defaultSettings DS
WHERE DS.ID = 101
</cfquery>

Thursday, October 25, 2007

Using SQL's Scope_Identity

So you want to get the ID(PrimaryKey) of the last record inserted into the database without having to do a second cfquery block. Here is an example of doing it the hard way.

<cfset value1 = 2007>
<cfset myuuid = CreateUUID()>
<cfquery name="insert" datasource="#APPLICATION.DSN.DB#">
    INSERT INTO table(column1,column2)
    VALUES(#VARIABLES.value1#, '#VARIABLES.myUUID#')
</cfquery>

<cfquery name="select" datasource="#APPLICATION.DSN.DB#">
    SELECT ID
    FROM table
    WHERE column2=<cfqueryparam cfsqltype="cf_sql_varchar" value="#VARIABLES.myUUID#"/>
</cfquery>


You would then reference the ID as select.ID.

The easier way.


<cfquery name="insert" datasource="#APPLICATION.DSN.DB#">
    INSERT INTO table(column1,column2)
    VALUES(#VARIABLES.value1#, '#VARIABLES.myUUID#')
    SELECT Scope_Identity() AS ID
</cfquery>


The ID returned would be referenced as insert.ID ...less code, less work for coldfusion, less work for you.

After reading the comment from Ray Camden, I found out that a variable was added to the result attribute of the cfquery tag in ColdFusion 7. This would be the "IDENTITYCOL" variable and here is how you would use it.


<cfquery name="insert" datasource="#APPLICATION.DSN.DB#" result="myResult">
    INSERT INTO table(column1,column2)
    VALUES(#VARIABLES.value1#, '#VARIABLES.myUUID#')
</cfquery>


You would then reference the ID(PrimaryKey) as myResult.IDENTITYCOL(SQL Server Only)
-myResult.GENERATED_KEY for MySQL
-myResult.ROWID for Oracle
-myResult.SYB_IDENTITY for Sybase and
-myResult.SERIAL_COL for Informix

This approach is even faster,but you have to at least be on CFMX 7 to utilize it. Thanks for the reminder Ray!