Summary of the content on the page No. 1
J2X0-1634-01EN
SymfoWARE(R)Server SQL Beginner's Guide
Summary of the content on the page No. 2
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. Intended Readers This manual
Summary of the content on the page No. 3
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. These manuals also provide an overview on specifying SQL statements and develo
Summary of the content on the page No. 4
SymfoWARE Programmer's Kit Position of this manual The manual system and the position of this manual are as follows: iii
Summary of the content on the page No. 5
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. Follow the items in the written contract to use this product properly Windows NT/2000/XP See t
Summary of the content on the page No. 6
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 Manuals Collection of AnswerBook 2 · Fujitsu COBOL User's Guide for Windows · COBOL85 User's Guide · Fujitsu COBOL Language Reference Precautions Applicable products UNIX · Sy
Summary of the content on the page No. 7
· 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 · Microso
Summary of the content on the page No. 8
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. vii
Summary of the content on the page No. 9
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.
Summary of the content on the page No. 10
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. The following Figure: Types of SQL shows the types of SQL
Summary of the content on the page No. 11
[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. 2
Summary of the content on the page No. 12
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 identif
Summary of the content on the page No. 13
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. PRODNO:
Summary of the content on the page No. 14
[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
Summary of the content on the page No. 15
[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. For example, the product with PRODNO 123 in the STOCK table is listed as REF
Summary of the content on the page No. 16
[Figure: Relationship between STOCK table, ORDER table, and COMPANY table] 7
Summary of the content on the page No. 17
8
Summary of the content on the page No. 18
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. The following is an example of the single row SELECT statement: Example: In this example, STOCKQTY o
Summary of the content on the page No. 19
[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 FRO
Summary of the content on the page No. 20
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. When an indicator variable is specified in an SQL statement, prefix the
