West Wind Internet and Client Tools
Update needed in wwBusinessObject.Query() method...
Gravatar is a globally recognized avatar based on your email address. Update needed in wwBusinessObject.Query() method...
  Matt Slay
  All
  Apr 15, 2021 @ 09:11pm

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
Gravatar is a globally recognized avatar based on your email address. re: Update needed in wwBusinessObject.Query() method...
  Rick Strahl
  Matt Slay
  Apr 16, 2021 @ 04:34pm

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 ---

Gravatar is a globally recognized avatar based on your email address. re: Update needed in wwBusinessObject.Query() method...
  Matt Slay
  Rick Strahl
  Apr 16, 2021 @ 04:50pm

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)
Gravatar is a globally recognized avatar based on your email address. re: Update needed in wwBusinessObject.Query() method...
  Rick Strahl
  Matt Slay
  Apr 16, 2021 @ 05:54pm

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 ---

Gravatar is a globally recognized avatar based on your email address. re: Update needed in wwBusinessObject.Query() method...
  Matt Slay
  Rick Strahl
  Apr 16, 2021 @ 07:11pm

Yes, this will do it.

Thanks.

Gravatar is a globally recognized avatar based on your email address. re: Update needed in wwBusinessObject.Query() method...
  Matt Slay
  Rick Strahl
  Apr 16, 2021 @ 07:29pm

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"
Gravatar is a globally recognized avatar based on your email address. re: Update needed in wwBusinessObject.Query() method...
  Rick Strahl
  Matt Slay
  Apr 16, 2021 @ 08:06pm

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 ---

© 1996-2021