求救：sqlite 中文读取的问题

[zlj9000]

我用C++ 的 CppSQLite3 操作 sqlite 库，但读取字段中的中文数据有问题。

下面是读取的表及对应实体：其中 CType 表的 CTypeName 字段是存储中文数据的字段

CREATE TABLE "main"."CType" (
"CTypeId" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
"CTypeName" TEXT NOT NULL,
"CTypeSort" INTEGER NOT NULL,
"CCategory" INTEGER NOT NULL
)
;

class CType
{
public:
CType(void){

}

~CType(void){

}

int CTypeId;
wstring CTypeName;
int CTypeSort;
int CCategory;
};

读取代码如下：

void SelectCType(TypeCategory category, list<CType>& const types)
{
CppSQLite3DB db;
db.open(dbPath);

string sql = "select CTypeId, CTypeName, CTypeSort, CCategory from CType where CCategory = " + Convert::ToString((int(category))) + " order by CTypeSort asc";

CppSQLite3Query q = db.execQuery(sql.c_str());
types.clear();
while(!q.eof())
{
CType type;
type.CTypeId = q.getIntField(0);
string a = q.getStringField(1);

type.CTypeName = Convert::ToWString(q.getStringField(1));
type.CTypeSort = q.getIntField(2);
type.CCategory = q.getIntField(3);

types.push_back(type);

q.nextRow();
}

db.close();
}

static wstring Convert::ToWString(const string& s)
{
setlocale(LC_ALL, "chs");
const char* _Source = s.c_str();
size_t _Dsize = s.size() + 1;
wchar_t *_Dest = new wchar_t[_Dsize];
wmemset(_Dest, 0, _Dsize);
mbstowcs(_Dest,_Source,_Dsize);
wstring result = _Dest;
delete []_Dest;
setlocale(LC_ALL, "C");
return result;
}

type.CTypeName = Convert::ToWString(q.getStringField(1)); 老是读不出中文来。

[libinfei8848]

读取不出来？还是读取出来的是乱码？

[libinfei8848]

代码都是开源的，楼主跟进去代码去看看原因吧。

[zlj9000]

其实问题出在读出的数据是utf_8编码，要转成gb2312就可看了。还是要感谢（jhony_lee）的提醒。不过没找到
int CCodingConv::GB2312_2_UTF8(char * buf, int buf_len, const char * src, int src_len = 0);
int CCodingConv::UTF8_2_GB2312(char * buf, int buf_len, const char * src, int src_len = 0);
俩方法。

我解决的步骤是：

1、下载sqlite3 的源码：加入CppSQLite3.h、CppSQLite3.cpp、再添加UCodeConvert 类，实现转换

void UCodeConvert::UTF_8ToUnicode(wchar_t* pOut,char *pText)
{
char* uchar = (char *)pOut;

uchar[1] = ((pText[0] & 0x0F) << 4) + ((pText[1] >> 2) & 0x0F);
uchar[0] = ((pText[1] & 0x03) << 6) + (pText[2] & 0x3F);

return;
}

void UCodeConvert::UnicodeToUTF_8(char* pOut,wchar_t* pText)
{
// 注意 WCHAR高低字的顺序,低字节在前，高字节在后
char* pchar = (char *)pText;

pOut[0] = (0xE0 | ((pchar[1] & 0xF0) >> 4));
pOut[1] = (0x80 | ((pchar[1] & 0x0F) << 2)) + ((pchar[0] & 0xC0) >> 6);
pOut[2] = (0x80 | (pchar[0] & 0x3F));

return;
}

void UCodeConvert::UnicodeToGB2312(char* pOut,wchar_t uData)
{
WideCharToMultiByte(CP_ACP,NULL,&uData,1,pOut,sizeof(wchar_t),NULL,NULL);
return;
}

void UCodeConvert::Gb2312ToUnicode(wchar_t* pOut,char *gbBuffer)
{
::MultiByteToWideChar(CP_ACP,MB_PRECOMPOSED,gbBuffer,2,pOut,1);
return ;
}

