community.borland.com

Article #25099: Creating views that contain the UNION keyword.

Problem:
In creating views sometimes using the UNION keyword is 
desired. In using ISQL, Windows ISQL, Delphi or another 
DSQL type of interface, the command:

  CREATE VIEW . . . SELECT . . . UNION  

generates an "unknown token . . ." error.  The view can be 
created using embedded SQL, but this is not inherently easy.


Solution:
The solution shown below uses embedded SQL in a C++ 
program, allowing the C++ program to create a view that 
uses the UNION keyword.  The general syntax looks like:

  CREATE VIEW  SELECT . . . UNION 

The view will have the following properties:

- It is read-only
- The "*" wildcard cannot be used in the  SELECT statement 
  to specify all columns.  All columns must be explicity stated.

This approach uses the GPRE utility that is not available in 
Local InterBase.  The GPRE utility will be used to parse through 
a .cpp file that contains embedded SQL statement, this .cppp is 
given a .e extension.  Following parsing the fiile GPRE will output 
a .cpp where the embedded SQL statements are replaced with 
calls to the InterBase API.  Next, this approach examines the .cpp 
for a minor correction.  Lastly the .cpp file is built and the executable 
is ran.

Follow these steps to begin creating the view:

1. Create a .cpp file and save it with a .e extension.  The .e file 
most likely look similar to the one show here:

  /* cv.e */
  #include 
  #include 

  isc_tr_handle T1 = 0;
           char SQLMESSAGE[128];

  int main (void);

  int main (void)
  {

    EXEC SQL SET DATABASE DB1 = "D:/IBSERVER/EXAMPLES/EMPLOYEE.GDB";

    EXEC SQL CONNECT DB1;

      cout << "SQLCODE for CONNECT = " << SQLCODE << endl;

    EXEC SQL SET TRANSACTION NAME T1;

      cout << "SQLCODE for SET TRANS = " << SQLCODE << endl;


    EXEC SQL CREATE VIEW V1 (EMP_NO) AS
      SELECT EMP_NO
      FROM EMPLOYEE
      UNION SELECT EMP_NO
        FROM EMPLOYEE1;

    long * p = isc_status;

    cout << "SQLCODE for CREATE VIEW= " << SQLCODE << endl;
    if (SQLCODE != 0)
    {
      isc_interprete(&SQLMESSAGE[0], &p);
      cout << "SQLMESSAGE for CREATE VIEW= " << SQLMESSAGE << endl;
    }

    EXEC SQL COMMIT TRANSACTION T1;

    return 0;
  }

  On the SET DATABASE line specify the location of the database file.  
  On Windows NT and Windows95 use the "/" character instead of the "" 
  character to delimit drives and directory paths.

2. Run this command from the operating system command line:

  gpre -user "SYSDBA" -password "masterkey" -m cv.e cv.cpp 

  Fill in the proper user name and password.  Replace the file names of 
  cv.e and cv.cpp with the appropriate filenames

3.  Use a text editor and open up the resulting .cpp file.  Go to the 
  line containing the function isc_ddl().  Change the parameter 
  "&gds__trans" to "&T1".

4.  Compile the .cpp file and run it.  

  For those using the Borland C++ v5.0 command line compiler, bcc32, use 
  the following syntax:

  bcc32 -I"c:program filesintrbaseinclude" 
    -L"c:program filesintrbaselib cv.cpp gds32.lib

  When using the Borland C++ IDE environment make the following changes:

  - Create a project that of type Application, Platform is WIN32, and 
    the Target Model is Console.

  - In the Options | Project | Directories dialog make these changes:
    - add the "c:program filesintrbaseinclude" directory on to the 
      end of the Include entries.
    - add the "c:program filesintrbaselib" directory on to the end of 
      the Library directories.

  - In the project window add the gds32.lib file in to the project

  For users of the Microsoft Visual C++ (and compatible compilers) use 
  the GDS32_MS.LIB file instead of the GDS32.LIB file.  These files have 
  different internal formats.

Last Modified: 26-OCT-00