GetTable
This command executes the SQL statement on the remote server and copies the results to an existing local table.
Group: Server Extensions
Syntax:
[bOK =] Server.GetTable(sSQL, sLocalTable, [vKeyFields])
bOK (Boolean) Optional – is a return value; a value of True means the SQL statement processed normally.
sSQL (String) is the SQL statement to be sent to the database. As this is a pass through statement, its syntax must be understood by the database, as no pre-processing will occur.
sLocalTable (String) is the name of the table already created on the mobile device.
vKeyFields (Variant) Optional – is 1 or more key fields to represent a unique record. One key would be represented by “PartNo” and multiple keys look like “PartNo,Onhand”.
Example:
Dim bOK as Boolean
bOK = Server.GetTable("Select * from Inventory", "Inv2", "PartNo")
Note: In the event that you need to update data using the Key field you should pay close attention to how the table has been populated. To optimize update performance you should try to match the order in which the data was populated in the table initially. For instance assume table INVENTORY has a unique ID column. Also assume that you will need to update a small subset or rows on the device database using the Server.GetTable() language extension. You would want to initially populate the table using the GetTable function with a SQL statement similar to:
Server.GetTable("SELECT * FROM INVENTORY ORDER BY Id", "INVENTORY")
To optimize performance while updating records you would want to match the data that way it was downloaded by using the ORDER BY clause in your update like:
Server.GetTable("SELECT Id, Column1, Column2 FROM INVENTORY WHERE LastWrite> '07/22/2013' ORDER BY Id", "INVENTORY", "Id")
The ORDER BY clause will ensure that the records in the initial download and the updates are in the same order and will optimize the databases ability to seek to the next record to update.