Copyright © 1996, 2000, Oracle Corporation. All rights reserved. Using the SQL Online Program Examples Examples of SQL programs are available online in the samples directory defined by the logical name SQL$SAMPLE. These program examples define and query databases, including the sample personnel database. You can create an executable image of any source program example for which your system has language support and then run the image to see how the pro- gram works. You can also create hardcopy listings of program sources and use them as references when you create your own applications. The examples are presented in several host languages. See Table 2 for information about which operating systems the programs support, whether the programs are available in pre- compiled SQL or SQL module language, and the languages in which they are written. The examples include the following: o sql_all_datatypes Illustrates INSERT and UPDATE statements, variations in techniques of handling null values, and the correspondence between SQL host language data types and their conversions. o sql_all_datatypes_date Similar to sql_all_datatypes, but focuses on handling the date/time data types. o sql_all_cdd_record_mod_c Demonstrates the use of repository definitions with SQL module language and a C host language program. o sql_all_record_mod_c Shows how to use SQL module language with C and demonstrates the use of indicator arrays to pass record structures to an SQL module program. o sql_connections 1 Demonstrates the use of connections by creating connections to a session and a database environment, inserting and re- trieving records, and disconnecting from the session and environment. o sql_dist_trans Illustrates how to use distributed transactions. This pro- gram updates two Oracle Rdb databases, which contain in- formation about the employees in a company's two divisions, and one Oracle CODASYL DBMS database, which contains central information about all employees in the company. o sql_dynamic Illustrates dynamic SQL. The C language version can be linked with either of the following two drivers: - sql_dynamic_driver_i When linked with sql_dynamic_driver_i, sql_dynamic demon- strates how a program using dynamic SQL can accept direct user input of SQL statements at run time or can accept, as input, a script file (@sql.dat, for example) containing interactive SQL statements. - sql_dynamic_driver_m When linked with sql_dynamic_driver_m, sql_dynamic demon- strates how a program using dynamic SQL can be used to loop through a series of predefined queries that are re- peated until the program is exited. The program includes a set of three queries that are repeated as you enter re- ponses and ends only when you manually stop the program. The Ada module language and the PL/I versions of this pro- gram provide more limited examples of dynamic SQL applica- tions. o sql_insert_degrees Illustrates SQL module language. This program adds informa- tion to the database about an employee's college degrees. o sql$intro_load_empl or sql_intro_load_empl Illustrates loading data into a database using SQL module language. This program loads data into the database created by the sql$intro_personnel.com or sql_intro_personnel.com script. o sql_load_(table-name) 2 Loads indicated table from a data file. Specific tables are used to illustrate various techniques. For instance, loading the EMPLOYEES table illustrates how to set a CHAR column to null based on a column value in the data file. Loading the JOB_HISTORY table illustrates how to set a DATE column to null based on a column value in the data file. o sql_mia_char_set Creates a database containing multiple MIA (Multivendor Integration Architecture) character sets. Inserts data of different character sets into that database, uses a cursor to fetch the data, and displays the data in a table format. o sql_msdb Lists the employees assigned to each department defined in the multischema sample corporate_data database. Shows how to use SQL module language and the SQL precompiled pro- grams when working with a multischema database and using multischema naming conventions. o sql_multi_stmt_dyn Illustrates how to use extended dynamic cursors to process any number of dynamic SQL statements. o sql_report Writes a salary report (SQLSAMP.RPT) with control breaks on the DEPARTMENT_CODE and JOB_CODE columns to print the average salary per job and total salary per department, and the grand total of department salary totals. o sql_resumes Illustrates how to insert LIST OF BYTE VARYING data (seg- mented strings) into the personnel database. o sql_terminate Illustrates how to update the personnel database when an employee is terminated. o sql_text_fields Loads table columns with values from repository records. Illustrates the three formats that you can specify for stor- ing text values from the repository using the C programming language: null-terminated bytes, null-terminated characters, and fixed (no null). 3 To find out which programs are available as precompiled SQL programs in a specific language, look in the samples directory for files ending with the characters shown in Table 1. Table_1:_Program_File_Types____________________________________ Language_________File_Type_____________________________________ Ada .sqlada C .sc COBOL .sco FORTRAN .sfo Pascal .spa PL/I .spl _______________________________________________________________ To locate SQL module language programs, look in the samples directory for files ending with .sqlmod. Table 2 lists the sample programs, whether they are available in precompiled SQL or SQL module language, and the language in which they are written. Table 2: Sample Programs in Precompiled SQL and SQL Module __________Language_________________________________________________ Module Precompiled SQL Language Sample Program Ada C COBOL Fortran Pascal PL/I ___________________________________________________________________ sql_all_datatypes X X X X X X Ada sql_all_datatypes_date X X X X X sql_all_cdd_record_mod X C sql_all_record_mod X C sql_connections X X C, FORTRAN sql_dist_trans X X X C, COBOL, FORTRAN sql_dynamic Ada, C, PL/I sql_insert_degrees COBOL 4 Table 2 (Cont.): Sample Programs in Precompiled SQL and SQL Module __________________Language_________________________________________ Module Precompiled SQL Language Sample Program Ada C COBOL Fortran Pascal PL/I ___________________________________________________________________ sql_intro_load_empl C sql_load_colleges X sql_load_degrees X X sql_load_depts X X sql_load_employees X X X X sql_load_jobhist X X X X Ada sql_load_jobs X X BASIC sql_load_salhist X X sql_mia_char_set X C sql_msdb X C sql_multi_stmt_dyn Ada sql_report X X X X X Ada sql_resumes X X X X sql_terminate X X X X X X Ada, BASIC, Pascal sql_text_fields X C ___________________________________________________________________ For information about compiling, linking, and running SQL pre- compiled programs and SQL module programs, see the Oracle Rdb7 Guide to SQL Programming or SQL Help. 5