JDBClient is released under the GPL: http://www.gnu.org/licenses/gpl.html

Please visit http://www.arachnoid.com/JDBClient for more documentation and to acquire the latest version of JDBClient. This help page explains how to use JDBClient, while the home page covers installation issues and certain other topics in greater depth.

For formatting reasons and to properly read these instructions, users may want to temporarily make the JDBClient program frame larger.

Introduction | Features | Command-line Operation | Miscellaneous Notes

JDBClient is a Java program that serves as a MySQL database client (meaning a program that interacts with a server). With JDBClient you can create, author and edit MySQL databases, and conduct sophisticated searches (in database terminology these searches are called "queries"). You can analyze database construction and optimization, and copy generated content to other environments in several formats. You can create, delete, and modify data and tables, and perform many kinds of maintenance tasks using JDBClient's built-in MySQL terminal.

Because JDBClient is written in Java, it will run on virtually any platform. And to run, JDBClient needs the most recent version of Java, available free at http://java.com. Many runtime issues are resolved by simply upgrading to the most recent Java runtime engine.

This section's topics follow the order of the JDBClient tabs one uses to access different features. The topics are:

• Setup/Log 

  This tab allows you to configure the MySQL server connection and view the system log. There are entries for server, user and password. There is also a font size dropdown list that allows the user to change the font size in the displayed tables, the SQL terminal, and this help page.

  The Setup/Log tab also contains a command/error log that records commands (and possible errors) issued from the query panel and the SQL terminal. Log entries from the query panel are prefixed with "Q:", while those from the SQL terminal are prefixed with "T:".

  One may copy the log to the system clipboard by pressing the Copy Log button. The  Clear Log button allows the user to erase the system log. The Ellipsize checkbox forces the log table lines to fit onto the display by making the lines either longer or shorter, depending on their natural length. In some cases, for example while reading error messages, it's better to deselect Ellipsize.

• Table 

  The Table tab shows the result table from the most recently executed SQL query. Like the log table described above, if the user enables Ellipsize, the table display will be forced into the program window's width. So, to read all the table's entries in full, one normally wants to disable Ellipsize and use horizontal scrolling to access the full width of each record, but to get an abbreviated overview of the table's content, enable Ellipsize.

  If the user clicks one of the table's rows ("records"), the Edit/Enter tab (explained below) will open, offering the option to change the record's contents.

• Edit/Enter 

  The Edit/Enter tab offers the ability to create new records, delete records, make copies of existing records, and edit or enter record data. But to prevent inadvertent changes to database content, this feature is disabled by default. To enable it, select  Allow Edits at the lower right of the editing display.

  The rows in the editing display are labeled with field names at the left. It's not obvious at first glance, but each of the editing controls accepts multi-line entries — just press Enter to start a new line. When the user changes any of the field content, that field becomes red, showing that an entry has been made but not yet committed to the database. To commit your entries to the database, either press the  Commit button or press Shift+Enter within the editing field. To revert your entries before committing them to the database, press  Cancel.

  After asking for confirmation, the  Delete button will delete the presently selected record from the database. Remember about editing, deleting and similar actions, that there is no "Undo" button for a database table — changes are permanent. So think about backing up tables you intend to edit (see below for more about this).

  The  Copy button will make a copy of the presently selected record within the current table, but this feature can't always work, in which case the  Copy button will be disabled. The reason one can't always copy a record is that copying requires a table to have a primary key, an issue explained below under Table Description .

  The  New button creates a new, mostly-empty record in the present table. I say "mostly" empty because, to save the user some effort and anticipating typical entries, any existing date, time and datetime fields are automatically filled in with the present date and time. It's important to understand that the design of some tables prevents the creation of a new, empty record, a topic beyond the scope of these instructions.

  Navigation and commit

  Because the edit entry controls accept linefeeds and allow internal navigation with the arrow keys, one may move between entry fields with Shift+Up arrow and Shift+Down arrow. A record may be committed to the database either by typing Shift+Enter within an entry control, or by pressing the  Commit button.

  Backup

  Remember about record editing that database content editing is not like word processing and most other computer activities, where there is an "Undo" button for nearly everything. A database table has precisely one state, and no sense of history, so one must (a) be careful, or (b) always have a backup copy of the edited table or the entire database. Actually, I recommend both: (a) be careful, and (b) always have a backup. On that topic, the Table Description  tab described below has a table backup feature whose use I strongly recommend.

  Editing and querying

  Before considering editing a table that results from a selective query, be sure to read the item "Don't mix queries and edits" at the bottom of the next section.

