Difference between revisions of "Documentation/BASIC Guide/Data Sources"

From Apache OpenOffice Wiki
Jump to: navigation, search
m
 
(10 intermediate revisions by 4 users not shown)
Line 7: Line 7:
 
}}
 
}}
 
{{DISPLAYTITLE:Data Sources}}
 
{{DISPLAYTITLE:Data Sources}}
 
+
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 is incorporated into {{AOo}} 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 {{AOo}} 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.
 
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.
Line 14: Line 14:
 
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.
 
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">
+
<syntaxhighlight lang="oobas">
 
Dim DatabaseContext As Object
 
Dim DatabaseContext As Object
 
Dim Names
 
Dim Names
Line 26: Line 26:
 
   MsgBox Names(I)
 
   MsgBox Names(I)
 
Next I
 
Next I
</source>
+
</syntaxhighlight>
  
The individual data sources are based on the <tt>com.sun.star.sdb.DataSource</tt> service and can be determined from the database context using the <tt>getByName</tt> method:
+
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">
+
<syntaxhighlight lang="oobas">
 
Dim DatabaseContext As Object
 
Dim DatabaseContext As Object
 
Dim DataSource As Object
 
Dim DataSource As Object
Line 36: Line 36:
 
DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
 
DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
 
DataSource = DatabaseContext.getByName("Customers")
 
DataSource = DatabaseContext.getByName("Customers")
</source>
+
</syntaxhighlight>
  
 
The example creates a <tt>DataSource</tt> object for a data source called '''Customers'''.
 
The example creates a <tt>DataSource</tt> object for a data source called '''Customers'''.
Line 42: Line 42:
 
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:
 
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>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>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>Settings (Array)</tt>:array containing <tt>PropertyValue</tt>-pairs with connection parameters (usually at least username and password)
;<tt>User (String)</tt>:user name.
+
;<tt>User (String)</tt>:username
;<tt>Password (String)</tt>:user password (is not saved).
+
;<tt>Password (String)</tt>:user password (is not saved)
;<tt>IsPasswordRequired (Boolean)</tt>:the password is needed and is interactively requested from user.
+
;<tt>IsPasswordRequired (Boolean)</tt>:the password is needed and is interactively requested from the user.
;<tt>IsReadOnly (Boolean)</tt>:permits read-only access to the database.
+
;<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 <tt>com.sun.star.util.XNumberFormatsSupplier</tt> interface, refer to [html/p24.html#fapcj Number, Date and Text Format] section).
+
;<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>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>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.
+
;<tt>SuppressVersionColumns (Boolean)</tt>:suppresses the display of columns that are used for version administration
  
{{Documentation/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}}.}}
+
{{Note|The data sources from {{AOo}} 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 {{AOo}} also includes a range of information about how the data is displayed within the database windows of {{AOo}}.}}
  
 
== Queries ==
 
== 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.
+
Predefined queries can be assigned to a data source. {{AOo}} 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 <tt>com.sun.star.sdb.QueryDefinition</tt> service is concealed behind a query. The queries are accessed by means of the <tt>QueryDefinitions</tt> method of the data source.
+
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.
 
The following example lists the names of data source queries can be established in a message box.
  
<source lang="oobas">
+
<syntaxhighlight lang="oobas">
 
Dim DatabaseContext As Object
 
Dim DatabaseContext As Object
 
Dim DataSource As Object
 
Dim DataSource As Object
Line 79: Line 79:
 
   MsgBox QueryDefinition.Name
 
   MsgBox QueryDefinition.Name
 
Next I
 
Next I
</source>
+
</syntaxhighlight>
  
In addition to the Name property used in the example, the <tt>com.sun.star.sdb.QueryDefinition</tt> provides a whole range of other properties. These are:
+
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>Name (String)</tt>:query name
;<tt>Command (String)</tt>:SQL command (typically a <tt>SELECT</tt> command).
+
;<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.
 
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">
+
<syntaxhighlight lang="oobas">
 
Dim DatabaseContext As Object
 
Dim DatabaseContext As Object
 
Dim DataSource As Object
 
Dim DataSource As Object
Line 101: Line 101:
 
QueryDefinition.Command = "SELECT * FROM Customer"
 
QueryDefinition.Command = "SELECT * FROM Customer"
 
QueryDefinitions.insertByName("NewQuery", QueryDefinition)
 
QueryDefinitions.insertByName("NewQuery", QueryDefinition)
</source>
+
</syntaxhighlight>
  
 
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>.
 
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>.
  
 +
{{InterWiki Languages BasicGuide|articletitle=Documentation/BASIC Guide/Data Sources}}
 
{{PDL1}}
 
{{PDL1}}

Latest revision as of 14:06, 30 January 2021


A database is incorporated into Apache OpenOffice 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 Apache OpenOffice Basic.

A database context object that is created using the createUnoService function serves as the starting point for accessing a data source. This based on the com.sun.star.sdb.DatabaseContext 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.

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

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

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

The example creates a DataSource 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:

Name (String)
name of data source
URL (String)
URL of data source in the form of jdbc: subprotocol : subname or sdbc: subprotocol : subname
Settings (Array)
array containing PropertyValue-pairs with connection parameters (usually at least username and password)
User (String)
username
Password (String)
user password (is not saved)
IsPasswordRequired (Boolean)
the password is needed and is interactively requested from the user.
IsReadOnly (Boolean)
permits read-only access to the database
NumberFormatsSupplier (Object)
object containing the number formats available for the database (supports the com.sun.star.util.XNumberFormatsSupplier interface)
TableFilter (Array)
list of table names to be displayed
TableTypeFilter (Array)
list of table types to be displayed. Values available are TABLE, VIEW and SYSTEM TABLE
SuppressVersionColumns (Boolean)
suppresses the display of columns that are used for version administration
Documentation note.png The data sources from Apache OpenOffice 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 Apache OpenOffice also includes a range of information about how the data is displayed within the database windows of Apache OpenOffice.

Queries

Predefined queries can be assigned to a data source. Apache OpenOffice 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 com.sun.star.sdb.QueryDefinition service is concealed behind a query. The queries are accessed by means of the QueryDefinitions method of the data source.

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

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

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

Name (String)
query name
Command (String)
SQL command (typically a SELECT 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.

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)

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


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