解决日文片假名导致 Access 搜索“内存溢出”的方法大全(转帖)

原创 2007年09月20日 10:32:00
ゴ ガ ギ グ ゲ ザ ジ ズ ヅ デ ド ポ ベ プ ビ パ ヴ ボ ペ ブ ピ バ ヂ ダ ゾ ゼ

当字段内包含了这26个日文字符任意一个多个时,就会导致在执行SQL语句中包含了
[字段] like '%aaaaa%' 或 inStr(1,[字段],'aaaaa',1)>0
这样的查询时,毫无道理的出现了
"Microsoft JET Database Engine 错误 '80040e14' 内存溢出" 的错误
其他Jet SQL函数命令未作测试,大概与字符搜索定位匹配相关的都可能出错
搜索相关资料得知被微软工程师证实是Access的bug

1.

一个简单的方法是:
把如下的语句:
Sql = "select * From [tbname] Where Title like '%"&keyword;&"%' Order By id Desc"
换成:
Sql = "select * from [tbname] where InStr(1,LCase(Title),LCase('"&keyword;&"'),0)
0 order by id desc"

 



2.
写一段代码把含有26个片假名的条目搜索出来,或者老老实实查找
删除条目即可


3.Wodig2.0版本,存在的搜索日文造成内存溢出问题解决

修改文件:cls_clall.asp
文件位置:./wolib文件夹下
修改第79行:有此修改过此文件之后,有可能行数就不对了。

查找:可用查找功能,查找“like”能够快速定位到修改位置。
sql = sql & " and src_webname like '%"& s_havving &"%' or src_url like '%" & s_havving & "%' or src_desc like '%" & s_havving & "%'"

替换为:
sql = sql & " and instr(1,lcase(src_webname),lcase('"& s_havving &"'),0)
0 or instr(1,lcase(src_url),lcase('"& s_havving &"'),0)
0 or instr(1,lcase(src_desc),lcase('"& s_havving &"'),0)
0"

如果对程序代码不熟悉,可在些下载本站修改好的cls_clall.asp文件,替换原文件即可。
修改好的cls_clall.asp文件请到e迷网下载!


4.

一简单有效的解决办法:
对这26个字符进行编码和解码,可能效率感觉不理想,测试下来问题不大,速度影响不是太大

编码:
Function Jencode(byVal iStr)
if isnull(iStr) or isEmpty(iStr) then
Jencode=""
Exit function
end if
dim F,i,E
' F=array("ゴ","ガ","ギ","グ","ゲ","ザ","ジ","ズ","ヅ","デ",_
' "ド","ポ","ベ","プ","ビ","パ","ヴ","ボ","ペ","ブ","ピ","バ",_
' "ヂ","ダ","ゾ","ゼ")
E=array("Jn0;","Jn1;","Jn2;","Jn3;","Jn4;","Jn5;","Jn6;","Jn7;","Jn8;","Jn9;","Jn10;","Jn11;","Jn12;","Jn13;","Jn14;","Jn15;","Jn16;","Jn17;","Jn18;","Jn19;","Jn20;","Jn21;","Jn22;","Jn23;","Jn24;","Jn25;")
F=array(chr(-23116),chr(-23124),chr(-23122),chr(-23120),_
chr(-23118),chr(-23114),chr(-23112),chr(-23110),_
chr(-23099),chr(-23097),chr(-23095),chr(-23075),_
chr(-23079),chr(-23081),chr(-23085),chr(-23087),_
chr(-23052),chr(-23076),chr(-23078),chr(-23082),_
chr(-23084),chr(-23088),chr(-23102),chr(-23104),_
chr(-23106),chr(-23108))
Jencode=iStr
for i=0 to 25
Jencode=replace(Jencode,F(i),E(i))
next
End Function

解码:
Function Juncode(byVal iStr)
if isnull(iStr) or isEmpty(iStr) then
Juncode=""
Exit function
end if
dim F,i,E
' F=array("ゴ","ガ","ギ","グ","ゲ","ザ","ジ","ズ","ヅ","デ",_
' "ド","ポ","ベ","プ","ビ","パ","ヴ","ボ","ペ","ブ","ピ","バ",_
' "ヂ","ダ","ゾ","ゼ")
E=array("Jn0;","Jn1;","Jn2;","Jn3;","Jn4;","Jn5;","Jn6;","Jn7;","Jn8;","Jn9;","Jn10;","Jn11;","Jn12;","Jn13;","Jn14;","Jn15;","Jn16;","Jn17;","Jn18;","Jn19;","Jn20;","Jn21;","Jn22;","Jn23;","Jn24;","Jn25;")
F=array(chr(-23116),chr(-23124),chr(-23122),chr(-23120),_
chr(-23118),chr(-23114),chr(-23112),chr(-23110),_
chr(-23099),chr(-23097),chr(-23095),chr(-23075),_
chr(-23079),chr(-23081),chr(-23085),chr(-23087),_
chr(-23052),chr(-23076),chr(-23078),chr(-23082),_
chr(-23084),chr(-23088),chr(-23102),chr(-23104),_
chr(-23106),chr(-23108))
Juncode=iStr
for i=0 to 25
Juncode=replace(Juncode,E(i),F(i))'□
next
End Function

