Category Archives: Databases

H2 Database: Missing UNIX_TIMESTAMP

So maybe you are running tests using an H2 database with your MySQL-based application, and you just got the message that the MySQL function UNIX_TIMESTAMP(value) is missing from the H2 database? No worries. With an H2 database, you can build your own UNIX_TIMESTAMP (or any other function you might need). I’m here going to show you one way to do it:

First, we need to create a class on the classpath of the application that connects to the H2 database, this is normally the application you are testing:

package h2;

import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;

public class H2UserDefined {

    public static Long UNIX_TIMESTAMP(String d) throws ParseException {
        DateFormat dateFormat 
                          = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
        Date dateresult = dateFormat.parse(d);
        return dateresult.getTime()/1000;
    }
}

Now we need to tell H2 that we have a new function to use. To do this we need some SQL to run against the H2 database:

CREATE ALIAS UNIX_TIMESTAMP FOR "h2.H2UserDefined.UNIX_TIMESTAMP";

Here we tell the H2 database that there is a new alias called UNIX_TIMESTAMP that can be used and that it is located in the package ‘h2’ with the path ‘H2UserDefines’ and a function name UNIX_TIMESTAMP. Quite simple 🙂

Tested on Play Framework 2.3.6, H2 v1.4

A small MySQL backup script using mysqldump and creates one file per database

This is the script I use for all my Amazon RDS instances local backups (called from an EC2). It gives me one db backup file for every database in the RDS instance, and at the end it makes sure that I don’t have any files older then 30 days. I put it here now so I don’t loose it again 🙂

#!/bin/bash
 
DATE=$(date +"%Y%m%d_%H%M")
DATABASES=`mysql -hsomedbname-someregion.rds.amazonaws.com -udbadmin -pmypassword -e "show databases;"`
 
for db in $DATABASES; do
        echo $db
        mysqldump --databases $db --single-transaction -hsomedbname-someregion.rds.amazonaws.com -udbadmin -pmypassword | gzip > /opt/backup/mysql.backup.$db.$DATE.sql.gz
done
find /opt/backup/* -mtime +30 -exec rm {} \;

I set this on a two hour cron to make sure that I have fresh backups of my production databases

0 */2 * * * /opt/scripts/mysql_db_backup.sh > /var/log/mysql_db_backup.log

How to access a Sqlite database in C

I’m here going to demonstrate a simple connection and query to a SQLite database using C.

First you need to download and build (if necessary) the SQLite code so you get the sqlite3.so (or sqlite3.lib on Windows) and the necessary header and c files (sqlite3.h, sqlite3.c). I will not go into how this is done here.

After the environment is setup we start with connecting to the SQLite database:

 
#include "sqlite3.h"

int main(int argc, char const *argv[]) {
        
    sqlite3 *conn; // Database struct handle
    const char *dbname = "test.db"; // Database file name
    int dbhandle; // Resource handle

    // Open database
    dbhandle = sqlite3_open_v2(dbname, &conn, SQLITE_OPEN_READONLY, NULL);

    if(dbhandle == SQLITE_OK) {
        fprintf(stdout, "Database open\n");
    }
    else {		
        fprintf(stderr, "Could not open database: %s\n", sqlite3_errmsg(conn) );
        return 1;
   }

   // Cleanup
   sqlite3_close(conn);

   return 0;

}

In short this code will do the following:
1. Create necessary variables for connection, resource handle and the database file name
2. Call sqlite3_open_v2() to open a read only connection to the SQLite database (test.db)
3. Check if we succeeded and report result to console
4. Cleanup connection object

I choose here to use the sqlite3_open_v2() function due to two main reasons:
1. I don’t want to create the database if it does not exist. This is the default behavior of sqlite3_open()
2. I want to be able to open the database as readonly (SQLITE_OPEN_READONLY) or as readwrite (SQLITE_OPEN_READWRITE)

Now we are also going to query the database and present the result. We start with adding a callback function to handle the output like this:

static int callback(void *NotUsed, int argc, char **argv, char **colname){

	NotUsed = 0;
	int i;
	for (i = 0; i < argc; i++){

		printf("%s = %s\n", colname[i], argv[i] ? argv[i] : "NULL");
	}
	printf("\n");
	return 0;
}

This function is going to be called from sqlite3_exec() function later. Once per row of results from query. It will print a list with “column name = cell value” rows

 
#include "sqlite3.h"

int main(int argc, char const *argv[]) {
    
    // Connection variables    
    sqlite3 *conn; // Database struct handle
    const char *dbname = "test.db"; // Database file name
    int dbhandle; 

    // Query variables
    int stmthandle;  // Statement handel
    char *errorMsg = 0; // Error message holder
    const char *query = "SELECT * FROM user";  // Query to run 

    // Open database
    dbhandle = sqlite3_open_v2(dbname, &conn, SQLITE_OPEN_READONLY, NULL);

    if(dbhandle == SQLITE_OK) {
        fprintf(stdout, "Database open\n");
    }
    else {		
        fprintf(stderr, "Could not open database: %s\n", sqlite3_errmsg(conn) );
        return 1;
    }
   
    // Execute query
    stmthandle = sqlite3_exec(conn, query, callback, 0, &errorMsg);

    if (stmthandle == SQLITE_OK) {
        fprintf(stdout, "Query successful\n");
    }
    else {
         fprintf(stderr, "Could not run query: %s\n", errorMsg );
         return 1;
    }

    // Cleanup
    sqlite3_close(conn);
    
    return 0;

}

I have now added the sqlite3_exec() wrapper function (it handles sqlite3_prepare_v2(), sqlite3_step() and sqlite3_finalize() all into one nifty function) and the necessary query variables (statement handle, error message holder and SQL statement).

The sqlite3_exec() function takes the following input:
1. Connection handle to the database
2. SQL query
3. Name of callback function to call for every row of results from database
4. First argument to callback function (the NotUsed argument in the callback function above)
5. Error message holder to return error messages in (if any)

After these arguments have all been satisfied, sqlite3_exec() will call the callback function with the results. Here is an example of output:

Database open
id = 1
login = anders
password = dator123

id = 2
login = niklas
password = dator123

id = 3
login = miew
password = dator123

Query successful

and that is it. We now have a small and simple example of how you connect and query a SQLite database using C.

NOTE: When compiling do not forget to add the -lsqlite3 argument to gcc:

gcc test.c -o test -lsqlite3 -Wall

This has been tested on SQLite 3.7.15.2, gcc 4.2 and OSX 10.7.5