Coldfusion cfquery: running mySql user-defined functions

CF10, mySql 5

I was creating a very complex sql query for my app with lots of conditional code and realised it would be simpler to have some mySql functions to abstract some of the complexity. This was new territory for me.

One immediate gotcha was with sqlYog: If you are testing your function in sqlYog and get a 1305 error: FUNCTION not found, make sure you are clicking the button to Execute ALL queries, not just your one test SELECT query. Doh!

Related to this, if you combine all the statements below into a single cfquery statement your CF server must be configured to run multiple queries. Since that is not a great idea from a security point of view the solution is to run each one separately as shown below.

This is what I eventually found to work (a highly simplified example to demonstrate the syntax):

<cfquery datasource="#request.dsn#" >

DROP FUNCTION IF EXISTS my_func;

</cfquery>

Define the function:


<cfquery datasource="#request.dsn#">
    CREATE FUNCTION my_func(str1 VARCHAR(30), str2 VARCHAR(30))
       RETURNS VARCHAR(100)
    BEGIN
       DECLARE res VARCHAR(100);
       SET res = CONCAT(str1, ' ', str2);
       RETURN res;
    END
</cfquery>

Test just the function:

<cfquery datasource="#request.dsn#" NAME="recordset1">

SELECT my_func('abc1','def2') as myString;

</cfquery>
<cfdump var="#recordset1#">
<!--- myString = "abc1 def2" --->

Use in an SQL SELECT:

<cfquery datasource="#request.dsn#" NAME="recordset2">

SELECT 
members.memberid, 
my_func(members.firstname, members.lastname) as myFullName 
FROM members;

</cfquery>
<cfdump var="#recordset2#">
<!--- Outputs myFullName like "Murray Hopkins"  --->

Same thing using CFSCRIPT:

<cfscript>	
	
	sql = "DROP FUNCTION IF EXISTS my_func;";
	qry = new Query( sql = sql, dsn=request.dsn );	
	
	sql = "CREATE FUNCTION my_func(str1 VARCHAR(30), str2 VARCHAR(30)) RETURNS VARCHAR(100) BEGIN DECLARE res VARCHAR(100); SET res = CONCAT(str1, ' ', str2); RETURN res; END";
	qry = new Query( sql = sql, dsn=request.dsn );	
	
	sql = "SELECT my_func('abc','def') as myString;";
	qry = new Query( sql = sql, dsn=request.dsn );	
	qryObj = qry.execute();		
    writedump(qryObj.getresult());

	sql = "SELECT members.userid, my_func(members.firstname, members.lastname) as myFullName FROM members;";
	qry = new Query( sql = sql, dsn=request.dsn );
	qryObj = qry.execute();		
    writedump(qryObj.getresult());	

</cfscript>

I hope this saves someone some time.

Go well,
Murray

Advertisements

Signing the OSX version of your NWJS (node-webkit) app for GateKeeper (for Mac noobs like me!)

The problem: I am new to Mac. I want my nwjs (node-webkit) app to be able to be downloaded from my website as a DMG file so users can install it. I am not intending to put this app in the Apple Store. I am using Yosemite.

Rant paragraph: The first thing you need to know is that Apple run a kind of racket in this case. In order to do this simple task you need to pay them AU $149 (per annum) just to get a Developer ID so you can sign your app so users can install it without seeing the “unidentified developer” message! The purported reason is that it increases security on the end user’s mac. Well, all the user needs to do is to Control+click the app then click Open. So much for the security! To charge $149 for that is a racket IMJ. Anyway, rant aside…

Gatekeeper info is here if you dont know about it.

The overall steps are:

  1. Get a Developer ID (and certificate and install it on your development Mac).
  2. Use that certificate to sign your app
  3. Package the signed app into a DMG file for distribution

This post also has some useful background info, as does this post. – especially the comment about making sure you have downloaded XCode (from Apple Developer area)

