MDB2

PEAR::MDB2 database abstraction layer

[ AdvancedSearch | AreaMap ]

Character Set

Add a portable way to get/set charset of a connection/query/result (Request #4666 Different database charsets)

There are four (or more) possible settings related to character set/encoding.

1. client: charset of a query, as it leaves from the client
2. connection (server): charset of a query, as the server receives
3. database/table/column: charset/collation of a database/table/field, as it's kept in the server
4. results (message): charset of a result, as it leaves from the server

It seems for most RDBMS there is only 1 setting, which sets the client charset. The database charset is determined when the database/table is setup. Its the job of the client-server combination to convert the charsets at some point. This can be set inside an array dsn using the "charset" key.

What is mainly missing at this point is support for setting database, table, column and result level charsets and collations.

Collation (sorting) is also another parameter to be considered.

Where to get/set them ?

Possible places:

• (set) a parameter in DSN ?
• (set) an option in MDB2::connect ?
  • a natural place for connection charset setting ?
• (get/set) a new method in MDB2 ?
• (get/set) a new method in MDB2_Result ?
• (get/set) a new method in MDB2_Datatype ?
• (schema) new <charset> tag in the xml schema (database, table, field) ?

Survey of DBMS-specific commands

• MySQL: ¹
  • client: SET character_set_client = charset;
  • connection: SET character_set_connection = charset;
  • database: CREATE DATABASE database DEFAULT CHARACTER SET charset COLLATE collation;
  • table: CREATE TABLE table1 (...) CHARACTER SET charset COLLATE collation;
  • field: CREATE TABLE table1 (column1 VARCHAR(5) CHARACTER SET charset COLLATE collation);
  • results: SET character_set_results = charset;
    • if no conversion needed, set it to NULL (e.g. SET character_set_results = NULL;)
  • Shortcuts:
    • SET NAMES 'charset'
      • equivalent to these three commands:
        • SET character_set_client = charset;
        • SET character_set_results = charset;
        • SET character_set_connection = charset;
      • setting character_set_connection to x also sets collation_connection to the default collation for x.
    • SET CHARACTER SET charset
      • equivalent to these three commands:
        • SET character_set_client = charset;
        • SET character_set_results = charset;
        • SET collation_connection = @@collation_database;
      • similar to SET NAMES, but sets the connection character set and collation to be those of the default database.
• SQLite: ²
  • client:
  • connection:
  • database:
  • table: PRAGMA encoding="charset";
    • only effective for newly created table, cannot be changed later
  • results:
• PostgreSQL?: ³
  • client: SET CLIENT_ENCODING TO 'value';
  • connection: -
  • database: CREATE DATABASE korean WITH ENCODING 'EUC_KR';
  • table:
  • field:
  • results: SET CLIENT_ENCODING TO 'value';
    • if no conversion needed RESET client_encoding;
  • Shortcuts:
    • SET NAMES 'charset' (standard sql)
      • equivalent to :
        • SET CLIENT_ENCODING TO 'value';
• Oracle: ⁴
  • client: oci_connect() or NLS_LANG environmental variable
  • connection: -
  • database:
  • table:
  • results:
• Interbase/Firebird: ⁵
  • client: ibase_connect() or ibase.default_charset ini setting
  • connection: N/A
  • database: CREATE DATABASE korean DEFAULT CHARACTER SET 'EUC_KR';
  • table: N/A
  • field: CREATE TABLE Table1 (column1 VARCHAR(5) CHARACTER SET charset COLLATE collation); (NB: only for CHAR and VARCHAR fields, not blobs)
  • results:
  • Shortcuts:
    • SET NAMES 'charset' (standard sql)

This site powered by YaWiki 0.22 beta.