Difference between revisions of "Documentation/DevGuide/Database/Getting Data from a Table"

From Apache OpenOffice Wiki
Jump to: navigation, search
m (1 revision(s))
m
Line 8: Line 8:
 
{{DISPLAYTITLE:Getting Data from a Table}}
 
{{DISPLAYTITLE:Getting Data from a Table}}
 
Now that the table <code>SALESMAN</code> has values in it, write a <code>SELECT</code> statement to access those values. The asterisk <code>*</code> in the following SQL statement indicates that all columns should be selected. Since there is no <code>WHERE</code> clause to select less rows, the following SQL statement selects the whole table:
 
Now that the table <code>SALESMAN</code> has values in it, write a <code>SELECT</code> statement to access those values. The asterisk <code>*</code> in the following SQL statement indicates that all columns should be selected. Since there is no <code>WHERE</code> clause to select less rows, the following SQL statement selects the whole table:
−
 
+
<source lang="sql">
 
   SELECT * FROM SALESMAN
 
   SELECT * FROM SALESMAN
−
 
+
</source>
 
The result contains the following data:
 
The result contains the following data:
  
Line 65: Line 65:
  
 
The following is another example of a <code>SELECT</code> statement. This statement gets a list with the names and addresses of all the salespersons. Only the columns <code>FIRSTNAME</code>, <code>LASTNAME</code> and <code>STREET</code> were selected.
 
The following is another example of a <code>SELECT</code> statement. This statement gets a list with the names and addresses of all the salespersons. Only the columns <code>FIRSTNAME</code>, <code>LASTNAME</code> and <code>STREET</code> were selected.
−
 
+
<source lang="sql">
 
   SELECT FIRSTNAME, LASTNAME, STREET FROM SALESMAN
 
   SELECT FIRSTNAME, LASTNAME, STREET FROM SALESMAN
−
 
+
</source>
 
The result of this query only contains three columns:
 
The result of this query only contains three columns:
  
Line 98: Line 98:
  
 
The <code>SELECT</code> statement above extracts all salespersons in the table. The following SQL statement limits the SALESMAN <code>SELECT</code> to salespersons who were born before 01/01/1950:  
 
The <code>SELECT</code> statement above extracts all salespersons in the table. The following SQL statement limits the SALESMAN <code>SELECT</code> to salespersons who were born before 01/01/1950:  
−
 
+
<source lang="sql">
 
   SELECT FIRSTNAME, LASTNAME, BIRTHDATE
 
   SELECT FIRSTNAME, LASTNAME, BIRTHDATE
 
   FROM SALESMAN
 
   FROM SALESMAN
 
   WHERE BIRTHDATE < '1950-01-01'
 
   WHERE BIRTHDATE < '1950-01-01'
−
 
+
</source>
 
The resulting data is:
 
The resulting data is:
  
Line 122: Line 122:
 
When a database is accessed through the {{PRODUCTNAME}} API database integration, the results are retrieved through <code>ResultSet</code> objects. The next section discusses how to use result sets. The following <code>executeQuery()</code> call executes the SQL command above. Note that the Statement is used again:  
 
When a database is accessed through the {{PRODUCTNAME}} API database integration, the results are retrieved through <code>ResultSet</code> objects. The next section discusses how to use result sets. The following <code>executeQuery()</code> call executes the SQL command above. Note that the Statement is used again:  
 
<!--[SOURCE:Database/Sales.java]-->
 
<!--[SOURCE:Database/Sales.java]-->
−
 
+
<source lang="java">
 
   com.sun.star.sdbc.XResultSet xResult = xStatement.executeQuery("SELECT FIRSTNAME, LASTNAME, BIRTHDATE " +  
 
   com.sun.star.sdbc.XResultSet xResult = xStatement.executeQuery("SELECT FIRSTNAME, LASTNAME, BIRTHDATE " +  
 
       "FROM SALESMAN " +  
 
       "FROM SALESMAN " +  
 
       "WHERE BIRTHDATE < '1950-01-01'");
 
       "WHERE BIRTHDATE < '1950-01-01'");
−
 
+
</source>
 
{{PDL1}}
 
{{PDL1}}
 
[[Category: Database Access]]
 
[[Category: Database Access]]

Revision as of 15:18, 31 May 2008



Now that the table SALESMAN has values in it, write a SELECT statement to access those values. The asterisk * in the following SQL statement indicates that all columns should be selected. Since there is no WHERE clause to select less rows, the following SQL statement selects the whole table: 

  SELECT * FROM SALESMAN

The result contains the following data:

SNR FIRSTNAME LASTNAME STREET STATE ZIP BIRTHDATE
1 Joseph Smith Bond Street CA 95460 02/07/46
2 Frank Jones Lake silver CA 95460 12/24/63
3 Jane Esperanza 23 Hollywood drive CA 95460 04/01/72
4 George Flint 12 Washington street CA 95460 02/13/53
5 Bob Meyers 2 Moon way CA 95460 09/07/49

The following is another example of a SELECT statement. This statement gets a list with the names and addresses of all the salespersons. Only the columns FIRSTNAME, LASTNAME and STREET were selected. 

  SELECT FIRSTNAME, LASTNAME, STREET FROM SALESMAN

The result of this query only contains three columns:

FIRSTNAME LASTNAME STREET
Joseph Smith Bond Street
Frank Jones Lake silver
Jane Esperansa 23 Hollywood drive
George Flint 12 Washington street
Bob Meyers 2 Moon way

The SELECT statement above extracts all salespersons in the table. The following SQL statement limits the SALESMAN SELECT to salespersons who were born before 01/01/1950: 

  SELECT FIRSTNAME, LASTNAME, BIRTHDATE
  FROM SALESMAN
  WHERE BIRTHDATE < '1950-01-01'

The resulting data is:

FIRSTNAME LASTNAME BIRTHDATE
Joseph Smith 02/07/46
Bob Meyers 09/07/49

When a database is accessed through the OpenOffice.org API database integration, the results are retrieved through ResultSet objects. The next section discusses how to use result sets. The following executeQuery() call executes the SQL command above. Note that the Statement is used again: 

  com.sun.star.sdbc.XResultSet xResult = xStatement.executeQuery("SELECT FIRSTNAME, LASTNAME, BIRTHDATE " + 
      "FROM SALESMAN " + 
      "WHERE BIRTHDATE < '1950-01-01'");
Content on this page is licensed under the Public Documentation License (PDL).
Retrieved from "https://wiki.openoffice.org/w/index.php?title=Documentation/DevGuide/Database/Getting_Data_from_a_Table&oldid=79180"
Personal tools