!------------------------------------------------------------------------------!
! GUI for JDBC Interface
!
!   Author : J. Bauer
!
!------------------------------------------------------------------------------!
startmacro
  set dbProps {}

  ! Default to not show the options until connection is made
  set resLabelState "HIDDEN"
  set actLabelState "HIDDEN"
  set queryLabelState "HIDDEN"
  set exeLabelState "HIDDEN"
  set descLabelState "HIDDEN"

  pipe on

    ! create the instance of nmdb without connecting
    nmdb/id=myDB PROPS=dbProps RESNAME=myResults /CONNECT=false

    panel/setup/controls=gc
    list2/msgid=MAIN/id=idlist/legacy TEMPATE={_GLOBAL_={SORTENABLED=F}}

    ! The EXIT button
    gcontrol label   sLabel   "SYSTEM" /msg=y
    gcontrol button  Exit     ""       "Exit"

    gcontrol       label   dbLabel    "Select DATABASE TYPE" "OPEN"  /msg=y
    gcontrol/tleft BUTTON  dbType "" "DERBY,HSQLDB,MYSQL,ORACLE,OTHER" "HSQLDB"

    !      The Database Connection Setup prompts
    !      These are filled in with some default Oracle information.
    !      Note that the Password has no default.
    gcontrol label   propsLabel    "SETUP CONNECTION" "HIDDEN"  /msg=y
    gcontrol prompt  pDbName       "DB Name"           myDBName
    gcontrol prompt  pHost         "Server"            myHost
    gcontrol prompt  pPort         "Port"             ^myPort
    gcontrol prompt  pUserName     "User Name"         myUserName
    gcontrol prompt  pPassWord     "Password"          myUserPassWord
    gcontrol prompt  pDriverString "Driver String"     myDriverString
    gcontrol prompt  pConnString   "Connection String" myConnString

    ! Connect to database
    gcontrol button bConnect "" "CONNECT"

    ! The RESULTS group. User chooses type of results (CSV, Table, XML)
    ! and whether or not to display them in the text box.
    gcontrol label   resLabel   "RESULTS" p1=^resLabelState  /msg=y
    gcontrol/nc=4/tleft  BUTTON resultsType  "Results Type:" "NONE,csv,tbl,xml" "csv"
    gcontrol prompt pFileName "Filename (NO EXTENSION)" "tmpFile"
    gcontrol/tleft  BUTTON display "Display Results? " "YES,NO" "YES"

    ! The ACTION group. User chooses the type of action:
    !   QUERY:     'SELECT' statements (Generate Results)
    !   COMMAND:   'INSERT', 'UPDATE', 'TRUNCATE', etc statements. (Generates
    !                  affirmation messages, such as rows updated)
    !   DESCRIBE:  'DESCRIBE' statements (Generates table description)
    gcontrol label   actLabel   "ACTION" p1="^actLabelState"  /msg=y
    gcontrol/tleft BUTTON action "" "NONE,QUERY,COMMAND,DESCRIBE" "NONE"

    ! The QUERY group. User inputs SELECT statements in the prompt.
    ! The query executes when the user hits ENTER
    gcontrol label  queryLabel   "QUERY" p1="^queryLabelState"  /msg=y
    gcontrol prompt pQuery "Query:" ""

    ! The COMMAND group. User inputs COMMAND statements in the prompt.
    ! The query executes when the user hits ENTER
    gcontrol label  exeLabel   "COMMAND" p1=^exeLabelState  /msg=y
    gcontrol prompt pExecute "Command:" ""

    ! The DESCRIBE group. User inputs table name in the prompt.
    ! The query executes when the user hits ENTER
    gcontrol label  descLabel   "DESCRIBE" p1=^descLabelState  /msg=y
    gcontrol prompt pDescribe "Table Name:" ""

    ! The Text Area
    textdisplay/id=td/scroll=V/exit=none/title="Feedback" text="Feedback will display here."

  pipe off

endmacro

