Difference between revisions of "Documentation/DevGuide/Database/Getting Data from a Table"
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
- Creating Statements
- Inserting and Updating Data
- Getting Data from a Table
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). |