Railo Features Part IV

January 8, 2007 · By Gert Franz · 12 Comments

HSQLDB

As soon as you have installed Railo you are able to use the build in database called HSQLDB. Since HSQLDB is a file based Java database we were able to implement it by default in Railo. The only thing you have to do is to add a new datasource of the type HSQLDB.
Note: The underlying database in Railo 1.1 is in fact H2DB. This is just the descendant project of HSQLDB.

Below is shown what options are available when creating a HSQLDB datasource in the Railo Administrator.

Note: In the path definition (as evrywhere in Railo) you can use relative or absolute paths. You can even use the constant paths ({railo-web},{temp-directory}, etc.). I will address these path constants in a later blog.
After creating the datasource (the database does not need to exist yet) you can access the database by creating tables, filling them and querying them accordingly.
Like for example:

<cfquery name="createTable" datasource="myTest">
   CREATE TABLE tblBlogCategories (
      categoryid varchar(35) NULL ,
      categoryname varchar(50) NULL ,
      categoryalias varchar(50) NULL ,
      blog varchar(50) NOT NULL ,
      CONSTRAINT PK_tblBlogCategories PRIMARY KEY
      (
         categoryid
      )
   )
</cfquery> After creating the table you can access it as usual. For instance,
inserting a record:
<cfquery name="insertRecord" datasource="myTest">
   INSERT INTO tblBlogCategories (categoryid, categoryname, categoryalias, blog) VALUES ('4711','My category','mine','myBlog')
</cfquery> and querying the table: <cfquery name="getRecords" datasource="myTest">
   Select * from tblBlogCategories
</cfquery>

<cfdump eval="getRecords">

At the moment you need to add the data manually or use one of the tools available for HSQL. But for small applications that do not require a high performant database server, HSQLDB is a good choice.
More about HSQL db can be read on their homepage.

View count: 391

Tags: Features

12 responses so far ↓

  • 1 Jeff G // Jan 8, 2007 at 2:54 PM

    Is it really worth it to even bother with HSQL when MySQL is free and easy to install?

  • 2 Gert Franz // Jan 8, 2007 at 3:00 PM

    Well HSQL ist not there for competing with MySQL. We use HSQLDB internally to deal with QueryOfQuery. So having a database ready to use just with the installation of Railo is just a side effect worth mentioning.
    Since Railo uses HSQLDB for supporting QoQ, the QoQ Syntax allows anything that HSQLDB allows. It is ANSI92 compatible.
    Besides that, if someone hosts Railo and has no way to access a database for what reason ever, he can use the builtin HSQLDB.

    Oh, and since it is file based HSQLDB is great if you want to distribute an Railo application from a CDROM.

    But I agree that for larger applications, HSQLDB is the wrong choice.

    Gert

  • 3 Jeff G // Jan 8, 2007 at 4:28 PM

    Aha. In normal CF, QoQ was quite limited in the filtering and grouping you could do. Does HSQLDB fix this problem?

  • 4 Gert Franz // Jan 8, 2007 at 4:34 PM

    Well as I posted, we use it for QoQ. We create a valid HSQLDB table out of the resultset of the Railo query and then query it with the HSQLDB api which claims to be ANSI92 compatible. This means advanced filtering and grouping should be possible as well.
    But I allways recommend not to use QoQ if possible. It is almost allways slower than querying a database server. But I'm sure there are cases when QoQ is doing the job better than other sollutions...

  • 5 Alan // Jan 9, 2007 at 10:25 AM

    Do not underestimate the power of QoQ, and do not assume it is a slower mechanism. In the majority of cases it can out perform the database. But you have to understand the proper use-cases for QoQ. Sadly the majority of CFML developers fail to harness this rather powerful directive.

    The BlueDragon implementation is seriously fast

  • 6 Gert Franz // Jan 9, 2007 at 10:56 AM

    Well, yes, I partly agree with you. In my opinion it makes sense, when you do not have a large number of records left. But from what I've seen, and as you mentioned, people use QoQ for just any case just because it is convenient and easy to use.
    But I can hardly agree with your statement that QoQ out performs the database in the majority of cases since:
    - QoQ is not aware of indexes
    - QoQ has limited syntax
    - QoQ is not aware of prepared statements
    - QoQ is not aware of execution plans
    - QoQ consumes lots of memory and processor power
    But, I agree that in some cases it can be easier to use and faster to execute than doing it all on the database server (if even possible). If BD's sollution of QoQ is faster than our implementation, then we'll take a closer look on QoQ performance for the Railo 1.1 release.

    Gert

  • 7 Streit // Jan 9, 2007 at 9:16 PM

    Railo uses HSQLDB for QoQ only as &quot;second choice&quot;. This means that HSQLDB for QoQ is only used for example when more than one table is invoked, for groupings and some other special constellation. But in most cases HSQLDB is not used for QoQ.

  • 8 Ron Stewart // May 8, 2007 at 3:45 PM

    I am playing a bit with the 1.1.005 beta on Mac OS and am bumping into a bit of a problem with the &quot;directory placeholders&quot; and creating an HSQLDB datasource. I can't seem to get any of them to work; regardless of which of the placeholders I try to use, I get an error message in the form &quot;directory [{web-root-directory}/TestDB/] doesnt exists&quot;.

    The only way I can get this to work is to use just the path with no placeholder when I create the datasource (e.g., just &quot;TestDB&quot;), in which case it creates it in the Railo directory (e.g., ~/opt/railo-1.1 on my system).

    Is this a known problem with the directory placeholders in the beta, or perhaps unique to the Mac OS platform?

  • 9 Gert Franz // May 8, 2007 at 10:40 PM

    Well I guess you have to use the relative addressing, since allthough mentiont different (I guess I wrote it wrong) placeholders do not work on the path settings for DBDrivers since the interaction of the JDBC driver with the database is managed by the JVM and not by Railo. In addition to that the configuration is made over CFML in Railo. And in CFML placeholders are not known to Railo.
    Sorry that I confused you.
    You can use something like this:
    /db/TestDB.db
    It is not a problem of Railo in the Beta and no particular problem to Mac OSX (in fact we develop Railo under Mac OSX :-).

    Have fun

    Gert

  • 10 Ron Stewart // May 9, 2007 at 1:56 PM

    Thanks for the response, Gert. It is too bad the placeholders won't work that way on the datasources. That would indeed be handy.

  • 11 Gert Franz // May 9, 2007 at 2:19 PM

    Well we will implement it in one of the next minor releases, but at the moment it's not working that way. With this implementation we will try to make the Railo placeholders available to CFML in Railo as well. So that you could do something like this:
    &lt;cfinclude template=&quot;{railo-web}/...&gt;
    I will post here as soon as this feature is available.

    Gert

    Hope to meet you all at Scotch on the Rocks

  • 12 Streit // May 9, 2007 at 2:56 PM

    the example from gert you can already do, if you make a mapping for one of this placeholder.
    first make a mapping like this:
    physical:{temp-directory}; virtual:/temp

    now you can use the temp directory as follows:
    &lt;cfinclude template=&quot;/temp/myfile.cfm&quot;&gt;
    &lt;cfdirectory source=&quot;/temp/myfile.txt&quot;&gt;

    if you rewrite the hsqldb driver a liitle bit, this also wok there.
    you can find the hsqldb driver here:
    /WEB-INF/railo/context/admin/dbriver/HSQLDB.cfc
    (we also will in future versions)

    in a first step we wil support this placeholders for the BIF &quot;expandPath&quot;, if we will support for all file operations is not shure at the moment.

    greetings michael

Leave a Comment

Leave this field empty: