>1) What is involved in connecting to an Oracle database? An exmaple would be nice.
>
>2) Can I get something like MSDE from Oracle? If so, where?
>
>Thanks
1. Work station will have the Oracle drivers already installed.
2. Be careful of the placement of the tnsnames.ora file. We have found that depending on where it is located in the path of the application it could behave differently.
e.g. c:\windows\tnsnames.ora
e.g. C:\Oracle\product\10.1.0\Client_1\network\ADMIN\tnsnames.ora
e.g. f:\app path\tnsnames.ora <--- this one will be first in line.
3. tnsnames.ora file will contain the exact location of the data server.
4. Setup a connection in your ODBC connection to test the connection.
a. Data Source Name (what will be used for a connection inside the application)
b. Description (whatever you want)
c. TNS Service Name - the reference to tnsnames.ora entry
d. User ID - a valid user id used to connect to Oracle
5. Test the connection.
That is the workstation setup. Now what version of FoxPro are you using. If the version you have uses CursorAdapters, I would suggest that. There is a security issue regarding the DCT file and a connection to databases such as Oracle. The DCT stores the password to the Oracle database as plain text. Any smart person can figure it out. CursorAdapters on the other hand, manage their own connections and do not rely on database containers; therefore you can't find the password in a data file.
1. Each table gets a CursorAdapter object (ReadWrite)
2. Each view gets a CursorAdapter object (ReadOnly)
This simplifies the requirements of data access.
The following is a base object for basically all CursorAdapter classes within my application. From here we make more objects which have more specific needs.
#INCLUDE "f:\devel\src\include\dal.h"
DEFINE CLASS cabase AS cursoradapter
Height = 22
Width = 23
UseDeDataSource = .T.
FetchSize = 1
ConflictCheckType = 1
MapVarchar = .T.
lrefreshed = .F.
savedeleted = ""
Name = "cabase"
PROCEDURE getupdatablefieldlist
LOCAL lnFields, lnLoop, lnSelect, lcList
lcList = ""
IF USED(THIS.ALIAS)
lnSelect = SELECT()
SELECT (THIS.ALIAS)
lnFields = FCOUNT()
FOR lnLoop = 1 TO lnFields
lcList = lcList + FIELD(lnLoop)
IF lnLoop < lnFields
lcList = lcList + ","
ENDIF
ENDFOR
SELECT (lnSelect)
ENDIF
RETURN lcList
ENDPROC
PROCEDURE getupdatenamelist
LOCAL lnFields, lnLoop, lnSelect, lcList
lcList = ""
IF USED(THIS.ALIAS)
lnSelect = SELECT()
SELECT (THIS.ALIAS)
lnFields = FCOUNT()
FOR lnLoop = 1 TO lnFields
lcList = lcList + FIELD(lnLoop) + " " + THIS.Tables + "." + FIELD(lnLoop)
IF lnLoop < lnFields
lcList = lcList + ","
ENDIF
ENDFOR
SELECT (lnSelect)
ENDIF
RETURN lcList
ENDPROC
PROCEDURE refreshcursor
THIS.lRefreshed = THIS.CursorFill()
RETURN THIS.lRefreshed
ENDPROC
PROCEDURE Init
LPARAMETERS tcAlias
THIS.Alias = tcAlias
THIS.SaveDeleted = SET("Deleted")
DODEFAULT()
ENDPROC
PROCEDURE AfterUpdate
LPARAMETERS cFldState, lForce, nUpdateType, cUpdateInsertCmd, cDeleteCmd, lResult
DODEFAULT(cFldState, lForce, nUpdateType, cUpdateInsertCmd, cDeleteCmd, lResult)
IF lResult AND _TALLY = 0 THEN
ERROR 1585
ENDIF
ENDPROC
PROCEDURE BeforeCursorFill
LPARAMETERS luseCursorSchema, lNoDataOnLoad, cSelectCmd
THIS.Parent.BeforeOpenTables()
SET DELETED ON
DODEFAULT(luseCursorSchema, lNoDataOnLoad, cSelectCmd)
ENDPROC
PROCEDURE AfterCursorFill
LPARAMETERS luseCursorSchema, lNoDataOnLoad, cSelectCmd, lResult
DODEFAULT(luseCursorSchema, lNoDataOnLoad, cSelectCmd, lResult)
LOCAL lcDeleted
lcDeleted = THIS.SaveDeleted
SET DELETED &lcDeleted
ENDPROC
ENDDEFINE
An example of more specific needs. ReadOnly, but can easily be changed to ReadWrite.
#INCLUDE "f:\devel\src\include\dal.h"
DEFINE CLASS cacis_cipsbase AS cabase
cip_code = ""
acadyear = ""
HIDDEN eff_start_query_date
eff_start_query_date = ""
HIDDEN eff_end_query_date
eff_end_query_date = ""
Name = "cacis_cipsbase"
PROCEDURE BeforeCursorFill
LPARAMETERS luseCursorSchema, lNoDataOnLoad, cSelectCmd
WITH THIS AS caCIS_CIPSBase OF dal
LOCAL lcWhere
lcWhere = ""
IF !ISNULL(.CIP_Code) AND !EMPTY(.CIP_Code)
lcWhere = lcWhere + " AND CIP_Code "
DO CASE
CASE "%"$.CIP_Code OR "_"$.CIP_Code
lcWhere = lcWhere + " LIKE ?THIS.CIP_Code"
CASE LEFT(.CIP_Code, 1) = "("
lcWhere = lcWhere + " IN " + THIS.CIP_Code
OTHERWISE
lcWhere = lcWhere + " = ?THIS.CIP_Code"
ENDCASE
ENDIF
IF !ISNULL(.AcadYear) AND !EMPTY(.AcadYear)
THIS.eff_start_query_date = DATE(VAL(.AcadYear), 7, 1)
THIS.eff_end_query_date = DATE(VAL(.AcadYear)+1, 6, 30)
lcWhere = lcWhere + " AND eff_start_date <= ?THIS.eff_end_query_date"
lcWhere = lcWhere + " AND (eff_end_date is null"
IF UPPER(RIGHT(THIS.Parent.cDataEnvironment, 4)) = ".DBC"
lcWhere = lcWhere + " OR eff_end_date = {}"
ENDIF
lcWhere = lcWhere + " OR eff_end_date >= ?THIS.eff_start_query_date)"
ENDIF
ENDWITH
IF !EMPTY(lcWhere)
cSelectCmd = cSelectCmd + " WHERE " + SUBSTR(lcWhere, 5)
ENDIF
cSelectCmd = cSelectCmd + " ORDER BY CIP_Code, Eff_Start_Date DESC"
DODEFAULT(luseCursorSchema, lNoDataOnLoad, cSelectCmd)
RETURN
ENDPROC
PROCEDURE AfterCursorFill
LPARAMETERS luseCursorSchema, lNoDataOnLoad, cSelectCmd, lResult
DODEFAULT(luseCursorSchema, lNoDataOnLoad, cSelectCmd, lResult)
IF USED(THIS.Alias)
SELECT (THIS.Alias)
INDEX ON ALLTRIM(cip_code)+DTOS(eff_start_date) TAG CANDIDATE_INDEX_NAME
SET ORDER TO
ENDIF
RETURN
ENDPROC
PROCEDURE Init
LPARAMETERS tcAlias
LOCAL llRetVal
DODEFAULT(tcAlias)
WITH THIS AS caCIS_CIPSBase OF dal
TEXT TO THIS.SelectCmd NOSHOW
SELECT * FROM CIS_CIPS
ENDTEXT
.CIP_Code = null
.AcadYear = null
llRetVal = .CursorFill(.F., .T.)
STORE .F. TO .AllowDelete, .AllowInsert, .AllowUpdate
ENDWITH
RETURN llRetVal
ENDPROC
ENDDEFINE
Hmmm come to think of it what we are missing is the actual connection... I will continue on in another section on the base object for a data environment object.