Rick - we need a change in the Query() method of wwBusinessObject...

You have some code in that method that tests if the SQL string does not begin with "SELECT", and you add it to the beginning of the passed SQL if it does not.

That is causing problems and I'll give you an example... I have a valid query that declares a SQL Variable at the top, then it runs the query. Well, the Query() method is adding "SELECT *" to the beginning of my valid SQL statement, but doing so breaks the SQL statement, making it invalid.

I can think of 2 possible fixes:

1. Add a property to the class to disable this behavior.
2. Add a small bit of new code on the existing line of the IF statement:

If !StartsWith(lcSelect, "SELECT ", .T.) And !StartsWith(lcSelect, "DECLARE ", .T.)
    lcSelect = "SELECT * " + lcSelect
EndIf

Here is my example that breaks your current method:

Text To lcSql TextMerge NoShow PreText 15
	
	Declare @Date1 as Date = '<<lcDate>>' ;

	Select Distinct part_id as id, PartNo, Parts.Description
	From JobItems
		Join Jobs on JobItems.Job_id = Jobs.id
		Join Parts on JobItems.part_id = Parts.id
	Where Jobs.Updated_at >= @Date1 or Parts.Updated_at >= @Date1 or
		  Jobs.Created_at >= @Date1 or Parts.Created_at >= @Date1
	Order By Id desc
	
EndText

The Query() method isn't really meant as a generic execution method. It's meant to be a shortcut selection method that explicitly is meant to fix up commands.

If you want to send a more complex command can't you just use Execute() to accomplish that?

+++ Rick ---

If you want to send a more complex command can't you just use Execute() to accomplish that?

The only issue I have with using Execute() is that it doesn't accept a Cursor name as a parameter, like Query() does.

FUNCTION Query(lcSelect, lcCursor, lnResultmode)

FUNCTION Execute(lcSQL)

Hmmm... yes that's a good point.

wwBusiness::Execute

************************************************************************
*  Execute
****************************************
***  Function: Executes a SQL command that returns one or more cursors.
***      Pass: lcSql     -  SQL Command to execute
***            lcCursor  - Optional - Cursor name for resultset(s)
***    Return: Record count or 0. Check .lError for errors
************************************************************************
FUNCTION Execute(lcSQL, lcCursor)
LOCAL lnResult

IF !THIS.OPEN()
    RETURN 0
ENDIF

IF EMPTY(lcCursor)
   lcCursor = this.cSqlCursor
ENDIF

THIS.lerror = .F.

DO CASE
CASE THIS.ndatamode = 0
    lcSQL = FlattenSql(lcSQL)

    *** Run the SQL Statement - note there's no INTO clause
    *** added by default to Fox data. If you need this you
    *** should use Query() instead or else make sure you
    *** always issue 'raw' SQL strings
    &lcSQL

    IF THIS.lerror
        lnResult = 0
    ELSE
        lnResult = _TALLY
    ENDIF
CASE THIS.ndatamode = 2
    THIS.osql.csqlcursor = lcCursor
    lnResult = THIS.osql.execute(lcSQL)

    *** Always reset the cursor afterwards
    THIS.csqlcursor = "TSQLQuery"

    IF lnResult < 0
        THIS.SetError(THIS.osql.cerrormsg)
        RETURN 0
    ENDIF

    lnResult = THIS.osql.nAffectedRecords
CASE THIS.ndatamode = 4
    THIS.ohttpsql.csqlcursor = lcCursor
    lnResult = THIS.ohttpsql.execute(lcSQL)
    IF THIS.ohttpsql.lerror
        THIS.SetError(THIS.ohttpsql.cerrormsg)
        RETURN 0
    ENDIF
    IF lnResult < 0
        THIS.ohttpsql.cerrormsg
        RETURN 0
    ENDIF
ENDCASE

RETURN lnResult
ENDFUNC
*   Execute

Note that this won't add an INTO clause to SELECT statements that are missing it if you run a FoxPro SELECT. For FoxPro you need to provide the INTO CURSOR command, as .Execute() expects a raw query to execute against the data engine.

+++ Rick ---

Yes, this will do it.

Thanks.

Rick, I noticed something in:

CASE THIS.nDataMode = 4

You do not set it back, like you did in CASE THIS.nDataMode = 2.

CASE THIS.nDataMode = 4 is missing:

    *** Always reset the cursor afterwards
    THIS.cSqlCursor = "TSQLQuery"

Thanks - I guess it was like that before, didn't look real close but did run a few tests with single and multiple cursors against SQL.

+++ Rick ---