Sancho: Основной текст

2011-09-05T17:25:44Z

Основной текст

New page

<section begin=title /><noinclude>{{DISPLAYTITLE:</noinclude>Database Access<noinclude>}}</noinclude><section end=title />{{BreadCrumbL10n}}

{{Documentation/BASICGuideTOC
|ShowPrevNext=block
|ShowPrevPage=block
|PrevPage=EN/documentation/basic_guide/data_sources
|NextPage=EN/documentation/basic_guide/dialogs
|base=block
}}

__NOTOC__

A database connection is needed for access to a database. This is a transfer channel which permits direct communication with the database. Unlike the data sources presented in the previous section, the database connection must therefore be re-established every time the program is restarted.

{{OOo}} provides various ways of establishing database connections. This example shows how to connect to an existing data source.

<source lang="oobas">
Dim DatabaseContext As Object
Dim DataSource As Object
Dim Connection As Object
Dim InteractionHandler as Object

DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
DataSource = DatabaseContext.getByName("Customers")

If Not DataSource.IsPasswordRequired Then
Connection = DataSource.GetConnection("","")
Else
InteractionHandler = createUnoService("com.sun.star.sdb.InteractionHandler")
Connection = DataSource.ConnectWithCompletion(InteractionHandler)
End If
</source>

The code used in the example first checks whether the database is password protected. If not, it creates the database connection required using the <tt>GetConnection</tt> call. The two empty strings in the command line stand for the user name and password.

If the database is password protected, the example creates an <tt>InteractionHandler</tt> and opens the database connection using the <tt>ConnectWithCompletion</tt> method. The InteractionHandler ensures that {{OOo}} asks the user for the required login data.

== Iteration of Tables ==
A table is usually accessed in {{OOo}} through the <tt>ResultSet</tt> object. A <tt>ResultSet</tt> is a type of marker that indicates a current set of data within a volume of results obtained using the <tt>SELECT</tt> command.

This example shows how a <tt>ResultSet</tt> can be used to query values from a database table.

<source lang="oobas">
Dim DatabaseContext As Object
Dim DataSource As Object
Dim Connection As Object
Dim InteractionHandler as Object
Dim Statement As Object
Dim ResultSet As Object

DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
DataSource = DatabaseContext.getByName("Customers")

If Not DataSource.IsPasswordRequired Then
Connection = DataSource.GetConnection("","")
Else
InteractionHandler = createUnoService("com.sun.star.sdb.InteractionHandler")
Connection = DataSource.ConnectWithCompletion(InteractionHandler)
End If

Statement = Connection.createStatement()
ResultSet = Statement.executeQuery("SELECT ""CustomerNumber"" FROM ""Customer""")

If Not IsNull(ResultSet) Then
While ResultSet.next
MsgBox ResultSet.getString(1)
Wend
End If
</source>

Once the database connection has been established, the code used in the example first uses the <tt>Connection.createObject</tt> call to create a <tt>Statement</tt> object. This <tt>Statement</tt> object then uses the <tt>executeQuery</tt> call to return the actual <tt>ResultSet</tt>. The program now checks whether the <tt>ResultSet</tt> actually exists and traverses the data records using a loop. The values required (in the example, those from the <tt>CustomerNumber</tt> field) returns the <tt>ResultSet</tt> using the <tt>getString</tt> method, whereby the parameter 1 determines that the call relates to the values of the first column.

{{VBAnote|The <tt>ResultSet</tt> object from SDBC is comparable with the <tt>Recordset</tt> object from DAO and ADO, since this also provides iterative access to a database.}}

{{SO5note|The database is actually accessed in {{OOo}} through a <tt>ResultSet</tt> object. This reflects the content of a table or the result of a SQL-SELECT command. In the past, the <tt>ResultSet</tt> object provided the resident methods in the <tt>Application</tt> object for navigation within the data, for example, <tt>DataNextRecord</tt> ).}}

== Type-Specific Methods for Retrieving Values ==

As can be seen in the example from the previous section, {{OOo}} provides a <tt>getString</tt> method for accessing table contents. The method provides the result in the form of a string. The following <tt>get</tt> methods are available:

;<tt>getByte()</tt>:supports the SQL data types for numbers, characters and strings
;<tt>getShort()</tt>:supports the SQL data types for numbers, characters and strings
;<tt>getInt()</tt>:supports the SQL data types for numbers, characters and strings
;<tt>getLong()</tt>:supports the SQL data types for numbers, characters and strings
;<tt>getFloat()</tt>:supports the SQL data types for numbers, characters and strings
;<tt>getDouble()</tt>:supports the SQL data types for numbers, characters and strings
;<tt>getBoolean()</tt>:supports the SQL data types for numbers, characters and strings
;<tt>getString()</tt>:supports all SQL data types
;<tt>getBytes()</tt>:supports the SQL data types for binary values
;<tt>getDate()</tt>:supports the SQL data types for numbers, strings, date and time stamp
;<tt>getTime()</tt>:supports the SQL data types for numbers, strings, date and time stamp
;<tt>getTimestamp()</tt>:supports the SQL data types for numbers, strings, date and time stamp
;<tt>getCharacterStream()</tt>:supports the SQL data types for numbers, strings and binary values
;<tt>getUnicodeStream()</tt>:supports the SQL data types for numbers, strings and binary values
;<tt>getBinaryStream()</tt>:binary values
;<tt>getObject()</tt>:supports all SQL data types

