VOOZH about

URL: https://www.cdata.com/kb/tech/outlook-odbc-foxpro.rst

⇱ Work with Outlook Data in FoxPro

Work with Outlook Data in FoxPro

👁 Jerod Johnson
Jerod Johnson
Director, Technology Evangelism
Load Outlook data into a FoxPro database. This article includes full code and a walk-through of the process.

The CData ODBC driver for Outlook enables you to access Outlook data using the ODBC standard. You can use the CData ODBC Driver for Outlook to integrate Outlook data into your FoxPro project. The procedure below provides a walk-through of the included code sample, which saves Outlook data into tables in a FoxPro database.

Connect to Outlook as an ODBC Data Source

If you have not already, first specify connection properties in an ODBC DSN (data source name). This is the last step of the driver installation. You can use the Microsoft ODBC Data Source Administrator to create and configure ODBC DSNs.

NOTE: Set the 'Map To WVarchar' connection property to FALSE (FoxPro uses the ODBC W API and returns the WCHAR for the value type).

Using OAuth Authentication

Microsoft Graph API uses OAuth 2.0 for authentication. You must register an application in the Microsoft Azure Portal to obtain OAuth credentials (Client ID and Client Secret).

Obtaining OAuth Credentials

  1. Log in to the Azure Portal.
  2. Navigate to Azure Active Directory > App registrations.
  3. Click New registration to create a new application.
  4. Enter an application name and select the appropriate account types.
  5. Set the Redirect URI to your application's callback URL (e.g., http://localhost:33333 for desktop apps).
  6. Click Register to create the application.
  7. On the application overview page, copy the Application (client) ID - this is your OAuthClientId.
  8. Navigate to Certificates & secrets and create a new client secret.
  9. Copy the client secret value - this is your OAuthClientSecret.
  10. Navigate to API permissions and add the required Microsoft Graph API permissions:
    • Mail.Read - For accessing email messages
    • Contacts.Read - For accessing contacts
    • Calendars.Read - For accessing calendar events
    • Tasks.Read - For accessing To Do tasks
    • offline_access - For obtaining refresh tokens
  11. Click Grant admin consent to grant these permissions.

Connecting with OAuth

