Wednesday, October 29, 2014

JDBC Commit Failed in ColdFusion / Railo

In one of my Railo application, I found a blocker issue in one module, there I wrote the functionality to add, edit and delete bill items.  The issue I were faced is edit/delete operation is not working as expected.  For figuring out the issue I verified the application as well as exception logs. The log says that JDBC commit failed.

I’m using MySQL 5.6 as the database management system.

After a small research I get to know that, if we set  ‘relaxAutoCommit’ attribute is true in the connection string will help to resolve this issue.

Before I used MySQL as the driver type (in Railo Administrator) to create the data source. To resolve the error I used Other – JDBC Driver as the type instead of MY SQL.

Below is the step wise action that I did to resolve JDBC Commit Failed Issue.
 Step1 : I deleted my existing data source from Railo Administrator .
Step2 : created new data source with old name
Step3: instead of mySql as a type, select Other-JDBC Driver as a type.
Step 4: fill all the necessary fields.
Step 5: Set class as com.mysql.jdbc.Driver
Step 6: Set DSN as jdbc:mysql://127.0.0.1:3306/ebms?autoReconnect=true&characterEncoding=UTF-8&useUnicode=true&allowMultiQueries=true&useOldAliasMetadataBehavior=true&tinyInt1isBit=false&jdbcCompliantTruncation=true&zeroDateTimeBehavior=convertToNull&rewriteBatchedStatements=true&relaxAutoCommit=true
Step 7 : update 

After making this change I didn't see JDBC commit failed error in my application.

Thursday, October 24, 2013

Call Stored Procedure in ColdFusion by using CFQUERY



Yes we can call stored procedures by using <cfquery>  tag. Below code depicts how to call a my SQL stored procedure by using <cfquery>.



<cfquery datasource="ebms" name="k">
CALL ebmsSP_getOptedOutEmployeeList
(
<cfqueryparam value="1" cfsqltype="cf_sql_integer" >,
<cfqueryparam value="1" cfsqltype="cf_sql_integer" >,
<cfqueryparam value="Y" cfsqltype="cf_sql_char">
);
</cfquery>

Sunday, January 27, 2013

AjaxOnLoad is not working



Most of the CF developers face this issue at least one time in their development life cycle. After  made an investigation, we get to know , the issue is because of careless javascript coding . To resolve this issue, we need to place our javascript function within html <head></head> tag and add <cfajaximport> tag on our cfm template.

Example:
Following test.cfm file will not work as we expected, because we are not wrote the ‘init’ javascript function definition with in html head tag.

test.cfm

<html>
                <title>Test Ajax OnLoad function</title>
                <head></head>
               
               
                <body>
                                <!--- Call javascript init(), by using ajaxOnLoad()--->
                                <cfset ajaxOnLoad('init')>
                               
                                <script language="javascript">
                                init = function()
                                {
                                                alert('I got a call through CF ajaxOnLoad function');
                                }
                                </script>
                               
                </body>
               
               
</html>


To resolve this, I’m going to place the javascript function definition within html <head></head> tag and add <cfajaximport> tag . Now try the modified test.cfm file


<html>
                <title>Test Ajax OnLoad function</title>
                <head>
                <script language="javascript">
                                init = function()
                                {
                                                alert('I got a call through CF ajaxOnLoad function');
                                }
                </script>
                </head>
               
                <cfajaximport>
                <body>
                                <!--- Call javascript init(), by using ajaxOnLoad()--->
                                <cfset ajaxOnLoad('init')>
                </body>

</html>

Thursday, January 17, 2013

ColdFusion - Encrypt/Decrypt Query String



It’s a good application development practice, to hide/encrypt Query String from the address bar, which help us to prevent unauthorized use of URL variables. It’s also improving the application security. (eg. Prevent SQL injection through URL tampering).

Here I’m going to implement Query String encryption functionality. For that, I’m using following CF methods.

  1.      encrypt()
  2.     decrypt()
  3.   urlEncodedFormat()
  4.    urlDecode()

Code for encrypt the Query String

<cffunction name="urlEncryptEncode" output="false" access="public" returntype="String" hint="to get the original url variable and values">
       <cfargument name="strQueryString" type="string" required="yes" default="">
      
       <cfscript>
             
              var strEncrQueryString = "";
             
              try
              {
                     /*Checking whether the url string is empty or not*/
                     if(len(arguments.strQueryString))
                     {
                           /*Encrpting & Encoding the the URL variables*/
                           strEncrQueryString = urlEncodedFormat(encrypt(arguments.strQueryString,CLIENT.CFID));
                          
                     }
                    
              }
              catch(any ex)
              {
                     writeDump(ex.detail);
              }
             
              return strEncrQueryString;
             
       </cfscript>
      
</cffunction>


The function urlEncryptEncode will receive query string as the input parameter and return encrypted query string as the result. Below are the steps involved in urlEncryptEncode().

               Step 1   :  Encrypt the input string by using CLIENT.CFID as the key.

                   Note : You need to activate client management in application.cfc/application.cfm

      Step 2   :  Format the encrypted query string into url encoded format, by using   urlEncodedFormatI().

      Step 3   :  Return the result.

Code for decrypt the Query String

<cffunction name="urlDecodeDecrypt" output="true" access="public" returntype="void" hint="to get the original url variable and values">
       <cfargument name="strQueryString" type="string" required="yes" default="">
             
       <cfscript>
       try
       {
              if(len(trim(arguments.strQueryString)))
              {
                     structClear(url);
                     /*decode & decrypt the encoded Query String */
                     local.strDecodedURL = decrypt(urlDecode(arguments.strQueryString),CLIENT.CFID);
                     local.arrayDecodedURL = listToArray(local.strDecodedURL,"&");
                                  for(intIndex = 1;intIndex lte arrayLen(local.arrayDecodedURL);intIndex = intIndex + 1)
                                  {
                                         /*if URL variable has vale*/
                                         if(listLen(local.arrayDecodedURL[intIndex],"=") eq 2)
                                         {
                                                structInsert(URL,listGetAt(local.arrayDecodedURL[intIndex],1,"="),listGetAt(local.arrayDecodedURL[intIndex],2,"="),true);
                                         }
                                         else
                                         {
                                                structInsert(URL,listGetAt(local.arrayDecodedURL[intIndex],1,"="),"",true);
                                         }
                                  }
              }
       }  
       catch(any ex)
       {
              writeDump(ex.detail);
       }     
    </cfscript>


The function urlDecodeDecrypt will receive encrypted query string as the input parameter. Instead of return the result, it will directly out put the result to the calling page. urlDecodeDecrypt  () perform the opposite functionality of urlEncryptEncode ().Below are the steps involved in urlDecodeDecrypt ().


Step 1   : Clear the URL structure

Step 2   : Decode the encrypted query string, by using urlDecode()

Step 3   : Decrypt the decoded string by using CLIENT.CFID as the key.

Now we got the original query string, and going to assign it to URL structure

Step 4 : Convert the query string list to an array.

Step 5 : Loop over the array,  treat each array element as a list with ‘=’ as the delimiter .

Step 6 : Insert the array elements to URL structure as a key value pair.