Insert, Update, Delete

This is a powerful component helping you to perform common database actions with very little input. The commands you can use are:

  • ADD
  • ADD WHEN NOT EXISTS
  • DELETE
  • UPDATE
  • UPDATE OR ADD

Usage

For this component to work you need to have defined a database connection, either in your code or configured in the web administration.

When you have selected a connection and a table the dialog will list all your script's variables on the left side and all of the table's columns on the right side. You can now drag a variable from the left side to a column on the right side, this will create a mapping which means that the the value of the variable will be added to that column (for command type: Add) or update the column with that variable's value (for command type: Update). If you wish to remove a mapping simply right-click on the column and select Unmap.

Another way of adding a mapping is to select a variable on the left side, select a column name on the right, and the right-click on the column and select Map.

For some operations a key need to be specified, for example in an Update the component need to know what row in the database to update. This is done by creating a normal mapping and then right-clicking on the column and selecting Set key. More than one key can be specified, this means that all keys need to match its value in order for the command to consider the row a match.

Each command type is described below with examples on how to use them.

Return variable

This field specifies the name of the variable used for the JDBCCommand. You will most likely not need to do anything with this variable as the code will be generated for you.

Connection

This dropdown will hold all available connections you have specified in your script, select the one you want to use.

Table

You need to specify what table you want to work with, either by typing its name directly in the field or by clicking the help icon which will query your database and show a list of all available tables. If this help icon does not show any tables you need to verify that the connection you have chosen is valid.

Batch size

This value specifies how many records are sent to the database in one batch. When working with large data sizes a value between 30 and 100 is recommended to increase the performance.

Command type

Add

As the name implies this command will add data to the table. No keys are needed since we're not trying to do things with data already in the table, so all you need to do is to create one or more mappings. When clicking ok you will get something like this:

String name = "Robert";
int age = 35;

JDBCCommand cmd1 = mycon1.commandAdd("mydatabase.mytable", "name,age", 1);
cmd1.addString(name);
cmd1.addInt(age);
cmd1.execute();
cmd1.close();

This will add one row to the table mytable in database mydatabase with the values Robert and 35.

Looking closely we have two variables, one String name and one int age, with values Robert and 35 respectively. We're using the Add command and have mapped each variable to the columns in the database named name and age. Looking at the commandAdd line we can see that the order of the columns corresponds to the order in which the values are being assigned (name first and age second).

Add when not exists

This is a powerful command to use when you want to add something to a table but only if such a row does not already exist. For this to work we need to specify one or several keys.

Using the same scenario as above we get:

String name = "Robert";
int age = 35;

JDBCCommand cmd1 = mycon1.commandAddWhenNotExists("mydatabase.mytable", "name", "age", 1);
cmd1.addKeyString(name);
cmd1.addInt(age);
cmd1.execute();
cmd1.close();

If a row in the table already exists where the column name has a value of Robert nothing is added, but if no such row can be found it will be added to the table. In short, if the key can be found in the table nothing happens, otherwise the mappings are added.

Delete

This command does what it says, it deletes rows from the table. All mappings you make in this mode will be considered keys since we are not updating any value, we simply just need to find any rows that match our criteria. For example:

String name = "Robert";
int age = 35;

JDBCCommand cmd1 = mycon1.commandDelete("mydatabase.mytable", "age", 1);
cmd1.addKeyInt(age);
cmd1.execute();
cmd1.close();

Here we are looking for all rows where the column age has the value 35 (which is what our varialbe age is set to). These rows will be deleted.

Update

If you want to update rows this is the command to use. At least one key need to be mapped in order to find the rows you want to update. For example:

String name = "Robert";
int age = 35;

JDBCCommand cmd1 = mycon1.commandUpdate("mydatabase.mytable", "age", "name", 1);
cmd1.addKeyInt(age);
cmd1.addString(name);
cmd1.execute();
cmd1.close();

Here we're looking for all rows where the column age has the value 35 (which is what our variable age is set to) and update the column name to Robert (which is what our varialbe name is set to).

So if mytable had three rows in it that looked like this:

  • name: Bob, age: 35
  • name: Mike, age: 35
  • name: Per, age: 40

And we ran our example the table would look like:

  • name: Robert, age: 35
  • name: Robert, age: 35
  • name: Per, age: 40

Update or add

In some cases you want to keep a table up to date while at the same time add new data to it. This is most easily done with this command. For example:

String name = "Robert";
int age = 35;

JDBCCommand cmd1 = mycon1.commandAddOrUpdate("mydatabase.mytable", "age", "name", 1);
cmd1.addKeyInt(age);
cmd1.addString(name);
cmd1.execute();
cmd1.close();

In this example we're looking for rows where the column age has the value 35 and updates all those rows so that the column name is set to Robert. If we cannot find any rows that match that criteria the row is simply added to the table.

A common scenario for this command is data synchronization where you need to keep two tables (perhaps in different databases) in sync. You can specify if you need all values to be synced or just a subset.