*!* 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
