============================================================================ Information On The Connections ini File Used By ReportSmith: "RS_SQLIF.INI" ---------------------------------------------------------------------------- This file contains information pertaining to database connections for ReportSmith. New with the 3.0 release, this file also specifies which connections should display as available in the Table Open dialog. ReportSmith needs to know certain information about the capabilities and idiosyncrasies of the databases it retrieves data from. The standard ReportSmith product comes with this knowledge built in for many of the industry-standard databases. With the advent of Microsoft's ODBC standard for accessing databases, however, it is now possible for the end user to install new ODBC drivers that the ReportSmith product has no built-in special knowledge of. While the ODBC interface allows ReportSmith to query the driver to find out some of this information, much of it is not available through the ODBC interface. When ReportSmith is directed to use a database driver for which it has no built-in knowledge, it makes the best guesses it can with regards to the capabilites and behaviors of the database by utilyzing what information is is available through the standard ODBC interface. Depending upon how closely the database follows certain ODBC conventions and standards, this may or may not be acceptable. The RS_SQLIF.INI file is used to allow the information about a new ODBC driver to be specified to ReportSmith. This allows ReportSmith to interact more appropriately with the database, and with the user, by properly conforming to the database's specific idiosyncrasies and by taking advantage of the database's capabilities. The RS_SQLIF.INI file is used not only for new ODBC drivers. It may also be used to change, add, or delete capabilities for the built-in databases as well. Part or all of the information for the database knowledge built-in within ReportSmith may be overridden by placing specifications in the file. This could be useful, for example, if an older, newer, or custom version of the database is being used. By overriding the built-in knowledge, ReportSmith could be made to behave properly with the different database version. As a side benefit, the RS_SQLIF.INI file can also be used to disable the use of certain features of the database, if desired. The RS_SQLIF.INI file may be used, for example, to prevent the user from accessing particular capabilities of the database by telling ReportSmith that the database does not have those capabilities. This applies to the built-in databases as well as to newly-added ODBC drivers. The following information may be specified in the RS_SQLIF.INI file: 1. The outer join capabilites provided by the database. 2. The SQL syntax used to express outer joins, if they are supported. 3. Whether the SQL "COUNT(*)" aggregate function is supported. 4. Whether expressions are supported in the SQL "ORDER BY" clause. 5. The quoting character placed around field names in SQL statements to distinguish them from keywords, and to allow field names that contain special characters. 6. The SQL functions and operators provided by the database, and the details of their syntax. The ReportSmith product contains built-in knowledge about the following types of databases: Microsoft SQL Server Sybase Gupta SQLBase DB2 Oracle Teradata Ingres Informix When this release of ReportSmith is installed, a default version of RS_SQLIF.INI is placed in the Windows directory. This default version contains information about certain ODBC drivers. FORMAT OF THE RS_SQLIF.INI FILE THE [CONNECTIONS] SECTION RS3.0 has a new section. The [Connections] section specifies which connections that ReportSmith should display in Connections drop down list in the Table Open Dialog box. Normally this section is set by the install and reflects which connections were installed. A user may change the values in order to remove the availability of a connection. If ODBC=1 is specified then RS searches for installed ODBC drivers and places those in the listbox. The settings take the form of: [Connections] Oracle=0 - Oracle was not installed/not available as a connection SQL Base=1 - Gupta SQL Base is available SQL Server=1 Sybase10=1 Sybase=0 Teradata=0 Idapi=1 - IDAPI (aka BDE) should always be set to 1. ReportSmith uses IDAPI for local sorting or when in client disk mode. DB2=0 DB2 Gupta=0 dBase for Windows=0 - Special integrated reporting with Borland's dBASE for Windows ODBC=1 Unify=0 SPECIFIC CONNECTION SECTION NAMES The RS_SQLIF.INI file follows the format of Microsoft Windows ".ini" files. There is a section for each database type. The section name is either the ODBC "Description" text for the ODBC Data Source. For the ReportSmith for SQL Databases product, there are special reserved names for ReportSmith's built-in knowledge. As with all Windows .ini files, the section name is enclosed in brackets "[]". The special reserved section names are: [SQL Server] Microsoft SQL Server / Sybase [DB2] DB2 [Teradata] Teradata [Oracle] Oracle [SQLBase] Gupta SQLBase [Ingres] Ingres For ODBC databases, ReportSmith first looks at the ODBC "Description" text for the database. If the description text contains anywhere within it certain special substrings, then it is assumed that the ODBC driver is for one of the databases ReportSmith knows about and the built-in knowledge is used (unless overridden in one of the special section names listed above). Otherwise, ReportSmith looks for a section name that exactly matches the ODBC Description text. If this substring is contained within the ODBC description, in ReportSmith assumes that the any combination of upper/lower ODBC driver is for this type of case: database: MICROSOFT SQL SERVER Microsoft SQL Server / Sybase SYBASE Microsoft SQL Server / Sybase DB2 DB2 TERADATA TeraData ORACLE Oracle GUPTA Gupta SQLBase INGRES Ingres SECTION CONTENTS Within each database section are keywords and values that specify the capabilities and idiosyncrasies of that particular database. As with all Windows .ini files, each entry is of the form "keyword=value". There are a number of specifically-named keywords that describe information for which there is a single value. There are also numbered keywords used to describe lists of values for operator and function lists. For these lists, there is a keyword that gives the count of the number of items in the list. SINGLE-VALUE KEYWORDS SortExprSupport=n "n" should be the number 1 if the database supports expressions in the SQL "ORDER BY" clause, or the number 0 if it does not. CountStarSupport=n "n" should be the number 1 if the database supports the SQL "COUNT(*)" aggregate function, or the number 0 if it does not. IDQuote=string "string" is the quoting character(s) to be placed around field names in SQL statements to allow SQL keywords and special characters in field names. OuterJoinSyntax=n "n" should be the number 1, 2 or 3, according to which syntax is used by the database to support outer joins: 1 = The text is placed around the join operator. To the left of it for a left outer join, to the right of it for a right outer join, and in both places for a full outer join. The following examples assume that the join text is an asterisk: Left outer join: col1 *= col2 Right outer join: col1 =* col2 Full outer join: col1 *=* col2 2 = The text follows the join column names. It follows the left column name for a left outer join, the right column name for a right outer join, both column names for a full outer join. The following examples assume that the join text is "(+)": Left outer join: col1 (+) = col2 Right outer join: col1 = col2 (+) Full outer join: col1 (+) = col2 (+) 3 = The special ODBC outer join SQL syntax extension is used in the SQL "FROM" clause. See the ODBC standard for a description. In this case, only left outer joins are permitted, and if an outer join exists the query may contain only those two tables. OuterJoinText=string "string" should be the character(s) used in the outer join syntax (the asterisk and "(+)" in the OuterJoinSyntax examples above). This keyword is used only if OuterJoinSyntax is 1 or 2. OuterJoinSupport=string "string" should be a string of characters that signifies which types of outer joins are supported by the database. It can be any combination of the following characters: L - Left outer joins are supported. R - Right outer joins are supported. F - Full outer joins are supported. M - The database supports multiple outer joins in a single query. This keyword is ignored is OuterJoinSyntax is 3. OPERATOR AND FUNCTION LIST KEYWORDS These keywords are used to allow the list of SQL functions and operators supported by the database to listed, so that they may appear in various dialog boxes in ReportSmith (for example the "Selections" and "Derived Field" dialogs). For each database for which ReportSmith has built-in knowledge, ReportSmith contains lists of the functions and operators supported. It also contains two lists of standard ODBC functions and operators that it will use for all ODBC databases for which it has no specific knowledge. This portion of RS_SQLIF.INI can be used to either replace or augment the built-in lists. Entirely new lists can be given, or additional entries added to the existing lists. Each list is divided into "groups". For example, the functions list might be divided into string, arithmetic, and date/time functions. Each group appears as entry in the upper combobox in the ReportSmith dialogs. Each element of a group contains two entries. One entry is the description text to be displayed to the user in the listbox, while the other entry is the substitution text to be inserted into the SQL edit area if the user picks that entry from the listbox. The substitution text may contain up to two special marker characters: A substitution location marker (SLM) is a location in the text, marked by "\r" characters, where the user will have to insert something -- for example, a parameter value for a function call. If at the time of the insert the user has blocked (highlighted) some text, the blocked text will be placed inside the inserted text at the SLM location. A caret location marker (CLM) is a location in the text, marked by "\n" characters, where the caret is to be placed after the text is inserted. A CLM differs from an SLM in that for a CLM, no surrounding of blocked text is done. The substitution text can contain one SLM, one CLM, or an SLM followed by a CLM. In the latter case, the CLM is used as the location to put the caret if something was automatically substituted into the SLM location. In addition to the SLM and CLM, the substitution text may also contain "\t" characters, which will be replaced with a single tab character. This allows the tab-stops that ReportSmith places in some listboxes to be used. FUNCTION LIST KEYWORDS FnListType=n "n" is a number that indicates whether this list is to replace or augment any built-in function list in ReportSmith for this database. It should be one of the following values: 0 - Do not use the built-in list. The entire list will be specified in the .ini file. 1 - Use the standard built-in list for this database, if there is one. The entries in the .ini file will appended to the built-in list. For ODBC databases, the list of functions described in the ODBC standard is used. 2 - Use the built-in ODBC "escaped" function list. This applies to ODBC databases only. The ODBC standard provides for a special function syntax that allows logical function names to be used in the SQL statement that are then translated to the actual function names and syntaxes that are used by the underlying databases. If this setting is used for a non-ODBC database, setting 1 will be used. FnGrpCount=n "n" is the total number of function groups in the ini file. This must be a number from 0 to 255. FnGrp1Title=string There is one of these keywords for each function group. The "1" in the keyword should be replaced by the actual group number (from 1 to the setting of FnGrpCount). This specifies the title text for this group -- the description that is to appear in the top combobox in the dialog box. If this list is being merged with a built-in list and the built-in list already contains a group with the exact same title, the entries in the .ini group will be appended to the built-in group. FnGrp1Count=n There is one of these keywords for each function group. The "1" in the keyword should be replaced by the actual group number (from 1 to the setting of FnGrpCount). "n" should be a number from 0 to 255 that specifies how many entries (function descriptions) are contained within the group. FnGrp1Item1Disp=string There is one of these keywords for each function within each group. The first "1" in the keyword should be replaced by the actual group number (from 1 to the setting of FnGrpCount), while the second "1" should be replaced by the number of the function within the group (from 1 to the setting of FnGrp1Count). "string" is the descriptive text for the function that is to be displayed in the listbox. FnGrp1Item1Text=string There is one of these keywords for each function within each group. The first "1" in the keyword should be replaced by the actual group number (from 1 to the setting of FnGrpCount), while the second "1" should be replaced by the number of the function within the group (from 1 to the setting of FnGrp1Count). "string" is the substitution text to be inserted into the SQL edit area if the user picks that entry from the listbox. See the discussion above about the special "marker" characters that may be placed in this text. OPERATOR LIST KEYWORDS OpListType=n "n" is a number that indicates whether this list is to replace or augment any built-in operator list in ReportSmith for this database. It should be one of the following values: 0 - Do not use the built-in list. The entire list will be specified in the .ini file. 1 - Use the standard built-in list for this database, if there is one. The entries in the .ini file will appended to the built-in list. For ODBC databases, the SQL syntax described in the ODBC standard is used. OpGrpDCount=n OpGrpWCount=m The operators list contains some syntax items that are allowed in both derived field expressions and WHERE-clause text, and other syntax items that are allowed only in WHERE-clause text. The former must be at the beginning of the operator list, while the latter must be at the end of the operator list. "n" is the number of operator groups in the ini file that may be used in both places. "m" is the number of operator groups in the ini file that may be used only in the WHERE clause. The sum of "n" and "m" is the total number of operators in the list. "n" and "m", and the sum of "n" and "m" must be from 0 to 255. OpGrp1Title=string There is one of these keywords for each operator group. The "1" in the keyword should be replaced by the actual group number (from 1 to the setting of OpGrpDCount + OpGrpWCount). This specifies the title text for this group -- the description that is to appear in the top combobox in the dialog box. If this list is being merged with a built-in list and the built-in list already contains a group with the exact same title, the entries in the .ini group will be appended to the built-in group. OpGrp1Count=n There is one of these keywords for each operator group. The "1" in the keyword should be replaced by the actual group number (from 1 to the setting of OpGrpDCount + OpGrpWCount). "n" should be a number that specifies how many entries (operator descriptions) are contained within the group. OpGrp1Item1Disp=string There is one of these keywords for each operator within each group. The first "1" in the keyword should be replaced by the actual group number (from 1 to the setting of OpGrpDCount + OpGrpWCount), while the second "1" should be replaced by the number of the operator within the group (from 1 to the setting of OpGrp1Count). "string" is the descriptive text for the function that is to be displayed in the listbox. OpGrp1Item1Text=string There is one of these keywords for each operator within each group. The first "1" in the keyword should be replaced by the actual group number (from 1 to the setting of OpGrpDCount + OpGrpWCount), while the second "1" should be replaced by the number of the operator within the group (from 1 to the setting of OpGrp1Count). "string" is the substitution text to be inserted into the SQL edit area if the user picks that entry from the listbox. See the discussion above about the special "marker" characters that may be placed in this text. SAMPLE RS_SQLIF.INI FILES This sample .ini file disables all outer-join use in Oracle databases: [Oracle] OuterJoinSupport= This sample .ini file disables full outer join use in Oracle databases, but continues to allow left and right outer joins: [Oracle] OuterJoinSupport=LRM This sample .ini file is used for an ODBC driver for dBase. The ODBC "Description" text for the driver is "dBase Files (*.dbf)". Due to inadequacies in the ODBC interface, the driver is unable to tell ReportSmith the following information, so the .ini file is used to provide this additional information to ReportSmith: COUNT(*) is supported, even though other summary functions are not. Some additional functions in the existing numeric and string categories are provided over and above the ODBC standard. Some additional operators are provided in a new "bitwise" category over over and above the ODBC standard. These operators are allowed in both Derived Field Expressions and WHERE Clauses. One form of subquery is supported, which may only be used in WHERE clauses. [dBase Files (*.dbf)] CountStarSupport=1 FnListType=1 FnGrpCount=2 FnGrp1Title="Numeric functions:" FnGrp1Count=3 FnGrp1Item1Disp="POWER(num1, num2) - num1 to the power of num2" FnGrp1Item1Text=" POWER(\r, \n) " FnGrp1Item2Disp="EXP(num) - Exponential" FnGrp1Item2Text=" EXP(\r) \n" FnGrp1Item3Disp="SQRT(num) - Square root" FnGrp1Item3Text=" SQRT(\r) \n" FnGrp2Title="String functions:" FnGrp2Count=2 FnGrp2Item1Disp="DIFFERENCE(char, char) - SOUNDEX difference" FnGrp2Item1Text=" DIFFERENCE(\r, \n) " FnGrp2Item2Disp="REVERSE(string) - Reverse string" FnGrp2Item2Text=" REVERSE(\r) \n" OpListType=1 OpGrpDCount=1 OpGrpWCount=1 OpGrp1Title="Bitwise:" OpGrp1Count=4 OpGrp1Item1Disp="x&y\tAND" OpGrp1Item1Text=" & " OpGrp1Item2Disp="x|y\tOR" OpGrp1Item2Text=" | " OpGrp1Item3Disp="x^y\tExclusive OR" OpGrp1Item3Text=" ^ " OpGrp1Item4Disp="~x\tNOT" OpGrp1Item4Text=" ~" OpGrp2Title="Subqueries:" OpGrp2Count=1 OpGrp2Item1Disp="x = (subquery) - True if x equals single result from subquery" OpGrp2Item1Text=" = (\n)"