Table Aliases for data sources
Note: The below information
is outdated, it applies only to OpenOffice.org versions prior to 2.0.
The Problem
When working with select statements, OpenOffice.org usually uses an
alias name for tables such as in "SELECT
* FROM <table> aliasname" Here "aliasname"" is a
so-called table alias. However, some
databases do not allow such aliases. The
statement then would be "SELECT *
FROM <table>". Such databases
usually reject statements with aliases.
The Solution
OpenOffice.org features the disabling as well as the enabling of this
behavior.
Table aliases can be enabled on a per-data-source basis.
For this, the "Info" property of a data
source should contain a name-value-pair with
Name: AppendTableAlias
Value: FALSE
Unfortunately, there is no user interface, yet, for doing so. You could
use the Basic macro provided below, until we get OOo 2.0 ui, which adds
the setting for a data
source of your choice.
Note that this feature will be first available in version
OpenOffice.org 2.0.
The Macro
The following macro disables the use of table alias names for a data
source of your choice. You can also download this macro in the downloads section.
REM
***** BASIC *****
Option Explicit
Sub Main
Dim sDataSourceName as String
sDataSourceName
= InputBox( "Please enter the name of the data
source:" )
AppendTableAlias(sDataSourceName )
End Sub
Sub AppendTableAlias( sDataSourceName as String )
' the data
source context (ehm - the service name is historical :)
Dim aContext as Object
aContext
= createUnoService( "com.sun.star.sdb.DatabaseContext"
)
If ( Not aContext.hasByName( sDataSourceName ) ) Then
MsgBox "There is no data source named "
+ sDataSourceName + "!"
Exit Sub
End If
' the data
source
Dim aDataSource as Object
aDataSource
= aContext.getByName( sDataSourceName )
' append
the new AppendTableAlias flag
Dim bFlag as Boolean
bFlag =
FALSE
Dim aInfo as Variant
aInfo =
aDataSource.Info
aInfo =
AddInfo( aInfo, "AppendTableAlias", bFlag )
' and
write back
aDataSource.Info = aInfo
' flush
(not really necessary, but to be on the safe side :)
aDataSource.flush
End Sub
Function AddInfo( aOldInfo() as new com.sun.star.beans.PropertyValue,sSettingsName as String, aSettingsValue as Variant ) as Variant
Dim nLower as Integer
Dim nUpper as Integer
nLower
= LBound( aOldInfo() )
nUpper
= UBound( aOldInfo() )
' look if
the setting is already present
Dim bNeedAdd as Boolean
bNeedAdd
= TRUE
Dim i As Integer
For i = nLower To nUpper
If ( aOldInfo( i ).Name = sSettingsName ) Then
aOldInfo( i ).Value = aSettingsValue
bNeedAdd = FALSE
End If
Next i
' allocate
the new array
Dim nNewSize as Integer
nNewSize
= ( nUpper - nLower )
If bNeedAdd Then nNewSize = nNewSize + 1
Dim aNewInfo( nNewSize ) as new com.sun.star.beans.PropertyValue
' copy the
elements (a simply copy does not work in Basic)
For i = nLower To nUpper
aNewInfo( i ) = aOldInfo( i )
Next i
' append
the new setting, if necessary
If ( bNeedAdd ) Then
aNewInfo( nUpper + 1 ).Name = sSettingsName
aNewInfo( nUpper + 1 ).Value = aSettingsValue
End If
AddInfo
= aNewInfo()
End Function