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:
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
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
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.
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.
This dropdown will hold all available connections you have specified in your script, select the one you want to use.
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.
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.
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
Looking closely we have two variables, one
String name and one
int age, with values
35 respectively. We're using the
Add command and have mapped each variable to the columns in the database named
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
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.
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.
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
Robert (which is what our varialbe
name is set to).
So if mytable had three rows in it that looked like this:
And we ran our example the table would look like:
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.