Category Archives: JavaScript

JavaScript as an Excel scripting language via JSDB

Posted on June 4, 2010 by gobansaor| 5 Comments

A few years back I posted about JavaScript as an Excel scripting language via ExcelDNA. That involved using JavaScript (in the guise of JScript.NET) as an ExcelDNA scripting language. It was purely an academic exercise to prove it could be done, I continued to use C# (or increasingly VB.NET) to build .NET user defined functions. This time however, I’ve managed to embed JavaScript (in the guise of Mozilla Foundation’s SpiderMonkey) directly via a native C interface not to prove I could do it (even though there’s a definite satisfaction in simply doing it) but to use it.

Why add another scripting language to xLite, hasn’t it already got Python?

True, Python is and remains a very powerful add-on to xLite. It’s a mature and long-established language, popular amongst IT professionals and “citizen programmers” alike. But, it’s a bit of a monster and can be awkward to package, particularly on Windows. By using Py2Exe and after a lot of digging on the issue of manifest files I have managed to package and isolate xLite’s Pythonic bits so that it can be used on a PC without first installing the required Python version (I’ve only tested against V2.6, Python’s lack of a side-by-side Windows installation capability is a major pain-in-the-butt   bad news: tested against Python 2.5 & it doesn’t work; good news: side-by-side is possible; simply change the system path to reflect which-ever version you wish to run at the command line; xLite will (must) continue to use V2.6). This “version-hell” mitigates against using Python as a core-element of xLite, fine for those of us who are comfortable with and require the full power of Python, but not as the tool’s primary scripting environment.

No, what I need is:

  • a light (single EXE or DLL preferably) and an approachable popular language,
  • with native SQLite support,
  • runable as standalone executable (on both Windows & Linux),
  • embeddable (is that a word?) in Excel via VBA-friendly DLL.

Add to that essential list some nice-to-haves such as:

  • native COM-interface support (for the likes of ADO etc.),
  • native networking support, for HTTP, raw TCP sockets etc.,
  • native (and easy to use) XML and JSON parsers and emitters,
  • ability to spawn detached/attached command line processes and the ability to stream data to and from such processes; allowing me to easily orchestrate & provide a “grid” of processes (scripted, command line executables, Excel instances) both local and remote (with remote being either traditional servers, http servers or Hadoop Streaming grids).

While looking at the various implementations of Javascipt as a server/shell language, I came across JSDB – JavaScript for Databases. It’s a C++ wrapper around Mozilla’s SpiderMonkey, with lots of useful data related utility classes added. To make JSDB a perfect fit it simply required:

  • a few minor changes to the SQlite class (allow the loading of Virtual Table extensions, add the ability to pass in the address of already open SQLite memory structures);
  • a linker change to use the DLL version of SQLite;
  • plus a VBA-friendly DLL wrapper ( & VBA declares, to call the DLL) to replace the JSDB shell when embedding in Excel.

From my SQLite as the MP3 of Data post:  ”Just as “fractional horsepower” electrical motors revolutionised manufacturing and eventually all our lives (car starter-motors, fridge motors, washing machines etc.), “fractional horsepower” databases can do the same for data. Distributing data to where it is needed.” I can now add a distributed “fractional horsepower” processing engine for that distributed data. This transforms xLite from a micro-ETL platform into one capable of handling (or at least orchestrating) practically any ETL (Extract, Transform & Load),DI (data integration) or “Time Asset” (see this post) process.

→ 5 Comments

Posted in BI, ETL, JavaScript, Python, SQLite, VBA, data, excel, xLite

Time Assets

Posted on May 12, 2010 by gobansaor| 3 Comments


This Stephen Hawkins article on “How to build a time machine” (all that’s needed is a wormhole, the Large Hadron Collider or a rocket that goes really,really fast) is well worth a read.

The concept of time travel was, for most of my life, simply science fiction, but it’s now looking more & more like science fact. Most science-fiction plots involving time-travel tend to involve travelling to the past; this, however, is not part of the emerging time travelling theory, moving forward in time seems the only option.