However, this post  had the most helpful checklist for doing steps 1 and 2 above. Thanks Jean-Baptiste Escoyez!
Since it was written a couple of things have changed which is what prompted me to write this blog post.

  • Part A:
  • Keychain is an app in Applications/Utilities
  • If you are taking responsibility for the whole process, ignore the step “Send the newly created file to your agent”
  • Part B:
  • “Upload the CSR file sent by admin (or dev)” means navigate to where you saved the certificate you created in Part A
  • Ignore the “Send it to the requetser” step. You are the requester.
  • Part C:
  • The “User ID” mentioned is the Developer ID which is a long hash. I couldnt find where the ID was mentioned within the Keychain item (probably missed the obvious) but you can get the ID by opening a Terminal window on your Mac and entering security find-identity in a Terminal session. If all is well you should see the Developer ID displayed.
  • Part D:
  • The Terminal commands neeed to change slightly because node-webkit is now called nwjs (since v0.12). If you are using that version or later, change the …./Frameworks/node-webkit references to …./Frameworks/nwjs
  • Of course you can put all that in a “command” script file so you dont need to keep typing it.
  • Part E:
  • I added those commands to my script file

Gotchas found:

  • You need to have internet access in order to run the codesign command. It seems it accesses a Timestamp server when it runs. My internet access had gone down and I got a codesign message about not finding a Timestamp.
  • Dont leave random files in the package or the code signing might fail with a “code object is not siged at all” message. I had made a copy of the info.plist file as info.plistCopy while I was trying to work all this out and codesign didnt like it!

To package the app in a DMG file I am using DropDMG. It seems you can make DMGs manually, and there might be other solutions, but I found this worked really well. At $24 I found the time saving worth it.

There are a few issues around changing the nwjs icon to your custom icon and getting DropDMG to use it in the installer which I will write about in another post soon. Ask if you need it.

Creating a command script for OSX (for Mac noobs like me)

In Windows you have batch files.

In OSX you can create “command” files. Open a text editor eg Sublime Text. (I noticed that TextEdit wouldnt allow me to save the file with a .command extention)

Enter your terminal (command line) commands into your file and save it with a .command extention.

To allow OSX to run that file via a double click you need to change the permissions. Open a Terminal window and enter

sudo chmod 755 your_command_file_path_and_name

You will be asked for your OSX password. Note that as you type it does NOT give any feedback about the key presses so just enter the password anyway.

You should be able to then navigate to your command file in the Finder and double click it to execute the script.

Outlook 2007 – take care when switching from Exchange Server!

Our company recently switched it’s email server from Exchange Server. Not thinking, I deleted the Exchange item: Tools / Account Settings / Email / Remove. Not a good move!

Normal Outlook data files are PST files (e.g. outlook.pst). Exchange Server files are .OST files. Microsoft intentionally do not provide a way to convert an OST file to a PST file! Oh shit! There are commercial solutions and other solutions that may or may not work to convert the file.

After the panic subsided, I realised that I still had the company’s Exchange Server setup on my old laptop’s Outlook. So, I copied the OST file across to that laptop (so I had the emails that were present at the time I stopped using Exchange), fired up Outlook which told me the server was AWOL (which didnt matter), all my email and folders were there! Phew.

I then Archived ALL the folders (File / Archive, etc which copies the data to a PST file – e.g. archive.pst). I then copied the archive.pst file back to my new laptop, started Outlook and voila, all the old email was available.

So, the moral is, if you are switching from Exchange and not migrating your old email to a new mail system, ARCHIVE all your old email BEFORE you disconnect the Exchange data file from Outlook!

 

Solved: Outlook 2007 – email stuck in Outbox after switching from Exchange Server to Gmail IMAP – error 0x80040201

This took a long time to work out. As usual, someone had walked the path before me but it took a while to find that solution amongst all the false leads (in my case).

Our company had recently switched it’s email from Exchange Server to Gmail. (Yes, they are aware of the security issues. Anyway …). Not liking the GMail interface etc I wanted to continue using Outlook for my mail client and using GMail’s IMAP to do that. After setting that up (lots of posts on the net on how to do that), I noticed the following problem.

Important note: See this post BEFORE you delete your old Outlook Exchange file. If you do it the wrong way you will lose all your email!

The problem: When I created a new email to some of my contacts, or replied to an email from one of those same contacts, the email stayed in the Outbox of my default PST file. Sometimes it threw the error 0x80040201. Nothing I did would cause Outlook to send the email. Other email addresses worked fine! Very weird. Eventually I discovered the post below that explained that the problem was due to the Outlook.nk2 file that contains the data for the auto-complete functionality of Outlook. That file remembers whether the address is an Exchange Server address or an SMTP address. And guess what? All the addresses I had problems with had Exchange settings.