void UCodeConvert::GB2312ToUTF_8(string& pOut,char *pText, size_t pLen)
{
if(pLen == 0)
{
pLen = strlen(pText);
}

char buf[4];
size_t nLength = pLen* 3;
char* rst = new char[nLength];

memset(buf,0,4);
memset(rst,0,nLength);

size_t i = 0;
size_t j = 0;
while(i < pLen)
{
//如果是英文直接复制就可以
if( *(pText + i) >= 0)
{
rst[j++] = pText[i++];
}
else
{
wchar_t pbuffer;
Gb2312ToUnicode(&pbuffer,pText+i);

UnicodeToUTF_8(buf,&pbuffer);

unsigned short int tmp = 0;
tmp = rst[j] = buf[0];
tmp = rst[j+1] = buf[1];
tmp = rst[j+2] = buf[2];

j += 3;
i += 2;
}
}
rst[j] = '\0';

//返回结果
pOut = rst;
delete []rst;

return;
}

void UCodeConvert::GB2312ToUTF_8(string& pOut,const char *pText, size_t pLen)
{
GB2312ToUTF_8(pOut, (char*)pText, pLen);
}

void UCodeConvert::UTF_8ToGB2312(string &pOut, char *pText, size_t pLen)
{
if(pLen == 0)
{
pLen = strlen(pText);
}

char * newBuf = new char[pLen];
char Ctemp[4];
memset(Ctemp,0,4);

size_t i =0;
size_t j = 0;

while(i < pLen)
{
if(pText[i] > 0)
{
newBuf[j++] = pText[i++];
}
else
{
WCHAR Wtemp;
UTF_8ToUnicode(&Wtemp,pText + i);

UnicodeToGB2312(Ctemp,Wtemp);

newBuf[j] = Ctemp[0];
newBuf[j + 1] = Ctemp[1];

i += 3;
j += 2;
}
}
newBuf[j] = '\0';

pOut = newBuf;
delete []newBuf;

return;
}

void UCodeConvert::UTF_8ToGB2312(string &pOut, const char *pText, size_t pLen)
{
UTF_8ToGB2312(pOut, (char*)pText, pLen);
}

2、修改CppSQLite3的 open() 方法为：
void CppSQLite3DB::open(const char* szFile)
{
string path;
UCodeConvert::GB2312ToUTF_8(path, (char*)szFile, strlen(szFile));

int nRet = sqlite3_open(path.c_str(), &mpDB);

if (nRet != SQLITE_OK)
{
const char* szError = sqlite3_errmsg(mpDB);
throw CppSQLite3Exception(nRet, (char*)szError, DONT_DELETE_MSG);
}

setBusyTimeout(mnBusyTimeoutMs);
}
，解决打开中文路径的问题。

3、编译sqlite3
4、在调用sqlite3 的程序中，添加方法：
static string GB2312ToUTF_8(string& str)
{
string str1;
UCodeConvert::GB2312ToUTF_8(str1, str.c_str());
return str1;
}
此方法用于查询时把查询语句转成UTF_8字符：调用如下：
string sql = "select CTypeId, CTypeName,from CType where CCategory = '凉菜'";
CppSQLite3Query q = db.execQuery(Convert::GB2312ToUTF_8(sql).c_str());
这就解决了查询中有中文出错的问题。

5、在调用sqlite3 的程序中，添加方法：
static string UTF_8ToGB2312(string& str)
{
string str1;
UCodeConvert::UTF_8ToGB2312(str1, str.c_str());
return str1;
}
此方法用于查询时把查询出的字符转成gb2312字符：调用如下：
type.CTypeName = Convert::UTF_8ToGB2312(q.getStringField(1));

最后看下完整的调用过程：

const char* dbPath = "d:\\中文库.db";
CppSQLite3DB db;
db.open(dbPath);

string sql = "select CTypeId, CTypeName, CTypeSort, CCategory from CType where CCategory = " + Convert::ToString((int(category))) + " order by CTypeSort asc";
//CppSQLite3Query q = db.execQuery(Convert::GB2312ToUTF_8(sql).c_str()); 查询里有中文内容，需转成utf_8处理
CppSQLite3Query q = db.execQuery(sql.c_str());
types.clear();
while(!q.eof())
{
CType type;
type.CTypeId = q.getIntField(0);
type.CTypeName = Convert::UTF_8ToGB2312(q.getStringField(1));
type.CTypeSort = q.getIntField(2);
type.CCategory = q.getIntField(3);

types.push_back(type);

q.nextRow();
}

db.close();

[jhony_lee]

楼主，sqlite有CodingConv.h和CodingConv.cpp

int CCodingConv::GB2312_2_UTF8(char * buf, int buf_len, const char * src, int src_len = 0);
int CCodingConv::UTF8_2_GB2312(char * buf, int buf_len, const char * src, int src_len = 0);

用上面这两个接口不就可以了