-- Copyright © Oracle Corporation 1995.  All Rights Reserved.
-------------------------------------------------------------------------------
--

------------------------------------------------------------------------------
--
-- This SQL_MIA_CHAR_SET_C.SQLMOD module file includes the SQL 
-- procedures that  the SQL_MIA_CHAR_SET_MOD.C program uses to
-- create a  database  containing multiple MIA character sets,
-- to insert  data  of  different  character  sets  into  that 
-- database, to use a cursor to fetch the data, and to display
-- the data in a table format.
--
-------------------------------------------------------------------------------
-- SQL Module Processor Command: SQL$MOD SQL_MIA_CHAR_SET_C.SQLMOD
-------------------------------------------------------------------------------
-- Header Information Section
-------------------------------------------------------------------------------
MODULE        SQL_MIA_CHAR_SET_C     -- Module name
DIALECT                 SQL92        -- Interpreting as MIA; NOTE: the
                                     -- character length is set to CHARACTERS
NAMES ARE               DEC_KANJI    -- Names character set
NATIONAL CHARACTER SET  KANJI        -- National character set
DEFAULT CHARACTER SET   DEC_KANJI    -- Default character set
LANGUAGE                C            -- Language of calling program
AUTHORIZATION           SQL_SAMPLE   -- Default authorization id
ALIAS                   RDB$HANDLE   -- Default alias
PARAMETER COLONS                     -- Parameters prefixed with colons
-------------------------------------------------------------------------------
-- DECLARE Statements Section
-------------------------------------------------------------------------------

DECLARE TRANSACTION READ WRITE          -- Declare read/write transaction

DECLARE COLOURS_CURSOR TABLE CURSOR     -- Declare table cursor for the 
    FOR SELECT                          -- COLOURS table.
      ENGLISH,
      FRENCH,
      JAPANESE,
      ROMAJI,
      KATAKANA,
      HINDI,
      GREEK,
      ARABIC,
      RUSSIAN FROM COLOURS

-------------------------------------------------------------------------------
-- Procedure Section
-------------------------------------------------------------------------------

--
-- Create database MIA_CHAR_SET
--

PROCEDURE CREATE_MIA_CHAR_SET_DB
    SQLCODE;
    CREATE DATABASE FILENAME mia_char_set
      DEFAULT CHARACTER SET DEC_KANJI
      NATIONAL CHARACTER SET KANJI
      IDENTIFIER CHARACTER SET DEC_KANJI;

--
-- When you declare character string parameters, you must take into
-- account the character set of the corresponding SQL column, whether
-- or not the characters are single- or multiple-octet characters, and
-- whether or not the module specified the character length in octets
-- or characters.
--

-- Create domains

PROCEDURE CREATE_DOMAIN_MCS
    SQLCODE;
    CREATE DOMAIN MCS_DOM       CHAR (8) CHARACTER SET DEC_MCS;

--
-- The CREATE DATABASE statement and the module header identify
-- KANJI as the national character set. Thus, you can declare
-- the KANJI_DOM domain as data type NCHAR.
--

PROCEDURE CREATE_DOMAIN_KANJI
    SQLCODE;
    CREATE DOMAIN KANJI_DOM     NCHAR (4);

--
-- Because the module header and the CREATE DATABASE statement
-- define DEC_KANJI as the default character set, you do not
-- have to identify the character set for the DEC_KANJI_DOM
-- domain.
--

PROCEDURE CREATE_DOMAIN_DEC_KANJI
    SQLCODE;
    CREATE DOMAIN DEC_KANJI_DOM   CHAR (8);

PROCEDURE CREATE_DOMAIN_KATAKANA
    SQLCODE;
    CREATE DOMAIN KATAKANA_DOM  CHAR (8) CHARACTER SET KATAKANA;

PROCEDURE CREATE_DOMAIN_HINDI
    SQLCODE;
    CREATE DOMAIN HINDI_DOM     CHAR (8) CHARACTER SET DEVANAGARI;

PROCEDURE CREATE_DOMAIN_GREEK
    SQLCODE;
    CREATE DOMAIN GREEK_DOM     CHAR (8) CHARACTER SET ISOLATINGREEK;

PROCEDURE CREATE_DOMAIN_ARABIC
    SQLCODE;
    CREATE DOMAIN ARABIC_DOM    CHAR (8) CHARACTER SET ISOLATINARABIC;

PROCEDURE CREATE_DOMAIN_RUSSIAN
    SQLCODE;
    CREATE DOMAIN RUSSIAN_DOM   CHAR (8) CHARACTER SET ISOLATINCYRILLIC;

--
-- Create table COLOURS
--

PROCEDURE CREATE_TABLE_COLOURS
    SQLCODE;
    CREATE TABLE COLOURS (
          ENGLISH   MCS_DOM,
          FRENCH    MCS_DOM,
          JAPANESE  KANJI_DOM,
          ROMAJI    DEC_KANJI_DOM,
          KATAKANA  KATAKANA_DOM,
          HINDI     HINDI_DOM,
          GREEK     GREEK_DOM,
          ARABIC    ARABIC_DOM,
          RUSSIAN   RUSSIAN_DOM);

--
-- Create index
--