! Procedure for handling all messages
procedure processMessage m:msg
  ! say "Got msg name=^msg.name info=^msg.info data=^msg.data"

  ! Exit if EXIT button clicked
  if msg.name eqs "EXIT" then
    pipe stop

  ! Database Type Button clicked
  elseif msg.name eqss "DBTYPE" then
    call setDBType msg.data

  ! Connect Button clicked
  elseif msg.data eqs "CONNECT" then
    call connect

  ! Action Button clicked
  elseif msg.name eqs "ACTION" then
    call action msg.data

  ! Sends the Query / Command / Describe to the nmdb instance and calls the results
  elseif msg.name eqs "PQUERY" then
    message func=sendw id=myDB name=select data="^{gc.pQuery.value}"
    call getResults
  elseif msg.name eqs "PEXECUTE" then
    message func=sendw id=myDB name=insert data="^{gc.pExecute.value}"
    message func=sendw id=TD ,,REPLACE,,"^myResults Rows Updated."
  elseif msg.name eqs "PDESCRIBE" then
    message func=sendw id=myDB name=describe data="^{gc.pDescribe.value}"
    message func=sendw id=TD ,,REPLACE,,myResults
  endif

return

  ! Once the user selects a database type, the connection properties are
  ! displayed, with some information filled in (such as the driver).
  ! -- NOTE -- For testing purposes, I have filled in default info for each DB
procedure setDBType S:DBType

   if DBType eqss "HSQLDB" then
      set myDBName       "hsqldbTestDB"
      set myHost         "localhost"
      set myPort         ""
      set myUserName     "sa"
      set myUserPassWord ""
      set myDriverString "org.hsqldb.jdbcDriver"
      set myConnString   "jdbc:hsqldb:file:^myDBName"


   elseif DBType eqss "ORACLE" then
      set myDBName       "XE"
      set myHost         "10.10.1.56"
      set myPort         1521
      set myUserName     "nxm"
      set myUserPassWord ""
      set myDriverString "oracle.jdbc.driver.OracleDriver"
      set myConnString   "jdbc:oracle:thin:@^myHost:^myPort:^myDBName"

    elseif DBType eqss "DERBY" then
      set myDBName        "DerbyTestDB"
      set myHost          "localhost"
      set myPort          1527
      set myUserName      ""
      set myUserPassWord  ""
      set myDriverString  "org.apache.derby.jdbc.EmbeddedDriver"
      set myConnString    "jdbc:derby:^myDBName;create=true"

    elseif DBType eqss "MYSQL" then
      set S:myDBName      "test"
      set S:myHost        "10.10.1.59"
      set myPort          3306
      set myUserName      "nxm"
      set myUserPassWord  ""
      set myDriverString  "com.mysql.jdbc.Driver"
      set myConnString    "jdbc:mysql://^myHost:^myPort/^myDBname"
    endif

   call setConnValues
return

  !Set the values in the prompts for the Connection Info
procedure setConnValues
      set gc.pHost.value         "^myHost"
      set gc.pDBName.value       "^myDBName"
      set gc.pConnString.value   "^myConnString"
      set gc.pDriverString.value "^myDriverString"
      set gc.pUserName.value     "^myUserName"
      set gc.pPassWord.value     "^myUserPassWord"
      set gc.pPort.value         "^myPort"

      set gc.propsLabel.show 1
      set gc.propsLabel.value "OPEN"
return

  ! If the user clicks the CONNECT button, the properties fields are read and
  ! the CONNECT message is sent to the nmdb instance. If connection is made,
  ! the Text Area will display 'Connected', the properties group will close,
  ! and the option groups for querying through that connection will display.
  ! If connection fails, the Text Area will display 'Not Connected' and the
  ! properties group will remain displayed.
