VFP 愛用者社區 首頁 VFP 愛用者社區
本討論區為 Visual Foxpro 愛用者經驗交流的地方, 請多多利用"搜尋"的功能, 先查看看有無前例可循, 如果還有不懂的再發問. 部份主題有附加檔案, 須先註冊成為社區居民才可以下載.
 
 常見問題常見問題   搜尋搜尋   會員列表會員列表   會員群組會員群組   會員註冊會員註冊 
 個人資料個人資料   登入檢查您的私人訊息登入檢查您的私人訊息   登入登入

使用SPT技巧存取Oracle(轉貼)

 
發表新主題   回覆主題    VFP 愛用者社區 首頁 -> VFP 討論區
上一篇主題 :: 下一篇主題  
發表人 內容
Ruey



註冊時間: 2003-03-12
文章: 1698
來自: tunglo

第 1 樓

發表發表於: 星期五 五月 16, 2003 10:32 pm    文章主題: 使用SPT技巧存取Oracle(轉貼) 引言回覆

值得研究的一篇文章

Using Oracle SPs to Insert and Update Data from VFP

Summary

This demo code will connect to Oracle [using your ID and password] to create a table of Presidents, a trigger, a sequence and some stored procedures. The stored procedures can be called to insert and update data in the Presidents table. Three of the methods in the VFP class will pass data values to the Oracle procedures which will either be inserted into the table or update existing records in the table. The update SPs will also return a value equal to the number of records updated.

Description

The following demo code can be pasted into a Visual FoxPro PRG and run without modifications.



*!* To use this code, you must be able to connect to Oracle and
*!* have been granted the following Oracle privileges for the
*!* UserID you connected with:
*!*
*!* DROP ANY SEQUENCE
*!* DROP TABLE
*!* CREATE TABLE
*!* CREATE SEQUENCE
*!* CREATE PROCEDURE
*!* CREATE TRIGGER
*!*
*!* The UpdateMultiple and UpdateByKeyID methods show you how to
*!* get a Stored Procedure to stuff a value inta a VFP variable
*!* [i.e., passing a value by reference between an Oracle SP and VFP].
*!*
*!* The Stored Procedures created in this demo do not have a COMMIT; line
*!* after the INSERT or UPDATE SQL. When the connection is terminated,
*!* Oracle will perform an implicit COMMIT. The Destroy method below
*!* issues a SQLCOMMIT() if the lCommitOnDestroy property is TRUE.
*!*
CLEAR
LOCAL oDemo
oDemo = CREATEOBJECT('OracleDemo')
IF TYPE('oDemo') <> "O"
?
? 'No Connection. An Invalid UserID, Password and/or Server parameter was specified.'
RETURN
ENDIF
IF NOT oDemo.Cr8_Presidents()
oDemo.Release()
?
? 'Cr8_Presidents method failed.'
RETURN
ENDIF
IF NOT oDemo.Cr8_SProcs()
oDemo.Release()
?
? 'Cr8_SProcs method failed.'
RETURN
ENDIF
IF NOT oDemo.InsertData()
oDemo.Release()
?
? 'InsertData method failed.'
RETURN
ENDIF
?
? oDemo.UpdateMultiple()
?
? [The Updated_By column was changed from "InsTest" to "Test1"]
? [for Presidents with a LastName LIKE "Adams%"]
?
? [-----------------------------------------------------------]
?
? oDemo.UpdateByKeyID()
?
? [For the record with KeyID = 40:]
? [The FirstName column was changed from "George" to "GeorgE"]
? [The LastName column was changed from "Bush" to "Bush1"]
? [The Update_By column was changed from "InsTest" to "Test2"]
?
oDemo.Release()
RETURN


DEFINE CLASS OracleDemo AS Custom

nHandle = 0
cUserID = []
lCommitOnDestroy = .T.

