Assumptions and Database Differences
[Previous] [Main] [Next]

Assumptions and Database Differences

Every system is based on some assumptions and JGuiGen is no different. Here is a partial list of these assumptions. See the README.HTML file for more information about this topic.

·Each table to be edited has a one column primary key field which is an auto-incremented integer field. It is possible to manually override this assumption, but it will require some hand coding by the developer.  
·JGuiGen assumes that the username and password to supply for attaching to your SQL backend will default to the database within that backend that you want your users to use.  
·Tables really should include a column that JGuiGen will use to insert a date/time stamp every time the row is updated. It is also advisable to have a char column that will be used to indicate who updated the row. These are used be several routines (if they are available) to check for multi-user conflicts and to determine the ID number of a row that was just inserted (where the ID is created by the database using the auto-increment function.)  
·The rowlayout manager provided can create the screen you need. Again, you can override this layout manager, but it will require some hand coding by the developer.  
·Each JPanel is created to edit one table in your database. The code generated is designed so that you can merge two or more generated panels into one panel, but it definitely requires cut and paste and hand coding by the developer.  
·Each JPanel is designed to be called by some master program. JGuiGen comes with a default main program (a menu, a title, and a big graphic), but you must manually put the calls for the generated panels into this file. JGuiGen generated panels can be set up so that they are passed an ID number and they edit one row (e.g. one person or one product) or they can have a scrolling table of rows and can be used to add, edit, delete, report, review, select, etc. In the first case all you would have is Exit, Save, Help, and Print buttons. In the latter case you would have many more options.  
·There are options for the placement of the menu buttons, the print screen button and the other buttons automatically provided by JGuiGen. Again, it is possible to remove these features from the generated code, but it must be done manually.  
·Larger database application will always have places where they must access multiple tables using Joins. JGuiGen does NOT generate this type of application. On the other hand it does create applications where all of the variables names are unique. This was done so that several different one-table applications could be manually merged to create a multi-table application. It is still easier than writing the multi-table application by hand.  
· Here are the SQL data types and those handled and not handled by JGuiGen  


Treated as INTEGER  
public static final int BIGINT -5  
public static final int INTEGER 4  
public static final int TINYINT -6  
public static final int SMALLINT 5  
Treated as BOOL (in some databases there are no BOOLs, they use ints and tinyints instead)  
public static final int BIT -7  
public static final int BOOLEAN 16  
Treated as STRING  
public static final int CHAR 1  
public static final int LONGVARCHAR -1  
public static final int VARCHAR 12  
Treadted as DOUBLE  
public static final int DECIMAL 3  
public static final int DOUBLE 8  
public static final int FLOAT 6  
public static final int NUMERIC 2  
public static final int REAL 7  
Treated as DATE  
public static final int DATE 91  
Treated as TIME  
public static final int TIME 92  
Treated as DATETIME  
public static final int TIMESTAMP 93  
public static final int ARRAY 2003  
public static final int BINARY -2  
public static final int BLOB 2004  
public static final int CLOB 2005  
public static final int DATALINK 70  
public static final int DISTINCT 2001  
public static final int VARBINARY -3  
public static final int JAVA_OBJECT 2000  
public static final int LONGVARBINARY -4  
public static final int NULL 0  
public static final int OTHER 1111  
public static final int REF 2006  
public static final int STRUCT 2002  
Potential difference between different backend SQL databases.
·The most obvious is obtaining the most recent ID number created by inserting a record that auto-increments the field containing the ID number. For example, PostgreSQL has a function you can call before you insert the row. The function auto-increments the value in the column and determines the new value. You then manually insert this value into the column name when you insert the new row. MySQL, on the other hand, has a function you can call just after you inserted a row. This function will tell you the value your new row contains (even though other processes may have added more rows after you added yours.) MS Access will not allow us to add a row with a predetrimed ID number. It insists on using its auto-increment feature. JDBC 3 will handle this problem, but not all databases have JDBC 3 drivers yet.  
·The queryTable() method generates an UPPER(TRIM(<column_name>)) LIKE UPPER(TRIM(<value>%)) WHERE clause. Backends like MS Access do not recognize the SQL UPPER command. Part of the problem is that databases like PostgreSQL and HSQL are case sensitve by default and databases like MS SQL Server and MS Access are case insensitive by default. HSQL also will not allow the TRIM comment, it uses RTRIM and LTRIM.  
·JGuiGen enters all dates using the SQL functions {d 'yyyy-MM-dd'} and {ts 'yyyy-MM-dd HH:mm:ss'}. It is possible that some databases have not implemented these functions. There may also be problems with what timezone the dates are saved and retreived in.  
·MS Access LONGVARCHAR returns a column length of over 1,000,000. I can't substring the field to make sure it is not too big for these fields.  
·MS Access and Oracle returns 1 and 0 for true and false for boolean columns. They don't have a boolean type.  
·Postgres returns a -1 for the field length of numrmic columns and returns a SQL Type of 12 for Text fields instead of the -1 for longvarchar fields that SQL specifies.  

·HSQL returns a 0 length for VARCHAR columns. It also does not have text columns, we use LONGVARCHAR instead. The version of HSQL used here by default is the standalone version. This can only be accessed by one application at a time.  
·MS - SQL SERVER calls a special column that contains the date/time when a row was created a "timestamp". Everyone else (and the SQL specs) call a date/time column you can enter data into a timestamp. MS - SQL SERVER also does not have a date data type. You alwas get date/time.  
The readme.html file has an extensive section on the features that are unique to each back end database that JGuiGen supports.