procedure connect

  set S:myState "FAILED TO CONNECT to ^{gc.pDBName.value}. Check Settings and try again."
    set dbProps.DBHOST     gc.pHost.value
    set dbProps.DBNAME     gc.pDBName.value
    set dbProps.DBCONNECT  gc.pConnString.value
    set dbProps.DBDRIVER   gc.pDriverString.value
    set dbProps.DBACCOUNT  gc.pUserName.value
    set dbProps.DBPASSWORD gc.pPassWord.value
    set dbProps.DBNODE     "null"
    set dbProps.DBPORT     gc.pPort.value

    message func=sendw id=myDB name=CONNECT

    if myResults eq 1 then
      set myState "Connected to database ^{gc.pDBName.value}!"
      set gc.actLabel.show 1
      set gc.actLabel.value "OPEN"
      set gc.resLabel.show 1
      set gc.resLabel.value "OPEN"
      set gc.propsLabel.value "CLOSED"
    endif

    message func=sendw id=TD ,,REPLACE,, "^{myState}"

return

  ! If the user clicks a button in the Action group, the appropriate
  ! action prompt group will be displayed and opened.
procedure action S:myAction
    if myAction eqs "COMMAND" then
      set gc.exeLabel.show 1
      set gc.exeLabel.value "OPEN"
    elseif myAction eqs "QUERY" then
      set gc.queryLabel.show 1
      set gc.queryLabel.value "OPEN"
    elseif myAction eqs "DESCRIBE" then
      set gc.descLabel.show 1
      set gc.descLabel.value "OPEN"
    endif
return

! Get the appropriate type of results as selected in the Gui
! and write the results to a file, if so selected in the Gui.
procedure getResults
  ! Clear the Text Display
  message func=sendw id=TD ,,CLEAR,,

  ! create the file which will be displayed in the list
  if gc.resultsType.value neqs "NONE" then
    set S:myFileName "^{gc.pFileName.value}.^{gc.resultsType.value}{AUX=RAM, HEADERROWS=1}"
    file open/t/n outFile p1=^{myFileName}
  else
    message func=sendw id=TD ,,REPLACE,, "Oops! Results called without type set!"
  endif

  ! XML results are written as an XML file. For clear display and on-screen
  !  interaction in the LIST2, user should select CSV as the result type.
  if gc.resultsType.value eqs "xml" then
    message func=sendw id=TD ,,REPLACE,, "For better displayed results, run query again as CSV"
    message func=sendw id=myDB name=getxml
      file write outFile ^myResults

  ! For CSV files, first get the column names to make a header row, then the data
  elseif gc.resultsType.value eqs "csv" then
    message func=sendw id=TD ,,REPLACE,, "For queries that return unusual &
      Column Names such as 'select count(*)...' you may see a NULL pointer &
      exception in your terminal window. This can be resolved by naming the &
      column, such as in 'select count(*) as COUNT from dual'."
    message func=sendw id=myDB name=getcol
      file write outFile ^myResults
    message func=sendw id=myDB name=getcsv
      file write outFile ^myResults

  ! For TABLE results, the TABLE is written to a *.tbl file.
  ! For Display purposes, the data is properly displayed as a table is the
  !  text display. The List2 shows the .tbl file contents. It is expected that
  !  users will manipulate the data in the TABLE and the display is for
  !  convenience purposes only. For interactive LIST2 display, use CSV output.
  elseif gc.resultsType.value eqs "tbl" then
    message func=sendw id=myDB name=gettable
    TABLE myTable CREATE myResults
    file write outFile "^{myTable}"
    ! Format the tbl results for display and send to the Text Display
    if gc.display.value eqs "YES" then
      set L:y 0         ! Used for adding the newline char at the end of a row
      foreach row intable/v myResults
        set L:x 0       ! Used for adding the comma between elements
        if y > 0 then
          message func=send id=TD ,,APPENDL,, ""
        endif
        calc y y 1 +
        foreach item intable/v row
          calc x x 1 +
           if x > 1 then
            message func=send id=TD ,,APPEND,, ", "
          endif
          message func=send id=TD ,,APPEND,, "^{item}"
        endfor
      endfor
    endif
  endif

  ! Close the file
  if gc.resultsType.value neqs "NONE" then
    file close outfile
  endif

  ! Display the file to the LIST2
  if gc.display.value eqs "YES" then
    set reg.idlist.openFile "^myFileName"
    set reg.idlist.setTemplate {}        ! reset template
  endif

return
