To comment on the following update, log in, then open the issue: http://www.openoffice.org/issues/show_bug.cgi?id=110117
------- Additional comments from [email protected] Tue Mar 16 17:20:45 +0000 2010 ------- Hi Below are some examples of issues re aliasing and the purpose of Advanced Settings which I think clearly indicate the intended use of Append table names and the use of "AS" - i.e that both options can modify the use of aliasing. Regards Ian Please see Issue 104961 Refer to below from Frank - note the second paragraph << From: [email protected] <[email protected]> Date: Wed, 6 Dec 2006 13:01:27 +0100 (MET) Content-Type: multipart/mixed; boundary="----=_Part_109_29221778.1165406487462" Subject: new/CWS dba22ui : advanced database setting: "Use keyword AS before table alias Product: Database Access Type: new Title: advanced database setting: "Use keyword AS before table alias names" Posted by: [email protected] Affected: - Effective from: CWS dba22ui *Flags* ------- API/ BASIC [ ] Configuration [ ] File format change [ ] Help/ Guide [x] Performance test [ ] Translation [x] UI relevant [x] *Description* ------------- In the advanced database settings (menu: Edit / Database / Advanced Settings, dialog page: special settings) a new option is available, called "Use keyword AS before table alias names". This option is enabled if and only if "Append the table alias name on SELECT statements" option is checked, and itself checked by default for newly created databases. When checked, SQL statements generated by Base will use the AS keyword as follows: SELECT * FROM "table" AS "table" When not checked, the above statement would look like SELECT * FROM "table" "table" The first option is more human readable, the second option is required by some databases (Oracle ODBC, in particular). --------------------------------------------------- http://dba.openoffice.org/howto/AppendTableAlias.html 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 --------------------------------------------------------------------------- --------------------------------------------------------------------- Please do not reply to this automatically generated notification from Issue Tracker. Please log onto the website and enter your comments. http://qa.openoffice.org/issue_handling/project_issues.html#notification --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