• Query 

  Detailed query example

  Database querying is practically a science, but the simplest examples are easily explained and performed. However, to be able to present meaningful examples, the user needs to have the same table used for the tutorial. So:

  1. There is a perfect practice table that's part of my online SQL tutorial, in the form of an SQL archive named people.sql. But it's not necessary to download this file with a browser, because JDBClient knows how to read Web content directly.
  2. Move to the SQL Terminal  pane and press  Connect.
  3. If you don't get connected right away, or if you hear a bell, you may need to visit the Setup/Log  page and enter a server, user name and password for your MySQL server.
  4. Once connected, from the SQL Terminal  type this:

     source http://arachnoid.com/MySQL/resources/people.sql

     To save time, you may want to copy the command from this page and paste it into the SQL Terminal entry row (copy = Ctrl+C, paste = Ctrl+V).
  5. If all is well, the command will be executed with no error messages, and you will have a new database named "tutorial" and a new table in that database named "people".
  6. Immediately after executing the above "source" command, just to make sure everything went as expected, at the SQL terminal type "select * from tutorial.people" and press Enter. A list of 1000 names should appear in the display.
  7. Next, to allow the new tutorial database and table to be listed in the JDBClient drop-down lists, select the Setup/Log  tab and press the  Start/Restart button at the upper right.

  Now that the user has the source table for the following examples, we can expect the same outcomes as those presented on this page. To set up for your first query experiments, do this:

  • Select the Query  tab.
  • At the top left of the query display, choose the "tutorial" database.
  • At the top right of the query display, choose the "people" table.
  • Click the  Query button at the bottom of the program display.

  The result should be a table display of 1000 records of make-believe people, with some associated information. This practice table is perfect for writing experimental queries — it's big enough to be interesting, and it's entirely made up, so no real person's information is exposed.

  Now we can write queries. A real-world MySQL query might look like this:

  SELECT `First Name`,`Last Name`,`Gender` FROM `tutorial`.`people` WHERE `age` > 25 AND `age` < 50 ORDER BY `Age`

  In fact, the reader can copy the query statement from this page and paste it into the SQL terminal entry control, and it should execute correctly (if the tutorial database has been specified in advance with "use tutorial"). But such complex queries are difficult to get right when hand-entered, which is the reason for the Query  entry pane — its purpose is to simplify the entry of complex queries.

  The basic outline of the above SQL query is:

  SELECT (selected fields) FROM (database.table) WHERE (conditions) (ORDER BY / GROUP BY etc.)

  Using this outline as a guide, the JDBClient query display is organized this way:

  • (selected fields) : from a "Prefix arguments" entry, or if blank, then from the "include" check-boxes at the right of the query pane.
  • (database.table) : from the database and table dropdown lists at the top of the program window.
  • (conditions) : from the row of entries at the center of the query display, two per field.
  • (ORDER BY / GROUP BY etc.) : from "Postfix Arguments" if an entry is made.

  The first thing to understand is that blank query fields are ignored — only those that have entries are included in the MySQL query.

  Now let's write a query:

  • For this example, the database and table are assumed to have been preselected to be "tutorial" and "people" as above.
  • In the "Age" field entries, enter "> 20" at the left and and "< 30" at the right:

    

  • Now press the  Query button at the bottom of the program display.
  • If the settings and entries have been made correctly, the outcome label at the lower left should say, "Read 161 records from tutorial.people". And the "Query" line above the query entries should read:

    SELECT * FROM `tutorial`.`people` WHERE ( `Age` > 20 AND `Age` < 30 )

  • Now move to the right side of the query entry pane, to the column of "include" check-boxes, and deselect all fields except "Last Name" , "Age" , and "Music". This is an easy way to choose specific fields for inclusion in a query result.
  • Press  Query again.
  • Notice that the number of selected records is the same (161) but the included fields correspond to the selected check-boxes — "Last Name" , "Age" , and "Music".
  • From this example, we learn that the WHERE clause, defined by the field entries, decides the number of records (rows) in the result, but the check-boxes at the right define the SELECT clause, thus deciding the number and identity of fields (columns).

  Another example. The above example selected 161 records from a set of 1000. That leaves (1000 - 161) 839 that were not selected. Can we design a query that selects only the records not selected by the above query?

  • For the age entry, instead of "Age > 20" and "Age < 30", we want to type in its logical opposite.
  • So for this new query we begin by typing "Age <= 20" and "Age >= 30", and we press  Query.
  • The result isn't 839, in fact, it's zero. We appear to have forgotten something in our new query.
  • What we must understand is that the logical opposite of "Age > 20 And Age < 30" is "Age <= 20 Or Age >= 30".
  • So between the two age entries, click "Or" instead of "And":

    

  • Press  Query again. If the entries have been made correctly, the label at the lower left should say "Read 839 records from tutorial.people" and the query text at the top of the query pane should be:

    SELECT * FROM `tutorial`.`people` WHERE ( `Age` <= 20 OR `Age` >= 30 )

  Here's a somewhat more advanced example:

  • Preserve the entries above, don't erase them.
  • In the "Prefix arguments" entry, type "Music, count(*) AS Total"
  • In the "Postfix arguments" entry, type "GROUP BY Music".
  • Press  Query.
  • This should be the result:

    Music	Total
    Baroque	171
    C & W	155
    Classical	169
    Jazz	177
    Rock	211

  • The text form of the query, visible at the "Query:" display at the top of the entry pane, should be:

    SELECT Music, count(*) AS Total FROM `tutorial`.`people` WHERE ( `Age` <= 20 OR `Age` >= 30 ) GROUP BY Music

  • This query result tells us how many individuals (from our imaginary population), outside the age range of 20 and 30, favor which kinds of music. That's a rather complex database result, and one arrived at with little effort.
  • This example also shows that:
    • An entry to "Prefix arguments" takes precedence over the field-selection checkboxes, and
    • Relatively simple queries can produce interesting and useful results.

  Query Export Formats

  There is one more important query panel feature — the ability to copy the displayed table onto the system clipboard in various forms, using one of the buttons at the top of the query display:

  •  Copy TSV: produces a a Tab-Separated-Values (TSV) plain-text table and places it on the system clipboard. This form is ideal for pasting into a spreadsheet program.
  •  Copy HTML: produces an HTML-formatted document and places it on the system clipboard (this is how the query result table above was created)
  •  Browser: launches the system browser showing a Web page version of the table

  Backtick database, table and field names

  While using JDBClient, for most entries the query entry code will manage this issue automatically, but at times the user may have to manually type in a query, as for example when using the prefix and postfix entry fields. When entering a "raw query" without any automatic formatting, remember that nearly everything but the actual argument should be enclosed in `backticks`. Example:

  SELECT `City`,`State`,`Zipcode` FROM `database`.`table` WHERE `City` = "Jefferson" ORDER BY `Zipcode`

  This may seem like overkill, and MySQL would have accepted this particular example without the backticks, because none of the names have embedded spaces. But in the general case, and especially when a query raises an incomprehensible error message, check for a name that's not enclosed in backticks.

  Always quote text arguments

  When making query entries, remember that numerical arguments don't need to be quoted:

  `Age` > 20

  but text arguments must be quoted:

  `Name` = "John"

  This is a very common source of MySQL errors among new users, and if one were to omit the quotes around "John", the MySQL error message would only sow confusion:

  Unknown column 'John' in 'where clause'

  Numerical arguments don't need to be quoted, but it's not an error to quote them. Some MySQL old hands adopt the habit of quoting all arguments, regardless of type.

  Different kinds of quotation

  The above example showed a typical MySQL query using `backticks`, plus an argument enclosed in normal quotation marks. The choice of quotation mark might not seem important, but unfortunately it is.

  • Database, table and field names should be quoted with `backticks`, but never 'single quotes' or "double quotes" (to find out why, try it :) ).
  • Text arguments should be enclosed in either 'single' or "double" quotes.

  Why are there two acceptable quotation marks for arguments? Easy to explain — If an argument contains one of the two acceptable quotation marks, one can avoid ambiguity by enclosing it with the other:

  • "That's not fun at all."
  • 'He said his name was "John".'

  In the special case where an argument contains both kinds of quotation mark, the user should use a special escape notation:

  • " John said, \"That's terrible!\" "
  • ' John said, "That\'s terrible!" '

  Special characters

  The query entry controls (and the SQL terminal entry control to be described below) are single-line entry controls, which means they won't accept tabs or linefeeds. So how does one use tabs or linefeeds as part of a query? Easily answered — to enter a linefeed into a query, you "escape" it — you type a reverse-slash before the identifier: \n for linefeed. Here are the special characters that are accepted this way:

  • \t : tab
  • \b : backspace
  • \n : linefeed
  • \r : carriage return
  • \f : formfeed

  Entry history

  Because the "Prefix" and "Postfix" text controls are often used to customize queries, they have a history feature, and this history is preserved between program runs. To access the history, click in the control of interest and press the up-arrow and down-arrow keys to browse past entries. To clear this history, click the  Clear H button at the upper right. For other query entry controls, the up-arrow and down-arrow keys navigate between fields.

  Navigation and commit

  Because the query entry controls are single-line, they don't use the up and down arrow keys. This means one may move between entry fields with the up-arrow and down-arrow keys. And a query may be executed by typing Enter within an entry control, or by pressing the  Query button at the bottom of the display.

  Don't mix queries and edits

  Before leaving the topic of queries, I should add that editing activities and querying activities are so different that users should try to avoid mixing them. The most reliable table editing is against a full table with all its records and fields on display, while the most interesting queries involve only a small part of a complete table. Editing a table that is the outcome of a complex query is a tricky business, best avoided.

  Just one example. While editing records, the user may want to create a new, empty record to fill with information. But if the displayed table results from a selective query, the new record may not appear in the table listing, and without an error message to explain why not. The reason might be that the current query's arguments exclude the added record. (I mention this because I had the experience.)

  To avoid these sorts of problems, before editing records and in most cases, press the  Clear Q button at the upper right in the query dialog, then press  Query again.

  An exception to the above rule would be a large table that takes too long to reload after each edit (because of MySQL's automatic content processing features, JDBClient always reloads a table after each edit). In that case a selective query makes sense because it speeds up the editing process. But the user needs to remember that a query that speeds up edits may also exclude records the user expects to see.

• Table Description 

  The TableDescription tab shows the internal structure of the selected table and offers some tools for table management.

  One of the more important fields a database table should possess is a primary key. A primary key, unique to each record, allows much faster database operation as well as avoiding one of the more annoying database behaviors — the inability to distinguish between records. In fact, without a primary key, JDBClient won't allow the user to copy records within a table, because of the good possibility that the database won't be able to distinguish the copy from the original.

  For a simple table with a handful of records, all of them clearly distinguishable, this is not an issue. But as tables become larger, the primary key issue also becomes larger. JDBClient has a simple solution to this problem, located on the TableDescription tab — the  Add Key button that will add a primary key to the currently selected table.

  Another equally important feature is the  Backup button, which will make a backup copy of an entire table. This is an important safeguard against data loss when used in advance of database editing. The  Backup button creates an exact copy of a table, with all its properties and data intact.

• SQL Terminal 

  The  SQL Terminal is for advanced database activities, and should be used carefully. There are safeguards elsewhere in JDBClient, but they can't protect against an error when using the SQL terminal. It cannot be overemphasized that a few careless keystrokes in the terminal can wipe out an entire database.

  The JDBClient SQL terminal resembles a standard MySQL utility, the "mysql" command-line utility, but it isn't exactly the same. The terminal built into JDBClient can do things the standard utility cannot (like reading URLs), and vice versa — there are some things the standard MySQL command-line utility can do that the built-in utility cannot. The reason for including the built-in tool is so that JDBClient will function exactly the same on any platform for which there is a Java runtime engine, including platforms that don't have the standard command-line utility installed.

  Remember about the SQL terminal that, because it has a single-line entry control and therefore cannot accept control characters, it instead accepts the escaped "Special Characters" described in the Query section above.

  The SQL terminal is ideal for certain database maintenance tasks, and the user will probably find it convenient and easy to use — but do remember that there are no safeguards against a mistyped command.

  The terminal has a history feature, a record of past entries that is accessible using the arrow keys, and this history is preserved between program runs. The terminal also has a button to copy the contents of the display to the system clipboard for use elsewhere.

• Help 

  This page is on the help pane, a useful utility that allows full-text searching and that will launch the system browser whenever the user clicks a hyperlink. It should be possible to read this help file while familiarizing oneself with JDBClient, by switching tabs between the help pane and another tab of interest.

JDBClient has some command-line arguments that simplify launching the program with a particular configuration, or one of many configurations for different purposes. The arguments are:

Usage: -f "name of font" -s server
       -u user -d database -t table
       -p password -r read table -h help
        

• -f (fontname) : forces use of a particular font for table, terminal and editing displays. In most cases this won't be needed — Java knows how to access system fonts, and if a suitable Unicode font is installed on your system, chances are Java (and JDBClient) will be able to access it.
• -s (servername) : specifies a server other than that last accessed (remember that JDBClient remembers things like this between runs).
• -u (user name) : as above.
• -d (database name) : as above.
• -t (table name) : as above.
• -p (password) : the user's MySQL access password. Entering a password on the command line involves some security risk on a system accessible by others. If no password is entered, JDBClient will prompt for one, which is a more secure approach.
• -r (read table) : This option causes a default query to be executed when the program starts. This, along with the other command-line arguments, has the effect of automating the launch process, so at startup the program reads and displays the table chosen by the user.
• -h (help) : prints the above option list and exits.

Unicode issues

• A modern computer program must support Unicode, and this goes double for a database program. JDBClient fully supports Unicode text, but for correct display it requires that your system have Unicode font support. What this means is that JDBClient will properly manage Unicode database content, but won't necessarily display it correctly unless the system has Unicode font support. Here are some sources for Unicode fonts:

  • Bitstream Cyberbit, a good Unicode font, meaning it has a large, but not complete, collection of glyphs. The link includes sources for download.
  • Arial Unicode MS, a better font than that above, which includes all the characters in version 2.0 of the Unicode standard, but apparently not free. It is available from various sources on the Web, but its standing and ownership are unclear.
  • Code2000, an ambitious, seemingly comprehensive, but now-abandoned project. Various versions of this font are available online, including some named "Code2001" and "Code2002".

Similarities with the "Mysql" command-line utility

• Users familiar with the eponymously named MySQL command-line application may recognize some of its behaviors in my terminal application, and may think I'm simply running the external application with a Java front end. But no. Because of portability concerns, I decided to write my own code, and it resembles the MySQL command-line app only superficially. My Java version has the advantage that it will run on any platform exactly the same, and I don't have to try to locate an external application that may not even be installed on some systems. My app also reads/understands URLs and directly downloads online databases, which the MySQL command-line app cannot do.

Program state preservation

• JDBClient maintains a configuration file located on your system at (configuration path). In this file is a pretty complete representation of the program's state — server name and access port, user name (no password is saved), and a number of user-settable options that I thought it desirable to preserve between program runs. Over the years I have increased the number of settings I wanted to preserve, and I've gradually developed better ways to save and restore program settings. This program's configuration file preserves a great number of things, such as the most recently entered query, the location and size of the program window, the last dialog tab the user selected, the history list (past entries) from the SQL terminal, and many more — even the last scroll position on this help page.

Partition bar

• Some JDBClient displays are partitioned by a horizontal bar. This bar's position can be adjusted to suit the user's requirements (just drag the mouse cursor on the bar). The user's choice is preserved in the program configuration, our next topic.

Saved Web pages

• When the user presses the  Browser button, JDBClient generates a Web page, saves it on your system in (webpage path), and launches the system browser to display it. This means that, over time, Web pages will accumulate in the above directory and the user may want to either move these pages for other uses, or delete them.

JDBClient resources

Here are some JDBClient-related resource locations:

Resource	Location
JDBClient Home Page	http://arachnoid.com/JDBClient
JDBClient version	2.7
User home directory	(user home directory)
JDBClient Web page directory location	(webpage path)
JDBClient configuration file location	(configuration path)
Currently running JDBClient application location	(application path)

Bug reports

• If you find a bug in JDBClient, please visit http://arachnoid.com/messages and leave a report. Please make sure what you report is actually a bug in the program.