ConfigureProgressTableview Advanced Features for Progress Connections: Distributed Databases, Array Fields, and Database Triggers Advanced Features for Progress Connections: Distributed Databases, Array Fields, and Database Triggers Our Progress SQL-89 ODBC Drivers support a number of advanced features (referred to collectively as "Tableview" features) through the use of a custom "Data Dictionary File." Tableview configuration cannot be supported in our SQL-92 drivers, due to changes made in the Progress Software components. Some features are still supported through different methods; others simply cannot be re-implemented (as of this writing). Distributed Databases This is the most common reason users need to configure Tableview features, with the SQL-89 drivers. There is no special configuration required here; just the basic Tableview Setup (below).Due to changes made in the Progress database engine between their SQL-89 and SQL-92 engines, JOINs over multiple Progress databases (i.e., multiple .db files) cannot be executed over a single SQL-92 mode ODBC connection against Progress OpenEdge 9.1x, 10.x, and laterAccording to the Progress SQL-92 documentation:
An application can connect to more than one database at a time, with a maximum of 10 connections. However, the application can actually gain access to only one database at a time. The database name specified in the CONNECT statement becomes the active one.
For this reason, if you need to make such queries, OpenLink recommends you run Progress 9 in SQL-89 mode (perhaps in combination with SQL-92 mode; this "hybrid" operation is the default for Progress 9.x), and use the appropriate SQL-89 driver, permitting execution of these JOINs in the traditional manner; or use Virtuoso and multiple single-DB SQL-92 connections to unify the Progress databases into a single Virtuoso schema, against which the JOINs may be executed. Array Fields Array fields are the second-most common reason users need to configure Tableview features. There is again no special configuration required with the SQL-89 drivers; just the basic Tableview Setup (below).Progress array fields are not supported via the SQL-92 engine the same way they are via the traditional SQL-89 interface drivers.Progress tables with array fields cannot be created while connected to the SQL-92 engine; however, array fields previously created using the SQL-89 driver may be accessed via the SQL-92 engine.From a technical perspective, the array field is mapped to a single variable character (VARCHAR) field, and the data is returned as a semicolon-separated list. Individual elements of the array may be accessed using three Progress functions within SQL Statements: <tgroup><thead /><tbody> <row><entry><para><emphasis>Progress function</emphasis> </para></entry><entry><para><emphasis>Effect</emphasis> </para></entry></row> <row><entry>PRO_ELEMENT </entry><entry>This function allows one or more elements to be selected. </entry></row> <row><entry>PRO_ARR_ESCAPE </entry><entry>This function simplifies escaping special characters when updating an array field. </entry></row> <row><entry>PRO_ARR_DESCAPE </entry><entry>This function removes escape characters from elements selected from an array field. </entry></row> </tbody></tgroup></table> <para>For more information on this topic, visit Appendix C, section 2, of the <emphasis>SQL-92 Guide and Reference</emphasis>, in the Progress 9 documentation. (Exact location will be different in Progress OpenEdge documentation.)</para><bridgehead class="http://www.w3.org/1999/xhtml:h2"> Progress Database Triggers </bridgehead> <para>Progress Database Triggers are only supported over SQL-89-based connections. The SQL-92 engine has completely changed the way Progress Triggers work.</para><para><emphasis><emphasis>Important:</emphasis> To make use of Progress Database Triggers, the Logical Database Name to which your Triggers have been compiled must match the value provided to the "-ld" parameter when setting the Progress Database Connect options during the execution of "setup.p".</emphasis></para><para>If you have compiled your triggers against a database physically identified as "/usr/progress/demo.db" but logically identified as "mydemo", your "setup.p" Database Connection String (used below) would be: </para><programlisting>/usr/progress/demo -ld mydemo </programlisting><para> </para><bridgehead class="http://www.w3.org/1999/xhtml:h2"> Setting Up Tableview Functionality for SQL-89 Connections </bridgehead> <para>This custom "Data Dictionary File" is built by running the OpenLink-provided Progress Program File "setup.p" found in the "bin" sub-directory below the OpenLink base installation directory. Follow the instructions for your platform --</para><itemizedlist mark="bullet" spacing="compact"><listitem> <ulink url="RunTableviewOnUnix">Unix Host</ulink> </listitem> <listitem> <ulink url="RunTableviewOnWindows">Windows Host</ulink></listitem> </itemizedlist><para>Once the setup.p script has been run, follow the instructions to configure your driver type to use the resulting tableview.pf and tableview.dat files:</para><itemizedlist mark="bullet" spacing="compact"><listitem> <ulink url="ConfigureSingle-TierToUseTableview">Single-Tier</ulink> </listitem> <listitem> <ulink url="ConfigureMulti-TierToUseTableview">Multi-Tier</ulink></listitem> </itemizedlist><bridgehead class="http://www.w3.org/1999/xhtml:h3"> Advanced Tableview Configuration </bridgehead> <para><ulink url="WorkingWithMultipleSetsOfTableviewConfigurationFilesMulti-TierOnly">Working with Multiple Sets of Tableview Configuration Files (Multi-Tier Only)</ulink></para><bridgehead class="http://www.w3.org/1999/xhtml:h2"> Troubleshooting Common Tableview Problems </bridgehead> <para><ulink url="SetupiEnforces255CharFieldLimit">setup.i Enforces 255 Char Field Limit</ulink> <ulink url="TableviewDefaultAllowsOnly5Databases">Tableview Default allows only 5 databases</ulink> </para></section></docbook>