GENERAL PURPOSE SQL CLIENT TUTORIAL

• current directory '.'
• the directory above current directory ..\
• The swiftapp API jar swiftappj.jar from the main swiftappj distribution
• The MMMYSQL driver jar file (mysql.jar or mysql_uncomp.jar depending on which version of MMMYSQL you have) NOTE:This API has been tested with MMMYSQL version 1.2c but should also work with any later version.
• dg_code.jar from the dg_coder distribution
• The main runtime jar file rt.jar (usually in \jre\lib under the JDK install directory) such as c:\jdk1.3\jre\lib\rt.jar
• on my system all jar files are in c:\jars so my classpath is:

  SET CLASSPATH=..;.;c:\jdk1.3\jre\lib\rt.jar;c:\jars\mysql_uncomp.jar;c:\jars\swiftappj.jar;c:\jars\dg_code.jar
  

• invoke the SQL client as follows:

        java com.wicolor.db.gp_client [setup-file] [utility-file] [metadata file]
  

  NOTE:filenames may be specified as relative paths 
        such as ../../urls.txt 
   or full pathnames such as 
           c:/test/urls.txt
   note always use forward shashes "/" instead of backslashes "\"
  
  
  

• The three files specified on the command line are :

  ---

  • A setup file loaded into the setup panel; this contains three types of information
    • Urls for JDBC(TM) data sources such as

      default_url=jdbc:mysql://localhost:3306/test
      
      up to ten additional URLs may be specified in the following form:
      
      url1=jdbc:mysql://localhost:3306/test1
      url2=jdbc:mysql://localhost:3306/test2
      ...
      url10=jdbc:mysql://localhost:3306/test10
      
      

    • Drivers (TYPE 3 or 4 JDBC(TM) drivers ) these must be on your classpath

      Format is similar to URLS above

      default_driver=org.gjt.mm.mysql.Driver
      up to ten additional Drivers may be specified in the following form:
      
      driver1=org.some_other_driver
      ....
      
      

    • Up to ten SQL commands (or partial commands ) mat be specified such as

      cmd1=SELECT * FROM DCUST_MAST
      cmd2=SHOW CREATE TABLE 
      ....
      cmd10=set @SOME_VAR=0
      
      
      

    • Sample setup file contents are shown below:

      default_driver=org.gjt.mm.mysql.Driver
      driver1=org.gjt.mm.mysql.Driver
      default_url=jdbc:mysql://localhost:3306/test
      url1=jdbc:mysql://localhost:3306/test1
      cmd1=SELECT * FROM DCUST_MAST
      cmd2=SHOW CREATE TABLE 
      

      ---

  • The second file is used for a log file; statements , notes ,comments etc can be logged to this file. this file is loaded into the utility panel on startup
  • The third is for Metadata ; it is loaded into the MetaData panel on startup

    output of commands such as

    show tables 
    describe_table [table_name] are logged to this file
    

    NOTE:If contents of any of the three panels, as well as the text dump panels are changed during a session, the GP_CLIENT will prompt you to save them on exit
  
• The setup panel is shown above:

  functions available are

  
  • The upper left panel below the "CUT" button

    This panel shows the currently selected driver and URL
    You may type directly into this area, or load it from the URL and DRIVER JComboBoxes to the right.

  • The lower area is a JTextArea it may contein URL,Driver,and Command preferences as explained above. It may also contain any other test you want to type into it.
  • The "CONNECT" AND "DISCONNECT" allow you to log into or disconnect from the currently selected URL/DRIVER comnbination. login is by a standard login dialog.

Functions of the other panels.

• QUERY PANEL
• OUTPUT PANEL
• UTILITY PANEL
• METADATA PANEL
• PROCEDURE PANEL
• PROCEDURE FILE LIST PANEL
• BATCH UPDATE PANEL
• TEXT DUMP PANEL

• QUERY PANEL

  This panel is used for interactively executing SQL statements.

  Functions are as follows:

  • The lower portion of the panel is a text editor which may be used to enter a single SQL statement.

    Functions such as cut,paste,clear etc are available on the control bar just above the text area

  • The "run query " button tries to execute the SQL statement in the edit area

    If the executed statement returns a result set, results will be displayed in the output panel.

    If an error occurs , a dialog will inform you of this, and the error will be logged to the lower part of the output panel. NOTE: you can cycle thru the editor function panels by clicking the "<" or ">" buttons at the ends of the command bar

  • The JComboBox above the edit area allows a number of 'standard' commands, along with any commands specified in the setup file to be selected into the lower text area for execution
  • The area above the JComboBox is a table used to store and retrieve commands.
  • The "get from table" button replaces the query in the edit area with the text from the table entry selected above
  • The "add to table" appends a new entry to the top table containing a copy of the text in the edit area.
  • The "delete from table" button delete the selected entry in the top table.
  • The "dump to utility panel" button copies all entries from the upper table to the utility panel.

  ---

• OUTPUT PANEL
  • The upper part of this panel is a JTable which displays results, if any, returned when a SQL statement is executed.
  • The lower part of the panel shows any messages or error information produced by the command, such as how many records were affected.

  ---

• UTILITY PANEL

  This panel serves as a general purpose 'session log' the program prompts you to save it on exit if its contents have been modified

  ---

• METADATA PANEL

  This panel serves as a second 'session log' the program prompts you to save it on exit if its contents have been modified

  output from SHOW TABLES and describe_table [table_name] are automatically logged to this panel

  ---

• PROCEDURE PANEL

  This panel allows a series of SQl statements to be run as a 'batch' There is no restriction on format , except commands must be separated by ';' (semicolon) NOTE:commands must not contain embedded semicolons

  • The "RUN PROCEDURE NO RESULTS" button is used to run insertrs, deletes etc. which do not return results
  • The "RUN PROCEDURE W/RESULTS" button is used to run a series of commands which may return results.
    If results are returned, they will be shown in the lower text area of the panel NOTE:if the "append" box is checked results will be appended to the lower text area instead of replacing it.
    Note also thet both top and bottom panels are fully functional text editors.
  • the program prompts you to save the upper panel on exit if its contents have been modified
  • If errors occur, the following dialog will be shown, alowing you to decide whether to continue or not.Error messages will be logged to the lower panel.

  ---

• PROCEDURE FILE LIST PANEL

  This panel allows groups of

  ---

• BATCH UPDATE PANEL

  The batch update panel functions similarly to the procedure panel except:

  • Commands must each be on a single line
  • They must not return results.
  • They do not need to be separated by ';' and may contain embedded semicolons.

  ---

This panel allows results from a SQL ststement which are displayed in the output panel to be manipulated in a number of different ways

Functions of controls are as follows:

• if the 'ALL' box is checked , the text dump panel wil use all rows of the output panel; if not it will use only those that are mouse-selected
• The 'LF' and 'RT' JRadioButtons control which of the two lower text areas will get the output text.
• The "append" checkbox controls whether output will be appended to or replace text in the selectes text area.
• The "dump as text" button formats selected rows of the outpput panel as plain text
• The "Get inserts" button uses the text in the upper text area as the first part of an insert statement, and uses the selected output rows to amke up a values() clause
• The "get as csv" button returns selected rows fromthe output panel as comma-separated values suitable for importing into a spreadsheet or other program.
• The 'html' button formats the selected output rows as an html table
  The 'IN clause" button allows a column in the selected result panel rows to be selected and displays a second dialog showing an in( ) clause containing values from the selected column.
  

  The image below shows some sample output from this panel.