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