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中使用这些内建函数。