SQL Helper for VFP + SQL Server

Core Rules

1. Use Safe SQL Execution

Always wrap SQL execution with error handling and retry logic:

*-- CORRECT - With error handling
FUNCTION example_sql_execute(tcSQL, tcCursor)
    LOCAL lnResult, lnRetry
    lnRetry = 0
    DO WHILE lnRetry < 3
        lnResult = SQLEXEC(gl_nSQLHandle, tcSQL, tcCursor)
        IF lnResult >= 0
            RETURN lnResult
        ENDIF
        lnRetry = lnRetry + 1
        =INKEY(0.5)  && Wait before retry
    ENDDO
    log_error("SQL failed after 3 retries: " + tcSQL)
    RETURN -1
ENDFUNC

*-- WRONG - Raw SQLEXEC without handling
SQLEXEC(gl_nSQLHandle, "SELECT * FROM clients")  && No error handling!

2. Date vs Datetime (CRITICAL!)

*-- CORRECT
m.dmodified = Date()      && For DATE field
m.tlogon = Datetime()     && For DATETIME field

*-- WRONG - Will cause "Conversion failed"
m.dmodified = Datetime()  && ERROR! DATE field expects Date()

3. Single T-SQL Batch for Multi-Step Operations

*-- CORRECT - One batch, one roundtrip
lcSQL = "SET NOCOUNT ON; " + ;
    "DECLARE @newid INT; " + ;
    "BEGIN TRY " + ;
    "  BEGIN TRANSACTION; " + ;
    "  INSERT INTO dbo.orders (cclient) VALUES ('ABC'); " + ;
    "  SET @newid = SCOPE_IDENTITY(); " + ;
    "  INSERT INTO dbo.orderitems (idorder) VALUES (@newid); " + ;
    "  COMMIT TRANSACTION; " + ;
    "  SELECT @newid AS newid; " + ;
    "END TRY " + ;
    "BEGIN CATCH " + ;
    "  IF @@TRANCOUNT > 0 ROLLBACK; " + ;
    "  SELECT -1 AS newid, ERROR_MESSAGE() AS errmsg; " + ;
    "END CATCH"
example_sql_execute(lcSQL, "result")

*-- WRONG - Multiple roundtrips, risk of blocking
sql_begin_transaction()
example_sql_execute("INSERT INTO dbo.orders ...")    && Roundtrip 1
example_sql_execute("INSERT INTO dbo.orderitems ...") && Roundtrip 2 - may block!
sql_commit_transaction()                        && Roundtrip 3

4. SET NOCOUNT ON - Required!

*-- CORRECT - NOCOUNT ensures you get the SELECT result
lcSQL = "SET NOCOUNT ON; UPDATE ...; INSERT ...; SELECT @id AS newid"

*-- WRONG - Without NOCOUNT, UPDATE returns empty result set
lcSQL = "UPDATE ...; INSERT ...; SELECT @id AS newid"  && Result is empty!

5. ROWLOCK for Multi-User

*-- CORRECT - Lock only the row, not the table
example_sql_execute("UPDATE dbo.sessions WITH (ROWLOCK) SET lactive = 0 WHERE idsession = 123")

*-- WRONG - May lock entire table
example_sql_execute("UPDATE dbo.sessions SET lactive = 0 WHERE idsession = 123")

6. NULL Handling in Queries

*-- CORRECT - Use ISNULL or IS NULL
example_sql_execute("SELECT * FROM orders WHERE ISNULL(dshipped, '1900-01-01') < GETDATE()")

*-- Also correct - IS NULL check
example_sql_execute("SELECT * FROM orders WHERE dshipped IS NULL OR dshipped < GETDATE()")

*-- WRONG - NULL comparison always returns false
example_sql_execute("SELECT * FROM orders WHERE dshipped < GETDATE()")  && Misses NULLs!

7. Report Date Fields - CAST Required

ODBC returns SQL DATE as STRING in VFP. For reports, cast to DATETIME:

*-- CORRECT - For VFP reports
example_sql_execute("SELECT CAST(dbirthdate AS DATETIME) AS dbirthdate FROM dbo.persons", "rpt")

