Sancho: Created page with "
{{DISPLAYTITLE:Data Sources}}
{{BreadCrumbL10n}} {{Documentation/BASICGuideTOC |ShowPrev..."

2011-09-05T17:23:29Z

Created page with "<section begin=title /><noinclude>{{DISPLAYTITLE:</noinclude>Data Sources<noinclude>}}</noinclude><section end=title />{{BreadCrumbL10n}} {{Documentation/BASICGuideTOC |ShowPrev..."

New page

<section begin=title /><noinclude>{{DISPLAYTITLE:</noinclude>Data Sources<noinclude>}}</noinclude><section end=title />{{BreadCrumbL10n}}

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

A database is incorporated into {{OOo}} by creating what is commonly referred to as a data source. The user interface provides a corresponding option for creating data sources in the Extras menu. You can also create data sources and work with them using {{OOo}} Basic.

A database context object that is created using the <tt>createUnoService</tt> function serves as the starting point for accessing a data source. This based on the <idl>com.sun.star.sdb.DatabaseContext</idl> service and is the root object for all database operations.

The following example shows how a database context can be created and then used to determine the names of all data sources available. It displays the names in a message box.

<source lang="oobas">
Dim DatabaseContext As Object
Dim Names
Dim I As Integer

DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")

Names = DatabaseContext.getElementNames()

For I = 0 To UBound(Names())
MsgBox Names(I)
Next I
</source>

The individual data sources are based on the <idl>com.sun.star.sdb.DataSource</idl> service and can be determined from the database context using the <tt>getByName</tt> method:

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

DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
DataSource = DatabaseContext.getByName("Customers")
</source>

The example creates a <tt>DataSource</tt> object for a data source called '''Customers'''.

Data sources provide a range of properties, which in turn provide general information about the origin of the data and information about access methods. The properties are:

;<tt>Name (String)</tt>:name of data source
;<tt>URL (String)</tt>:URL of data source in the form of '''jdbc: subprotocol : subname''' or '''sdbc: subprotocol : subname'''
;<tt>Settings (Array)</tt>:array containing <tt>PropertyValue</tt>-pairs with connection parameters (usually at least user name and password)
;<tt>User (String)</tt>:user name
;<tt>Password (String)</tt>:user password (is not saved)
;<tt>IsPasswordRequired (Boolean)</tt>:the password is needed and is interactively requested from user.
;<tt>IsReadOnly (Boolean)</tt>:permits read-only access to the database
;<tt>NumberFormatsSupplier (Object)</tt>:object containing the number formats available for the database (supports the <idl>com.sun.star.util.XNumberFormatsSupplier</idl> interface)
;<tt>TableFilter (Array)</tt>:list of table names to be displayed
;<tt>TableTypeFilter (Array)</tt>:list of table types to be displayed. Values available are <tt>TABLE</tt>, <tt>VIEW</tt> and <tt>SYSTEM TABLE</tt>
;<tt>SuppressVersionColumns (Boolean)</tt>:suppresses the display of columns that are used for version administration

{{Note|The data sources from {{OOo}} are not 1:1 comparable with the data sources in ODBC. Whereas an ODBC data source only covers information about the origin of the data, a data source in {{OOo}} also includes a range of information about how the data is displayed within the database windows of {{OOo}}.}}

== Queries ==

Predefined queries can be assigned to a data source. {{OOo}} notes the SQL commands of queries so that they are available at all times. Queries are used to simplify working with databases because they can be opened with a simple mouse click and also provide users without any knowledge of SQL with the option of issuing SQL commands.

An object which supports the <idl>com.sun.star.sdb.QueryDefinition</idl> service is concealed behind a query. The queries are accessed by means of the <tt>QueryDefinitions</tt> method of the data source.

The following example lists the names of data source queries can be established in a message box.

<source lang="oobas">
Dim DatabaseContext As Object
Dim DataSource As Object
Dim QueryDefinitions As Object
Dim QueryDefinition As Object
Dim I As Integer

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

For I = 0 To QueryDefinitions.Count() - 1
QueryDefinition = QueryDefinitions(I)
MsgBox QueryDefinition.Name
Next I
</source>

In addition to the Name property used in the example, the <idl>com.sun.star.sdb.QueryDefinition</idl> provides a whole range of other properties. These are:

;<tt>Name (String)</tt>:query name
;<tt>Command (String)</tt>:SQL command (typically a <tt>SELECT</tt> command)

The following example shows how a query object can be created in a program-controlled manner and can be assigned to a data source.

<source lang="oobas">
Dim DatabaseContext As Object
Dim DataSource As Object
Dim QueryDefinitions As Object
Dim QueryDefinition As Object
Dim I As Integer

DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
DataSource = DatabaseContext.getByName("Customers")
QueryDefinitions = DataSource.getQueryDefinitions()
QueryDefinition = createUnoService("com.sun.star.sdb.QueryDefinition")
QueryDefinition.Command = "SELECT * FROM Customer"
QueryDefinitions.insertByName("NewQuery", QueryDefinition)
</source>

The query object is first created using the <tt>createUnoService</tt> call, then initialized, and then inserted into the <tt>QueryDefinitions</tt> object by means of <tt>insertByName</tt>.

{{PDL1}}

EN/documentation/basic guide/data sources - Revision history

Sancho: Created page with "{{DISPLAYTITLE:Data Sources}}{{BreadCrumbL10n}} {{Documentation/BASICGuideTOC |ShowPrev..."

Sancho: Created page with "
{{DISPLAYTITLE:Data Sources}}
{{BreadCrumbL10n}} {{Documentation/BASICGuideTOC |ShowPrev..."