JET-SQL 是否支援 ACCESS APP的內建函數 比如 DSUM() ?

JET-SQL 是否支援 ACCESS APP的內建函數 比如 DSUM() ? 一直以為不能，結果上周在論壇，看到這個貼子，http://topic.csdn.net/u/20090119/15/44bb05d8-cd87-4861-8f4c-c9d3ec662d74.html

 

 表一
questionid  選項1  選項2  選項3  選項4
        1      S      D      I      C
        2      D      S      I      C
        3      I      D      S      C
        4      C      S      I      D
        5      S      I      C      D
        6      C      D      I      S
        
表二
nameid  name  questionid 選項
   001  小章          1  4
   001  小章          2  2
   001  小章          3  2
   001  小章          4  3
   001  小章          5  2
   001  小章          6  1
表三
tool  分數
   D  5
   I  6
   S  7
   C  8

要求得到的結果為
nameid  name  D總數  I總數  S總數  C總數  總分數
001      小章   1      2      1     2     40

 

由於沒有說明是在ASP+ACCESS環境下，所以直接在ACCESS環境中做了測試，

create table 表一
(
    questionid    integer,
    選項1    char,
    選項2    char,
    選項3    char,
    選項4    char
)

create table 表二
(
    nameid    varchar(4),
    name     varchar(20),
    questionid    integer,
    選項    integer
)

create table 表三
(
    tool    char,
    分數    integer
)

TRANSFORM Sum(q1.CNT) AS CNTOfSum
SELECT q1.nameid, q1.name
FROM (
    select a.nameid,a.name,a.questionid, Choose(選項,選項1,b.選項2,b.選項3,b.選項4) & '總數' as CATE,1 as CNT
    from 表二 a inner join 表一 b on a.questionid=b.questionid
    union all
    select a.nameid,a.name,a.questionid,'總分數' as CATE ,dlookup("分數","表三","tool='" & Choose(選項,選項1,b.選項2,b.選項3,b.選項4) & "'" )
    from 表二 a inner join 表一 b on a.questionid=b.questionid
) q1
GROUP BY q1.nameid, q1.name
PIVOT q1.CATE ;

nameid    name    C    D    I    S    總分數
001    小章    2    1    2    1    40

 

 

結果，發現要求是在ASP中，一直以為在JET-SQL中是不支援諸如 DUM() 這些ACCESS APPLITION 內建函數的。 本打算改成IIF 和 子查詢，不過心存僥倖，到原生IIS上試著用ASP試了一下這個語句，竟然能通過！！！。

 

幾天來一直很奇怪，因為記憶中這些函數以前開發的時候是不行的。 今天又測試了一下看看是不是因為本機安裝了ACCESS程式的原因。結果在伺服器上用ASP一試，果然報錯。

為什麼同樣的JET-SQL，有ACCESS的機器上可以？沒有ACCESS的不行？

 

利用 MS的 Filemon.exe來得到系統訪問了哪些檔案的log,結果沒有什麼異常，再用 Regmon.exe來看註冊表的訪問曆史，結果看到
dllhost.exe:2708 HKLM/SOFTWARE/ODBC/odbcinst.ini/Microsoft Access Driver (*.mdb)/setup C:/WINNT/system32/odbcjt32.dll

再比較兩台機器上的odbcjt32.dll

 

結果原生是, 版本是4.0.6200.0

 Directory of C:/WINNT/system32

06/21/2003  04:00a             270,608 odbcjt32.dll
               1 File(s)        270,608 bytes
               0 Dir(s)  35,632,940,032 bytes free

 

伺服器上的是, 4.0.6305.0, 版本反而高？！

 Directory of C:/WINNT/system32

02/17/2007  03:43 PM           278,559 odbcjt32.dll
               1 File(s)        278,559 bytes
               0 Dir(s)     347,160,576 bytes free

 

在繼續跟蹤，發現，原生訪問 c:/Program Files/Microsoft Office!!

 

原來如此。 就是在MS Office安裝的時候，會將增加一些註冊項指向ACCESS.EXE所在的目錄，這樣當系統調用 JET-SQL ODBC的時候，會利用OFFICE中的DLL來進行SQL語句的解析。 這樣也就解釋了為什麼在有OFFICE安裝的機器上可以用ACCESS的內建函數，但在WINDOWS基本配置上僅有的JET-SQL 驅動僅支援 JET-SQL列出的純量涵式。

 

結論
JET-SQL本身不支援ACCESS的內建函數。 但如果代碼啟動並執行環境中安裝了MS ACCESS則JET-SQL引擎會調用MS OFFICE中的組件來運行這些函數。故在開發的時候如確認將來的運行環境,

•   如有ACCESS安裝(如CS結構，現在WINDOWS使用者基本都安裝了OFFICE)，則可以使用這些函數。
•   如無法保證（如伺服器上），則應避免這些函數。用其它方法來替代實現。

考慮到系統將來的移植性，個人認為不推薦在ADO中使用這些內建函數。