GAMBAS-BOOK 3.19.5

This project also addresses the issues of 'process control' and 'process data' in the context of using the SHELL and EXEC instructions. The basis for a GUI for the interactive console client 'mysql' is presented.

Figure 21.3.5.1: Basic GUI for the console program 'mysql'.

You can extend the project in many ways to create your GUI for the console client 'mysql', based on the tasks to be processed as well as your own ideas. The following extensions would be conceivable:

• Login form (DB server, DB user, DB user password).
• Selection of all relevant databases and the tables belonging to this database.
• Output of the requested data in different formats → XML, HTML
• BackUp functions (console programme 'mysqldump').
• DB reports
• Data export and data import (csv files).

In this project you can use not only the console client 'mysql', but also the console clients 'sqlite3' for SQLite3 or 'psql' for Postgres databases, if you have these clients installed.

Figure 21.3.5.2: Basic GUI for the console programme 'sqlite3'.

In the following sections, the console client 'mysql' is used:

Figure 21.3.5.3: GUI after starting the client 'mysql'.

After starting the external programme 'mysql' you will see the name of the external DB client and the PID of the started process as well as the welcome text of the database client in the output component. After that you work as in a normal console - with the difference that you can now edit all outputs with the Gambas programme under the above-mentioned aspects of programme extensions:

Figure 21.3.5.4: Display of a query - client 'mysql'.

Some special features characterise the presented project:

• The project uses a new component. The new component is called HistoryBox and is provided in the HistoryBox class.
• The project uses the new component because it has a so-called History as an extension of the TextBox component.
• You can use the arrow keys ∧ ∨ in the HistoryBox to search for already entered input, just like in a console.
• The classes HistoryBox, History and _HistoryOptions were developed by Tobias Boege. They were tested during the trial phase of the presented project.

As is usual with all components, the HistoryBox was also given an icon by the authors, which you can see in the IDE in the Properties window under 'Form'. The icon must be inserted in the project directory in an invisible folder .hidden/control as an image of the type 'png' so that it can be seen in the IDE in the virtual folder 'Project'.

• All components - except the TextArea for the output of the data - have been placed in a container HBox, which also contains a non-visible panel between the PictureBox component and the other components. The width of the panel dynamically adjusts to the window size, keeping all components at the right edge.
• The exception is the PictureBox, which remains permanently positioned on the left margin. Different coloured images are inserted as pictures depending on the process state, creating the impression of a coloured LED. The LED signals the state of the started process:

Figure 21.3.5.5: The DB client has been terminated

There are some programmes - like the DB clients presented - that permanently redirect their error output to the standard output. Thus, this procedure is omitted for these programmes:

Public Sub myProcess_Error(sFehler As String)
' ...
End

The source code is given in full:

' Gambas class file
 
Private $hProcess As Process
 
'-- Private sDBClientName As String = "sqlite3"
'-- Private sStartParameter As String = ""
Private sDBClientName As String = "mysql"
Private sStartParameter As String = "-f -n -vvv -u root -pYourMySQLPassword4SQLRoot"
 
Public Sub Form_Open()
  FMain.Center()
  FMain.Resizable = True
  FMain.Arrangement = Arrange.Vertical
  txaOutput.Expand = True
  txaOutput.Clear()
  HBoxSpacing.Height = 8
  HBox.Spacing = True
  HBox.Height = 24
  pDynamicPanel.Expand = True
 
  txbHistoryEingabe.Foreground = Color.Gray
  txbHistoryEingabe.Alignment = Align.Center
  txbHistoryEingabe.Text = "*** Enter SQL statement and activate with ENTER key ***"
  txbHistoryEingabe.ReadOnly = True
  txbHistoryEingabe.History = New History(50)
 
  SetLEDColor("orange")
 
End
 
Public Sub btnProcessStart_Click()
 ProcessStart()
 FMain.Text = "Database client " & Upper(sDBClientName) & "   :   PID = " &  $hProcess.Id
 txbHistoryEingabe.Alignment = Align.Normal
End
 
Public Sub btnTextAreaClear_Click()
  txaOutput.Clear()
  txbHistoryEingabe.SetFocus
End
 
Public Sub btnClose_Click()
  FMain.Close()
End
 
'*******************************************************************************************
 
Public Sub ProcessStart()
   Dim sCommand As String
 
  txbHistoryEingabe.Foreground = Color.Black
  txbHistoryEingabe.Clear()
  txaOutput.Clear()
 
  sCommand = sDBClientName & Chr(32) & sStartParameter
  $hProcess = Shell sCommand For Input Output As "myProcess"
 
  txaOutput.Insert(gb.NewLine)
  txbHistoryEingabe.ReadOnly = False
  txbHistoryEingabe.SetFocus()
  btnProcessStart.Enabled = False
  SetLEDColor("green")
 
End
 
Public Sub myProcess_Read()
  Dim sPuffer As String
 
  txaOutput.Insert(gb.NewLine)
  sPuffer = ""
  Read #$hProcess, sPuffer, Lof($hProcess)
 
  Select sDBClientName
  Case "mysql"
    sPuffer = Replace(sPuffer, "mysql>", "System-User: " & User.Name & " # >" & gb.NewLine)
  Case "sqlite3"
    sPuffer = Replace(sPuffer, "sqlite>", gb.NewLine & "System-User: " & User.Name & " # >" & gb.NewLine)
  End Select
  txaOutput.Insert(sPuffer & gb.NewLine)
 
End
 
'-- Public Sub myProcess_Error(sFehler As String)
'--   This procedure can be omitted, since error outputs are redirected internally to the standard output both in the MySQL client
'--   as well as the SQLite3 client are redirected internally to the standard output!
'-- End
 
Public Sub myProcess_Kill()
  txaOutput.Insert(gb.NewLine)
  txaOutput.Foreground = Color.Red
  txaOutput.Insert("Rückgabewert vom Programm '" & sDBClientName & "' =  " & $hProcess.Value & gb.NewLine)
  Select Case $hProcess.State
  Case 0
   txaOutput.Insert("Prozess (PID = " & $hProcess.Id & ") wurde normal beendet." & gb.NewLine)
  Case 1
   txaOutput.Insert("Prozess (PID = " & $hProcess.Id & ") wurde gestoppt!" & gb.NewLine)
  Case 2
   txaOutput.Insert("Prozess (PID = " & $hProcess.Id & ") wurde beendet!" & gb.NewLine)
  End Select
  SetLEDColor("red")
End
 
Public Sub WriteToMyProcess(sInput As String)
   If $hProcess Then
      If $hProcess.State = $hProcess.Running Then
         Print #$hProcess, sInput
      Endif
   Endif
End
 
Public Sub txbHistoryEingabe_Activate()
  If txbHistoryEingabe.Text = "" Then Return
' -- txbHistoryEingabe.Text = Trim(txbHistoryEingabe.Text)  ---> This is done by the History Box itself
  WriteToMyProcess(txbHistoryEingabe.Text)
'-- txbHistoryEingabe.Clear   ---> This is done by the History Box itself
End
 
Public Sub txaOutput_Change()
  txaOutput.Pos = Len(txaOutput.Text) ' ---> Jump to the last line
End
 
Public Sub SetLEDColor(sLEDColor As String)
   PictureBox1.Picture = Picture["LED/led_" & sLEDColor & ".svg"]
End
 
Public Sub Form_Close()
  If $hProcess Then
     txaOutput.SetFocus
     txaOutput.Clear
     txaOutput.Insert(gb.NewLine)
     $hProcess.Kill()
     Wait 2
  Endif
End