Mega Code Archive
Creating views that contain the UNION keyword
<![CDATA[
Title: Creating views that contain the UNION keyword
Question: 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.
Answer:
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
EXEC SQL SET TRANSACTION NAME T1;
cout
EXEC SQL CREATE VIEW V1 (EMP_NO) AS
SELECT EMP_NO
FROM EMPLOYEE
UNION SELECT EMP_NO
FROM EMPLOYEE1;
long * p = isc_status;
cout if (SQLCODE != 0)
{
isc_interprete(&SQLMESSAGE[0], &p);
cout 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 files\intrbase\include"
-L"c:\program files\intrbase\lib 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 files\intrbase\include" directory on
to the end of the Include entries.
- add the "c:\program files\intrbase\lib" 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.
]]>