Namespace: Consultingwerk.Util
Class 
QueryHelper Copy to Clipboard
Parent classes:
Inherits: Progress.Lang.Object

File:QueryHelper
Syntax:Static methods only, Protected constructor to disallow
instance creation
Author(s):Mike Fechner / Consultingwerk Ltd.
Created:Sat Jul 04 21:15:06 CEST 2009



Top Method Summary
Options Name Purpose
+ CHARACTER BufferWhereClause (character, character) Returns the complete query where clause for a specified buffer INCLUDING leading and trailing blanks. EXCLUDING commas and period.
+ HANDLE CloneQuery (handle) Clones the query by creating a query for additional buffers for the query buffers
+ INTEGER ColumnsBufferIndex (character, handle) Returns the Index of the Queries Buffer the column belongs to
+ HANDLE CreatePreparedQuery (handle) Creates, Prepares and Opens a new Query widget for the given buffer handle. The Query will be positioned at the first result row
+ HANDLE CreatePreparedQuery (handle, character) Creates, Prepares and Opens a new Query widget for the given buffer handle. The Query will be positioned at the first result row
+ DeleteQueryAndBuffers (handle) Deletes the Qeury and the buffer handles
+ GetCurrentRowids (handle, rowid[]) Returns a ROWID Array containing the rowids of the current Query result row.
+ HANDLE GetQueryFieldHandle (handle, character) Returns the handle of a field from a queries buffer
+ CHARACTER InsertExpression (character, character, AndOrEnum) Inserts an expression into ONE buffer's where-clause.
+ CHARACTER InsertExpression (character, character, character) Inserts an expression into ONE buffer's where-clause.
+ InsertIntoOrder (handle, character, character, integer) Inserts a free slot into a sequence of ordered records (e.g. OrderLine.LineNum). I.o.w. prepares a lists of records to insert a new record at the desired position
- CHARACTER InsertString (character, character, integer, integer) Inserts a String into and existing string
+ INTEGER NumberOfRowids (rowid[]) Returns the number of rowids assigned in a ROWID[]
+ OpenNonMatchingQuery (handle) Open a query that returns no records
+ PrepareQueryInReserveOrder (handle) Prepares a Query in reverse sort order
+ PrepareQueryInReverseOrder (handle) Prepares a Query in reverse sort order
+ CHARACTER QualifiedColumnName (character, handle) Returns the column name qualified with the first table name of the Queries buffers that contains the field name
+ CHARACTER QueryBufferNames (handle) Returns a comma delimited list of the Buffer Names in the Query
+ CHARACTER QueryLargeColumns (handle) Returns the large columns of the buffers from the given query handle
+ Consultingwerk.ListSortField QuerySortList (character) Returns the list of SortField instances for the given query string
+ CHARACTER QuerySortPhrase (character) Returns the sort phrase of the given query string
+ CHARACTER QueryStringEntries (character) Returns an array with the entries of a query string, uses "," as the delimiter, but does not consider comma's in quotes or brackets
+ LOGICAL RepositionToRowidArray (handle, rowid[]) Moves a query objects result list pointer to the row corresponding to the specified ROWID or ROWIDs. To reposition to a particular row when the query is a join, supply the ROWIDs of the buffers that correspond to the desired row.
+ CHARACTER SetQuerySort (character, character) Sets or replaces the sort string (BY phrase) in the provided query string
+ CHARACTER SetUseIndex (character, character) Inserts a USE-INDEX option into a query string. Defaults to using the first buffer criteria for adding the query string
+ CHARACTER SetUseIndex (character, integer, character) Inserts a USE-INDEX option into a query string
+ ValidateQuery (character, character) Validates a query string and returns an error when the query string cannot be prepareed
+ ValidateQuery (character, character, character) Validates a query string and returns an error when the query string cannot be prepareed
- CHARACTER WhereClauseBuffer (character) Returns the buffername of a where clause expression.

Top Constructor Summary
Options Name Purpose
# QueryHelper () Protected default constructor.


Method Detail
Top

PUBLIC CHARACTER BufferWhereClause (character, character)

