>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.

**************************************************
*-- Class:        cabase (f:\devel\src\libs\dal.vcx)
*-- ParentClass:  cursoradapter
*-- BaseClass:    cursoradapter
*-- Time Stamp:   10/12/06 09:46:09 AM
*
#INCLUDE "f:\devel\src\include\dal.h"
*
DEFINE CLASS cabase AS cursoradapter


	Height = 22
	Width = 23
	UseDeDataSource = .T.
	FetchSize = 1
	ConflictCheckType = 1
	MapVarchar = .T.
	*-- Set by the RefreshCursor() method.
	lrefreshed = .F.
	*-- The current setting of the SET DELETED statement.
	savedeleted = ""
	Name = "cabase"


	*-- Returns the UpdatableFieldList based on the cursor Alias.
	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


	*-- Returns the UpdateNameList based on the cursor Alias.
	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


	*-- Reloads the cursor with data from the data source based on current property settings.
	PROCEDURE refreshcursor
		* Two ways to figure out if the cursor was created and filled
		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		&& Update Conflict
		ENDIF
	ENDPROC


	PROCEDURE BeforeCursorFill
		LPARAMETERS luseCursorSchema, lNoDataOnLoad, cSelectCmd

		THIS.Parent.BeforeOpenTables()

		* Don't pull deleted records when using VFP tables
		* Pulling deleted records are not marked as deleted when they arrive in a cursor.
		SET DELETED ON

		DODEFAULT(luseCursorSchema, lNoDataOnLoad, cSelectCmd)
	ENDPROC


	PROCEDURE AfterCursorFill
		LPARAMETERS luseCursorSchema, lNoDataOnLoad, cSelectCmd, lResult

		DODEFAULT(luseCursorSchema, lNoDataOnLoad, cSelectCmd, lResult)

		* resets the SET DELETED to the value when the object was initialized
		LOCAL lcDeleted

		lcDeleted	= THIS.SaveDeleted

		SET DELETED &lcDeleted
	ENDPROC


ENDDEFINE
*
*-- EndDefine: cabase
**************************************************

An example of more specific needs. ReadOnly, but can easily be changed to ReadWrite.

**************************************************
*-- Class:        cacis_cipsbase (f:\devel\src\libs\dal.vcx)
*-- ParentClass:  cabase (f:\devel\src\libs\dal.vcx)
*-- BaseClass:    cursoradapter
*-- Time Stamp:   10/19/06 09:20:13 AM
*
#INCLUDE "f:\devel\src\include\dal.h"
*
DEFINE CLASS cacis_cipsbase AS cabase


	cip_code = ""
	*-- Used only in queries to find the active cip code for that year.
	acadyear = ""
	*-- Used during the query process.  Not assignable.  Use acadyear instead.
	HIDDEN eff_start_query_date
	eff_start_query_date = ""
	*-- Used during the query process.  Not assignable.  Use acadyear instead.
	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
			* Dynamic Where clause
			LOCAL lcWhere
			lcWhere	= ""

			*** CIP_Code ***
			IF !ISNULL(.CIP_Code) AND !EMPTY(.CIP_Code)
				lcWhere	= lcWhere + " AND CIP_Code "

				DO CASE
					CASE "%"$.CIP_Code OR "_"$.CIP_Code
						* SQL wild cards
						lcWhere	= lcWhere + " LIKE ?THIS.CIP_Code"

					CASE LEFT(.CIP_Code, 1) = "("
						* more than one element
						lcWhere	= lcWhere + " IN " + THIS.CIP_Code

					OTHERWISE
						* a single element
						lcWhere	= lcWhere + " = ?THIS.CIP_Code"
				ENDCASE
			ENDIF
			*** CIP_Code ***

			*** AcadYear ***
			IF !ISNULL(.AcadYear) AND !EMPTY(.AcadYear)
				* based on values seen in the reftable and the cis_cips view
				THIS.eff_start_query_date	= DATE(VAL(.AcadYear), 7, 1)		&& 2005/07/01
				THIS.eff_end_query_date		= DATE(VAL(.AcadYear)+1, 6, 30)		&& 2006/06/30

				*** eff_start_date ***
				lcWhere	= lcWhere + " AND eff_start_date <= ?THIS.eff_end_query_date"
				*** eff_start_date ***

				*** eff_end_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)"
				*** eff_end_date ***
			ENDIF
			*** AcadYear ***
		ENDWITH

		IF !EMPTY(lcWhere)
			cSelectCmd	= cSelectCmd + " WHERE " + SUBSTR(lcWhere, 5)
		ENDIF

		* most effective start date first in the collection
		* e.g. 2006/10/31 is before 2006/07/07
		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
			***<SelectCmd>
			TEXT TO THIS.SelectCmd NOSHOW
				SELECT * FROM CIS_CIPS
			ENDTEXT
			***</SelectCmd>

			* Defaults for Dynamic Where Clause
			.CIP_Code	= null
			.AcadYear	= null

			* Same as opening an empty table
			llRetVal	= .CursorFill(.F., .T.)

			* Read-Only
			STORE .F. TO .AllowDelete, .AllowInsert, .AllowUpdate
		ENDWITH

		RETURN llRetVal
	ENDPROC


ENDDEFINE
*
*-- EndDefine: cacis_cipsbase
**************************************************

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.