VBA连接Oracle数据库的两种连接字符串的区别:MSDAORA-对象关闭时不允许操作

版权声明:本文为博主原创文章,未经博主允许也可随便转载。 https://blog.csdn.net/gtclulong/article/details/54235918

VBA连接数据库有两种连接字符串:

  dataSource = Chr(34) & "(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.10.101)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=mesdb)))" & Chr(34)
  '//连接字符串1
  cnn.ConnectionString = "Provider=OraOLEDB.Oracle;Password=pc;User ID=pcuser;Data Source=" & dataSource & ";Persist Security Info=true"
  '//连接字符串2
  cnn.ConnectionString = "Provider=MSDAORA.1; user id=pcuser; password=pc; data source = " & dataSource & "; Persist Security Info=True"

使用ADO查询数据库时,set rs=cnn.execute(sql)

我写了一个比较复杂的查询,用到了CTE,结果在PL/SQL中可以正常执行,使用连接字符串1也没问题。但切换到连接字符串2时就会出错,提示“对象关闭时不允许操作”,可是在执行没有CTE的Sql时是完全没有问题的。数据库oracle 11g R2

sql写的有点长:

with tempNo 
as 
(select B.No from 
   zg_aoi_dlist B,
   dm_wafer C
 where 1=1 
   and replace(upper(C.filename),'.CSV','')=B.waferid
   and C.product_Name like '%0620%'
   and C.load_seq=0
   and substr(C.specification,1,1)='C'
   and c.gross_die>1000
   and B.EndTime>='2017/01/06 08:00:00' and B.EndTime<'2017/01/08 08:00:00' )
select F.X,F.Y,E.CNT,F.SIO2PEEL,F.EMITTERCONTAM,F.FINGERSCRATCH,F.NFINGERPEEL,F.COLORDIFF,F.SIO2PEEL1,F.METALRESIDUE,F.DOUBLECHIP,
  F.PFINGERPEEL,F.ESDPOINT,F.COLORDIFF1,F.DOUBLECHIP1,F.NPADCONTAM,F.CHIPPING,F.COLORDIFF2,F.DOUBLECHIP2,F.PPADCONTAM,F.ITOPEEL,F.DOUBLECHIP3,F.PADPEEL,F.EPIDEFECT
from 
   ( select chipx,chipy,count(no) as CNT from zg_aoi_dlist_die D,
    (
      select max(A.rowid) as maxrow from zg_aoi_dlist_die A,
        tempNo B
        where 1=1
        and A.no=B.No
        group by A.no,chipx,chipy) C
    where D.rowid=C.maxrow
    group by D.chipx,D.chipy ) E,
  (
    select * from
    (   select to_number(ChipX) as X,to_number(ChipY) as Y, Defect_category,Count(*) as CNT 
        from zg_aoi_dlist_die A,
           TempNo B       
        where 1=1 
           and A.No=B.No
        group by to_number(ChipX),to_number(ChipY),Defect_category
           )
    pivot (sum(CNT) for( Defect_category) in  ('SiO2 Peeling (fx)' as SiO2Peel,'Emitter Contamination' as EmitterContam,'Finger Scratch' as FingerScratch,
    'N-Finger Peeling' as NFingerPeel,'Color Difference' as ColorDiff,'SiO2 Peeling(fx)' as SiO2Peel1,'Metal Residue' as MetalResidue,
    'Double Chip' as DoubleChip,'P-Finger Peeling' as PFingerPeel,'ESD Point' as ESDPoint,'NA' as NA,'Color different' as ColorDiff1,
    'Duoble chip' as DoubleChip1,'N-Pad Contamination' as NPadContam,'Chip Chipping' as Chipping,'Color differrent' as ColorDiff2,'Duoble Chip' as DoubleChip2,
    'P-Pad Contamination' as PPadContam,'ITO Peeling (fx)' as ITOPeel,'Double chip' as DoubleChip3,'Pad Peeling' as PadPeel,'Epitaxy Defect' as EpiDefect)) 
  ) F
where to_number(E.ChipX)=F.X and to_number(E.ChipY)=F.Y

虽然不知道为什么,感觉微软的MSDAORA支持的查询语句可能有些限制。以前出现过系统配置不当导致MSDAORA无法连接数据库的问题,不过又见到一次OraOLEDB.Oracle无法通过VBA连接的问题,而PLSQL可以正常连。电脑不是我自己的,所以也没法去研究了

展开阅读全文

对象关闭不允许操作

01-06

主程序主要部分如下(主要意思就是通过在输入框输入后,点击提交按钮,然后转向post.asp):[code=HTML][/code] rn <%rn if not request.Cookies("passed")="passed" thenrn response.Redirect"login.asp"rn response.end rn end ifrn rn rn phoneid=request.Cookies("phoneid")rn rn dim strSQL1,strSQL2,objRS1,objRS2rn strSQL1 = "Select * from user1 where phoneid='"&phoneid&"'"rn set objRS1 = getSQLRecordset(strSQL1,"db/user.mdb","user1")rn rn strSQL2 = "Select * from state where phoneid='"&phoneid&"'"rn set objRS2 = getSQLRecordset(strSQL2,"db/user.mdb","state")rn rn rn rn response.Cookies("phoneid")=phoneidrn rn rn rn rnrn %>rn rn rn rn rn rn [color=#FF0000]<% =objRS1("realname")%[/color]>rn rn rn <% =objRS2("state")%[/color]>rn <% =objRS2("shijian")%>rn rn rnrnrnrn rn rn rn rn 新鲜事rn rn rn rn rn rn rn rnpost。asp的代码主要如下(主要意思就是更新数据库中的内容后,然后再返回到主页面,并且重新显示最新的更新后的内容):rn[code=HTML][/code]rn<%rn rn rn phoneid=request.Cookies("phoneid")rn rn rn '定义变量,以便获取表单参数rn Dim newthing,currentTimern newthing= request("status")rn currentTime=now()rnrn 'SQL语句rn sql="update state set state='" & newthing& "',shijian='" & currentTime& "' where phoneid='"&phoneid&"'"rn rn [color=#FF0000]set objRS = getSQLRecordset(sql,"db/user.mdb","state")提示错误[/color]rn objRS.AddNew Array("state", "shijian"), _rn Array(newthing, currentTime)rn objRS.Update rn rn rn rn response.Cookies("phoneid")=phoneidrn response.Cookies("passed")="passed"rnrn rnrn '关闭数据集和数据库连接,并释放对象rn objRS.Close rn Set objRS = Nothing rn objConn.Close rn Set objConn = Nothingrnrn '重导向到系统主页面rn [color=#FF0000]Response.Redirect "main_test2.asp"[/color]rn Response.End()rn rn%> 论坛

没有更多推荐了,返回首页