Aug 31, 2014

Not able to run vbscript using cfexecute on Windows 64 bit Machine - Solution


Goal: To run VBScript using CFexecute using command base script Cscript.exe

Environment:  ColdFusion 10, Windows7 , 64 bit Machine.

My VB Script(Excel.vbs) performs simple office automation .

So first I tried with command line to run Excel.VBS.

C:\Windows\System32 > cscript //nologo D:\Excel.vbs D:\test.xls

and got expected o/p Success (Good my Excel VBS is correct)

Now, I have to see whether CF runs correctly or not.
So I use cfexecute to run Excel.VBS.

<cfexecute name = "C:\Windows\System32\CScript.exe"
            arguments = "//NoLogo D:\Excel.vbs D:\test.xls"
            variable = "savedOutput"
            timeout = "3600">
 </cfexecute>
<cfdump var="#savedOutput#">


But, I got this error message -

Error: 424 Source: Microsoft VBScript runtime error Description: Object required

Then what is wrong with ColdFusion, same VBScript is running using command line but CFExecute throws error.

So I tried two things :
  •  Checked whether account which runs ColdFusion is having admin rights or not?
    • Yes CF running with Admin privilege.
  • Tried using batch file
    batchFile.bat :
    @echo off
    pushd %~dp0
    cscript cscript //nologo D:\Excel.vbs D:\test.xls

    <cfexecute name = "D:\batchFile.bat"
                       variable = "savedOutput"
                       arguments="/C /Q"
                       timeout = "3600">
     </cfexecute>
    <cfdump var="#savedOutput#">


    Same Error: 424 Source: Microsoft VBScript runtime error Description: Object required
So what will be the solution for this ?
After checking some threads on office automation I found this solution.

We have to add the Desktop folder in the systemprofile and system32 folder to open file by Excel if you have Windows Service or IIS on Windows 7 x64 (dev machine) and Server 2008 x64.

So I added Desktop folder under these 2 directories -
C:\Windows\System32\config\systemprofile
C:\Windows\SysWOW64\config\systemprofile


O/P: Now my cfexecute code gives success and VBScript performs desired office operation.

Also, verify that ColdFusion is allowed to "Interact with Desktop" is enabled or not on Services.

Note: If you are doing office automation then you have to add the Desktop folder inside systemProfile irrelevant of technology you are using under above Environments.

Hope it helps you :)


Aug 29, 2014

POI Compatibility Issues with ColdFusion


In this post I will discuss with Issues coming with the latest POI 3.10 in ColdFusion 9/10..

Recently I worked on Import/Export features of Excel documents in my project. Moreover, this excel documents are too much complex with Multiple trigger and rich with Macros.

In result, I faced lots of Issue in reading such files and faced n no of errors with POI.

Let's discuss How ColdFusion reads excel sheets  ?

What is POI ?
- POI is open source java library used to read Microsoft documents.

Why POI in ColdFusion ?
- ColdFusion ships with POI to read Excel sheets. CF also ships with Open Office, by default excel read/manipulation handle by POI. If you want to use Open Office then Configure local/remote Open Office directories under CF Admin. ColdFusion 10 comes with POI 3.6 jar files.

POI jar files present under lib directory of ColdFusion: (Below jar files comes up with POI 3.10)
  1. dom4j-1.6.1.jar
  2. poi.jar
  3. poi-ooxml.jar
  4. poi-ooxml-schemas.jar
  5. poi-scratchpad.jar
  6. stax-api-1.0.1.jar
  7. xmlbeans-2.6.0.jar
Now let's discuss some errors which I have got while reading those XLS/XLSX files using ColdFusion 10. 

Some Errors while reading those XLS/XLSX files with ColdFusion 9/10 using inbuilt tags/function available in ColdFusion :
  • (with XLS) java.lang.NullPointerException at org.apache.poi.hssf.model.LinkTable.getExternalBookAndSheetName  
  • (with XLS)Duplicate PageSettingsBlock record (sid=0x89c) 
  • (with XLS) Unexpected celltype (5)  
    • This error may comes when you have some dirty data or cell value starting with #.
      But everything fine in my case.
  • (with XLS) coldfusion.excel.ExcelProcessException: An exception occurred while using action=read. NullPointerExceptioN.
    • There was nothing wrong in my cfspreadhseet syntax
  • (with XLSX) The supplied POIFSFileSystem does not contain a BIFF8 'Workbook' entry. Is it really an excel file?
    • Some of the XLSX files are failing
  • (with XLSX) The Supplied spreadsheet seems to be encrypted .xlsx file. It must be decrypted before use by HSSF