After setting the following connection properties, you are ready to connect:

  • AuthScheme: Set this to OAuth.
  • InitiateOAuth: Set this to GETANDREFRESH. The CData API Profile for Outlook will automatically walk through the OAuth process in order to obtain the access token.
  • OAuthClientId: Set this to the Application (client) ID from Azure Portal.
  • OAuthClientSecret: Set this to the client secret value from Azure Portal.
  • TenantId: Set this to your Azure AD tenant identifier (GUID or domain name like 'contoso.onmicrosoft.com').
  • CallbackURL: Set this to the Redirect URI you specified in your app registration (e.g., http://localhost:33333 for desktop apps).

Example connection string

Profile=C:\profiles\Outlook.apip;AuthScheme=OAuth;InitiateOAuth=GETANDREFRESH;OAuthClientId=your_client_id;OAuthClientSecret=your_client_secret;TenantId=your_tenant_id;CallbackUrl=http://localhost:33333;

Connect and Transfer

To get started, add the code sample to a new project. You can execute the following command to save all tables without displaying them:

DO C:\Temp\sqldump.prg WITH "CData API Source", "TESTDB", .f.

Below is the process that the program follows:

  1. Create a new FoxPro database.

  2. Open a connection to Outlook data with the line below:

    m.hSQLconnection = SQLCONNECT(m.cODBCDSN)

  3. If the connection was successful, you can now retrieve the list of tables using the SQLTABLES function. The code below saves the list of tables in sys_tables.dbf:

    WAIT WINDOW "Fetching tables for data source '" + m.cODBCDSN + "'..." NOWAIT NOCLEAR
m.nSQLTABLES = SQLTABLES(m.hSQLconnection)

IF lUseDistinctConnections
 WAIT WINDOW "Closing ODBC Connection for data source '" + m.cODBCDSN + "'..." NOWAIT NOCLEAR
 SQLDISCONNECT(m.hSQLconnection)
 m.hSQLconnection = 0 * SQLCONNECT needs to be called again
ENDIF

IF m.nSQLTABLES > 0
 * create local table containing list of tables in db
 COPY TO (m.cImportData + "sys_tables")
 USE

 * convert each table to DBF
 USE (m.cImportData + "sys_tables")
 ALTER TABLE DBF("sys_tables") ;
 ADD COLUMN records i ;
 ADD COLUMN dbfname m
  4. Scan over each table, saving it to a DBF file. The function ODBCtoDBF stores the table in a DBF file and then opens a grid if the lBrowse parameter is set to true: 
    FUNCTION ODBCtoDBF (cTableName as String, cTableType as String, lBrowse as Logical, lhSQLConnection as Integer)

 WAIT WINDOW ;
 "Converting " + m.cTableType + ' "' + m.cTableName + '" to DBF...' ;
 NOWAIT NOCLEAR

 ACTIVATE SCREEN

 * strip characters incompatible with FoxPro out of the name of the DBF file
 m.cTableAlias		= ""
 m.nTableNameLen		= LEN(m.cTableName)
 m.nTableNameStart	= 1
 
 FOR m.nTableNameStart = 1 TO m.nTableNameLen
 m.cCharacter = SUBSTR(m.cTableName, m.nTableNameStart, 1)	
 IF !ISALPHA(m.cCharacter) .and. (m.nTableNameStart = 1 .or. !ISDIGIT(m.cCharacter))
 m.cTableAlias = m.cTableAlias + "_"
 ELSE
 m.cTableAlias = m.cTableAlias + m.cCharacter
 ENDIF
 ENDFOR

 m.cFromTable = m.cTableName
 m.cTempViewAlias	= m.cTableType + "_" + m.cTableAlias

 LOCAL loException as Exception
 LOCAL lnResultSets
 
 TRY
 * run query
 m.lnResultSets = SQLEXEC(m.lhSQLConnection, ;
 "SELECT * FROM " + m.cFromTable, ;
 m.cTempViewAlias, ;
 aSQLResult)
	 
 IF m.lnResultSets#1
 SET STEP ON
 ENDIF
	
 ACTIVATE SCREEN

 CATCH TO m.loException
 ACTIVATE SCREEN
 ? "Error opening " + m.cTempViewAlias + ":"
 ? m.loException.Message
 ? m.loException.Details
 ENDTRY

 IF !USED(m.cTempViewAlias)
 RETURN
 ENDIF

 * copy records from view cursor to disk
 SELECT * FROM (m.cTempViewAlias) ;
 INTO TABLE (m.cImportData + m.cTableAlias)

 IF USED(m.cTableAlias)

 ACTIVATE SCREEN

 FLUSH	&& flush the buffer to write the data to disk

 IF m.lBrowse
 * pop the table up on screen
 SELECT (m.cTableAlias)
 BROWSE NORMAL NOMODIFY NOWAIT
 ACTIVATE SCREEN
 ELSE
 * close on-disk table for now
 USE IN (m.cTableAlias)
 ACTIVATE SCREEN
 ENDIF
 ENDIF

 IF USED(m.cTempViewAlias)
 USE IN &cTempViewAlias && close SQL view
 ACTIVATE SCREEN
 ENDIF

 WAIT CLEAR

ENDFUNC
  5. You can now save tables of Outlook data as DBF files.

Below is the full code, in FoxPro 9 syntax: 

PARAMETERS cODBCDSN, cDatabaseName, lBrowseAfterConvert

*	cODBCDSN				The ODBC data source name, used for the name of the folder where the DBF tables are stored.
*	cDatabaseName			The name of the FoxPro database to use. This database is created if it does not exist. The default is DATABASE_NAME.
*								
*	lBrowseAfterConvert		If ".t." display each table with the BROWSE command after importing it. If ".f." close each table before moving on to the next one.

#define		ODBC_DATASOURCE		"DataSourceName"
#define		DATABASE_NAME		"odbcdata"

m.m_tpath = ".\" && root directory for data

ON ERROR
SET SAFETY OFF

IF VARTYPE(m.cODBCDSN)#"C" .or. EMPTY(m.cODBCDSN)
 m.cODBCDSN = ODBC_DATASOURCE
ENDIF
IF VARTYPE(m.cDatabaseName)#"C" .or. EMPTY(m.cDatabaseName)
 m.cDatabaseName = DATABASE_NAME
ENDIF

m.cImportData = m.cODBCDSN + "\"

CLOSE TABLES ALL
CLOSE DATABASES ALL

IF !DIRECTORY(m.cImportData)
	MKDIR (m.cImportData)
ENDIF

CREATE DATABASE (m.cDatabaseName)
CREATE CONNECTION (m.cDatabaseName) DATASOURCE (m.cODBCDSN) DATABASE (m.cDatabaseName)

WAIT WINDOW "Opening ODBC Connection for data source '" + m.cODBCDSN + "'..." NOWAIT NOCLEAR
m.hSQLconnection = SQLCONNECT(m.cODBCDSN)

IF m.hSQLconnection > 0
 * Connection successful; get list of tables
 WAIT WINDOW "Fetching tables for data source '" + m.cODBCDSN + "'..." NOWAIT NOCLEAR
 m.nSQLTABLES = SQLTABLES(m.hSQLconnection)

 IF m.nSQLTABLES > 0
 * Create local table containing list of tables in db
 COPY TO (m.cImportData + "sys_tables")
 USE

 * Convert each table to DBF
 USE (m.cImportData + "sys_tables")
 ALTER TABLE DBF("sys_tables") ;
 ADD COLUMN records	i ;
 ADD COLUMN dbfname	m

 SCAN	
 m.cCurrentTable	= TRIM(sys_tables.table_name)
 m.cCurrentTableType	= TRIM(sys_tables.table_type) && Valid values are "SYSTEMTABLE", "TABLE", and "VIEW".
 IF m.cCurrentTableType = "TABLE" or m.cCurrentTableType = "VIEW"

 ODBCtoDBF(m.cCurrentTable, m.cCurrentTableType, m.lBrowseAfterConvert, m.hSQLConnection)
 ELSE
 ACTIVATE SCREEN
 ? "ERROR:", m.cCurrentTable, "Couldn't open ODBC connection."
 =MESSAGEBOX("couldn't open data source " + m.cODBCDSN + " for table " + m.cCurrentTable + ".")
 ENDIF

 ENDSCAN
 BROWSE NORMAL NOWAIT

 ELSE
 =MESSAGEBOX("No tables found in data source " + m.cODBCDSN + ".")
 SET STEP ON
 ENDIF
 
 SQLDISCONNECT(m.hSQLconnection)
 
ELSE
 =MESSAGEBOX("Could not open data source " + m.cODBCDSN + ".")
 SET STEP ON
ENDIF

FUNCTION ODBCtoDBF (cTableName as String, cTableType as String, lBrowse as Logical, lhSQLConnection as Integer)

 WAIT WINDOW ;
 "Converting " + m.cTableType + ' "' + m.cTableName + '" to DBF...' ;
 NOWAIT NOCLEAR

 ACTIVATE SCREEN

 * Strip characters incompatible with FoxPro out of the name of the DBF file
 m.cTableAlias		= ""
 m.nTableNameLen		= LEN(m.cTableName)
 m.nTableNameStart	= 1
 
 FOR m.nTableNameStart = 1 TO m.nTableNameLen
 m.cCharacter = SUBSTR(m.cTableName, m.nTableNameStart, 1)	
 IF !ISALPHA(m.cCharacter) .and. (m.nTableNameStart = 1 .or. !ISDIGIT(m.cCharacter))
 m.cTableAlias = m.cTableAlias + "_"
 ELSE
 m.cTableAlias = m.cTableAlias + m.cCharacter
 ENDIF
 ENDFOR

 m.cFromTable = m.cTableName
 m.cTempViewAlias	= m.cTableType + "_" + m.cTableAlias

 LOCAL loException as Exception
 LOCAL lnResultSets
 
 TRY
 * Run query
 m.lnResultSets = SQLEXEC(m.lhSQLConnection, ;
 "SELECT * FROM " + m.cFromTable, ;
 m.cTempViewAlias, ;
 aSQLResult)
	 
 IF m.lnResultSets#1
 SET STEP ON
 ENDIF
	
 ACTIVATE SCREEN

 CATCH TO m.loException
 ACTIVATE SCREEN
 ? "Error opening " + m.cTempViewAlias + ":"
 ? m.loException.Message
 ? m.loException.Details
 ENDTRY

 IF !USED(m.cTempViewAlias)
 RETURN
 ENDIF

 * Copy records from view cursor to disk
 SELECT * FROM (m.cTempViewAlias) ;
 INTO TABLE (m.cImportData + m.cTableAlias)

 IF USED(m.cTableAlias)

 ACTIVATE SCREEN

 FLUSH	&& Flush the buffer to write the data to disk

 IF m.lBrowse
 * Display the table on screen
 SELECT (m.cTableAlias)
 BROWSE NORMAL NOMODIFY NOWAIT
 ACTIVATE SCREEN
 ELSE
 * Close on-disk table
 USE IN (m.cTableAlias)
 ACTIVATE SCREEN
 ENDIF
 ENDIF

 IF USED(m.cTempViewAlias)
 USE IN &cTempViewAlias && Close SQL view
 ACTIVATE SCREEN
 ENDIF

 WAIT CLEAR

ENDFUNC