Absolute Database by Component Ace

dbManager32 [[REPLACE keyword/statement

Usage: (Starting with version dbManager32v07a7)
Format: [[REPLACE ReplaceName = ReplaceValue]]

  1. Each line of the SQL window is checked for an occurrence of [[REPLACE
    • If found that line must also contain, at a minimum, the ReplaceName and an equal(=) sign
    • A ReplaceName may be any (unique) sequence of characters to be replaced and is made up of the trimmed characters following [[REPLACE and before the equal(=) sign.
    • A ReplaceValue is all the characters immediately after the equal(=) sign and up to the close of [[REPLACE defined as "]]". Multiple lines are permitted.
  2. The [[REPLACE is not case sensitive
  3. [[REPLACE statements must be defined before the strings to be replaced.
  4. A ReplaceName is not case sensitive.
  5. A ReplaceValue is a literal. Don't use quotes, spaces, or anything else after the equal(=) sign unless you want it as part of the ReplaceValue.
  6. [[REPLACE statements may be nested.
  7. A right-click in the SQL statement window gives an option "SQL to Log". Selecting it will send the contents of the SQL window directly to the Log window after processing any-and-all ReplaceNames with its corresponding ReplaceValue and without executing the SQL statement(s).

Replace Examples

dbManager32 Simulated Parameters

Now that "[[REPLACE" string replacement ability is available, there seems to be no current value to the DECLARE and SET simulated parameters. They are being left in place in case a future need arises.

Using DECLARE and SET as parameter indicators are not yet implemented in the Absolute database engine SQL Interpreter (they are Reserved Words) but are now simulated in dbManager32 as of 2018/11/17

Rules: (Starting with version 07a2)

  1. All DECLARE(s) and SET(s) in the SQL Statement(s) are processed before the SQL statement(s) are executed.
    • The SQL statement(s) is the contents of the entire window unless only a portion of the window's contents are selected. If only a portion of the window is selected then only the selected text will be executed.
  2. Parameter names are identified by a colon(:) preceding the name i.e. :myParameter
  3. Parameter names are not case sensitive.
  4. DECLARE and SET lines must be the first non-whitespace characters on comment lines that begin with a "--" (without the quotes) and not embedded in a Slash/Star comment.
  5. Multiple parameters may be declared in a single DECLARE clause and are separated by commas.
  6. Only one parameter value may be SET per line
  7. You may not SET a parameter before it has been DECLAREd
  8. The SQL statement itself will fail if any DECLARE or SET in it is not considered as part of a comment by the Absolute database engine.
  9. String values for parameters must be in quotes or the system thinks the value is a field name
  10. Only Field values may utilize parameters.
  11. Valid Field Types for the DECLARE clause
    • ftString
    • ftInteger
    • ftFloat
    • ftCurrency
    • ftBoolean
    • ftDate
    • ftTime
    • ftDateTime

The decision to allow only "--" to precede a valid DECLARE or SET command was determined by the possibility that Component Ace might someday make them part of the SQL Interpreter. If that occurrs all the User would have to do is remove the "--" and it would be part of the SQL Statement.

Example SQL statement utilizing simulated pParameters and REPLACEments

/* Slash/Star comments are always ignored by simulated parameters */
-- DECLARE :FirstName ftString, :LastName ftString
-- SET :FirstName = "David"  -- More comments may be placed here
-- SET :LastName = "Somebody"
-- REPLACE @myTable = "C:\temp\dertTestData\CD1_DERT_190115a.ABS".People
SELECT * FROM @myTable
 LNAME = :LastName
 FNAME = :FirstName