PROCEDURE CREATE_INDEX
    SQLCODE;
    CREATE INDEX COLOUR_INDEX ON COLOURS (JAPANESE SIZE IS 4) TYPE IS SORTED;

--
-- Insert ASCII values of colours into table columns
--

PROCEDURE INSERT_BLACK
    SQLCODE;
    INSERT INTO COLOURS VALUES
          (_DEC_MCS'Black',
           _DEC_MCS'Noir',
           _KANJI'¹õ',
           _DEC_KANJI'kuro',
           _KATAKANA'¸Û',
           _DEVANAGARI'µÞÒÞ',
           _ISOLATINGREEK'Ìáýñïò',
           _ISOLATINARABIC'ÃÓÄÏ',
           _ISOLATINCYRILLIC'ÇñàÝëÙ');

PROCEDURE INSERT_WHITE
    SQLCODE;
    INSERT INTO COLOURS VALUES
          (_DEC_MCS'White',
           _DEC_MCS'Blanc',
           _KANJI'Çò',
           _DEC_KANJI'shiro',
           _KATAKANA'¼Û',
           _DEVANAGARI'ØËçÆ',
           _ISOLATINGREEK'Ëïõêü',
           _ISOLATINARABIC'ÃÈêÖ',
           _ISOLATINCYRILLIC'±ÕÛëÙ');

PROCEDURE INSERT_BLUE
    SQLCODE;
    INSERT INTO COLOURS VALUES
          (_DEC_MCS'Blue',
           _DEC_MCS'Bleu',
           _KANJI'ÀÄ',
           _DEC_KANJI'ao',
           _KATAKANA'±µ',
           _DEVANAGARI'ÈàÒÞ',
           _ISOLATINGREEK'Ãáëáíüò',
           _ISOLATINARABIC'ÃÒÑâ',
           _ISOLATINCYRILLIC'ÁØÝØÙ');

PROCEDURE INSERT_RED
    SQLCODE;
    INSERT INTO COLOURS VALUES
          (_DEC_MCS'Red',
           _DEC_MCS'Rouge',
           _KANJI'ÀÖ',
           _DEC_KANJI'aka',
           _KATAKANA'±¶',
           _DEVANAGARI'ÒÞÒ',
           _ISOLATINGREEK'Êüêêéíï',
           _ISOLATINARABIC'}ꌄ',
           _ISOLATINCYRILLIC'ºàÐáÝëÙ');

PROCEDURE INSERT_YELLOW
    SQLCODE;
    INSERT INTO COLOURS VALUES
          (_DEC_MCS'Yellow',
           _DEC_MCS'Jaune',
           _KANJI'²«',
           _DEC_KANJI'ki',
           _KATAKANA'·²Û',
           _DEVANAGARI'ÊàÒÞ',
           _ISOLATINGREEK'Êßôñéíïò',
           _ISOLATINARABIC'ÃÕáÑ',
           _ISOLATINCYRILLIC'¶ñÛâëÙ');

PROCEDURE INSERT_GREEN

    SQLCODE;
    INSERT INTO COLOURS VALUES
          (_DEC_MCS'Green',
           _DEC_MCS'Vert',
           _KANJI'ÎÐ',
           _DEC_KANJI'midori',
           _KATAKANA'ÐÄÞ¿',
           _DEVANAGARI'ÙÐÞ',
           _ISOLATINGREEK'ÐñÜóéíïò',
           _ISOLATINARABIC'ÃÎÖÑ',
           _ISOLATINCYRILLIC'·ÕÛñëÙ');

--
-- Open the cursor declared for the COLOURS table.
--

PROCEDURE OPEN_COLOURS_CURSOR
    SQLCODE;
    OPEN COLOURS_CURSOR;

--
-- Fetch data from the opened cursor for the COLOURS table.
--

PROCEDURE FETCH_COLOURS_DATA
    SQLCODE
    :ENGLISH		CHAR(8) CHARACTER SET DEC_MCS
    :FRENCH		CHAR(8) CHARACTER SET DEC_MCS
    :JAPANESE		CHAR(4) CHARACTER SET KANJI
    :ROMAJI             CHAR(8) 
    :KATAKANA		CHAR(8) CHARACTER SET KATAKANA
    :HINDI		CHAR(8) CHARACTER SET DEVANAGARI
    :GREEK		CHAR(8) CHARACTER SET ISOLATINGREEK
    :ARABIC		CHAR(8) CHARACTER SET ISOLATINARABIC
    :RUSSIAN		CHAR(8) CHARACTER SET ISOLATINCYRILLIC;


    FETCH COLOURS_CURSOR INTO 
                    :ENGLISH,
		    :FRENCH,
		    :JAPANESE,
                    :ROMAJI,
		    :KATAKANA,
		    :HINDI,
		    :GREEK,
		    :ARABIC,
		    :RUSSIAN;

--
-- Close the COLOURS_CURSOR cursor for the COLOURS table.
--

PROCEDURE CLOSE_COLOURS_CURSOR
   SQLCODE;
   CLOSE COLOURS_CURSOR;

--
-- Commit the transaction
--

PROCEDURE CREATE_COMMIT
    SQLCODE;
    COMMIT;

--
-- Roll back the transaction
--

PROCEDURE CREATE_ROLLBACK
    SQLCODE;
    ROLLBACK;