Purpose: Returns the complete query where clause for a specified buffer
INCLUDING leading and trailing blanks.
EXCLUDING commas and period.
Notes: This is supported as a 'utility function' that doesn't use any
properties.
- RETURNs the expression immediately when found.
RETURNs '' at bottom if nothing is found.

Parameters:
pcBuffer CHARACTER
The buffer name to return the where clause from
pcWhere CHARACTER
The complete query:prepare-string
Returns CHARACTER
The complete query where clause for the specified buffer
Top

PUBLIC HANDLE CloneQuery (handle)

Purpose: Clones the query by creating a query for additional buffers
for the query buffers
Notes: The caller is responsible for deleting the query and buffer handles

Parameters:
phSourceQuery HANDLE
The handle of the source query
Returns HANDLE
The handle of the cloned query
Top

PUBLIC INTEGER ColumnsBufferIndex (character, handle)

Purpose: Returns the Index of the Queries Buffer the column belongs to
Notes:

Parameters:
pcColumnName CHARACTER
The name of the Column to return the Index for
phQuery HANDLE
The handle of the query
Returns INTEGER
The index of the queries buffer the column belongs to
Top

PUBLIC HANDLE CreatePreparedQuery (handle)

Purpose: Creates, Prepares and Opens a new Query widget for the given
buffer handle. The Query will be positioned at the first result row
Notes: You need to manually delete the query widget. No garbage collection
is provided for query widgets.

Parameters:
phBuffer HANDLE
The buffer handle
Returns HANDLE
The handle of the prepared query
Top

PUBLIC HANDLE CreatePreparedQuery (handle, character)

Purpose: Creates, Prepares and Opens a new Query widget for the given
buffer handle. The Query will be positioned at the first result row
Notes: You need to manually delete the query widget. No garbage collection
is provided for query widgets.

Parameters:
phBuffer HANDLE
The buffer handle
pcQueryString CHARACTER
The prepare string for the query
Returns HANDLE
The handle of the prepared query
Top

PUBLIC DeleteQueryAndBuffers (handle)

Purpose: Deletes the Qeury and the buffer handles
Notes:

Parameters:
phQuery HANDLE
The handle of the query to delete
Top

PUBLIC GetCurrentRowids (handle, rowid[])

Purpose: Returns a ROWID Array containing the rowids of the current
Query result row.
Notes: Can be used with reposition to rowid

Parameters:
phQuery HANDLE
The handle of the query
prRowids ROWID
OUTPUT The array of the rowid of each query buffer
Top

PUBLIC HANDLE GetQueryFieldHandle (handle, character)

Purpose: Returns the handle of a field from a queries buffer
Notes: Field names to be specified as Buffername.Fieldname

Parameters:
phQuery HANDLE
The handle to the Query widget
pcFieldName CHARACTER
the name of the buffer field in the form Buffername.Fieldname
Returns HANDLE
The handle to the buffer field specified
Top

PUBLIC CHARACTER InsertExpression (character, character, AndOrEnum)

Purpose: Inserts an expression into ONE buffer's where-clause.
Notes: - The new expression is embedded in parenthesis and a parentheses
is also placed around the existing one, which is different.
than the original ADM's version which is just appended
This would be problematic in adm, as it would be to many
parenthesises.
(could be improved to check for any OR and avoid parenthesis if not
found )
- Lock keywords must be unabbreviated or without -lock (i.e. SHARE
or EXCLUSIVE.)
- Any keyword in comments may cause problems.
Creates unpredictable (false) results when used with a query string
on multiple buffers

Parameters:
pcWhere CHARACTER
The complete where clause with or without the FOR keyword, but without any comma before or after
pcExpression CHARACTER
The new expression OR OF phrase (Existing OF phrase is replaced)
poAndOr Consultingwerk.AndOrEnum
The AndOrEnum value that specifies which operator is used to add the new expression to existing ones, AND or OR
Returns CHARACTER
The new query string
Top

PUBLIC CHARACTER InsertExpression (character, character, character)

Purpose: Inserts an expression into ONE buffer's where-clause.
Notes: - The new expression is embedded in parenthesis and a parentheses
is also placed around the existing one, which is different.
than the original ADM's version which is just appended
This would be problematic in adm, as it would be to many
parenthesises.
(could be improved to check for any OR and avoid parenthesis if not
found )
- Lock keywords must be unabbreviated or without -lock (i.e. SHARE
or EXCLUSIVE.)
- Any keyword in comments may cause problems.
Creates unpredictable (false) results when used with a query string
on multiple buffers

