[RBASE-L] - Re: User defined search string

[A. Razzak Memon]

At 11:43 AM 6/3/2010, David Fowler wrote:

Is there a way to allow the user to enter a search string or "where clause"
?  I've done it in 2.11 by assigning a variable and having the variable
filled in (fillin x using "Enter search criteria") and then taking that
variable (x) and placing it after the Where in a select command ie. Select
LName from Job where .x (x being something along the lines of - date eq
12/1/2009 or Month eq May and Week eq One). Remember, this is from 2.11 so
eq works and quotes aren't needed.  I have tried this kind of set-up in
7.6, but get a syntax incorrect error, even with correct use of = and '.


David,

Here's how you can give your users the power they need. Just copy and
paste the code below and see what you get.

Use RRBYW14, RRBYW15, or RRBYW16 sample databases to test this technique.

-- User_Defined_Search_String.RMD
-- User_Defined_Search_String.RMD
-- A. Razzak Memon
-- June 3, 2010
-- Supported Versions:
-- R:BASE 7.6 for Windows
-- R:BASE Turbo V-8 for Windows
-- R:BASE eXtreme 9.0 (32/64)

-- Steps:

-- 00. Pre-Define All Global Variables
-- 01. Select Table/View
-- 02. Select Column(s)
-- 03. Build WHERE Clause, if necessary
-- 04. Build Syntax
-- 05. Execute Command

-- Step 00: Predefine All Global Variables
   SET VAR vTableViewName TEXT = NULL
   SET VAR vSys_Table_ID INTEGER = NULL
   SET VAR vColumns TEXT = NULL
   SET VAR vWhereSortClause TEXT = NULL
   SET VAR vSyntax NOTE = NULL
   SET VAR vOutPut TEXT = 'Screen'
   SET VAR vLines INTEGER = NULL
   SET VAR vSyntaxForBROWSE TEXT = NULL
   SET WALKMENU ON

-- Step 01: Select Table/View ...
LABEL PickTableView
   CLS
   CHOOSE vTableViewName FROM #VALUES FOR +
   ((LJS(SYS_TABLE_NAME,54))&SYS_COMMENT),SYS_TABLE_NAME FROM SYS_TABLES +
   WHERE SYS_TABLE_TYPE <> 'SYSTEM TABLE' ORDER BY SYS_TABLE_NAME ASC +
   CHKBOX 1 TITLE 'Select Table/View' +
   CAPTION 'Step 1' LINES 40 FORMATTED +
   OPTION LIST_FONT_COLOR MAROON +
   |LIST_BACK_COLOR 15395562 +
   |LIST_FONT_SIZE 11 +
   |LIST_FONT_NAME COURIER +
   |TITLE_FONT_COLOR RED +
   |TITLE_BACK_COLOR 15395562 +
   |WINDOW_BACK_COLOR 15395562 +
   |TITLE_FONT_SIZE 11 +
   |TITLE_FONT_NAME Verdana +
   |WINDOW_CAPTION +
   |BUTTONS_SHOW_GLYPH ON +
   |BUTTONS_BACK_COLOR WHITE +
   |THEMENAME R:BASE Rocks!
IF vTableViewName = '[Esc]' THEN
  SET VAR vTableViewName = NULL
  GOTO Done
ENDIF
IF vTableViewName IS NULL THEN
  PAUSE 2 USING 'Table/View Not Selected!' ICON STOP +
  OPTION THEMENAME R:BASE Rocks!
  GOTO PickTableView
ENDIF

