Progress on VFP runtime, SQL functions

Those of you that watch our commits on Github may have already noticed it:
we are working on the SQL functions for FoxPro support.
What have have so far:

SQLConnect()
SQLStringConnect()
SQLDisconnect()

These three create a SqlStatement object with an embedded SqlConnection object. When the statement is marked as "Shared" then the Connection can be shared between multiple statements.
To get and set properties for these SQLStatements you can call

SQLSetProp()
SQLGetProp()

just like in FoxPro. Some properties are not used yet (such as the asynchronous support and manual transactions) but that will follow shortly.

SQLExec()
SQLPrepare()
SQLMoreResults()

These functions use the allocated statement and execute SQL queries. The results are stored in an in memory cursor (based on the DBFVFP Rdd, so all VFP data types are available). The cursor is backed by an in memory stream and a collection of object[] arrays. When you close the cursor (by calling the close command for example) all data is deleted, just like what happens in FoxPro.
If you disable the BatchMode property with SQLSetProp() then SQLExec() will only return the first resultset from a command that returns multiple result sets, and you need to retrieve the rest of the results by calling SQLMoreResults().
The results are returned in a real cursor so you can create (local) indexes on the cursor and also use a COPY to command to copy the data to a DBF and SCAN .. ENDSCAN to process the results.
You can also read and write from/to this cursor and Append and Delete rows.
We are NOT writing changes back at this moment.

SQLTables()
SQLColumns()
You can use these to query info from the tables and table columns on the backend database.
Btw did you know that SQLColumns() in "FOXPRO" mode returns column names with a length of 14 characters, but the maximum column length in a free table is 10. We are truncating the column names to 10 characters to be compatible with the normal FoxPro table limitations.

A new function that we have added and that you may like is

SqlSetProvider().
You can call this function with a parameter of "ODBC", "OLEDB" and "SQLServer". By doing that you can switch the system to another Ado.Net data provider. X# will come with 3 providers installed.
You can also pass in an object that implements the right interface and give us your own implementation of the Data Provider. We will provide instructions how to add other providers, such as MySQL, Oracle etc.

If you change the provider, then you may also want to change the connection string that you use to connect to the backend.
For example for an ODBC connection to a Northwind database on my machine the connection string would look like :

[code]DSN=Northwind;Trusted_Connection=Yes;[/code]

where the OleDb connection string would look like this:

[code]Provider=SQLNCLI11.1;Integrated Security=SSPI;Initial Catalog="Northwind";Data Source=(local)[/code]

and the SQL Server connection string would then look like this:

[code]server=(LOCAL);trusted_connection=Yes;database=Northwind;[/code]

Everything else works transparently, so this is the only changed need: You select a new provider and a new connection string, and voila: everything works as expected. We noticed that using the SQLServer provider is much faster than using the ODBC SQLServer driver and/or the OLEDB SQLServer driver

Like we said before, we are not ready yet, but things are going smoothly. The next things we will be working on are:

• Transaction support (SQLCommit(), SQLRollBack()
• Asynchronous support (SQLCancel())
• Writing changes back to the backend (Sending changes back to the server with UPDATE/DELETE/INSERT commands is already working of course)
• Parameterized statements

More news will follow shortly.
If you are reading this and have examples on how you are using Transactions and Asychronous support in VFP today, please send us some real world examples. We could not find a good working example on the Web.