SQLTables(), SQLColumns()

These two functions let you collect information about the remote data being accessed via SQL Pass-Through. SQLTables() gives you a list of tables in the remote database, while SQLColumns() tells you about the fields of an individual table.

Usage

nSuccess = SQLTables( nConnectionHandle [, cTypeOfTables 
                      [, cResultCursor ] ] )

Parameter	Value	Meaning
nConnectionHandle	Numeric	The existing connection handle for the remote database being queried.
cTypeOfTables	Character	A comma-delimited list of table types. The list can include "View", "Table" and "System Table." Some servers support other types.
cTypeOfTables	Omitted or Empty	Include all table types.
cResultCursor	Character	The name of a cursor to contain the list of tables.
cResultCursor	Omitted	Put results in a cursor called SQLResult.
nSuccess	1	The command completed successfully.
	0	The command is still executing (in asynchronous mode).
	Negative	An error occurred.

The cursor created by SQLTables() has five fields. The most important one is the third, which contains the name of the table. The other fields provide information that doesn't have counterparts in FoxPro, like the table's owner and type.

The VFP documentation on the cTypeOfTables parameter is wrong in two respects. First, it states that the value must be in uppercase. In fact, any case works just fine. Second, it also insists that it must be delimited with single quotation marks, but that's true only if you specify more than one table type. Otherwise, any VFP delimiters ("", '', and []) work fine with the servers we tested.

Specifying "Table" for the cTypeOfTables parameter means you shouldn't get any system tables, right? Wrong! The SQL Server system table DTProperties is included in the cursor. Be sure to specifically ignore that one in your processing code.

Usage

nSuccess = SQLColumns( nConnectionHandle, cTable
                       [, cFormat  [, cResultCursor ] ] )

Parameter	Value	Meaning
nConnectionHandle	Numeric	The existing connection handle for the remote database being queried.
cTable	Character	The name of the table for which to return a list of fields (columns).
cFormat	"FOXPRO" or omitted	Return field information in FoxPro's usual structure format.
cFormat	"NATIVE"	Return field information in the server's native structure format.
cResultCursor	Character	The name of a cursor to contain the list of fields.
cResultCursor	Omitted	Put results in a cursor called SQLResult.
nSuccess	1	The command completed successfully.
	0	The command is still executing (in asynchronous mode).
	Negative	An error occurred.

The structure of the cursor created by SQLColumns() depends on what you pass for cFormat. With "FoxPro," you get a cursor with four columns containing field name, type, size and decimals, pretty much the usual, old-fashioned, structure information for FoxPro. (Seems to us it ought to have a lot more fields now to match the table structure used in Visual FoxPro.) When cFormat is "Native," the exact structure depends on the server—Help shows a fairly common structure. Using "Native" with a VFP source, for instance, will get you 12 fields, including information on the nullability of each column, and each field's comment from the DBC.Using SQLTables() and SQLColumns() together, you can build a local data dictionary for a remote database.

Example

nResult = SQLTables(nHandle, "", "TableList")
* Now use the names of the tables stored in TableList to 
* extract the columns of the table.
SCAN
    nResult = SQLColumns(nHandle, TableList.TableName)
ENDSCAN