Exzilla.net
Contact
Home -> Articles -> ADO and SP ->Using ADO and PL/SQL Table to retrieve a recordset from PL/SQL Stored Procedure for ASP Developer
 
Features
•Forums
•Files
•Blogs
Using ADO and PL/SQL Table to retrieve a recordset from PL/SQL Stored Procedure for ASP Developer
June 9, 2001
Cupid
Author's Bio | E-Mail
Copyright (C) 2001 exzilla.net

 

Using ADO and PL/SQL Table to retrieve a recordset from PL/SQL Stored Procedure for ASP Developer

Overview

ใน article นี้มีจุดประสงค์เพื่อ

    1. แสดงการดึงข้อมูลแบบ Recordset จาก PL/SQL Stored Procedure โดยที่การ Return จะมีType เป็น Table ของ PL/SQL
    2. แสดงการติดต่อการใช้งาน Oracle Database ด้วย ADO

สำหรับ ASP Developer

System Environment

    1. Microsoft ODBC for Oracle (2.573.2927), version 2.5
    2. Oracle Net8 8.1.7
    3. Oracle Database 8.1.7 for Microsoft Windows NT/2000
    4. Microsoft Windows 2000 with IIS5.0
    5. SQL*PLUS and Notepad

ตัวอย่างนี้ ไม่สามารถทำงานได้กับ Oracle ODBC Driver ต้องใช้ Microsoft ODBC for Oracle แทนนะครับ :)

Download

    1. Oracle Database Software

      2. สามารถ Download ได้จาก http://otn.oracle.com/

    2. MDAC 2.x Stack ,which includes the 2.573 Driver

สามารถ Download ได้จาก http://www.microsoft.com/data/

 

Step-by-Step to create a program

    1. Create sample table and data

      2. ในตัวอย่างนี้ผมขออนุญาตใช้ schema ของ scott ซึ่งมี table EMP ที่เป็น Demo table พร้อม data อยู่แล้วนะครับ

    2. Create the package Specification on Oracle database server

      3. เริ่มจากขั้นตอนแรก เราจะสร้าง Package Spec ที่ฝั่ง Database กันก่อน โดยใน Package จะประกอบด้วย Procedure อยู่ 2 Procedure ด้วยกัน Procedure แรกจะเป็นการยกตัวอย่างในการ return ค่าเพียงค่าเดียว ส่วน Procedure ที่สองนั้นจะ return ค่าที่ได้เป็นกลุ่มของข้อมูล

      เริ่มต้นด้วยการเรียกใช้งาน SQL Plus เพื่อสร้าง Package ดังนี้

      CREATE OR REPLACE PACKAGE getrecordset AS

      TYPE tempno is TABLE of NUMBER(4) INDEX BY BINARY_INTEGER;

      TYPE tename is TABLE of VARCHAR2(10) INDEX BY BINARY_INTEGER;

      TYPE tjob is TABLE of VARCHAR2(9) INDEX BY BINARY_INTEGER;

      PROCEDURE getallemp (empno OUT tempno, ename OUT tename, job OUT tjob);

      PROCEDURE getoneemp (oneempno IN NUMBER, empno OUT tempno,ename OUT tename, job OUT tjob);

      END getrecordset;

      /

       

    3. Create the package body on Oracle database server

      4. จากนั้นในส่วน Package body ก็จะเป็นส่วนของการ implement ของทั้งสอง Procedure ที่เป็น public procedure

      CREATE OR REPLACE PACKAGE BODY getrecordset AS

      PROCEDURE getallemp(empno OUT tempno, ename OUT tename, job OUT tjob) IS

      CURSOR emp_cur IS SELECT empno, ename, job FROM emp;

      empcount NUMBER DEFAULT 1;

      BEGIN

      FOR emprecord IN emp_cur LOOP

      empno(empcount) := emprecord.empno;

      ename(empcount) := emprecord.ename;

      job(empcount) := emprecord.job;

      empcount := empcount + 1;

      END LOOP;

      END;

      PROCEDURE getoneemp(oneempno IN NUMBER, empno OUT tempno, ename OUT tename, job OUT tjob) IS

      CURSOR emp_cur IS SELECT empno, ename, job FROM emp WHERE empno = oneempno;

      empcount NUMBER DEFAULT 1;

      BEGIN

      FOR emprecord IN emp_cur LOOP

      empno(empcount) := emprecord.empno;

      ename(empcount) := emprecord.ename;

      job(empcount) := emprecord.job;

      empcount := empcount + 1;

      END LOOP;

      END;

      END;

      /

       

    4. Create ASP program for call Stored Procedure and retrieve data

create connection Object

เริ่มจาก สร้าง ADODB.connection Object

Program ของเราในส่วนของ Connection ก็จะเป็นดังนี้

ConnStr = "UID=scott;PWD=tiger;DSN=mszeal;"

Set Conn = Server.CreateObject("ADODB.Connection")

With Conn

.ConnectionString = ConnStr

.CursorLocation = adUseClient

.Open

End With

 