注意,如果直接使用字符不方便(windows还没装日文支持),注释掉的部分提供有 chr(-23804) ..这样的定义

这样
1.
表单输入保存时,使用Jencode()将这26个字符先编码再保存(为什么是这26个字符,经过全部测试87个平假名89个片假名最终认定的)

ゴ 即 chr(-23116) 编码为 Jn1;
2.
显示时,则使用 Juncode() 函数进行解码,还原日文片假名显示
3.
搜索关键字,也要使用 Jencode() 进行编码后再放入 like里
where [Topic] like '%Jencode(kewwords)%' 使用
才能保证搜索的值和编码过的数据库字段内容匹配

==================================
PS:
也可以使用正则表达式来改写上面的两个函数,或许效率还要更高些
再就是如果 压根不使用日文,也不需要搜索日文,则解码部分可以不用,保存数据实直接把这26个片假名字符替换为空字符或任一字符,比如"□"

附:
----------------------------
平假名87个 asc值
-23391 --> -23316
unicode 3040-309F

ぁあぃいぅうぇえぉお
かがきぎくぐけげこご
さざしじすずせぜそぞ
ただちぢっつづてでと
どなにぬねのはばぱひ
びぴふぶぷへべぺほぼ
ぽまみむめもゃやゅゆ
ょよらりるれろゎわゐ
ゑをん゛゜ゝゞ
------------------------------
片假名89个 asc值
-23135 -> -23059
unicode 30A0-30FF

ァアィイゥウェエォオ
カガキギクグケゲコゴ
サザシジスズセゼソゾ
タダチヂッツヅテデト
ドナニヌネノハバパヒ
ビピフブプヘベペホボ
ポマミムメモャヤュユ
ョヨラリルレロヮワヰ
ヱヲンヴヵヶーヽヾ



5 .

今天上班的时候,同事晓霞在查看论坛的时候结果搜索数据库时出现“内存溢出”错误。开始俺也没有一个明确的答案,于是乎Kiven开始上网搜索寻求答案未果。最后才发现这就是传说中的“日文 26 个片假名(ゴ ガ ギ ア ゲ ザ ジ ズ ヅ デ ド ポ ベ プ ビ パ ヴ ボ ペ ブ ピ バ ヂ ダ ゾ ゼ)导致 Access 搜索出现内存溢出(Microsoft JET Database Engine 错误 '80040e14' 内存溢出)”的 Bug,研究了半天,最后终于还算是解决了。

撇开针对 LBS^2 的 http://www.ronnier.com/article.asp?id=31,再看看归纳出罪魁祸首为 26 个日文片假名 的 http://www.blueidea.com/bbs/NewsDetail.asp?id=1768538,再借用 Loveyuki 的图:http://www.loveyuki.com/blogview.asp?logID=621&cateID=4:

看原大图



最后,本人决定用最简单的笨方法:


Function EncodeJP(StrHTML)
If Not StrHTML="" Then
StrHTML=Replace(StrHTML,"ガ","ガ")
StrHTML=Replace(StrHTML,"ギ","ギ")
StrHTML=Replace(StrHTML,"ア","ア")
StrHTML=Replace(StrHTML,"ゲ","ゲ")
StrHTML=Replace(StrHTML,"ゴ","ゴ")
StrHTML=Replace(StrHTML,"ザ","ザ")
StrHTML=Replace(StrHTML,"ジ","ジ")
StrHTML=Replace(StrHTML,"ズ","ズ")
StrHTML=Replace(StrHTML,"ゼ","ゼ")
StrHTML=Replace(StrHTML,"ゾ","ゾ")
StrHTML=Replace(StrHTML,"ダ","ダ")
StrHTML=Replace(StrHTML,"ヂ","ヂ")
StrHTML=Replace(StrHTML,"ヅ","ヅ")
StrHTML=Replace(StrHTML,"デ","デ")
StrHTML=Replace(StrHTML,"ド","ド")
StrHTML=Replace(StrHTML,"バ","バ")
StrHTML=Replace(StrHTML,"パ","パ")
StrHTML=Replace(StrHTML,"ビ","ビ")
StrHTML=Replace(StrHTML,"ピ","ピ")
StrHTML=Replace(StrHTML,"ブ","ブ")
StrHTML=Replace(StrHTML,"ブ","ブ")
StrHTML=Replace(StrHTML,"プ","プ")
StrHTML=Replace(StrHTML,"ベ","ベ")
StrHTML=Replace(StrHTML,"ペ","ペ")
StrHTML=Replace(StrHTML,"ボ","ボ")
StrHTML=Replace(StrHTML,"ポ","ポ")
StrHTML=Replace(StrHTML,"ヴ","ヴ")
End If
EncodeJP=StrHTML
End Function


