Inserting and Updating Data

From Apache OpenOffice Wiki
Jump to: navigation, search



The following examples use a sample Adabas D database. Generate an Adabas D database in the Apache OpenOffice API installation and define a new table named SALESMAN.

SALESMAN Table Design

The illustration above shows the definition of the SALESMAN table in the Apache OpenOffice API data source administrator. The description column shows the lengths defined for the text fields of the table. After all the fields are defined, right-click the row header of the column SNR and choose Primary Key to make SNR the primary key. Afterwards a small key icon in the row header shows that SNR is the primary key of the table SALESMAN. When completed, save the table as SALESMAN. It is important to use uppercase letters for the table name, otherwise the example SQL code will not work.

The table does not contain any data. Use the following INSERT command to insert data into the table one row at a time:

  INSERT INTO SALESMAN (
    SNR,
    FIRSTNAME,
    LASTNAME,
    STREET,
    STATE,
    ZIP,
    BIRTHDATE
    )
  VALUES (
    1, 
    'Joseph', 
    'Smith',
    'Bond Street',
    'CA',
    '95460',
    '1946-07-02'
    )
Documentation note.png Note the single quotes around the values for the text fields. Single quotes denote character strings in SQL, while double quotes are used for case-sensitive identifiers, such as table and column names.

The following code sample inserts one row of data with the value 1 in the column SNR, 'Joseph' in FIRSTNAME, 'Smith' in LASTNAME, with other information in the following columns of the table SALESMAN. To issue the command against the database, create a Statement object and then execute it using the method executeUpdate():

  XStatement xStatement = xConnection.createStatement();
 
  xStatement.executeUpdate("INSERT INTO SALESMAN (" + 
      "SNR, FIRSTNAME, LASTNAME, STREET, STATE, ZIP, BIRTHDATE) " + 
      "VALUES (1, 'Joseph', 'Smith','Bond Street','CA','95460','1946-07-02')");

The next call to executeUpdate() inserts more rows into the table SALESMAN. Note the Statement object stmt is reused, rather than creating a new one for each update.

  xStatement.executeUpdate("INSERT INTO SALESMAN (" + 
      "SNR, FIRSTNAME, LASTNAME, STREET, STATE, ZIP, BIRTHDATE) " + 
      "VALUES (2, 'Frank', 'Jones', 'Lake Silver', 'CA', '95460', '1963-12-24')");
 
  xStatement.executeUpdate("INSERT INTO SALESMAN (" + 
      "SNR, FIRSTNAME, LASTNAME, STREET, STATE, ZIP, BIRTHDATE) " + 
      "VALUES (3, 'Jane', 'Esperanza', '23 Hollywood drive', 'CA', '95460', '1972-01-04')");
 
  xStatement.executeUpdate("INSERT INTO SALESMAN (" + 
      "SNR, FIRSTNAME, LASTNAME, STREET, STATE, ZIP, BIRTHDATE) " + 
      "VALUES (4, 'George', 'Flint', '12 Washington street', 'CA', '95460', '1953-02-13')");
 
  xStatement.executeUpdate("INSERT INTO SALESMAN (" + 
      "SNR, FIRSTNAME, LASTNAME, STREET, STATE, ZIP, BIRTHDATE) " + 
      "VALUES (5, 'Bob', 'Meyers', '2 Moon way', 'CA', '95460', '1949-09-07')");

Updating tables is basically the same process. The SQL command:

  UPDATE SALESMAN
  SET STREET='Grant Street', STATE='FL'
  WHERE SNR=2

writes a new street and state entry for Frank Jones who has SNR=2. The corresponding executeUpdate() call looks like this:

  int n = xStatement.executeUpdate("UPDATE SALESMAN " + 
      "SET STREET='Grant Street', STATE='FL' " + 
      "WHERE SNR=2");

The return value of executeUpdate() is an int that indicates how many rows of a table were updated. Our update command affected one row, so n is equal to 1.

Documentation note.png Note that it depends on the database and the driver, if the return value of executeUpdate() reflects the actual changes.
Content on this page is licensed under the Public Documentation License (PDL).
Personal tools
In other languages