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