The client applications request data operations from SOLID Server in the standard SQL language. The SQL syntax used in SOLID Server is based on the SQL89 Level 2 standard and ANSI SQL2 extensions.
SOLID Server supports dynamic SQL processing, which means that client applications submit SQL queries as character strings to the server, which optimizes the queries at runtime. After receiving results of a query, the client application can process the result set dynamically, e.g., handle a variable number of columns.
In addition to these SQL interfaces, SOLID Server offers a powerful low-level API, called the SA Interface. It bypasses all SQL processing and provides a direct interface to the SOLID Database Engine routines. It is recommended for advanced embedded systems and other software solutions that have extremely tight constraints on DBMS software size and real time performance. It also offers several post-relational services. For more information on the SA Interface, contact the SOLID OEM Services unit. The rest of this chapter describes the use of SOLID through the standard SQL interface.
SOLID SQL API is based on the SQL Access Group's call level interface standard (SAG CLI) specification for dynamic SQL access to database management systems.
Based on the SAG CLI standard draft, Microsoft has developed the open database connectivity (ODBC) interface definition, which provides additional connectivity on supported platforms. ODBC client applications access databases through the ODBC interface; thus, the actual database can be selected dynamically at runtime to be any DBMS that provides an ODBC driver. The SOLID SQL API conforms fully to the ODBC interface definition.
Because the SOLID database management system strictly adheres to ANSI and ODBC standards, developers are not tied to any particular development tool. Conversely, if SOLID Server does not meet your expectations for some reason, you are free to adopt another DBMS solution without sacrificing your investments in applications or other IT infrastructure.
When selecting a client application development environment, typically two contradictory objectives are taken into account application portability and programmer productivity.
Giving more emphasis to programmer productivity leads to selecting a 4GL application development tool that hides the details of the SQL API from application programmers. There are several application development tools that allow painting a graphical user interface and access databases through the ODBC, JDBC and other standard interfaces.
When maximum portability of client applications is a major concern, then the obvious choice is using the standard C programming language and calling the functions directly. SOLID SQL API is available uniformly on all supported platforms.
Naturally, the SOLID SQL API can be used from any programming language or tool that supports C-style parameter passing. In addition, native drivers or interfaces are available for certain 4GL environments and Web application development tools.
|
|
When ODBC compliant 4GL application development tools are used, the ODBC data source can be dynamically selected at application runtime. The ODBC Driver Manager invokes the right ODBC driver to route the requests to the database server that corresponds to the selected data source name. Starting to use SOLID Server from an ODBC application requires installing the SOLID ODBC Driver on the client workstation and introducing a data source name for it.
The ODBC interface gives developers the freedom to choose the DBMS quite freely from the set of ODBC compliant products. First, the application can be developed; then, the production database can be chosen based on benchmarks, or customers of a packaged application can select the DBMS according to their standards. Using SOLID Server as the ODBC development and production database has the following benefits:
- small footprint programmers can develop and test the application on their laptops against a robust, industrial-strength database.
- scalability exactly the same interface is available when moving to production environment at enterprise level.
When developing client applications with a programming language like C or C++, the developer must call the SOLID SQL API functions directly. This chapter describes basic steps to follow when using the SOLID SQL API from an application program. The API can be used similarly from any other 3GL or 4GL tool that supports C-style function invocation and parameter passing.
The SOLID SQL API can be used from desktop applications to enterprise-wide systems. In all supported environments SOLID Server offers a uniform application programming interface, which makes it easy to port applications from one platform to another.
For a more formal definition of the SQL syntax, refer to Appendix D SOLID SQL Syntax of SOLID Server Administrator's Guide. For a full description of the SAG CLI compliant SQL API and ODBC conformance, please refer to the SOLID Server Programmers Guide and Reference.
A client application takes the following steps to interact with SOLID Server using the SOLID SQL API:
- Initializes the environment, the connection, and the SQL statement.
Here the application needs to specify what Data Source it wants to connect to. In the connecting phase, user name and password are also required. If none of these are specified in the function call, they are prompted from the user at runtime.
- Executes one or more SQL statements.
Submits SQL queries for execution as text strings and sets parameter values as necessary.
- Receives results of each SQL statement.
If the query returns multiple rows, retrieves the results by running a cursor through the result set. Ends each transaction by committing it or rolling it back.
- Terminates the connection when it has finished interacting with the server.
The following figures list the SOLID SQL API function calls that an application makes to initialize and terminate the connection. They also show a simple sequence of SOLID SQL API function calls to execute SQL statements and to process their results. Depending on its needs, an application may also call other SQL API functions.
Error handling has been omitted from these simplified figures.
Basic application steps when using SOLID SQL API directly
initialization and termination functions.
Basic application steps when using SOLID SQL API directly
functions for processing SQL statements.
The basic application steps of processing SQL through the SOLID SQL API are the following:
- The application places the SQL text string in a buffer. If the statement includes parameter markers, it sets the parameter values.
If the statement returns a result set, the application assigns a cursor name to the statement or allows the ODBC driver to do so.
- The application submits the statement to be either prepared or executed immediately. The application can submit any SQL statement supported by SOLID Server through the SOLID SQL API.
If the execution is a repeatable one, the statement needs to be optimized first using the SQLPrepare function. Otherwise the SQL statement is executed using the SQLExecDirect function.
- If the SQL statement creates a result set, the application can inquire about the attributes of the result set, for instance the number of columns and the name and type of a specific column. It assigns storage for each column in the result set and fetches the results row by row.
- If the SQL statement was a data modification request, the application only needs to check that the execution of the submitted statement was successful. If the SQL statement causes an error, the application retrieves error information from the driver and takes appropriate action.
- If the SQL statement was a repeatable statement, it can be executed again.
- The transaction can either be committed or rolled back using the SQLTransact function.
This sequence of function calls is visualized in the figure on the previous page.
The SQL syntax used in SOLID Server is based on the ANSI X3.135-1989 level 2 standard and ANSI X3.135-1992 (SQL2) extensions. SQL2 is defined on three levels: Entry Level, Intermediate Level, and Full SQL. Some of the important SQL2 features of SOLID Server are described in this chapter.
SOLID Server supports all the data types specified in the SQL2 standard Entry Level specifications, as well as important Intermediate Level enhancements. For a complete description of the supported data types, refer to Appendix C Data Types of SOLID Server Administrator's Guide.
Some data types can also be defined with the optional length, scale, and precision parameters. In that case, the default properties of the corresponding data type are not used.
SOLID Server has native support for bi-directional scrollable cursors. Scrollable cursors are especially important when modern GUI-based applications are developed with multiple windows and lists of data.
Internal logical integrity checks on a SOLID database can be enforced by defining column specific CHECK constraints. For example:
CREATE TABLE STOCK
S_ITEM_ID INTEGER NOT NULL PRIMARY KEY,
S_AMOUNT INTEGER NOT NULL,
S_DATA VARCHAR(200),
CHECK (S_AMOUNT >= 0));
The row-value constructor provides a way to deal with an entire row of data at one time.
In SQL86 and SQL89 standards, virtually all data operations are on single values or single columns. For example, if you needed to compare all columns of a row in one table with all columns in a row of another table, you would have to write a fairly complicated expression.
In SQL2, however, you can do it very simply. For example, you might write:
WHERE (C1, C2, C3) >= (CA, CB, CC)
whereas, in SQL89, you would have needed to write:
WHERE C1 > CA or (C1 = CA AND C2 > CB) or
(C1 = CA and C2 = CB and C3 >= CC)
SOLID Server has an ODBC compliant support for the LEFT-OUTER-JOIN-syntax:
SELECT DNAME, ENAME FROM DEPT LEFT OUTER JOIN EMP
ON DEPT.DEPTNO = EMP.DEPTNO;
SOLID Server supports declarative referential integrity as described in the SQL89 standard. Using this mechanism, you can ensure the validity of references between tables. The rules for referential integrity are defined as part of the CREATE TABLE statement through primary and foreign keys.
CREATE TABLE DEPT (
DEPTNO INTEGER NOT NULL,
DNAME VARCHAR,
PRIMARY KEY(DEPTNO));
CREATE TABLE EMP (
DEPTNO INTEGER,
ENAME VARCHAR,
FOREIGN KEY (DEPTNO) REFERENCES DEPT (DEPTNO));
SOLID Server supports a set of numeric, string, time and date, system, and explicit data type conversion functions as defined in the ODBC specification.
The following example selects the names of employees who have been hired at least for five years:
SELECT EMP_NAME FROM EMPLOYEES WHERE
{fn TIMESTAMPADD(SQL_TSI_YEAR, 5, HIRE_DATE)}
<= {fn NOW()}
SOLID Server offers a number of features that make it possible to move parts of the application logic into the database. These features are described in more detail in the following paragraphs.
Stored procedures are simple programs, or procedures, that are executed in the server. The user can create a procedure that contains several SQL statements or a whole transaction and execute it with a single call statement.
The procedures in SOLID Server can take several input parameters and return a single row or several rows as a result. The result row contains the specified output parameters. The procedure calls are thus used in SOLID SQL API in the same way as the SQL SELECT statement.
Stored procedures have a number of advantages due to the nature of the three-tier architecture. They can dramatically reduce the network overhead in low bandwidth environments. In addition, they offer a higher abstraction level, better encapsulation, and allow a stricter control of user privileges on database operations.
A procedure cache is implemented into the server to keep the most frequently used procedures and related execution graphs active in the server memory cache. If the same procedure is executed repeatedly, extra performance is gained through this optimization.
Event alerts are used to signal an event in the database. Events are simple objects with a name and a set of parameters. The system does not automatically signal events typically events are signaled from stored procedures. Another stored procedure can wait until a specific event occurs and then respond to it. The use of event alerts removes resource-consuming database polling from stored procedures.
A Sequencer object is used to automatically create number sequences. Sequencers are typically used to generate unique identifiers for objects to be stored in the database, for example, order numbers.
Using a dense sequence guarantees that there are no holes in the sequence numbers. The sequence number allocation is bound to the current transaction. If the transaction rolls back, also the sequence number allocations are rolled back. The drawback of dense sequences is that the sequence is locked from other transactions until the current transaction ends.
Using a sparse sequence guarantees uniqueness of the returned values, but they are not bound to the current transaction. If a transaction allocates a sparse sequence number and later rolls back, that sequence number is simply lost forever.
The advantage of using a sequencer object instead of a separate table is that the sequencer object is specifically fine-tuned for fast execution, and it causes less overhead than normal update statements.
Copyright © 1992-1997 Solid Information Technology Ltd All rights reserved.