none
Inserting chinese text into the SQLite database RRS feed

  • Question

  • Table

    CREATE TABLE [Test2]
    (
        [ipaddress] INTEGER,
        [Name] text
    )

     

    The following query works fine

     

    Set Cmd1 = Server.CreateObject("ADODB.Command")

    Cmd1 .ActiveConnection = AuditLogConnection

    Cmd1 .CommandText = "INSERT INTO Test2(ipaddress, name) VALUES ('192.168.1.1' , '曹长庆从三个方面来分析为什么说是结构性上涨:首先,从价格指数看,今年1-11月份,在构成居民消费价格指数的八大类商品中,上涨幅度较大的主要是食品类和居住类价格。在1-11月价格总水平上升4.6%中,食品类价格上涨11.9%,影响价格总水平上升4个百分点,占涨价因素的87%。其中仅猪肉价格上涨就影响价格总水平上升1.4个百分点。居住类价格上涨4.3%,影响价格总水平上升0.6个百分点。其余六大类商品价格基本保持平稳,升降相抵,对价格总水平基本没有影响。从这个角度来看,当前的价格上涨结构性特征非常明显。') "

    ret = Cmd1 .Execute

     

    The following query inserts question marks into the database

     

     

    Dim Cmd1 , ret,namestr,id

    ip  ='192.168.1.1'

    namestr =  '曹长庆从三个方面来分析为什么说是结构性上涨:首先,从价格指数看,今年1-11月份,在构成居民消费价格指数的八大类商品中,上涨幅度较大的主要是食品类和居住类价格。在1-11月价格总水平上升4.6%中,食品类价格上涨11.9%,影响价格总水平上升4个百分点,占涨价因素的87%。其中仅猪肉价格上涨就影响价格总水平上升1.4个百分点。居住类价格上涨4.3%,影响价格总水平上升0.6个百分点。其余六大类商品价格基本保持平稳,升降相抵,对价格总水平基本没有影响。从这个角度来看,当前的价格上涨结构性特征非常明显。'

    Set Cmd1 = Server.CreateObject("ADODB.Command")

    Cmd1 .ActiveConnection = AuditLogConnection

    Cmd1 .CommandText = "INSERT INTO Test2(ipaddress, name) VALUES (?, ?)"

    Cmd1 .Prepared = true

    Cmd1 .Parameters.Append(Cmd1 .CreateParameter("ipaddress", adLongVarChar, adParamInput, 50, ipaddress))

    Cmd1 .Parameters.Append(Cmd1 .CreateParameter("name", adLongVarChar, adParamInput, 50, namestr))

    ret = Cmd1 .Execute

     

    AuditLogConnection contains connection string as

    "Provider=MSDASQL.1;Extended Properties=""DSN=Test;Database=C:\Program Files\Test.db;StepAPI=0;SyncPragma=NORMAL;NoTXN=;Timeout=100000;ShortNames=;LongNames=;NoCreat=;NoWCHAR=;LoadExt=;"""

     

     

    Whats wrong? Help appreciated.

     

    Thanks

     

    Thursday, December 13, 2007 10:28 PM

Answers

  • EDIT

    I almost forgot read the SQLite docs because I once helped a developer for days with one version of Japanese only to find it was not defined in MySQL at the time. So read the SQLite docs then read my post again. You need to read my post again because without Nvachar and the correct collation you cannot get Chinese in .NET, all relational data must use Nvarchar which tells .NET your data is Unicode and the collation tells .NET the version of Chinese you need.  The reason most RDBMS(relational database management systems) SQL Server included uses UCS-2 not UT8 or UTF16.  .NET is UTF16 by default but takes UTF8 when defined.  Good luck.

     

    Tuesday, December 18, 2007 12:20 AM

All replies

  • You are getting character conversion because the Latin alphabet is 26 characters while the Chinese alphabet is more than 2000 characters.  So you need to find out the version of Chinese you are using and then check the Unicode implementation of SQLite.  The .NET framework now comes with language packs and you can also use VS2005 to save your code as Chinese but still need to know the version of Chinese.  I have also seen people using fonts to solve localization problems see if you can install Chinese fonts.   In SQLite you need Nvarchar and NText with the Chinese collation.  Hope this helps.

    Friday, December 14, 2007 1:57 PM
  • thanks for your response.

    The default storage encoding on disk is UTF-8 so I do not need Nvarchar and NText with the Chinese collation in sqlite.How can I see unicode strings in Visual studio 2005 Add watch window. When I try to see Unicode strings Add watch it shows me square boxes ...

     

    Thanks,

     

    Monday, December 17, 2007 11:30 PM
  • EDIT

    I almost forgot read the SQLite docs because I once helped a developer for days with one version of Japanese only to find it was not defined in MySQL at the time. So read the SQLite docs then read my post again. You need to read my post again because without Nvachar and the correct collation you cannot get Chinese in .NET, all relational data must use Nvarchar which tells .NET your data is Unicode and the collation tells .NET the version of Chinese you need.  The reason most RDBMS(relational database management systems) SQL Server included uses UCS-2 not UT8 or UTF16.  .NET is UTF16 by default but takes UTF8 when defined.  Good luck.

     

    Tuesday, December 18, 2007 12:20 AM