FireDac 与数据库连接时字符集及对应的字段类型问题

时间:2021-07-17 03:50:07

近日在一个过程调用时发生一个奇怪现象, 异常返回意思是说, 数据的长度是【6】, 而字段定义的长度是【3】。  分析后认为:  调用过程你不涉及到对返回数据集的字段手动定义问题, 出现这个问题应是两边的字符集出了问题。

数据库字符集为 SIMPLIFIED CHINESE_CHINA.ZHS16GBK, 即双字节, 有可能FdConnection 的字符集设置出现匹配问题。 最后修改方案为:

1、修改 FDConnection 的 CharacterSet := 'UTF8' , 即以 UTF8 与数据库连接,兼容所有类型数据库
2、修改在调用过程时所有原  ftString  改为 ftWideString

问题解决

找到一份针对 SQLLite 的字段对应关系,我想其他类型数据库也同理:

Type name Description
ROWID | _ROWID_ | OID dtInt64, Attrs = [caSearchable, caAllowNull, caROWID]
BIT | BOOL | BOOLEAN | LOGICAL | YESNO dtBoolean
TINYINT | SHORTINT | INT8 [UNSIGNED] dtSByte / dtByte
BYTE | UINT8 dtByte
SMALLINT | INT16 [UNSIGNED] dtInt16 / dtUInt16
WORD | UINT16 | YEAR dtUInt16
MEDIUMINT | INTEGER | INT | INT32 [UNSIGNED] dtInt32 / dtUInt32
LONGWORD | UINT32 dtUInt32
BIGINT | INT64 | COUNTER | AUTOINCREMENT | IDENTITY [UNSIGNED] dtInt64 / dtUInt64
LONGLONGWORD | UINT64 dtUInt64
REAL | FLOAT | DOUBLE dtDouble
SINGLE [PRECISION] [(P, S)] dtSingle / dtBCD / dtFmtBCD
DECIMAL | DEC | NUMERIC | NUMBER [UNSIGNED] [(P, S)] dtSByte / dtInt16 / dtInt32 / dtInt64

dtByte / dtUInt16 / dtUInt32 / dtUInt64

dtBCD / dtFmtBCD

MONEY | SMALLMONEY | CURRENCY | FINANCIAL [(P, S)] dtCurrency
DATE | SMALLDATE dtDate
DATETIME | SMALLDATETIME dtDateTime
TIMESTAMP dtDateTimeStamp
TIME dtTime
CHAR | CHARACTER [(L)] dtAnsiString, Len = L, Attrs = [caFixedLen]
VARCHAR | VARCHAR2 | TYNITEXT | CHARACTER VARYING | CHAR VARYING [(L)] dtAnsiString, Len = L
NCHAR | NATIONAL CHAR | NATIONAL CHARACTER [(L)] dtWideString, Len = L, Attrs = [caFixedLen]
NVARCHAR | NVARCHAR2 | NATIONAL CHAR VARYING | STRING [(L)] dtWideString, Len = L
RAW | TYNIBLOB | VARBINARY | BINARY | BINARY VARYING [(L)] dtByteString, Len = L
BLOB | MEDIUMBLOB | IMAGE | LONGBLOB | LONG BINARY | LONG RAW | LONGVARBINARY | GENERAL | OLEOBJECT | TINYBLOB dtBlob
MEDIUMTEXT | LONGTEXT | CLOB | MEMO | NOTE | LONG | LONG TEXT | LONGCHAR | LONGVARCHAR | TINYTEXT dtMemo
TEXT | NTEXT | WTEXT | NCLOB | NMEMO | LONG NTEXT | LONG WTEXT | NATIONAL TEXT | LONGWCHAR | LONGWVARCHAR | HTML dtWideMemo
XMLDATA | XMLTYPE | XML dtXML
GUID | UNIQUEIDENTIFIER dtGUID
Other data types dtWideString

Note: With SQLite, the FormatOptions.StrsTrim works for all string data types.

参考: http://docwiki.embarcadero.com/RADStudio/XE6/en/Using_SQLite_with_FireDAC