J2X0-1634-01EN SymfoWARE(R)Server SQL Beginner's Guide
Preface Purpose This manual is a beginner's guide for the SymfoWARE Server Structured Query Language (SQL). The purpose of this manual is to help readers write programs for a variety of different data operations using SymfoWARE/RDB databases. SymfoWARE is a database system supporting a client-server application configuration. In this manual, the SymfoWARE client function is called SymfoWARE client, and the SymfoWARE server function is called SymfoWARE server.
How to Use This Manual This manual is intended as reference material for users developing application programs using SymfoWARE/RDB. First-time users of SymfoWARE/RDB should read the RDB User's Guide: Database Definition and the RDB User's Guide: Application Program Development before reading this manual. These manuals provide readers with an overview of SymfoWARE/RDB databases and the functions of SQL statements.
SymfoWARE Programmer's Kit Position of this manual The manual system and the position of this manual are as follows: iii
SymfoWARE Server In addition to the preceding manuals, SymfoWARE/RDB provides an online manual. Displaying Command References UNIX The man command is used to display the syntax of RDB commands. For details on the man command, refer to AnswerBook2 of the Reference Manual Collection. The copyright of the online manual is the property of UNIX System Laboratories, Inc. and Fujitsu Limited.
Action in response to displayed messages UNIX The rdbprtmsg command (RDB command) gives the meaning and user response for each displayed message. Windows NT/2000/XP See the online help of Windows NT/2000/XP.
· Microsoft(R) Windows XP Professional Operating systems supporting SymfoWARE Programmer's Kit · · · · · · · · · · · · · Microsoft(R) Windows(R) 95 operating system Microsoft(R) Windows(R) 98 operating system Microsoft(R) Windows(R) 98 Second Edition Microsoft(R) Windows(R) Millennium Edition Microsoft(R) Windows XP Professional Microsoft(R) Windows XP Home Edition Microsoft(R) Windows NT(R) Server, Enterprise Edition Microsoft(R) Windows NT(R) Server network operating system Microsoft(R) Windows NT(R) Wo
July 2002 Microsoft, MS, MS-DOS, Windows, and Windows NT are trademarks or registered trademarks of Microsoft Corporation in the United States and other countries.
UNIX is a registered trademark in the United States and other countries, licensed exclusively through X/Open Company Limited. Solaris is a trademark of Sun Microsystems, Inc in the United States. Lotus is a registered trademark of Lotus Development Corporation. SymfoWARE is a registered trademark of Fujitsu Limited. Other company and product names in this manual are trademarks or registered trademarks of their respective companies. The (R) and TM symbols have been omitted in this manual.
Chapter 1 Overview of SQL This chapter describes the types of SQL and the rules common to SQL statements in this manual. This chapter contains the following sections: 1.1 Types of SQL 1.2 Common SQL Statement Rules 1.1 Types of SQL SymfoWARE/RDB uses the international standard SQL for data manipulation, which is linked to languages such as C language and COBOL. SQL is a database language used for defining and accessing data bases.
[Figure: Types of SQL] This manual explains how to use data manipulation SQL statements to write application programs for manipulating data and how to use session management SQL statements, required to manipulate data.
1.2 Common SQL Statement Rules This section explains the rules common to SQL statements in this manual. 1.2.1 Names specified in SQL statements The following names can be specified in SQL statements: a. Schema names b. Table names c. Column names d. Routine names e. Parameter names f. Trigger names g. Host identifiers names h. SQL variable names i. Descriptor names j. Connection names k. SQL server names l. Cursor names m. Correlation names n. SQL statement identifiers o.
ITMNO: This column lists the code numbers assigned to the products. PRODUCT: This column lists the product names. STOCKQTY: This column lists the quantities of stock for the products. WHCODE: This column lists the numbers of the warehouses in which the products are stored. ORDER table Figure: Inventory management data base b) shows the contents of the ORDER table. The ORDER table contains the following four columns: CUSTOMER: This column lists the customers' company numbers.
[Figure: Inventory management data base] Attributes of table columns in inventory management data base Table: Attributes of table columns in the inventory management data base lists the attributes of table columns in the inventory management data base.
[Table: Attributes of table columns in the inventory management data base] Relationship between STOCK table, ORDER table, and COMPANY table Figure: Relationship between STOCK table, ORDER table, and COMPANY table shows the relationship between the three tables. The STOCK table and the ORDER table are joined through ITMNO and PRODNO. The ORDER table and the COMPANY table are joined through CUSTOMER and COMPNO.
[Figure: Relationship between STOCK table, ORDER table, and COMPANY table] 7
8
Chapter 2 Retrieving Data This chapter describes the data manipulation that can be performed using the single row SELECT statement. This chapter contains the following sections: 2.1 Fetching Column Values without Modifications 2.2 Fetching Columns that are Undefined 2.1 Fetching Column Values without Modifications Use the single row SELECT statement to fetch one row of data from a table.
[Figure: Example of specifying multiple columns in a single row SELECT statement] If all columns in a table are to be specified in the order in which they were defined, an asterisk can be used to specify the columns instead of column names. In the following example an asterisk is used to specify columns: Example 2: In this example, an asterisk is used to fetch data from all columns in the STOCK table. SELECT * INTO :ITMNO, :PRODUCT, :STOCKQTY, :WHCODE FROM STOCKS.
application program from the database, specify in the indicator variable whether the data to be fetched contains null values. When storing data specified by the application program in a database, also specify in the indicator variable whether the data to be stored contains null values. Use an embedded SQL declaration clause to declare the indicator variable. The declaration format is the same as that for a host variable.
If fetched data has a null value and no indicator variable is specified, processing for the single row SELECT statement ends in an error. If it is not known whether values for a column contain a null value, specify indicator variables. 2.1.3 Fetching data from multiple tables A single row SELECT statement can be used to join two or more tables to fetch data. An example of joining two tables follows. This section only explains the kinds of data that can be fetched as a result of joining tables.
FROM STOCKS. STOCK WHERE ITMNO < 120 [Figure: Example of deleting duplicate rows and fetching data] DISTINCT is used to form one row from fetched rows containing equal values. DISTINCT can easily be used incorrectly if the user does not realize that multiple rows will be fetched, which will result in an error. The following is an example of using DISTINCT incorrectly: Example 2: In this example, DISTINCT is used incorrectly.
[Figure: Example of specifying DISTINCT incorrectly] 2.2 Fetching Columns that are Undefined This section explains the following operations that can be performed by the single row SELECT statement: · Performing arithmetic operations on data · Obtaining the total, average, maximum, and minimum values, and the row count for column values 2.2.1 Performing arithmetic operations on data So far, this manual has only explained the fetching of column values from tables without modification.
Dyadic operators The following are the four dyadic operators: +: Addition -: Subtraction *: Multiplication /: Division Table: Method of specifying and meaning of operational expressions shows the method of specifying, and the meaning of, operational expressions. [Table: Method of specifying and meaning of operational expressions] Specify a column name, host variable, or literal in the operator. Alternatively, the result of the operational expression can be specified as an operand.
[Table: Data type for results of dyadic operators] 2.2.2 Obtaining total, average, maximum, and minimum value, and row count for column values So far, this manual has only explained the fetching of data from required columns for one specified row from a table. The single row SELECT statement can be used to fetch the results of totaling data from multiple rows. By totaling the data, the total, average, maximum, and minimum values, and the row count for the data can be calculated.
SELECT SUM (ORDERQTY) INTO :TOTALQTY FROM STOCKS. ORDER Various set functions are available for determining values in columns. "SUM" is used to obtain the total value, "AVG" to obtain the average value, "MAX" to obtain the maximum value, "MIN" to obtain the minimum value, and "COUNT" to obtain the row count. The reader has probably noticed that no WHERE clause was specified in the single row SELECT statement in Example 1 to specify the row to be fetched.
Types of set functions and how to specify them The following types of set function exist: COUNT (*) function: Obtains the table row count including rows that contain null values AVG function: Obtains average column value MAX function: Obtains maximum column value MIN function: Obtains minimum column value SUM function: Obtains total column values COUNT function: Obtains the table row count excluding rows that contain null values Except for the COUNT (*) function, set functions can be specified in two ways.
[Table: Methods of specifying set functions] Specify value expressions in arguments of ALL and DISTINCT set functions as shown in Table: Methods of specifying set functions. An operational expression that uses a column name can be specified in an argument. Data type of results of set functions Table: Data type of columns targeted by set functions and data type of results shows the relationship between the data type of columns targeted by set functions and the data type of the results.
[Table: Data type of columns targeted by set functions and data type of the results] 20
Chapter 3 Modifying Data This chapter describes how to use the INSERT statement, UPDATE statement (searched) and DELETE statement (searched) to modify data. This chapter contains the following sections: 3.1 Adding Data to a Data Base 3.2 Updating Data in a Data Base 3.3 Deleting Data from a Data Base 3.1 Adding data to a Data Base Use the INSERT statement to add data to a table.
Therefore, if the data to be added contains a null value or some other value dependent on the processing of the application program, use an indicator variable. If an indicator variable is used, only one INSERT statement needs to be coded. However, for processing in which the data must be specified as a null value, specify the keyword "NULL" to simplify coding of the INSERT statement. Figure: Example of adding a null value shows the execution results for the INSERT statement in Examples 1 and 2.
3.1.2 Using default values in data to be added Default values, also referred to as fixed values, can be defined for each column in a table when the table is defined. The default values can be used by the INSERT statement to add data. If no value is specified for a column for which no default value is defined, a null value is added to the column. However, processing ends in an error if the column has a NOT NULL constraint.
Specifying null values for all columns in data to be added To set null values in all columns in data to be added, specify the keyword "DEFAULT VALUES" instead of specifying an insert column list or an insert value list. The following is an example in which DEFAULT VALUES is specified: Example 3: In this example, a row of data is added to the ORDER table. All the columns for the data to be added are specified with the default value defined for each column. INSERT INTO STOCKS.
PROCESS_TIME. In this example, "212" has been specified for host variable PRODNO, and "15" has been specified for host variable ORDERQTY. INSERT INTO STOCKS.DAILY_ORDER (CUSTOMER, PRODNO, ORDERQTY, PROCESS_TIME) VALUES (61, :PRODNO, :ORDERQTY, CURRENT_TIME) [Figure: Adding data using the current time] 3.1.4 Adding data from another table Depending on the processing performed by the application program, data may need to be fetched from one table and added to another.
[Figure: Using a query specification to add data (where the column layout is the same)] In Example 1, the layout of the columns in the table to which data is added and the table from which data is fetched have a one-to-one correspondence. Usually, the layout of columns in tables does not correspond so closely. For example, the data from some columns in a table may be added to another table.
[Figure: Using a query specification to add data (when column layout does not correspond)] Besides fetching specific values from a table, query specifications can also be used to join and manipulate multiple tables. Query specifications can also be used to group tables and total their values. An example of this follows. For detailed information, see Chapter 5 "Joining Multiple tables and Manipulating Data" and 6.1 "Grouping tables and Manipulating Data".
[Figure: Adding data using a complex query specification] 28
3.2 Updating data in a Data Base Use the UPDATE statement to update data in a table. The following is an example of the UPDATE statement: Example: In this example, ORDERQTY is updated to "50" for the data for PRODNO "215" and CUSTOMER "61" in the ORDER table.
Data can also be updated using a cursor. For information on this method, see 4.5 "Updating Data Using a Cursor". The UPDATE statement explained in this section is called the UPDATE statement (searched) because search conditions are used to specify rows to be updated. 3.2.1 Updating values in multiple columns In the first example in 3.2 "Updating Data in a Data Base", data was updated in column ORDERQTY only. Suppose that data in two columns, PRICE and ORDERQTY, is to be updated.
UPDATE STOCKS. ORDER SET PRICE = 216000, ORDERQTY = NULL WHERE CUSTOMER = 61 AND PRODNO = 215 Example 2: In this example, an indicator variable is used. The data to be updated is the same as in Example 1. In Example 2, "-1" is set for indicator variable ORDERQTY_INDICATOR, then the UPDATE statement is executed. A value need not be specified in host variable ORDERQTY. In this case, the data to be updated becomes a null value.
UPDATE STOCKS. ORDER SET ORDERQTY = DEFAULT WHERE PRODNO = 240 [Figure: Updating data using a default value] 3.2.4 Specifying the current date and time in data to be updated To specify the current date and time in data to be added, specify the appropriate keyword in the set clause instead of a value. To specify the current date, specify CURRENT_DATE. To specify the current time, specify CURRENT_TIME. To specify the current time-stamp, specify CURRENT_TIMESTAMP.
[Figure: Updating data using the current date and time] 3.2.5 Using values from other columns in data to be updated Values from other columns can be used to update data. An example of this follows. Suppose that a table named SHIPMT table consisting of the four columns ITMNO, SHIPQTY, PREVSHIPMT, and VARIATION exists. When products are shipped, the quantity is set in SHIPQTY and the quantity previously shipped is set in PREVSHIPMT.
[Figure: Example of using values from other columns to update data] Note that when column values are specified for data to be updated, the values used are those prior to execution of the UPDATE statement. In this example, SHIPQTY is updated to "120", as specified by the literal. However, PREVSHIPMT is updated to "100", the value of SHIPQTY prior to execution of the UPDATE statement. SHIPQTY is not "120", which is the value to be updated to by this UPDATE statement. 3.2.
3.2.7 Updating all rows in a table To update all rows in a table, do not specify any search conditions or the keyword "WHERE". Review the example in Figure: Example of using values from other columns to update data. In this example, when shipment data was produced, the values for SHIPQTY and PREVSHIPMT were updated. However, VARIATION was not specified. In the following example, VARIATION is specified to be updated to the current value.
This section explains the following operation that can be performed by the DELETE statement: · Deleting all rows from a table A cursor can also be used to delete data. For information on using a cursor to delete data, see 4.6 "Deleting Data Using a Cursor". The method of deleting rows explained in this section is called the "DELETE statement" (searched) because the rows to be deleted are specified using search conditions. 3.3.
[Figure: Example of deleting all rows from a table] 37
38
Chapter 4 Using a Cursor to Manipulate Data This chapter describes how to use a cursor to manipulate data. This chapter contains the following sections: 4.1 Cursor Overview 4.2 Declaring a Cursor 4.3 Opening and Closing a Cursor 4.4 Positioning a Cursor and Fetching Data 4.5 Updating Data Using a Cursor 4.6 Deleting Data Using a Cursor 4.1 Cursor Overview A cursor is a virtual tool used to specify one row in a table. A cursor can be used in fetching, updating, or deleting data in a row.
[Figure: Cursor overview] 40
Sequence of data manipulations using a cursor Cursor SQL statements are as follows: · · · · · · Cursor declaration (DECLARE CURSOR) OPEN statement CLOSE statement FETCH statement UPDATE statement DELETE statement The SQL statement used to declare a cursor is the cursor declaration, a non-executable statement.
declaration specifies the tables in which data is to be manipulated and the method by which data is to be fetched. Before data can be manipulated using a cursor, the cursor must be declared. The cursor declaration must be coded before any SQL statements that use the cursor are coded, regardless of the execution sequence of the application program. Data is manipulated in the following sequence using a cursor: 1. The OPEN statement opens the cursor. 2.
· Calculates the total of STOCKQTY in the rows for which PRODUCT is REFRIGERATOR and stores the value of total in variable TOTAL.
4.2 Declaring a Cursor Before data can be manipulated using a cursor, the cursor must be declared using a cursor declaration, a non-executable statement. The cursor declaration must be coded before any SQL statements that use the cursor are coded, regardless of the execution sequence of the application program. The following is an example of a cursor declaration: Example 1: In this example, a cursor to obtain CUSTOMER, PRODNO, and ORDERQTY from the ORDER table is declared.
[Figure: Deriving a cursor table] Sequence of rows fetched using a cursor Note that the sequence of rows fetched using a cursor was not specified. Although the query expression specifies the rows to be included in the cursor table, the query expression does not specify the sequence of fetching the rows. This is true even for a simple cursor that fetches all rows of a table. The rows in the cursor table shown in Figure: Deriving a cursor table are sometimes arranged in ITMNO sequence.
[Figure: Example of rows in a cursor table when the sequence is undefined] Reordering the sequence of rows A cursor declaration can specify the reordering, or sorting, of rows in a cursor table. This is done by specifying the ORDER BY clause after the query expression. The following is an example of specifying the ORDER BY clause: Example 3: In this example, the cursor declaration used in Example 2 specifies that data is fetched by the cursor in the sequence from lowest ITMNO.
Figure: Deriving a cursor table shows the sequence of rows in the cursor table if the cursor declaration is specified as in Example 3. In Figure: Deriving a cursor table, the value of ITMNO is different for each row, so the sequence of rows in the cursor table is unique. The situation when the value of ITMNO is the same for more than one row is explained further on. The sort specification determines the sequence of rows that have different values for the sort key.
In this example, the cursor declaration used in Example 2 is used. Here, data is fetched by the cursor using the number of CUSTOMERs as the first sort key. The data is sorted in descending order. The totals of STOCKQTY and ORDERQTY are used as the second sort key, and the data is sorted in ascending order. [Figure: Example of specifying column numbers in sort specification] Updatable cursors and read-only cursors Two types of cursors are available: updatable cursor and read-only cursors.
If one of conditions a) to t) applies to the format of the cursor declaration, the cursor is read-only. If none of the conditions applies, the cursor is updatable. Conditions a) to p) are specified in query specifications. Condition q) is specified in query expressions. Conditions r) to t) are specified in cursor declarations. Conditions r) and s) support updatable cursors only if FOR UPDATE is specified in the updatable clause. For more information on the subquery in condition p), see 6.
4.3 Opening and Closing a Cursor Before data can be manipulated using a cursor, the cursor must be opened using an OPEN statement. After data has been manipulated, the cursor must be closed using a CLOSE statement. Opening a cursor Use an OPEN statement to begin using a cursor. The cursor table specified in the cursor declaration is created as soon as the OPEN statement is executed.
Figure: Example of using a FETCH statement to position a cursor shows the results of executing the FETCH statement in Example 1. The cursor is positioned on the second row because the FETCH statement was executed twice.
The cursor declaration specifying the cursor name must be coded before the FETCH statement in the coding sequence for the application program. The cursor specified by the FETCH statement must be open. If a cursor that is not open is specified, an error occurs. If the FETCH statement is executed when the cursor is positioned in the last row, the result is "No data". Likewise, if the FETCH statement is executed when the cursor is positioned immediately after the last row, the result is still "No data".
d) This example fetches the value of the last row (LAST specified) regardless of the current row. The cursor is positioned to [8] in the following Figure: Example of positioning a cursor in different directions. Example: FETCH LAST FROM CSR1 INTO :H1, :H2 e) This example fetches the value of the nth row from the first or last row regardless of the current row (ABSOLUTE specified). - The value of the second row from the first row is fetched.
[Figure: Example of updating data using a cursor (column to be updated is not in the curso r table)] Data can also be updated using an UPDATE statement without using a cursor. For more information on this method, see 3.2 "Updating Data in a Data Base". Example 2: In this example, the cursor in Figure: Example of using a FETCH statement to position a cursor is moved again.
[Figure: Example of updating data using a cursor (column to be updated is not in the curso r table)] Data can also be updated using an UPDATE statement without using a cursor. For more information on this method, see 3.2 "Updating Data in a Data Base". This method of updating data using an UPDATE statement is called UPDATE statement (positioned) because the row to be updated is specified by positioning the cursor. 4.
[Figure: Example of deleting data using a cursor] Data can also be deleted using an UPDATE statement without using a cursor. For more information on this method, see 3.3 "Deleting Data from a Data Base". This method of deleting data using a DELETE statement is called DELETE statement (positioned) because the row to be deleted is specified by positioning the cursor.
Chapter 5 Joining Multiple Tables and Manipulating data This chapter describes how to join multiple tables and manipulate data when data is fetched from data bases. For example, suppose that to check on the status of orders for products, data for CUSTOMER, PRODUCT, and ORDERQTY is fetched from the stock inventory data base. This data is to be fetched from the ORDER table, but data for PRODUCT is discovered to be unavailable there. However, PRODUCT data is available in the STOCK table.
[Figure: Table derived when two table names are specified in a FROM clause] If two table names are specified in a FROM clause, the table derived from the FROM clause contains all columns and rows in the two specified tables. This is called the expanded direct product of the table. In the same way, if three or more tables names are specified in the FROM clause, the derived table contains all columns and rows in the specified tables.
[Figure: Example of specifying conditions in WHERE clause for joining tables] In Example 1, because the "COLA = COLZ" condition is specified in the WHERE clause, the only rows fetched are those in which the values of columns COLA and COLZ are the same. When multiple tables are specified in a FROM clause, columns with the same name may exist in more than one table. To specify a column name that may exist in more than one table, qualify the column name by preceding it with a table name and a period.
[Figure: Example in which multiple tables are joined and data manipulated] 60
Join tables In Example 2, all rows in the ORDER table and STOCK table are in the table derived from the WHERE clause. Some rows that are not in the ORDER or STOCK table may also be present depending on the conditions. To also fetch rows that do not satisfy the conditions, use a join table. A join table can be used to also fetch rows which do not satisfy the join specification fetch conditions, but which are in the tables specified by the join type.
fetched.
Example 3 illustrates coding RIGHT for the outer join type. If LEFT is coded for the outer join type in the same example statements, the results are as follows: 5.3 Manipulating Data Using Aliases in Tables If column names are qualified by long table names, coding columns may become time-consuming. Instead, data can be manipulated by specifying correlation names for tables in table expressions.
manipulated. In this example, the correlation names "T1" and "T2" are specified for the ORDER table and the STOCK table, respectively. One of these correlation names is used as the column name qualifier. The correlation name is valid only in the specified SQL statement. If a correlation name is specified, the correlation name must be used in the column name qualifier. The original table name cannot be used as the column name qualifier. 5.
[Figure: Example of joining a table to itself and manipulating data] 65
Operations on different rows The following is an example of performing an operation on values in different rows of the same table: Example 2: In this example, the difference in STOCKQTY for products for which ITMNO is consecutive in the STOCK table is obtained. The data to be fetched is ITMNO for both products, PRODUCT for the one with the higher ITMNO, and the difference in STOCKQTY. SELECT T2. ITMNO, T1. ITMNO, T2.PRODUCT, T2.STOCKQTY - T1.STOCKQTY AS STOCKQTYDIFER FROM STOCKS. STOCK AS T1, STOCKS.
[Figure: Example of performing an operation on different rows] 5.5 Obtaining the Aggregate for Rows from Multiple Table s The aggregate for rows contained in multiple tables can be obtained by joining two or more query specifications with "UNION". Specify the tables to be processed and the conditions for fetching data in the query specifications. In obtaining the aggregate, rows in which the column values are the same can be specified as one row.
[Figure: Example of specifying UNION in query expression] If UNION is used to join query specifications, a new table is derived corresponding to the columns in both query specification results. The column sequence is from the left. Each query specification must be specified as follows: · The number of columns in the results table must be the same as in the query specification. · The data type for columns for each corresponding results table must be comparable.
[Figure: Example of specifying UNION ALL in query specification] Aggregate sequence due to UNION Query specifications joined by UNION are processed in sequence from the left. However, parentheses can be used to specify a different evaluation sequence. A query expression enclosed in parentheses is processed before other query expressions.
70
Chapter 6 Methods of Manipulating Data This chapter describes methods of using SQL statements to manipulate data. This chapter contains the following sections: 6.1 Grouping Tables and Manipulating Data 6.2 Specifying Various Search Conditions 6.3 Manipulating Numeric Data 6.4 Manipulating Character String Data 6.5 Manipulating Date Data 6.6 Converting the Data Type to Manipulate Data 6.7 Using CASE Expression to Manipulate Data 6.8 Specifying Row IDs to Manipulate Data 6.
[Figure: Example of a table derived from GROUP BY clause] A collection of rows derived from the FROM clause and WHERE clause in the table expression are grouped together. In this example, the result of the FROM clause is equivalent to the STOCK table because only the STOCK table is specified in the FROM clause in the table expression. Also, because the WHERE clause is omitted, all rows are grouped.
Fetching data from a grouped table A query specification is used to derive a table with which data is to be manipulated. If a query specification is used to derive a table from a grouped table, the table is made up of a row for each group. In a query specification for which a GROUP BY clause is specified, a grouped column, a grouped function, or a set function can be specified in the select column list.
[Figure: Example of fetching data by grouping a table] The reader probably already noticed in Figure: Example of a table derived from GROUP BY clause that the result of specifying only a grouped column in the select column list is the same as specifying "DISTINCT" in the query specification. In Example 2, only PRODUCT was specified in the select column list, so PRODUCT was obtained with duplicate rows deleted.
[Figure: Example of using a set function incorrectly] In Example 3, the row count for the result is one row for the total of STOCKQTY, but n rows for PRODUCT. Therefore, this query specification results in an error. Grouping tables by using parts of character strings Methods of grouping table data in columns have been explained so far. The following explains a method of grouping tables by using parts of character strings. Specify a character substring function in the GROUP BY clause.
[Figure: Example of grouping tables by using parts of character strings] Case-splitting data and grouping tables Specify a CASE expression in the GROUP BY clause to split column data into cases instead of columns, and to group tables for each value split into cases. An example of specification is shown below. For details on the CASE expression, refer to "6.7 Using Case Expression to Manipulate Data.
[Figure: Case-splitting data and grouping tables] Grouping tables by month To group tables of a DATE-type column by year or month, specify the date-time value function. A specification example is shown below. For details on the date-time value function, see "6.5 Manipulating Date Data." Example 6 The sales dates in the RETAILSALESHIST table are grouped by month, and the total proceeds are fetched.
[Figure: Example of grouping tables by month] 6.1.2 Specifying groups to be processed from a grouped table The WHERE clause can be used in a table expression to specify rows to be processed in the table specified in the FROM clause. To do this, specifying the groups to be processed in a table grouped by the GROUP BY clause, using a HAVING clause.
Figure: Example of a table derived from a HAVING clause shows the table derived from the HAVING clause and the query specification result.
[Figure: Example of a table derived from a HAVING clause] HAVING clause Specify the search conditions for the groups to be processed in the HAVING clause. The columns used as conditions must either be grouped columns or must be able to be specified by a set function. If a column other than a grouped column is specified by other than a set function, an error occurs. The WHERE clause can also specify search conditions for grouped columns.
specifying search conditions in the WHERE clause improves the processing efficiency of SQL statements. For example, to derive rows in which PRODUCT is TELEVISION, specify PRODUCT = 'TELEVISION' as the condition in the WHERE clause of the query specification in the previous example. 6.1.3 Grouping multiple tables that are joined and manipulating data Specifying multiple tables in the FROM clause to join tables and manipulate data has already been explained.
82
A common error with this method of manipulating data is that the user may mistakenly believe that obtaining the totals of STOCKQTY and ORDERQTY for each ITMNO will provide the same result as in Example 1. The following is an example of using this method incorrectly: Example 2: In this example, the data manipulation used in Example 1 is incorrectly specified. Figure: Example of a common error when grouping multiple joined tables shows the result of the execution of this query specification.
[Figure: Example of a common error when grouping multiple joined tables] The MAX function or MIN function can be used to correct the previous error, as in the following example: Example 3: In this example, the error in Example 2 is corrected. Because the value of STOCKQTY is the same in each group, both the maximum and minimum values for each group can only be one value, the STOCKQTY value.
6.2 Specifying Various Search Conditions Search conditions can be specified in SQL statements to specify the manipulation of rows that satisfy the search condition. This section explains how to specify search conditions that use a single predicate and predicates combined by logical operators ("AND", "OR", or "NOT"). The result of a predicate or combined predicate is either "true", "false", or "undefined". If the result is "true", the search condition is satisfied.
STOCK table for which ITMNO is 100 or greater or WHCODE is 1 are fetched. Example 3: In this example, the logical operator "NOT" is specified in the search condition. The rows from the STOCK table for all items except TELEVISION are fetched. Table: Truth table for AND lists the result of specifying AND between predicates. Table: Truth table for OR lists the result of specifying OR between predicates. Table: Truth table for NOT lists the result of specifying NOT before the predicate.
[Table: Truth table for NOT] Evaluation sequence of logical operators Multiple logical operators can be specified in a search condition. In this case, logical operators are evaluated in the following sequence: 1. NOT 2. AND 3. OR However, parentheses can be used to in the specification of the evaluation sequence. A condition enclosed in parentheses is evaluated before other conditions. 6.2.
[Table: Comparison operators] The data types of the left and right value expressions of a comparison operator must be comparable. Table: Comparable data types lists comparable data types. For more information on data types, see Appendix A "SQL Data Types and Equivalent Host Variable Data Types".
[Table: Comparable data types] The following is an example of specifying a comparison predicate: Example 1: 89
In this example, rows for which ITMNO is 123 are fetched from the STOCK table. Example 2: In this example, rows for which STOCKQTY is less than 50 are fetched from the STOCK table. SELECT ... FROM STOCKS. STOCK WHERE STOCKQTY < 50 Example 3: In this example, rows for which "PRICE x ORDERQTY" is 1,000,000 or less are fetched from the ORDER table. SELECT ... FROM STOCKS.
WHERE PRODUCT = 'TELEVISION' AND STOCKQTY >= 90 Example 8: In this example, rows for which STOCKQTY is 10 or greater and PRODUCT is VIDEO CASSETTE PLAYER, and rows for which STOCKQTY is 200 or greater and PRODUCT is TELEVISION are fetched from the STOCK table. SELECT ... FROM STOCKS.
[Figure: Example of specifying a subquery in a comparison predicate to retrieve data] Example 11: In this example, the maximum value for ITMNO is fetched from the STOCK table, and the PRODNO rows for that value are deleted from the ORDER table. DELETE FROM STOCKS.ORDER WHERE PRODNO = (SELECT MAX (ITMNO) FROM STOCKS.
[Figure: Example of specifying a subquery in a comparison predicate to delete data] Checking whether a value is a null value Use the NULL predicate to check whether a column value is a null value. The following are examples of specifying the NULL predicate: Example 12: In this example, rows for which WHCODE is a null value are fetched from the STOCK table. Example 13: In this example, rows for which WHCODE is not a null value are fetched from the STOCK table.
6.2.2 Checking whether a value is in a certain range Use the BETWEEN predicate to check whether a value is in a certain range. The following are examples of specifying the BETWEEN predicate: Example 1: In this example, rows for which STOCKQTY is greater than or equal to 50 but less than or equal to 100 are fetched from the STOCK table.
A comparison predicate can also be used to check whether a value is in a certain range. In the following example, the specified comparison predicate has the same effect as the BETWEEN predicate in Example 1: Example 4: In this example, rows for which STOCKQTY is greater than or equal to 50 but less than or equal to 100 are fetched from the STOCK table. 6.2.
[Figure: Example of specifying IN predicate] The values to be compared in the value expression are enclosed in parentheses and specified in a quantified value list. Use host variables or literals to specify values in a quantified value list. Column names cannot be specified. The result of the IN predicate is true if the value for the value expression is equal to one of the values enclosed in parentheses.
[Figure: Example of using a subquery in the IN predicate] Comparing a set of values Use a quantified predicate as a search condition to specify comparison with a set of quantified values. Use a comparison operator and quantifier (ALL, SOME, or ANY) in specifying the quantified predicate. The format of the subquery specification in parentheses is the same as that for a query specification. However, a table derived from a subquery can only have one column.
[Figure: Example of specifying a quantified predicate] 98
Checking whether rows that satisfy certain conditions exist The EXISTS predicate can be specified in a subquery. Whether rows are specified depends on whether or not the result of the subquery is null. In other words, the EXISTS predicate is used when the row count for the table derived from the result of the subquery can be 0. If the result of the subquery specified after EXISTS is not null, the EXISTS predicate is true. If the result of the subquery is null, the EXISTS predicate is false.
[Figure: Example of specifying the EXISTS predicate] 6.2.4 Checking whether values match a character-string pattern Use the LIKE predicate to check whether data in a character-string type or national-language character-string type matches a certain character-string pattern. The result of the LIKE predicate is true if the specified pattern is contained in a column. The result is false if the pattern is not contained in any column.
[Figure: Using the LIKE predicate to specify rows] Use a character string, arbitrary string specifier, or arbitrary character specifier to represent the pattern. Table: Arbitrary string specifier and arbitrary character specifier explains the arbitrary string specifier and the arbitrary character specifier. When the pattern is a character string, use alphabetic characters to specify the pattern. When the pattern is in a national language, use national-language characters to specify the pattern.
Rows on which the value in column COL1 in table TBL5 is "ABC" are specified. SELECT ... FROM SCM.TBL5 WHERE COL1 LIKE 'ABC' Example 2: In this example, rows for which the value in column COL1 in table TBL5 begins with "ABC" are specified. SELECT ... FROM SCM.TBL5 WHERE COL1 LIKE 'ABC%' Example 3: Rows on which the value in column COL1 in table TBL5 begins with "ABC" are specified. SELECT ... FROM SCM.
[Figure: Example of LIKE predicate specification (using an arbitrary string specifier)] Examples of using arbitrary string specifier "_" Examples of using arbitrary string specifier "_" are shown below. The figure with the example of "Figure: LIKE predicate specification (using an arbitrary string specifier)" shows which row is specified. Example 1: Rows of four characters for which the value in column COL1 in table TBL5 begins with "ABC" and ends with any character are specified. SELECT ... FROM SCM.
Rows of four characters for which the value in column COL1 in TBL5 begins with any character followed by "A" and ends with "BC". SELECT ... FROM SCM.TBL5 WHERE COL1 LIKE 'A_BC' [Figure: Example of LIKE predicate specification (using an arbitrary string specifier) ] Escape characters In a pattern specification in the LIKE predicate, the percent and underscore signs specify the location of the arbitrary characters.
Example 8: In this example, rows for which the value in column COL2 in table TBL5 begins with any two characters and ends with "_ _ABC_ _" are specified. [Figure: Example of specifying a LIKE predicate specification using escape characters] 6.3 Manipulating Numeric Data Users can manipulate numeric data to obtain absolute values, minimum integer values, maximum integer values, and other values. The table below lists the processing used to manipulate numeric data.
For products with a high variation of shipment quantity (assuming that the difference between the preceding and current shipment quantities is 10 or greater), the corresponding product numbers are obtained from the SHIPTBL table. Example 2: The maximum and minimum temperatures in each region are rounded down to the next integers.
Example 3: The maximum temperature in each region is rounded off to two decimal places. 6.4 Manipulating Character String Data When manipuling table data, users can manipulate and join character strings. The table below lists the processing for manipulating parts of character strings.
[Table: Processing for manipulating parts of character strings] Example 1: Company names and addresses are fetched from the company table. For each company name, a null character at the end is removed, "Co., Ltd.," is concatenated, and then only the metropolis and district names are fetched from the address. Users can fetch a part of a specific character string and join a value to a character string in a specific column.
[Figure: Example of combining character value functions to fetch data] Example 2: Codes and office names are fetched from the office list table, and character string "OFFICE" is removed from each office name.
[Figure: Example of combining a character substring function with a numeric function to fetc h data] Example 3: The last three characters are fetched from the product name character string in 2NDTBL. [Figure: Example of combining a character substring function with a numeric function to upd ate data] Example 4: An asterisk "*" is embedded on the left end of each branch name. The entire length is five characters.
Example 5: Character string "VIDEO" in COL2 of table 2NDTBL is replaced with "VIDEOTAPE". 6.5 Manipulating Date Data Users can manipulate table data to calculate dates and times, convert date-time value expression data to characters, and convert character-type data to DATE-type data. The table below lists the processing for manipulating date data.
[Table: Processing for manipulating date data] Example 1: The order quantity for each quarter is obtained from the order table. By using the TRUNC_DATE function, the date can be truncated for each year (January 1 on the year), month (1st day of the month), or quarter.
Example 2: The number of days that have been elapsed from the order date to the current date is obtained from the ORDERTBL table. The current date is 2000-08-10. By using the SPAN_DATE function, the difference (time interval) between dates can be obtained, where YEAR, MONTH, or DAY is used as the unit for the time interval. Users can also obtain the number of times the year, month, or day changes between dates.
Example 3: For orders that are one month old or older in the ORDERTBL table, the corresponding order numbers are obtained from the order table according to the specified order day. The current date is 2000-08-01. By using the ADD_DATE function, dates can be added, where YEAR, MONTH, or DAY is used as the unit of addition for the time interval. The added number can be defined as a number of years, months, or days. Example 4: A number of days ranging from the order day until the end of month is obtained.
The last date of the month can be obtained using the LAST_DAY function. Example 5: The maximum temperature date data in the WEATHERMNTBL table is converted to a character string in the "YYYY.MM.DD DY" format. The date type can be converted to the character type using the CNV_CHAR function. The conversion is performed in the date format.
Example 6: The minimum temperature date in the WEATHERMNTBL table is converted to character-type data in the date format. The date format "DAY MONTH DD, YYYY" is assumed to be specified in CHAR-type host variable :DAYFMT. Example 7: The date characters in MINTEMPDAYCHARTBL are converted to a date. The character-type data defined in the date format can be converted to date-time-type data using the CNV_DATE function.
6.6 Converting the Data Type to Manipulate Data The data types of columns can be converted to execute date or time operations using character strings and to add or update data in time type or time interval type columns. An example of a date operation executed by converting the character string data type to the time interval type (year and month) is given below. Example 1: In this example, codes and dates obtained by adding six months to the process date are fetched from the OFFICE table.
[Figure: Converting data from character string type to time interval type (year and month)] An example of a time operation executed by converting the character string data type to the time interval type (date and time) is given below. Example 2: In this example, the process time is updated to a value reduced by 1 hour and 30 minutes for data in the DAILY_ORDER table whose ORDERQTY is less than 10. Also, "1:30" has been specified for character string type host variable OTIME. UPDATE STOCKS.
[Figure: Converting data from numeric type to character string type] 6.7 Using CASE Expression to Manipulate Data The CASE expression is used to change the value of a results column depending on the column value, arrange fetched columns in a sequence based on priority level, and split data to cases to specify an appropriate value to each case. Examples of using the CASE expression are as follows. Example 1: The number of services used for each user is obtained from history table HISTORYTBL.
Example 3: The first setting items are obtained from the user table in the sequence of e-mail address, phone, and address. Example 4: The name of users and points of the users who reach the next rank by 500 points are obtained from the POINTTBL table. The number of points of each rank are as follows: SILVER is 3,000 points; GOLD is 10,000 points; and PLATINUM is 50,000 points.
Example 5: The services used are grouped, and the total number of services used for each group is obtained, from history table HISTORYTBL. Example 6: The service symbols listed in history table HISTORYTBL are changed.
6.8 Specifying Row IDs to Manipulate Data A row ID (ROW_ID) recognizes a unique row of a table in the database. Use a single row SELECT statement or FETCH statement to fetch a row ID. The host variable for receiving the row ID must correspond to the ROW_ID. For the variable definition corresponding to each data type, see Table "SQL data types and equivalent C language host variable data types" and Table "SQL date types and equivalent data types for COBOL host variables".
123
6.10 Omitting Schema Names In the examples of SQL statements provided so far, table names have been qualified with a schema name, such as in STOCKS.STOCK. This section explains how to use a table declaration in which the schema name qualifier is not specified. Specify the table name used by the application program and the name of the schema in which the table is located in the table declaration.
6.11 Changing the User of the Current Session When an application program accesses a database, the user of the current session becomes the person connected to the database. This user can be changed in the application program. Example: In this example, the user of the current session is changed to USER2. 6.12 Adding Name to Results Column in Query Specificati on If a function is specified in the select column list of the query specification, no name is added to the derived columns.
6.13 Manipulating Data Using Sequence CURRVAL and NEXTVAL can be used to fetch the created sequence number. CURRVAL fetches the current sequence number in the application program. NEXTVAL fetches the next value after the last sequence number fetched in the RDB system.
This example shows the method for specifying a sequence in the SQL statement of an application program. In the example, employee numbers (EMPLOYEENO) are incremented and assigned to new employees. Example 2: This example shows the method for automatically inserting a sequence number in an insertion line using a sequence. In the example, new order numbers (ORDERNO) are assigned by incrementing order numbers (ORDERNO) in the order table (ORDERTBL).
Example 3: This example shows the method for referencing the sequence numbers for stock control (STOCKCTL) and sequence 1 (SEQ-1) by specifying CURRVAL and NEXTVAL.
129
130
Chapter 7 Executing Dynamic SQL Statements When Application Programs are Executed This chapter describes the functions and uses of dynamic SQL. This chapter consists of the following sections: 7.1 Overview of Dynamic SQL 7.2 Dynamically Modifying and Executing SQL Statements 7.3 Dynamically Modifying and Executing SQL Statement Conditions 7.4 Immediately Executing SQL Statements 7.5 Changing Database Names and Omitted Schema Names 7.
SQL descriptor area An SQL statement may be dynamically modified to manipulate data. In other words, the SQL statement may be entered from outside the application program. In this case, however, the type of SQL statement to be entered cannot be determined when the application program is created. For example, a variable used to fetch data from a data base must normally be declared in the application program. However, if the type of SQL statements to be entered is not known, the variable cannot be declared.
The SET USER PASSWORD statement (user control statement) and SET ROLE statement (access control statement) cannot be executed as preparable statements. The syntax of the multiple row SELECT statement is the same as that of the cursor specification. To execute these statements, the statements must be prepared for execution. These statements then become prepared statements. The following are examples of preparable statements: Example 1: In this example, a single row SELECT statement is specified.
SET STOCKQTY = 0, WHCODE = ? WHERE ITMNO = 111 Example 6: In this example, the dynamic parameter specification is specified in the insert value list of an INSERT statement. INSERT INTO STOCKS. STOCK (ITMNO, PRODUCT, STOCKQTY) VALUES(?, ?, ?) USING clause Use the USING clause to set values in the dynamic parameter specification of a prepared statement and to fetch the execution results.
figure below. The SQLDA structure provides information about the target specification and dynamic parameter specifications. The SQLDA structure consists of SQLVAR (item descriptor area), SQLN (maximum number of SQLVAR elements), and SQLD (number of valid elements). The format of the SQLDA structure is as follows.
SQLN SQLN indicates the maximum number of the dynamic parameter specifications that can be written or the maximum number of the select column list items that can be written. SQLN therefore determines the maximum number of elements of array SQLVAR. SQLD SQLD determines the number of valid elements of array SQLVAR. Executing a output DESCRIBE statement specifying an SQLDA structure when the prepared statement contains select column list items returns the number of select column list items to SQLD.
If the TYPE value is 9, indicating the datetime type, any of the following DATE_TIME_INTERVAL_CODE values is set in SQLSCALE: If the TYPE value is 10, indicating the interval type, any of the following DATETIME_INTERVAL_CODE values is stored in SQLSCALE: 137
Procedure for fetching execution results The order in which execution results are fetched using the USING descriptor is shown in "Figure: Procedure for fetching execution results for a prepared statement." The flow of fetching execution results is explained here. Each SQL statement is explained after "Figure: Procedure for fetching execution results for a prepared statement.
[Figure: Procedure for fetching execution results for a prepared statement] When modifying an SQL statement dynamically to manipulate data, prepare execution of the SQL statement. Preparing the SQL statement Use the PREPARE statement to prepare a dynamic SQL statement for execution. To create and execute an SQL statement when the program is executed, store the SQL statement as a character string in a host variable called the SQL statement variable.
references the cursor corresponding to the SQL statement identifier is also deallocated. However, if the prepared statement is a dynamic SELECT statement, the cursor corresponding to the SQL statement identifier must have already been closed. An example of specifying the PREPARE statement is shown below. The SQL statement to be prepared for execution is assumed to be stored in SQL statement variable str. The SQL statement identifier of this SQL statement is defined as STM.
7.2.1.1 Preparing and executing dynamic SELECT statements (for SQLDA stru cture) To fetch data continuously, prepare and execute a dynamic SELECT statement. An example of an application program that executes a dynamic SELECT statement using the SQLDA structure is shown in "Figure: Example of application program that executes a dynamic SELECT statement." Example: This is an example of a dynamic SELECT statement entered from a terminal.
[Figure: Example of application program that executes a dynamic SELECT statement] 142
143
144
7.2.1.2 Preparing and executing dynamic single row SELECT statements (for SQLDA structure) To fetch data from one row, prepare and execute a dynamic single row SELECT statement. To execute a dynamic single row SELECT statement, use an EXECUTE statement in which the USING clause is specified. An example of a dynamic single row SELECT statement using the SQLDA structure is shown below.
statements that can use the SQL structure, see the examples in "Figure: Procedure for fetching execution results for a prepared statement." Example: This is an example of a dynamic single row SELECT statement entered from a terminal. 7.2.2 Preparing SQL statements and manipulating the SQL descriptor area When data is fetched from a database using dynamic SQL, specify the target that will receive the execution results.
The SQL descriptor area either consists of only one identifier (COUNT) or consists of one identifier (COUNT) and one or more item descriptor areas (elements of the SQL descriptor area). COUNT indicates the number of select column lists in the SQL descriptor area; the data type is an exact numeric with binary precision. In the following figure, n indicates the number of select column lists in the SQL descriptor area. Each item descriptor area consists of the items listed in the table below.
If the TYPE value is 1 or 12, the CHARACTER_SET_NAME value (character set name) varies according to character string type and national character string type. · Character string type: BASIC · National character string type: NCHAR If the TYPE value is 9, indicating the datetime type, the DATETIME_INTERVAL_CODE value is any of the codes listed in the table below. If the TYPE value is 10, indicating the interval type, the DATETIME_INTERVAL_CODE value is any of the following codes listed in the table below.
Procedure for fetching execution results The order in which execution results are fetched using the USING descriptor is shown in "Figure: Procedure for fetching execution results for a prepared statement." The flow of fetching execution results is explained here. Each SQL statement is explained after "Figure: Procedure for fetching execution results for a prepared statement.
[Figure: Procedure for fetching execution results for a prepared statement] When modifying an SQL statement dynamically to manipulate data, prepare execution of the SQL statement. Preparing the SQL statement Use the PREPARE statement to prepare a dynamic SQL statement for execution. To create and execute an SQL statement when the program is executed, store the SQL statement as a character string in a host variable called the SQL statement variable.
references the cursor corresponding to the SQL statement identifier is also deallocated. However, if the prepared statement is a dynamic SELECT statement, the cursor corresponding to the SQL statement identifier must have already been closed. An example of specifying the PREPARE statement is shown below. The SQL statement to be prepared for execution is assumed to be stored in SQL statement variable str. The SQL statement identifier of this SQL statement is defined as STM.
specified in a dynamic OPEN statement, the cursor must have already been closed. An example of deallocating the SQL descriptor area with descriptor name DESC1 is shown below. DESCRIBE statement The DESCRIBE statement fetches select column list information for the prepared statement prepared by the PREPARE statement to the SQL descriptor area. This type of statement is called an output DESCRIBE statement.
GET DESCRIPTOR statement The GET DESCRIPTOR statement fetches the information set in the SQL descriptor area to a host variable. The data type of the host variable must match data type of the corresponding fetch identifier. An example of fetching the number of select column lists to host variable varcount is shown below. The following is an example of fetching the contents of the select column lists set in the SQL descriptor area to host variables vartypc, varleng, and varoctct.
7.2.2.1 Preparing and executing dynamic SELECT statements (for SQL descri ptor area) To fetch data continuously, prepare and execute a dynamic SELECT statement. An example of an application program that executes a dynamic SELECT statement using the SQL descriptor area is shown in "Figure: Example of application program that executes a dynamic SELECT statement." Example: This is an example of a dynamic SELECT statement entered from a terminal.
[Figure: Example of application program that executes a dynamic SELECT statement] 155
156
157
7.2.2.2 Preparing and executing a dynamic single row SELECT statement (for SQL descriptor area) To fetch data from one row, prepare and execute a dynamic single row SELECT statement. To execute a dynamic single row SELECT statement, use an EXECUTE statement in which the USING clause is specified. An example of a dynamic single row SELECT statement using the SQL descriptor area is shown below.
7.3 Dynamically Modifying and Executing SQL Statement Conditions This section describes the following methods that can be used to dynamically modify and execute SQL statement conditions.
The SQLDA structure holds dynamic parameter specification information. The SQLDA structure consists of item descriptor area SQLVAR, the maximum number of SQLVAR elements SQLN, and the number of effective elements SQLD. Item descriptor area SQLVAR consists of arrays in which dynamic parameter specification information is stored. SQLD indicates the number of dynamic parameter specifications in the SQLDA structure. The data type is exact numeric with binary precision.
[Figure: Procedure for setting dynamic parameter specification values for prepared statement s] SQL statements used to manipulate the SQLDA structure As shown in "Figure: Procedure for setting dynamic parameter specification values for prepared statements," use SQL statements to fetch dynamic parameter specification information from the database to the SQLDA structure and to get dynamic parameter specification information from the SQLDA structure.
When the prepared statement corresponding to SQL statement identifier STM1 is as follows, the SQLDA structure variable will have the following contents: This section describes only the SQL statements used to manipulate a SQLDA structure containing dynamic parameter specification information. For information about other SQL statements, see "7.2.1 Preparing SQL statements and manipulating the SQLDA structure." 7.3.1.
[Figure: Example of an application program that uses the SQLDA structure (with dynamic pa rameter specification)] 163
164
165
166
167
7.3.1.2 Preparing and executing dynamic single-row SELECT statements (for SQLDA structure) To fetch single-row data, prepare and execute a dynamic single-row SELECT statement.
single-row SELECT statement, use the EXECUTE statement with the USING clause specified. An example of using the SQLDA structure to specify a dynamic single-row SELECT statement with the dynamic parameter specification is shown below. For information about the SQL statements used to manipulate the SQL structure, see "Figure: Procedure for setting dynamic parameter specification values for prepared statements." Example: This is an example of a dynamic single-row SELECT statement entered from a terminal.
information must match the host variable attributes. Matching is accomplished by fetching dynamic parameter specification information from the database to the SQL descriptor area, modifying the attributes, and then specifying the value. The following figure shows the position of the SQL descriptor area. The SQL descriptor area either consists of only one identifier (COUNT) or consists of one identifier (COUNT) and one or more item descriptor areas (elements of the SQL descriptor area).
[Figure: Procedure for setting dynamic parameter specification values for prepared statement s] SQL statements used to manipulate the SQL descriptor area As shown in "Figure: Procedure for setting dynamic parameter specification values for prepared statements," use SQL statements to fetch dynamic parameter specification information from the database to the SQL descriptor area and get dynamic parameter specification information placed in the SQL descriptor area.
When the prepared statement corresponding to SQL statement identifier STM1 is as follows, SQL descriptor area DESC1 will have the following contents: GET DESCRIPTOR statement The GET DESCRIPTOR statement fetches information set in the SQL descriptor area to the host variable. The data type of the host variable must match the data type of the corresponding get identifier. An example of fetching the number of dynamic parameter specifications to host variable varcount is shown below.
SET DESCRIPTOR statement The SET DESCRIPTOR statement sets the data type and value of the dynamic parameter specification in the SQL descriptor area. Specify them using the constant or host variable. The data type of the host variable must match the data type of each set identifier. When the descriptor name specified in the SET DESCRIPTOR statement is specified in a dynamic OPEN statement, the cursor must be closed.
[Figure: Example of an application program that uses the SQL descriptor area (with dynamic parameter specification)] 174
175
176
177
178
179
180
181
7.3.2.2 Preparing and executing dynamic single-row SELECT statements (for SQL descriptor area) To fetch single-row data, prepare and execute a dynamic single-row SELECT statement. To execute a dynamic single-row SELECT statement, use the EXECUTE statement with the USING clause specified.
is shown below. For information about SQL statements used to manipulate the SQL descriptor area, see "Figure: Procedure for setting dynamic parameter specification values for prepared statements." Example: This is an example of a dynamic single-row SELECT statement entered from a terminal. Data is searched using the value of the dynamic parameter specification entered from the terminal as the search condition. 7.3.
Example: This is an example of an UPDATE statement (searched) entered from a terminal. Data for the values of the dynamic parameter specifications entered from the terminal is updated as a set clause. 7.3.4 Executing prepared statements for which variable attributes are known When an application program is created, the attributes and the number of dynamic parameter specifications for prepared statements, or the attributes and the number of select column lists to be fetched may be known.
Using the EXECUTE statement to fetch execution results Specify USING arguments in the EXECUTE statement to fetch execution results. Example 2: In this example, the values of PRODUCT and STOCKQTY for the product with ITMNO "110" are fetched from the STOCK table. The data types for PRODUCT and STOCKQTY and those for the two arguments in which the results are fetched must be comparable.
Using the dynamic FETCH statement to fetch execution results Specify USING arguments in the dynamic FETCH statement to fetch execution results. Example 4: In this example, values of PRODUCT and STOCKQTY with ITMNO greater than "200" are fetched from the STOCK table. The data types of ITMNO and STOCKQTY and those of the two arguments in which the results are fetched must be comparable.
Figure: Example of an application program that uses the USING argument is an example of an application program that uses the USING argument. Example 6: This is an example of a dynamic SELECT statement that has two dynamic parameter specifications. Values for ITMNO and STOCKQTY entered from a terminal are used as search conditions to fetch data. The rows that satisfy the search conditions are updated to the value of WHCODE entered from the terminal.
[Figure: Example of an application program that uses the USING argument] 188
7.4 Immediately Executing SQL Statements If an SQL statement is to be executed without being prepared, use the EXECUTE IMMEDIATE statement. The SQL statements that can be executed using an EXECUTE IMMEDIATE statement are preparable statements other than the dynamic SELECT statement and the single row SELECT statement. For details on preparable statements, see 7.
"Overview of Dynamic SQL". When the preparable statement is the INSERT statement, UPDATE statement (searched), DELETE statement (searched), UPDATE statement (positioned), or DELETE statement (positioned), the dynamic parameter specification cannot be specified. If an SQL statement variable contains an UPDATE statement (positioned) or a DELETE statement (positioned), the corresponding cursor must be opened beforehand. Also, the specified cursor must be a dynamic cursor.
Example: In this example, the schema name is changed to "SCH1".
192
Appendix A SQL Data types and equivalent Host Variable Data Types Table: SQL data types and equivalent C language host variable data types lists SQL data types and the equivalent C language host variable data types.
[Table: SQL data types and equivalent C language host variable data types] 194
[Table: Time interval types and equivalent data types for C language host variables] Table: SQL data types and equivalent data types for COBOL host variables lists the data types and the equivalent data types for COBOL host variables.
[Table: SQL data types and equivalent data types for COBOL host variables] Table: Time interval types and equivalent data types for COBOL host variables shows the time interval types and equivalent data types for COBOL host variables.
[Table: Time interval types and equivalent data types for COBOL host variables] 197
198
Appendix B Handling RDB Messages User handling of some messages can be referenced online. These messages are issued when RDB commands are being executed or an application program is being compiled. These messages also include messages set in a message variable (SQLMSG) of an application program. Use the online manual SymfoWARE/RDB Message Reference to reference user handling of messages issued by the SymfoWARE/RDB. This appendix describes reference methods for each task.
When executing an application program Example 4: Display a description of message JYP2031E set in the message variable (SQLMSG) when executing an application program.
Appendix C SQLSTATE Values The system reports the execution results of SQL statements in status variable SQLSTATE while an application program is running. The application program checks SQLSTATE whenever an SQL statement is to be executed, and processes SQL information while checking the result of SymfoWARE/RDB processing. SQLSTATE Status variable SQLSTATE is a 5-digit character-type variable. The value set in SQLSTATE, the result of an SQL statement being executed, is known as the status code.
202
203
204
205
206
Glossary ALL set function The ALL set function is a set function for which ALL is specified in the argument. Rows containing the same value are targeted by the ALL set function. Related term: DISTINCT set function Application program In general, programs used in the work of a computer user are called application programs. In this manual, a program that reads data from a database and writes data to a database for user work processing is called an application program.
Column A constituent element of a table. A relational database represents data using two-dimensional tables consisting of rows and columns. Column name (item name) A column name is the name assigned to a column, defined in the schema definition. The column name is used in SQL statements for data manipulation to specify a column to be processed. Commit Makes the data manipulation of a transaction being processed take effect. The data manipulation in the transaction is physically reflected in the database.
Database name Many databases can be created as units of administration and design on one server system. To identify each database uniquely, each is assigned a unique name (database name) on the server system. DEFAULT clause An element of the definition of a column in a table. If the following condition applies, the value defined in the DEFAULT clause is inserted. The data to be inserted in a column is not specified when a row is inserted in a table using the INSERT statement.
Escape character An escape character is an alternate character for underscore (_) of an arbitrary character specifier or for percent symbol (%)of an arbitrary string specifier. An escape character is specified in a LIKE predicate. A LIKE predicate is used to specify a retrieval condition for data manipulation. However, to retrieve the actual underscore or percent symbol, an alternate character, the escape character, must be defined as the arbitrary character specifier for these symbols.
IN predicate In the search condition specification for an SQL statement, the IN predicate specifies that rows are manipulated depending on comparisons with a set of values. The IN predicate is the predicate in IN (C, D, ...) or IN subquery. Related term: predicate Indicator variable In high-level languages such as C language and COBOL, the indicator variable is a variable specified to be paired with an SQL data variable.
Non-cursor SQL statement An SQL statement used for data manipulation, the non-cursor SQL statement does not use a cursor to specify rows to be manipulated. Instead, the rows to be processed are specified in the search condition specified in the SQL statement. NOT NULL constraint The NOT NULL constraint is a constraint that can be applied to columns of tables. The NOT NULL constraint means that the value in a row of a column of a table cannot be a NULL value.
types of predicate are available: comparison, BETWEEN, IN, LIKE, NULL, quantified, and EXISTS. Search conditions are specified using a predicate and Boolean operators. Procedure routine A procedure routine defines processing procedures for a database using SQL. Procedure routine definition A procedure routine definition defines a procedure routine using a logical structure definition.
Relation operation With relational databases, a relation operation allows only data from specified columns or a collection of data that meets certain conditions to be fetched. Three types of relation operations are available: selection, projection, and join. Selection refers to fetching rows that match specified conditions from a table. Projection refers to fetching only specified columns from a table. Join refers to specifying join conditions to create one table from two or more tables.
Search condition The specification for identifying rows that are the subject of the operation in data manipulation SQL. For example, a search condition is specified in the WHERE clause of an SQL SELECT statement. Select column list SQL statement query specifications and single row SELECT statements specify columns to be targeted for data reference from the table specified in the table expression. The columns to be targeted are specified in a select column list.
Single row SELECT statement The single row SELECT statement is an SQL data manipulation statement used to reference data. The single row SELECT statement can specify a search condition and fetch one row of data from a table. Sort specification When an SQL statement for manipulating data gets the results of a query expression using a cursor, it can rearrange the order of the rows. A sort specification specifies the column that is the sort key and ascending (ASC) or descending (DESC) order.
an SQL statement is executed, the status code for the execution result is stored in the status variable. Storage structure A database structure along with logical structure and physical structure. Storage structure physically locates data logically expressed as rows and columns of tables in a database as storage data. A storage structure is expressed using DSO and DSI.
Target specification A target specification is used to fetch values stored in a database to an application program. The target specification is specified as a variable. Trigger definition A trigger definition defines the data manipulation (insertion) of a table linked to the data manipulation (insertion, deletion, update) of another table. Unique constraint A constraint on a table or column.