经测试——居然OK了!
===========================================
---------------------------------------------------
ガギグゲゴザジズゼゾダヂヅデドバビブベボパピプペポヴ
---------------------------------------------------

上面的日文会导致access在搜索时出错,就是内存溢出,在sql下不会有这种问题,要在access下解决的话在储存和读取时用下面的函数


储存
Function jp2code(CodeStr)
jp2code=Replace(Replace(Replace(Replace(Replace(CodeStr,"ガ","j30AC"),"ギ","j30AE"),"グ","j30B0"),"ゲ","j30B2"),"ゴ","j30B4")
jp2code=Replace(Replace(Replace(Replace(Replace(jp2code,"ザ","j30B6"),"ジ","j30B8"),"ズ","j30BA"),"ゼ","j30BC"),"ゾ","j30BE")
jp2code=Replace(Replace(Replace(Replace(Replace(jp2code,"ダ","j30C0"),"ヂ","j30C2"),"ヅ","j30C5"),"デ","j30C7"),"ド","j30C9")
jp2code=Replace(Replace(Replace(Replace(Replace(jp2code,"バ","j30D0"),"ビ","j30D3"),"ブ","j30D6"),"ベ","j30D9"),"ボ","j30DC")
jp2code=Replace(Replace(Replace(Replace(Replace(jp2code,"パ","j30D1"),"ピ","j30D4"),"プ","j30D7"),"ペ","j30DA"),"ポ","j30DD")
jp2code=Replace(jp2code,"ヴ","j30F4")
End Function



读取
[code]
Function code2jp(CodeStr)
code2jp=Replace(Replace(Replace(Replace(Replace(CodeStr,"j30AC","ガ"),"j30AE","ギ"),"j30B0","グ"),"j30B2","ゲ"),"j30B4","ゴ")
code2jp=Replace(Replace(Replace(Replace(Replace(code2jp,"j30B6","ザ"),"j30B8","ジ"),"j30BA","ズ"),"j30BC","ゼ"),"j30BE","ゾ")
code2jp=Replace(Replace(Replace(Replace(Replace(code2jp,"j30C0","ダ"),"j30C2","ヂ"),"j30C5","ヅ"),"j30C7","デ"),"j30C9","ド")
code2jp=Replace(Replace(Replace(Replace(Replace(code2jp,"j30D0","バ"),"j30D3","ビ"),"j30D6","ブ"),"j30D9","ベ"),"j30DC","ボ")
code2jp=Replace(Replace(Replace(Replace(Replace(code2jp,"j30D1","パ"),"j30D4","ピ"),"j30D7","プ"),"j30DA","ペ"),"j30DD","ポ")
code2jp=Replace(code2jp,"j30F4","ヴ")
End Function

相关文章推荐

Eclipse显示日文乱码解决方法

不是当前编码的文字的Eclipse中显示会是乱码,解决办法:          project->propertiesresource 修改 text file coding属性,如果Other下拉列...
  • yan8024
  • yan8024
  • 2011年05月03日 20:49
  • 9938

iOS内存错误EXC_BAD_ACCESS的解决方法

iOS内存错误EXC_BAD_ACCESS的解决方法(message sent to deallocated instance)》一文,转载于博客园之VicStudio的博客,以下为原文: iO...

iOS内存错误EXC_BAD_ACCESS的解决方法

iOS开发,最郁闷的莫过于程序毫无征兆地就崩溃了,用bt命令打出调用栈,给出的是一堆系统EXC_BAD_ACCESS的信息,根本没办法定位问题出现在哪里 iOS开发,最郁闷的莫过于程...

解决tomcat内存溢出的配置方法

  • 2014年07月18日 20:09
  • 189KB
  • 下载

myeclipse内存溢出解决方法

  • 2013年06月29日 15:45
  • 17KB
  • 下载

Access 出现 所有记录中均未找到搜索关键字 的解决方法!

今天要把我的网站生成静态的,后台得做一个管理模版的功能,其实挺简单的一个更新,就一条更新语句,来管理模版代码就完了,可是第一次修改的时候可以,第二次就会出现一个错误:"所有记录中均未找到搜索关键字" ...

oracle日语片假名转平假名函数

  • 2009年01月06日 14:39
  • 7KB
  • 下载

TOMCAT内存溢出之解决方法

  • 2008年05月28日 20:21
  • 23KB
  • 下载

mysql Access denied for user root@localhost错误解决方法总结(转)

mysql Access denied for user \'root\'@\'localhost\'”解决办法总结,下面我们对常见的出现的一些错误代码进行分析并给出解决办法,有需要的朋友可参考一下。...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:解决日文片假名导致 Access 搜索“内存溢出”的方法大全(转帖)
举报原因:
原因补充:

(最多只允许输入30个字)