ซึ่งก็คือ ต้องการ สร้าง connection เพื่อติดต่อ กับ Oracle Database โดยใช้ user ชื่อ Scott มี password เป็น tiger โดยผ่าน connection String ชื่อ mszeal ( ก่อนที่จะใช้งาน DSN ชื่อ mszeal อย่าลืมสร้าง DSN ชื่อmszeal ที่ต้องใช้ Microsoft ODBC for Oracle กันก่อนนะครับ )

Create command Object

ในตัวอย่างนี้เราสร้าง Command Object ขึ้นมา 2 object เพื่อเรียก Stored Procedure ดังนี้

Command Object ตัวแรก ถูกสร้างขึ้นเพื่อเรียก Stored Procedure ชื่อ GetAllEmp ซึ่ง Stored Procedure ตัวนี้ไม่ต้องใส่ Input parameter และจะ Return ค่า 3 Arrays คือ EmpNo, Ename, Job ส่วนในการกำหนด Resultset xx จะต้องมากกว่า หรือเท่ากับจำนวน Row ใน Cursor ซึ่งในตัวอย่างจะกำหนด xx ไว้เท่ากับ 20 เพราะคาดว่าจะไม่มีข้อมูลที่ Return มาเกิน 20 Records

SQLStr = "{call getrecordset.GetAllEmp({resultset 20, " _

& "empno, ename, job})}"

Set AllCmd = Server.CreateObject("ADODB.Command")

With AllCmd

Set .ActiveConnection = Conn

.CommandText = SQLStr

.CommandType = adCmdText

End With

 

Command Object ตัวที่สอง ถูกสร้างขึ้นเพื่อเรียก Stored Procedure ชื่อ GetOneEmp ซึ่งข้อมูลที่ Return มาจะมีเพียง Record เดียว แต่จุดที่แตกต่างกันกับ GetAllEmp คือ จะมีการรับค่า Input parameter เพื่อไปค้นหาข้อมูลที่ตรงกับ Input ที่เราต้องการ จึงต้องมีการเรียกใช้ Parameters.Append และ CreateParameter

SQLStr = "{call getrecordset.GetOneEmp(?,{resultset 2, " _

& " empno, ename, job})}"

Set OneCmd = Server.CreateObject("ADODB.Command")

With OneCmd

Set .ActiveConnection = Conn

.CommandText = SQLStr

.CommandType = adCmdText

.Parameters.Append .CreateParameter(, adInteger, adParamInput)

End With

(adCmdText, adInteger, adParamInput ต้องมีการอธิบายไว้ใน file ADOVBS.inc อยู่ด้วยนะครับ)

Create RecordSet Object

จากนั้น ก็สร้าง Recordset Object รอไว้เพื่อเป็นที่เก็บข้อมูลที่เกิดจาก execute Command Object

Set RecSet = Server.CreateObject("ADODB.Recordset")

With RecSet

.CursorType = adOpenStatic

.LockType = adLockReadOnly

End With

 

Calling GetAllEmp Procedure

ก็คงเหมือนการเขียนใช้งานอย่างปกติ เราก็จะนำ Data ในแต่ละ Row ซึ่งมีหลายColumn มาแสดงผลออกทาง Browser

Set RecSet.Source = AllCmd

RecSet.Open

While Not RecSet.EOF

response.write "Employee: " & RecSet(0) & ", " & RecSet(1) & ", " & _

RecSet(2)

response.write "<br>"

RecSet.MoveNext

Wend

RecSet.Close

Calling GetOneEmp Procedure

ครับ เช่นกัน เพียงแต่ GetOneEmp ต้องการ Input parameter เพื่อใช้ในการดึงข้อมูลที่ต้องการ ซึ่งในตัวอย่าง เราต้องการได้ข้อมูลของพนักงานที่มี Employee No. เท่ากับ 7902 จึง Assign ค่านี้ให้กับ OneCom object

Dim inputssn

Set RecSet.Source = OneCmd

'Get Employee detail for employee Number 7902

inputssn = 7902

OneCmd(0) = inputssn

RecSet.Open

response.write "Employee: " & RecSet(0) & ", " & RecSet(1) & ", " & _

RecSet(2)

RecSet.Close

 

Close and unset all variable

และก่อนจะจบ Program ก็อย่าลืม Clear ตัวแปรและ Connection ต่าง ๆ ด้วยนะครับ

Conn.Close

Set Conn = Nothing

Set AllCmd = Nothing

Set OneCmd = Nothing

Set RecSet = Nothing

 

Complete Sample code:

ตัวอย่าง Code ที่สมบูรณ์ ที่นี่ ครับ

  1. การสร้าง Package
  2. ASP Program เพื่อเรียกใช้ Package

References:

Article ID: Q176086 from http://support.microsoft.com/

Microsoft ODBC Driver for Oracle Help File

Keywords:

ADO, Stored Procedure, Table, PL/SQL, Recordset

 
 
 
{exzilla.net -- e-development QuickStart --}



Copyright (c) 2001-2005 - Exzilla.net -  All Rights Reserved.
Contact Us | Privacy Policy | Terms & Conditions