In all instances, the number of columns should be listed as a parameter whose values should be queried.

== The ResultSet Variants ==

Accessing databases is often a matter of critical speed. {{OOo}} provides several ways of optimizing <tt>ResultSets</tt> and thereby controlling the speed of access. The more functions a <tt>ResultSet</tt> provides, the more complex its implementation usually is and therefore the slower the functions are.

A simple <tt>ResultSet,</tt> provides the minimum scope of functions available. It only allows iteration to be applied forward, and for values to be interrogated. More extensive navigation options, such as the possibility of modifying values, are therefore not included.

The Statement object used to create the <tt>ResultSet</tt> provides some properties which allow the functions of the <tt>ResultSet</tt> to be influenced:

;<tt>ResultSetConcurrency (const)</tt>:specifications as to whether the data can be modified (specifications in accordance with <idl>com.sun.star.sdbc.ResultSetConcurrency</idl>).
;<tt>ResultSetType (const)</tt>:specifications regarding type of <tt>ResultSets</tt> ( specifications in accordance with <idl>com.sun.star.sdbc.ResultSetType</idl>).

The values defined in <idl>com.sun.star.sdbc.ResultSetConcurrency</idl> are:

;<tt>UPDATABLE</tt>:<tt>ResultSet</tt> permits values to be modified
;<tt>READ_ONLY</tt>:<tt>ResultSet</tt> does not permit modifications

The <idl>com.sun.star.sdbc.ResultSetConcurrency</idl> group of constants provides the following specifications:

;<tt>FORWARD_ONLY</tt>:<tt>ResultSet</tt> only permits forward navigation
;<tt>SCROLL_INSENSITIVE</tt>:<tt>ResultSet</tt> permits any type of navigation, changes to the original data are, however, not noted
;<tt>SCROLL_SENSITIVE</tt>:<tt>ResultSet</tt> permits any type of navigation, changes to the original data impact on the <tt>ResultSet</tt>

{{VBAnote|A <tt>ResultSet</tt> containing the <tt>READ_ONLY</tt> and <tt>SCROLL_INSENSITIVE</tt> properties corresponds to a record set of the <tt>Snapshot</tt> type in ADO and DAO. }}

When using the <tt>ResultSet's</tt> <tt>UPDATEABLE</tt> and <tt>SCROLL_SENSITIVE</tt> properties, the scope of function of a <tt>ResultSet</tt> is comparable with a <tt>Dynaset</tt> type <tt>Recordset</tt> from ADO and DAO.

== Methods for Navigation in ResultSets ==

If a <tt>ResultSet</tt> is a <tt>SCROLL_INSENSITIVE</tt> or <tt>SCROLL_SENSITIVE</tt> type, it supports a whole range of methods for navigation in the stock of data. The central methods are:

;<tt>next()</tt>:navigation to the next data record
;<tt>previous()</tt>:navigation to the previous data record
;<tt>first()</tt>:navigation to the first data record
;<tt>last()</tt>:navigation to the last data record
;<tt>beforeFirst()</tt>:navigation to before the first data record
;<tt>afterLast()</tt>:navigation to after the last data record

All methods return a Boolean parameter which specifies whether the navigation was successful.

To determine the current cursor position, the following test methods are provided and all return a Boolean value:

;<tt>isBeforeFirst()</tt>:<tt>ResultSet</tt> is before the first data record
;<tt>isAfterLast()</tt>:<tt>ResultSet</tt> is after the last data record
;<tt>isFirst()</tt>:<tt>ResultSet</tt> is the first data record
;<tt>isLast()</tt>:<tt>ResultSet</tt> is the last data record

== Modifying Data Records ==

If a <tt>ResultSet</tt> has been created with the <tt>ResultSetConcurrency</tt> = <tt>UPDATEABLE</tt> value, then its content can be edited. This only applies for as long as the SQL command allows the data to be re-written to the database (depends on principle). This is not, for example, possible with complex SQL commands with linked columns or accumulated values.

The <tt>ResultSet</tt> object provides <tt>Update</tt> methods for modifying values, which are structured in the same way as the <tt>get</tt> methods for retrieving values. The <tt>updateString</tt> method, for example, allows a string to be written.

After modification, the values must be transferred into the database using the <tt>updateRow()</tt>method. The call must take place before the next navigation command, otherwise the values will be lost.

If an error is made during the modifications, this can be undone using the <tt>cancelRowUpdates()</tt>method. This call is only available provided that the data has not be re-written into the database using <tt>updateRow()</tt>.

{{PDL1}}

EN/documentation/basic guide/database access - Revision history

Sancho: Основной текст