Lm th? no ? l?y l?i ADO Recordset t? Oracle qua ASP s? d?ng REF CURSORS

D?ch tiu ? D?ch tiu ?
ID c?a bi: 255043
Bung t?t c? | Thu g?n t?t c?

? Trang ny

TM T?T

V?i vi?c pht hnh c?a Microsoft d? li?u truy c?p cc thnh ph?n (MDAC) 2.5, nh cung c?p c?a Microsoft OLEDB cho Oracle by gi? h? tr? s? tr? l?i c?a ActiveX Data Objects (ADO) recordsets t? m?t th? t?c Oracle thng qua vi?c s? d?ng cc ki?u con TR? REF. Ch?c nng ny ch? ?c h? tr? trong cc nh cung c?p c?a Microsoft OLEDB cho Oracle. ODBC Microsoft cho Oracle tr?nh i?u khi?n khng h? tr? vi?c s? d?ng cc REF CURSORS.

THNG TIN THM

Ch?c nng m?i ny c m?t s? l?i ch trn cc nh cung c?p tr?c :
  • Trong phin b?n tr?c c?a cc nh cung c?p cch duy nh?t ? l?y m?t recordset ADO t? m?t th? t?c Oracle ? tuyn b? m?t PL/SQL b?ng cho m?i c?t ?c tr? l?i vo recordset. V?i nh cung c?p m?i, b?n ch? c?n ph?i khai bo m?t con tr? chu?t REF, m sau x? l? s? tr? l?i c?a t?t c? cc c?t.

    ? thm thng tin v? cch truy xu?t Oracle th? t?c thng qua b?ng PL/SQL, b?m vo s? bi vi?t d?i y ? xem bi vi?t trong c s? ki?n th?c Microsoft:
    229919 Lm th? no ? l?y m?t Recordset t? Oracle s? d?ng ADO trn ASP
  • Trong phin b?n tr?c c?a cc nh cung c?p b?n ph?i ch? ?nh s? l?ng t?i a c?a h? s s? tr? l?i trong recordset. Khi b?n m? m?t recordset ADO d?a trn m?t con TR? REF, m b?c khng c?n c?n thi?t. V?i cc nh cung c?p m?i, b?n ch? c th? ch? ?nh 0 cho s? l?ng h? s. Vi du:

    {call curspkg_join.open_join_cursor1(?, {resultset 0, io_cursor})}
    					
S? d?ng m?u trong bi vi?t ny:
  • B?n nn ?c thnh th?o v?i Microsoft Visual Basic script Edition (VBScript), ActiveX Data Objects (ADO) v Active Server Pages (ASP).

  • N?u b?n s? t?o gi Oracle, b?n ph?i hi?u c?a Oracle th? t?c ng?/c?u trc Query Language.

  • C?a b?n Internet Information Server (IIS) ph?i ?c c?u h?nh ng ? lm vi?c v?i c s? d? li?u Oracle.

    ? bi?t thm chi ti?t, b?m vo s? bi vi?t d?i y ? xem bi vi?t trong c s? ki?n th?c Microsoft:
    193225 Lm th? no ? c?u h?nh IIS ? k?t n?i v?i Oracle t? ASP v ADO

M?u m?

