Fujitsu J2X0-1634-01EN - Manual

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. SymfoWARE Programmer's Kit Related manuals The related manuals are as follows: · Reference M...

· 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 X...

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

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

[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. 5

[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 ar...

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

[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 col...

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

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

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

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

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

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

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

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

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: Exampl...

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. This section exp...

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

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

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

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

[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, PRE...

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

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

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

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

[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 r...

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

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

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

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. [Figure: Example of using a FETCH statement to position a cursor] 51

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

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

[Figure: Example of updating data using a cursor (column to be updated is not in the cursor 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 UP...

Chapter 5 Joining Multiple Tables and Manipul ating 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 ...

[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, i...

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

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

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

[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 bo...

[Figure: Example of performing an operation on different rows] 5.5 Obtaining the Aggregate for Rows from Multiple Tables 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 co...

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

[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 pr...

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

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

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

[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 i...

[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 sh...

[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." Exam...

[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 groupe...

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

[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, ...

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 ("A...

[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 evalua...

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. STOCK WHERE ...

[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 whic...

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

[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 pre...

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

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

[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 pr...

[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 sp...

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

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

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

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

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

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

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

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

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

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. CURRVAL and NEXTVAL can be spec...

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

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

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

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

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

[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 ex...

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

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

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

[Figure: Procedure for setting dynamic parameter specification values for prepared statements] 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...

[Figure: Procedure for setting dynamic parameter specification values for prepared statements] 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 dyn...

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

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

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

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

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

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

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

When executing an application program Example 4: Display a description of message JYP2031E set in the message variable (SQLMSG) when executing an application program. 200

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

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