Disable/Enable outer join sequnce

Note: The below information is outdated, it applies only to OpenOffice.org versions prior to 2.0.

The Problem

When working with drivers which not fully support the JDBC or ODBC standard, it may happen that the escape sequences are not fully supported. Statement like this don't work.
"SELECT * from {OJ xx left join yy ... }"

The Solution

The OpenOffice.org data source supports to work around the problem in just to remove the {OJ} keywords from the SQL statement.

Disable/Enable outer join sequence 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: EnableOuterJoinEscape
Value: FALSE

Unfortunately, there is no user interface, yet, for doing so. You could use the Basic macro provided below, which adds the setting for a data source of your choice.

The Macro

The following macro enables Disable/Enable outer join sequence 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:" )
    IgnoreDriverPrivileges(sDataSourceName )
End Sub

Sub IgnoreDriverPrivileges(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 IgnoreDriverPrivilegesflag
    Dim bFlag as Boolean
    bFlag = FALSE
    Dim aInfo as Variant
    aInfo = aDataSource.Info
    aInfo = AddInfo( aInfo, "EnableOuterJoinEscape", 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

The Free and Open Productivity Suite

Disable/Enable outer join sequnce

The Problem

The Solution

The Macro