wwBusinessObject::query

High level query method that queries the underlying data store using SELECT statements. You can use partial SQL syntax (without using INTO) to retrieve query results that can be run on different SQL back ends.

For porting data between different data layers use this method for SQL query operations that can run against any backend.

This method can also automatically convert data into the vResult property, using a variety of different encoding formats based on the lnResultMode parameter:

  • JSON
  • XML
  • Encoded DBF/Memo
  • Collections
  • ADO Recordsets
o.Query(lcSelect, lcCursor, lnResultmode)

Return Value

number of records retrieved or -1 on error.

Parameters

lcSelect
The SQL statement to run.

lcCursor
Optional - Name of the cursor to return result to. Uses cSQLCursor if not set

lnResultmode
If specified converts a successful SQL result set into the following formats:

Cursor XML Modes
0 - VFP Cursor (just returns)
1 - XML from cursor
2 - XML from cursor with Schema
3 - XML from cursor with DTD
5 - XML from Cursor using VFPs CursorToXML
6 - XML from Cursor using VFPs CursorToXML with Schema

Object XML Modes:
11 - XML from oData member
12 - XML from oData member with Schema 13 - XML from oData member with DTD
20 - XML from object hierarchical (No Schema/DTD)

Json Modes:
50 - Raw unformatted Json
51 - Formatted Json

Miscellaneous Modes:
30 - ADO recordset from cursor (into vResult)
40 - Binary conversion of VFP cursor (EncodeDbf) (into vResult)
64 - FoxPro Collection of objects (into vResult)

Remarks

lcSqlCmd should be a SELECT statement as the class fixes up the SQL statement.

SELECT statements should not use an INTO clause since the method will generate it.

Running Query() without any parameter will return all data from the datasource (be careful)

You can pass multi-line FoxPro SQL statements without ; at the end to break lines. The method code fixes up line breaks and flattens the SQL statement to a single line when executing against the FoxPro data engine. The following is legal against a FoxPro database/table:

TEXT TO lcSQL NOSHOW
SELECT id, Company, Entered 
   FROM customers
   WHERE entered > DATETIME() - 365 * 24600
   ORDER BY Entered Desc
ENDTEXT

? loCustBus.Query(lcSql)

If you need to pass string literals with line breaks - don't! Always use named or positional SQL parameters!


See also:

Class wwBusinessObject
© West Wind Technologies, 2023 • Updated: 04/16/21
Comment or report problem with topic