-- Stepe 02: Select Columns
LABEL PickColumns
   CLS
   SELECT SYS_TABLE_ID INTO vSys_Table_ID INDIC iv1 FROM SYS_TABLES +
   WHERE SYS_TABLE_NAME = .vTableViewName
   SET VAR vLines INTEGER = 0
   SELECT COUNT(*) INTO vLines INDIC iv1 FROM SYS_COLUMNS +
   WHERE SYS_TABLE_ID = .vSys_Table_ID
   IF vLines > 40 THEN
      SET VAR vLines = 40
   ENDIF
   CLS
   CHOOSE vColumns FROM #VALUES FOR +
   ((LJS(SYS_COLUMN_NAME,52))&SYS_COMMENT),SYS_COLUMN_NAME FROM SYS_COLUMNS +
   WHERE SYS_TABLE_ID = .vSys_Table_ID +
   CHKBOX TITLE 'Select Columns' +
   CAPTION 'Step 2' LINES .vLines FORMATTED +
   OPTION LIST_FONT_COLOR MAROON +
   |LIST_BACK_COLOR 15395562 +
   |LIST_FONT_SIZE 11 +
   |LIST_FONT_NAME COURIER +
   |TITLE_FONT_COLOR RED +
   |TITLE_BACK_COLOR 15395562 +
   |WINDOW_BACK_COLOR 15395562 +
   |TITLE_FONT_SIZE 11 +
   |TITLE_FONT_NAME Verdana +
   |WINDOW_CAPTION +
   |BUTTONS_SHOW_GLYPH ON +
   |BUTTONS_BACK_COLOR WHITE +
   |THEMENAME R:BASE Rocks!
IF vColumns = '[Esc]' THEN
   SET VAR vColumns = NULL
   GOTO Done
ENDIF
IF vColumns IS NULL THEN
  PAUSE 2 USING 'Column(s) Not Selected!' ICON STOP +
  OPTION THEMENAME R:BASE Rocks!
  GOTO PickColumns
ENDIF

-- Stepe 03: Build WHERE Clause
   CLS
   CHOOSE vWhereSortClause FROM #WHERE IN &vTableViewName +
   OPTION THEMENAME R:BASE Rocks!
   IF (SLEN(.vWhereSortClause)) < 10 THEN
      SET VAR vWhereSortClause = NULL
   ENDIF

-- Stepe 04: Build Syntax
   SET VAR vSyntax = +
   (IFNULL(.vWhereSortClause,('SELECT'&.vColumns&'FROM'&.vTableViewName), +
   ('SELECT'&.vColumns&'FROM'&.vTableViewName&.vWhereSortClause)))
   SET VAR vSyntaxForBROWSE = +
   (IFNULL(.vWhereSortClause,('BROWSE'&.vColumns&'FROM'&.vTableViewName&'NOCHG'), 
+
   ('BROWSE'&.vColumns&'FROM'&.vTableViewName&.vWhereSortClause&'NOCHG')))

-- Step 05: Execute Command
SWITCH (.vOutPut)
  CASE 'SCREEN'
    &vSyntaxForBROWSE
    BREAK
  CASE 'PRINTER'
    SET LINES 59
    OUTPUT PRINTER FONTNAME COURIER FontSize 12
    &vSyntax
    SET LINES 19
    OUTPUT SCREEN
    BREAK
  CASE 'HTA'
    SET VAR vFileName = (.vTableViewName+'.HTA')
    SET WIDTH 700
    SET LINES 0
    OUTPUT &vFileName
    -- Added Minimum Legal HTML Tags
    WRITE '<HTML>'
    WRITE '<BODY>'
    -- the following must be <PRE> to display plaintext output
    WRITE '<PRE>'
    &vSyntax
    WRITE '</PRE>'
    WRITE '</BODY>'
    WRITE '</HTML>'
    OUTPUT SCREEN
    SET LINES 19
    SET WIDTH 79
    LAUNCH &vFileName
    BREAK
  CASE 'PDF'
    SET VAR vFileName = (.vTableViewName+'.PDF'&'PDF')
    SET VAR vFileNameView = (.vTableViewName+'.PDF')
    SET WIDTH 400
    SET LINES 0
    OUTPUT &vFileName
    &vSyntax
    OUTPUT SCREEN
    SET LINES 19
    SET WIDTH 79
    LAUNCH &vFileNameView
    BREAK
  DEFAULT
    BREAK
ENDSW

LABEL Done
CLEAR VARIABLES iv%,vTableViewName,vColumns,vWhereSortClause, +
vSys_Table_ID,vSyntax,vOutPut,vLines,vSyntaxForBROWSE
RETURN
-- End here ...

Have fun implementing this technique into your own application!

Very Best R:egards,

Razzak.