We may not be able to go back in time but we humans have become adept at “capturing time” and packaging it for reuse later on; our early ancestors spent valuable time crafting  tools and honing skills that they figured would repay any time spent many times over; they were in fact investing in time assets.

Software is perhaps human kind’s greatest time asset generator, similar in concept to the tools and machines we’ve always built, but nearly totally frictionless and with the potential of immense returns on the asset once the initial upfront cost has been met. Yet many are leaving our formal education systems with no idea of the power of software to harness time, to save it, shape it and reuse it again & again. They have not been taught to program.

I’m not suggesting here that every student be forced to study computer science, no, just for them to be introduced to the practical everyday uses of programming (with some formal theory as a foundation) – Applied Computing, if you like. In fact, if hardcore geeks consider the course to be rubbish and refuse to take it, then you know you’re hitting the right note.

At a minimum, everybody should be taught the basics and the possibilities of spreadsheets.  Although using Excel for this purpose would be more “saleable” once students hit the streets and join the work force, I would think that Google Docs Spreadsheets would be a better option as a teaching tool, because:

  • Firstly, it would be cheaper, no licences, minimum hardware requirement (anything with a browser) and the collaboration features of Goggle Docs in general are ideally suited for use in education.
  • Secondly, such training should not be primarily vocational, it should be about learning the possibilities of end-user programming.
  • Excel’s macro language is VBA, a noble language with a long distinguished history, but a language that its owners have abandoned. Google Doc’s scripting language is JavaScript, like VB a language that has often been much maligned, but unlike VB, it’s a language with a future, it’s the magic behind the browser. So students would not only learn the fundamentals of spreadsheets but would through the courses’ scripting modules learn a language that lies at the heart of their everyday computing experience.
  • Google Docs can also be manipulated via a web-based API and can be embedded in web pages. So again students would learn the fundamentals of REST and basic HTML markup, the underlying architecture of the WWW .

Studying such a course, would not only teach a useful life skill (the manipulation of numbers and lists and the automation of such tasks to create time assets) but would also provide an understanding of the building blocks of modern IT.

We need more, and better prepared (dare I say, trained) citizen programmers; there’ll never be enough professional programmers to go around and even if there were, the cost will continue to be prohibitive in many situations (both the financial cost and the time cost of keeping professional programmers aligned with (or even aware of) the business needs of multitudes of organisations).

Just like the right to bear arms was regarded as a necessity in the frontier society of 18th century America, the right (and the basic skills) to program is a necessity on our modern IT frontier. Not everybody will use (or indeed even be capable of using, or allowed to use) that right, but for millions of others, having the power to build time assets for themselves or their businesses will be one of their most prized skills.

→ 3 Comments

Posted in GoogleApps, JavaScript, VBA

Tagged , ,

Zimki – the spirt lives on …

Posted on December 13, 2007 by gobansaor| 2 Comments

Although Zimki is to shut down on Christmas Eve, the ideas behind the service live on. Two new offerings, Horuku and AppJet, offer variations on the idea of hosted application development/deployment.

AppJet, funded by Paul Graham‘s Y-Combinator, is very similar to Zimki, being a server-side JavaScript platform. No details yet as to what sort of paid options will be offered (all accounts are free at the moment). Unlike Zimki there’s no plans to create an open-source version. I like the easy “build a Facebook app” feature; and I guess this is the sort of light-weight applications that they hope to attract.

Although Heroku uses Ruby-on-Rails technology, rather than JavaScript, it is closer to the original Zimki idea; but rather than take the hard (and ultimately unsuccessful in Zimki’s case) road of building an open-source platform from scratch, Heroku takes an already popular open-source project and offers it wrapped in a full on-line development and deployment environment. Again, being in beta, there’s no indication as to what pricing model it will operate under, but I would think that it will attract more “serious” projects than AppJet since anything developed under Heroku is pure Rails which means it can be migrated to any other Rails hosting environment; so no lock-in. The online editor is excellent and whatever about its merits as a hosting service it’s by far the easiest way to learn and explore Ruby and Rails, even easier than this…

If Facebook apps are your goal but you wish to use Ruby rather than AppJet’s JavaScript then not to panic, as being Ruby some bright young spark (no, not me I’m afraid) will already have done a lot of the hard graft for you…

