[SOLVED] String constants being displayed as messy code when using IBConnection

[mihaisheng]

This issue does not occur with PostgreSQL, but it does with Firebird.

The default file format for Lazarus is UTF8, the CharSet for IBConnection is UTF8, and the file format for Firebird 4.0 is UTF8.

The params attribute of IBConnection is set to lc_ctype=UTF8
sc_dpb_utf8_filename=1.

After populating the DBGrid with the query results from SQLQuery, field status_display displays messy code.

Code: Pascal  [Select][+][-]

1. select
2.      CASE
3.             WHEN status = TRUE THEN '启用'
4.             WHEN status = FALSE THEN '禁用'
5.             ELSE '未知'
6.      END AS status_display,
7.      name,
8.      code
9. from sys_department  
10.  

If using PostgreSQL and TPQConnection, the field status_display can be displayed normally.

It seems the setting of lc_ctype=UTF8 not work at all when using IBConnection。

I  have to define a custom function to solve this problem for firebird.

Code: Pascal  [Select][+][-]

1. CREATE OR ALTER FUNCTION TO_UTF8 (
2.     SOMESTR VARCHAR(500)
3. )
4. RETURNS VARCHAR(500) CHARACTER SET UTF8
5. AS
6. BEGIN
7.     RETURN CAST(SOMESTR AS VARCHAR(500) CHARACTER SET UTF8);
8. END;
9.  

thanks.

[dsiders]

This issue does not occur with PostgreSQL, but it does with Firebird.

The default file format for Lazarus is UTF8, the CharSet for IBConnection is UTF8, and the file format for Firebird 4.0 is UTF8.

The params attribute of IBConnection is set to lc_ctype=UTF8
sc_dpb_utf8_filename=1.

After populating the DBGrid with the query results from SQLQuery, field f1 displays messy code, while field f2 displays normally.

Code: Pascal  [Select][+][-]

1. select
2.       '感谢' as f1 ,
3.       CAST('感谢' AS VARCHAR(100) CHARACTER SET UTF8) as f2,
4.       dept_name,
5.       dept_code
6. from sys_department  
7.  

If using PostgreSQL , the field f1 can be displayed normally.

thanks.

As I understand it, columns in firebird which use UTF-8 must be wrapped in Double Quotes not Single Quotes.

[Thaddy]

Correct, but it is not only Firebird, but proper SQL in general. The same goes for e.g. Oracle or Postgress even if you say it doesn't......
Pascal's single quotes vs everybody else's double quotes is an easy gotcha.
You are writing SQL, not Pascal..... So use '"<whatever stringified SQL>"'. This is also PostGress compatible.
( It may be that the LCL component handles this for Postgress and weirdly accepts single quotes )

[mihaisheng]

No need to define the function TO_UTF8.

Previously, I set the CharSet property of IBConnection to UTF-8, Just clear the CharSet property of IBConnection and it will work normally.