PROTECTED PROCEDURE Init
LOCAL lcConString, lnOldValue
lnOldValue = SQLGETPROP(0, "DispLogin")
SQLSETPROP(0, "DispLogin", 2)
lcConString = [Driver=Microsoft ODBC for Oracle;UID=;PWD=;Server=;]
THIS.nHandle = SQLSTRINGCONNECT(lcConString)
SQLSETPROP(0, "DispLogin", lnOldValue)
IF THIS.nHandle > 0
THIS.GetUserID()
ENDIF
RETURN THIS.nHandle > 0
ENDPROC
PROCEDURE Release
RELEASE THIS
ENDPROC
PROTECTED PROCEDURE Destroy
IF THIS.nHandle > 0
IF THIS.lCommitOnDestroy
SQLCOMMIT(THIS.nHandle)
ENDIF
SQLDISCONNECT(THIS.nHandle)
ENDIF
ENDPROC
PROTECTED PROCEDURE GetUserID
SQLEXEC(THIS.nHandle, [select USER UserID from dual], [crsUser])
THIS.cUserID = ALLTRIM(crsUser.UserID)
USE IN crsUser
ENDPROC
PROCEDURE UpdateMultiple
LOCAL lcSQL, lnRetVal, laError(1)
PRIVATE pcNameMask pcUpdated_By, pnRowCount
pcNameMask = [Adams%]
pcUpdated_By = [TEST1]
pnRowCount = 0
TEXT TO lcSQL TEXTMERGE NOSHOW PRETEXT 2
begin <<THIS.cUserID>>.PrezUpdateAll
(nRowCount=>?@pnRowCount,
cNameMask=>?pcNameMask,
cUpdated_By=>?pcUpdated_By); end;
ENDTEXT
lcSQL = STRTRAN(lcSQL, CHR(13) + CHR(10), [ ])
IF THIS.RunSQL(lcSQL)
RETURN [UpdateMultiple RowCount: ] + TRANSFORM(pnRowCount)
ELSE
RETURN []
ENDIF
ENDPROC
PROCEDURE UpdateByKeyID
LOCAL lcSQL, lnRetVal, laError(1)
PRIVATE pnKeyID, pcLoginID, pcFirstName, pcLastName, pcUpdated_By, pnRowCount
pnKeyID = 40
pcLoginID = [GBUSH]
pcFirstName = [GeorgE]
pcLastName = [Bush1]
pcUpdated_By = [Test2]
pnRowCount = 0
TEXT TO lcSQL TEXTMERGE NOSHOW PRETEXT 2
begin <<THIS.cUserID>>.PrezUpdate
(nRowCount=>?@pnRowCount, nKeyID=>?pnKeyID, cLoginID=>?pcLoginID,
cFirstName=>?pcFirstName, cLastName=>?pcLastName,
cUpdated_By=>?pcUpdated_By); end;
ENDTEXT
lcSQL = STRTRAN(lcSQL, CHR(13) + CHR(10), [ ])
IF THIS.RunSQL(lcSQL)
RETURN [UpdateByKeyID RowCount: ] + TRANSFORM(pnRowCount)
ELSE
RETURN []
ENDIF
ENDPROC
PROCEDURE Cr8_Presidents
LOCAL lcScript, lnRetVal
TEXT TO lcScript TEXTMERGE NOSHOW PRETEXT 2
drop sequence S_Presidents
ENDTEXT
IF NOT THIS.RunSQL(lcScript)
RETURN .f.
ENDIF
TEXT TO lcScript TEXTMERGE NOSHOW PRETEXT 2
drop table Presidents
ENDTEXT
IF NOT THIS.RunSQL(lcScript)
RETURN .f.
ENDIF
TEXT TO lcScript TEXTMERGE NOSHOW PRETEXT 2
create table Presidents
(keyid number(4),
loginid varchar2(8),
lastname varchar2(30),
firstname varchar2(30),
updated_by varchar2(8))
ENDTEXT
IF NOT THIS.RunSQL(lcScript)
RETURN .f.
ENDIF
TEXT TO lcScript TEXTMERGE NOSHOW PRETEXT 2
create sequence S_Presidents start with 1 increment by 1 nocache
ENDTEXT
IF NOT THIS.RunSQL(lcScript)
RETURN .f.
ENDIF
TEXT TO lcScript TEXTMERGE NOSHOW PRETEXT 2
create or replace trigger Presidents_BEFORE_INSUPDT
before insert or update on <<THIS.cUserID>>.Presidents
for each row
declare
v_Id Number;
BEGIN
If :new.KeyID Is Null or :new.KeyID < 1 Then
select S_Presidents.nextval into v_Id from dual;
:new.KeyID := v_Id;
End If;
END;
ENDTEXT
IF NOT THIS.RunSQL(lcScript)
RETURN .f.
ENDIF
ENDPROC
PROCEDURE Cr8_SProcs
LOCAL lcScript, lnRetVal
TEXT TO lcScript NOSHOW PRETEXT 2
CREATE OR REPLACE PROCEDURE PrezInsert
(cLoginID IN Presidents.LoginID%Type,
cLastName IN Presidents.LastName%Type,
cFirstName IN Presidents.FirstName%Type,
cUpdated_By IN Presidents.Updated_By%Type) AS
BEGIN
INSERT INTO Presidents
(LoginID, LastName, FirstName, Updated_By)
VALUES
(cLoginID, cLastName, cFirstName, cUpdated_By);
END;
ENDTEXT
IF NOT THIS.RunSQL(lcScript)
RETURN .f.
ENDIF
TEXT TO lcScript NOSHOW PRETEXT 2
CREATE OR REPLACE PROCEDURE PrezUpdateAll
(cNameMask IN Presidents.LastName%Type,
cUpdated_By IN Presidents.Updated_By%Type,
nRowCount OUT NUMBER) AS
BEGIN
UPDATE Presidents
SET Updated_By = cUpdated_By
WHERE LastName LIKE cNameMask;
nRowCount := SQL%ROWCOUNT;
END;
ENDTEXT
IF NOT THIS.RunSQL(lcScript)
RETURN .f.
ENDIF
TEXT TO lcScript NOSHOW PRETEXT 2
CREATE OR REPLACE PROCEDURE PrezUpdate
(nRowCount OUT NUMBER,
nKeyID IN Presidents.KeyID%Type,
cLoginID IN Presidents.LoginID%Type,
cLastName IN Presidents.LastName%Type,
cFirstName IN Presidents.FirstName%Type,
cUpdated_By IN Presidents.Updated_By%Type) AS
BEGIN
UPDATE Presidents
SET LoginID = cLoginID,
LastName = cLastName,
FirstName = cFirstName,
Updated_By = cUpdated_By
WHERE KeyID = nKeyID;
nRowCount := SQL%ROWCOUNT;
END;
ENDTEXT
IF NOT THIS.RunSQL(lcScript)
RETURN .f.
ENDIF
ENDPROC
PROCEDURE InsertData
LOCAL laData(42, 3)
laData[ 1, 1] = 'GWASHING'
laData[ 1, 2] = 'Washington'
laData[ 1, 3] = 'George'
laData[ 2, 1] = 'JADAMS'
laData[ 2, 2] = 'Adams'
laData[ 2, 3] = 'John'
laData[ 3, 1] = 'TJEFFERS'
laData[ 3, 2] = 'Jefferson'
laData[ 3, 3] = 'Thomas'
laData[ 4, 1] = 'JMADISON'
laData[ 4, 2] = 'Madison'
laData[ 4, 3] = 'James'
laData[ 5, 1] = 'JMONROE'
laData[ 5, 2] = 'Monroe'
laData[ 5, 3] = 'James'
laData[ 6, 1] = 'JQADAMS'
laData[ 6, 2] = 'Adams'
laData[ 6, 3] = 'John Q'
laData[ 7, 1] = 'AJACKSON'
laData[ 7, 2] = 'Jackson'
laData[ 7, 3] = 'Andrew'
laData[ 8, 1] = 'MVBUREN'
laData[ 8, 2] = 'Van Buren'
laData[ 8, 3] = 'Martin'
laData[ 9, 1] = 'WHARRISO'
laData[ 9, 2] = 'Harrison'
laData[ 9, 3] = 'William'
laData[10, 1] = 'JTYLER'
laData[10, 2] = 'Tyler'
laData[10, 3] = 'John'
laData[11, 1] = 'JPOLK'
laData[11, 2] = 'Polk'
laData[11, 3] = 'James'
laData[12, 1] = 'ZTAYLOR'
laData[12, 2] = 'Taylor'
laData[12, 3] = 'Zachary'
laData[13, 1] = 'MFILLMOR'
laData[13, 2] = 'Fillmore'
laData[13, 3] = 'Millard'
laData[14, 1] = 'FPIERCE'
laData[14, 2] = 'Pierce'
laData[14, 3] = 'Franklin'
laData[15, 1] = 'JBUCHANA'
laData[15, 2] = 'Buchanan'
laData[15, 3] = 'James'
laData[16, 1] = 'ALINCOLN'
laData[16, 2] = 'Lincoln'
laData[16, 3] = 'Abe'
laData[17, 1] = 'AJOHNSON'
laData[17, 2] = 'Johnson'
laData[17, 3] = 'Andrew'
laData[18, 1] = 'UGRANT'
laData[18, 2] = 'Grant'
laData[18, 3] = 'Ulysses'
laData[19, 1] = 'RHAYES'
laData[19, 2] = 'Hayes'
laData[19, 3] = 'Rutherford'
laData[20, 1] = 'JGARFIEL'
laData[20, 2] = 'Garfield'
laData[20, 3] = 'James'
laData[21, 1] = 'CARTHUR'
laData[21, 2] = 'Arthur'
laData[21, 3] = 'Chester'
laData[22, 1] = 'GCLEVELA'
laData[22, 2] = 'Cleveland'
laData[22, 3] = 'Grover'
laData[23, 1] = 'BHARRISO'
laData[23, 2] = 'Harrison'
laData[23, 3] = 'Benjamin'
laData[24, 1] = 'WMCKINLE'
laData[24, 2] = 'McKinley'
laData[24, 3] = 'William'
laData[25, 1] = 'TROOSEVE'
laData[25, 2] = 'Roosevelt'
laData[25, 3] = 'Theodore'
laData[26, 1] = 'WTAFT'
laData[26, 2] = 'Taft'
laData[26, 3] = 'William'
laData[27, 1] = 'WWILSON'
laData[27, 2] = 'Wilson'
laData[27, 3] = 'Woodrow'
laData[28, 1] = 'WHARDING'
laData[28, 2] = 'Harding'
laData[28, 3] = 'Warren'
laData[29, 1] = 'CCOOLIDG'
laData[29, 2] = 'Coolidge'
laData[29, 3] = 'Calvin'
laData[30, 1] = 'HHOOVER'
laData[30, 2] = 'Hoover'
laData[30, 3] = 'Herbert'
laData[31, 1] = 'FROOSEVE'
laData[31, 2] = 'Roosevelt'
laData[31, 3] = 'Franklin'
laData[32, 1] = 'HTRUMAN'
laData[32, 2] = 'Truman'
laData[32, 3] = 'Harry'
laData[33, 1] = 'DEISENHO'
laData[33, 2] = 'Eisenhower'
laData[33, 3] = 'Dwight'
laData[34, 1] = 'JKENNEDY'
laData[34, 2] = 'Kennedy'
laData[34, 3] = 'John'
laData[35, 1] = 'LJOHNSON'
laData[35, 2] = 'Johnson'
laData[35, 3] = 'Lyndon'
laData[36, 1] = 'RNIXON'
laData[36, 2] = 'Nixon'
laData[36, 3] = 'Richard'
laData[37, 1] = 'GFORD'
laData[37, 2] = 'Ford'
laData[37, 3] = 'Gerald'
laData[38, 1] = 'JCARTER'
laData[38, 2] = 'Carter'
laData[38, 3] = 'James'
laData[39, 1] = 'RREAGAN'
laData[39, 2] = 'Reagan'
laData[39, 3] = 'Ronald'
laData[40, 1] = 'GBUSH'
laData[40, 2] = 'Bush'
laData[40, 3] = 'George'
laData[41, 1] = 'WCLINTON'
laData[41, 2] = 'Clinton'
laData[41, 3] = 'William'
laData[42, 1] = 'GWBUSH'
laData[42, 2] = 'Bush'
laData[42, 3] = 'George W'
LOCAL lnI, lcSQL
PRIVATE pcLoginID, pcLastName, pcFirstName, pcUpdated_By
pcUpdated_By = "InsTest"
FOR lnI = 1 TO 42
pcLoginID = laData(lnI, 1)
pcLastName = laData(lnI, 2)
pcFirstName = laData(lnI, 3)
lcSQL = "begin MARK.PrezInsert" ;
+ "(cLoginID=>?pcLoginID," ;
+ " cFirstName=>?pcFirstName," ;
+ " cLastName=>?pcLastName," ;
+ " cUpdated_By=>?pcUpdated_By); end;"
THIS.RunSQL(lcSQL)
ENDFOR
ENDPROC
PROTECTED PROCEDURE RunSQL
LPARAMETERS pcSQL
LOCAL lnRetVal
lnRetVal = SQLEXEC(THIS.nHandle, pcSQL)
IF lnRetVal < 0
AERROR(laError)
?
? pcSQL
?
DISPLAY MEMORY LIKE laErr*
ENDIF
RETURN lnRetVal > 0
ENDPROC
ENDDEFINE
回頂端
檢視會員個人資料 發送私人訊息
從之前的文章開始顯示:   
發表新主題   回覆主題    VFP 愛用者社區 首頁 -> VFP 討論區 所有的時間均為 台北時間 (GMT + 8 小時)
1頁(共1頁)

 
前往:  
無法 在這個版面發表文章
無法 在這個版面回覆文章
無法 在這個版面編輯文章
無法 在這個版面刪除文章
無法 在這個版面進行投票
無法 在這個版面附加檔案
無法 在這個版面下載檔案


Powered by phpBB © 2001, 2005 phpBB Group
正體中文語系由 phpbb-tw 維護製作