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 . . . UNIONgenerates 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