Getting Oracle JDBC Driver version info
Bookmark and Share
 

Posted by Rahul in

While debugging one of the application tonight I doubted the issue could be the underlying database driver it was using.  I thought that writing a tiny script to check the version of the Oracle JDBC driver that was used, like the one below would have done the trick

 <cfscript>
 jdbcDriver = CreateObject("java", "oracle.jdbc.OracleDriver");
 writeoutput(jdbcDriver.getMajorVersion() &"."&jdbcDriver.getMinorVersion());
 </cfscript>

But to my disappointment this was not giving the results I expected.

Hence I started  looking for a utility online that could help. I came across a few resources like this and this but all these talked about getting details from the ColdFusion database drivers(macromedia.jdbc.oracle.OracleDriver) which are different than the Oracle database thin drivers (oracle.jdbc.OracleDriver) I was using and wanted info on.

After spending a little more time on figuring this out, I can up wiith this snippet that helped me with what I was looking for.

<cfset oracleDriver=resolveResource()>
<cfif fileExists(oracleDriver)>
Loaded from <cfoutput>#oracleDriver#</cfoutput><br>
<cftry>
<cfzip action="read" file="#oracleDriver#" 
         entrypath="META-INF/MANIFEST.MF" variable="mainfest">
<cfset line = listcontains(mainfest,"Specification-Version","#chr(13)#")>
<cfoutput>#listgetAt(mainfest, line, "#chr(13)#")#</cfoutput>
<cfcatch type="any">
  Unable to find version details
</cfcatch>
</cftry>
<cfelse>
  Oracle Driver not Found !!
</cfif>
<cffunction name="resolveResource" returnType="string">
  <cfargument name="resource" required="Yes" 
        default="oracle/jdbc/OracleDriver.class">
  <cfset var resourceURL = getClass().getClassLoader().getResource(resource)>
  <cfif isDefined("resourceURL")>
    <cfreturn listlast(listfirst(resourceURL,"!"),":")>
  </cfif>
  <cfreturn "">
</cffunction>

The above snippet fetches the version info from the manifest file of the jar where the version info is available for all oracle jdbc drivers. After utilizing <cfzip> earlier to get the list of ColdFusion tags  the <cfzip> helped me with this one too.

I leveraged an existing function resolveResource() that we have to resolve from which jar a java class gets loaded. It is quite helpful in situations where there are conflicting classes (in different libraries or different version of the same lib) in the class path. resolveResource() is used to return the jar file location from where the object is loaded.

Two utility ColdFusion UDF
Bookmark and Share
 

Posted by Rahul in

While working for the ExcelProcessor utility  I required a function to convert the Excel cell reference to the number. ie. for a cell reference A17, I had to refer data in 17 row, 1 column. similarly AC6 would be 6 row, 29 column. To accomplish this I wrote a UDF that took the alphabets representing Excel column and returned number.

Here is the method code, in case you bump across the need to do something similar

<cfscript>
  function convertExcelCRtoNumber(cr) {
  var intVal =0;
  var charVal ="";
  var j=0;
  var t=0;
  if(cr.length() > 2) {  return -1; }
  cr= ucase(cr);
  for(i=cr.length()-1; i >=0;i--)  { 
    charVal = cr.charAt(i);
    t = iif(j >0,  (charVal-64)* (j*26), charVal-64);
    intVal += t;
    j++;
  }
  return val(intVal);
}
</cfscript>

So now <cfoutput>#convertExcelCRtoNumber(‘AZ’)#</cfoutput> would give 52 and <cfoutput>#convertExcelCRtoNumber('IV')#</cfoutput> would give 256. Since Excel has the maximum limit of 256 columns i.e till IV I am limiting the string to 2 chars in length. To be able to return correct number in the series for more than 2 char string like AAB, ZZZ was something I was not able to achieve easily and since the above function met my immediate requirement I didn’t spent much of my grey matter figuring that out. Though that is something I  keep it as a exercise for readers ;-)

Now out of curiosity, I tried to do the reverse of this, i.e a function that will return Excel style column reference given a number and this is what I could manage

<cfscript>
  function convertNumbertoExcelCR(colnum){
    var colname="";  
    var div = colnum \ 26;
    var offset =  colnum mod 26;
   if(colnum > 256){
     return "invalid";
   }  
   if(offset == 0) { offset = 26; --div;}
   colname =chr(64+offset);
   if(div){
    colname=chr(64+div) & colname;
   }
  return colname;
  }
</cfscript>

<cfoutput>#convertNumbertoExcelCR(40)#</cfoutput> would give AN and <cfoutput>#convertNumbertoExcelCR(79)#</cfoutput> would give CA. Again this UDF will limit the number to 256. This method again will not give the expected cell reference if the number goes big enough to correspond to 3 char string ie AAA et al.

I trying posting these 2 UDFs on cflib.org but was unsuccessful due to some error on the site. Hopefully will be able to upload it sometime.

ColdFusion and Excel 2007 Integration
Bookmark and Share
 

Posted by Rahul in ,

