Creating sample Unicode databases; escaped Unicode data in SQL

From: Maurice Bauhahn (bauhahnm@clara.net)
Date: Thu May 01 2003 - 17:45:44 EDT

  • Next message: Jim Allan: "RE: Accented IJ"

    Database gurus...please help.

    I'm trying to find/create scripts to create (and the escaped Unicode in SQL
    to populate) simple Unicode databases for many commercial and non-commercial
    databases (Oracle, DB2, MS Access, MS SQL Server, Sybase, Informix,
    Terradata, MySQL, PostgreSQL, PointBase, iAnywhere, SQLBase, etc). An
    immediate project is to fill such databases with the Unicode equivalents of
    characters in major Microsoft CodePages (hence the reference to
    CP1250_ora.sql below), using database clients to convert those back into
    CodePage points. Please feel free to critique what I present below for DB2
    and Oracle.

    I would welcome similar contributions (setup instructions on creating
    Unicode databases, scripts that create simple databases and tables, syntax
    for inserting escaped Unicode values into fields of those tables,
    instructions for retrieving such data from command line database clients in
    various encodings) for any other major database. I'm particularly interested
    in working with common datatypes like CHAR, VARCHAR and only secondarily in
    NCHAR-like datatypes that are 'forced' Unicode datatypes.

    I would be happy to make the text of this research available to anyone who
    wants them (hopefully after they have been perfected by helpful
    Unicoders!!).

    *****DB2 v8:
    create database mydatabase ccsid unicode;
    create tablespace mytablespace in mydatabase ccsid unicode;
    create table myutf (mytext char(100), mycodepage integer) ccsid unicode;

    *****DB2 v7:

    db2set db2codepage=1250

    Create directory on Windows D drive: myutf8

    DB2PATH should point to DB2 executable location

    Open a command line screen and type:

    db2start

    Open another window and type:

    db2cc #to start the control centre

    within that application type/choose(or use the wizard):

    CREATE DATABASE myutf8 ON 'd:/myutf8' USING CODESET UTF-8 TERRITORY UK

    quit that and type at command line:
    chcp 1250

    change directory to the same location of your CP1250_db2.sql script
    db2
    connect to myutf8
    create table myutf (mytext char(100), mycodepage integer);
    db2 -t -f CP1250_db2.sql
    terminate

    The script CP1250_db2.sql ends:
    insert into myutf (mytext, mycodepage) VALUES
    (UX'0159016F00FA017100FC00FD016302D9',1250);

    *****For Oracle 8/9:
    create database mydb datafile '/export/home/oracle/software/9i/data.db'
    character set utf8 national character set AL16UTF16;
    create table myutf (mytext varchar(100), mycodepage number(4));
    @CP1250_ora.sql

    The script CP1250_ora.sql ends:
    insert into myutf (mytext, mycodepage) VALUES
    (UNISTR('\0159\016F\00FA\0171\00FC\00FD\0163\02D9'),1250);

    Hopefully,

    Maurice Bauhahn

    This archive was generated by hypermail 2.1.5 : Thu May 01 2003 - 18:52:20 EDT