| <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> |
| <html> |
| <head> |
| <meta http-equiv="content-type" |
| content="text/html; charset=ISO-8859-1"> |
| <title>Parameter Name Substitution</title> |
| </head> |
| <body> |
| <h1>Table Aliases for data sources</h1> |
| <span style="font-size:xx-large;font-weight:bold">Note:</span> <em>The below information |
| is outdated, it applies only to OpenOffice.org versions prior to 2.0.</em> |
| <h2>The Problem</h2> |
| When working with select statements, OpenOffice.org usually uses an |
| alias name for tables such as in "<span style="font-family: monospace;">SELECT |
| * FROM <table> </span>aliasname" Here "aliasname"" is a |
| so-called table alias. However, some |
| databases do not allow such aliases. The |
| statement then would be "<span style="font-family: monospace;">SELECT * |
| FROM <table></span>". Such databases |
| usually reject statements with aliases.<br> |
| <h2>The Solution</h2> |
| OpenOffice.org features the disabling as well as the enabling of this |
| behavior.<br> |
| <br> |
| Table aliases can be enabled on a per-data-source basis. |
| For this, the "Info" property of a <a |
| href="http://api.openoffice.org/docs/common/ref/com/sun/star/sdb/DataSource.html">data |
| source</a> should contain a name-value-pair with<br> |
| <span style="font-family: monospace;">Name</span>: <span |
| style="font-family: monospace;">AppendTableAlias</span><br> |
| <span style="font-family: monospace;">Value</span>: <span |
| style="font-family: monospace;">FALSE</span><br> |
| <br> |
| 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.<br> |
| <br> |
| Note that this feature will be first available in version |
| OpenOffice.org 2.0. |
| <h2>The Macro</h2> |
| 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 <a |
| href="../downloads/">downloads section</a>.<br> |
| <br> |
| <span style="font-family: monospace; color: rgb(102, 102, 102);">REM |
| ***** BASIC *****</span><br style="font-family: monospace;"> |
| <br style="font-family: monospace;"> |
| <span style="font-family: monospace;"><span |
| style="color: rgb(0, 0, 128);">Option Explicit</span><br> |
| <br> |
| <span style="color: rgb(0, 0, 128);">Sub</span> <span |
| style="color: rgb(0, 128, 0);">Main</span><br> |
| <span style="color: rgb(0, 0, 128);">Dim</span> <span |
| style="color: rgb(0, 128, 0);">sDataSourceName</span> <span |
| style="color: rgb(0, 0, 128);">as String</span><br> |
| <span style="color: rgb(0, 128, 0);">sDataSourceName</span> |
| = <span style="color: rgb(0, 128, 0);">InputBox</span>( <span |
| style="color: rgb(255, 0, 0);">"Please enter the name of the data |
| source:"</span> )<br> |
| <span style="color: rgb(0, 128, 0);">AppendTableAlias</span>(<span |
| style="color: rgb(0, 128, 0);">sDataSourceName</span> )<br> |
| <span style="color: rgb(0, 0, 128);">End Sub</span><br> |
| <br> |
| <span style="color: rgb(0, 0, 128);">Sub</span> <span |
| style="color: rgb(0, 128, 0);">AppendTableAlias</span>( <span |
| style="color: rgb(0, 128, 0);">sDataSourceName</span> <span |
| style="color: rgb(0, 0, 128);">as String</span> )<br> |
| <span style="color: rgb(102, 102, 102);">' the data |
| source context (ehm - the service name is historical :)</span><br> |
| <span style="color: rgb(0, 0, 128);">Dim</span> <span |
| style="color: rgb(0, 128, 0);">aContext</span> <span |
| style="color: rgb(0, 0, 128);">as Object</span><br> |
| <span style="color: rgb(0, 128, 0);">aContext</span> |
| = <span style="color: rgb(0, 128, 0);">createUnoService</span>( <span |
| style="color: rgb(255, 0, 0);">"com.sun.star.sdb.DatabaseContext"</span> |
| )<br> |
| <br> |
| <span style="color: rgb(0, 0, 128);">If</span> ( <span |
| style="color: rgb(0, 0, 128);">Not</span> <span |
| style="color: rgb(0, 128, 0);">aContext</span>.<span |
| style="color: rgb(0, 128, 0);">hasByName</span>( <span |
| style="color: rgb(0, 128, 0);">sDataSourceName</span> ) ) <span |
| style="color: rgb(0, 0, 128);">Then</span><br> |
| <span |
| style="color: rgb(0, 128, 0);">MsgBox</span> <span |
| style="color: rgb(255, 0, 0);">"There is no data source named "</span> |
| + <span style="color: rgb(0, 128, 0);">sDataSourceName</span> + <span |
| style="color: rgb(255, 0, 0);">"!"</span><br> |
| <span |
| style="color: rgb(0, 0, 128);">Exit Sub</span><br> |
| <span style="color: rgb(0, 0, 128);">End If</span><br> |
| <br> |
| <span style="color: rgb(102, 102, 102);">' the data |
| source</span><br> |
| <span style="color: rgb(0, 0, 128);">Dim</span> <span |
| style="color: rgb(0, 128, 0);">aDataSource</span> <span |
| style="color: rgb(0, 0, 128);">as Object</span><br> |
| <span style="color: rgb(0, 128, 0);">aDataSource</span> |
| = <span style="color: rgb(0, 128, 0);">aContext</span>.<span |
| style="color: rgb(0, 128, 0);">getByName</span>( <span |
| style="color: rgb(0, 128, 0);">sDataSourceName</span> )<br> |
| <br> |
| <span style="color: rgb(102, 102, 102);">' append |
| the new AppendTableAlias flag</span><br> |
| <span style="color: rgb(0, 0, 128);">Dim</span> <span |
| style="color: rgb(0, 128, 0);">bFlag</span> <span |
| style="color: rgb(0, 0, 128);">as Boolean</span><br> |
| <span style="color: rgb(0, 128, 0);">bFlag</span> = |
| <span style="color: rgb(0, 128, 0);">FALSE</span><br |
| style="color: rgb(0, 128, 0);"> |
| <span style="color: rgb(0, 0, 128);">Dim</span> <span |
| style="color: rgb(0, 128, 0);">aInfo</span> <span |
| style="color: rgb(0, 0, 128);">as Variant</span><br> |
| <span style="color: rgb(0, 128, 0);">aInfo</span> = |
| <span style="color: rgb(0, 128, 0);">aDataSource</span>.<span |
| style="color: rgb(0, 128, 0);">Info</span><br> |
| <span style="color: rgb(0, 128, 0);">aInfo</span> = |
| <span style="color: rgb(0, 128, 0);">AddInfo</span>( <span |
| style="color: rgb(0, 128, 0);">aInfo</span>, <span |
| style="color: rgb(255, 0, 0);">"AppendTableAlias"</span>, <span |
| style="color: rgb(0, 128, 0);">bFlag</span> )<br> |
| <br> |
| <span style="color: rgb(102, 102, 102);">' and |
| write back</span><br> |
| <span style="color: rgb(0, 128, 0);">aDataSource</span>.<span |
| style="color: rgb(0, 128, 0);">Info</span> = <span |
| style="color: rgb(0, 128, 0);">aInfo</span><br> |
| <span style="color: rgb(102, 102, 102);">' flush |
| (not really necessary, but to be on the safe side :)</span><br> |
| <span style="color: rgb(0, 128, 0);">aDataSource</span>.<span |
| style="color: rgb(0, 128, 0);">flush</span><br> |
| <span style="color: rgb(0, 0, 128);">End Sub</span><br> |
| <br> |
| <span style="color: rgb(0, 0, 128);">Function</span> <span |
| style="color: rgb(0, 128, 0);">AddInfo</span>( <span |
| style="color: rgb(0, 128, 0);">aOldInfo</span>() <span |
| style="color: rgb(0, 0, 128);">as new</span> <span |
| style="color: rgb(0, 128, 0);">com.sun.star.beans.PropertyValue</span>,<span |
| style="color: rgb(0, 128, 0);">sSettingsName</span> <span |
| style="color: rgb(0, 0, 128);">as String</span>, <span |
| style="color: rgb(0, 128, 0);">aSettingsValue</span> <span |
| style="color: rgb(0, 0, 128);">as Variant</span> ) <span |
| style="color: rgb(0, 0, 128);">as Variant</span><br> |
| <span style="color: rgb(0, 0, 128);">Dim</span> <span |
| style="color: rgb(0, 128, 0);">nLower</span> <span |
| style="color: rgb(0, 0, 128);">as Integer</span><br> |
| <span style="color: rgb(0, 0, 128);">Dim</span> <span |
| style="color: rgb(0, 128, 0);">nUpper</span> <span |
| style="color: rgb(0, 0, 128);">as Integer</span><br> |
| <span style="color: rgb(0, 128, 0);">nLower</span> |
| = <span style="color: rgb(0, 128, 0);">LBound</span>( <span |
| style="color: rgb(0, 128, 0);">aOldInfo</span>() )<br> |
| <span style="color: rgb(0, 128, 0);">nUpper</span> |
| = <span style="color: rgb(0, 128, 0);">UBound</span>( <span |
| style="color: rgb(0, 128, 0);">aOldInfo</span>() )<br> |
| <br> |
| <span style="color: rgb(102, 102, 102);">' look if |
| the setting is already present</span><br> |
| <span style="color: rgb(0, 0, 128);">Dim</span> <span |
| style="color: rgb(0, 128, 0);">bNeedAdd</span> <span |
| style="color: rgb(0, 0, 128);">as Boolean</span><br> |
| <span style="color: rgb(0, 128, 0);">bNeedAdd</span> |
| = <span style="color: rgb(0, 128, 0);">TRUE</span><br> |
| <br> |
| <span style="color: rgb(0, 0, 128);">Dim</span> <span |
| style="color: rgb(0, 128, 0);">i</span> <span |
| style="color: rgb(0, 0, 128);">As Integer</span><br> |
| <span style="color: rgb(0, 0, 128);">For</span> <span |
| style="color: rgb(0, 128, 0);">i</span> = <span |
| style="color: rgb(0, 128, 0);">nLower</span> <span |
| style="color: rgb(0, 0, 128);">To</span> <span |
| style="color: rgb(0, 128, 0);">nUpper</span><br> |
| <span |
| style="color: rgb(0, 0, 128);">If</span> ( <span |
| style="color: rgb(0, 128, 0);">aOldInfo</span>( <span |
| style="color: rgb(0, 128, 0);">i</span> ).<span |
| style="color: rgb(0, 0, 128);">Name</span> = <span |
| style="color: rgb(0, 128, 0);">sSettingsName</span> ) <span |
| style="color: rgb(0, 0, 128);">Then</span><br> |
| <span |
| style="color: rgb(0, 128, 0);">aOldInfo</span>( <span |
| style="color: rgb(0, 128, 0);">i</span> ).<span |
| style="color: rgb(0, 128, 0);">Value</span> = <span |
| style="color: rgb(0, 128, 0);">aSettingsValue</span><br> |
| <span |
| style="color: rgb(0, 128, 0);">bNeedAdd</span> = <span |
| style="color: rgb(0, 128, 0);">FALSE</span><br> |
| <span |
| style="color: rgb(0, 0, 128);">End If</span><br> |
| <span style="color: rgb(0, 0, 128);">Next</span> i<br> |
| <br> |
| <span style="color: rgb(102, 102, 102);">' allocate |
| the new array</span><br> |
| <span style="color: rgb(0, 0, 128);">Dim</span> <span |
| style="color: rgb(0, 128, 0);">nNewSize</span> <span |
| style="color: rgb(0, 0, 128);">as Integer</span><br> |
| <span style="color: rgb(0, 128, 0);">nNewSize</span> |
| = ( <span style="color: rgb(0, 128, 0);">nUpper</span> - <span |
| style="color: rgb(0, 128, 0);">nLower</span> )<br> |
| <span style="color: rgb(0, 0, 128);">If</span> <span |
| style="color: rgb(0, 128, 0);">bNeedAdd</span> <span |
| style="color: rgb(0, 0, 128);">Then</span> <span |
| style="color: rgb(0, 128, 0);">nNewSize</span> = <span |
| style="color: rgb(0, 128, 0);">nNewSize</span> + <span |
| style="color: rgb(255, 0, 0);">1</span><br> |
| <span style="color: rgb(0, 0, 128);">Dim</span> <span |
| style="color: rgb(0, 128, 0);">aNewInfo</span>( <span |
| style="color: rgb(0, 128, 0);">nNewSize</span> ) <span |
| style="color: rgb(0, 0, 128);">as new</span> <span |
| style="color: rgb(0, 128, 0);">com.sun.star.beans.PropertyValue</span><br> |
| <br> |
| <span style="color: rgb(102, 102, 102);">' copy the |
| elements (a simply copy does not work in Basic)</span><br> |
| <span style="color: rgb(0, 0, 128);">For</span> <span |
| style="color: rgb(0, 128, 0);">i</span> = <span |
| style="color: rgb(0, 128, 0);">nLower</span> <span |
| style="color: rgb(0, 0, 128);">To</span> <span |
| style="color: rgb(0, 128, 0);">nUpper</span><br> |
| <span |
| style="color: rgb(0, 128, 0);">aNewInfo</span>( <span |
| style="color: rgb(0, 128, 0);">i</span> ) = <span |
| style="color: rgb(0, 128, 0);">aOldInfo</span>( <span |
| style="color: rgb(0, 128, 0);">i</span> )<br> |
| <span style="color: rgb(0, 0, 128);">Next</span> i<br> |
| <br> |
| <span style="color: rgb(102, 102, 102);">' append |
| the new setting, if necessary</span><br> |
| <span style="color: rgb(0, 0, 128);">If</span> ( <span |
| style="color: rgb(0, 128, 0);">bNeedAdd</span> ) <span |
| style="color: rgb(0, 0, 128);">Then</span><br> |
| <span |
| style="color: rgb(0, 128, 0);">aNewInfo</span>( <span |
| style="color: rgb(0, 128, 0);">nUpper</span> + <span |
| style="color: rgb(255, 0, 0);">1</span> ).<span |
| style="color: rgb(0, 0, 128);">Name</span> = <span |
| style="color: rgb(0, 128, 0);">sSettingsName</span><br> |
| <span |
| style="color: rgb(0, 128, 0);">aNewInfo</span>( <span |
| style="color: rgb(0, 128, 0);">nUpper</span> + <span |
| style="color: rgb(255, 0, 0);">1</span> ).<span |
| style="color: rgb(0, 128, 0);">Value</span> = <span |
| style="color: rgb(0, 128, 0);">aSettingsValue</span><br> |
| <span style="color: rgb(0, 0, 128);">End If</span><br> |
| <br> |
| <span style="color: rgb(0, 128, 0);">AddInfo</span> |
| = <span style="color: rgb(0, 128, 0);">aNewInfo</span>()<br> |
| <span style="color: rgb(0, 0, 128);">End Function</span></span> |
| <h2></h2> |
| </body> |
| </html> |