So, see the LAST item on this post: http://www.pcreview.co.uk/forums/getting-error-0x80040201-t1872774.html which explains a couple of fixes.

Alternatively, download this: http://www.nirsoft.net/utils/outlook_nk2_edit.html and delete all the email items with an account type of EX (as opposed to SMTP) and follow the instructions. You will then need to type the full email address the next time you send to that person and it will then remember the correct settings. Some addresses had both EX and SMTP entries so deleting the EX entries allowed the SMTP entries to correctly auto-correct.

I hope this saves someone the hours it took for me to find the solution.

NB: there is still a problem with Outlook that appears to be a bug. To get the email to actually send, you must click Send/Receive / Send ALL. I haven’t found a way for it to send automatically but at least I can send email with a couple of extra button clicks!

Now, I wonder how well Thunderbird’s IMAP works?

Cheers,

Murray

Using AlivePDF to print from AIR Javascript via ActionScript3 – part 2

For the background to this post, and how to use the following code, see Part 1

In this section I will describe the “solution” I found to the problem of the lack of adequate printing capability in AIR apps. My solution is to use ActionScript to create a PDF on the fly on the client-side. In this case, I am creating it from Javascript since I develop HTML/Javascript apps (using the EXTJS library). This solution does NOT require EXTJS or any other JS library.

I found two AS libraries that create PDF files client-side – AlivePDF and purePDF. The latter interested me particularly becuase it is a port of iText java library which I was already familiar with. However, as discussed in my forum post here, I was unable to get purePDF to work  so I switched to AlivePDF which was more successful. It is highly likely that my problems with purePDF we of my own making. However I was unable to get any help to work out what, if anything, I was doing wrong.

Now, this is really the start of the solution and very much a work in progress. I would value feedback about the overall approach and architecture I am proposing. Since I really wanted to use purePDF instead of AlivePDF, I elected to make the JS class as a layer so that it would be easier to create a version for purePDF if I ever get it to work!

So, here is the “proof of concept” source code.

First, the ActionScript class that exposes Javascript-friendly methods. Compile this as per Part 1 of this article.

