>>>>
>>>>Any example of query that would not work both ways?
>>>>
>>>
>>>Here's a trivial example.
>>>
>>>VFP
>>>SELECT * FROM billing WHERE inv_date = {01/31/17}
>>>
>>>SQL Server
>>>SELECT * FROM billing where inv_date = '2017-01-31'
>>>
>>
>>
>>SELECT * FROM billing WHERE inv_date = ?({^2017-01-31})
>>
>>
>>will work both with VFP tables and cursors, either directly or through SPT, and with any SQL back-end (MSSQL, MySQL, Oracle, ...), won't require any intermediate storage, and will assure that date ambiguity won't occur.
>
>Yes.
>Good solution.
>However, would you write it that way if your backend were only SQL Server?
>My point is that using one code base for two backends requires this kind of code.
No, I would right specific code for specific back-ends, but that is true as soon as one targets different back-ends, because they can differ greatly (not only VFP vs MSSQL, but also MySQL vs MSSQL, or MSSQL 2008 vs MSSQL 2016, and so on).
Even if CursorAdapter is not used, this can be done hiding deep the actual SQL statements in the class hierarchy. Consider, for example, the case when the application needs to get the current date and time from the server:
#DEFINE MSSQL_DB .F.
#DEFINE MYSQL_DB .T.
LOCAL BackEnd AS String
LOCAL DataManager
m.BackEnd = ""
IF MSSQL_DB
m.BackEnd = "MSSQL"
ELSE
IF MYSQL_DB
m.BackEnd = "MySQL"
ENDIF
ENDIF
m.DataManager = CREATEOBJECT(m.BackEnd + "DataManager")
? m.DataManager.GetServerDateTime()
DEFINE CLASS DataManager AS Custom
ConnectionHandle = 0
FUNCTION GetServerDateTime
RETURN DATETIME()
ENDFUNC
FUNCTION ExecuteServerCommand (Statement AS String, OutputCursor AS String)
LOCAL CurrentWorkArea AS Integer
m.CurrentWorkArea = SELECT()
SQLEXEC(This.ConnectionHandle, m.Statement, m.OutputCursor)
SELECT(m.CurrentWorkArea)
ENDFUNC
ENDDEFINE
DEFINE CLASS SQLDataManager AS DataManager
ConnectionHandle = SQLCONNECT()
PROCEDURE Destroy
IF This.ConnectionHandle != -1
SQLDISCONNECT(This.ConnectionHandle)
ENDIF
ENDPROC
ENDDEFINE
DEFINE CLASS MSSQLDataManager AS SQLDataManager
FUNCTION GetServerDateTime
This.ExecuteServerCommand("SELECT GETDATE() AS ServerDateTime", "trash")
RETURN trash.ServerDateTime
ENDFUNC
ENDDEFINE
DEFINE CLASS MySQLDataManager AS SQLDataManager
FUNCTION GetServerDateTime
This.ExecuteServerCommand("SELECT NOW() AS ServerDateTime", "trash")
RETURN trash.ServerDateTime
ENDFUNC
ENDDEFINE
The application does not have to worry about which data back-end is being used. Once the data manager is instantiated, calling the data manager object may be safely unaware of which server is actually being used. In the case of the VFP data object, this wouldn't even require a SQL statement.
----------------------------------
António Tavares Lopes