Hacker’s Guide to Visual FoxPro
An irreverent look at how Visual FoxPro really works. Tells you the inside scoop on every command, function, property, event and method of Visual FoxPro.
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. |
|
Omitted or Empty |
Include all table types. |
|
|
cResultCursor |
Character |
The name of a cursor to contain the list of tables. |
|
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. |
|
"NATIVE" |
Return field information in the server's native structure format. |
|
|
cResultCursor |
Character |
The name of a cursor to contain the list of fields. |
|
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