Difference between revisions of "Documentation/DevGuide/Database/Inserting and Updating Data"

From Apache OpenOffice Wiki
Jump to: navigation, search
m (1 revision(s))
m
Line 14: Line 14:
  
 
The table does not contain any data. Use the following <code>INSERT</code> command to insert data into the table one row at a time:
 
The table does not contain any data. Use the following <code>INSERT</code> command to insert data into the table one row at a time:
 
+
<source lang="sql">
 
   INSERT INTO SALESMAN (
 
   INSERT INTO SALESMAN (
 
     SNR,
 
     SNR,
Line 33: Line 33:
 
     '1946-07-02'
 
     '1946-07-02'
 
     )
 
     )
 
+
</source>
  
 
{{Documentation/Note|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.}}
 
{{Documentation/Note|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 <code>1</code> in the column <code>SNR</code>, '<code>Joseph</code>' in <code>FIRSTNAME</code>, '<code>Smith</code>' in <code>LASTNAME</code>, with other information in the following columns of the table SALESMAN. To issue the command against the database, create a <code>Statement</code> object and then execute it using the method <code>executeUpdate()</code>:
 
The following code sample inserts one row of data with the value <code>1</code> in the column <code>SNR</code>, '<code>Joseph</code>' in <code>FIRSTNAME</code>, '<code>Smith</code>' in <code>LASTNAME</code>, with other information in the following columns of the table SALESMAN. To issue the command against the database, create a <code>Statement</code> object and then execute it using the method <code>executeUpdate()</code>:
 
+
<source lang="java">
 
   XStatement xStatement = xConnection.createStatement();
 
   XStatement xStatement = xConnection.createStatement();
 
    
 
    
Line 44: Line 44:
 
       "SNR, FIRSTNAME, LASTNAME, STREET, STATE, ZIP, BIRTHDATE) " +  
 
       "SNR, FIRSTNAME, LASTNAME, STREET, STATE, ZIP, BIRTHDATE) " +  
 
       "VALUES (1, 'Joseph', 'Smith','Bond Street','CA','95460','1946-07-02')");
 
       "VALUES (1, 'Joseph', 'Smith','Bond Street','CA','95460','1946-07-02')");
 
+
</source>
 
The next call to <code>executeUpdate()</code> inserts more rows into the table <code>SALESMAN</code>. Note the Statement object <code>stmt</code> is reused, rather than creating a new one for each update.
 
The next call to <code>executeUpdate()</code> inserts more rows into the table <code>SALESMAN</code>. Note the Statement object <code>stmt</code> is reused, rather than creating a new one for each update.
 
+
<source lang="java">
 
   xStatement.executeUpdate("INSERT INTO SALESMAN (" +  
 
   xStatement.executeUpdate("INSERT INTO SALESMAN (" +  
 
       "SNR, FIRSTNAME, LASTNAME, STREET, STATE, ZIP, BIRTHDATE) " +  
 
       "SNR, FIRSTNAME, LASTNAME, STREET, STATE, ZIP, BIRTHDATE) " +  
Line 62: Line 62:
 
       "SNR, FIRSTNAME, LASTNAME, STREET, STATE, ZIP, BIRTHDATE) " +  
 
       "SNR, FIRSTNAME, LASTNAME, STREET, STATE, ZIP, BIRTHDATE) " +  
 
       "VALUES (5, 'Bob', 'Meyers', '2 Moon way', 'CA', '95460', '1949-09-07')");
 
       "VALUES (5, 'Bob', 'Meyers', '2 Moon way', 'CA', '95460', '1949-09-07')");
 
+
</source>
 
Updating tables is basically the same process. The SQL command:
 
Updating tables is basically the same process. The SQL command:
 
+
<source lang="sql">
 
   UPDATE SALESMAN
 
   UPDATE SALESMAN
 
   SET STREET='Grant Street', STATE='FL'
 
   SET STREET='Grant Street', STATE='FL'
 
   WHERE SNR=2
 
   WHERE SNR=2
 
+
</source>
 
writes a new street and state entry for Frank Jones who has SNR=2. The corresponding <code>executeUpdate()</code> call looks like this:
 
writes a new street and state entry for Frank Jones who has SNR=2. The corresponding <code>executeUpdate()</code> call looks like this:
 
+
<source lang="java">
 
   int n = xStatement.executeUpdate("UPDATE SALESMAN " +  
 
   int n = xStatement.executeUpdate("UPDATE SALESMAN " +  
 
       "SET STREET='Grant Street', STATE='FL' " +  
 
       "SET STREET='Grant Street', STATE='FL' " +  
 
       "WHERE SNR=2");
 
       "WHERE SNR=2");
 
+
</source>
 
The return value of <code>executeUpdate()</code> 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.  
 
The return value of <code>executeUpdate()</code> 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.  
  

Revision as of 15:15, 31 May 2008



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

SALESMAN Table Design

The illustration above shows the definition of the SALESMAN table in the OpenOffice.org 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'
    )

Template:Documentation/Note

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.

Template:Documentation/Note

Content on this page is licensed under the Public Documentation License (PDL).
Personal tools