JdbM was designed to get access to a wide variety of databases mainly to retrieve data, view the data, express the data as graphics, create report tables that can be shared via email or further processed via spreadsheet. The idea behind is to provide the user with a well-known consistent appearance no matter where the data come from.
It also provides means through SQL to create and manipulate tables, edit, append, delete and modify data. Security is only maintained via the Session login level and thus relies on the driver/server.
Since JdbM relies on the JDBC model all the possible SQL functionality is determined by the underlying driver/server combinations that can be extremely different in capabilities.
I have tested many drivers for the built-in JDBC/ODBC bridge e.g. for dBase, MS Access�, IBM Lotus Notes� (Domino) and also some direct JDBC drivers e.g. for Domino, and Atinav� drivers. They all work with JdbM but offer completely different levels of SQL from read-only in textmode to full featured SQL.
Before you can use JdbM you need to put the following parts together:
In order to get these components to work together it is neccesary to get your PATH and CLASSPATH environment variables right.
The minimum requirement is to get the PATH and CLASSPATH variables point to your Java2SE Runtime environment directory, to the JDBC database driver (eventually also server directory or jarfile) and to JdbM.jar.
How you do that is determined by the OS you use. In MS Windows� you would log in as Administrator and use the System tray.
In Linux you would modify the environment variables in the .profile or .bashrc scripts.
When starting JdbM from the command line you do the following: go to the directory where JdbM.jar resides and type : java -jar JdbM.jar.
If you need to set the classpath for the Java session only you type�:
java -cp PathToJavaSE;PathToJDBC;PathToJdbM;. -jar JdbM.jar
where PathToJavaSE is the Path that points to your Java Environment, PathToJDBC is the path that points to your JDBC driver(s), PathToJdbM is the path pointing to JdbM.jar and the dot (.) is the current directory (where JdbM.jar resides)
Note: In Linux you have to separate the path entries by a colon (:) instead of the semicolon (;).
Note: For setting the environment variables in Windows you need to be logged in as Adminstrator.
File Menu:
The program stores its information in project files that have the extension "jdb". The file menu contains everything you can do with a project file such as creating a New, Open, Save, Save As.
You can open multiple project files at the same time. You will need to do this when using the data pump. NOTE: Not all possible cross effects have been eliminated yet.
Exporting of tables to HTML files works with all Queries, Data Grids and Reports and thus provides a good means to share data by email and web and also provides possibilities for importing by other programs that understand HTML.
Exporting to a tabular format (here called TAB format), where the rows are separated by line feeds and the columns are separated by tabstopps works the same way. This format is ideal for import in spreadsheet programs, other databases or text oriented applicatios.
Page Layout and printing output for text work on a basic level. Printing of Graphics works fine, but the size of the diagram on paper is determined by the size of the screen window. This needs some experimenting to get acceptable results.
Edit Menu:
The Edit Menu contains means for clipboard operations such as cut, copy, and paste.
With all text sensitive components such as editor fields, text fields, labels the clipboard is addressed via Ctrl + C for copy and Ctrl + V for paste.
For the Nodes in the JdbTree you have to use the menu items to copy and paste
.For example you can select a query, use the Edit/copy menu, select the query Parent Node and then use the Edit/paste menu; you will get an identical copy that you can later on modify
.You can also transfer fields from a form to a report etc. in the same way.
You can also search certain database objects like Tables, Queries, Grids and Forms. Searching is possible only in the underlying Table models, that means you are not actually searching the database but you are searching the result set represented by the underlying query.
The ways to search are key sensitive search, key insensitive search and - since Java 1.4 - regular expressions.
Regular expressions have a syntax of their own that can get pretty complicated. Here are a few easy examples:
To find the strings "Doctor" and "Doktor" type "Do.tor" => the dot represents one single letter no matter which
To find the strings "Doctor", "Doktor" and "Doktoor" you type "Do.to*r" => the asterix represents zero or more repetitions of the previous letter (o)
To find the strings "Doctor Mabuse", "Doktor Mabuse was here" and "Doctors" you type "Do.tor.*" => ".*" represents any number of any subsequent letters
For further information look in the internet under the keyword "regular expression"
Data Pump
Using the data pump, you can transfer datasets from one database to another, across different database sessions, different drivers, computers, servers.
In order to do that, you must have minimum one open connection. If you want to transfer data between different databases you must have one connection open for each database you are using. Then you fill out the fields for source connection (that's where the data come from) and target connection (thats where the data go to). You decide from which table you want to retrieve the data and to which table you want to append the data. NOTE: The display name of the table node and the table name of the table node must be identical, otherwise you will get a 'SQL error: table not found'.
Then you select the appropriate fields and add them to the field list. Fields opposite each other in source and target table will be matched. When this is done you can check the underlying query by pressing the 'Test' button. When everything is OK then you press the 'Start' button.
View Menu:
The view menu lets you toggle on and off the JdbTree and the Connection window which is useful when you have a working project that you control via macro buttons in a main menu form.
Help Menu:
The help menu lets you display help for the program and som other informations.
When you have managed to start JdbM, it presents you an empty frame with a menu.
From the menu you chose File->New to create a new database project. New projects are named default.jdb. You better save this file under a new name before you continue.
When you have saved the file you first start editing the Session node by pressing on the gear symbol "Edit a node".
In the dialog box you enter the driver class name for the JDBC driver you want to use. This you must find out from the supplier of the driver. It usually consists of 3-4 parts like sun.jdbc.odbc.JdbcOdbcDriver which is the URL of the built-in driver bridge for ODBC.
The next thing you enter is the URL for the connection which is jdbc:odbc:DataSource for the JDBC ODBC bridge where DataSource is the ODBC data source you need to configure first. With other JDBC drivers you have to state a server connection port and a database name or similar. This information you get from the supplier of the JDBC driver.
Then you can enter a username and a password for the connection. If you leave these fields blank, it is assumed that you want to log in manually when you open the connection. This does not work with all JDBC drivers. Some require a username and/or a password from the beginning.
Then you choose which way you want to connect when you open the project. With Auto Connect = yes the session is opened automatically when you open the project file. Choose this option when you have a relieable connection established only.
Left, Top, Width and Height are the dimensions of the main window you want to have when you next open the project file.
Update Mode specifies how you want to have your database updated. This can bei eighter "Up Where All" where the update statement is done with a SQL WHERE clause that includes all database fields in the query. This only works properly, when you have no dublettes in the database. Otherwise all fields would be updated that match the WHERE clause.
The other possibility is to update the database with a single but unique field value (e.g. a primary index). Unfortunately not all JDBC drivers and databases support primary indices so JdbM has a feature to create unique values for a field on entry. For further explanation see chapter about Data Grids and Forms.
Now you can close the dialog box and save your project again.
When you have created a database session you can open it by selecting the connection node and pressing the folder symbol.
A window will open that displays informations about the database driver, the database tables (if the driver can retrieve them) and wether the connection could be opened.
Should the connection not be open you get error messages in this window.
Most problems come from
At this stage everything depends on the JDBC driver. Problems have to be solved with the driver manufacturer.
Having established an open connection, you can go to the next node named Tables and press the button with the document symbol ("add a node") this will create a table node under the Tables folder. You select the new node and press the gear symbol ("Edit a node") again.
You get a dialog box which allows you to set the parameters for the table but also to create, modify and delete tables in the database via SQL commands.It also allows you to retrieve information about fields in the database table.
A table is identified within the project by its Display Name. Allthough multiple display names are allowed, it is useful to keep them unique.
Then you can select (if the driver supports retrieving the names) a table name. Otherwise you have to enter the name manually.Left, Top, Width, Height, Background, font name and font color specify the position, size and style of the table when opened for display.
Then you will find two selections for "SQL-Commands" and "Table-Info". In SQL-Commands you can enter valid SQL statements from the Data Definition Language (DDL) like CREATE, ALTER, DROP TABLE | INDEX and the Data Manipulation Language (DML) like SELECT, DELETE, INSERT, UPDATE as far as the driver allows. To put these commands in effect press the button "Exec SQL". SQL errors that come from the driver will be displayed in a popup window.
If you put a SELECT * FROM table statement in the SQL-Commands window, move to the Table-Info tab and press the button Table-Info, you will get a list of all fields in the specified table with their data types, field widths and more information.
NOTE:Be careful when using SQL-Commands on the table. This can have severe undesired effects on the database and is only recommended for SQL-experienced users. All commands wil take effect without prior warnings when you press the button Exec SQL.
When you select a table and press the button "open a node" the full table will be displayed with all columns and rows. This can be quite a huge table, so be careful what you do.
Queries are somewhat like the central part of the application. All forms, reports, graphics and macros retrieve their data from named queries in the project.
NOTE:JDBC by its definition does not support any special SQL features but it relies on the capabilities of the JDBC driver/server combination. The SQL commands are only transferred to the driver which has to take care that the commands are properly understood by the underlying database. Therefor some features may not work with one or the other database.
In our definition, queries are SQL statements that begin with a SELECT command. For creating a new query, you select the Queries node and press the document button ("add a node"). Then select the new node and press the gearbox button ("edit a node"). In the popup window you can specify the properties of the query.
Display name is the identifier for a query in the project. So keep these names unique to avoid mixup.
Left, Top, Width, Height, Background, font name and font color specify the position, size and style of the query window when opened for display. In the field with the button Table list fields you can first select the table that you want to query and then press the button Tablel list fields. A list of fields will then appear in the following dropdown list.
You can copy/paste table names, field names etc. into the query statement by using Ctrl + C and Ctrol + V or by using the button Insert to SQL. These dropdown lists are only for convenience and work only if the driver supports the required features. The data retrieved by the query are determined by the content of the SQL statement only so it does not matter what is displayed in the dropdown fields.
The dropdown list called Auto field has a special meaning. When selecting a value in this field (before you must select a table and fill the list by pressing the button) this field will be treated as a primary index like structure. That means, when you insert a new dataset, this field will be automatically filled with a value that is (hopefully) unique for the database. This value is generated by an algorithm that basicly uses the millisecons counter of the system. Provided that the field is large enough to hold this value this should be a fairly unique value.
For smaller fields like integer, the information content needs to be reduced thereby increasing the risk that the value is not unique.
NOTE: this has nothing to do with auto-incremental fields or primary indices that are maintained by the database server. If the server is properly configured, you will receive an SQL error when you try to insert a duplicate value in such a field. Exceptions to standard SQL:
Normally parameters are understood in the way that the SQL WHERE statement contains one or more question marks (?). These are used to prepare a query on the server side which has to be completed later by filling out the missing parameters. In our application, these parameters are filtered out by the program instead of being passed to the server. Instead of always editing a query to get the right information filtered, you can put question marks.
A structure like WHERE (year = 2003 AND month = 10) you can write as WHERE (year = ? and month = ? )
and define 2 parameters named month and year with the appropriate data types. (To find out these data types you can use the Table info function in the table node).
When the query is opened, a dialog box is displayed asking for the values of year and month and providing a default value. Whenever the SQL command contains a question mark (?), the program expects a parameter and will ask the user upon opening of the query to enter a value for the parameter.
The parameter names, data types ans so on are defined as subnodes of the query node and have to be inserted manually. They are expected to appear in the same order as the question marks appear in the SQL command.
$MASTER:
In order to be able to support automatic processing of combined main/subforms, a special command was added. When the WHERE statement contains the string $MASTER, the query knows that it has to search for the value in a main form. It will take the field that was specified in the form definition (see later) as the Master field and filter only those records that equal the master field value displayed in the active data set of the main form. This sounds complicated but isn't really.
As already described above, parameters contain information that is neccesary to complete an uncomplete query and to make queries more flexible.
Even drop down lists can be created that use a query of their own for display. Display Name as always is an identifier for the parameter. It has no further meaning as parameters have no name, but you should use the field name as identifier. Display Label is the text that is shown in the popup window Parameter Default is a default value for the parameter that is also shown in the popup window Param Type is the data type of the field that is queried. e.g. when the field year is an integer you must give the parameter also an integer type.
Lookup Query is an optional query that you can specify as a SELECT statement directly in the field. When the popup window is opened, the query is run and the results of the query fill up a dropdown list. This is very practical for presenting a selection.
Allthough you can open queries directly and get a table with the result set it is more comfortable to have a nice layout for data presentation.
For this purpose you create forms. A form can be displayed as standard data mask representing one data set, as a data grid (a table) or as a combination of a form and a grid. For every form the contents must be defined by adding field nodes to the form node. This is done semiautomatically with the button "Create field list". But lets's start from the beginning:
Display Name as usual is the unique identifier for the form. Left, Top, Width, Height, Background, Font are the parameters that define the appearance of the form. Note that when you are in the background field, you can use the "Colors" button to select a colur, that is displayed in a numeric (RGB) format in the field.
Data Source is the name of the query that delivers the data for the form (that's why query names have to be unique) Master Field is the field as explained in the queries chapter that will provide the data for a main/subform construction where the form is the main form and an embedded grid is the subform displaying a subset of the data. Form Style lets you select wether the form is displayed as a data grid (table) or a data form (mask). Before you can see any data you have to press the button "Create field list". This will insert subnodes for every field in the specified data source (query).
Then you close the dialog box and are free to add, edit or delete some of the field nodes.
Fields can not exist on their own, only in conjunction with a form, a report or a graphic, Therefor they are created when you press "Add a node" on a selected Form, Report or Graphics node. When you create fields using the "Create field list" button, the fields are already preformatted. When you create fields manually by pressing the "Add a node" button you just get an empty field that you have to fill out manually. You will need to do this when creating Macro buttons
Many of the parameters are already well known such as Display Name, Left, Top, Width, Height, Background and Font properties. Note that not all parameters are used in every conjunction e.g. in a form or a grid versus in a graphics or a report. Nevertheless you should give the parameters proper values. Data Source is again the query where the data come from. This data source must conform with the datasource of the parent node (the form, report or graphics)
Data Field is the field in the selected data source that contains the data for this field. Field Type is the SQL data type of the data field. Field Style is the way the field is displayed in the form or grid. It might be one of the following: Edit which is the usual way to enter data Lookup which presents you a dropdown list in conjunction with the subsequent fields that have to be filled out properly as explained later Button (Macro) to display a button that starts a Macro Picture for presenting a picture in the form (not in a grid) Radio Button for selecting between a few options Embedded Object for embedding a grid inside a form. Alignment is the way the text is aligned inside the field
The following fields are for the field style Lookup only Lookup Source is the data source (query) that delivers the lookup list Result Field is the field that delivers the data for the lookup list.
For every field a label is generated automatically. For data grids only the label text is used, for forms also the geometry (Label Left, Top, Width, Height) is used. Label Text is the text displayed as the field name (or macro button name) Show Label specifies wether the label is visible. Read Only specifies wether the data field is Read only or not
When using a macro, you must first select the field style "Button (Macro)" and then select the macro name, then press the "Data field" button to refresh the list and select the Macro you want to run when the button is pressed. In data grids, macro buttons will appear in the function panel, in forms macro buttons will appear in the position as specified by left, Top, Width, Heigth.
When using an embedded data grid, you must set the Width and Height parameters large enough to display the grid inside.
NOTE:Upon creation, a new field has always the same default geometry. Therefor several fields may lie on top of each other and you may not see them when opening the form � check the geometry first.
Reports are similar to forms except that you can not edit the data and that you can display the data only in a tabular way. As in a form, you specify the Display Name, the geometry and font, select a data source, create the field list, close the form and edit the field nodes as needed. The Report Display Name is used as the Report title in the output. For convenience, you can also specify a header text and a footer text. Inside these texts you can use HTML tags to fancy format the output. The table itself is rendered by the information present in the field descriptions such as background color, font, font size and so on. When you press the open button, you will see the report (which is nothing else than a HTML page) as rendered by the Java default HTML rendering engine (which is not really the best). Therefor you can export the report as a HTML file and view it in any HTML browser. You can also print it from there or also send the file as an email attachment that everybody can open. Most spreadsheet programs will also be able to read and understand the contens of a HTML table.
Diagrams are very similar to forms and reports only that the data are represented in a graphical way as an x/y diagram. The x axis is always the data vector (= the data field) that is first in the underlying data source (the query). Of course only numerical data can be presented in a diagram form but you are flexible to also use text fields if they contain numbers and dots or commas. The parser will try to extract something out of the field that can be interpreted as a nuber. Apart from the standard parameters like Display Name, Left, Top, Width, Height, Background, Font and Data Source there are some new parameters to be entered: Show Lables defines wether to show the field labels in the diagram (you can toggle this option in the graphics window later on) Show Markers defines wether to show tickmarks for every data point (you would like to switch that off when you have a lot of data points) Show Lines defines wether to combine the datapoints with lines (you would switch that off when the x-axis is not sorted because you would get a terrible mess of lines) Show x-Grid defines wether to show subdivisions of the x-axis Show y-Grid defines wether to show subdivisions of the y-axis x-Minimum specifies where to begin the x-axis of the diagram window x-Maximum specifies where to end the diagram window. When you enter 0, it means you want the program to determine the maximum by itself. y-Minimum specifies where to begin the y-axis of the diagram window y-Maximum specifies where to end the y-axis of the diagram window. When you enter 0, it means that you want the program to determine the maximum by itself.
There are also some simple statistics calculated automatically for the dataset as defined in the query. (Not neccesarily equal to what you see in the diagram due to xMin, xMax, yMin, yMax!) You can display the statistics window by pressing the Statistics button. In the window you can copy the data to the system clipboard via Ctrl + C.
Macros are useful when you want to do things with a click. A macro consists of lines of code with predefined funcions that you can select from a list. Every line of code has to be ended with a semicolon (;) Every line of code consists of a macro function. The function parameters are interpreted as text. When you have text that contains spaces (like Report names) you have to include the text in quotation marks e.g. "My Test Report" otherwise the blanks will be stripped away and the program will not find the Report node "MyTestReport". To facilitate entering of node names, you can select the node type and then press the list button to refresh the nodes list. From there you can directly copy / paste the node names as function arguments into the macro text with Ctrl + C and Ctrl + V.
To start a macro you just press the Open object button.
There is one special macro that must be called "AUTOSTART". This macro is automatically started when you open a project file. Such an autostart macro usually consists of the following statements:
HideConnection();
HideTree();
OpenForm(MainMenu);
So when you specify the Session Node as "Auto Connect" the following will happen: When the user opens the project file, the connection is automatically opened and the user logged in. Then the AUTOSTART macro is run and the connection window is hidden, afterwards the Tree is hidden and then the form "Main Menu" is opened that perhaps presents the user a collection of buttons for starting macros (e.g. for opening forms, reports, graphics and so forth).
To make the automation perfect, you can create a script that starts up the program with a given project file. Such a script would look like:
java -classpath driverlocation;. -jar ARjdbManager.jar myproject.jdb where driverlocation is/are the path(s) to your JDBC driver(s)
JdbM is covered by GPL. see http://www.gnu.org for details
Copyright (C) 2002 ff by Andreas Rauch This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation; either version 2 of the License, or (at your option) any later version. This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details. You should have received a copy of the GNU General Public License along with this program; if not, write to the Free Software Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.