What I have tried so far with inbuilt Tags/Function available in CF :    Sucess/Failure ?
  • As all issues are pointing towards POI issue. So I download the latest version of POI 3.10 and extracted all jar files and put it under \lib directory.

    Success: 6/5 issues with 100% success rate with  XLS solved using updated POI except encrypted error coming up with some XLSX file.

    Adverse effects (Failures):  
    • Formatting rows/columns/cells of Excel Sheets with styles like fonts and color are not working and throw an error "Java: no Method error with fonts and color".
      • Cause: ColdFusion expecting Font.getCharSet to return a byte, while the latest version of POI returns an int. Stackoverflow thread
    •  Some of the XLSX file which was easily readable using POI 3.6 is now unreadable and through
      "org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorksheet.getColsList()Ljava/util/List
      java.lang.NoSuchMethodError"
In the next post I will discuss some workaround for it.

Aug 15, 2014

Mach-II Framework - Basic beginner start.

 My journey towards learning Mach-II framework

Few days back, I have added post regarding CF frameworks.
In this post we are going to understand what Mach-II is and what this beautiful framework provides.

There is already a superb Github directory from where you can learn Mach-II.
This post will help you to get familiar with some basics of Mach-II and early hurdles you face if you are a beginner.

Below points will cover whatever I've learned so far in Mach-II.

Let's begin: -
What Mach-II  is ?
  • MVC framework for ColdFusion
    • Benifit of MVC : Separation of codes into different layers which help to reuse and organize your code better using the Model(mainly CFCs), View(shows your content) and Controller(which link Model and Views)
    • In Mach-II controller comprises of plugins,listeners and filters
  • Object Oriented F/W
  • Event Driven Framework
  • mach-ii.xml where all configuration needs to be done. 
    • XML contains : Properties, Events, Listeners, Page Views, Filters, Plugins 
  • II stands for Implicit Invocation
    • As Mach-II is an event driven framework, so all events are handled though event handlers which in turn announced a component for which event is registered.
    • Know More here Implicit-Invocation-Architectures in MVC
  • Easy to maintain and provide flexibility in developing robust application.
How to setup Mach-II ?
  •  First download Mach-II f/w code and skeleton from
    MachII Github or Mach-II home
  •  A basic folder structure which I kept up while learning Mach-II



     
  • Once code downloaded,  now we have to run mach-II application based on where you kept our framework files and skeleton files.
    If you are keeping f/w files outside web root,  then we have to add a mapping in CF administrator. You can also extend Mach-II f/w codes to setup your mach-ii f/w environment.
    Here is the link which shows you various ways to setup your mach-ii application
  • Above Image shows my application folder comprises of both f/w codes + skeleton.
    • I pointed this directory as a Virtual Directory in my IIS and named as "mach2test"
    • Now we need to open /config/ mach-II.xml.  In property applicationRoot we have to specify the root of our application.
      <property name="applicationRoot" value="/mach2test" />
    • Now,  once we have added value for applicationRoot, we have to add mapping or inherit f/w codes using extends attribute.
      So for this I have added "MachII.mach-ii" in application.cfc to inherit mach-ii.cfc
       <cfcomponent displayname="learnMach-ii" output="false" extends="MachII.mach-ii">
    • Now code setup is over, you are ready to run your first o/p in the browser.
      http://localhost/mach2test/index.cfm. If you have downloaded Mach-II skeleton then first o/p will be Hello Mach-II!

Flow of Mach-II Application:
  • All Mach-II request flows from index.cfm because it follows pattern called Front Controlerl
    • Wiki: It provides a centralized entry point for handling requests.
  •  As Mach-II is event driven in nature, so everything goes on by calling an event which creates event objects. Let's look for one example to get a clear Idea on Mach-II flow.
  • Say for example: If you have a basic login page in your application
    • URL : http://localhost/mach2test/index.cfm?event=login. So first request goes to central page index.cfm and start looking for Mach-II.xml
    • As we are calling an event called login (If we don't specify any eventParameter then defaultEvent will execute) so It will look for event-handers where an event is login
    • Now event handler told to open a view called login.cfm.
    • In login.cfm - we have one form whose action calls "index.cfm?event=checkLogin"
    • Now, once again flows go to Mach-II.xml and look for checkLogin event. checkLogin event handler calls listener loginListener which looks for validateLogin method present under loginComponent CFC which resides under the model.
    •  validateLogin() return success/failure and announces an event loginSucess and shows home.cfm or  loginFailure which shows error message.
 Hope you get a basic flow of Mach-II framework.
I am still driving deeper into Mach-II framework, hope I will be coming with some more post related to Mach-II soon. 

Happy Learning :)