Low Level Database Access: Difference between revisions

If you add tables and columns it is a good practice to create them automatically on server start. The method runSQLScript() in the class JDBCHelper can be used to execute an SQL script. Note: You should use "create table if not exists" in stead of simple "create table" so that you can execute the script on every server start without having to worry about whether the table already exists or not.

Unfortunately there is no "create column if not exists" clause in SQL. So if we have the need to add columns to existing tables, we will need to do the check ourselves.

The following example shows the code that was used to add the new result column to the passwordChange table. Before that column was added, only successful password changes were logged. So the result column should be initialed to 1.

<source lang="java">

if (!transaction.doesColumnExist("passwordChange", "result")) {

transaction.execute("ALTER TABLE passwordChange ADD COLUMN (result TINYINT);", null);

transaction.execute("UPDATE passwordChange SET result=1 WHERE result IS NULL", null);

}

</source>