→ 2 Comments

Posted in JavaScript, Ruby, Web2.0, news, programming

Tagged , , , , , ,

JavaScript as an Excel scripting language via ExcelDNA

Posted on October 4, 2007 by gobansaor| 3 Comments

Update June 2010: Also, see JavaScript as an Excel scripting language via JSDB

Developing .NET DLLs that are to be used within an Excel VBA add-in is relatively easy to do. But the overhead of the COM managed interfaces can be a serious performance bottleneck if the .NET managed functions are called from within a tight loop. The alternative is to create a C++ XLL and then to call out to .NET, and that’s exactly what the ExcelDNA add-in does.

ExcelDNA allows you to expose any .NET DLL’s public static methods as Excel UDFs, but not only that, by inserting code into the add-in’s associated .dna file you can ‘script’ a UDF without compiling it into a DLL (via the magic of CodeDomProviders). Both C# and VB.net are supported as ‘scripting’ languages but in fact any .NET language that exposes a CodeDomProvider class can be used. I thought to myself, how cool would it be to use JavaScript as an Excel scripting language!

To use JavaScript (in the guise of JScript.NET) with ExcelDNA you’ll need the AssemblyQualifiedName of the Microsoft.JScript.JScriptCodeProvider type. Set the Project Language=”Microsoft.JScript.JScriptCodeProvider, Microsoft.JScript, Version=1.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a” within the .dna file and that’s it, code away in JavaScript. The code snippet below also includes a C# UDF to check if a Type is a sub-class of CodeDomProvider.


<DnaLibrary Name="My Test AddIn">
<Project Language="Microsoft.JScript.JScriptCodeProvider, Microsoft.JScript, Version=1.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a">
<![CDATA[

import ExcelDna.Integration;
public class myFunctions
{
ExcelFunctionAttribute(Description="Checks if a 1 to 3 digit number", Category="JScript REGEX example", IsMacroType=true)
public static function  ValidateStuff(str)
{
var regex=/^\\d{1,3}$/;
if (regex.test(str))
{
return "VERY GOOD: it's a 1 to 3 digit number";
}
else
{
return "NOT GOOD: Must be a number with between 1 and 3 digits";
}
}
}

]]>
</Project>
<Project Language="CS">
<![CDATA[
using System;
using System.CodeDom.Compiler;
using System.Collections.Generic;
using System.Diagnostics;
using System.Reflection;
using System.Reflection.Emit;
using System.Text;
using System.Xml.Serialization;

using ExcelDna.Integration;

public class Testing123
{
[ExcelFunction(Description="Check if type inherits CodeDomProvider", Category="Useful functions")]
public static string Test11(string str)
{
string str2;
str2="Testing if ...";
try
{
Type t = Type.GetType(str,true,true);

if (t.IsSubclassOf(typeof(CodeDomProvider)))
{
ConstructorInfo ci = t.GetConstructor(new Type[] {} );
CodeDomProvider p = (CodeDomProvider)ci.Invoke(new object[] { });
str2 = p.GetType().AssemblyQualifiedName;
}

}
catch (Exception e)
{
str2=e.ToString();
}
return str2;
}
[ExcelFunction(Description="Joins a string to a number", Category="Useful functions")]
public static string DoJoinThem(string str, double val)
{
return str + val;
}

}

]]>
</Project>
</DnaLibrary>

→ 3 Comments

Posted in JavaScript, VBA, excel

Tagged , , , , , ,

Zimki – R.I.P.

Posted on September 25, 2007 by gobansaor| 1 Comment

Zimki, the London based innovative JavaScript application hosting service is to close down this Christmas Eve. Not surprising really, the announcement last June to stop the proposed open sourcing of the platform and the parent company’s (Canon Europe) decision to order a review on the future direction of Zimki looked ominous at the time. Ah well, at least my experimentation with Zimki has given me a very useful take-away, a real respect for the JavaScript language.

