【技巧】解決ACCESS表中含有日文時(shí)使用like模糊搜索時(shí)出現(xiàn)“內(nèi)存溢出”

admin

解決ACCESS表中含有日文時(shí)使用like模糊搜索時(shí)出現(xiàn)“內(nèi)存溢出”，大家都知道，ACCESS有個(gè)BUG，那就是如果遇到日文或一些亂碼文字時(shí)在使用 like 進(jìn)行模糊搜索時(shí)就會(huì)出現(xiàn)“內(nèi)存溢出”的問題，提示“80040e14/內(nèi)存溢出”。

以前也遇到過了，不過那時(shí)沒怎么在意把含有日文的數(shù)據(jù)刪除了就好了，現(xiàn)在這次的都是些重要的資料不可能刪除的，就需要想辦法來解決了。

那導(dǎo)致出問題的26個(gè)日文：ゴ ガ ギ グ ゲ ザ ジ ズ ヅ デ ド ポ ベ プ ビ パ ヴ ボ ペ ブ ピ バ ヂ ダ ゾ ゼ

說一下解決辦法：

1、最笨的方法：刪除含有日文的數(shù)據(jù)
  
這就不用解釋了；

2、替換方法：

思路：就是存儲(chǔ)進(jìn)入數(shù)據(jù)庫時(shí)把那26個(gè)日文替換成別的編碼，在從數(shù)據(jù)庫取出來后在反替換回來；

從網(wǎng)上找了個(gè)現(xiàn)成的函數(shù)：
編碼:
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

3、處理SQL語句：
不要使用like語句，舉個(gè)例子：

會(huì)出問題的SQL：   where title like '%"&keyword&"%'

現(xiàn)在換成下面的就不會(huì)出現(xiàn)內(nèi)存溢出了：where InStr(1,LCase(Title),LCase('"&keyword&"'),0)<>0

摘自網(wǎng)絡(luò)。

補(bǔ)充：

當(dāng)access數(shù)據(jù)庫的字段內(nèi)包含了26個(gè)日文片假名字符任意一個(gè)時(shí),就會(huì)導(dǎo)致在執(zhí)行SQL語句中包含了[字段] like '%aaaaa%' 或 inStr(1,[字段],'aaaaa',1)類似查詢時(shí),出現(xiàn)了"Microsoft JET Database Engine 錯(cuò)誤 ‘80040e14'內(nèi)存溢出"的錯(cuò)誤。

這個(gè)問題在ASP中有一個(gè)折中的解決辦法就是將日文代碼轉(zhuǎn)換成對(duì)應(yīng)的代碼，比如：

•         source=Replace(source,"ガ","ガ")
• 
  

我們都知道ACCESS是ASP的親密伙伴。因?yàn)閮煞N最簡(jiǎn)單的東西碰在一起總能迸發(fā)出火花。然而，當(dāng)我們過濾不嚴(yán)格的時(shí)候經(jīng)常出現(xiàn)日文字符，這個(gè)時(shí)候搜索之后就會(huì)出現(xiàn)日文溢出。此時(shí)我們通常能想到的想法是找一個(gè)exe程序，把這個(gè)問題解決掉。我原來用C#寫過這樣一個(gè)類似的程序。Google里面應(yīng)該能找到我以前寫的那個(gè)。就不說了。后來某一天，我發(fā)現(xiàn)ASP就能實(shí)現(xiàn)。我當(dāng)時(shí)真的覺得自己太屎了。不過ASP的這種方法很不好。容易造成系統(tǒng)崩潰（如果數(shù)據(jù)庫大）。所以只是寫在這里。算是拋磚引玉。'下面我解釋一下下：Function TransferJapanDc9CnInDB() On Error Resume Next Err.Clear Dim objRS,i Set objRS=Server.CreateObject("ADODB.Recordset") objRS.CursorType = adOpenKeyset objRS.LockType = adLockReadOnly objRS.ActiveConnection=objConn objRS.Source="SELECT * FROM [blog_Comment]" objRS.Open() If (Not objRS.bof) And (Not objRS.eof) Then For i=1 to objRS.RecordCount'遍歷，看看有沒有日文，如果有，就溢出了，隨便搜索一個(gè)東西就行了。搜什么無所謂。因?yàn)槭潜闅vACCESS只要指針移動(dòng)到日文就溢出。 objConn.Execute("SELECT * FROM [blog_Comment] WHERE comm_ID="&objRS("comm_ID")&" AND [comm_Content] LIKE '%URL%'") If Err.Number=-2147217900 Then'因?yàn)槭莖n err繼續(xù) 所以這里找到溢出的錯(cuò)誤代碼，這個(gè)是自己debug出來的，實(shí)際上不是從什么地方找的。 objConn.Execute("UPDATE [blog_Comment] SET [comm_Content]='"&FilterSQL(Japan2Html(objRS("comm_Content")))&"',[comm_Author]='"&FilterSQL(Japan2Dc9CnHtml(objRS("comm_Author")))&"' WHERE comm_ID="&objRS("comm_ID")&" ")'這個(gè)就是把日文替換一下，思路是不是很巧妙。恩。不過數(shù)據(jù)庫龐大的化，反復(fù)溢出就會(huì)很出問題的。內(nèi)存寶寶會(huì)哭的。 Err.Clear End If objRS.MoveNext Next End If objRS.Close Set objRS=Nothing'   End FunctionFunction Japan2Dc9CnHtml(source) source=Replace(source,"ガ","ガ") source=Replace(source,"ギ","ギ") source=Replace(source,"ア","ア") source=Replace(source,"ゲ","ゲ") source=Replace(source,"ゴ","ゴ") source=Replace(source,"ザ","ザ") source=Replace(source,"ジ","ジ") source=Replace(source,"ズ","ズ") source=Replace(source,"ゼ","ゼ") source=Replace(source,"ゾ","ゾ") source=Replace(source,"ダ","ダ") source=Replace(source,"ヂ","ヂ") source=Replace(source,"ヅ","ヅ") source=Replace(source,"デ","デ") source=Replace(source,"ド","ド") source=Replace(source,"バ","バ") source=Replace(source,"パ","パ") source=Replace(source,"ビ","ビ") source=Replace(source,"ピ","ピ") source=Replace(source,"ブ","ブ") source=Replace(source,"ブ","ブ") source=Replace(source,"プ","プ") source=Replace(source,"ベ","ベ") source=Replace(source,"ペ","ペ") source=Replace(source,"ボ","ボ") source=Replace(source,"ポ","ポ") source=Replace(source,"ヴ","ヴ") Japan2Html=sourceEnd Function