package {

    import flash.display.DisplayObject;
    import flash.display.Sprite;
    import flash.display.StageAlign;
    import flash.display.StageScaleMode;

    import org.alivepdf.pdf.PDF;
    import org.alivepdf.saving.Method;
    import org.alivepdf.fonts.* ;
    import org.alivepdf.pages.Page;
    import org.alivepdf.display.Display;
    import org.alivepdf.layout.* ;
    import org.alivepdf.links.* ;

    import mx.utils.UIDUtil;

    import flash.filesystem.FileStream;
    import flash.filesystem.File;
    import flash.filesystem.FileMode;

    import flash.utils.ByteArray;

    public class alivePDFWrapper extends Sprite {
        private
        var pdf: PDF;
        private
        var file: File;

        public function alivePDFWrapper() {}

        // The translate methods convert the integers passed from the javascript to
        // whatever correct data types AlivePDF uses
        private function translateSize(size: int): Size {
            var s: Size = Size.A4;
            switch (size) {
            case 0:
                s = Size.A3;
                break;
            case 1:
                s = Size.A4;
                break;
            case 2:
                s = Size.A5;
                break;
            case 3:
                s = Size.LEGAL;
                break;
            case 4:
                s = Size.LETTER;
                break;
            case 5:
                s = Size.TABLOID;
                break;
            }
            return s;
        }

        private function translateOrientation(orientation: int): String {
            var o: String = Orientation.PORTRAIT;
            switch (orientation) {
            case 0:
                o = Orientation.PORTRAIT;
                break;
            case 1:
                o = Orientation.LANDSCAPE;
                break;
            }
            return o;
        }

        private function translateUnit(unit: int): String {
            var u: String = Unit.MM;
            switch (unit) {
            case 0:
                u = Unit.MM;
                break;
            case 1:
                u = Unit.CM;
                break;
            case 2:
                u = Unit.INCHES;
                break;
            case 3:
                u = Unit.POINT;
                break;
            }
            return u;
        }

        private function translateDisplayZoom(dzoom: int): String {
            var dz: String = Display.FULL_PAGE;
            switch (dzoom) {
            case 0:
                dz = Display.DEFAULT;
                break;
            case 1:
                dz = Display.FULL_PAGE;
                break;
            case 2:
                dz = Display.FULL_WIDTH;
                break;
            case 3:
                dz = Display.REAL;
                break;
            }
            return dz;
        }

        private function translateDisplayLayout(dlayout: int): String {
            var dl: String = Layout.SINGLE_PAGE;
            switch (dlayout) {
            case 0:
                dl = Layout.SINGLE_PAGE;
                break;
            case 1:
                dl = Layout.ONE_COLUMN;
                break;
            case 2:
                dl = Layout.TWO_COLUMN_LEFT;
                break;
            case 3:
                dl = Layout.TWO_COLUMN_RIGHT;
                break;
            }
            return dl;
        }

        public function newPDF(size: int, orientation: int, unit: int): void {
            var s: Size = translateSize(size);
            var o: String = translateOrientation(orientation);
            var u: String = translateUnit(unit);

            pdf = new PDF(o, u, s);

            //var myCoreFont:IFont = new CoreFont ( FontFamily.HELVETICA_BOLD );
            var myCoreFont: IFont = new CoreFont(FontFamily.HELVETICA);
            pdf.setFont(myCoreFont, 12);

        }

        public function setDisplayMode(displayZoom: int, displayLayout: int): void {
            var dz: String = translateDisplayZoom(displayZoom);
            var dl: String = translateDisplayLayout(displayLayout);

            pdf.setDisplayMode(dz, dl);
        }

        public function newPage(size: int, orientation: int,unit: int): void {
            var s: Size = translateSize(size);
            var o: String = translateOrientation(orientation);
            var u: String = translateUnit(unit);
            var newPage: Page = new Page(o, u, s);
            pdf.addPage(newPage);
        }

        public function addPage(): void {
            pdf.addPage();
        }

        public function writeText(lineHeight: Number, text: String, link: ILink = null): void {
            pdf.writeText(lineHeight, text, link);
        }

        public function saveToFileAIR(fname: String): void {
            // Save to file for AIR
            var fs: FileStream = new FileStream();
            file = new File(fname);
            fs.open(file, FileMode.WRITE);
            var bytes: ByteArray = pdf.save(Method.LOCAL);
            fs.writeBytes(bytes);
            fs.close();
        }

    }
}

The Javascript interface (so far). Save this to /js folder in your AIR app.

function jsPDF() {
    // Define constants
    this.size = {
        A3: 0,
        A4: 1,
        A5: 2,
        LEGAL: 3,
        LETTER: 4,
        TABLOID: 5
    }
    this.unit = {
        MM: 0,
        CM: 1,
        INCHES: 2,
        POINT: 3
    }
    this.orientation = {
        PORTRAIT: 0,
        LANDSCAPE: 1
    }
    this.displayZoom = {
        DEFAULT: 0,
        FULL_PAGE: 1,
        FULL_WIDTH: 2,
        REAL: 3
    }
    this.displayLayout = {
        SINGLE_PAGE: 0,
        ONE_COLUMN: 1,
        TWO_COLUMN_LEFT: 2,
        TWO_COLUMN_RIGHT: 3
    }

    // Load the ActionScript Library (ie alivePDF + wrapper class)
    this.ASlib = new window.runtime.alivePDFWrapper();

    // Create a new PDF instance
    this.newPDF = function (size, orientation, unit) {
        this.ASlib.newPDF(size, orientation, unit);
    }
    // Set the display mode for the PDF when displayed in the reader
    this.setDisplayMode = function (displayZoom, displayLayout) {
        this.ASlib.setDisplayMode(displayZoom, displayLayout);
    }
    // Create a new page and add it to the pdf document
    this.newPage = function (size, orientation, unit) {
        this.ASlib.newPage(size, orientation, unit);
    }

    this.addPage = function () {
        this.ASlib.addPage();
    }

    this.writeText = function (lineHeight, text, link) {
        this.ASlib.writeText(lineHeight, text, link);
    }

    this.saveToFileAIR = function (fname) {
        this.ASlib.saveToFileAIR(fname);
    }
}

The AIR app:

<html>
    
    <head>
        <title>
            Test alivePDF
        </title>
        <script type="text/javascript" src="lib/air/AIRAliases.js"></script>
        <script src="lib/alivePDFWrapper.swf" type="application/x-shockwave-flash"></script>
        <script src="js/jspdf.js"></script>

        <script>

            var mypdf = new jsPDF();

            mypdf.newPDF(mypdf.size.A4, mypdf.orientation.PORTRAIT, mypdf.unit.MM);

            //mypdf.setDisplayMode(mypdf.displayZoom.FULL_PAGE,mypdf.displayLayout.SINGLE_PAGE);
            mypdf.setDisplayMode(mypdf.displayZoom.FULL_PAGE, mypdf.displayLayout.TWO_COLUMN_LEFT);

            mypdf.newPage(mypdf.size.A4, mypdf.orientation.LANDSCAPE, mypdf.unit.MM);

            mypdf.writeText(10, "Just one line on the first page!");

            mypdf.newPage(mypdf.size.A5, mypdf.orientation.PORTRAIT, mypdf.unit.MM);

            mypdf.writeText(10, "Lorem ipsum dolor sit amet, consectetur adipiscing elit.\n\nPraesent vel lectus lorem. Phasellus convallis, tortor a venenatis mattis, erat mi euismod tellus, in fermentum sapien nibh sit amet urna. Class aptent taciti sociosqu ad litora torquent per conubia nostra, per inceptos himenaeos. Praesent tellus libero, lacinia ac egestas eget, interdum quis purus. Donec ut nisl metus, sit amet viverra turpis. Mauris ultrices dapibus lacus non ultrices. Cras elementum luctus mauris, vitae eleifend diam accumsan ut. Aliquam erat volutpat. Suspendisse placerat nibh in libero tincidunt a elementum mi vehicula. Donec lobortis magna vel nibh mollis tempor. Maecenas et elit nunc. Nam non auctor orci. Aliquam vel velit vel mi adipiscing semper in ac orci. Vestibulum commodo sem eget tortor lobortis semper. Ut sit amet sapien non velit rutrum egestas sollicitudin in elit. Fusce laoreet leo a sem mattis iaculis");

            mypdf.saveToFileAIR("C:/CFusionMX7/wwwroot/Sites/TestAS/test2.pdf"); // Set YOUR path here

        </script>
    </head>
    
    <body>
    </body>

</html>

I will continue to add functionality and test the AS and JS classes above. I will update the code above as I do. I am very interested to see if others find this idea has potential.

500 Null errors with POIutility – java.lang.OutOfMemoryError

I use Ben Nadel’s great POIUtility custom tags to create Excel spreadsheets in Coldfusion MX 7. I have two applications that run on a shared server. Occassionally, the spreadsheet generation stops with the dreaded 500 Null error and I can finally shed some light on the problem. I can often rerun the same generation process with the same data and sometimes it works, sometimes not. Frustratingly, I could not get it to fail on my local development machine until today – when I threw a much larger dataset at it and finally it crashed with the 500 Null error.

So, digging into my exception.log file  I found what I had suspected was probably happenning on the shared server but couldnt prove  – java.lang.OutOfMemoryError. My service provider said they couldnt see any errors in the log files which wasnt helping my debug efforts!

The final spreadsheet is 1,491 kb on the drive and is 187 columns x 380 rows = 71,060 cells.

I set up some tests on my local development machine and using the same dataset, adjusted the JVM settings and restarted CF each time to work out which setting was the problem. Here are the results:

JVM Heap size Mb MaxPermSize Mb Worked?
512 64 No
750 64 No
750 512 No
925 64 Yes
925 512 Yes
1024 64 Yes
1024 128 Yes
1024 512 Yes

So, it looks like the heap size is the critical setting.

Using CreateObject("java","java.lang.Runtime").getRuntime() and maxMemory = runtime.maxMemory() it looks like the server setting is probably 512Mb.

Interestingly, I also used the java runtime.freeMemory() in the cell.cfm custom tag (using cflog) whenever a new cell object was created to monitor the memory useage and even when the 500 Null errors occurred the memory always seemed to be at least approx 28 Mb and when the last log entry was written each time there was substantially more than that! I guess I was expecting the freeMemory to fall to something close to zero. My guess is that the garbage collection or some other process must have been grabbing some memory?

Now, I just need to convince my service provider to increase their settings. Or, find another service provider!

I hope this helps others with a similar problem.

Cheers,
Murray