Parameters:
pcWhere CHARACTER
The complete where clause with or without the FOR keyword, but without any comma before or after
pcExpression CHARACTER
The new expression OR OF phrase (Existing OF phrase is replaced)
pcAndOr CHARACTER
Specifies what operator is used to add the new expression to existing ones, AND (default) or OR
Returns CHARACTER
The new query string
Top

PUBLIC InsertIntoOrder (handle, character, character, integer)

Purpose: Inserts a free slot into a sequence of ordered records (e.g.
OrderLine.LineNum). I.o.w. prepares a lists of records to insert
a new record at the desired position
Notes:

Parameters:
phBufferHandle HANDLE
The handle of the buffer to insert a record
pcQueryPredicate CHARACTER
The query predicate (without FOR EACH <buffer name>)
pcSortField CHARACTER
The field by which the sequence is ordered
piInsertSequence INTEGER
The sequence number to make available
Top

PRIVATE CHARACTER InsertString (character, character, integer, integer)

Purpose: Inserts a String into and existing string
Notes:

Parameters:
pcString CHARACTER
The string to insert into the target string
pcTargetString CHARACTER
The string to insert into
piPos INTEGER
The position to insert into
piLength INTEGER
The length of the string to insert to
Returns CHARACTER
The resulting string
Top

PUBLIC INTEGER NumberOfRowids (rowid[])

Purpose: Returns the number of rowids assigned in a ROWID[]
Notes: Will stop counting at the first ROWID = ?, useful for REPOSITION
operations

Parameters:
prRowids ROWID
The ROWID[] to return the effective number of rowids from
Returns INTEGER
The effective number of rowids
Top

PUBLIC OpenNonMatchingQuery (handle)

Purpose: Open a query that returns no records
Notes: Query is open but returns no records. This might be useful
when setting up GUI for .NET Data-Binding in a Progress.Data.BindingSource
instance before we know the actual selection criteria. Data-Binding
requires an open query

Parameters:
phQuery HANDLE
The handle of the query
Top

PUBLIC PrepareQueryInReserveOrder (handle)

Purpose: Prepares a Query in reverse sort order
Notes: This method is depricated. In the original implementation there was
a typo in the name of this method. This was fixed, a method with the
typo remains for now as a facade. However, customers should be calling
directly into QueryHelper:PrepareQueryInReverseOrder ()

Parameters:
phQuery HANDLE
The handle of the query to process
Top

PUBLIC PrepareQueryInReverseOrder (handle)

Purpose: Prepares a Query in reverse sort order
Notes: As the Dataserver for ORACLE does not return the Queries index information
this method will throw an NoIndexInformationAvailable when the query
was not openend without an BY phrase and the ORACLE Tables are involed

Parameters:
phQuery HANDLE
The handle of the query to process
Top

PUBLIC CHARACTER QualifiedColumnName (character, handle)

Purpose: Returns the column name qualified with the first table name
of the Queries buffers that contains the field name
Notes: Returns ? if the field in not found in any of the queries buffers

Parameters:
pcColumnName CHARACTER
The unqualified column name
phQuery HANDLE
The handle of the QUERY
Returns CHARACTER
The qualified column name or ?
Top

PUBLIC CHARACTER QueryBufferNames (handle)

Purpose: Returns a comma delimited list of the Buffer Names in the Query
Notes:

Parameters:
phQuery HANDLE
The handle of the Query
Returns CHARACTER
The comma delimited list of Buffer Names in the Query
Top

PUBLIC CHARACTER QueryLargeColumns (handle)

Purpose: Returns the large columns of the buffers from the given query handle
Notes: Uses by BufferHelper:BufferLargeColumns, please be aware that every
column name will only appear once, like Column names of a joined
ProBindingSource query

Parameters:
phQuery HANDLE
The handle of the query
Returns CHARACTER
The comma delimited list of names of the large columns in the query buffers
Top

PUBLIC Consultingwerk.ListSortField QuerySortList (character)

Purpose: Returns the list of SortField instances for the given query string
Notes:

Parameters:
pcQueryString CHARACTER
The original query string
Returns Consultingwerk.ListSortField
The list of SortField instances
Top

PUBLIC CHARACTER QuerySortPhrase (character)

Purpose: Returns the sort phrase of the given query string
Notes:

Parameters:
pcQueryString CHARACTER
The original query string
Returns CHARACTER
The sort phrase of the query string
Top

PUBLIC CHARACTER QueryStringEntries (character)

Purpose: Returns an array with the entries of a query string, uses "," as the
delimiter, but does not consider comma's in quotes or brackets
Notes: QueryStringEntries (2, "for each Customer, first SalesRep of Customer")
returns ["for each Customer", "first SalesRep of Customer"].

Parameters:
pcQueryString CHARACTER
The complete query string
Returns CHARACTER
Array of entries of the query string
Top

PUBLIC LOGICAL RepositionToRowidArray (handle, rowid[])

Purpose: Moves a query objects result list pointer to the row corresponding
to the specified ROWID or ROWIDs. To reposition to a particular row
when the query is a join, supply the ROWIDs of the buffers that
correspond to the desired row.
Notes: The advantage of this method over the REPOSITION-TO-ROWID method
of the Query widget is that this method works with a dynamic
array.

Parameters:
phQuery HANDLE
The handle of the query
prRowids ROWID
An Array of Rowids (maximum 18)
Returns LOGICAL
Logical value indicating if the method was successful
Top

PUBLIC CHARACTER SetQuerySort (character, character)

Purpose: Sets or replaces the sort string (BY phrase) in the provided query
string
Notes:

Parameters:
pcQueryString CHARACTER
The query string in which to replace the sort string
pcSortPhrase CHARACTER
The new sort string (including BY)
Returns CHARACTER
The query string with the new sort phrase
Top

PUBLIC CHARACTER SetUseIndex (character, character)

Purpose: Inserts a USE-INDEX option into a query string. Defaults to using
the first buffer criteria for adding the query string
Notes: When the query did already contain a USE-INDEX option, that option
is replaced, otherwise a USE-INDEX option is injected
This method does not validate the index name for performance reasons

Parameters:
pcQueryString CHARACTER
The base query string
pcIndexName CHARACTER
The name of the index to inject
Returns CHARACTER
The resulting Query String
Top

PUBLIC CHARACTER SetUseIndex (character, integer, character)

Purpose: Inserts a USE-INDEX option into a query string
Notes: When the query did already contain a USE-INDEX option, that option
is replaced, otherwise a USE-INDEX option is injected.
This method does not validate the index name for performance reasons

Parameters:
pcQueryString CHARACTER
The base query string
piBufferIndex INTEGER
The index of the query buffer criteria to add the USE-INDEX option to
pcIndexName CHARACTER
The name of the index to inject
Returns CHARACTER
The resulting Query String
Top

PUBLIC ValidateQuery (character, character)

Purpose: Validates a query string and returns an error when the
query string cannot be prepareed
Notes: Creates a temporary query string

Parameters:
pcTableNames CHARACTER
The names of the database tables
pcQueryString CHARACTER
The query string to validate
Top

PUBLIC ValidateQuery (character, character, character)

Purpose: Validates a query string and returns an error when the
query string cannot be prepareed
Notes: Creates a temporary query string

Parameters:
pcTableNames CHARACTER
The names of the database tables
pcBufferNames CHARACTER
The buffer names to use
pcQueryString CHARACTER
The query string to validate
Top

PRIVATE CHARACTER WhereClauseBuffer (character)

Purpose: Returns the buffername of a where clause expression.
Notes: This function avoids problems with leading or double blanks in
where clauses.

Parameters:
pcWhere CHARACTER
Complete where clause for ONE table with or without the FOR keyword. The buffername must be the second token in the where clause as in "EACH order OF Customer" or if "FOR" is specified, the third token as in "FOR EACH order".
Returns CHARACTER
The buffer name of the where clause


Constructor Detail
Top

PROTECTED QueryHelper ()

Purpose: Protected default constructor.
Notes: There's no need to create instances of the helper classes



©2006-2019 Consultingwerk Ltd.         info@consultingwerk.de         http://www.consultingwerk.de       08.08.2019 23:57:47