CLASS:

ADOQuery

DESCRIPTION:

Represents an object that describes a SQL query and contains the resulting rowset.

SYNTAX:

[<oRef> =] new ADOQuery()

PROPERTY:

DEFAULT:

DESCRIPTION:

baseClassName

ADOQUERY

Identifies the object as an instance of the ADOQuery class 

className

ADOQUERY

Identifies the object as an instance of a custom class. When no custom class exists, defaults to baseClassName

name

Empty string 

The name of custom object

parent

null

Container form or report 

active

false

Whether the object is open and active or closed.

cacheSize

1

Number of result rows that are cached locally in memory.

commandTimeout

30

In seconds, how long to wait while executing a command before terminating the command and issuing and error.

connectionString

Empty string 

Connection statement that describes parameters used to establish a connection with data source.

connected

false

Whether the data object is connected with the data source.

cursorLocation

0 - Client

Location of the cursor service.

cursorType

4 - Static

Cursor type that can be used.

database

null

A reference to the ADODatabase object to which the object is assigned.

databaseName

Empty string 

Database alias name to which the object is assigned.

executeOptions

0 - Unspecified

Available options that can be used to execute command.

fetchOptions

0 - Unspecified

Available options that can be used to fetched records.

lockType

3 - Optimistic

Lock type that will be placed on rows during editing.

masterSource

null

Reference to data object that acts as master query and provides parameter values.

maxRows

0

Maximum number of result rows.

params

Object

Reference to Associative array that contains parameter names and values for the SQL statement.

parameters

Object

ADOParameter array of ADOParameter objects representing parameters in SQL statement

parameterMarkers

":@"

Character string containing list of symbols to scan to identify parameters within the sql statement

prepared

false

Whether to save a compiled version of a command before execution.

properties

AssocArray

A reference to Associative array that contains the dynamic properties.

rowset

Object

A reference to the ADORowset object that contains the result rows.

sql

Empty string 

SQL statement that describes the query.

state

0 - Closed

Enum that indicates the state object.

handle

0

ADO statement handle.

EVENT:

PARAMETERS:

DESCRIPTION:

canClose

Event fired when there’s an attempt to deactivate object; return value determines if the object is deactivated.

canOpen

Event fired when attempting to open object; return value determines if object is opened.

onClose

Event fired after object is successfully closed.

onOpen

Event fired after object is successfully opened.

onProgress

<percent expN>,<type expN>,<status expN>,<msg expC>

Event fired periodically during long-running data processing operations.

METHOD:

PARAMETERS:

DESCRIPTION:

cancel

Cancels execution of a pending, asynchronous method call.

close

Closes object.

open

Opens object.

requery

Re-executes the query regenerating the rowset.

updateBatch

cancelBatch

DESCRIPTION:

Use ADOQuery to access one or more tables in a data store using SQL statements.

The retrieve data resulting from an SQL statement that describes a query is stored in the ADORowset object.

EXAMPLES:

ADOQuery with Parameters

There are a couple of ways to use Parameters with ADOQuery in 8.0.0.3.

Most DBMS connections will be able to use positional parameter markers indicated

by a question mark (?).

Here are some examples of some connections to SQL Server using positional parmeters...

Example 1 - in Form.onOpen event.

   this.COMPANIES1 = new ADOQUERY(this)

   with (this.COMPANIES1)

      left = 14.7778

      top = 5.72

      databaseName = "SQLTEST"

      sql = "SELECT * FROM KathyTest.dbo.Companies where NAME = ? "

   endwith

   this.GRID1 = new GRID(this)

   with (this.GRID1)

      height = 12.32

      left = 3.8889

      top = 2.2

      width = 31.1111

   endwith

   this.rowset = this.companies1.rowset

   function form_onOpen

      with (this.COMPANIES1.parameters['NAME'])

         value = 'dBase, LLC.'

      endwith

      this.COMPANIES1.active = true

      this.grid1.dataLink =  this.COMPANIES1.rowset

      return   

Example 2 - in constructor code.

   this.COMPANIES1 = new ADOQUERY(this)

   with (this.COMPANIES1)

      left = 14.7778

      top = 5.72

      databaseName = "SQLTEST"

      sql = "SELECT * FROM Test.dbo.Companies where NAME = ? "

      with (params["NAME"])

         value = "dBase, LLC."

      endwith

      active = true

   endwith   

Example 3 - using ADOQuery.beforeConnect event.

   this.COMPANIES1 = new ADOQUERY(this)

   with (this.COMPANIES1)

      beforeConnect = {;this.parameters['NAME'].value = 'dBase, LLC.'}

      left = 17.1111

      top = 10.56

      databaseName = "SQLTEST"

      sql = "SELECT * FROM Test.dbo.Companies where NAME = ? "

      cursorLocation = 1 // Server

      active = true

   endwith  

You can use the named parameters in most circumstances as well.

This Example also sets up a Parent/Child relationship on SQL Server data using ADOQueries and named parameters and the masterSource property.

this.ORDERS1 = new ADOQUERY(this)

with (this.ORDERS1)

  left = 89

      top = 178

      databaseName = "MYSQLTEST_CA"

      sql = "SELECT * FROM orders"

      active = true

endwith

this.ORDERDETAILS1 = new ADOQUERY(this)

with (this.ORDERDETAILS1)

       left = 367

       top = 195

       databaseName = "MYSQLTEST_CA"

       sql = "SELECT * FROM orderdetails where orderNumber = :orderNumber"

       masterSource = form.orders1.rowset

       active = true

endwith   

Making ADOQuery object editable.

If your DBMS ADO/ODBC driver has the ability to set the cursorLocation to 'Server' you may be able to make your ADOQuery.rowset editable and not just readable.

For example the following connects to SQL Server and will make the Companies table editable in an ADOQuery object.

   this.COMPANIES1 = new ADOQUERY(this)

   with (this.COMPANIES1)

      left = 17.1111

      top = 10.56

      databaseName = "SQLTEST"

      sql = "SELECT * FROM Test.dbo.Companies"

      cursorLocation = 1 // Server

      active = true

   endwith   

If you are unable to use cursorLocation = 1 //Server -- then you can use ADOTable objects (which connect directly to table - NOT using sql) to edit your data on a form.

You will know if the cursorLocation is settable or not by simply trying it. If it does not hold after setting the object active=true, then it is not available.

Creating a Parent/Child relationship on data using masterSource

This Example sets up a Parent/Child relationship on SQL Server data using ADOQueries and named parameters and the masterSource property.

this.ORDERS1 = new ADOQUERY(this)

with (this.ORDERS1)

  left = 89

      top = 178

      databaseName = "MYSQLTEST_CA"

      sql = "SELECT * FROM orders"

      active = true

endwith

this.ORDERDETAILS1 = new ADOQUERY(this)

with (this.ORDERDETAILS1)

       left = 367

       top = 195

       databaseName = "MYSQLTEST_CA"

       sql = "SELECT * FROM orderdetails where orderNumber = :orderNumber"

       masterSource = form.orders1.rowset

       active = true

endwith