home *** CD-ROM | disk | FTP | other *** search
/ Programming Languages Suite / ProgramD2.iso / Visual Database / Visual dBase v5.5 / UTILS.PAK / SQL.WFM < prev    next >
Encoding:
Text File  |  1995-07-18  |  104.3 KB  |  3,331 lines
  1. *******************************************************************************
  2. * PROGRAM:     Sql.wfm
  3. *
  4. * WRITTEN BY:  Borland Samples Group
  5. *
  6. * DATE:        3/95
  7. *
  8. * UPDATED:     7/95
  9. *
  10. * REVISION:    $Revision:   1.37  $
  11. *
  12. * VERSION:     Visual dBASE
  13. *
  14. * DESCRIPTION: This is a tool for building a SQL statement.  You can also
  15. *              use this tool to see examples of dBASE vs SQL code, and
  16. *              use pre-defined templates for SQL statements.
  17. *              This tool supports local dBASE and paradox tables, as well as
  18. *              Interbase, Oracle and Sybase server tables.
  19. *
  20. * PARAMETERS:  None
  21. *
  22. * CALLS:       Buttons.cc    (Custom controls file)
  23. *              Getfield.wfm  (Field picker)
  24. *              Gettable.wfm  (Table picker)
  25. *              Options.pop   (Options popup)
  26. *
  27. * USAGE:       DO Sql.wfm
  28. *
  29. ********************************************************************************
  30. #include <Messdlg.h>
  31.  
  32. #define ALLTRIM(s)             ltrim(rtrim(s))
  33. #define TRIMSTR(s)             ltrim(str(s))
  34.  
  35. #define MAX_STATEMENTS         4
  36. #define MAX_AGGREGATES         5
  37. #define MAX_OPERATORS          8
  38. #define MAX_ARTICLES           3
  39. #define MAX_VIEWS              3
  40. #define MAX_ALLOWED_CLAUSES    5
  41. #define MAX_INSERT_VALUES      25
  42. #define PERIOD                 "."
  43.  
  44. #define ALL_FIELDS             "*"
  45.  
  46. *** Activity indicators
  47. #define NO_WARNING             0
  48. #define DISPLAY_WARNING        1
  49.  
  50. *** For SQL/dBASE code examples (page 3 of form)
  51. #define NO_QUERY               0
  52. #define SQL_QUERY              1
  53. #define DBASE_QUERY            2
  54.  
  55. *** Field restrictions
  56. #define MEMO_BINARY_OLE        "M, B, O"
  57. #define CHAR_LOGICAL_DATE      "C, L, D"
  58. #define NUMERIC                "N"
  59.  
  60. *** INSERT value control constants
  61. #define INSERT_VALUE_WIDTH     12
  62. #define INSERT_VALUE_HEIGHT    1.2
  63. #define INSERT_VALUES_PER_ROW  5
  64. #define INSERT_START_TOP       4.5
  65. #define INSERT_START_LEFT      3
  66.  
  67. *** Constants for referencing table information from IDAPI API
  68. #define DATABASE_INFO_LEN      224
  69. #define DATABASE_TYPE_LOC      147
  70.  
  71. *** Error Messages
  72. #define ERROR_NO_SUCH_DATABASE 239
  73.  
  74.  
  75. *** New Session
  76. create session
  77.  
  78. *** Environment
  79. set talk off
  80. set ldcheck off
  81. set date mdy            && SQL wants american date format
  82. set mark to
  83. set century off
  84. set memowidth to 120
  85.  
  86.  
  87. ** END HEADER -- do not remove this line*
  88. * Generated on 07/11/95
  89. *
  90. parameter bModal
  91. local f
  92. f = new SQLFORM()
  93. if (bModal)
  94.    f.mdi = .F. && ensure not MDI
  95.    f.ReadModal()
  96. else
  97.    f.Open()
  98. endif
  99. CLASS SQLFORM OF FORM
  100.    Set Procedure To &_dbwinhome.custom\BUTTONS.CC additive
  101.    this.OnOpen = CLASS::FORM_ONOPEN
  102.    this.Left = 1
  103.    this.StatusMessage = "Select a SQL command"
  104.    this.Top = 1
  105.    this.OnClose = CLASS::FORM_ONCLOSE
  106.    this.Width = 77.333
  107.    this.ColorNormal = "N/BTNFACE"
  108.    this.View = "Sql.qbe"
  109.    this.Text = "SQL Statement Builder"
  110.    this.OnMouseMove = CLASS::FORM_ONMOUSEMOVE
  111.    this.Height = 19
  112.  
  113.    DEFINE RECTANGLE STATEMENTRECT OF THIS;
  114.        PROPERTY;
  115.          Left 0.5,;
  116.          Top 0.1973,;
  117.          Width 76.5,;
  118.          Text "",;
  119.          OnMouseMove CLASS::STATEMENTRECT_ONMOUSEMOVE,;
  120.          Height 13.5674
  121.  
  122.    DEFINE COMBOBOX STATEMENTCOMBO OF THIS;
  123.        PROPERTY;
  124.          Left 2.5,;
  125.          Top 0.5,;
  126.          Style 2,;
  127.          Width 13,;
  128.          OnChange CLASS::STATEMENTCOMBO_ONCHANGE,;
  129.          OnMouseMove CLASS::STATEMENTCOMBO_ONMOUSEMOVE,;
  130.          FontBold .F.,;
  131.          Height 1.2646
  132.  
  133.    DEFINE TEXT PAREN1TEXT OF THIS;
  134.        PROPERTY;
  135.          Left 22,;
  136.          Top 1.7979,;
  137.          Width 1,;
  138.          ColorNormal "N/BtnFace",;
  139.          Visible .F.,;
  140.          Text "(",;
  141.          FontBold .F.,;
  142.          Height 0.8262
  143.  
  144.    DEFINE ENTRYFIELD FIELDSENTRY OF THIS;
  145.        PROPERTY;
  146.          Left 23,;
  147.          Enabled .F.,;
  148.          Top 0.5,;
  149.          Width 48,;
  150.          ColorNormal "N/W",;
  151.          Value "",;
  152.          OnMouseMove CLASS::FIELDSENTRY_ONMOUSEMOVE,;
  153.          FontBold .F.,;
  154.          Height 1
  155.  
  156.    DEFINE TEXT PAREN2TEXT OF THIS;
  157.        PROPERTY;
  158.          Left 71,;
  159.          Top 1.7979,;
  160.          Width 1,;
  161.          ColorNormal "N/BtnFace",;
  162.          Visible .F.,;
  163.          Text ")",;
  164.          FontBold .F.,;
  165.          Height 0.8262
  166.  
  167.    DEFINE TOOLBUTTON FIELDSTOOLBUTTON OF THIS;
  168.        PROPERTY;
  169.          SpeedTip "Select fields",;
  170.          Left 72,;
  171.          Top 0.5,;
  172.          Width 4,;
  173.          Group .T.,;
  174.          OnClick CLASS::FIELDSTOOLBUTTON_ONCLICK,;
  175.          OnMouseMove CLASS::FIELDSTOOLBUTTON_ONMOUSEMOVE,;
  176.          FontBold .F.,;
  177.          Height 1
  178.  
  179.    DEFINE TEXT STATEMENTTEXT OF THIS;
  180.        PROPERTY;
  181.          Left 2.5,;
  182.          Top 1.7998,;
  183.          Width 8,;
  184.          ColorNormal "N/BTNFACE",;
  185.          Text "FROM",;
  186.          FontBold .F.,;
  187.          Height 1.0137
  188.  
  189.    DEFINE TEXT INSERTINTOTEXT OF THIS;
  190.        PROPERTY;
  191.          Left 17,;
  192.          Top 0.5,;
  193.          Width 5,;
  194.          ColorNormal "N/BtnFace",;
  195.          Visible .F.,;
  196.          Text "INTO",;
  197.          FontBold .F.,;
  198.          Height 0.8525
  199.  
  200.    DEFINE ENTRYFIELD TABLESENTRY OF THIS;
  201.        PROPERTY;
  202.          Left 23,;
  203.          Enabled .F.,;
  204.          Top 1.7979,;
  205.          Width 48,;
  206.          ColorNormal "N/W",;
  207.          Value "",;
  208.          OnMouseMove CLASS::TABLESENTRY_ONMOUSEMOVE,;
  209.          FontBold .F.,;
  210.          Height 1.0137
  211.  
  212.    DEFINE TOOLBUTTON TABLESTOOLBUTTON OF THIS;
  213.        PROPERTY;
  214.          SpeedTip "Select tables",;
  215.          Left 72,;
  216.          Top 1.7979,;
  217.          Width 4,;
  218.          Visible .F.,;
  219.          Group .T.,;
  220.          OnClick CLASS::TABLESTOOLBUTTON_ONCLICK,;
  221.          OnMouseMove CLASS::TABLESTOOLBUTTON_ONMOUSEMOVE,;
  222.          FontBold .F.,;
  223.          Height 1.0137
  224.  
  225.    DEFINE TOOLBUTTON ADDCLAUSEBUTTON OF THIS;
  226.        PROPERTY;
  227.          Left 2.5,;
  228.          Top 3.1172,;
  229.          Width 13,;
  230.          Text "Where",;
  231.          Group .T.,;
  232.          OnClick CLASS::ADDCLAUSEBUTTON_ONCLICK,;
  233.          OnMouseMove CLASS::ADDCLAUSEBUTTON_ONMOUSEMOVE,;
  234.          FontBold .F.,;
  235.          Height 1.0068
  236.  
  237.    DEFINE TOOLBUTTON GROUPBYBUTTON OF THIS;
  238.        PROPERTY;
  239.          Left 2.5,;
  240.          Top 10.5,;
  241.          Width 13,;
  242.          Visible .F.,;
  243.          Text "Group By",;
  244.          Group .T.,;
  245.          OnClick CLASS::GROUPBYBUTTON_ONCLICK,;
  246.          OnMouseMove CLASS::GROUPBYBUTTON_ONMOUSEMOVE,;
  247.          FontBold .F.,;
  248.          Height 1
  249.  
  250.    DEFINE TOOLBUTTON ORDERBYBUTTON OF THIS;
  251.        PROPERTY;
  252.          Left 2.5,;
  253.          Top 12.5879,;
  254.          Width 13,;
  255.          Visible .F.,;
  256.          Text "Order By",;
  257.          Group .T.,;
  258.          OnClick CLASS::ORDERBYBUTTON_ONCLICK,;
  259.          OnMouseMove CLASS::ORDERBYBUTTON_ONMOUSEMOVE,;
  260.          FontBold .F.,;
  261.          Height 0.9736
  262.  
  263.    DEFINE PUSHBUTTON RUNSQLBUTTON OF THIS;
  264.        PROPERTY;
  265.          Left 0.5,;
  266.          Top 16.3975,;
  267.          Width 14.166,;
  268.          PageNo 0,;
  269.          Text "&Run",;
  270.          Group .T.,;
  271.          OnClick CLASS::RUNSQLBUTTON_ONCLICK,;
  272.          OnMouseMove CLASS::RUNSQLBUTTON_ONMOUSEMOVE,;
  273.          FontBold .F.,;
  274.          UpBitmap "RESOURCE #108",;
  275.          Height 1.5391
  276.  
  277.    DEFINE PUSHBUTTON CLEARSQLBUTTON OF THIS;
  278.        PROPERTY;
  279.          Left 16,;
  280.          Top 16.3975,;
  281.          Width 14.166,;
  282.          PageNo 0,;
  283.          Text "&Clear",;
  284.          Group .T.,;
  285.          OnClick CLASS::CLEARSQLBUTTON_ONCLICK,;
  286.          OnMouseMove CLASS::CLEARSQLBUTTON_ONMOUSEMOVE,;
  287.          FontBold .F.,;
  288.          UpBitmap "RESOURCE #620",;
  289.          Height 1.5391
  290.  
  291.    DEFINE PUSHBUTTON SHOWSQLBUTTON OF THIS;
  292.        PROPERTY;
  293.          Left 31.5,;
  294.          Top 16.3975,;
  295.          Width 14.166,;
  296.          PageNo 0,;
  297.          Text "&Show SQL",;
  298.          Group .T.,;
  299.          OnClick CLASS::SHOWSQLBUTTON_ONCLICK,;
  300.          OnMouseMove CLASS::SHOWSQLBUTTON_ONMOUSEMOVE,;
  301.          FontBold .F.,;
  302.          UpBitmap "RESOURCE #621",;
  303.          Height 1.5391
  304.  
  305.    DEFINE OKBUTTON OKBUTTON1 OF THIS;
  306.        PROPERTY;
  307.          Left 47,;
  308.          Top 16.3975,;
  309.          Width 14.166,;
  310.          PageNo 0,;
  311.          Group .T.,;
  312.          OnClick CLASS::PAGE1OKBUTTON1_ONCLICK,;
  313.          OnMouseMove CLASS::OKBUTTON1_ONMOUSEMOVE,;
  314.          FontBold .F.,;
  315.          Height 1.5391
  316.  
  317.    DEFINE CANCELBUTTON CANCELBUTTON1 OF THIS;
  318.        PROPERTY;
  319.          Left 62.5977,;
  320.          Top 16.3975,;
  321.          Width 14.2354,;
  322.          PageNo 0,;
  323.          Group .T.,;
  324.          OnMouseMove CLASS::CANCELBUTTON1_ONMOUSEMOVE,;
  325.          FontBold .F.,;
  326.          Height 1.5391
  327.  
  328.    DEFINE RECTANGLE MESSAGERECT OF THIS;
  329.        PROPERTY;
  330.          Left 0.5,;
  331.          Top 13.7637,;
  332.          Width 76.5,;
  333.          Text "",;
  334.          BorderStyle 2,;
  335.          OnMouseMove CLASS::MESSAGERECT_ONMOUSEMOVE,;
  336.          Height 2.5303
  337.  
  338.    DEFINE TABBOX PAGETABBOX OF THIS;
  339.        PROPERTY;
  340.          Top 18,;
  341.          DataSource "Array {'SQL Statement Builder', 'Templates', 'Examples'}",;
  342.          Width 77.333,;
  343.          ID 116,;
  344.          OnSelChange CLASS::PAGETABBOX_ONSELCHANGE,;
  345.          ColorHighLight "",;
  346.          OnMouseMove CLASS::PAGETABBOX_ONMOUSEMOVE,;
  347.          FontBold .F.,;
  348.          Height 1
  349.  
  350.    DEFINE TEXT MESSAGETEXT OF THIS;
  351.        PROPERTY;
  352.          Left 1.3311,;
  353.          Top 13.9473,;
  354.          Width 74.1689,;
  355.          Alignment 9,;
  356.          ColorNormal "B/BTNFACE",;
  357.          Text "",;
  358.          FontBold .F.,;
  359.          Height 2.2393
  360.  
  361.    DEFINE RECTANGLE MAINRECT OF THIS;
  362.        PROPERTY;
  363.          Left 0.5,;
  364.          Top 0.1973,;
  365.          Width 76.166,;
  366.          PageNo 2,;
  367.          Text "",;
  368.          Height 15.9893
  369.  
  370.    DEFINE RADIOBUTTON TEMPLATE1RADIO OF THIS;
  371.        PROPERTY;
  372.          OnGotFocus CLASS::TEMPLATE1RADIO_ONGOTFOCUS,;
  373.          Left 1.5,;
  374.          Top 1,;
  375.          Width 18.666,;
  376.          ColorNormal "B/BtnFace",;
  377.          PageNo 2,;
  378.          Text "Show all data in ",;
  379.          Group .T.,;
  380.          Value .T.,;
  381.          FontBold .F.,;
  382.          Height 1
  383.  
  384.  
  385.    DEFINE ENTRYFIELD TEMPLATE1ENTRY OF THIS;
  386.        PROPERTY;
  387.          Left 22.5,;
  388.          Enabled .F.,;
  389.          Top 1,;
  390.          Width 48.5,;
  391.          ColorNormal "N/BG+",;
  392.          PageNo 2,;
  393.          Value "",;
  394.          FontBold .F.,;
  395.          Height 1
  396.  
  397.    DEFINE TOOLBUTTON TEMPLATE1TOOLBUTTON OF THIS;
  398.        PROPERTY;
  399.          Left 72,;
  400.          Top 1,;
  401.          Width 4,;
  402.          PageNo 2,;
  403.          Group .T.,;
  404.          OnClick CLASS::TEMPLATE1TOOLBUTTON_ONCLICK,;
  405.          Height 1
  406.  
  407.    DEFINE RADIOBUTTON TEMPLATE2RADIO OF THIS;
  408.        PROPERTY;
  409.          OnGotFocus CLASS::TEMPLATE2RADIO_ONGOTFOCUS,;
  410.          Left 1.5,;
  411.          Top 3,;
  412.          Width 14.166,;
  413.          ColorNormal "B/BtnFace",;
  414.          PageNo 2,;
  415.          Text "Show fields",;
  416.          Group .T.,;
  417.          Value .T.,;
  418.          FontBold .F.,;
  419.          Height 0.875
  420.  
  421.    DEFINE LISTBOX TEMPLATE2LIST OF THIS;
  422.        PROPERTY;
  423.          Left 16.5,;
  424.          Enabled .F.,;
  425.          Top 3,;
  426.          Width 17.5,;
  427.          ColorNormal "N/BG+",;
  428.          PageNo 2,;
  429.          Multiple .T.,;
  430.          ID 130,;
  431.          ColorHighLight "WINDOWTEXT/WINDOW",;
  432.          FontBold .F.,;
  433.          Height 2.5
  434.  
  435.    DEFINE TEXT TEMPLATE2TEXT OF THIS;
  436.        PROPERTY;
  437.          Left 35,;
  438.          Top 3,;
  439.          Width 10.166,;
  440.          ColorNormal "B/BtnFace",;
  441.          PageNo 2,;
  442.          Text "from table",;
  443.          FontBold .F.,;
  444.          Height 0.875
  445.  
  446.    DEFINE ENTRYFIELD TEMPLATE2ENTRY OF THIS;
  447.        PROPERTY;
  448.          Left 44,;
  449.          Enabled .F.,;
  450.          Top 3,;
  451.          Width 27,;
  452.          ColorNormal "N/BG+",;
  453.          PageNo 2,;
  454.          Value "",;
  455.          FontBold .F.,;
  456.          Height 1
  457.  
  458.    DEFINE TOOLBUTTON TEMPLATE2TOOLBUTTON OF THIS;
  459.        PROPERTY;
  460.          Left 72,;
  461.          Top 3,;
  462.          Width 4,;
  463.          PageNo 2,;
  464.          Group .T.,;
  465.          OnClick CLASS::TEMPLATE2TOOLBUTTON_ONCLICK,;
  466.          Height 1
  467.  
  468.    DEFINE RADIOBUTTON TEMPLATE3RADIO OF THIS;
  469.        PROPERTY;
  470.          OnGotFocus CLASS::TEMPLATE3RADIO_ONGOTFOCUS,;
  471.          Left 1.5,;
  472.          Top 6,;
  473.          Width 44.166,;
  474.          ColorNormal "B/BtnFace",;
  475.          PageNo 2,;
  476.          Text "Show customers whose orders totaled over ",;
  477.          Group .T.,;
  478.          Value .T.,;
  479.          FontBold .F.,;
  480.          Height 0.875
  481.  
  482.    DEFINE SPINBOX TEMPLATE3SPIN OF THIS;
  483.        PROPERTY;
  484.          Left 44.5,;
  485.          Enabled .F.,;
  486.          Top 6,;
  487.          Width 16.166,;
  488.          ColorNormal "N/BG+",;
  489.          Picture "99999999999.99",;
  490.          PageNo 2,;
  491.          Function "$",;
  492.          Value 500,;
  493.          Step 10,;
  494.          Rangemax 100,;
  495.          Rangemin 1,;
  496.          FontBold .F.,;
  497.          Height 1
  498.  
  499.  
  500.    DEFINE RECTANGLE ANSWERRECT OF THIS;
  501.        PROPERTY;
  502.          Left 0.5,;
  503.          Top 10,;
  504.          Width 76.5,;
  505.          ColorNormal "n+/BtnFace",;
  506.          PageNo 3,;
  507.          Text "Answer:  ",;
  508.          FontBold .F.,;
  509.          Height 6.1875
  510.  
  511.    DEFINE RECTANGLE COMMANDRECT OF THIS;
  512.        PROPERTY;
  513.          Left 0.5,;
  514.          Top 0.1973,;
  515.          Width 76.5,;
  516.          ColorNormal "BtnFace",;
  517.          PageNo 3,;
  518.          Text "",;
  519.          FontName "Arial",;
  520.          FontSize 10,;
  521.          FontBold .F.,;
  522.          Height 6.9893
  523.  
  524.    DEFINE TABBOX SQLDBASETABBOX OF THIS;
  525.        PROPERTY;
  526.          Top 9,;
  527.          DataSource "ARRAY {'SQL', 'DBASE'}",;
  528.          Width 80,;
  529.          ColorNormal "r/BtnFace",;
  530.          PageNo 3,;
  531.          Anchor 0,;
  532.          ID 100,;
  533.          OnSelChange CLASS::SQLDBASETABBOX_ONSELCHANGE,;
  534.          ColorHighLight "",;
  535.          FontBold .F.,;
  536.          Height 1
  537.  
  538.    DEFINE COMBOBOX DESCRIPTCOMBO OF THIS;
  539.        PROPERTY;
  540.          Left 1.3311,;
  541.          Top 1.5879,;
  542.          Style 2,;
  543.          DataSource "FIELD SQLDBASE->DESCRIPT",;
  544.          Width 74.002,;
  545.          DropDownHeight 8,;
  546.          PageNo 3,;
  547.          OnChange CLASS::DESCRIPTCOMBO_ONCHANGE,;
  548.          FontBold .F.,;
  549.          Height 1.2354
  550.  
  551.    DEFINE EDITOR COMMANDEDITOR OF THIS;
  552.        PROPERTY;
  553.          Left 1.6641,;
  554.          DataLink "SQLDBASE->SQL_CMD",;
  555.          Top 4,;
  556.          Width 74.002,;
  557.          ColorNormal "N/W*",;
  558.          PageNo 3,;
  559.          CUATab .T.,;
  560.          Modify .F.,;
  561.          FontSize 10,;
  562.          FontBold .F.,;
  563.          Height 2.875
  564.  
  565.    DEFINE TEXT COMMANDTEXT OF THIS;
  566.        PROPERTY;
  567.          Left 1,;
  568.          Top 0.5,;
  569.          Width 25,;
  570.          ColorNormal "b/BtnFace",;
  571.          PageNo 3,;
  572.          Text "Command Description:",;
  573.          FontBold .F.,;
  574.          Height 1
  575.  
  576.    DEFINE TEXT COMPLETECOMMANDTEXT OF THIS;
  577.        PROPERTY;
  578.          Left 1,;
  579.          Top 3,;
  580.          Width 20,;
  581.          ColorNormal "b/BtnFace",;
  582.          PageNo 3,;
  583.          Text "Complete Command:",;
  584.          FontBold .F.,;
  585.          Height 1
  586.  
  587.    DEFINE PUSHBUTTON EXECUTEBUTTON OF THIS;
  588.        PROPERTY;
  589.          Left 32.1641,;
  590.          Top 7.2979,;
  591.          Width 13.8359,;
  592.          PageNo 3,;
  593.          Text "Execute",;
  594.          Group .T.,;
  595.          OnClick CLASS::EXECUTEBUTTON_ONCLICK_SQL,;
  596.          Default .T.,;
  597.          FontBold .F.,;
  598.          UpBitmap "RESOURCE #108",;
  599.          Height 1.3887
  600.  
  601.    DEFINE BROWSE ANSWERBROWSE OF THIS;
  602.        PROPERTY;
  603.          Left 1.5,;
  604.          Top 10.7979,;
  605.          Width 74,;
  606.          ColorNormal "B/BtnFace",;
  607.          Visible .F.,;
  608.          PageNo 3,;
  609.          TabStop .F.,;
  610.          CUATab .T.,;
  611.          Modify .F.,;
  612.          Height 5.0137
  613.  
  614.    DEFINE CLOSEBUTTON CLOSEBUTTON1 OF THIS;
  615.        PROPERTY;
  616.          Left 62.5,;
  617.          Top 16.3975,;
  618.          Width 14.166,;
  619.          PageNo 3,;
  620.          Group .T.,;
  621.          FontBold .F.,;
  622.          Height 1.5391
  623.  
  624.    Procedure FORM_OnOpen
  625.    ****************************************************************************
  626.  
  627.    * Error trapping
  628.    form.saveOnError = setto("on error")
  629.    on error msgBox(message(), "Sorry", ALERT_MESSAGE + OK_BUTTON)
  630.  
  631.    set procedure to program(1) additive && Procedure file
  632.    set procedure to &_dbwinhome.utils\Options.pop additive
  633.    form.popupMenu = new OptionsPopup(form, "popup1")
  634.    form.CleanupCurrentCommand = {;}     && Function pointer to cleanup routine
  635.    form.mouseOverControl = NULL         && for each command, assigned in the
  636.                                         && command's SetUp routine
  637.    form.savePath = setto("path")        && Initial path (reset on page 3)
  638.    form.saveDirectory = setto("directory")   && Initial directory
  639.    CLASS::Page1Start()
  640.    CLASS::Page2Start()
  641.    CLASS::Page3Start()
  642.  
  643.  
  644.    ****************************************************************************
  645.  
  646.    Procedure Page1Start
  647.    ****************************************************************************
  648.  
  649.    *** Page 1 Setup
  650.    * Options Popup
  651.    *   do Options.pop with form, "optionsPop"
  652.  
  653.    * Properties used throughout form
  654.  
  655.    form.statementAr = new array(MAX_STATEMENTS)
  656.    form.clauseAr = new array(MAX_ALLOWED_CLAUSES)
  657.    form.aggregateAr = new array(MAX_AGGREGATES)
  658.    form.operatorAr = new array(MAX_OPERATORS)
  659.    form.articleAr = new array(MAX_ARTICLES)
  660.    form.sqlviewAr = new array(MAX_VIEWS)
  661.  
  662.    form.curCommand = "SELECT"           && -- Current SQL command
  663.    form.clauseCnt = 0                   && -- Number of clauses in command
  664.    form.insertValueCnt = 0              && -- Number of values to INSERT
  665.    form.selectedFieldsAr = new array()  && -- Array of selected fields
  666.    form.selectedTablesAr = new array()  && -- Array of selected tables
  667.    form.serverName = ""
  668.    form.tablesFromDatabase = .F.
  669.  
  670.    * Fill arrays
  671.    form.statementAr[1] = "SELECT"
  672.    form.statementAr[2] = "UPDATE"
  673.    form.statementAr[3] = "INSERT"
  674.    form.statementAr[4] = "DELETE"
  675.  
  676.    form.aggregateAr[1] = "SUM"
  677.    form.aggregateAr[2] = "AVG"
  678.    form.aggregateAr[3] = "MIN"
  679.    form.aggregateAr[4] = "MAX"
  680.    form.aggregateAr[5] = "COUNT"
  681.  
  682.    form.operatorAr[1] = "="
  683.    form.operatorAr[2] = "<"
  684.    form.operatorAr[3] = ">"
  685.    form.operatorAr[4] = ">="
  686.    form.operatorAr[5] = "<="
  687.    form.operatorAr[6] = "<>"
  688.    form.operatorAr[7] = "LIKE"
  689.    form.operatorAr[8] = "IS NULL"
  690.  
  691.    form.articleAr[1] = "     "
  692.    form.articleAr[2] = " AND "
  693.    form.articleAr[3] = " OR  "
  694.  
  695.    * Set dataSource for combobox of commands
  696.    form.statementCombo.datasource = "array form.statementAr"
  697.    form.statementCombo.value = "SELECT"
  698.  
  699.    * Initialization for SELECT command
  700.    CLASS::SetupSelect()
  701.  
  702.    ****************************************************************************
  703.  
  704.    Procedure Page2Start
  705.    ****************************************************************************
  706.  
  707.    form.templateStatement = form.template1Radio.text
  708.    form.template1Radio.datalink = "form.templateStatement"
  709.    form.template2Radio.datalink = "form.templateStatement"
  710.    form.template3Radio.datalink = "form.templateStatement"
  711.    form.templateNum = 1
  712.  
  713.  
  714.  
  715.  
  716.  
  717.    ****************************************************************************
  718.  
  719.    Procedure Page3Start
  720.    ****************************************************************************
  721.  
  722.    if select("Sqldbase") = 0
  723.       form.view = "Sql.qbe"
  724.    endif
  725.    form.commandEditor.dataLink = "sqldbase->sql_cmd"
  726.    form.answerBrowse.visible = .F.
  727.    form.answerBrowse.alias = ""
  728.    form.answerRect.text = "Answer:  "
  729.  
  730.    form.sqlDbasePromptAr = new array(2)
  731.    form.sqlDbasePromptAr[1] = "SQL"
  732.    form.sqlDbasePromptAr[2] = "dBASE"
  733.    form.sqlDbaseTabBox.datasource = "array form.sqlDbasePromptAr"
  734.  
  735.    * Environment that will be restored before every query is executed
  736.    form.saveFields = setto("fields")       && Original set of fields
  737.    form.saveArea = str(workarea())         && Original work area
  738.    form.lastQueryType = NO_QUERY           && Indicates type of the last query
  739.    form.dbaseCleanupCmdArray = new array() && Array for storing cleanup commands
  740.                                            &&    after each dBASE query
  741.    form.sqlCleanupCmdArray = new array()   && Array for storing cleanup commands
  742.                                            &&    after each SQL query
  743.  
  744.  
  745.  
  746.    ****************************************************************************
  747.  
  748.    Procedure FORM_OnClose
  749.    ****************************************************************************
  750.    private savePath, saveDirectory, saveOnError
  751.  
  752.    *** Page 1 (statement builder) cleanup
  753.    if select("temp") > 0
  754.       use in temp
  755.    endif
  756.    if select("temp2") > 0
  757.       use in temp2
  758.    endif
  759.  
  760.  
  761.    *** Page 3 (examples) cleanup
  762.    if form.pageNo = 3
  763.       if form.dbaseCleanupCmdArray.size > 0   && if a query was performed
  764.          form.RestoreEnvironment()
  765.       endif
  766.    endif
  767.  
  768.    close procedure &_dbwinhome.custom\Buttons.cc, program(1),;
  769.                       &_dbwinhome.utils\Options.pop
  770.  
  771.    savePath = form.savePath
  772.    set path to &savePath
  773.    saveDirectory = form.saveDirectory
  774.    set directory to &saveDirectory
  775.    saveOnError = form.saveOnError
  776.    on error &saveOnError
  777.  
  778.  
  779.  
  780.    ****************************************************************************
  781.  
  782.    Procedure STATEMENTCOMBO_OnChange
  783.    ****************************************************************************
  784.  
  785.    form.ClearSQLButton.OnClick()        && Clear clauses from prev statement.
  786.    form.CleanupCurrentCommand()         && Function Pointer assigned in each
  787.                                         && setup routine, referring to its
  788.                                         && cleanup routine.
  789.    form.curCommand = this.value         && After cleanup, change command
  790.    do case
  791.       case this.value = "SELECT"
  792.          form.SetUpSelect()
  793.       case this.value = "UPDATE"
  794.          form.SetUpUpdate()
  795.       case this.value = "INSERT"
  796.          form.SetUpInsert()
  797.       case this.value = "DELETE"
  798.          form.SetUpDelete()
  799.    endcase
  800.  
  801.  
  802.    ****************************************************************************
  803.  
  804.    Procedure PAGETABBOX_OnSelChange
  805.    ****************************************************************************
  806.    private notOnPage3, pageNo, savePath, saveDirectory
  807.  
  808.    if this.curSel <= form.PageCount()
  809.       savePath = form.savePath
  810.       saveDirectory = form.saveDirectory
  811.       form.pageNo = this.curSel
  812.       pageNo = form.pageNo
  813.       notOnPage3 = (pageNo < 3)
  814.       form.runSQLButton.visible = notOnPage3
  815.       form.clearSQLButton.visible = notOnPage3
  816.       form.showSQLButton.visible = notOnPage3
  817.       form.okButton1.visible = notOnPage3
  818.       form.cancelButton1.visible = notOnPage3
  819.  
  820.       do case
  821.          case pageNo = 1
  822.             CLASS::Page1Setup()
  823.          case pageNo = 2
  824.             CLASS::Page2Setup()
  825.          case pageNo = 3
  826.             CLASS::Page3Setup()
  827.       endcase
  828.    endif
  829.  
  830.  
  831.    ****************************************************************************
  832.  
  833.    Procedure Page1Setup
  834.  
  835.    * Set up Statement Builder page when it is selected
  836.    ****************************************************************************
  837.    private savePath, saveDirectory
  838.  
  839.    savePath = form.savePath
  840.    saveDirectory = form.saveDirectory
  841.  
  842.    set exact off
  843.    set path to &savePath
  844.    set directory to &saveDirectory
  845.    form.okButton1.OnClick = CLASS::Page1OkButton1_OnClick
  846.    form.runSQLButton.OnClick = CLASS::RunSQLButton_OnClick
  847.    form.clearSQLButton.OnClick = CLASS::ClearSQLButton_OnClick
  848.    form.OnMouseMove = CLASS::Form_OnMouseMove
  849.  
  850.  
  851.  
  852.    ****************************************************************************
  853.  
  854.    Procedure Page2Setup
  855.  
  856.  
  857.    * Set up Templates page when it is selected
  858.    ****************************************************************************
  859.    private savePath, saveDirectory
  860.  
  861.    savePath = form.savePath
  862.    saveDirectory = form.saveDirectory
  863.  
  864.    set exact on
  865.    set path to &savePath
  866.    set directory to &saveDirectory
  867.    form.okButton1.OnClick = CLASS::Page2OkButton1_OnClick
  868.    form.runSQLButton.OnClick = CLASS::TemplateRunSQLButton_OnClick
  869.    form.clearSQLButton.OnClick = CLASS::TemplateClearSQLButton_OnClick
  870.    form.clearSQLButton.OnClick()
  871.    form.templateNum = 1
  872.    form.template1Radio.SetFocus()
  873.    form.OnMouseMove = .F.
  874.  
  875.  
  876.    ****************************************************************************
  877.  
  878.    Procedure Page3Setup
  879.  
  880.    * Set up Examples page when it is selected
  881.    ****************************************************************************
  882.  
  883.    set exact off
  884.    form.view = "Sql.qbe"
  885.    if file(_dbwinhome + "samples\animals.dbf")
  886.       set path to &_dbwinhome.utils
  887.       set directory to &_dbwinhome.samples
  888.       form.executeButton.enabled = .T.
  889.    else
  890.       InformationMessage("The tables necessary for running the " +;
  891.                          "commands on this page do not exist in the " +;
  892.                          upper(_dbwinhome) + "SAMPLES directory. " +;
  893.                          chr(13) +;
  894.                          "You will be able to see " +;
  895.                          "the code, but not execute it.", ;
  896.                          "Info")
  897.       form.executeButton.enabled = .F.
  898.    endif
  899.    CLASS::Page3Start()
  900.    form.OnMouseMove = .F.
  901.  
  902.  
  903.  
  904.  
  905.    ****************************************************************************
  906.  
  907.    Procedure ShowSQLButton_OnClick
  908.    ****************************************************************************
  909.  
  910.    if form.pageNo = 1        && Statement Builder
  911.       if CLASS::MakeSQLStatement(DISPLAY_WARNING)
  912.          InformationMessage(form.sqlStatement, "SQL")
  913.       endif
  914.    else                 && PageNo = 2 (templates)
  915.       if CLASS::MakeTemplateSQLStatement()
  916.          InformationMessage(form.sqlStatement, "SQL")
  917.       endif
  918.    endif
  919.  
  920.  
  921.  
  922.  
  923.    ****************************************************************************
  924.  
  925.    Procedure FieldsToolButton_OnClick
  926.    ****************************************************************************
  927.    private fieldsForm, i, selectedControl, selectedFieldCnt, selectedTableCnt,;
  928.               serverName
  929.  
  930.    if empty(form.statementCombo.value)
  931.       InformationMessage("You need to select a SQL command from the combobox" +;
  932.                          " at the top of the form.", "Info")
  933.    else
  934.       if .not. empty(form.fieldsEntry.value)
  935.          form.clearSQLButton.OnClick()          && Clear previous clauses
  936.       endif
  937.       create session
  938.       set talk off
  939.       set ldCheck off
  940.  
  941.       set procedure to &_dbwinhome.utils\GetField.wfm additive
  942.       fieldsForm = new GetFieldForm()
  943.       fieldsForm.mdi = .F.
  944.       if form.curCommand = "INSERT"        && If INSERT, only allow
  945.          fieldsForm.oneTable = .T.         && selection of one table
  946.       endif
  947.  
  948.       selectedControl = fieldsForm.ReadModal()
  949.  
  950.       * If form wasn't CANCELed, Retrieve field and table information
  951.       if type("selectedControl") = "O" .and. selectedControl.text <> "Cancel"
  952.          form.tablesFromDatabase = fieldsForm.tablesFromDatabase
  953.          if form.tablesFromDatabase             && Set database if tables from
  954.             serverName = fieldsForm.serverName  && database
  955.             form.serverName = serverName
  956.             set database to &serverName
  957.          endif
  958.          * If all fields were selected, use ALL_FIELDS indicator
  959.          if fieldsForm.allFields = .T. .and. (.not. form.curCommand = "INSERT")
  960.                                            && All fields were selected
  961.             form.fieldsEntry.value = ALL_FIELDS
  962.          else
  963.             CLASS::ListFieldsInEntryfield(fieldsForm)
  964.          endif
  965.  
  966.          CLASS::ListTablesInEntryfield(fieldsForm)
  967.  
  968.          form.selectedTablesAr = fieldsForm.tableNamesAr
  969.          if form.curCommand <> "INSERT"    && If command wasn't INSERT
  970.             form.selectedFieldsAr = CLASS::GetAllTableFields()
  971.          else
  972.             form.selectedFieldsAr = fieldsForm.selectedAr
  973.             form.CreateInsertValues()
  974.          endif
  975.  
  976.          show object form.fieldsEntry      && Just in case
  977.          show object form.tablesEntry
  978.       endif
  979.       close procedure &_dbwinhome.utils\GetField.wfm
  980.       fieldsForm.Release()
  981.    endif
  982.  
  983.  
  984.  
  985.    ****************************************************************************
  986.  
  987.    Procedure TablesToolButton_OnClick
  988.    ****************************************************************************
  989.    private tablesForm, selected, tableName, aliasName, saveDbType,;
  990.               saveArea, tempTable, i, directory, fieldCnt, fullTableName
  991.  
  992.  
  993.    saveDbType = setto("dbType")
  994.  
  995.    create session
  996.    set talk off
  997.    set ldCheck off
  998.  
  999.    set procedure to &_dbwinhome.utils\GetTable.wfm additive
  1000.  
  1001.    tablesForm = new GetTableForm()
  1002.    tablesForm.mdi = .F.
  1003.    selected = tablesForm.ReadModal()
  1004.    if type("selected") = "O" .and. type("tablesForm.selectedTable") = "C"
  1005.       if .not. empty(form.tablesEntry.value)
  1006.          form.clearSQLButton.OnClick()          && Clear previous clauses
  1007.       endif
  1008.       tableName = tablesForm.selectedTable
  1009.  
  1010.       form.tablesFromDatabase = tablesForm.tablesFromDatabase
  1011.  
  1012.       CLASS::WriteTableInEntryfield(tablesForm)         && Write table name
  1013.  
  1014.       form.selectedTablesAr = new array(1)              && using correct
  1015.       if form.tablesFromDatabase                        && server/table syntax
  1016.          form.selectedTablesAr[1] = tableName
  1017.          fullTableName = tableName
  1018.          aliasName = tableName
  1019.          serverName = tablesForm.serverName
  1020.          form.serverName = serverName
  1021.          set database to &serverName
  1022.       else
  1023.          form.selectedTablesAr[1] = rtrim(form.tablesEntry.value)
  1024.          aliasName = GetAliasName(tableName)
  1025.          fullTableName = StripQuotes(form.selectedTablesAr[1])
  1026.          if right(tableName, 3) = ".db"    && Paradox table
  1027.             set dbType to Paradox
  1028.          endif
  1029.          serverName = ""
  1030.       endif
  1031.  
  1032.  
  1033.       saveArea = str(workarea())
  1034.       select select()
  1035.       use (fullTableName)
  1036.       tempTable = "T" + left(funique(),7)     && Temporary table for structure (some servers
  1037.       copy structure extended to (tempTable)  && don't allow all numeric table names)
  1038.       use (tempTable)
  1039.       *** MEMO, BINARY, OLE fields aren't available in SQL statements
  1040.       count to fieldCnt for (.not. field_type $ MEMO_BINARY_OLE)
  1041.       go top
  1042.       form.selectedFieldsAr = new array(fieldCnt, 1)
  1043.       copy all to array form.selectedFieldsAr fields field_name;
  1044.          for (.not. field_type $ MEMO_BINARY_OLE)
  1045.  
  1046.       CLASS::CorrectFieldSyntax(aliasName, serverName) && Make sure fields
  1047.       use                                         && use correct server syntax
  1048.       delete table (tempTable)
  1049.       select &saveArea
  1050.       set dbType to &saveDbType
  1051.  
  1052.       *** set up rest of controls for specific SQL commands
  1053.       if form.curCommand = "UPDATE"
  1054.          form.updateFieldsCombo.datasource = "array form.selectedFieldsAr"
  1055.          show object form.updateFieldsCombo
  1056.  
  1057.  
  1058.          *** Value doesn't show up right away
  1059.          form.updateFieldsCombo.value = form.selectedFieldsAr[1]
  1060.          form.updateFieldsCombo.OnChange()
  1061.       endif
  1062.    endif
  1063.    tablesForm.Release()
  1064.    close procedure &_dbwinhome.utils\GetTable.wfm
  1065.  
  1066.  
  1067.    ****************************************************************************
  1068.  
  1069.    Procedure AddClauseButton_OnClick
  1070.    ****************************************************************************
  1071.    private strCnt, strCntMinus1, articleCombo, valueCombo, fieldCombo
  1072.  
  1073.    if empty(form.tablesEntry.value)
  1074.       InformationMessage(;
  1075.          "You must select some fields from a table before adding a clause." +;
  1076.             chr(13) + "Click the tool button on the 'SELECT' line.",;
  1077.          "Info")
  1078.    else
  1079.       form.clauseCnt = form.clauseCnt + 1
  1080.       strCnt = TRIMSTR(form.clauseCnt)
  1081.  
  1082.       if form.clauseCnt = 1                        && First clause for UPDATE
  1083.          DEFINE TEXT WHERETEXT OF FORM;
  1084.              PROPERTY;
  1085.                Text "WHERE",;
  1086.                Top this.top,;
  1087.                Height 1,;
  1088.                Left 2.5,;
  1089.                PageNo 1,;
  1090.                Width 8,;
  1091.                FontBold .F.,;
  1092.                ColorNormal "BTNTEXT/BTNFACE";
  1093.              CUSTOM;
  1094.                Value "WHERE"
  1095.  
  1096.          * Button is invisible so don't see it moving after this line
  1097.          this.top = this.top + .9
  1098.          this.text = "Add Clause"
  1099.          this.visible = .T.
  1100.  
  1101.          form.groupByButton.visible = .T.
  1102.          form.orderByButton.visible = .T.
  1103.  
  1104.       else
  1105.          strCntMinus1 = TRIMSTR(form.clauseCnt - 1)
  1106.          articleCombo = form.articleCombo&strCntMinus1
  1107.          if empty(articleCombo.value)
  1108.             articleCombo.value = " AND "
  1109.          endif
  1110.       endif
  1111.       DEFINE COMBOBOX FIELDCOMBO&strCnt OF FORM;
  1112.           PROPERTY;
  1113.             Top this.top,;
  1114.             FontBold .F.,;
  1115.             FontName "MS Serif",;
  1116.             FontSize 7,;
  1117.             Value "",;
  1118.             Style 2,;
  1119.             DropDownHeight 6,;
  1120.             Height 1,;
  1121.             Left 2.5,;
  1122.             PageNo 1,;
  1123.             Width 20,;
  1124.             ColorNormal "N/BTNFACE",;
  1125.             DataSource "array form.selectedFieldsAr",;
  1126.             OnChange CLASS::FIELDCOMBO_OnChange
  1127.  
  1128.       DEFINE COMBOBOX OPERATORCOMBO&strCnt OF FORM;
  1129.           PROPERTY;
  1130.             Top this.top,;
  1131.             FontBold .F.,;
  1132.             FontName "MS Serif",;
  1133.             FontSize 7,;
  1134.             Value "=",;
  1135.             Style 2,;
  1136.             DropDownHeight 6,;
  1137.             Height 1,;
  1138.             Left 23.0,;
  1139.             PageNo 1,;
  1140.             Width 9.335,;
  1141.             ColorNormal "WINDOWTEXT/WINDOW",;
  1142.             DataSource "array form.operatorAr",;
  1143.             OnChange CLASS::OPERATORCOMBO_OnChange
  1144.  
  1145.       DEFINE COMBOBOX VALUECOMBO&strCnt OF FORM;
  1146.           PROPERTY;
  1147.             Top this.top,;
  1148.             FontBold .F.,;
  1149.             FontName "MS Serif",;
  1150.             FontSize 7,;
  1151.             Value "",;
  1152.             Height 1,;
  1153.             Left 33,;
  1154.             PageNo 1,;
  1155.             Width 30.666,;
  1156.             Style 1,;
  1157.             ColorNormal "WINDOWTEXT/WINDOW",;
  1158.             OnChange CLASS::VALUECOMBO_OnChange,;
  1159.             OnLostFocus CLASS::VALUECOMBO_OnLostFocus
  1160.  
  1161.       if form.clauseCnt < MAX_ALLOWED_CLAUSES
  1162.          DEFINE COMBOBOX ARTICLECOMBO&strCnt OF FORM;
  1163.              PROPERTY;
  1164.                Top this.top,;
  1165.                FontBold .F.,;
  1166.                FontName "MS Serif",;
  1167.                FontSize 7,;
  1168.                Value "     ",;
  1169.                Style 2,;
  1170.                DropDownHeight 6,;
  1171.                Height 1,;
  1172.                Left 65.5,;
  1173.                PageNo 1,;
  1174.                Width 10,;
  1175.                ColorNormal "WINDOWTEXT/WINDOW",;
  1176.                DataSource "array form.articleAr"
  1177.  
  1178.          this.top = this.top + 1.2
  1179.       else
  1180.          this.visible = .F.
  1181.       endif
  1182.       fieldCombo = form.fieldCombo&strCnt
  1183.       fieldCombo.OnChange()
  1184.    endif
  1185.  
  1186.  
  1187.    ****************************************************************************
  1188.  
  1189.    Procedure Page1OkButton1_OnClick
  1190.    ****************************************************************************
  1191.    local closeForm
  1192.  
  1193.    closeForm = .T.
  1194.    if CLASS::MakeSQLStatement(NO_WARNING)
  1195.       CLASS::SaveSQLStatement()
  1196.       closeForm = .T.
  1197.    else
  1198.       if ConfirmationMessage(;
  1199.          "The SQL statement is not complete." + chr(13) +;
  1200.          "Are you sure you want to close this form?", "Confirmation") == YES
  1201.          closeForm = .T.
  1202.       else
  1203.          closeForm = .F.
  1204.       endif
  1205.    endif
  1206.    if closeForm
  1207.       form.Close()
  1208.    endif
  1209.  
  1210.  
  1211.    ****************************************************************************
  1212.  
  1213.    Procedure SaveSQLStatement
  1214.    * Save created SQL statement to a file
  1215.    ****************************************************************************
  1216.    local fileName, fileHandle
  1217.  
  1218.    if ConfirmationMessage(;
  1219.       "Save created SQL statement to a .qbe file?", "Confirmation") == YES
  1220.       fileName = putFile(;
  1221.          "Enter a new file name for your SQL query", "*.qbe", "qbe")
  1222.       if .not. empty(fileName)
  1223.          fileHandle = fcreate(fileName)
  1224.          fputs(fileHandle, form.sqlStatement)
  1225.          fclose(fileHandle)
  1226.       endif
  1227.    endif
  1228.  
  1229.  
  1230.    ****************************************************************************
  1231.  
  1232.    Procedure RunSQLButton_OnClick
  1233.    ****************************************************************************
  1234.    private sqlStatement, saveArea, tableName
  1235.  
  1236.    if CLASS::MakeSQLStatement(DISPLAY_WARNING)
  1237.       sqlStatement = form.sqlStatement
  1238.       &sqlStatement
  1239.       if form.curCommand $ "UPDATE,INSERT,DELETE"
  1240.          if ConfirmationMessage("UPDATE, INSERT and DELETE statements do not " +;
  1241.                             "produce an answer table.  Would you like to " +;
  1242.                             "see the resulting table?", "Confirm") = YES
  1243.  
  1244.             saveArea = str(workarea())
  1245.             select select()
  1246.             tableName = StripAllQuotes(form.tablesEntry.value)
  1247.             use (tableName)
  1248.             do Browse.wfm with .T.  && Open browse form modally
  1249.             use
  1250.             select &saveArea
  1251.          endif
  1252.       else
  1253.          if reccount() > 0
  1254.             browse
  1255.          else
  1256.             InformationMessage("There are no records in this answer table.",;
  1257.                                "Info")
  1258.  
  1259.          endif
  1260.       endif
  1261.    endif
  1262.  
  1263.  
  1264.    ****************************************************************************
  1265.  
  1266.    Procedure CLEARSQLBUTTON_OnClick
  1267.  
  1268.    * Clear all clause controls that have been defined
  1269.    ****************************************************************************
  1270.    private strCnt
  1271.  
  1272.    if form.curCommand = "INSERT"
  1273.       CLASS::InsertValuesClear()
  1274.    else
  1275.       CLASS::WhereClauseClear()
  1276.       CLASS::GroupByClauseClear()
  1277.       CLASS::OrderByClauseClear()
  1278.    endif
  1279.  
  1280.    CLASS::ClearTables()
  1281.  
  1282.    form.clauseCnt = 0
  1283.    form.insertValuesCnt = 0
  1284.    form.selectedTablesAr = new array(0)
  1285.    form.selectedFieldsAr = new array(0)
  1286.    form.serverName = ""
  1287.    form.tablesFromDatabase = .F.
  1288.  
  1289.  
  1290.    ****************************************************************************
  1291.  
  1292.    Procedure WhereClauseClear
  1293.  
  1294.    ****************************************************************************
  1295.    private strCnt, fieldCombo, operatorCombo, valueCombo, articleCombo,;
  1296.               clauseCnt
  1297.  
  1298.    clauseCnt = form.clauseCnt
  1299.    if clauseCnt > 0
  1300.       form.whereText.Release()
  1301.  
  1302.       form.addClauseButton.top = 3.1172
  1303.       form.addClauseButton.text = "Where"
  1304.       form.addClauseButton.visible = .T.
  1305.  
  1306.       for i = 1 to clauseCnt
  1307.          strCnt = TRIMSTR(i)
  1308.          fieldCombo = form.fieldCombo&strCnt
  1309.          operatorCombo = form.operatorCombo&strCnt
  1310.          valueCombo = form.valueCombo&strCnt
  1311.          fieldCombo.Release()
  1312.          operatorCombo.Release()
  1313.          valueCombo.Release()
  1314.          if i < MAX_ALLOWED_CLAUSES
  1315.             articleCombo = form.articleCombo&strCnt
  1316.             articleCombo.Release()
  1317.          endif
  1318.       next i
  1319.       form.clauseCnt = 0
  1320.    endif
  1321.  
  1322.    ****************************************************************************
  1323.  
  1324.    Procedure GroupByClauseClear
  1325.  
  1326.    ****************************************************************************
  1327.    private strCnt
  1328.  
  1329.    if type("form.groupByText") <> "U"        && If Group By clause was selected
  1330.       form.groupByText.Release()
  1331.       form.groupByFieldCombo.Release()
  1332.       form.havingButton.Release()
  1333.       if type("form.havingText") <> "U"      && If Having clause was selected
  1334.          form.havingText.Release()
  1335.          form.havingFieldsCombo.Release()
  1336.          form.havingOperatorCombo.Release()
  1337.          form.havingValueCombo.Release()
  1338.       endif
  1339.    endif
  1340.  
  1341.  
  1342.    ****************************************************************************
  1343.  
  1344.    Procedure OrderByClauseClear
  1345.  
  1346.    ****************************************************************************
  1347.    private strCnt
  1348.  
  1349.    if type("form.orderByText") <> "U"        && If Order By clause was selected
  1350.       form.orderByText.Release()
  1351.       form.orderByFieldsCombo.Release()
  1352.    endif
  1353.  
  1354.  
  1355.    ****************************************************************************
  1356.  
  1357.    Procedure InsertValuesClear
  1358.  
  1359.    ****************************************************************************
  1360.    private strCnt, insertValueCnt,;
  1361.               insertValueText, insertValueCombo, insertComma, insertLastParen
  1362.  
  1363.    insertValueCnt = form.insertValueCnt
  1364.    if form.insertValueCnt > 0
  1365.       for i = 1 to insertValueCnt
  1366.          strCnt = TRIMSTR(i)
  1367.          insertValueText = form.insertValueText&strCnt
  1368.          insertValueCombo = form.insertValueCombo&strCnt
  1369.          insertValueText.Release()
  1370.          insertValueCombo.Release()
  1371.          if i < insertValueCnt
  1372.             insertComma = form.insertValueCommaText&strCnt
  1373.             insertComma.Release()
  1374.          else
  1375.             form.lastValueParenText.Top = form.statementText.top - .1
  1376.             form.lastValueParenText.Left = form.statementText.left +;
  1377.                                               len(form.statementText.value) + 1
  1378.          endif
  1379.       next i
  1380.       form.insertValueCnt = 0
  1381.    endif
  1382.  
  1383.  
  1384.    ****************************************************************************
  1385.  
  1386.    Procedure ClearTables
  1387.  
  1388.    ****************************************************************************
  1389.    private command
  1390.  
  1391.    command = form.curCommand
  1392.    do case
  1393.       case command = "SELECT"
  1394.          form.tablesEntry.value = ""
  1395.          form.fieldsEntry.value = ""
  1396.       case command = "UPDATE"
  1397.          form.tablesEntry.value = ""
  1398.          form.updateFieldsCombo.dataSource = ""
  1399.          form.updateFieldsCombo.value = ""
  1400.          form.updateValueCombo.dataSource = ""
  1401.          form.updateValueCombo.value = ""
  1402.       case command = "INSERT"
  1403.          form.tablesEntry.value = ""
  1404.          form.fieldsEntry.value = ""
  1405.       case command = "DELETE"
  1406.          form.tablesEntry.value = ""
  1407.    endcase
  1408.    close database
  1409.    set database to
  1410.  
  1411.  
  1412.    ****************************************************************************
  1413.  
  1414.    Function MakeSQLStatement(message)
  1415.    ****************************************************************************
  1416.    private madeStatement, control, sqlStatement, ending
  1417.  
  1418.    control = form.first
  1419.    sqlStatement = ""
  1420.  
  1421.    madeStatement = .F.
  1422.  
  1423.    * Make sure there is a table/fields to operate on
  1424.    if .not. empty(form.fieldsEntry.value) .or. .not. empty(form.tablesEntry.value)
  1425.       madeStatement = .T.
  1426.  
  1427.  
  1428.       * Make a string of all controls that are visible and have a value property
  1429.       do
  1430.          if control.pageNo = 1 .and. type("control.value") <> "U";
  1431.                .and. control.visible
  1432.             sqlStatement = sqlStatement + ALLTRIM(control.value) + " "
  1433.          endif
  1434.          control = control.before
  1435.       until control.hwnd = form.first.hwnd
  1436.  
  1437.       * Strip and/or if statement ended in an article
  1438.       ending = right(sqlStatement, 4)
  1439.       if (ending == "AND " .or. ending == " OR ")
  1440.          sqlStatement = left(sqlStatement, len(sqlStatement) - 4)
  1441.       endif
  1442.  
  1443.       ?sqlStatement
  1444.       form.sqlStatement = sqlStatement
  1445.    else
  1446.       madeStatement = .F.
  1447.       if message = DISPLAY_WARNING
  1448.          InformationMessage("The SQL statement is not complete. " +;
  1449.                             "If you want to complete it, you should " +;
  1450.                             "select a table/fields.", "Oops")
  1451.       endif
  1452.    endif
  1453.  
  1454.    return madeStatement
  1455.  
  1456.  
  1457.    ****************************************************************************
  1458.  
  1459.    Procedure SetUpSelect
  1460.    ****************************************************************************
  1461.  
  1462.    form.CleanupCurrentCommand = CLASS::CleanupSelect
  1463.    form.fieldsEntry.visible = .T.
  1464.    form.fieldsToolButton.visible = .T.
  1465.    form.tablesEntry.enabled = .F.
  1466.    form.tablesEntry.top = 1.8
  1467.    form.tablesToolButton.top = 1.8
  1468.    form.tablesToolButton.visible = .F.
  1469.    form.statementText.text = "FROM"
  1470.    form.statementText.value = "FROM"
  1471.  
  1472.    form.groupByButton.visible = .F.
  1473.    form.orderByButton.visible = .F.
  1474.  
  1475.    ****************************************************************************
  1476.  
  1477.    procedure CleanupSelect
  1478.    ****************************************************************************
  1479.  
  1480.    form.selectedFieldsAr = new array()
  1481.    form.selectedTablesAr = new array()
  1482.    form.fieldsEntry.value = ""
  1483.    form.tablesEntry.value = ""
  1484.  
  1485.  
  1486.    ****************************************************************************
  1487.  
  1488.    Procedure SetUpUpdate
  1489.    ****************************************************************************
  1490.  
  1491.    form.CleanupCurrentCommand = CLASS::CleanupUpdate
  1492.    form.fieldsEntry.visible = .F.
  1493.    form.fieldsToolButton.visible = .F.
  1494.    form.tablesEntry.top = .5            && Where Fields entry usually is
  1495.    form.tablesToolButton.visible = .T.
  1496.    form.tablesToolButton.top = .5
  1497.    form.statementText.text = "SET"
  1498.    form.statementText.value = "SET"
  1499.    form.tablesEntry.before = form.statementText
  1500.  
  1501.    form.groupByButton.visible = .F.
  1502.    form.orderByButton.visible = .F.
  1503.  
  1504.    DEFINE COMBOBOX UPDATEFIELDSCOMBO OF FORM;
  1505.        PROPERTY;
  1506.          ColorNormal "WINDOWTEXT/WINDOW",;
  1507.          DataSource "array form.selectedFieldsAr",;
  1508.          Style 2,;
  1509.          Top 1.8,;
  1510.          FontName "Ms Serif",;
  1511.          FontSize 7,;
  1512.          FontBold .F.,;
  1513.          Width 24.5,;
  1514.          Left 23,;
  1515.          Height 1,;
  1516.          PageNo 1,;
  1517.          OnChange CLASS::UPDATEFIELDSCOMBO_OnChange
  1518.  
  1519.    DEFINE TEXT UPDATEEQUALSTEXT OF FORM;
  1520.       PROPERTY;
  1521.          ColorNormal "BTNTEXT/BTNFACE",;
  1522.          Border .F.,;
  1523.          Text "=",;
  1524.          Top 1.8,;
  1525.          Left 49,;
  1526.          Height 1.26,;
  1527.          FontBold .F.,;
  1528.          PageNo 1;
  1529.       CUSTOM;
  1530.          Value "="
  1531.  
  1532.    DEFINE COMBOBOX UPDATEVALUECOMBO OF FORM;
  1533.       PROPERTY;
  1534.          ColorNormal "WINDOWTEXT/WINDOW",;
  1535.          Top 1.8,;
  1536.          Left 51.5,;
  1537.          Height 1.26,;
  1538.          Width 24,;
  1539.          FontBold .F.,;
  1540.          PageNo 1,;
  1541.          Value "",;
  1542.          OnChange CLASS::UPDATEVALUECOMBO_OnChange
  1543.  
  1544.  
  1545.    ****************************************************************************
  1546.  
  1547.    procedure CleanupUpdate
  1548.    ****************************************************************************
  1549.  
  1550.    form.fieldsEntry.visible = .T.
  1551.    form.fieldsToolButton.visible = .T.
  1552.    form.tablesEntry.top = 1.8
  1553.    form.tablesEntry.value = ""
  1554.    form.tablesToolButton.visible = .F.
  1555.    form.tablesToolButton.top = 1.8
  1556.    form.statementText.text = "FROM"
  1557.    form.statementText.value = "FROM"
  1558.    form.tablesEntry.before = form.tablesToolButton
  1559.  
  1560.    form.updateFieldsCombo.Release()
  1561.    form.updateEqualsText.Release()
  1562.    form.updateValueCombo.Release()
  1563.  
  1564.  
  1565.  
  1566.  
  1567.  
  1568.  
  1569.    ****************************************************************************
  1570.  
  1571.    Procedure SetUpInsert
  1572.    ****************************************************************************
  1573.  
  1574.    form.CleanupCurrentCommand = CLASS::CleanupInsert
  1575.    form.fieldsEntry.visible = .T.
  1576.    form.fieldsEntry.top = 1.8
  1577.    form.fieldsToolButton.visible = .T.
  1578.    form.fieldsToolButton.top = 1.8
  1579.    form.tablesEntry.top = .5
  1580.    form.tablesToolButton.visible = .F.
  1581.  
  1582.    form.addClauseButton.visible = .F.
  1583.    form.groupByButton.visible = .F.
  1584.    form.orderByButton.visible = .F.
  1585.  
  1586.    form.statementText.top = 3
  1587.    form.statementText.text = "VALUES ("
  1588.    form.statementText.value = "VALUES ("
  1589.    form.insertIntoText.visible = .T.
  1590.    form.insertIntoText.value = form.insertIntoText.text
  1591.    form.paren1Text.visible = .T.
  1592.    form.paren1Text.value = form.paren1Text.text
  1593.    form.paren2Text.visible = .T.
  1594.    form.paren2Text.value = form.paren2Text.text
  1595.  
  1596.    DEFINE TEXT LASTVALUEPARENTEXT OF FORM;
  1597.        PROPERTY;
  1598.          Alignment 3,;    && Center left
  1599.          Top form.statementText.top - .05,;
  1600.          Left form.statementText.left + len(form.statementText.value) + 1,;
  1601.          Height 1,;
  1602.          Width 1,;
  1603.          FontBold .F.,;
  1604.          Text ")";
  1605.        CUSTOM;
  1606.          Value ")"
  1607.  
  1608.    * Make sure that when statement is put together, controls are in correct
  1609.    * order
  1610.  
  1611.    form.tablesEntry.before = form.paren1Text
  1612.    form.insertIntoText.before = form.tablesEntry
  1613.  
  1614.  
  1615.  
  1616.    ****************************************************************************
  1617.  
  1618.    Procedure CleanupInsert
  1619.    ****************************************************************************
  1620.  
  1621.    form.fieldsEntry.top = .5
  1622.    form.fieldsEntry.value = ""
  1623.    form.fieldsToolButton.top = .5
  1624.    form.tablesEntry.enabled = .F.
  1625.    form.tablesEntry.top = 1.8
  1626.    form.tablesToolButton.visible = .F.
  1627.    form.statementText.top = 1.8
  1628.    form.statementText.text = "FROM"
  1629.    form.statementText.value = "FROM"
  1630.  
  1631.    form.tablesEntry.before = form.tablesToolButton
  1632.    form.insertIntoText.before = form.tablesEntry
  1633.  
  1634.    form.insertIntoText.visible = .F.
  1635.    form.paren1Text.visible = .F.
  1636.    form.paren2Text.visible = .F.
  1637.  
  1638.    form.addClauseButton.visible = .T.
  1639.  
  1640.    form.lastValueParenText.Release()
  1641.  
  1642.    ****************************************************************************
  1643.  
  1644.    Procedure SetUpDelete
  1645.    ****************************************************************************
  1646.  
  1647.    form.CleanupCurrentCommand = CLASS::CleanupDelete
  1648.    form.fieldsEntry.visible = .F.
  1649.    form.fieldsToolButton.visible = .F.
  1650.    form.tablesEntry.top = 1.8
  1651.    form.tablesToolButton.visible = .T.
  1652.    form.tablesToolButton.top = 1.8
  1653.  
  1654.    form.groupByButton.visible = .F.
  1655.    form.orderByButton.visible = .F.
  1656.  
  1657.    form.statementText.text = "FROM"
  1658.    form.statementText.value = "FROM"
  1659.  
  1660.  
  1661.    ****************************************************************************
  1662.  
  1663.    Procedure CleanupDelete
  1664.    ****************************************************************************
  1665.  
  1666.    form.fieldsEntry.visible = .T.
  1667.    form.fieldsToolButton.visible = .T.
  1668.  
  1669.  
  1670.  
  1671.    ****************************************************************************
  1672.  
  1673.    Procedure GroupByButton_OnClick
  1674.    ****************************************************************************
  1675.  
  1676.  
  1677.    if empty(form.tablesEntry.value)
  1678.       InformationMessage(;
  1679.          "You must select some fields from a table before specifying a group." +;
  1680.             chr(13) + "Click the tool button on the 'SELECT' line.",;
  1681.          "Info")
  1682.    else
  1683.       form.addClauseButton.visible = .F.        && Cannot add any more clauses
  1684.       DEFINE TEXT GROUPBYTEXT OF FORM;
  1685.        PROPERTY;
  1686.          Top this.top,;
  1687.          FontBold .F.,;
  1688.          PageNo 1,;
  1689.          Width 12,;
  1690.          ColorNormal "BTNTEXT/BTNFACE",;
  1691.          Text "GROUP BY",;
  1692.          Height 1.0293,;
  1693.          Left 2.5,;
  1694.          Border .F.;
  1695.        CUSTOM;
  1696.          Value "GROUP BY"
  1697.  
  1698.       DEFINE COMBOBOX GROUPBYFIELDCOMBO OF FORM;
  1699.          PROPERTY;
  1700.             Top this.top + .8,;
  1701.             FontName "MS Serif",;
  1702.             FontSize 7,;
  1703.             FontBold .F.,;
  1704.             PageNo 1,;
  1705.             Width 15.666,;
  1706.             ColorNormal "WINDOWTEXT/WINDOW",;
  1707.             Height 1,;
  1708.             Left 2.5,;
  1709.             Style 2,;
  1710.             DropDownHeight 6,;
  1711.             DataSource "array form.selectedFieldsAr",;
  1712.             Value form.selectedFieldsAr[1]
  1713.  
  1714.       if type("form.havingButton") = "U"
  1715.          DEFINE TOOLBUTTON HAVINGBUTTON OF FORM;
  1716.              PROPERTY;
  1717.                OnClick CLASS::HAVINGBUTTON_ONCLICK,;
  1718.                OnMouseMove CLASS::HAVINGBUTTON_ONMOUSEMOVE,;
  1719.                Top this.top + .8,;
  1720.                Width 13,;
  1721.                Text "Having",;
  1722.                Height 1,;
  1723.                Left 19,;
  1724.                Group .T.,;
  1725.                FontBold .F.
  1726.       endif
  1727.       this.visible = .F.
  1728.    endif
  1729.  
  1730.  
  1731.    ****************************************************************************
  1732.  
  1733.    Procedure HavingButton_OnClick
  1734.    ****************************************************************************
  1735.  
  1736.    * Don't need to check for table open because already checked in Group By
  1737.  
  1738.    this.visible = .F.           && Button out of view while controls defined
  1739.                                 && over it
  1740.  
  1741.    DEFINE TEXT HAVINGTEXT OF FORM;
  1742.        PROPERTY;
  1743.          Top this.top,;
  1744.          FontBold .F.,;
  1745.          PageNo 1,;
  1746.          Width 8.166,;
  1747.          ColorNormal "BTNTEXT/BTNFACE",;
  1748.          Text "HAVING",;
  1749.          Height 1.0293,;
  1750.          Left 20,;
  1751.          Border .F.;
  1752.        CUSTOM;
  1753.          Value "HAVING"
  1754.  
  1755.    DEFINE COMBOBOX HAVINGFIELDSCOMBO OF FORM;
  1756.        PROPERTY;
  1757.          Top this.top,;
  1758.          FontName "Ms Serif",;
  1759.          FontSize 7,;
  1760.          FontBold .F.,;
  1761.          Value "",;
  1762.          Style 2,;
  1763.          DropDownHeight 6,;
  1764.          Height 1,;
  1765.          Left 29,;
  1766.          PageNo 1,;
  1767.          Width 20,;
  1768.          ColorNormal "WINDOWTEXT/WINDOW",;
  1769.          DataSource "array form.selectedFieldsAr",;
  1770.          OnChange CLASS::HAVINGFIELDSCOMBO_OnChange
  1771.  
  1772.    DEFINE COMBOBOX HAVINGOPERATORCOMBO OF FORM;
  1773.        PROPERTY;
  1774.          Top this.top,;
  1775.          FontName "Ms Serif",;
  1776.          FontSize 7,;
  1777.          FontBold .F.,;
  1778.          Value "=",;
  1779.          Style 2,;
  1780.          DropDownHeight 6,;
  1781.          Height 1,;
  1782.          Left 50.0,;
  1783.          PageNo 1,;
  1784.          Width 9.335,;
  1785.          ColorNormal "WINDOWTEXT/WINDOW",;
  1786.          DataSource "array form.operatorAr",;
  1787.          OnChange CLASS::OPERATORCOMBO_ONCHANGE         && This routine used
  1788.                                                         && for this control and
  1789.                                                         && operatorCombo&cnt
  1790.  
  1791.    DEFINE COMBOBOX HAVINGVALUECOMBO OF FORM;
  1792.        PROPERTY;
  1793.          Top this.top,;
  1794.          FontName "Ms Serif",;
  1795.          FontSize 7,;
  1796.          FontBold .F.,;
  1797.          Value "",;
  1798.          Height 1,;
  1799.          Left 60,;
  1800.          PageNo 1,;
  1801.          Width 15,;
  1802.          Style 1,;
  1803.          ColorNormal "WINDOWTEXT/WINDOW",;
  1804.          DataSource "array form.selectedFieldsAr",;
  1805.          OnChange CLASS::HAVINGVALUECOMBO_OnChange
  1806.  
  1807.    form.havingFieldsCombo.OnChange()
  1808.    this.visible = .F.
  1809.  
  1810.    ****************************************************************************
  1811.  
  1812.    Procedure OrderByButton_OnClick
  1813.    ****************************************************************************
  1814.  
  1815.  
  1816.    if empty(form.tablesEntry.value)
  1817.       InformationMessage(;
  1818.          "You must select some fields from a table before specifying an order." +;
  1819.             chr(13) + "Click the tool button on the 'SELECT' line.",;
  1820.          "Info")
  1821.    else
  1822.       form.addClauseButton.visible = .F.        && Cannot add any more clauses
  1823.       form.groupByButton.visible = .F.
  1824.       if type("form.havingButton") <> "U"
  1825.          form.havingButton.visible = .F.
  1826.       endif
  1827.       DEFINE TEXT ORDERBYTEXT OF FORM;
  1828.         PROPERTY;
  1829.           Top this.top,;
  1830.           PageNo 1,;
  1831.           Width 12,;
  1832.           ColorNormal "BTNTEXT/BTNFACE",;
  1833.           Text "ORDER BY",;
  1834.           Height 1,;
  1835.           Left 2.5,;
  1836.           Border .F.,;
  1837.           FontBold .F.;
  1838.         CUSTOM;
  1839.           Value "ORDER BY"
  1840.  
  1841.       DEFINE COMBOBOX ORDERBYFIELDSCOMBO OF FORM;
  1842.           PROPERTY;
  1843.             Top this.top,;
  1844.             FontName "Ms Serif",;
  1845.             FontSize 7,;
  1846.             FontBold .F.,;
  1847.             PageNo 1,;
  1848.             Width 15.666,;
  1849.             ColorNormal "WINDOWTEXT/WINDOW",;
  1850.             Height 1,;
  1851.             Left 19,;
  1852.             Style 2,;
  1853.             DropDownHeight 6,;
  1854.             DataSource "array form.selectedFieldsAr",;
  1855.             Value form.selectedfieldsAr[1]
  1856.  
  1857.       this.visible = .F.
  1858.    endif
  1859.  
  1860.  
  1861.    ****************************************************************************
  1862.  
  1863.    Procedure OperatorCombo_OnChange
  1864.    ****************************************************************************
  1865.  
  1866.    if this.value = "IS NULL"
  1867.       this.before.value = ""
  1868.       this.before.enabled = .F.
  1869.    else
  1870.       this.before.enabled = .T.
  1871.    endif
  1872.  
  1873.  
  1874.    *******************************************************************************
  1875.  
  1876.    Procedure STATEMENTCOMBO_OnMouseMove(flags, col, row)
  1877.    *******************************************************************************
  1878.  
  1879.    if form.mouseOverControl <> "statementCombo"
  1880.       form.mouseOverControl = "statementCombo"
  1881.       form.messageText.text = "Select a SQL command from this combobox"
  1882.    endif
  1883.  
  1884.  
  1885.    *******************************************************************************
  1886.  
  1887.    Procedure FIELDSENTRY_OnMouseMove(flags, col, row)
  1888.    *******************************************************************************
  1889.  
  1890.    if form.mouseOverControl <> "fieldsEntry"
  1891.       form.mouseOverControl = "fieldsEntry"
  1892.       form.messageText.text = "Type in the fields you would like to see " +;
  1893.                               "in your SQL statement, or click on the tool " +;
  1894.                               "button on the right to select the fields."
  1895.    endif
  1896.  
  1897.  
  1898.    *******************************************************************************
  1899.  
  1900.    Procedure TABLESENTRY_OnMouseMove(flags, col, row)
  1901.    *******************************************************************************
  1902.  
  1903.    if form.mouseOverControl <> "tablesEntry"
  1904.       form.mouseOverControl = "tablesEntry"
  1905.       form.messageText.text = "Type in the names of the tables you " +;
  1906.                               "want processed in this SQL statement."
  1907.    endif
  1908.  
  1909.  
  1910.    *******************************************************************************
  1911.  
  1912.    Procedure FIELDSTOOLBUTTON_OnMouseMove(flags, col, row)
  1913.    *******************************************************************************
  1914.  
  1915.    if form.mouseOverControl <> "fieldsToolButton"
  1916.       form.mouseOverControl = "fieldsToolButton"
  1917.       form.messageText.text = "Press this button to select the fields " +;
  1918.                               "you want processed in this SQL command."
  1919.    endif
  1920.  
  1921.    *******************************************************************************
  1922.  
  1923.    Procedure TABLESTOOLBUTTON_OnMouseMove(flags, col, row)
  1924.    *******************************************************************************
  1925.  
  1926.    if form.mouseOverControl <> "tablesToolButton"
  1927.       form.mouseOverControl = "tablesToolButton"
  1928.       form.messageText.text = "Press this button to select the tables " +;
  1929.                               "you want processed in this SQL statement."
  1930.    endif
  1931.  
  1932.    *******************************************************************************
  1933.  
  1934.    Procedure ADDCLAUSEBUTTON_OnMouseMove(flags, col, row)
  1935.    *******************************************************************************
  1936.  
  1937.    if form.mouseOverControl <> "addClauseButton"
  1938.       form.mouseOverControl = "addClauseButton"
  1939.       form.messageText.text = "Press this button to create a condition " +;
  1940.                               "that joins the selected tables, or to " +;
  1941.                               "restrict the data that you will see in the " +;
  1942.                               "answer table."
  1943.    endif
  1944.  
  1945.    *******************************************************************************
  1946.  
  1947.    Procedure GROUPBYBUTTON_OnMouseMove(flags, col, row)
  1948.    *******************************************************************************
  1949.  
  1950.    if form.mouseOverControl <> "groupByButton"
  1951.       form.mouseOverControl = "groupByButton"
  1952.       form.messageText.text = "Press this button to create a grouping " +;
  1953.                               "of data in the answer table."
  1954.    endif
  1955.  
  1956.  
  1957.    *******************************************************************************
  1958.  
  1959.    Procedure HAVINGBUTTON_OnMouseMove(flags, col, row)
  1960.    *******************************************************************************
  1961.  
  1962.    if form.mouseOverControl <> "havingButton"
  1963.       form.mouseOverControl = "havingButton"
  1964.       form.messageText.text = "Press this button to make grouping " +;
  1965.                               "of data in the answer table more specific."
  1966.    endif
  1967.  
  1968.  
  1969.    *******************************************************************************
  1970.  
  1971.    Procedure ORDERBYBUTTON_OnMouseMove(flags, col, row)
  1972.    *******************************************************************************
  1973.  
  1974.    if form.mouseOverControl <> "orderByButton"
  1975.       form.mouseOverControl = "orderByButton"
  1976.       form.messageText.text = "Press this button to select an order " +;
  1977.                               "for viewing the selected data."
  1978.    endif
  1979.  
  1980.    *******************************************************************************
  1981.  
  1982.    Procedure STATEMENTRECT_OnMouseMove(flags, col, row)
  1983.    *******************************************************************************
  1984.  
  1985.    form.mouseOverControl = "statementRect"
  1986.    form.messageText.text = ""
  1987.  
  1988.  
  1989.    *******************************************************************************
  1990.  
  1991.    Procedure MESSAGERECT_OnMouseMove(flags, col, row)
  1992.    *******************************************************************************
  1993.  
  1994.    form.mouseOverControl = "messageRect"
  1995.    form.messageText.text = ""
  1996.  
  1997.  
  1998.    *******************************************************************************
  1999.  
  2000.    Procedure FORM_OnMouseMove(flags, col, row)
  2001.    *******************************************************************************
  2002.  
  2003.    form.mouseOverControl = "form"
  2004.    form.messageText.text = ""
  2005.  
  2006.  
  2007.    *******************************************************************************
  2008.  
  2009.    Procedure PAGETABBOX_OnMouseMove(flags, col, row)
  2010.    *******************************************************************************
  2011.  
  2012.    if form.mouseOverControl <> "pageTabBox"
  2013.       form.mouseOverControl = "pageTabBox"
  2014.       form.messageText.text = "Select the 'Statement Builder' tab " +;
  2015.                               "to create a SQL statement, the 'Templates' tab " +;
  2016.                               "to use templates for creating a SQL statement, " +;
  2017.                               "and the Examples tab for seeing examples of SQL and " +;
  2018.                               "dBASE statements performing the same action."
  2019.    endif
  2020.  
  2021.  
  2022.    *******************************************************************************
  2023.  
  2024.    Procedure RUNSQLBUTTON_OnMouseMove(flags, col, row)
  2025.    *******************************************************************************
  2026.  
  2027.    if form.mouseOverControl <> "runSqlButton"
  2028.       form.mouseOverControl = "runSqlButton"
  2029.       form.messageText.text = "Run the created SQL statement.  If an answer " +;
  2030.                               "table is produced, display it in browse format."
  2031.    endif
  2032.  
  2033.  
  2034.    *******************************************************************************
  2035.  
  2036.    Procedure CLEARSQLBUTTON_OnMouseMove(flags, col, row)
  2037.    *******************************************************************************
  2038.  
  2039.    if form.mouseOverControl <> "clearSqlButton"
  2040.       form.mouseOverControl = "clearSqlButton"
  2041.       form.messageText.text = "Clear the displayed SQL statement.  Display " +;
  2042.                               "the statement as if nothing had been added " +;
  2043.                               "to it yet."
  2044.    endif
  2045.  
  2046.  
  2047.  
  2048.    *******************************************************************************
  2049.  
  2050.    Procedure SHOWSQLBUTTON_OnMouseMove(flags, col, row)
  2051.    *******************************************************************************
  2052.  
  2053.    if form.mouseOverControl <> "showSqlButton"
  2054.       form.mouseOverControl = "showSqlButton"
  2055.       form.messageText.text = "Show the created SQL statement."
  2056.    endif
  2057.  
  2058.  
  2059.    *******************************************************************************
  2060.  
  2061.    Procedure OKBUTTON1_OnMouseMove(flags, col, row)
  2062.    *******************************************************************************
  2063.  
  2064.    if form.mouseOverControl <> "okButton1"
  2065.       form.mouseOverControl = "okButton1"
  2066.       form.messageText.text = "Close form with option to save statement " +;
  2067.                               "to a .qbe file; also save statement " +;
  2068.                               "as a property (sqlStatement) " +;
  2069.                               "of the form, as well as output statement " +;
  2070.                               "to the Command Results pane."
  2071.    endif
  2072.  
  2073.  
  2074.    *******************************************************************************
  2075.  
  2076.    Procedure CANCELBUTTON1_OnMouseMove(flags, col, row)
  2077.    *******************************************************************************
  2078.  
  2079.    if form.mouseOverControl <> "cancelButton1"
  2080.       form.mouseOverControl = "cancelButton1"
  2081.       form.messageText.text = "Close the form without saving any " +;
  2082.                               "statement that might have been created."
  2083.    endif
  2084.  
  2085.  
  2086.  
  2087.    ****************************************************************************
  2088.  
  2089.    Procedure FIELDCOMBO_OnChange
  2090.    ****************************************************************************
  2091.    private strCnt, valueCombo
  2092.  
  2093.    strCnt = right(this.name, 1)
  2094.    valueCombo = form.valueCombo&strCnt
  2095.    CLASS::UpdateFields(this, valueCombo,;
  2096.                          CLASS::ValueCombo_OnChange, "temp")
  2097.    this.SetFocus()
  2098.  
  2099.  
  2100.    ****************************************************************************
  2101.  
  2102.    Procedure VALUECOMBO_OnChange
  2103.    ****************************************************************************
  2104.    private strCnt, fieldCombo
  2105.  
  2106.    strCnt = right(this.name, 1)
  2107.    fieldCombo = form.fieldcombo&strCnt
  2108.    CLASS::UpdateValues(fieldCombo, this, "temp")
  2109.  
  2110.  
  2111.    ****************************************************************************
  2112.  
  2113.    Procedure VALUECOMBO_OnLostFocus
  2114.  
  2115.    * DOESN'T WORK NOW
  2116.    * Make sure that when leave combobox, if the operator is not "IS NULL", it is
  2117.    * set to "is null", so the statement would be valid
  2118.    ****************************************************************************
  2119.    private strCnt, operatorCombo
  2120.  
  2121.    if empty(this.value)
  2122.       strCnt = right(this.name, 1)
  2123.       operatorCombo = form.operatorCombo&strCnt
  2124.       if operatorCombo.value <> "IS NULL"            && If operator expects
  2125.          REDEFINE COMBOBOX OPERATOR&strCnt OF FORM;  && a value on the right
  2126.             PROPERTY;
  2127.                value "IS NULL"
  2128.    *        operatorCombo.value = "IS NULL"
  2129.    *        show object opertorCombo
  2130.       endif
  2131.    endif
  2132.  
  2133.  
  2134.  
  2135.  
  2136.  
  2137.    ****************************************************************************
  2138.  
  2139.    Procedure HAVINGFIELDSCOMBO_OnChange
  2140.    ****************************************************************************
  2141.  
  2142.    CLASS::UpdateFields(this, form.havingValueCombo,;
  2143.                           CLASS::HavingValueCombo_OnChange, "temp2")
  2144.    this.SetFocus()
  2145.  
  2146.  
  2147.    ****************************************************************************
  2148.  
  2149.    Procedure HAVINGVALUECOMBO_OnChange
  2150.    ****************************************************************************
  2151.  
  2152.    CLASS::UpdateValues(form.havingFieldsCombo, this, "temp2")
  2153.  
  2154.  
  2155.    ****************************************************************************
  2156.  
  2157.    Procedure UPDATEFIELDSCOMBO_OnChange
  2158.    ****************************************************************************
  2159.  
  2160.    CLASS::UpdateFields(this, form.updateValueCombo,;
  2161.                          CLASS::UpdateValueCombo_OnChange, "temp")
  2162.  
  2163.    this.SetFocus()
  2164.  
  2165.  
  2166.    ****************************************************************************
  2167.  
  2168.    Procedure UPDATEVALUECOMBO_OnChange
  2169.    ****************************************************************************
  2170.  
  2171.    CLASS::UpdateValues(form.updateFieldsCombo, this, "temp")
  2172.  
  2173. ****************************************************************************
  2174.  
  2175.    Procedure UpdateFields
  2176.  
  2177.    ****************************************************************************
  2178.    * tempAlias is used in macro, so cannot be inline parameter
  2179.    param fieldCombo, valueCombo, ValueComboOnChange, tempAlias, serverName
  2180.  
  2181.    private fullTableName, fieldName, valueComboName, dataSource
  2182.  
  2183.    if select(tempAlias) > 0      && if already opened table in tempAlias
  2184.       use in (tempAlias)
  2185.    endif
  2186.    if form.tablesFromDatabase .and. empty(setto("database"))
  2187.       serverName = form.serverName
  2188.       open database &serverName
  2189.       set database to &serverName
  2190.    endif
  2191.  
  2192.    fullTableName = CLASS::GetFullTableName(;
  2193.                              CLASS::GetTableName(fieldCombo.value))
  2194.    fieldName = CLASS::GetFieldName(fieldCombo.value)
  2195.  
  2196.    use (fullTableName) in select() alias &tempAlias
  2197.    if type(tempAlias + "->" + fieldName) $ MEMO_BINARY_OLE
  2198.       dataSource = ""
  2199.    else
  2200.       dataSource = "field " + tempAlias + "->" + fieldName
  2201.    endif
  2202.  
  2203.    valueComboName = valueCombo.name
  2204.  
  2205.    REDEFINE COMBOBOX &valueComboName OF FORM;
  2206.       PROPERTY;
  2207.         DataSource dataSource,;
  2208.         OnChange ValueComboOnchange
  2209.  
  2210.    valueCombo.width = min(valueCombo.width, 24)         && Width to fit in form
  2211.  
  2212.    valueCombo.OnChange()
  2213.  
  2214.  
  2215.  
  2216.    ****************************************************************************
  2217.  
  2218.    Procedure UpdateValues(fieldCombo, valueCombo, tempAlias)
  2219.    ****************************************************************************
  2220.    private fieldName, fieldType, operatorCombo
  2221.  
  2222.    fieldName = tempAlias + "->" + CLASS::GetFieldName(fieldCombo.value)
  2223.    fieldType = type(fieldName)
  2224.    operatorCombo = fieldCombo.before      && Object after fieldCombo
  2225.  
  2226.    do case
  2227.       case empty(valueCombo.value)              && Empty value
  2228.          * (problem with onlostfocus)
  2229.          if operatorCombo.className = "COMBOBOX"   && Comparison operator combobox
  2230.             operatorCombo.value = "IS NULL"
  2231.          else                                      && Assignment operator
  2232.             valueCombo.value = "NULL"
  2233.             operatorCombo.value = "="
  2234.  
  2235.          endif
  2236.  
  2237.       case fieldType $ CHAR_LOGICAL_DATE        && Char/logical/date value
  2238.          valueCombo.value = SurroundByQuotes(valueCombo.value)
  2239.          operatorCombo.value = "="
  2240.  
  2241.       case fieldType = NUMERIC                  && Numeric value
  2242.          valueCombo.value = CheckDecimalPoint(ltrim(valueCombo.value))
  2243.          operatorCombo.value = "="
  2244.  
  2245.       otherwise                                 && Whatever
  2246.          valueCombo.value = "NULL"
  2247.          operatorCombo.value = "="
  2248.  
  2249.    endcase
  2250.  
  2251.  
  2252.  
  2253.  
  2254.  
  2255.    ****************************************************************************
  2256.  
  2257.    Procedure CreateInsertValues
  2258.  
  2259.    ****************************************************************************
  2260.    private top, left, strCnt, fullTableName, fieldName,;
  2261.               valueCombo, fullFieldName, valueCnt, serverName
  2262.  
  2263.    valueCnt = min(form.selectedFieldsAr.size, MAX_INSERT_VALUES)
  2264.    top = INSERT_START_TOP
  2265.    left = INSERT_START_LEFT
  2266.  
  2267.    * Open selected table in temporary area
  2268.    if select("temp") > 0      && if already opened table in "Temp"
  2269.       use in temp
  2270.    endif
  2271.    if form.tablesFromDatabase
  2272.       serverName = form.serverName
  2273.       if setto("database") <> serverName
  2274.          open database &serverName
  2275.          set database to &serverName
  2276.       endif
  2277.       fullTableName = form.tablesEntry.value    && Server table syntax used
  2278.    else
  2279.       fullTableName = StripQuotes(form.tablesEntry.value)
  2280.    endif
  2281.    use (fullTableName) in select() alias temp
  2282.  
  2283.    for i = 1 to valueCnt
  2284.       fieldName = CLASS::GetFieldName(form.selectedFieldsAr[i])
  2285.       fullFieldName = "temp->" + fieldName
  2286.       strCnt = TRIMSTR(i)
  2287.  
  2288.       DEFINE TEXT INSERTVALUETEXT&strCnt OF FORM;
  2289.           PROPERTY;
  2290.             Top top - .7,;
  2291.             Left left,;
  2292.             Height .7,;
  2293.             Width INSERT_VALUE_WIDTH,;
  2294.             Alignment 7,;               && Bottom center
  2295.             FontBold .F.,;
  2296.             FontItalic .T.,;
  2297.             ColorNormal "B/W",;
  2298.             Text lower(fieldName)
  2299.  
  2300.       DEFINE COMBOBOX INSERTVALUECOMBO&strCnt OF FORM;
  2301.           PROPERTY;
  2302.             Before form.lastValueParenText,;    && So values appear in
  2303.             Top top,;                           && correct order when statement
  2304.             Left left,;                         && is created
  2305.             Height INSERT_VALUE_HEIGHT,;
  2306.             Width INSERT_VALUE_WIDTH,;
  2307.             FontBold .F.,;
  2308.             FontName "MS Serif",;
  2309.             FontSize 7,;
  2310.             Style 1,;
  2311.             DataSource "field temp->" + fieldName,;
  2312.             Value &fullFieldName,;
  2313.             OnChange CLASS::InsertUpdateValues
  2314.  
  2315.       if i < valueCnt
  2316.          DEFINE TEXT INSERTVALUECOMMATEXT&strCnt OF FORM;
  2317.              PROPERTY;
  2318.                Before form.lastValueParenText,; && This Before is also necessary
  2319.                Alignment 6,;    && Bottom left  && along with the one above for
  2320.                Top top,;                        && correct statement creation
  2321.                Left left + INSERT_VALUE_WIDTH + .1,;
  2322.                Height INSERT_VALUE_HEIGHT,;
  2323.                Width 1,;
  2324.                Text ",";
  2325.              CUSTOM;
  2326.                Value ","
  2327.       else
  2328.          form.lastValueParenText.top = top
  2329.          form.lastValueParenText.left = left + INSERT_VALUE_WIDTH + .5
  2330.       endif
  2331.  
  2332.       valueCombo = form.insertValueCombo&strCnt
  2333.       valueCombo.OnChange()
  2334.  
  2335.       top = INSERT_START_TOP +;
  2336.                ((INSERT_VALUE_HEIGHT + .7) * floor(i / INSERT_VALUES_PER_ROW))
  2337.       left = INSERT_START_LEFT +;
  2338.                 mod((INSERT_VALUE_WIDTH + 1) * i,;
  2339.                     (INSERT_VALUE_WIDTH + 1) * INSERT_VALUES_PER_ROW)
  2340.    next i
  2341.  
  2342.    form.insertValueCnt = valueCnt
  2343.  
  2344.  
  2345.  
  2346.  
  2347.  
  2348.    ****************************************************************************
  2349.  
  2350.    Procedure InsertUpdateValues
  2351.    ****************************************************************************
  2352.    private fieldName, fieldType, operatorObj
  2353.  
  2354.    fieldName = substr(this.dataSource, at("temp->", this.dataSource))
  2355.    fieldType = type(fieldName)
  2356.  
  2357.    do case
  2358.       case empty(this.value)                    && Empty value
  2359.          this.value = "NULL"
  2360.  
  2361.       case fieldType $ CHAR_LOGICAL_DATE        && Char/logical/date value
  2362.          this.value = SurroundByQuotes(this.value)
  2363.  
  2364.       case fieldType = NUMERIC                  && Numeric value
  2365.          this.value = CheckDecimalPoint(ltrim(this.value))
  2366.  
  2367.       otherwise                                 && Whatever
  2368.          this.value = "NULL"
  2369.    endcase
  2370.  
  2371.  
  2372.    *******************************************************************************
  2373.  
  2374.    function GetTableName(prompt)
  2375.  
  2376.    * Extract table name from prompt in form of 'tableName."fieldName"'
  2377.    *******************************************************************************
  2378.  
  2379.    return substr(prompt, 1, at(".", prompt) - 1)
  2380.  
  2381.  
  2382.    *******************************************************************************
  2383.  
  2384.    function GetFullTableName(name)
  2385.  
  2386.    * Get table name with directory from selectedTablesAr
  2387.    *******************************************************************************
  2388.    private tablesAr, i, lenName, found, aSize
  2389.  
  2390.    tablesAr = form.selectedTablesAr
  2391.    lenName = len(name)
  2392.    found = .F.
  2393.    i = 0
  2394.    aSize = tablesAr.size
  2395.    * Look for entry ending in name
  2396.    do while .not. found .and. i < aSize
  2397.       i = i + 1
  2398.       if right(tablesAr[i], lenName + 1) = name + '"'
  2399.          found = .T.
  2400.       endif
  2401.    enddo
  2402.  
  2403.    * Return value without surrounding quotations
  2404.    return StripQuotes(tablesAr[i])
  2405.  
  2406.  
  2407.    *******************************************************************************
  2408.  
  2409.    function GetFieldName(promptValue)
  2410.  
  2411.    * Extract field name w/out quotes from prompt in form of
  2412.    * 'tableName."fieldName"'
  2413.    *******************************************************************************
  2414.    local fieldStart, fieldName
  2415.  
  2416.    fieldStart = at('"', promptValue)
  2417.    if .not. empty(fieldStart)
  2418.       fieldName = StripQuotes(substr(promptValue, fieldStart))
  2419.    else                                       && No quotes (some server tables)
  2420.       fieldStart = at(".", promptValue) + 1
  2421.       fieldName = substr(promptValue, fieldStart)
  2422.    endif
  2423.  
  2424.    return fieldName
  2425.  
  2426.  
  2427.  
  2428.    *******************************************************************************
  2429.  
  2430.    function GetTableAndField(fieldRef)
  2431.  
  2432.    * Extract field name w/out quotes from prompt in form of
  2433.    * 'tableName."fieldName"'
  2434.    *******************************************************************************
  2435.    local tableStart, tempFieldRef
  2436.  
  2437.    tempFieldRef = StripFieldQuotes(fieldRef)
  2438.    tableStart = at('.', tempFieldRef) + 1
  2439.    return substr(tempFieldRef, tableStart)
  2440.  
  2441.  
  2442.  
  2443.  
  2444.  
  2445.  
  2446.  
  2447.    *******************************************************************************
  2448.  
  2449.    function GetQuotedFieldName(promptValue)
  2450.  
  2451.    * Extract field name w/quotes from prompt in form of 'tableName."fieldName"'
  2452.    *******************************************************************************
  2453.    local fieldStart
  2454.  
  2455.    fieldStart = at('"', promptValue)
  2456.    return substr(promptValue, fieldStart)
  2457.  
  2458.  
  2459.  
  2460.  
  2461.  
  2462.  
  2463.    ****************************************************************************
  2464.  
  2465.    Function GetAllTableFields
  2466.  
  2467.    * Create array of all fields in tables from which fields were selected.
  2468.    ****************************************************************************
  2469.    private allFieldsAr, aliasName, fieldName, selectedTablesAr,;
  2470.               selectedTablesArSize, i, j, serverName, serverType,;
  2471.               separator, fieldFormula, aliasFormula
  2472.  
  2473.    if select("temp") > 0      && if already opened table in "Temp"
  2474.       use in temp
  2475.    endif
  2476.    selectedTablesAr = form.selectedTablesAr
  2477.    selectedTablesArSize = selectedTablesAr.size
  2478.    allFieldsAr = new array(0)
  2479.    separator = "."
  2480.    if form.tablesFromDatabase
  2481.       serverName = form.serverName
  2482.  
  2483.       open database &serverName
  2484.       set database to &serverName
  2485.       serverType = GetDatabaseType(serverName)
  2486.       do case
  2487.          case serverType = "ORACLE"
  2488.             fieldFormula = "SurroundByQuotes(fieldName)"
  2489.             aliasFormula = "  "
  2490.             separator = ""
  2491.          case serverType = "INTRBASE"
  2492.             fieldFormula = "fieldName"
  2493.             aliasFormula = "dbf('temp')"
  2494.          case serverType = "SYBASE"
  2495.             fieldFormula = "substr(fieldName, at('.', fieldName) + 1)"
  2496.             aliasFormula = "substr(dbf('temp'), at('.', dbf('temp')) + 1)"
  2497.          otherwise
  2498.             fieldFormula = "fieldName"
  2499.             aliasFormula = "  "
  2500.       endcase
  2501.    else
  2502.       fieldFormula = "SurroundByQuotes(fieldName)"
  2503.       aliasFormula = "StripExtension(StripDrive(dbf('temp')))"
  2504.    endif
  2505.    for i = 1 to selectedTablesArSize
  2506.       use (StripQuotes(selectedTablesAr[i])) in select() alias temp
  2507.       for j = 1 to fcount("temp")
  2508.          fieldName = field(j, "temp")
  2509.          if .not. type(fieldName) $ MEMO_BINARY_OLE   && Make sure field is
  2510.                                                       && not MEMO, OLE, or BINARY
  2511.             allFieldsAr.Add(&aliasFormula + separator + &fieldFormula)
  2512.          endif
  2513.       next i
  2514.       use in temp
  2515.    next i
  2516.  
  2517.    return allFieldsAr
  2518.  
  2519.  
  2520.    ****************************************************************************
  2521.  
  2522.    procedure ListFieldsInEntryfield(fieldsForm)
  2523.  
  2524.    * List fields in fields entryfield using syntax appropriate for current
  2525.    * database server or current table, if it is local.
  2526.    ****************************************************************************
  2527.    private serverName, serverType, selectedFieldCnt, i, fieldFormula, selectedAr
  2528.  
  2529.  
  2530.    selectedAr = fieldsForm.selectedAr
  2531.    selectedFieldCnt = selectedAr.size
  2532.  
  2533.    if .not. form.tablesFromDatabase        && dBASE/Paradox table
  2534.       fieldFormula = "fieldsForm.selectedAr[i]"
  2535.    else                                    && server table
  2536.       serverName = fieldsForm.serverName
  2537.       serverType = GetDatabaseType(serverName)
  2538.       do case
  2539.          case serverType = "ORACLE"
  2540.             fieldFormula = "form.GetQuotedFieldName(selectedAr[i])"
  2541.          case serverType = "INTRBASE"
  2542.             fieldFormula = "StripFieldQuotes(selectedAr[i])"
  2543.          case serverType = "SYBASE"
  2544.             fieldFormula = "form.GetTableAndField(selectedAr[i])"
  2545.          otherwise
  2546.             fieldFormula = "selectedAr[i]"
  2547.       endcase
  2548.    endif
  2549.  
  2550.    * Create list based on formula assigned above
  2551.    * Each field is modified according to formula, and then added to entryfield
  2552.  
  2553.    form.fieldsEntry.value = ""
  2554.    for i = 1 to selectedFieldCnt
  2555.       form.fieldsEntry.value = form.fieldsEntry.value +;
  2556.                                   &fieldFormula
  2557.       if i < selectedFieldCnt
  2558.          form.fieldsEntry.value = form.fieldsEntry.value + ", "
  2559.       endif
  2560.    next i
  2561.  
  2562.  
  2563.    ****************************************************************************
  2564.  
  2565.    procedure ListTablesInEntryfield(fieldsForm)
  2566.  
  2567.    * List tables in tables entryfield according to current database/table
  2568.    * syntax.  This procedure is called after Fields were selected using the
  2569.    * Fields Tool Button
  2570.    ****************************************************************************
  2571.    private serverName, serverType, selectedTableCnt, i, tableFormula,;
  2572.               tableNamesAr
  2573.  
  2574.    tableNamesAr = fieldsForm.tableNamesAr
  2575.    selectedTableCnt = tableNamesAr.size
  2576.  
  2577.    if .not. form.tablesFromDatabase        && dBASE/Paradox table
  2578.       tableFormula = "fieldsForm.tableNamesAr[i]"
  2579.    else
  2580.       serverName = fieldsForm.serverName
  2581.       serverType = GetDatabaseType(serverName)
  2582.       do case
  2583.          case serverType = "ORACLE"
  2584.             * Strip quotes around whole <alias.table> reference, and surround
  2585.             * only table name with quotes for Oracle
  2586.             tableFormula = "SurroundLastWordByQuotes(StripQuotes(tableNamesAr[i]))"
  2587.          case serverType = "INTRBASE"
  2588.             tableFormula = "StripQuotes(tableNamesAr[i])"
  2589.          case serverType = "SYBASE"
  2590.             tableFormula = "StripQuotes(tableNamesAr[i])"
  2591.          otherwise
  2592.       endcase
  2593.    endif
  2594.  
  2595.    * Create list based on formula assigned above
  2596.    * Each table is modified according to formula, and then added to entryfield
  2597.  
  2598.    form.tablesEntry.value = ""
  2599.    for i = 1 to selectedTableCnt
  2600.       form.tablesEntry.value = form.tablesEntry.value +;
  2601.                                   &tableFormula
  2602.       if i < selectedTableCnt
  2603.          form.tablesEntry.value = form.tablesEntry.value + ", "
  2604.       endif
  2605.    next i
  2606.  
  2607.  
  2608.  
  2609.  
  2610.    ****************************************************************************
  2611.  
  2612.    procedure WriteTableInEntryfield(tablesForm)
  2613.  
  2614.    * List one table in tables entryfield according to current database/table
  2615.    * syntax.  This procedure is called after a single table was selected
  2616.    * using the tables tool button.
  2617.    ****************************************************************************
  2618.    private serverName, serverType, directory, tableFormula, tableName
  2619.  
  2620.    tableName = tablesForm.selectedTable
  2621.    if .not. form.tablesFromDatabase        && dBASE/Paradox table
  2622.       directory = tablesForm.directoryText.text
  2623.       tableFormula = "SurroundByQuotes(tableName)"
  2624.    else
  2625.       serverName = tablesForm.serverName
  2626.       serverType = GetDatabaseType(serverName)
  2627.       do case
  2628.          case serverType = "ORACLE"
  2629.             * Strip quotes around whole <alias.table> reference, and surround
  2630.             * only table name with quotes for Oracle
  2631.             tableFormula = "SurroundLastWordByQuotes(StripQuotes(tableName))"
  2632.          case serverType = "INTRBASE"
  2633.             * No quotes at all for Interbase
  2634.             tableFormula = "StripQuotes(tableName)"
  2635.          case serverType = "SYBASE"
  2636.             tableFormula = "StripQuotes(tableName)"
  2637.          otherwise
  2638.       endcase
  2639.    endif
  2640.  
  2641.    * Write table name based on formula assigned above
  2642.  
  2643.    form.tablesEntry.value = ""
  2644.    form.tablesEntry.value = &tableFormula
  2645.  
  2646.  
  2647.  
  2648.  
  2649.    ****************************************************************************
  2650.  
  2651.    procedure CorrectFieldSyntax(aliasName, serverName)
  2652.  
  2653.    * Modifies field reference in form.selectedFieldsAr, using syntax dependent
  2654.    * on server.
  2655.    ****************************************************************************
  2656.    private serverType, selectedFieldCnt, i, fieldFormula, selectedAr
  2657.  
  2658.  
  2659.    selectedAr = form.selectedFieldsAr
  2660.    selectedFieldCnt = selectedAr.size
  2661.  
  2662.    if .not. form.tablesFromDatabase        && dBASE/Paradox table
  2663.       fieldFormula = "aliasName + '.' + SurroundByQuotes(rtrim(selectedAr[i]))"
  2664.    else                                    && server table
  2665.       serverType = GetDatabaseType(serverName)
  2666.       do case
  2667.          case serverType = "ORACLE"
  2668.             fieldFormula = "SurroundByQuotes(rtrim(selectedAr[i]))"
  2669.          case serverType = "INTRBASE"
  2670.             fieldFormula = "rtrim(selectedAr[i])"
  2671.          case serverType = "SYBASE"
  2672.             fieldFormula = "form.GetFieldName(rtrim(selectedAr[i]))"
  2673.          otherwise
  2674.       endcase
  2675.    endif
  2676.  
  2677.    * Modify form.selectedFieldsAr array based on formula assigned above
  2678.    for i = 1 to selectedFieldCnt
  2679.       selectedAr[i] = &fieldFormula
  2680.    next i
  2681.  
  2682.  
  2683.    ***************************** Page 2 Functions *****************************
  2684.  
  2685.  
  2686.  
  2687.  
  2688.    ****************************************************************************
  2689.  
  2690.    Procedure Page2OkButton1_OnClick
  2691.    ****************************************************************************
  2692.    private bCloseForm
  2693.  
  2694.    bCloseForm = .T.
  2695.    if CLASS::MakeTemplateSQLStatement()
  2696.       ?form.sqlStatement
  2697.    else
  2698.       if ConfirmationMessage("SQL statement not complete.  Do you still " +;
  2699.                                 "want to close the form?",;
  2700.                              "Confirm") = NO
  2701.          bCloseForm = .F.
  2702.       endif
  2703.    endif
  2704.  
  2705.    if bCloseForm
  2706.        form.Close()
  2707.    endif
  2708.  
  2709.  
  2710.    ****************************************************************************
  2711.  
  2712.    Function MakeTemplateSQLStatement
  2713.  
  2714.    * Make SQL statement from the selected template
  2715.    ****************************************************************************
  2716.    private templateNum, sqlStatement, i, field, fieldList, fieldCount,;
  2717.               samplesDir, bStatementComplete, aliasName, selectionMade
  2718.  
  2719.    bStatementComplete = .T.
  2720.    templateNum = form.templateNum
  2721.    sqlStatement = ""
  2722.  
  2723.    do case
  2724.       case templateNum = 1      && Show all fields of selected table
  2725.          if empty(form.template1Entry.value)
  2726.             bStatementComplete = .F.
  2727.          else
  2728.             sqlStatement = "SELECT * FROM " +;
  2729.                 SurroundByQuotes(form.template1Entry.value)
  2730.          endif
  2731.       case templateNum = 2      && Show selected fields from a selected table
  2732.          if empty(form.template2Entry.value)
  2733.             bStatementComplete = .F.
  2734.          else
  2735.             sqlStatement = "SELECT "
  2736.             fieldList = form.template2List
  2737.             fieldCount = fieldList.Count()
  2738.             aliasName = GetAliasName(form.template2Entry.value)
  2739.             selectionMade = .F.
  2740.             for i = 1 to fieldCount
  2741.                field = fieldList.Selected(i)
  2742.                if .not. empty(field)
  2743.                   selectionMade = .T.
  2744.                   && Put all fields in quotations so no keyword conflict
  2745.                   sqlStatement = sqlStatement + aliasName + "." +;
  2746.                                    SurroundByQuotes(field) + ", "
  2747.                endif
  2748.             next i
  2749.             if selectionMade
  2750.                && lose the last comma
  2751.                sqlStatement = left(sqlStatement, len(sqlStatement)- 2) + " "
  2752.             else
  2753.                sqlStatement = "SELECT * "
  2754.             endif
  2755.             sqlStatement = sqlStatement + "FROM " + ;
  2756.                SurroundByQuotes(form.template2Entry.value)
  2757.          endif
  2758.       case templateNum = 3      && Show all customers with orders over N
  2759.          * Check if Samples directory exists
  2760.          samplesDir = _dbwinhome + "SAMPLES"
  2761.          if .not. file(samplesDir + "\nul")
  2762.             InformationMessage(samplesDir + " directory does not exist.",;
  2763.                                "Sorry")
  2764.          else
  2765.             sqlStatement = 'SELECT CUSTOMER."NAME", ORDERS."TOTAL" FROM ' +;
  2766.                '"' + samplesDir + '\CUSTOMER' + '", ' +;
  2767.                '"' + samplesDir + '\ORDERS' +'" WHERE ' +;
  2768.                'CUSTOMER."CUSTOMER_N" = ORDERS."CUSTOMER_N" ' +;
  2769.                'AND ORDERS."TOTAL" > ' +;
  2770.                CheckDecimalPoint(str(form.template3Spin.value, 10, 3))
  2771.          endif
  2772.    endcase
  2773.  
  2774.    if .not. bStatementComplete
  2775.       InformationMessage("Statement is not complete.  A table must be opened.",;
  2776.                          "Sorry")
  2777.    else
  2778.       form.sqlStatement = sqlStatement
  2779.    endif
  2780.  
  2781.    return bStatementComplete
  2782.  
  2783.  
  2784.    ****************************************************************************
  2785.  
  2786.    Procedure TemplateClearSQLButton_OnClick
  2787.  
  2788.    * Clears out SQL statement built by selected template
  2789.    ****************************************************************************
  2790.  
  2791.    CLASS::ClearTemplate()
  2792.    form.sqlStatement = ""
  2793.  
  2794.  
  2795.    ****************************************************************************
  2796.  
  2797.    Procedure ClearTemplate
  2798.  
  2799.    * Clears values entered for the currently selected template
  2800.    ****************************************************************************
  2801.    private templateNum
  2802.  
  2803.    templateNum = form.templateNum
  2804.  
  2805.    do case
  2806.       case templateNum = 1      && Show all fields of selected table
  2807.          form.template1Entry.value = ""
  2808.       case templateNum = 2      && Show selected fields from a selected table
  2809.          form.template2List.dataSource = ""
  2810.          form.template2Entry.value = ""
  2811.       case templateNum = 3      && Show all customers with orders over N
  2812.          form.template3Spin.value = 500.00
  2813.    endcase
  2814.  
  2815.  
  2816.  
  2817.    ****************************************************************************
  2818.  
  2819.    Procedure TemplateRunSQLButton_OnClick
  2820.  
  2821.    * Run SQL statement built by selected template
  2822.    ****************************************************************************
  2823.    private sqlStatement, saveArea, tableName
  2824.  
  2825.    if CLASS::MakeTemplateSQLStatement()
  2826.       sqlStatement = form.sqlStatement
  2827.       &sqlStatement
  2828.       if reccount() > 0
  2829.          browse
  2830.       else
  2831.          InformationMessage("There are no records in this answer table.",;
  2832.                             "Info")
  2833.       endif
  2834.    endif
  2835.  
  2836.  
  2837.  
  2838.    ****************************************************************************
  2839.  
  2840.    Procedure DisableTemplateControls
  2841.  
  2842.    * Disable all controls tied to templates -- this is done before the specific
  2843.    * controls for the current template are enabled
  2844.    ****************************************************************************
  2845.  
  2846.    CLASS::ClearTemplate()
  2847.    form.template1ToolButton.enabled = .F.
  2848.    form.template2List.enabled = .F.
  2849.    form.template2ToolButton.enabled = .F.
  2850.    form.template3Spin.enabled = .F.
  2851.  
  2852.  
  2853.    ****************************************************************************
  2854.  
  2855.    Procedure TEMPLATE1RADIO_OnGotFocus
  2856.    ****************************************************************************
  2857.  
  2858.    CLASS::DisableTemplateControls()
  2859.    form.template2Radio.value = .F.
  2860.    form.template3Radio.value = .F.
  2861.    form.template1ToolButton.enabled = .T.
  2862.    form.templateNum = 1
  2863.  
  2864.  
  2865.    ****************************************************************************
  2866.  
  2867.    Procedure TEMPLATE2RADIO_OnGotFocus
  2868.    ****************************************************************************
  2869.  
  2870.    CLASS::DisableTemplateControls()
  2871.    form.template1Radio.value = .F.
  2872.    form.template3Radio.value = .F.
  2873.    form.template2list.enabled = .T.
  2874.    form.template2ToolButton.enabled = .T.
  2875.    form.templateNum = 2
  2876.  
  2877.  
  2878.    ****************************************************************************
  2879.  
  2880.    Procedure TEMPLATE3RADIO_OnGotFocus
  2881.    ****************************************************************************
  2882.  
  2883.    CLASS::DisableTemplateControls()
  2884.    form.template1Radio.value = .F.
  2885.    form.template2Radio.value = .F.
  2886.    form.template3spin.enabled = .T.
  2887.    form.templateNum = 3
  2888.  
  2889.  
  2890.    ****************************************************************************
  2891.  
  2892.    Procedure TEMPLATE1TOOLBUTTON_OnClick
  2893.    ****************************************************************************
  2894.    private fileName
  2895.  
  2896.    fileName = getFile("*.dbf", "Select a table")
  2897.    if .not. empty(fileName)
  2898.       form.template1Entry.value = fileName
  2899.    endif
  2900.  
  2901.  
  2902.    ****************************************************************************
  2903.  
  2904.    Procedure TEMPLATE2TOOLBUTTON_OnClick
  2905.    ****************************************************************************
  2906.    private fileName, saveArea, i, fCount
  2907.  
  2908.    fileName = getFile("*.dbf", "Select a table")
  2909.    if .not. empty(fileName)
  2910.       form.template2Entry.value = fileName
  2911.       saveArea = str(workarea())                && Save current workarea
  2912.       select select()
  2913.       use (fileName)                            && Open selected table
  2914.       fCount = fcount()
  2915.       form.template2List.structureAr = new array(fCount)
  2916.       for i = 1 to fcount                       && Save structure to array
  2917.          form.template2List.structureAr[i] = field(i)
  2918.       next i                                    && Set list datasource to array
  2919.       form.template2List.dataSource = "array form.template2List.structureAr"
  2920.       use                                       && Close table
  2921.       select &saveArea                          && Select original workarea
  2922.    endif
  2923.  
  2924.  
  2925.  
  2926.    ***************************** Page 3 Functions *****************************
  2927.  
  2928.  
  2929.  
  2930.    ****************************************************************************
  2931.  
  2932.    Procedure EXECUTEBUTTON_OnClick_SQL
  2933.    ****************************************************************************
  2934.    private mLines, sqlCmdArray
  2935.  
  2936.    form.RestoreEnvironment()
  2937.    mLines = memlines(sqldbase->sql_cmd)
  2938.    select select()
  2939.    sqlCmdArray = new array(mlines)
  2940.    store memo sqldbase->sql_cmd to array sqlCmdArray
  2941.    form.ExecuteCommandsFromArray(sqlCmdArray)
  2942.    form.answerRect.text = "Answer from SQL Query:  "
  2943.    form.answerRect.colorNormal = "b+/BtnFace"
  2944.    form.answerBrowse.alias = alias()  && Show query result in browse object
  2945.    form.answerBrowse.visible = .t.    && Browse is invisible until a query is run
  2946.  
  2947.    * For cleanup
  2948.    form.sqlCleanupCmdArray = new array(memlines(sqldbase->sql_cln)) && Store cleanup
  2949.    store memo sqldbase->sql_cln to array form.sqlCleanupCmdArray    && commands
  2950.  
  2951.    form.lastQueryType = SQL_QUERY
  2952.  
  2953.  
  2954.    ****************************************************************************
  2955.  
  2956.    Procedure EXECUTEBUTTON_OnClick_dBASE
  2957.    ****************************************************************************
  2958.    private mLines, dbCmdArray
  2959.  
  2960.    form.RestoreEnvironment()          && Start with a clean environment
  2961.    mLines = memlines(sqldbase->dbase_cmd)
  2962.    select select()
  2963.    dbCmdArray = new array(mlines)
  2964.    store memo sqldbase->dbase_cmd to array dbCmdArray  && Store dbase commands to array
  2965.    form.ExecuteCommandsFromArray(dbCmdArray)
  2966.    * don't want to lose fields in sqldbase table
  2967.    form.answerRect.text = "Answer from dBASE Query:  "
  2968.    form.answerRect.colornormal = "b+/BtnFace"
  2969.    form.answerBrowse.visible = .f.    && So don't see so much flashing
  2970.    form.answerBrowse.alias = alias()  && Show query result in browse object
  2971.    form.answerBrowse.fields = setto("fields")
  2972.    if .not. empty(form.answerBrowse.fields)
  2973.       allFields = form.answerBrowse.fields + ;
  2974.          ",sqldbase->descript, sqldbase->sql_cmd, sqldbase->dbase_cmd, sqldbase->dbase_cln"
  2975.       set fields to &allFields
  2976.    endif
  2977.    form.answerBrowse.visible = .t.
  2978.  
  2979.    * For cleanup
  2980.    form.dbaseCleanupCmdArray = new array(memlines(sqldbase->dbase_cln)) && Store cleanup
  2981.    store memo sqldbase->dbase_cln to array form.dbaseCleanupCmdArray    && commands
  2982.  
  2983.    form.lastQueryType = DBASE_QUERY
  2984.  
  2985.  
  2986.  
  2987.    ****************************************************************************
  2988.  
  2989.    Procedure SQLDBASETABBOX_OnSelChange
  2990.    ****************************************************************************
  2991.  
  2992.    do case
  2993.       case this.curSel = SQL_QUERY
  2994.          form.commandEditor.datalink = "sqldbase->sql_cmd"
  2995.          form.executeButton.OnClick = CLASS::EXECUTEBUTTON_ONCLICK_SQL
  2996.       case this.curSel = DBASE_QUERY
  2997.          form.commandEditor.datalink = "sqldbase->dbase_cmd"
  2998.          form.executeButton.OnClick = CLASS::EXECUTEBUTTON_ONCLICK_DBASE
  2999.       otherwise
  3000.          * No other tabs for now
  3001.    endcase
  3002.  
  3003.  
  3004.    ************************** Support Procedures ******************************
  3005.  
  3006.  
  3007.    ****************************************************************************
  3008.  
  3009.    Procedure DESCRIPTCOMBO_OnChange
  3010.    ****************************************************************************
  3011.    *** Changing combo values doesn't move record pointer
  3012.    private curAlias, thisValue
  3013.  
  3014.    curAlias = alias()
  3015.    select sqldbase
  3016.    thisValue = this.value
  3017.    locate for sqldbase->descript = thisValue
  3018.    go recno("sqldbase")
  3019.    select &curAlias
  3020.  
  3021.    form.answerBrowse.visible = .f.
  3022.    form.answerRect.text = "Answer:  "
  3023.    form.answerRect.colorNormal = "n+/BtnFace"
  3024.  
  3025.    ****************************************************************************
  3026.  
  3027.    Procedure RestoreEnvironment
  3028.    ****************************************************************************
  3029.    private saveFields, saveArea, command, i, arSize
  3030.  
  3031.    do case
  3032.       case form.lastQueryType = SQL_QUERY
  3033.          * currently located in query area
  3034.          use
  3035.          form.ExecuteCommandsFromArray(form.sqlCleanupCmdArray)
  3036.       case form.lastQueryType = DBASE_QUERY
  3037.          form.ExecuteCommandsFromArray(form.dbaseCleanupCmdArray)
  3038.       otherwise
  3039.          * No query was performed.  Nothing to cleanup
  3040.    endcase
  3041.  
  3042.    form.answerBrowse.fields = ""
  3043.    saveFields = form.saveFields
  3044.    saveArea = form.saveArea
  3045.    set fields to &saveFields
  3046.    select &saveArea
  3047.  
  3048.  
  3049.    ****************************************************************************
  3050.  
  3051.    procedure ExecuteCommandsFromArray(cmdArray)
  3052.    ****************************************************************************
  3053.    private command, i, cmdArraySize
  3054.  
  3055.    cmdArraySize = cmdArray.size
  3056.    for i = 1 to cmdArraySize
  3057.       command = cmdArray[i]
  3058.       do while right(command, 1) = ";"
  3059.          command = substr(command, 1, len(command) - 1)
  3060.          i = i + 1
  3061.          command = command + cmdArray[i]
  3062.       enddo
  3063.       &command
  3064.    next i
  3065.  
  3066. ENDCLASS
  3067.  
  3068.  
  3069. ********************************** Utility Functions **************************
  3070.  
  3071. *******************************************************************************
  3072.  
  3073. function StripExtension(fileName)
  3074. *******************************************************************************
  3075. private strippedName, periodLoc
  3076.  
  3077. periodLoc = rat(".", fileName)
  3078. if periodLoc > 0                && fileName has an extension
  3079.    strippedName = left(fileName, periodLoc - 1)
  3080. else
  3081.    strippedName = fileName
  3082. endif
  3083.  
  3084. return strippedName
  3085.  
  3086.  
  3087.  
  3088. *******************************************************************************
  3089. function StripAllQuotes(value)
  3090.  
  3091. * Strip quotes around whole value, or around last part of value
  3092. *******************************************************************************
  3093. private returnValue
  3094.  
  3095. do case
  3096.    case left(value, 1) = '"'            && Quotes around whole value
  3097.       returnValue = StripQuotes(value)
  3098.    case right(value, 1) = '"'           && Quotes around last part of value
  3099.       returnValue = StripFieldQuotes(value)
  3100.    otherwise
  3101.       returnValue = value               && No quotes -- return as is
  3102. endcase
  3103.  
  3104. return returnValue
  3105.  
  3106.  
  3107.  
  3108.  
  3109.  
  3110. *******************************************************************************
  3111. function StripQuotes(value)
  3112.  
  3113. * Extract field name from prompt in form of 'tableName."fieldName"'
  3114. *******************************************************************************
  3115. local firstQuote, lastQuote, start, length, lenValue
  3116.  
  3117. firstQuote = at('"', value)
  3118. lastQuote = rat('"', value)
  3119. lenValue = len(value)
  3120. do case
  3121.    case (firstQuote = lastQuote) .and. firstQuote = 0
  3122.       start = 1
  3123.       length = lenValue
  3124.    case (firstQuote = lastQuote) .and. firstQuote = 1
  3125.       start = firstQuote + 1
  3126.       length = lenValue - 1
  3127.    case (firstQuote = lastQuote) .and. firstQuote = lenValue
  3128.       start = 1
  3129.       length = lastQuote - 1
  3130.    otherwise
  3131.       start = firstQuote + 1
  3132.       length = lastQuote - 2
  3133. endcase
  3134.  
  3135. return substr(value, start, length)
  3136.  
  3137.  
  3138.  
  3139. ****************************************************************************
  3140. Procedure SurroundByQuotes(value)
  3141. ****************************************************************************
  3142. private first, last, returnValue
  3143.  
  3144. first = left(value, 1)
  3145. last = right(value, 1)
  3146.  
  3147. do case
  3148.    case first = '"' .and. last = '"'         && Already surrounded by '
  3149.       returnValue = value
  3150.  
  3151.    case first = '"' .and. last = '"'         && Already surounded by "
  3152.       returnValue = value
  3153.  
  3154.    case first $ ["']                         && Only starts with a quote
  3155.       returnValue = value + first
  3156.  
  3157.    case last $ ["']                          && Only ends with a quote
  3158.       returnValue = first + value
  3159.  
  3160.    otherwise                                 && No quotes at all
  3161.       returnValue = '"' + value + '"'
  3162. endcase
  3163.  
  3164. return returnValue
  3165.  
  3166.  
  3167. ****************************************************************************
  3168. Procedure SurroundBySingleQuotes(value)
  3169. ****************************************************************************
  3170. private first, last, returnValue
  3171.  
  3172. first = left(value, 1)
  3173. last = right(value, 1)
  3174.  
  3175. do case
  3176.    case first = "'" .and. last = "'"         && Already surrounded by '
  3177.       returnValue = value
  3178.  
  3179.    case first = "'" .and. last = "'"         && Already surounded by "
  3180.       returnValue = value
  3181.  
  3182.    case first $ ["']                         && Only starts with a quote
  3183.       returnValue = value + first
  3184.  
  3185.    case last $ ["']                          && Only ends with a quote
  3186.       returnValue = first + value
  3187.  
  3188.    otherwise                                 && No quotes at all
  3189.       returnValue = "'" + value + "'"
  3190. endcase
  3191.  
  3192. return returnValue
  3193.  
  3194.  
  3195.  
  3196. ****************************************************************************
  3197. Procedure SurroundLastWordByQuotes(value)
  3198. ****************************************************************************
  3199. private afterPeriod
  3200.  
  3201. afterPeriod = rat(".", value) + 1
  3202. return stuff(value, afterPeriod, 0, '"') + '"'
  3203.  
  3204.  
  3205.  
  3206. ****************************************************************************
  3207. Procedure StripFieldQuotes(fieldRef)
  3208. ****************************************************************************
  3209. private firstQuoteLoc, fieldRefLen, tempNewRef
  3210.  
  3211. firstQuoteLoc = at('"', fieldRef)
  3212. fieldRefLen = len(fieldRef)
  3213. tempNewRef = left(fieldRef, fieldRefLen - 1)
  3214. return stuff(tempNewRef, firstQuoteLoc, 1, "")
  3215.  
  3216.  
  3217. ****************************************************************************
  3218.  
  3219. Procedure StripDrive(directory)
  3220. ****************************************************************************
  3221.  
  3222. return substr(directory, at(":", directory) + 1)
  3223.  
  3224.  
  3225.  
  3226. ****************************************************************************
  3227.  
  3228. Procedure GetAliasName(fullTableName)
  3229. ****************************************************************************
  3230. private tableName
  3231.  
  3232. tableName = substr(fullTableName, rat("\", fullTableName) + 1)
  3233.  
  3234. return StripExtension(tableName)
  3235.  
  3236.  
  3237.  
  3238. ****************************************************************************
  3239. Function CheckDecimalPoint(numberStr)
  3240. ****************************************************************************
  3241. private decPoint, decPointLoc, returnStr
  3242.  
  3243. decPoint = setto("point")
  3244. decPointLoc = at(decPoint, numberStr)
  3245.  
  3246. * Replace decimalPoint with "." if it exists and is different
  3247. if decPointLoc > 0 .and. decPoint <> "."
  3248.    returnStr = stuff(numberStr, decPointLoc, 1, ".")
  3249. else
  3250.    returnStr = numberStr
  3251. endif
  3252.  
  3253. return returnStr
  3254.  
  3255.  
  3256.  
  3257.  
  3258.  
  3259. ****************************************************************************
  3260. function GetDatabaseType(databaseName)
  3261.  
  3262. * Retrieve database type by using dbiGetDatabaseDesc IDAPI function.
  3263. ****************************************************************************
  3264. private iResult, databaseInfo
  3265.  
  3266. * Function for retrieving database information
  3267. extern CINT dbiGetDatabaseDesc(CSTRING, CPTR) Idapi01.dll
  3268.  
  3269. databaseInfo = space(DATABASE_INFO_LEN)      && Buffer for database info
  3270. iResult = dbiGetDatabaseDesc(databaseName, databaseInfo)
  3271.  
  3272. return rtrim(substr(databaseInfo, DATABASE_TYPE_LOC))
  3273.  
  3274.  
  3275.  
  3276.  
  3277. ****************************************************************************
  3278. function ServerTableSyntax(tableName, databaseName)
  3279.  
  3280. * Retrieve database type by using dbiGetDatabaseDesc IDAPI function.
  3281. ****************************************************************************
  3282. private iResult, databaseInfo
  3283.  
  3284. * Function for retrieving database information
  3285. extern CINT dbiGetDatabaseDesc(CSTRING, CPTR) Idapi01.dll
  3286.  
  3287. databaseInfo = space(DATABASE_INFO_LEN)      && Buffer for database info
  3288. iResult = dbiGetDatabaseDesc(databaseName, databaseInfo)
  3289.  
  3290. return rtrim(substr(databaseInfo, DATABASE_TYPE_LOC))
  3291.  
  3292.  
  3293. ****************************************************************************
  3294.  
  3295. Procedure HandleDatabaseError
  3296. ****************************************************************************
  3297. do case
  3298.    case error() = ERROR_NO_SUCH_DATABASE
  3299.       InformationMessage("Sorry, Database doesn't exist.", "Oops")
  3300.  
  3301.    otherwise
  3302.       InformationMessage("Sorry, couldn't connect to database", "Oops")
  3303.  
  3304. endcase
  3305.  
  3306.  
  3307.  
  3308.  
  3309.  
  3310.  
  3311.  
  3312.  
  3313.  
  3314.  
  3315.  
  3316.  
  3317.  
  3318.  
  3319.  
  3320.  
  3321.  
  3322.  
  3323.  
  3324.  
  3325.  
  3326.  
  3327.  
  3328.  
  3329.  
  3330.  
  3331.