DEV Community

Michael
Michael

Posted on • Originally published at gbase.cn

GBase 8s: How DELIMIDENT Controls Case Sensitivity

#gbase #database #数据库

By default, identifiers in GBase 8s are case‑insensitive: uppercase letters are silently treated as lowercase. Setting the environment variable DELIMIDENT=Y changes how double‑quoted identifiers behave, enabling case‑sensitive table and column names. Here's a demonstration and a deep dive into the option, as used in a gbase database.

Testing Case Sensitivity

With DELIMIDENT=y exported, execute the following statements:

export DELIMIDENT=y
dbaccess testdb <<!
-- Uppercase table name, double-quoted
CREATE TABLE "Tab04"(id INT, name VARCHAR(20));
INSERT INTO "Tab04" VALUES(1, 'T DA XIE');

-- Lowercase table name, double-quoted
CREATE TABLE "tab04" (id INT, name VARCHAR(20));
INSERT INTO "tab04" VALUES(1, 't xiao xie');

-- Without double quotes, this collides with the lowercase one (error 310)
CREATE TABLE tab04 (id INT, name VARCHAR(20));  -- fails
!
Enter fullscreen mode Exit fullscreen mode

Query the system catalog to confirm both tables exist:

SELECT tabname FROM systables WHERE tabname LIKE '%ab04%';
-- Returns tab04 and Tab04 (displayed without quotes, but distinguished)
Enter fullscreen mode Exit fullscreen mode

Query behavior:

  • Unquoted or lowercase‑quoted references all go to the lowercase table tab04: 
  SELECT * FROM Tab04;     -- → tab04
  SELECT * FROM tab04;     -- → tab04
  SELECT * FROM "tab04";   -- → tab04
Enter fullscreen mode Exit fullscreen mode
  • Only an exactly matching double‑quoted uppercase name reaches the uppercase table: 
  SELECT * FROM "Tab04";   -- → Tab04
Enter fullscreen mode Exit fullscreen mode

After unsetting DELIMIDENT:

unset DELIMIDENT
dbaccess -e testdb <<!
SELECT * FROM Tab04;       -- → tab04 (still works)
SELECT * FROM tab04;       -- → tab04
SELECT * FROM "tab04";     -- error 201
SELECT * FROM "Tab04";     -- error 201
!
Enter fullscreen mode Exit fullscreen mode

Double‑quote wrapping becomes illegal, so the uppercase table becomes completely unreachable.

Understanding the DELIMIDENT Variable

DELIMIDENT controls whether double quotes enclose an SQL identifier or a plain string. It accepts three configurations:

  • y (default for OLE DB, .NET) Single quotes '...' denote string literals; double quotes "..." denote SQL identifiers, which become case‑sensitive.
  • n (default for ESQL/C, JDBC, ODBC) Both single and double quotes denote string literals. Double‑quoted identifiers are not allowed (error 201). One exception: you can use single quotes to qualify an owner name.
  • Not set The behavior follows the driver‑specific default (e.g., ESQL/C assumes n).

GBase 8s does not offer a way to achieve case‑sensitive identifiers without double quotes. DELIMIDENT=y is the only mechanism, and it requires the identifier to be written exactly as it was created — a small price for clarity in a gbase database.

Going forward, if your application needs to preserve case in table or column names, always set DELIMIDENT=y and standardize on double‑quoted identifiers. This keeps your GBASE environment predictable and avoids silent data‑access mistakes.

Top comments (0)