程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle數據庫基礎 >> Oracle大文本在ASP中存取問題的解決

Oracle大文本在ASP中存取問題的解決

編輯:Oracle數據庫基礎

一、關於ADO

在基於ClIEnt/Server結構的數據庫環境中,通過OLE DB接口可以存取數據,但它定義的是低層COM接口,不僅不易使用,而且不能被VB,VBA,VBScript等高級編程工具訪問。而使用ADO則可以很容易地使VB等編程語言直接訪問數據(通過OLE DB接口)。ADO是基於面向對象方法的,ADO對象模型總共才包括六個對象,相對於數據訪問對象(DAO)來說簡單得多。因此實際中常常使用它來訪問數據庫。

二、ADO訪問數據庫實例

下面,我們以Oracle為例,使用VB6.0來訪問其數據庫中的存儲過程。在此例中,我們首先在Oracle數據庫上創建有兩個存儲過程,一個不帶參數,另一個帶有參數。然後,使用ADO來訪問這兩個存儲過程。步驟如下:

1. 在Oracle服務器上運行以下DDL腳本:

  

  DROP TABLE person; 

  CREATE TABLE person 

  (ssn NUMBER(9) PRIMARY KEY, 

  fname VARCHAR2(15), 

  lname VARCHAR2(20)); 

  INSERT INTO person VALUES(555662222,'Sam','Goodwin'); 

  INSERT INTO person VALUES(555882222,'Kent','Clark'); 

  INSERT INTO person VALUES(666223333,'Jane','Doe'); 

  COMMIT; 

  /
  

2. 在Oracle服務器上創建包(package):

  CREATE OR REPLACE PACKAGE packperson 

  AS 

  TYPE tssn is TABLE of NUMBER(10) 

  INDEX BY BINARY_INTEGER; 

  TYPE tfname is TABLE of VARCHAR2(15) 

  INDEX BY BINARY_INTEGER; 

  TYPE tlname is TABLE of VARCHAR2(20) 

  INDEX BY BINARY_INTEGER; 

  

  PROCEDURE allperson 

  (ssn OUT tssn, 

  fname OUT tfname, 

  lname OUT tlname); 

  PROCEDURE oneperson 

  (onessn IN NUMBER, 

  ssn OUT tssn, 

  fname OUT tfname, 

  lname OUT tlname); 

  END packperson; 

  /
  

3. 在Oracle服務器上創建以下包體(package body):

  CREATE OR REPLACE PACKAGE BODY packperson 

  AS 

  PROCEDURE allperson 

  (ssn OUT tssn, 

  fname OUT tfname, 

  lname OUT tlname) 

  IS 

  CURSOR person_cur IS 

  SELECT ssn, fname, lname 

  FROM person; 

  percount NUMBER DEFAULT 1; 

  BEGIN 

  FOR singleperson IN person_cur 

  LOOP 

  ssn(percount) := singleperson.ssn; 

  fname(percount) := singleperson.fname; 

  lname(percount) := singleperson.lname; 

  percount := percount + 1; 

  END LOOP; 

  END; 

  PROCEDURE oneperson 

  (onessn IN NUMBER, 

  ssn OUT tssn, 

  fname OUT tfname, 

  lname OUT tlname) 

  IS 

  CURSOR person_cur IS 

  SELECT ssn, fname, lname 

  FROM person 

  WHERE ssn = onessn; 

  percount NUMBER DEFAULT 1; 

  BEGIN 

  FOR singleperson IN person_cur 

  LOOP 

  ssn(percount) := singleperson.ssn; 

  fname(percount) := singleperson.fname; 

  lname(percount) := singleperson.lname; 

  percount := percount + 1; 

  END LOOP; 

  END; 

  END; 

  /
  

4. 在 VB 6.0 中打開一個新的工程,缺省創建表單 Form1。

5. 在表單上添加二個按鈕,cmdGetEveryone和cmdGetOne。

6. 在代碼窗口中添加以下代碼:

  

  Option Explicit 

  Dim Cn As ADODB.Connection 

  Dim CPw1 As ADODB.Command 

  Dim CPw2 As ADODB.Command 

  Dim Rs As ADODB.Recordset 

  Dim Conn As String 

  Dim QSQL As String 

  Dim inputssn As Long 

  

  Private Sub cmdGetEveryone_Click() 

  Set Rs.Source = CPw1 

  Rs.Open 

  While Not Rs.EOF 

  MsgBox "Person data: " & Rs(0) & ", 

  " & Rs(1) & ", " & Rs(2) 

  Rs.MoveNext 

  Wend 

  Rs.Close 

  End Sub 

  

  Private Sub cmdGetOne_Click() 

  Set Rs.Source = CPw2 

  inputssn = InputBox( 

  "Enter the SSN you wish to retrIEve:") 

  CPw2(0) = inputssn 

  Rs.Open 

  MsgBox "Person data: " & Rs(0) & " 

  , " & Rs(1) & ", " & Rs(2) 

  Rs.Close 

  End Sub 

  

  Private Sub Form_Load() 

  '使用合適的值代替以下用戶ID, 

  口令(PWD)和服務器名稱(SERVER) 

  Conn = "UID=*****;PWD=*****;driver=" _ 

  & "{Microsoft ODBC for 

  Oracle};SERVER=dSEOracle;" 

  Set Cn = New ADODB.Connection 

  '創建Connection對象 

  With Cn 

  .ConnectionString = Conn 

  .CursorLocation = adUseClIEnt 

  .Open 

  End With 

  QSQL = "{call packperson.allperson( 

  {resultset 9,ssn,fname,"_ 

  & "lname})}" 

  Set CPw1 = New ADODB.Command 

  '創建Command對象 

  With CPw1 

  Set .ActiveConnection = Cn 

  .CommandText = QSQL 

  .CommandType = adCmdText 

  End With 

  QSQL ="{call packperson.oneperson(?, 

  {resultset 2,ssn, "_ 

  & " fname,lname})}" 

  '調用存儲過程 

  Set CPw2 = New ADODB.Command 

  With CPw2 

  Set .ActiveConnection = Cn 

  .CommandText = QSQL 

  .CommandType = adCmdText 

  .Para

meters.Append.CreateParameter(   ,adInteger, _   adParamInput)   '添加存儲過程參數   End With   Set Rs = New ADODB.Recordset   With Rs   .CursorType = adOpenStatic   .LockType = adLockReadOnly   End With   End Sub      Private Sub Form_Unload(Cancel As Integer)   Cn.Close   Set Cn = Nothing   Set CPw1 = Nothing   Set CPw2 = Nothing   Set Rs = Nothing   End Sub

  

7. 運行程序。當點下cmdGetEveryone按鈕時,程序調用Oracle數據庫中不帶參數的存儲過程packperson.allperson,點下cmdGetOne按鈕時調用packperson.oneperson存儲過程。

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved