:.

FirstSQL 100% Java ORDBMS

1. General FirstSQL/J Frequently Asked Questions

Contents

1. General Questions - Enterprise Server, Professional and Embedded Mobile core.
1.1 What is FirstSQL/J and what is it designed for?
1.2 What are the licensing details for Enterprise Server and Professional?
1.3 How do I get support for Enterprise Server and Professional?
1.4 How will an application be delivered using FirstSQL/J?
1.5 How do I run the recovery bat file from within my java code? This way I can automate the
database recovery if there is an abnormal shutdown, instead of requiring the user to run the file manually?
1.6 How can I get my existing data files into the FirstSQL/J
database?
1.7 What is the overhead or space utilization for FirstSQL/J tables.
1.8 Is it possible to have more than one thread in the same application opening simultaneous read-only connections to the
database?
1.9 Does FirstSQL/J have a trace feature to resolve problems.

2. SQL Questions
2.1 What is SQL and why is it important?
2.2 What level of SQL 92 does FirstSQL/J support?
2.3 Does FirstSQL/J provide any extensions to SQL?
2.4 Is FirstSQL/J Multi-threaded?
2.5 How does FirstSQL/J support Transactions?
2.6 Does FirstSQL/J support 3-valued Logic?
2.7 Does FirstSQL protect against data corruption?
2.8 Is there a good way to implement auto-increment in FirstSQL/J?
2.9 Does FirstSQL/J have BLOB, CLOB and Unicode support?
2.10 We need the ability to store jpegs inside the
database.
2.11 I am having a problem inserting DATE data types.
2.12 Does FirstSQL/J support the Boolean data type?
2.13 Do you require a fixed limit on VARCHAR columns and enforce it? Or can VARCHAR be used without limit? Does that have intrinsic performance drawbacks i.e., in optimizing physical storage allocation.
2.14 Do you still abide by this 31 character limit for table names, and attribute names and other object names? Are these names case-insensitive by default?
2.15 trying to put a single quote into the DB as part of a text field causes an error.  Are there any other characters
that will cause a similar problem?  Is there a way to mask a single quote to make it valid(like \' or something)?
2.16 Is it possible to perform complex relationships (joins) using objects as opposed to SQL statements? If so, what are the drawbacks?

3. JDBC/ODBC Questions
3.1 What is
JDBC?
3.2 How do I use
JDBC in my application?
3.3 Is an ODBC Driver available?

4. Java Questions
4.1 What versions of Java does FirstSQL support?
4.2 What Java interfaces does FirstSQL/J support for EJB?
4.3  What J2EE features does FirstSQL support?

5. What Object Oriented features are there included in FirstSQL/J?
5.1  I am having trouble creating user-defined
database types.  I have updated my sandbox settings to include the package nasa_uddi.  However, when I try to execute the SQL statement: 
CREATE CLASS name FROM 'nasa_uddi.name'

6. Database Configuration and Setup Questions
6.1 Can I configure the footprint size?

7. Performance Questions
7.1 Are any benchmark routines available?
7.2 How does performance compare with other embedded
databases?

8. Feature Questions
8.1 Are there any tools available with FirstSQL/J?
8.2 Any administrative tools (Enterprise Server Edition only)?
8.3 Does FirstSQL engines support double byte data (for
Asia)?
8.4 Are the enterprise and professional systems compatible? If a client needs more bandwidth and migrates from an appliance with the professional engine to a Unix server with the enterprise engine, do we need to change the Java application?
8.5 Does FirstSQL/J support encryption?
8.6 Why does the recovery utility need to be run so often?
8.7 Is there any way that I can open more than one connection (even read-only) in Professional to simultaneously read two separate tables?

9. Miscellaneous
9.1  I am trying to set up a plugin for IntelliJ that will let me make queries to the DB without having to open up ijdbc to check values in the db. The plugin finds the driver fine, but I need to somehow point it to the actual
database location I am using.  Is there an easy way to do this from the url (jdbc:dbcp://local) that I have set up, or does this need to be done through a system property?  If its through the system property, what’s the best way to make the change to the sys property?
9.2 What are the range of values that FirstSQL supports for the datatypes integer and numeric. I'd assume that, for integer, the range is from the Java constant values Integer.MAX_VALUE to Integer.MIN_VALUE.  How about for the numeric data type?
9.3 How are the quotes supposed to look? Single? Double? None?
9.4 I was trying to run our DDL scripts against FirstSQL and it apparently doesn't allow foreign keys or other table constraints to be defined in ALTER TABLE statements.
9.5 Do the classes installed in the
database share static variables?
9.6 How can I expand the sandbox on a linux instance of firstsql?  Can I just copy the
database files from my windows box or is there some way to do it with the Maint.sh command line utility?
9.7 Is it possible to create a USER that has NO CREATE permissions whatsoever?
9.8 Is it possible to write objects directly to the
database without using the NEW keyword?
9.9 How do I execute a file containing multiple sql statements (for setting up a
database). I have been using ijdbcwin to create users, classes, and tables. It would be a lot easier if I could just execute the whole script from a file.
10. Can I create a prepared statement like this "UPDATE job_table SET ? WHERE job_id=2"? Essentially I want to know if I can replace a question mark with any string (something like "name='job1'") or if I have to prepare the statement including the column names affected.  I am trying to have a single java method that is called with the string to put in at the ?. Is this possible?
10.1 I have a composite Java object where one class contains a reference to a second class. Each of these classes are represented in my
database using separate tables. The first table references the second one using a foreign key. My problem is when I wish to load the top level object instance from the table. I am reading the result set to create the top level object and, since the connection is open, I cannot execute a query to load the contained object instance.  This is forcing me to do some messy workarounds, especially when I am loading more than one of the top level objects. Is there any way that I can open more than one connection (even read-only) to simultaneously read the two separate tables?
10.2 I am trying to pull a min value out of a secondary table that includes a job_id column.  Here is the command I have come up with.

SELECT job_id,name,serial_num,(SELECT MIN(time_stamp) AS first_save FROM
master_time_entrys WHERE job_table.job_id = master_time_entrys.job_id)
FROM job_table

My question is does the WHERE portion of the subquery use the job_id that is included as part of the initial section of the command(beside the first SELECT) to match the master_time_entrys.job_id in the subquery?  If it does not, then I would get the min value of the entire master_time_entrys table(which I don't want).  Is this the correct way to do this or is there a better/correct way to get that min value of time_stamp from all rows in the master_time_entrys table where the job_id= the one already called? time_stamp is bigint in the database in case that makes any difference.

======================================================================

1. General Questions

1.1 What is FirstSQL/J and what is it designed for.

FirstSQL/J is designed for use in any type of embedded system, device, or server computing application. FirstSQL/J can be developed and deployed on any general or RTOS computing platform supporting a JVM (Java Virtual Machine).

FirstSQL/J not only brings object persistence and convenient manipulation of objects to object-oriented applications; it delivers the power of objects integrated with a relational database and SQL. Java, as the basis for the object capabilities, ensures the widest availability of services and flexibility for database application developers.

1.2 What are the licensing details.

FirstSQL provides two FirstSQL/J database products; both licensed as a development system per developer. A small runtime fee is charged for commercial application distribution. Unlimited client connections are allowed with the Enterprise Server version.

Professional - for embedded and desktop database applications requiring single-user access.

Enterprise Server - with unlimited user/client access for scalable client/server database applications.

1.3 How do I get support.

In the FirstSQL/J documentation you will find a Quick Start and Tips section that may be useful to get you started. The Enterprise Server version is based on the Professional version and includes some additional documentation specific to its powerful client/server features and administrative tools. Use the documentation from the Professional version with the documentation for Enterprise Server version.

If you have any installation or operation questions please feel free to email our support group;

Support@firstsql.com

Alternatively you may call our technical support line at;

1- 510-232-6800 (Pacific West Coast USA)

1.4 How will applications be delivered using FirstSQL/J?

Only two things are needed to distribute FirstSQL/J Professional-based applications and one additional file with Enterprise Server.

For Professional; - the contents of the 'db' directory (the physical database) and the contents of fsjclasses.jar (the ORDBMS engine and Jdbc Driver).

To prepare the for the distribution database:

+ Make a new install of FirstSQL/J.

+ Remove the user/schema - demo ... connect as 'adm' with IjdbcWin and issue: "DROP USER demo CASCADE';

+ Create the database & schema's for your application (see the doc's on Database Users) and the appropriate tables, indexes, views, privileges and classes in those schema's.

All of this information will be stored in the 'db' subdirectory for this new install. You would jar this up for distribution. The customer install sequence would include unjarring the contents of db into special directory created for that purpose. Your scripts/apps would specify the path to this directory with the database.path= property (either a Java System property or a Jdbc connection property).

As to fsjclasses.jar ... depending on your application specifics, you could:

+ Distribute the jar as is and reference it in the classpath for your scripts/apps, or

+ Combine its contents with your application classes and distribute a combined jar ... and reference that on the classpath.

You may want to include IjdbcWIN so it can be available for the application database.

For Enterprise Server include; - the contents of the 'db' directory (the physical database) and the contents of fsjserver.jar and fsjclient.jar (the ORDBMS engine and Jdbc Driver).

Essentially follow along with Enterprise Server apps as you would as described above for Professional apps.

1.5 How do I run the recovery bat file from within my application java code so it can automate the database recovery if there is an abnormal shutdown, instead of requiring the user to run the file manually?

FirstSQL/J supports the connection property --- recover=yes, to request automatic recovery (if needed). It can be set in the Properties object passed with DriverManager.getConnection(), or it can be appended to the connection URL:

jdbc:dbcp://local;user=demo;recover=yes

However, we strongly recommend that you be careful in using this option. This will cause database corruption if two applications access the same physical database.

We would suggest that you do normal connect, and if it fails, ask the user to confirm override (asking them to check that there is not another application running against the database) before issuing a connect with recover=yes.

The Recovery utility is just a convenience. It's a simple Java app that uses "recover=yes". The source code is available in the distribution.

1.6 How can I get my existing data files into the FirstSQL/J database?

FirstSQL includes a full Import/Export facility available from IjdbcWin, Ijdbc, or via command line.

For bulk inserts, you should use a prepared statement with ? parameters. The SQL command would look like:

INSERT INTO my_table VALUES(?,?,?,?)

You would create the statement with prepareStatement() in java.sql.Connection:

PreparedStatement myload = connection.prepareStatement("INSERT INTO my_table VALUES(?,?,?,?)");

For each row inserted you would use setXXX() calls to set the column values and then call executeUpdate():

myload.setInt(1, ...);
myload.setString(2, ...);
myload.setBigDecimal(3, ...);
myload.setDate(4, ...);
myload.executeUpdate();

For parsing the CSV file, you may take a look at java.io.StreamTokenizer. See the JDBC section in the FirstSQL/J documentation for more information on PreparedStatement.

1.7 What is the overhead or space utilization for FirstSQL tables?

Data for a FirstSQL/J table is stored in one or two files. The main file has a .tab extension. The optional second file has a .dat extension. .tab files are used for storage of fixed-length column data and variable-length column data that doesn't exceed 256 bytes. Variable-length column data greater than 256 bytes is stored in the .dat file. The .tab file consists of a 512 byte header followed by 0 to n fixed-length pages. Pages are used for the storage of table rows and local variable-length data (<= 256 bytes in length). Page sizes start at 512 bytes and go up by a multiples of 4 (512, 2048, 8192, 32768, 131072, 524288, 2097152, 8388608). Above 8MB, arbitrary page sizes are used.

The system automatically assigns the page size for a table, based on the row size and estimated local variable-length data sizes. It picks a value that will use the space in a reasonable manner. For example, a row size of 257 would use a page size of 2048.

The page size for a table is stored in definition_schema.systables in the table_blkl column.

Each page has a 29 byte header. This is followed by a mix of fixed-length rows and local variable-length data. The format of the variable-length data is a 4 byte length followed by the raw data.

The fixed-length row consist of a 1 byte status indicator followed by a set of fixed-length column values. Each column value includes a 1 byte null indicator.

The row size for a table is stored in definition_schema.systables.table_recl.

The size of each column value depends on the data-type (values are excluding the 1 byte null indicator):

  • tinyint -- 1 byte
  • smallint -- 2 bytes
  • int -- 4 bytes
  • bigint -- 8 bytes
  • float -- 4 bytes
  • double -- 8 bytes
  • date -- 4 bytes
  • time -- 4 bytes
  • time with timezone -- 6 bytes
  • timestamp -- 6 bytes
  • timestamp with timezone -- 8 bytes
  • interval:
    • without seconds component -- 2 bytes
    • with seconds component -- 4 bytes
  • char -- 1 byte per character
  • dec -- converted to character (see char)
  • varchar -- 9 bytes plus extra storage as follows:
    • direct (<= 4 bytes) -- stored in basic 9 bytes
    • local (> 4 and <= 256 bytes) -- stored elsewhere in a .tab page with a 4 byte length prefixing the actual data (1 byte per character)
    • external (> 256) bytes -- stored in .dat file
  • serialized objects -- stored in same format as varchar
  • class bytecodes -- stored in same format as varchar.

The number of variable-length columns for a table is stored in definition_schema.systables.table_var_count.

The .dat file is allocated in blocks of 512. It also has a 512 byte header. External variable-length data (> 256 bytes in length) is stored with a 32 byte prefixing the actual data using as many 512 byte blocks as needed.

Indexes are stored in .ndx files. .ndx files use an extension of the .tab format. The various headers are larger, each index entry is a row and a modified form of page size determination is used.

1.8 Is it possible to have more than one thread in the same application opening simultaneous read-only connections to the database?

Currently, the Professional version only allows one connection at a time, however multiple threads can share a connection. They also share the transaction, but that shouldn't be a problem if they are read only.

1.9 Does FirstSQL/J have a trace feature to resolve problems.

Yes - To turn on the trace, you need two additional startup properties -- trace.mode=adm,engine and trace.level=1. Take XA support for and example: Add them along with the xa=on specification for example:

"localhost;xa=on;trace.mode=adm,engine;trace.level=1", or more fully,

ServerStartup "localhost;xa=on;trace.mode=adm,engine;trace.level=1" 8000 firstsql@db

This will cause the server to write the "firstsql.out" file with debugging info.

Startup the server, recreate the failure in the application and then shutdown the server. Now the problem can be detected by looking in the "firstsql.out" file and also the "firstsql.adm" and "firstsql.fail" files, produced by the server.

2. SQL Questions

2.1 What is SQL and why is it important?

SQL stands for Structured Query Language. It has become the standard for manipulating and querying data in a relational database. SQL is widely taught in higher education CS courses. There are several books and online tutorials that teach SQL. See the Contacts/links section for online SQL tutorials, Links, and reference material.

2.2 What level of SQL 92 does FirstSQL/J support?

FirstSQL/J supports the Entry Level of SQL92 with a number of Intermediate Level features. For a description of SQL92 support, see "Standard SQL Support".

2.3 Does FirstSQL/J provide any extensions to SQL?

Yes - Additional extensions in FirstSQL provide query capabilities not possible with other RDBMSs. These features allow FirstSQL to handle the SQL requirements of the most sophisticated applications in a natural and convenient way.

Extended View Definitions
View definitions in FirstSQL may contain GROUP BY clauses and UNION operators. Some systems allow GROUP BY in views but few support UNION. This illustrates the problem with non-orthogonal features. UNION is a powerful tool in dealing with normalized data. Lack of support for
UNION in views forces users to denormalize their data to achieve appropriate functionality.

Outer Joins
Many DBMSs now support outer joins, but this support is limited and sometimes incorrect. FirstSQL provides both Left and Right outer joins. It also supports embedded outer joins on both sides of an outer join.
While ODBC and many DBMSs support embedded outer joins only on the right hand side, FirstSQL allows embedding on the left hand side. This allows a base table to be outer joined to 2 tables separately. For example, a customer table might be outer joined to both the sales order table and the invoice table. This is not possible in other systems except perhaps using a view.

MAYBE Operator
The MAYBE operator allows full control over Three Valued Logic. Queries involving nulls can produce unknown results. Unknown results are detected with the MAYBE operator. In other DBMSs, users must formulate complex queries to detect unknown results.

Two Types of Nulls
FirstSQL is the only system supporting both types of nulls defined by the relational model. Two types of nulls allow more accurate representation and processing of missing information.

Complete Referential Integrity
FirstSQL is a pioneer in providing complete Referential Integrity support in an RDBMS. FirstSQL also fully supports self-referencing and cross-referencing Referential Integrity.

Recursive Queries
FirstSQL/J extends SQL92 with recursive queries, providing transitive closure. Recursive queries can access unbounded link structures, like trees, stored in a table or multiple tables.

2.4 Is FirstSQL/J Multi-threaded?

Yes.

2.5 How does FirstSQL/J support Transactions?

SQL-Transaction Statements control transactions in FirstSQL/J database access. This subset of SQL is also called the Data Control Language for SQL (SQL DCL).  Auto-commit is set to ON by default.

There are 2 SQL-Transaction Statements:
* COMMIT Statement -- commit (make persistent) all changes for the current transaction
* ROLLBACK Statement -- roll back (rescind) all changes for the current transaction

A
database transaction is a larger unit that frames multiple SQL statements. A transaction ensures that the action of the framed statements is atomic with respect to recovery.

A SQL Modification Statement has limited effect. A given statement can only directly modify the contents of a single table (Referential Integrity effects may cause indirect modification of other tables.) The upshot is that operations which require modification of several tables must involve multiple modification statements. A classic example is a bank operation that transfers funds from one type of account to another, requiring updates to 2 tables. Transactions provide a way to group these multiple statements in one atomic unit.
In SQL92, there is no BEGIN TRANSACTION statement. A transaction begins with the execution of a SQL-Data statement when there is no current transaction. All subsequent SQL-Data statements until COMMIT or ROLLBACK become part of the transaction. Execution of a COMMIT Statement or ROLLBACK Statement completes the current transaction. A subsequent SQL-Data statement starts a new transaction. In terms of direct effect on the
database, it is the SQL Modification Statements that are the main consideration since they change data. The total set of changes to the database by the modification statements in a transaction are treated as an atomic unit through the actions of the transaction. The set of changes either:

* Is made fully persistent in the
database through the action of the COMMIT Statement, or
* Has no persistent effect whatever on the
database, through:

* the action of the ROLLBACK Statement,
*abnormal termination of the client requesting the transaction, or
*abnormal termination of the transaction by the DBMS. This may be an action by the system (deadlock resolution) or by an administrative agent, or it may be an abnormal termination of the DBMS itself. In the latter case, the DBMS must roll back any active transactions during recovery.

The DBMS must ensure that the effect of a transaction is not partial. All changes in a transaction must be made persistent, or no changes from the transaction must be made persistent.

FirstSQL/J provides complete transaction processing with row locking and journalling for rollback. Standard transaction isolation levels are supported. During crash recovery, the ORDBMS automatically rolls back uncommitted transactions. DDL commands are not under transaction control.

The 4 levels of transaction isolation are:

  • Serializable -- transactions are fully serialized. Dirty reads, non-repeatable reads and phantoms are not possible.
  • Repeatable Read -- transactions are partially serialized. Dirty reads and non-repeatable reads are not possible. Phantoms are possible.
  • Read Committed -- transactions are partially serialized. Dirty reads are not possible. Non-repeatable reads and phantoms are possible.
  • Read Uncommitted -- transactions are partially serialized. Dirty reads, non-repeatable reads and phantoms are possible.

The default transaction isolation level is Repeatable Read. Each session can change its isolation level using the JDBC method -- setTransactionIsolation() in the Connection object.

2.6 Does FirstSQL/J support 3-valued Logic?

Yes - FirstSQL/J provides 3-valued logic. FirstSQL/J also supports two types of nulls -- a-marked and i-marked, but both types of nulls produce the same logical value - unknown, in logical expressions.

FirstSQL/J implements the relational model sometimes known as Relational Model, Tasmania, defined by Ted Codd in 1979. In his 1979 paper, "Extending the Database Relational Model to Capture More Meaning", Codd specifies two types of null but recommends 3-valued logic.

Because of our advanced implementation, we have the only correct implementation of 3-valued logic among SQL DBMSs. We don't have the SQL EXISTS problem (this can be proved using the sample tables provided with the system.)

2.7 Does FirstSQL protect against data corruption?

With Commit and Rollback statements and referential integrity.

2.8 Is there a good way to implement auto-increment in FirstSQL/J.

FirstSQL/J supports a special Sequence datatype as an auto-increment primary key. An auto-increment column is defined as follows:

    id SEQUENCE PRIMARY KEY
Retrieval of assigned sequence values uses the sql.lastSequence() function or JDBC 3's getGeneratedKeys().

2.9 Does FirstSQL/J have BLOB & CLOB (unicode) support?

Yes, starting in v1.25.

2.10 We need the ability to store jpegs inside the database.

FirstSQL/J includes the Blob class for storing binary objects like jpegs in the database. It implements java.sql.Blob. After the Blob class is added to the database, columns in database tables can use Blob as the datatype.

2.11 I am having a problem inserting DATE data types.

FirstSQL/J supports SQL92 date formats for date literals. See our online tutorial at http://www.firstsql.com/tutor3.htm#literal or the same sub-section in our docs. It is also covered in the syntax appendices of the docs. You can also use the JDBC escape format for date/time literals:

{d 'yyyy-mm-dd'}

For CAST, you need to use the SQL92 basic form for string dates --- 'yyyy-mm-dd', to get the appropriate results ...

CAST('1999-01-01' AS DATE)

2.12 Does FirstSQL/J support the Boolean data type?

We don't currently support BOOLEAN data type. One reason is that it is non-standard ... SQL92 doesn't have a BOOLEAN data type, and we have been concentrating on SQL92 support. However, we are considering support in a future version of FirstSQL/J.

In the interim, you can use integer (or tinyint) and 0/1, or a char field with Y/N or T/F.

2.13 Do you require a fixed limit on VARCHAR columns and enforce it? Or can VARCHAR be used without limit? Does that have intrinsic performance drawbacks (i.e., in optimizing physical storage allocation)

We do enforce a fixed limit on VARCHAR following SQL92. The storage implications are minor.

We store VARCHAR in two ways: 1) if 256 characters or less, it is stored with table data (but not in the row); 2) if greater than 256, we store it separate from the table data. This choice is made each time data is placed in a VARCHAR column. The only storage effect of specifying a limit > 256 in a VARCHAR declaration is that we create the separate storage area (it may not be used).

2.14 Do you still abide by this 31 character limit for table names, and attribute names and other object names? Are these names case-insensitive by default?

We impose the SQL92 limit on names --- 128 characters. The names are always case-insensitive unless quoted. We support SQL92 quoting of names with a double-quote. Quoted names can be reserved words and include special characters.

2.15 Trying to put a single quote into the DB as part of a text field causes an error.  Are there any other characters that will cause a similar problem?  Is there a way to mask a single quote to make it valid(like \' or something)?

If you are talking about a quoted string literal, using single quotes. For a single quote in the literal, you use two single quotes as an escape. For example,  'Don''t go in the woods!' // note this is 2 single quotes not a double quote

which represents the string value: Don't go in the woods!

This is the formulation for Standard SQL. This is described two places in our documentation: in the SQL Tutorial, under "Extended Query Capabilities", "Literals" (tutor3.html#literal) and in Appendix 1: SQL Syntax, under "SQL Basic Elements" (fsjsql5.html), at the bottom of the page.

2.16 Is it possible to perform complex relationships (joins) using objects as opposed to SQL statements? If so, what are the drawbacks?

We only support SQL for joining tables. These tables may contain columns whose type is a Java class (stored in the database with CREATE CLASS). These columns contain Java objects.

For complex relationships, we recommend using joins between tables (or rows in the same table) utilizing SQL capabilities. These can be combined into lattices of Java objects before being returned to the client. See Tips #4 & 5 under "FirstSQL/J Tips" in the User Reference Manual.

3. JDBC/ODBC Questions

3.1 What is JDBC?

JDBCTM is the core JavaTM API that Java applications / applets / servlets use to communicate with a database. The JDBCTM API documentation can be found at http://java.sun.com/products/jdk/1.2/docs/api/java/sql/package-summary.html.

FirstSQL/J provides a Type 4 implementation of the JDBCTM Version 3 API.

3.2 How do I use JDBC in my application?

JDBC (see docs for additional info) supports SQL DML, DCL and DDL, Full Escape Processing -- {d '2002-02-28'}. The Jdbc MetaData Class (catalog access methods) are in the documentation.

Connections to the FirstSQL/J ORDBMS are made through JDBC with java.sql.DriverManager.getConnection(String url, java.util.Properties info). For FirstSQL/J Professional, the url string is jdbc:dbcp://local, establishing a local dialog with FirstSQL/J. The initial "jdbc:" is optional. The info Properties object must contain a property named user whose value is the login user name. Additional properties:
* password= authorization password for user, default is no password
* recover= yes or no, indicates whether automatic recovery is performed, default is no.
*
database.path= physical path to database (overrides system properties), example: database.path=~/firstsql/db.

A basic connection to FirstSQL/J Professional would be:
Class.forName("COM.FirstSQL.Dbcp.DbcpDriver");
java.sql.Connection connect;
java.util.Properties info;
info = new java.util.Properties();
info.put("user", "demo");
connect = java.sql.DriverManager.getConnection("jdbc:dbcp://local",info);

Example Bench1Insert using the command line - connecting to a FirstSQL/J Enterprise Server from a client on the network could use the following;
C>java -cp C:\Firstsql\lib\fsjclient.jar;. Bench1Insert
COM.FirstSQL.Dbcp.DbcpDriver "dbcp://servercomputer:8000;user=demo"

3.3 Is an ODBC Driver Available?

An ODBC Driver is available for Enterprise Server Edition only. Contact FFE Software, Inc. for this driver.

4. Java Questions

4.1 What versions of Java does FirstSQL support?

FirstSQL/J is written in pure Java (no non-java components, except the ODBC Driver). It only uses the basic API from JDK 1.1.x.
FirstSQL supports Java 1 (
JDK 1.1) and Java 2 (JDKs 1.2, 1.3, 1.4).

Our message is this - "If your operating system and platform has a JVM, then FirstSQL/J will run on it."

There are several JVMs for 64 bit processors and for multiple CPU configurations.

4.2 What Java interfaces does FirstSQL/J support for EJB?

FirstSQL supports for JNDI (Java Naming and Directory Interface) and  JCA (J2EE Conector Architecture).

4.3  What J2EE features does FirstSQL support?

Data Source -- support for javax.sql.DataSource and for javax.sql.ConnectionPoolDataSource and javax.sql.XADataSource
JCA Resource Adapter -- support for JCA 1.0 and JCA 1.5
J2EE Deployment and Operation -- Data Source and JCA deployment to J2EE Container Software and Server operation for J2EE
XA -- support for distributed transactions (XA) for both Data Source and JCA

FirstSQL/J Enterprise Server is bundled with Arjuna – Arjuna+JBoss Application Server AND Atomikos -TransactionJTA.

5. What Object Oriented features are there included in FirstSQL/J?

FirstSQL/J supports full object capabilities in the database using Java Classes. The CREATE CLASS command catalogs a Java class into the database. Once cataloged, the Java Class has multiple uses, for:

* User Defined Functions - the static methods of the class can be called as functions in SQL.
* Stored Procedures - the static methods of the class can be called as stored procedures. The class methods have direct access to the current
database connection and can return multiple resultsets.
* Data Wrappers - instances of the class can be used by the client application to pass data to and from the ORDBMS (See Tips 4 and 5 in FirstSQL/J Tips.)
* Column Data Types - table columns can use the class as their data type. Values for the columns in table rows are then instances of the class. SQL statements construct instance values for the column with the NEW operator and utilize the object column values by calling their methods.

Methods called in SQL command can access the database through JDBC.

Operating on Database Objects in SQL allows objects to be instantiated from user-defined database classes. These operations are:
1. assign them to
database columns,
2. call their methods, and
3. pass them to
database methods.

FirstSQL/J provides additional operations on database objects:
*conversion - CAST operator
*type testing - INHERITS operator

FirstSQL/J provides a set of built-in classes used in translating between database values and Java values.

5.1  I am having trouble creating user-defined database types.  I have updated my sandbox settings to include the package nasa_uddi.  However, when I try to execute the SQL statement:
 
CREATE CLASS name FROM 'nasa_uddi.name'
 
This error is returned:
 
Msg 191, Level 16, State 42000
COM.FirstSQL.Dbcp.JdbcException: Semantic error at 23; Unable to load class/interface nasa_uddi.name
 
Note:  I am executing this statement in FirstSQL's IjdbcWin.


Yes, that seems to be a classpath problem. By default, the ORDBMS doesn't use the Java classpath for locating user-defined
database classes. It uses its own special classpath --- the current directory. Database classes must be located in the current directory or, if in a package, in the appropriate subdirectory under the current directory. For example,

  ./nasa_uddi/name.class

To change the default classpath(s) for a physical database, access Database Options using the Maint Utilities.

6. Database Configuration and Setup Questions

6.1 Can I configure the footprint size?

Yes - the foot print is less then 600k but it is dynamic meaning it only brings into ram what is being used. FirstSQL/J takes advantage of the modular nature of Java so classes of functionality can be removed.
The class files listed below can be removed from fsjclasses.jar (Professional) and fsjserver.jar (Enterprise Server). The original fsjclasses.jar file is 541k but by eliminating these classes the size is reduced by 87k to 454k. In addition to these, other classes may be removed but are application specific.
+ DDL, all DDL commands or selective:
- User Maintenance DDL
COM.FirstSQL.DTSystem.ExecDDLa.class
- Privilege Maintenance DDL
COM.FirstSQL.DTSystem.ExecDDLb.class
- Remaining DDL
COM.FirstSQL.DTSystem.ExecDDL.class
COM.FirstSQL.DTSystem.ForceClassLoader.class
COM.FirstSQL.DTSystem.ExecDDLr.class
COM.FirstSQL.DTSystem.ExecDDLs.class
COM.FirstSQL.DTSystem.ExecDDLt.class
COM.FirstSQL.DTSystem.ExecDDLu.class
COM.FirstSQL.DTSystem.ExecDDLx.class
+ User Defined Privilege
Support (uses default privileges)
COM.FirstSQL.DTSystem.DTPrivilege.class
+ Sort Algorithm
COM.FirstSQL.DBSystem.SortNdx.class
COM.FirstSQL.DBSystem.KeyedTag.class
COM.FirstSQL.DBSystem.KeyedRec.class
COM.FirstSQL.DBSystem.SortKeyed.class
COM.FirstSQL.DBSystem.SortKeyed1.class
COM.FirstSQL.DBSystem.SortKeyed2.class
+ Jdbc MetaData Class (catalog access methods)
COM.FirstSQL.DBcp.MetaServer.class
COM.FirstSQL.DBcp.MetaSubs.class

With Embedded Mobile the capability to reduce footprint size has been made easier and more comprehensive.

7. Performance Questions

7.1 Are any benchmark routines available?

Yes - and they are available for download from www.firstsql.com in the download section. We are very interested in hearing about your results so please let us know what you find. IMPORTANT- all benchmarks use PreparedStatements for faster performance.

7.2 How does performance compare with other embedded databases?

Performance test show FirstSQL/J significantly faster in simple insert and select tests with major embedded database competitors (both pure Java and non-Java products) and show similar TPS processing results as insert and select statements get very complex.

8. Feature Questions

8.1 Are there any tools available with FirstSQL/J?

Ijdbc - command line utility for executing SQL and DDL statements and displaying formatted results.
IjdbcWin - GUI utility for executing SQL and DDL statements and displaying results in a grid.
Recovery - command line utility for recovery from
database crashes (Professional Edition only.)
Import/Export

8.2 Any administrative tools?

FirstSQL/J Enterprise provides a full set of tools for operating and administering the FirstSQL/J Server.

Two command line utilities handle server startup and shutdown. The optional GUI Administrator also supports server startup and shutdown.

The system includes an Administrative API for developing administrative tools. The Administrative API supports server startup and shutdown, plus additional administrative facilities:

·         Monitoring server operation and client sessions.

·             Controlling individual sessions:

a.    forcing transaction rollback for a session,

b.    killing (external termination) a session.

·             Controlling deadlock resolution.

·       The optional GUI Administrator also supports additional facilities. The optional GUI Administrative API provides a set of AWT components for creating administrative tools. The GUI Administrator is built from these components.

·        Using these tools, an administrator can perform administration remotely, from a client system. Only startup must be performed on the server machine, locally. Server shutdown and the additional administrative facilities are available remotely. The administrative utilities and tools are available in source form.

·        The tool sections following are:

·             Command Line Tools: Server Startup and Shutdown -- Using the command line tools for server startup and shutdown.

·             GUI Administrator: Server Startup and Shutdown – Using the GUI Administrator for server startup and shutdown.

·             GUI Administrator -- full package of administrative capabilities in one application, including server startup and shutdown and server session control.

·             Monitor -- dynamic charting of server activity, plots a wide variety of elements -- connects, sql statements, open tables, row access, physical page read/write, memory usage.

·             Adm Cmd Utility -- command line utility for general administrative tasks.

·             Adm Options Utility -- application for creating and editting property files for the GUI Administrator.

·             Administrative API -- Java API for creating administrative tools.

·             Maint and MaintGUI – copy, backup, restore, build databases and modify database settings.

8.3 Does FirstSQL engines support double byte data (for Asia)?

FirstSQL/J includes the Clob class for storing Unicode (double-byte) characters in the database. It implements java.sql.Clob. After the Clob class is added to the database, columns in database tables can use Clob as the datatype.

8.4 Are the enterprise and professional systems compatible? If a client needs more bandwidth and migrates from an appliance with the professional engine to a Unix server with the enterprise engine, do we need to change the Java application?

The physical database for the Professional version and the Enterprise Server version is identical. You can use either version to access the same physical database (just not simultaneously!)

The only difference for client applications is in the Jdbc connection URL. For the Professional version, you use:

jdbc:dbcp://local

For the Enterprise Server version, 'local' is replaced by the hostname and port #:

jdbc:dbcp://host-name:port-number

Otherwise, there is no difference in Jdbc access to the ORDBMS, and no other changes are required to the application.

8.5 Does FirstSQL/J support encryption?

Not directly. FirstSQL/J does encrypt some important catalog information such as passwords and privileges and when sending these over the wire.

We have looked into the JCE option a while ago and felt it wasn't ready for prime-time and it didn't seem there was a reference platform available. Encryption in the database is important to us and we are going to look into JCE further now.

See Tip #6 in the docs. A similar work-around could be used to do your own encryption.

 

8.6 Why does the recovery utility need to be run so often?

It doesn’t. Your application is not shutting the server down properly thus requiring a recovery. FirstSQL/J Professional is an embedded DBMS that runs on the same JVM as your application. In order to shut the database access down properly, it must be notified by the application. The application does this by closing the connection --- java.sql.Connection.close(), If the application fails to close the connection properly, recovery is required.

We changed the shutdown problem in v2.10.

8.7 Is there any way that I can open more than one connection (even read-only) in Profesional to simultaneously read two separate tables?

With the Professional Edition, you can have only one (multi-threaded) connection. However, you can have multiple active statements. Use two statements for the two queries. You can have more than one ResultSet active as long as you make each java.sql.ResultSet object with a separate java.sql.Statement object.

You can append the database location to the connection url using database.path=. For example:

jdbc:dbcp://local;database.path=c:/my-database/db

You can also set database.path= as a system property. Note: database.path= in the connection url overrides any database.path= system property.

9. Miscellaneous

9.1  I am trying to set up a plugin for IntelliJ that will let me make queries to the DB without having to open up ijdbc to check values in the db. The plugin finds the driver fine, but I need to somehow point it to the actual database location I am using.  Is there an easy way to do this from the url (jdbc:dbcp://local) that I have set up, or does this need to be done through a system property?  If its through the system property, what’s the best way to make the change to the sys property?

The connection property -- database.path=, specifies the path to the actual database location. It can be added to the connection url:

    jdbc:dbcp://local;database.path=/MyApp/db

9.2 Yes, the integer values are stored in binary so the limits are:

TINYINT -- Byte.MAX_VALUE to Byte.MIN_VALUE
SMALLINT -- Short.MAX_VALUE to Short.MIN_VALUE
INT -- Integer.MAX_VALUE to Integer.MIN_VALUE
BIGINT -- Long.MAX_VALUE to Long.MIN_VALUE

The NUMERIC (or DECIMAL) data type doesn't have specific limit. Internally, BigDecimal is used. BigDecimal also doesn't have specific limit except maximum array size. The practical limit is probably about 32k decimal digits.

9.3 How are the quotes supposed to look? Single? Double? None?

We support straight SQL92. Single quotes are used to enclose character strings (and hexadecimal literals for COM.FirstSQL.Blob). Double quotes are used for identifiers --- names.

9.4 I was trying to run our DDL scripts against FirstSQL and it apparently doesn't allow foreign keys or other table constraints to be defined in ALTER TABLE statements.

Version 3.00 provides full support for adding and dropping table (and column) constraints using ALTER TABLE

9.5 Do the classes installed in the database share static variables?

Static variables in database classes can only be shared within a session (connection). This is to prevent inappropriate cross-talk between sessions. The only appropriate way for sessions inside the DBMS to communicate is using the database and transactions.

9.6 How can I expand the sandbox on a linux instance of firstsql?  Can I just copy the database files from my windows box or is there some way to do it with the Maint.sh command line utility?

You can do it either way. The files in the physical database (db directory) are compatible across operating platforms (Note: we include a full physical database in the install jar that was built in Windows but works everywhere). We provide command-line versions of all our GUI utilities (see the end of the readme.txt), though the command-line versions can be a little harder to use.

Be that as it may, it would probably be more convenient to do it directly on Linux. As you guessed, Maint.sh is the way to go. With the command-line Maint, you use the set command. Here's a step-by-step:

+ First, you need to create a properties file with a single property  ---- sandbox=. This property has a special format for specifying the sandbox expansion, described in the "Sandbox Settings Format" subsection of the Maint section (maint.html). For example, you might use:

sandbox=java.awt(Point,Color)

or simply,

sandbox=java.awt

+ Then start Maint.sh to run the commands.

+ If the physical database is not already in Maint's database list, you need to add it to the list, for example:

add file:db

+ Use the pick command to pick the database from the list, either by number or by a name you give it:

pick 1

+ Finally, use the set options command to expand the sandbox by referencing the properties file:

set options properties-file-name

+ Note: you can use the options command to check that the sandbox is indeed expanded.

9.7 Is it possible to create a USER that has NO CREATE permissions whatsoever?

Yes - Here's a sequence:

+ build a database with 2 catalogs, say --- catalog1, catalog2.

+ start the server referencing catalog1 only:

ServerStartup localhost 8000 catalog1@db

+ create your user with "FOR catalog2", say user2 as the name.

+ Now, user2 can not connect with this URL:

jdbc:dbcp://:8000;user=user2;catalog=catalog2

Thus, user2 cannot log into the user2 schema, and can't do CREATEs, DROPs etc..

+ However, assuming the user1 schema is in catalog1, user2 can connect with this URL:

  jdbc:dbcp://:8000;user=user2;schema=user1

BTW, this only works for the Enterprise Edition. There is no way to accomplish this with the Professional Edition.

9.8 Is it possible to write objects directly to the database without using the NEW keyword?

Yes, you definitely can.

9.9 How do I execute a file containing multiple sql statements (for setting up a database). I have been using ijdbcwin to create users, classes, and tables. It would be a lot easier if I could just execute the whole script from a file.

Yes, the normal way to execute multiple sql statements in IjdbcWin (and Ijdbc) is to follow each statement with a separate line containing "go". Note: Ijdbc was originally a knock-off of Sql Server's Isql utility which used this technique.

FirstSQL/J will also process multiple statements optionally separated by semi-colons. Because SQL is a 'leading' keyword language, there is no ambiguity in concatenating statements. For example:

select*from p select*from s select*from sp

... is legal, however the use of "go" is preferred for clarity.

Both IjdbcWin and Ijdbc can read scripts of sql statements from files.

BTW, IjdbcWin displays the results of each statement (whether concatenated or separated by "go") in separate panes which you can page through.

10.0 Can I create a prepared statement like this "UPDATE job_table SET ? WHERE job_id=2"?  Essentially I want to know if I can replace a question mark with any string (something like "name='job1'") or if I have to prepare the statement including the column names affected.  I am trying to have a single java method that is called with the string to put in at the ?. Is this possible?

Sorry, ? parameters can only be used where a data value (such as a literal) can be used. In the SET clause, ? parameters can only be used in the expression following the '='. The reason is that the optimizer uses the information about which columns are updated to determine which indexes need to be updated and to determine what referential integrity (foreign key references) constraints must be checked. This is standard SQL processing.

10.1 I have a composite Java object where one class contains a reference to a second class. Each of these classes are represented in my database using separate tables. The first table references the second one using a foreign key. My problem is when I wish to load the top level object instance from the table. I am reading the result set to create the top level object and, since the connection is open, I cannot execute a query to load the contained object instance.  This is forcing me to do some messy workarounds, especially when I am loading more than one of the top level objects.

Is there any way that I can open more than one connection (even read-only) to simultaneously read the two separate tables?

With the Professional Edition, you can have only one connection. However, you can have multiple active statements. Use two statements for the two queries. You can have more than one ResultSet active as long as you make each java.sql.ResultSet object with a separate java.sql.Statement object.

10.2 I am trying to pull a min value out of a secondary table that includes a job_id column.  Here is the command I have come up with.

SELECT job_id,name,serial_num,(SELECT MIN(time_stamp) AS first_save FROM master_time_entrys WHERE job_table.job_id = master_time_entrys.job_id) FROM job_table

My question is does the WHERE portion of the subquery use the job_id that is included as part of the initial section of the command(beside the first SELECT) to match the master_time_entrys.job_id in the subquery?  If it does not, then I would get the min value of the entire master_time_entrys table(which I don't want).  Is this the correct way to do this or is there a better/correct way to get that min value of time_stamp from all rows in the master_time_entrys table where the job_id= the one already called? time_stamp is bigint in the database in case that makes any difference.

In a SQL query, the FROM clause is conceptually executed first (followed by the WHERE clause, the GROUP BY clause and the HAVING clause) and then the SELECT clause is executed. So, the SELECT clause (or subquery within the SELECT clause) can reference columns & tables from the FROM clause, except when the GROUP BY clause is included. When you use GROUP BY, only the grouped columns can be referenced directly (other columns from the FROM clause must be referenced in SET Functions).


Copyright FFE Software, Inc. 2002-2006

FirstSQL is a registered trademark and FirstSQL/J is a trademark of FFE Software, Inc. Java and Java-based trademarks are trademarks or registered trademarks of Sun Microsystems, Inc. Other names used in this release may or may not be the trademarks or registered trademarks of their respective owners.