Embedded H2 Database

If you do not have a database handy, you can easily use the embedded database that ships with OpenBD, H2 (http://h2database.com/). H2 supports more advanced queries than the likes of the QueryOfQueries can offer.

H2 is a fully fledged Java database that can be used either completely in memory, or from disk if you are either memory constraint, or wish the database to persist between restarts. H2 communicates through SQL statements, that you can easily send do using the CFQUERY tag or the QueryRun() function.

You need to first create a datasource that will point to the new database. H2 uses the 'hoststring' to control the features of the database. The details of this can be found here. If the database already exists, then it will be opened. If nothing is there, a new database will be created.

<cfscript>
if ( !DataSourceIsValid("tmpdb") ){
  ds = {
    databasename : "TempDatabase",
    drivername : "org.h2.Driver",
    hoststring : "jdbc:h2:file:c:/data/tmpjournal;MODE=MYSQL"
  };

  DataSourceCreate( "tmpdb", ds );
}

QueryRun( "tmpdb", "CREATE TABLE IF EXISTS table1 (ID INT PRIMARY KEY, NAME VARCHAR(32))" );
QueryRun( "tmpdb", "INSERT INTO table1 (id, name) VALUES (1,'alan')" );
QueryRun( "tmpdb", "INSERT INTO table1 (id, name) VALUES (2,'andy')" );


WriteDump( QueryRun( "tmpdb", "SELECT * FROM table1") );

</cfscript>

If you are using a file based database, then files are created in the directory you specified in the hoststring. Details of what these files are can be read here