The reasonable notice period gives me plenty of time to find alternative hosting, I’ll more than likely give GAFyD another try; maybe JotSpot wiki will have been added to Google Apps by then allowing me to continue to use a hosted service to handle my online database application needs. If not, I may have to sign up for a bog-standard PHP hosting deal.

→ 1 Comment

Posted in GAFyD, JavaScript

Tagged

Zimki – trust and hope.

Posted on June 16, 2007 by gobansaor| Leave a comment

Fotango‘s Chief Operating Officer, Simon Wardle, writes further about the decision to delay open sourcing Zimki on his personal blog. I’ve decided to look at the situation form a glass-half-full perspective; hey it’s still free, professionally managed, easy to use; I’ve some really useful web services running on it and the rest are static pages that I migrated from Google Apps a few months ago but could easily be moved back to Google or to a run-of-the-mill hosting provider.

OK maybe it’ll disappear in the future but I like the way Simon was up front about the problems, not ‘going to ground’ or worse using marketing-speak to promise vapour ware! So I’m going to trust that if the situation changes again that Fotango will give Zimki users sufficient notice to enable a orderly transition to an alternative platform but hope that they can eventually realise the potential of this innovative product.

→ Leave a comment

Posted in GAFyD, JavaScript

Tagged

Zimki – goes off the boil.

Posted on June 14, 2007 by gobansaor| 1 Comment

Looks like any further enhancement of Zimki is to be put on hold until Fotango‘s parent company, Canon Europe, completes a review on the future direction (and viability?) of the hosted application market. This means the platform will not be open sourced in the near future. This and the lack of any sign of a Zimki user community probably means I’ll abandon the platform; with some regret I might add as the infrastructure is rock solid and professionally managed, and the JavaScript based development environment is both productive and fun to work on. But the feeling that one is backing the wrong horse is hard to shake, particularly with the lack of any sign of developer mind share (i.e. no official forum, unofficial forum never took off and very few, if any, comments on the Zimki blog).

→ 1 Comment

Posted in JavaScript, Web2.0, news

Tagged

Google Gears – SQLite Killer App

Posted on May 31, 2007 by gobansaor| 1 Comment

The announcement of Google Gears is of course a game changer for those working in the development of online apps; its addition to Goggle Reader alone would make it worth while for me and I’m sure we’ll see it integrated into Google Docs and GMail in the near future. If you had any plans to develop a web based app or already deploy one you need to give this technology some quality time.

But it is the use of SQLite as the client-side persistence engine that excites this datasmith’s old heart. Since first coming across SQLite (while learning Ruby on Rails) I’ve been convinced that this “good enough” micro-database on steroids was a winner. Since then, as well as using it with Ruby ,I’ve integrated it with excel to use as a micro-ETL tool, I was instrumental in getting SQLite support added to Kettle (Pentaho PDI), I wrote a SQLite custom module for Proto, in fact the first thing I now do when checking out a data manipulation tool is to check if it supports SQLite.

I’m looking forward to investigating further what Google Gears can add to my existing datasmithing tool set, and I’m certainly glad I invested the time in learning JavaScript. Exciting times.

→ 1 Comment

Posted in ETL, JavaScript, Proto, SQLite, excel, kettle

Talend vs. Kettle (Pentaho PDI)

Posted on May 27, 2007 by gobansaor| 5 Comments

Over the last few weeks I’ve received a lot of traffic from Goggle searches comparing Talend and Kettle and also from Vincent McBurney’s ITtoolbox article comparing the two products, so where do I stand?

As ETL tools they take different approaches, Kettle is a meta data driven framework (which is in turn tightly integrated into an even larger BI framework, the Pentaho BI Project), while Talend is at the end of the day a code generator. The code generator nature of Talend does impart certain capabilities such as the ability to easily integrate into other BI platforms (such as JasperSoft and SpagoBI ) and as micro-ETL tool. On the other hand Kettle’s increasing integration into the Pentaho family makes it natural choice for larger BI implementations. So which one would I recommend ? It’s got to be Kettle and the main reason for that choice is the Pentaho PDI community.

My experience so far with the Talend community has not been good. Admittedly I’ve only asked two questions, one looking for more details on a statement regarding ERP and CRM connectors mentioned in a press release and the other a technical query about a new tSQLite connector. Neither were answered. OK I’m a big boy I can handle the rejection and I’m also technically savvy, I can eventually fix most of my own problems. But I’m sure if I posted similar questions on the Pentaho forums I would have received an answer, probably from Matt Casters if he though the general community would not be able to provide the answer.

So will this experience stop me from using Talend? Maybe not, the code generation capability may come in handy as a micro-ETL tool (but I do have other options); however, through an offline conversation with Samatar (an active member of the Kettle community) and Matt Casters I learned of a new XML input step and of a PDI alternative to the Talend tJavaFlex component, the “Modified Java Script Value” step. This allows for the creation of START, TRANSFORM (i.e. for-each-row) and END JavaScript scripts. As Kettle uses the Rhino scripting engine I have access to any Java API (e.g. Palo,S3, Google Spreadsheets) but with the flexibility of JavaScript; brilliant!

Not only that, since my experience with Talend tJavaFlex reintroduced me to the world of Java I though I might as well look into creating my own Kettle plugin and guess what, it looks easy enough. So I’m going to develop my first plugin to be based on the ScriptValueMod step, replacing the Rhino engine with the Java 6 ScriptEngineManager. This will give me access to the latest version of Rhino which supports E4X (will make handling XML must easier) and also opens the possibility of using JavaFX or indeed JRuby as Kettle scripting languages.

UPDATE:

I’ve received a reply to my tSQLite question!

UPDATE: July 2008

This post is now over a year old, Talend as a product and as a community has improved enormously in the last 12 months; so much so, I now use Talend  (Java) in preference to PDI for most of my datasmithing needs.

→ 5 Comments

Posted in BI, ETL, Java, JavaFX, JavaScript, Palo, Ruby, SQLite, Talend, kettle, xLite

Tagged

JavaFX – a GUI DSL

Posted on May 19, 2007 by gobansaor| 4 Comments

Having mastered JavaScript (OK master is too strong a word – having become comfortable with both its syntax and usage patterns) my next port of call is JavaFX the recently announced Flash/Silverlight competitor. What led me to JavaFX Script was not its role in this Flash/AJAX alternative platform (which unless  Sun improves the JRE download experience is dead in the water they may have a chance with the promised consumer JRE ) but its status as the 2nd scripting language to be supported by the Java 6 ScriptEngineManager – the first being JavaScript. Although the JavaFX platform is still in alpha, the important elements of the scripting language appear to be usable.

Why do I need another scripting language? Well JavaFX Script is a DSL (domain-specific language) and the domain in question is the GUI and as a GUI DSL, JavaFX is impressive.

In my quest for a micro ETL environment the lack of a fast and powerful GUI tool has been a problem. Admittedly, in my Excel/Sqlite xLite suite GUI generation is not a problem as VBA forms are both fast to develop in and feature rich, but the other two options (Java and Ruby) both lack a cost-effective GUI tool (I know there’s Swing and Tk but for somebody who’s used to the speed of development of tools such as VB forms or Oracle’s Application Express, neither appealed). JavaFX changes all that, it’s elegant, powerful, fast to develop in and portable. Further pushes me down the road of using Talend generated Java as my micro-ETL environment of choice.

It’s easy to dismiss JavaFX as hype but the technology behind the hype looks sound. The language borrows powerful features from other languages, object literals from JavaScript, list-comprehensions from Python/Erland/Haskell and first-class functions from JavaScript/Lisp all combined with the full power and glory of Java. The end result:

..is a declarative and statically typed programming language. It has first-class functions, declarative syntax, list-comprehensions, and incremental dependency-based evaluation. The JavaFX Script language makes intensive use of the Java2D swing GUI components and allows for easy creation of GUIs.

Go the community page for FAQs, tutorials and reference information. Timothy O’Brien has a good first pass at calling JavaFX from a Java program and this article by John Smart explains the basics of using ScriptEngineManager to call JavaScript.

→ 4 Comments

Posted in ETL, Java, JavaFX, JavaScript, Ruby, SQLite, Talend, VBA, excel, programming

JavaScript 101

Posted on May 12, 2007 by gobansaor| 2 Comments

In a previous post I explained why I had decided to improve my JavaScript skills. The problem was finding a decent reference book as the ones I had looked at where amongst the worst technical books I had ever encountered; then I found this series of lectures by Douglas Crawford. Not only are the presentations excellent but he also recommends a decent reference book, JavaScript: The Definitive Guide by David Flanagan.

More Douglas Crawford JavaScript/DOM related presentations can be found here.

The feature that impressed me most about JavaScript is its functional programming and closure support, a good introduction to the functional programming capabilities of JavaScript can be found here.

→ 2 Comments

Posted in JavaScript, education, programming

More Google Goodness

Posted on April 18, 2007 by gobansaor| 1 Comment

Another interesting Google announcement, this time an AJAX feed API. The API essentially provides the same functionality as Yahoo Pipes but without the user-friendly UI i.e. the ability to create mashups without the need for server-side proxy code. Currently it supplies RSS and ATOM feeds normalised to an ATOM-like JSON output, but the API also supports full XML and “XML within JSON” access to a feed. More feeds types – I’m guessing (hoping for!) the various GData ATOM extended formats – are promised in the future.

→ 1 Comment

Posted in JavaScript, data, news

Tagged

VBA & JavaScript – glue languages

Posted on March 22, 2007 by gobansaor| 2 Comments

What have Javascript and VBA in common? Not much on the surface and their respective user bases rarely if ever overlap. What they do share are their roles as the imperative (the-if-then-else-loop-etc) programming languages of the “I’m not a programmer” programmers, the great unwashed, the “normal” people out there who are just trying to get something done. VBA adds that “something special” to millions of Excel spreadsheets and MS Access databases. JavaScript does likewise for millions of web pages. Most of the code is copied from existing examples or generated by macro recorders. Often the code is not pretty, not well structured, not adequately tested but it is usually “good enough”.

Both languages can also transcend their “ugly duckling” status. If you want to take VBA development to a new level have a look at Professional Excel Development by Bullen et al. GMail, Google Docs & Spreadsheets and the other AJAX powered applications of Web2.0 show the power of JavaScript. Both languages are now starting to move beyond their original home bases, particularly in the case of JavaScript.

VBA has been used in the past as the scripting language for corporate applications such as Siebel; now in an inspired move, Proto opted to use VBA as the scripting environment in their corporate focused mashup tool.

JavaScript is however showing the real expansionist streak. Along side its traditional client-side role in web page DOM manipulation, Mozilla Firefox extensions, widgets and within Flash applications under its ActionScript implementation, it can now be found providing the imperative flourish to many client and server side declarative tools (e.g. Kettle ETL, OpenLaszlo and Grazr). You can even use JavaScript to provide the complete server-side logic for a web application/service by using Zimki.(deadpool)

My decision to spend time to improve my JavaScript skills was as a result of my experience with Zimki. I initially created a web service to sign requests for uploading files to my Amazon S3 account; allowing me to give clients a small executable (developed in VBA’s big brother VB6) with which to securely upload data to S3 without the need to expose my Amazon account credentials. This turned out to be so simple and elegant with Zimki’s Rhino based JavaScript environment that I decided this language deserved some quality time. I had I guess, like many others, confused the mess that is the browser-wars scared DOM with JavaScript (or more correctly, EMACScript) the language. Having looked further, I’ve discovered an elegant,simple yet powerful language that I’m tempted to think may become the universal glue-language.

UPDATE:

As Tom from Fotango (the Canon Europe company behind Zimki also in deadpool) points out, Zimki uses Mozilla’s C implementation of JavaScript, SpiderMonkey rather than Rhino.

UPDATE: Sep 2007

Cannon Europe are to close the Zimki service on Dec 24th, 2007

UPDATE: June 2010

See how I combined VBA and JavaScript, to make JavaScript an Excel scripting language http://blog.gobansaor.com/2010/06/04/javascript-as-an-excel-scripting-language-via-jsdb/

→ 2 Comments

Posted in JavaScript, Proto, excel, kettle, programming

Tagged ,