M?u m? sau y ch?ng t? lm th? no ? l?y m?t recordset ADO t? m?t th? t?c Oracle thng qua m?t con TR? REF, v sau s? hi?n th? cc d? li?u trn m?t trang ASP. B?n ph?i s?a ?i cc chu?i k?t n?i OLEDB ?c s? d?ng trong m? ASP ? k?t n?i v?i c s? d? li?u Oracle.

  1. T?o ra cc gi ph?n m?m Oracle sau y trn h? ph?c v? Oracle tr?c khi b?n ch?y b?t k? m? ASP. Gi ny s? d?ng m?t s? b?ng ?c ?nh ngh?a trong gi?n ? Oracle Scott/Tiger. Gi?n ? Oracle Scott/Tiger ?c ci ?t v?i ci ?t Oracle m?c ?nh. Gi?n ? ny khng t?n t?i, b?n ph?i ch?y cc script b?ng sau y v a m?t s? thng tin vo cc b?ng:

    B?ng script
    CREATE TABLE DEPT
    (DEPTNO NUMBER(2,0) NOT NULL, 
    DNAME VARCHAR2(14) NULL, 
    LOC VARCHAR2(13) NULL,
    PRIMARY KEY (DEPTNO)
    );
    
    CREATE TABLE EMP
    (EMPNO NUMBER(4,0) NOT NULL, 
    ENAME VARCHAR2(10) NULL, 
    JOB VARCHAR2(9) NULL, 
    MGR NUMBER(4,0) NULL, 
    HIREDATE DATE NULL, 
    SAL NUMBER(7,2) NULL, 
    COMM NUMBER(7,2) NULL, 
    DEPTNO NUMBER(2,0) NULL,
    FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO),  
    PRIMARY KEY (EMPNO)
    );
    						

    Gi script
    CREATE OR REPLACE PACKAGE curspkg_join AS 
    	TYPE t_cursor IS REF CURSOR ; 
    	Procedure open_join_cursor1 (n_EMPNO IN NUMBER, io_cursor IN OUT t_cursor); 
    END curspkg_join;
    / 
    
    CREATE OR REPLACE PACKAGE BODY curspkg_join AS
    Procedure open_join_cursor1 (n_EMPNO IN NUMBER, io_cursor IN OUT t_cursor) 
    IS 
    	v_cursor t_cursor; 
    BEGIN 
    	IF n_EMPNO <> 0 
    	THEN
    		OPEN v_cursor FOR 
    		SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME 
    			FROM EMP, DEPT 
    			WHERE EMP.DEPTNO = DEPT.DEPTNO 
    			AND EMP.EMPNO = n_EMPNO;
    
    	ELSE 
    		OPEN v_cursor FOR 
    		SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME 
    			FROM EMP, DEPT 
    			WHERE EMP.DEPTNO = DEPT.DEPTNO;
    
    	END IF;
    	io_cursor := v_cursor; 
    END open_join_cursor1; 
    END curspkg_join;
    / 
    						
  2. Sau y l k?ch b?n ASP m cc cu?c g?i th? t?c tr?c:
    <%@ Language=VBScript %>
    <HTML>
    <HEAD>
    <META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
    </HEAD>
    <BODY>
    <P>
    <%
    	Const CONNECT = "Provider=MSDAORA.1;Data Source=your_tns_alias_name;User ID=scott;Password=tiger"
    		
    	Dim cn, rs, cmd, param
    	Dim SQL
    
    	set cn = Server.CreateObject( "ADODB.Connection" )
    	cn.Open CONNECT	
    
    	SQL = "{call curspkg_join.open_join_cursor1(?, {resultset 0, io_cursor})}"
    	set cmd = server.CreateObject ("ADODB.Command")
    	with cmd
    		set .ActiveConnection	= cn
    	    .CommandText			= SQL
    	    .CommandType			= 1		'adCmdText
    	    'Pass in 0 to retrieve all of the records
    	    set param = .CreateParameter("id", 131 , 1 , , 0)  '<<131=adNumeric, 1=adParamInput
    	    .Parameters.Append param
    	end with
    					
    	set rs = server.CreateObject ( "ADODB.Recordset" ) 
    	set rs = cmd.execute
    		    
    	%>
    	<TABLE WIDTH="80%" ALIGN=center BORDER=1 CELLSPACING=3 CELLPADDING=3>
    	<%
    	Response.Write ("<TR bgcolor=Gray>")
    	For i = 0 To rs.Fields.Count - 1
    		Response.Write ("<TD>" & rs.Fields(i).Name & "</TD>")
    	Next
    	Response.Write ("</TR>")
    	Do until rs.EOF
    		response.write ("<TR>")
    		for i = 0 to rs.Fields.Count - 1
    			If IsNull(rs(i).value) then 
    				Response.Write ("<TD> NULL </TD>")
    			else 
    				Response.Write ("<TD>" & rs(i).value & "</TD>")
    			end if 
    		next 
    		rs.MoveNext
    		response.write ("</TR>")
    	loop
    	%>
    	</TABLE>
    	<%
    	Set cmd = Nothing
    	Set param = Nothing
    	rs.close
    	Set rs = Nothing
    	cn.close
    	Set cn = Nothing
    %>
    </P>
    </BODY>
    </HTML>
    						

THAM KH?O

? bi?t thm chi ti?t v? MDAC, xin vui l?ng xem trang Web sau y:
http://msdn2.Microsoft.com/en-US/Data/aa937695.aspx
Ti li?u Oracle8 Enterprise Edition

Thu?c tnh

ID c?a bi: 255043 - L?n xem xt sau cng: 21 Thang Tam 2011 - Xem xt l?i: 2.0
T? kha:
kbhowto kboracle kbmt KB255043 KbMtvi
My d?ch
QUAN TRONG: Bi vi?t ny ?c d?ch b?ng ph?n m?m d?ch my c?a Microsoft ch? khng ph?i do con ng?i d?ch. Microsoft cung c?p cc bi vi?t do con ng?i d?ch v c? cc bi vi?t do my d?ch ? b?n c th? truy c?p vo t?t c? cc bi vi?t trong C s? Ki?n th?c c?a chng ti b?ng ngn ng? c?a b?n. Tuy nhin, bi vi?t do my d?ch khng ph?i lc no c?ng hon h?o. Lo?i bi vi?t ny c th? ch?a cc sai st v? t? v?ng, c php ho?c ng? php, gi?ng nh m?t ng?i n?c ngoi c th? m?c sai st khi ni ngn ng? c?a b?n. Microsoft khng ch?u trch nhi?m v? b?t k? s? thi?u chnh xc, sai st ho?c thi?t h?i no do vi?c d?ch sai n?i dung ho?c do ho?t ?ng s? d?ng c?a khch hng gy ra. Microsoft c?ng th?ng xuyn c?p nh?t ph?n m?m d?ch my ny.
Nh?p chu?t vo y ? xem b?n ti?ng Anh c?a bi vi?t ny:255043
Khc t Ni dung trong C s Kin thc Khng con c h tr
Bi vi?t ny ni v? cc s?n ph?m m Microsoft khng c?n h? tr? n?a. Do , bi vi?t ny ?c cung c?p "nguyn b?n" v s? khng ?c c?p nh?t.

Cung cp Phan hi

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com