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 <>.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 <>.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
责任编辑：小草
