Working with SQL-J's Java Extensions
Page 5 of 7

VALUES Expression

Before you do the next section, you have to learn a little more about SQL and SQL-J.

As you saw in Chapter 4, "Objects and SQL", SELECT is the SQL-J command that lets you retrieve data from a table. UPDATE and DELETE modify and delete data stored in a table. There is another basic SQL-J command that does not iterate over values in a database; it simply constructs a value or values. This command is VALUES.

Use the VALUES Command

The VALUES clause is used in an INSERT statement.

  1. Execute the following statement in your open ij window:

    INSERT INTO COUNTRIES VALUES ('Bhutan', 'BH', 'Asia');

  2. Check to see if the value is actually in the database:

    SELECT * FROM COUNTRIES WHERE country_ISO_code = 'BH';

  3. Don't make this change in the database permanent, however; issue the ij rollback command:

    rollback;

  4. Check that Bhutan is no longer in the database:

    SELECT * FROM COUNTRIES WHERE country_ISO_code = 'BH';

    In an INSERT statement, the VALUES clause is used to specify which values get inserted.

  5. However, the VALUES command can also stand on its own. Try executing the following statement:

    VALUES ('Bhutan', 'BH', 'Asia');

    It "returns" three String values, just as if you had selected them from some table. This VALUES expression essentially constructs a virtual table of one row and three columns.

The VALUES expression is useful in many cases. One example is that it allows you to execute system-supplied functions that return values.

  1. Try this one:

    -- returns the current date
    VALUES CURRENT_DATE;

  2. Leave ij running and your connection open.