*-- WRONG - ODBC returns DATE as STRING, report formatting fails
example_sql_execute("SELECT dbirthdate FROM dbo.persons", "rpt")

8. INSERT with IDENTITY Return (Multi-User Safe)

*-- CORRECT - SCOPE_IDENTITY is session-safe
FUNCTION sql_insert_get_identity(tcTable, tcFields, tcValues)
    LOCAL lcSQL
    lcSQL = "SET NOCOUNT ON; " + ;
        "INSERT INTO dbo." + tcTable + " (" + tcFields + ") VALUES (" + tcValues + "); " + ;
        "SELECT SCOPE_IDENTITY() AS newid"
    example_sql_execute(lcSQL, "qryId")
    RETURN qryId.newid
ENDFUNC

*-- WRONG - @@IDENTITY may return ID from trigger insert!
example_sql_execute("INSERT INTO dbo.orders ...")
example_sql_execute("SELECT @@IDENTITY AS id", "result")  && May be wrong ID!

9. sql_exec Return Value is Numeric

*-- CORRECT
IF example_sql_execute(lcSql, "result") < 0
    log_error("Query failed")
ENDIF

*-- WRONG - Can't use ! operator on numeric
IF !example_sql_execute(lcSql)  && ERROR! "!" expects logical

10. String Escaping

*-- CORRECT - Escape single quotes
FUNCTION sql_quote(tcValue)
    RETURN STRTRAN(tcValue, "'", "''")
ENDFUNC

lcSQL = "INSERT INTO notes (ctext) VALUES ('" + sql_quote(m.ctext) + "')"

*-- WRONG - SQL injection risk
lcSQL = "INSERT INTO notes (ctext) VALUES ('" + m.ctext + "')"  && O'Brien crashes!

11. Date Formatting for SQL

*-- CORRECT - ISO format works universally
FUNCTION sql_format_date(tdDate)
    RETURN "'" + TRANSFORM(YEAR(tdDate)) + "-" + ;
        PADL(MONTH(tdDate), 2, "0") + "-" + ;
        PADL(DAY(tdDate), 2, "0") + "'"
ENDFUNC

*-- Usage
lcSQL = "SELECT * FROM orders WHERE dorder >= " + sql_format_date(DATE())

*-- WRONG - Locale-dependent, may fail
lcSQL = "SELECT * FROM orders WHERE dorder >= '" + DTOC(DATE()) + "'"

Buffered Cursor Pattern

*-- Open with buffering
FUNCTION example_open_buffered_table(tcTable, tcCursor, tlReadWrite)
    LOCAL lcSQL
    lcSQL = "SELECT * FROM dbo." + tcTable
    IF example_sql_execute(lcSQL, tcCursor) < 0
        RETURN .F.
    ENDIF

    IF tlReadWrite
        =CURSORSETPROP("Buffering", 5, tcCursor)  && Optimistic table buffering
        =CURSORSETPROP("Tables", "dbo." + tcTable, tcCursor)
        =CURSORSETPROP("KeyFieldList", "id" + tcTable, tcCursor)
        =CURSORSETPROP("UpdatableFieldList", "*", tcCursor)
        =CURSORSETPROP("UpdateNameList", ..., tcCursor)
        =CURSORSETPROP("SendUpdates", .T., tcCursor)
    ENDIF
    RETURN .T.
ENDFUNC

*-- Save changes
FUNCTION sql_tableupdate(tcCursor)
    IF !TABLEUPDATE(.T., .F., tcCursor)
        =TABLEREVERT(.T., tcCursor)
        RETURN .F.
    ENDIF
    RETURN .T.
ENDFUNC

*-- Close (auto-saves if dirty)
FUNCTION example_close_cursor(tcCursor)
    IF USED(tcCursor)
        IF CURSORGETPROP("Buffering", tcCursor) > 1
            =sql_tableupdate(tcCursor)
        ENDIF
        USE IN (tcCursor)
    ENDIF
ENDFUNC

Quick Reference

Common Errors

Resources

• VFPX Community
• SQL Server Documentation