There has been a lot of resources available already for ColdFusion and Excel Integration but yet I couldn't find anything to make use for my requirement of processing xlsx files.
This gave me an opportunity to role my own version, using POI which primarily has the following features :

  • Support for xlsx files i.e. it can consume files created with Microsoft Excel 2007
  • Support for reading large files using the POI Event API than the normal user API which is limited by the memory

The creation of Excel documents which is done using HSSF and XSSF APIs for xls and xlsx documents respectively, can still be an issue as the memory requirement of files is so large that it can completely overwhelm the server and whilst this is not really the fault of the API - the BIFF8 file structure demands that the complete file be built in memory before it can be saved to disk.

I used POI 3.5 beta 4 which has support for new Office Open XML file formats, such as XLSX and PPTX introduced with Office 2007.

I am using using the JavaLoader Utility to load the POI 3.5 and also the custom jar used to read Excel document. . Refer this post to find to how to install POI 3.5 beta on ColdFusion 8 using JavaLoader.cfc

This is not a pure ColdFusion solution and wrote a custom library to

  • utilize the POI Event API library for processing
  • make the utility also available for Java program

Importance of using java for the utility is much as ColdFusion natively doesn’t support batch processing and I feel that in case we are using this utlity to consume large data it makes more sense to load the processed data using batch. Though we can leverage JDBC batch processing in ColdFusion we could just do the same directly in Java without involving ColdFusion.

The installation instruction are pretty simple which have been added to the Readme.txt file. All the details about the usage are also in the Readme.txt file.

I would like to thank Ben Nadal whose POIUtility.cfc is the base of almost the complete write Excel functionality which I tried to use as is and adding support for creating Office 2007 (xlsx files).

I am naming this utility as ExcelProcessor and is still in development. Please let me know if anybody is interested in the source code and can contribute in improving the same. I would like to hear feedback, suggestions, comments from people who take the opportunity to try it.

Download ExcelProcessor (zip)

Note : If you receive a error java.lang.ClassNotFoundException: org.apache.xerces.parsers.SAXParser while running ExcelReader just inlcude Xerces parser in the classpath

ColdFusion biggest challenge
Bookmark and Share
 

Posted by Rahul in

Ya I know it has been quite a long that I had written a post. It has been quite a hectic 2009 and hence this is my first post in year 09. I feel its just a blink of an eye and 2 months are trimmed of 2009

Many people would have found the subject quite interesting but since I wanted to be shown alive and no specific topic to write about I just thought of why not interact with community folks and find out

What has been the biggest challenge of all that you had in your as many numbers of years working with ColdFusion (irrespective of version)?

I know this question might have been answered by you a lot many times but why not refresh the memory once more and share them with ever growing community.

The format I would prefer the response would be

  • No. of years with ColdFusion
  • Problem definition
  • How you work around the challenge
  • Lessons learnt (if any)

A vacation in Goa
Bookmark and Share
 

Posted by Rahul in ,

This year my firm decision of heading to Goa paid off finally when I packed up and headed to North Goa. After being back from a much needed vacation I feel refreshed and energized. I hope this last till my next vacation.

My Goa travelogue.

Only interested in some pictures peek in here.

If you need help in planning a trip to Goa I can surely help with my experiences. The Goa wikitravel has a lot of information regarding the same.

Adobe announces CS4
Bookmark and Share
 

Posted by Rahul in ,

Adobe today announced the new CS4 range of products. The new Creative Suite 4 product line advances the creative process across print, Web, mobile, interactive, film and video production. With new levels of integration and expressiveness for Flash technology across the entire product line, Adobe’s biggest software release to date includes Adobe Creative Suite 4 Design Premium, Adobe Creative Suite 4 Web Premium, Adobe Creative Suite 4 Production Premium, Adobe Creative Suite 4 Master Collection, as well as 13 point products, 14 integrated technologies and seven services.

Adobe plans to offer tiered upgrade pricing from previous versions. For a limited time, validly licensed customers of eligible versions of Adobe Creative Suite, Adobe Production Studio and Macromedia Studio will be able to upgrade to Creative Suite 4 for the CS3 upgrade price. For more detailed information about features, upgrade policies, pricing visit CS4 Home

Adobe Creative Suite 4 and its associated products are scheduled to ship in October 2008. You can preorder them on Adobe Store at www.adobe.com/go/gn_store.

Google Chrome : Googles take on browser
Bookmark and Share
 

Posted by Rahul in ,

With a few hours to go before Google unveils the first beta version of its open source browser, Google Chrome. I am getting ready to try my hands on it. As with most of Google launches there is a lot of talk & hype about the success of its new product.

These are a few things you can expect from the browser as per Google

Under the hood, we were able to build the foundation of a browser that runs today's complex web applications much better. By keeping each tab in an isolated "sandbox", we were able to prevent one tab from crashing another and provide improved protection from rogue sites. We improved speed and responsiveness across the board. We also built a more powerful JavaScript engine, V8, to power the next generation of web applications that aren't even possible in today's browsers.

Google chrome promises to be fast, clean, streamlined but yet simple. It uses components from Apple's WebKit and Mozilla's Firefox, among others. We won't know what Google Chrome will look like till tomorrow, when they make the beta available for download, but Techcrunch has a few screenshots of how it might look

Download (PDF) the pre launch comic strip