Java Glossary : SQL

Last updated 2004-06-28 by Roedy Green ©1996-2004 Canadian Mind Products

Java definitions: 0-9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z

You are here : home : Java Glossary : S words : SQL.

SQL

SQL stands for Standard Query Language, a platform independent relational database query language. It is accessed via JDBC (Java Data Base Connectivity) in Java. SQL and relational databases were pioneered by Codd and C.J. Date.

The Vendor Lists

I have collected three vendor lists:

1. JDBC driver vendors.
2. SQL engine vendors including prices.
3. Java-friendly ISPs.

Alternatives to SQL

You may vaguely sense you need a data base and so reflexively reach for SQL. SQL is a fairly big hammer. Perhaps something simpler and lighter weight will suffice.

Alternatives To SQL
Technique	Advantages	Disadvantages	Description
Serialisied Collections	Very fast. Easy to program.	No protection from crashing. Not scaleable to large datasets.	Totally RAM resident HashMaps, TreeSets etc, serialised when the prgram is not running. Collections ¤ serialization
Serialisied Objects	Very fast. Easy to program.	No protection from crashing. Very wasteful of disk space. No caching. Inefficient use of operating system cache.	You serialise your objects to a ByteArrayStream. Let us say m bytes is the worst case longest serialised object. You divide your RandomAccessFile into n slots each m bytes. You write your serialised object in slot s, computing the offset to seek to as s * m; The File I/O Amanuensis will show you how how to serialise/write and read/reconstitute objects to the RandomAccessFile. This of course wastes space for Objects shorter than m, and does not allow objects to grow larger than m bytes. The Hermit Crab file described next avoids those problems. FileIO Amanuensis serialization
Hermit Crab Files	Very fast. Easy to install. Code is compact. No caching other that what the operating system does.	Limited protection from crashing. Provides only lookup of variable length records by integer key. Indexes must be done with ordinary HashMaps etc. Can provide extremly fast access to very large databases. You have to hire someone to write the code, e.g. me.	hermit crab
POD (Persistent Object Database)	Flexible, may offer crash protection, coding similar to working with Serialised collections	Expensive. Tend to be slow. Though some claim to be much faster than SQL databases. Ideally the entire database floats into RAM and stays there.	Object/Relational Bridge ¤ POD
SQL	The SQL engine intelligently manages indexes to do searches. It is not the programmer's responsibility.	Clumsy to code and install because it is platform specific and runs as a separate process. Can be very expensive for high end engines.	relational database. Programs only see parts of the database they are authorised to see.

The Basics

To get started, find the sames of the databases. From there you can discover the tables in one of those databases, and then names of the columns of that table.

SHOW DATABASES; -- examine list of supported databases
USE mydata; -- select mydata database
SHOW TABLES; -- examine tables in mydata database
DESCRIBE animals; -- look at column descriptions in the animals table

To delete a database or table so that it can be recreated use:

DROP TABLE animals;
DROP DATABASE mydata;

You will need high privilege to do that.

In SQL you request sets of records with statements like this to show just the name, city and state of people in Massachusetts. DESC requests descending order.

SELECT last_name, first_name, city, state
FROM contacts
WHERE state = 'MA'
ORDER BY last_name DESC;

You can limit the number of results returned, though the syntax is non-standard. This is MySQL syntax.

SELECT confirm, ordertimestamp
FROM orders
WHERE confirm < 2000 AND vendorid = 1234
ORDER BY confirm DESC
LIMIT 1

Will find the previous record to confirm number 2000.

There is some slick syntax like BETWEEN and IN writing terser WHERE clauses. LIKE 'Mc'; gives you wildcard matching. You can also summarise data with queries like this to get the count of people in each state (not bothering with states with one or fewer people.)

SELECT state, count(*)
FROM contacts
WHERE age > 18
GROUP BY state
HAVING COUNT(*) > 1
ORDER BY state;

To change individual fields is a bit tedious. You must compose ASCII sentences. You can't just hand over the modified record in binary. You must tell it precisely which fields changed and how to find the record that needs changing again.

UPDATE contacts
SET last_name='Brown', state='WA'
WHERE acct=2103 AND state='MA';

By adding AND state="MA" you ensure no recent changes have been made by someone else. The syntax for adding new records is quite different from that for updating. If you left off the WHERE clause, every record in the table would be updated! To insert a new record you need something like this:

INSERT INTO contacts(last_name, first_name, city, state)
VALUES('Brown','James','Seattle', 'WA');

With INSERT, you have to supply all the must enter fields. For bulk insertions, there is the LOAD TABLE command that accepts a file of comma and apostrophe delimited data.

LOAD INTO TABLE contacts FROM 'C:\temp\contacts.txt';

Delete is straightforward. Be careful. If you forget the WHERE clause, every record in the table will be deleted!

DELETE FROM contacts
WHERE acct=2103;

Most of the time you reuse PreparedStatement, filling in different data values for each use.

view

How do you get results back from a query into your variables? This is not so easy. You might think SQL would hand you an an Iterator of Objects populated by fields named after the columns. No such luck. It is quite a production, with JDBC method calls for each field. You will have to pore over the JDBC documentation. You need code roughly like this:

String employeeName = result.getString( "EmpName" );
int employeeNumber = result.getInt( "EmpNum" );

SQL looks quite simple, but is suprisingly powerful. It will let you look up by fields which are not indexed. It will let you change the primary key in a record. It will let you change individual fields in a record without disturbing the others. SQL has its own procedural language to write triggers, code that is automatically run before or after various database events.

SQL tries hard to avoid transporting data to and from the server. Instead of fetching records for you to look at at the client, you send a command to the server to do what you want and return just the summarised information.

Don't be timid about creating huge result sets then only using part of them. Most database engines are quite clever, and only transmit a hundred records at at time of the result set. This buffering is completely transparent to your application.

SQL uses quite different string literal conventions from Java. Strings are surrounded in ( ') not ( "). Embedded ( ') are written ( '') [two single quotes in a row] not ( ") not ( \') and embedded ( ") are left plain as ( "). These conventions also apply to data imported into SQL as comma-delimited Strings. It gets really hairy creating string literals in Java to be fed to SQL since you have two layers of quoting. First you compose the string to get it right for SQL, then you apply the Java quoting conventions. You also have to be aware of the SQL quoting conventions when you dynamically compose SQL statements in Java or when you feed data to SQL from Java. None of this would be necessary if SQL had a method interface instead of an ASCII sentence interface.

SQL uses = both for assignment and comparison unlike Java with uses = for assignment and == for comparison. If you load your triggers individually they work. If you try to load them in batches, SQL gets confused about terminating semicolons. You can view your triggers with:

SELECT * FROM SYS.SYSTRIGGERS;

SQL uses CASE/WHEN/ELSE instead of SWITCH/CASE/DEFAULT. Its these little differences that often trip you up and leave you scratching your head. It is missing features you would expect such as the ability to traverse forward and back in result sets.

LIMIT row_count lets you limit the size of a result set. Unfortunately, this is not standard in all SQLs. Your vendor may do it a different way. SQL-2003 is the most standardised of all the variants. Users are refusing to put up with proprietary extensions. There is now wide choice.

JDBC stands for Java Database Connectivity. Sun's official position is that it does not, although that is the generally accepted assumption. It describes a list of methods a Java programmer can use to access an SQL relational database. JDBC is similar to Microsoft's ODBC (Open Data baseConnectivity) interface to SQL databases.

Escapes

One problem with SQL is its age. There are many divergent dialects, and the standards allow for a lot of slop. It can take more time to move an app from one SQL engine to another than to write it in the first place. The problem is you start to expect things to work a give way the second time around. The first time, you test everything incrementally. JDBC tries to restore order to at least date/time literals by inventing its own, that it guarantees to convert into whatever form your particular SQL engine likes. e.g.

{d 'yyyy-mm-dd'}
-- e.g.
WHERE arrivalDate < {d '2002-12-31'}

{t 'hh:mm:ss'}
-- e.g.
WHERE arrivalTime < {t '23:59:59'}

{ts 'yyyy-mm-dd hh:mm:ss'}
-- e.g.
WHERE arrivalTimestamp < {ts '2002-12-31 23:59:59'}

the documenation is vague on which timezone is implied. I strongly suggest storing all database information in GMT.

Atomic Updates

Imagine what would happen if you updated a bank balance in two transactions, one to discover the balance, and one to set the new balance adding on the deposit. If someone else were doing the same thing, interleaved with you, you would get the wrong balance. Similarly if you had two threads trying to track the highest value so far interleaved could get the lesser of the two values finally stored in the database. The easiest way around this is to get SQL to do such updates in one atomic operation done all of a piece such as this:

-- incrementing a field in one atomic operation
UPDATE bankAccount SET balance = balance+? WHERE accountNumber=?

-- setting a field to the highest value so far, all in one atomic operation
UPDATE vendors SET highestConfirm=GREATEST(?,highestConfirm) WHERE vendorId=?

Security

Typically you have users and programs coming at your database from all over the web, talking on sockets directly to your database engine. Users don't login first to your OS. This means that the SQL engine has to manage its own completely separate security system.

By entering GRANT commands into your database, you control who can access which tables from where with which passwords. You can separately control read and write access.

Under The Hood

Conceptually you compose an ASCII English-like sentence query and send it off to the SQL engine. The SQL engine goes through all its records the relevant tables one by one finding the ones that fit your criteria. It puts these in a separate file called a result set. You then process the rows/records in it one by one.

What actually happens is much more efficient and clever. When I first used the Sybase SQL engine I could not believe how fast it was compared with the Btrieve DOS files and ISAM I was used to. What makes it so fast?

First, it does not actually wade through all the records in each relevant table looking for matches. It has indexes. It uses those indexes to narrow down the search to likely candidates. Clever SQL engines even create new indexes on the fly without being asked to help them process queries faster.

Next, SQL engines cache as much of the database as they can in RAM. Sometimes databases are totally RAM-resident. This is quite feasible now-a-days with RAM as cheap as it is.

Next the SQL engine does not actually fetch the entire result set. It just grabs a decent sized chunk of it, say 15 rows worth and hands that to you in a chunk. When you have processed that, it gets you the next lump, or it may get the next chunk ready while you are processing the first chunk. This is why you can get away with creating giant result sets then using just the first few rows of them.

You might think the way you update a row is to submit a C-like binary struct representing it and that, to fetch a row, you would get such a beast back. Oddly, it does not work that way. Baud knows why. Instead you compose ASCII sentences to update fields. I kid you not. You have to painstakingly compose things like this:

UPDATE contacts
SET last_name='Brown', state='WA'
WHERE acct=2103 AND state='MA';

ResultSets

To get a row back you make a series of method calls, roughly one per field. The exact format of what the SQL engine sends back is thus invisible to the programmer. The JDBC method calls are ghastly code only their mother could love.
view
The JDBC interface is not really designed for direct human use. We desperately need, but do not yet have, a layer to shield you from all the administrative details. Rows should just be a group of smart self-validating objects that automatically refresh the screen and inform the database of changes when their values are changed either by keying or computation. This is the way Abundance works.

O-R Tools

O/R Object relational tools help you map between objects and SQL relational databases. In most cases code generators take the database schema (con.getMetaData()) and create corresponding java classes, for example one data class and one manager class per table. Some OR tools (TopLink, Cocobase, etc) create both java classes and database schema from single xml master file.

List of O-R tools

SQL Engine Vendors

In most cases the server price shown is for NT. The seat cost is usually for Windows-95 clients. Seats typically come in bundles of 5, 10 or 20. The per seat prices are calculated on the size of the smallest bundle. Seat prices usually decline with volume. Prices are approximate street prices in US dollars. Java Development Magazine also maintains a list of SQL vendors.

VENDORS: Please help fill in the blanks in this table or report any errors via email.

SQL Vendor	SQL DBMS Server Product	Version	Server Platforms	SQL Server Software Price	Seats Included with Server	Additional Seat Software Price
Agave Software Design	JDBC Netserver	1.2	Solaris, NT, HPUX	$2995.00 USD	unlimited	free
Allaire	Cold Fusion	4.0	?	$495.00 USD	unlimited	free
Ardent (née Vmark)	Datastage (née UniVerse)	9.3.1	NT, over 70 Unix versions	$375.00 USD	1	$375.00 USD
Birdstep (née Velocis, née Raima, née MBrane, née Centura, née Gupta)	SQLBase	6	?	?	?	?
Cloudscape	Developer ORDBMS	1.5	pure Java, embedded.	$895.00 USD	1	n/a
	Embeddable Light Server ORDBMS	1.5	pure Java, embedded.	$4895.00 USD	unlimited	free
	Lite Server ORDBMS	1.5	pure Java, client server.	$6395.00 USD	unlimited	free
Compaq (née DEC Digital Equipment)	RDB DBI	?	Alpha	?	?	?
Computer Associates	Ingres	6.4	?	?	?	?
Connect Software	FastForward	?	?	$2995.00 USD	unlimited	free
Daffodil Software	Daffodil DB	3.2	pure Java, SQL-99 compliant, JDBC 3.0, J2EE compiliant.	Prices are now secret. They used to be $485.00 USD	5	$60.00 USD per connection
Empress	Personal Empress	?	AIX, HP, Irix, Linux PC Solaris, SCO Open Server, Sun Solaris	$149.00 USD	1	n/a
	Empress RDBMS	8.1.0	AIX, DEC OSF, DEC Unix, HP, Irix, Linux, Lynx, Max, NT, QNX, SCO Open Desktop, SCO OpenServer, Solaris, Sun OS, Sys V, Unicos, Unixware, Win3.1, Win95	?	?	?
FirstSQL	FirstSQL SQL-92	1.01	pure Java. Also a persistent object database.	$1195 USD per enterprise developer seat. $595 USD per embedded mobile developer seat. pricing details	unlimited	free
Firebird	Firebird SQL-92	1.5	C++, originally a Borland project	free	unlimited	free
hsqlb	hsqlb née Hypersonic SQL	1.61	pure Java. The author, Thomas Mueller decided to stop developing. A sourceforge group has taken over. Open Source. Embeddable. It runs in three modes, as a webserver, as a local server, and as part of the app's JVM.	free	unlimited	free
Hughes Technologies	Mini SQL	2.0.4.1	Unix	free	unlimited	free
I-Kinetics	OPENjdbc	?	pure Java	$1995.00 USD	10	$22.00 USD
IBM	DB2	2	AIX, NT, OS/2, Win95	$1400.00 USD	?	$350.00 USD
IDS Software	IDS Server	3.2.1	NT	$1495.00 USD	unlimited	free
	IDS Server Lite	2.0	NT	$995.00 USD	unlimited	free
Inprise (née Borland)	Data Gateway	?	?	$1495.00 USD	unlimited	free
	dBase	5.5	DOS, NT, Win3.1, Solaris, Win95, Unix	$350.00 USD	1	$250.00 USD
	Paradox	7	DOS, NT, Win3.1, Win95	$1500.00 USD	1	$250.00 USD
	Delphi Client Server	2.0	NT, Win95	$2000.00 USD	unlimited?	free
	Local Interbase	4	NT Win95	$250.00 USD	1	n/a
	Interbase	4	NLM, NT, SCO, Win95	$850.00 USD	5	$40.00 USD
Informix	Informix-SE general purpose limited support	7.2	NT, Unix, Win-95	?	?	?
	Informix-OnLine Workstation	?	NT	?	?	?
	Informix-OnLine/Secure Dynamic Server muli-level secure	?	?	?	?	?
	Informix-Illustra Server ODBMS	?	?	?	?	?
	Informix-OnLine Dynamic Server general purpose high availability, high performance RDBMS	?	?	?	?	?
	Informix-Universal Server ORDBMS	?	?	?	?	?
	Informix-Extended Parallel Server data warehousing MPP	?	?	?	?	?
Intersoft	Essentia	?	?	?	?	?
JB Development (unfortunately out of business)	Harmonia Lite	1.0	anything that supports Java	$100.00 USD	1	n/a
	Harmonia Pro	1.0	anything that supports Java	$250.00 USD	1	n/a
Brian Jepson	tinySQL	?	?	negotiable	1	n/a
Just Logic	Just Logic/SQL	?	BSDI Unix, Linux, NT, SCO OpenServer, SCO Unixware, Win3.1, Win95	$300.00 USD	unlimited	free
Lutris	InstantDB	3.25	pure Java	free for non-commercial and non-governmental use	unlimited	free, unlimited commercial distribution licence $1399.00 USD
McKoi	McKoi	1.0.2	pure Java	opensource	unlimited	free
Microsoft	Access	2.0	NT, Win95	n/a	n/a	$300.00 USD
	MS SQL Server	7.0	NT	$500.00 USD	?	$140.00 USD
NASCE Northwest Alliance for Computational Science and Engineering	HyperSQL	2.1	Sybase and Oracle	free	unlimited	free
Mimer	Mimer	8.2	Windows 98/NT/2000, Linux, Sun Solaris and OpenVMS. Supports SQL99 INSTEAD-OF triggers.	Free developer download	?	They won't tell, you must ask for a quote.
MySQLB	MySQL	3.23.49a	win32(95,98,NT,W2K,XP), BSD, Linux, Solaris Sparc, Solaris i386, SunOS Sparc, source available for any platform with Posix Threads and a C++ compiler.	free	unlimited	free. MySQL gotchas. I have written a comparison with PostgreSQL.
NCR	Teradata	2/2	NCR Unix WorldMark	secret	unlimited	free
NCSA	Decibel	0.5a4	pure Java	free	?	?
Oracle	Personal Oracle Lite	10g	Mac, NT, OS/2, Win98	$295.00 USD with free trial	1	n/a
	Personal Oracle	10g	NT, OS/2, Win98, (Mac support dropped after 7.1)	$395.00 USD	1	n/a
	Oracle Standard Edition	10g	HP MPE/XL, ICL, MVS, NLM, NT, OS/2, Siemens, 40+ Unix, VM, Win95, (Mac support dropped after 7.1)	free	0	$200.00 USD
	Oracle Enterprise Edition	10g	HP MPE/XL, ICL, MacIntosh, MVS, NLM, NT, OS/2, Siemens, 40+ Unix, VM, Win95	free	0	$750.00 USD
Pervasive (née Btrieve)	Scaleable SQL concurrent users	7.0	clients DOS, Win3.1, Win95 servers NLM, NT	$595.00 USD	5	$80.00 USD
Pick	D3 Database Server	?	Not SQL. AIX, DGUX, HPUX, Linux, NT, SCO, Win95	?	?	?
PointBase	PointBase JDJ	3.2	Pure Java.	$1295.00 USD	10	?
PostgreSQL	PostgreSQL	6.4	Linux, various Unixes	free	unlimited	free. I have written a comparison with MySQL.
Progress	Progress	8.3	DOS, NLM, NT, OpenVMS, every major Unix, Win95	?	?	?
	Progress 95	?	?	?	?	?
Red Brick Systems	Red Brick Warehouse	5.1	?	$50000.00 USD	?	?
Quadbase		5.0	NLM, NT, Win95	$995.00 USD	5	$100.00 USD
Quadcap	Quadcap Embeddable Database	1.0 beta 11	Pure Java 1.2	$800.00 USD (developer licence)	unlimited	$150.00 USD (runtime+unlimited seats)
SAS	Access	?	?	?	?	?
Sequiter (sic)	CodeBase/CodeServer xBase	6	DOS, MacIntosh, OS/2, Win95, Unix	$495.00 USD	unlimited	free
SleepyCat	Berkeley DB Java Edition	1.3	Single user database written in Java, and runs in the same JVM as your application. You don't have a separate server, which greatly simplifies administration and deployment. Source code included.	not yet determined	?	?
Solid Information Technology	Solid Server Single User	2.3	Linux	free	1	n/a
	Solid Server Single User	2.3	Windows	$99.00 USD	1	n/a
	Solid Server Workgroup	2.3	Linux, HPUX, NT, Sun Unix, Win3.1, Win95, ...	$199.00 USD	1	$199.00 USD
Software AG	Adabas	?	?	?	?	?
SPCS Scandinavian PC Systems	Primabase	?	?	?	?	?
Sun	NetDynamics/j.rad	1.0	anything that runs Java	$1995.00 USD	unlimited	free
Sqlite	SQLite	2.8.13	Single user database written in C, and runs in the same process as your application. You don't have a separate server, which greatly simplifies administration and deployment. C Source code included.	public domain source	1	n/a
SqlMagic	Super*SQL	beta	Written in Java so it runs anywhere. It is not an SQL database itself. It works with any database that has a JDBC driver. Its primary purpose is to generate HTML reports on a scheduled basis from an SQL database. It also helps in various debugging tasks.	$20.00 USD one time fee for personal edition. $100.00 USD yearly fee for the corporate edition.	unlimited	n/a
Sybase	SQL Anywhere per standalone user	5.5.01	DOS, NLM, NT, OS/2, Win3.1, Win95	$300.00 USD	1	$180.00 USD
	SQL Anywhere per seat	5.5.01	DOS, NLM, NT, OS/2, Win3.1, Win95	$300.00 USD	1	$120.00 USD
	SQL Anywhere per concurrent user	5.5.01	DOS, NLM, NT, OS/2, Win3.1, Win95	$600.00 USD	4	$150.00 USD
	SQL Anywhere Professional (includes Web server)	5.5.01	DOS, NLM, NT, OS/2, Win3.1, Win95	$400.00 USD	1	$200.00 USD
	jConnect	1.0	NT and low end Unix	$500.00 USD	unlimited	free
	jConnect Enterprise	1.0	high end Unix, various mainframes	$2000.00 USD	unlimited	free
	SQL Server	11	Alpha, HP, NT, PowerPC, Sun	secret	?	secret
	SQL Server Pro	11	NT	$695.00 USD	?	$39.00 USD
Symantec	Visual Café Database Edition includes: dbAnywhere Workgroup and Sybase SQL Anywhere	2.5a	NT, Win95	$500.00 USD	unlimited	free
	dbAnywhere Server	1.0	NT, Win95	$1500.00 USD	unlimited	free
Tandem	NonStop SQL	?	Tandem NonStop	?	?	?
ThinkSQL	ThinkSQL	beta	Written in Delphi/Kylix for Windows and Linux. Has OBJC, JDBC and dbExpress drivers.	?	?	?
TimesTen	TimesTen RAM Resident SQL	3.2	Windows NT 4.0 (32-bit), HP-UX 10.20 (32-bit), HP-UX 11 (32- and 64-bit), Solaris 2.5.1 and above (32-bit), Solaris 7 (Sparc, 32- and 64-bit), AIX 4.1.4 and above (32-bit), Lynx OS 3.0 (PowerPC)	?	?	?
Thought Inc	Cocobase Workgroup	2.0	anything that supports Java	$895.00 USD	unlimited	free
TriFox	GENESISsql	?	ISAM, dBase, flat files	?	?	?
TurboPower	FlashFiler	1.5	includes Delphi/C++ source	$229.00 USD	unlimited	free
Unidata	Unidata RDBMS	4.0	NT, Win95, others	?	?	?
Unify	Unify Dataserver 6	?	?	?	?	?
Unisys	DMSII	?	Unisys A series mainframes	secret	?	secret
W3Apps	Jeevan	2.0	anything that runs Java	$69.00 USD	1	$69.00 USD
Watcom	SQLAnywhere Now handled by Sybase
Yard Software	Yard-SQL	4.06.03	NT, Win95	free to schools, free for single user.	1	n/a?

Notes

DaveCentral also maintains a list of SQL vendors.

1. Standalone refers to the situation when users are usually disconnected each running with their own copy of the server. Periodically they connect via the Remote feature to resynchronise.
2. Per Seat refers to the normal situation when many computers are accessing the same database running on a server on a LAN.
3. Per Concurrent User allows an unlimited number of potential computers to access the database, but only a limited number of them can be actively logged onto the database at any one time.

Some vendors flatly refused to give me pricing information (marked secret), especially for high end products. Most simply ignored my requests for information. I gather either the prices are negotiated, or they are afraid of having out of date prices on the web. Perhaps they are just embarrassed by their high prices. This is one heck of a way to treat someone with money burning a hole in his pocket! Frustrating!

Codebase is not an SQL server, but an xBase server. It offers Java support through a proprietary API, not JDBC. The advantage of that is CodeBase's small footprint -- about 50K.

Hypersonic includes a front end that generates SQL queries from a simpler syntax.

Inprise's Local Interbase is single user, though it lets you run several programs at once. Delphi Client Server package contains Interbase servers for NT and Windows 95, as well as some native code links to its Delphi product that work faster than the ODBC interface. Presumably Inprise's JBuilder product will be compatible with it.

JustLogic is most unusual. It not only charges nothing per seat, but charges no royalties either. You can distribute it freely with any code you write.

Microsoft Access is an ultra lightweight slightly incompatible SQL. It does not even use a server. Each client updates the database independently using ordinary file locking.

mySQL from Sweden is unusual in that they give you source. Under some circumstances you must pay a licensing fee. MySQL is very popular because it is both powerful and usually free. The drawbacks to consider include:

• absence of triggers, stored procedures and views. Some people used to write almost a complete application in plsql/tsql inside the database. With multi-tier architecture this code becomes less and less important for business applications. Still, triggers and stored procedures are useful for ensuring data integrity. All of this can be done at the application level outside the database.
• less auditing capabilities.
• less powerful visual admin/monitoring tools, but this segment growing very fast.
• less granular locking.
• no clustering.
• less literature, fewer experts, knowledge base, PR. On the other hand, most amateurs cut their teeth on MySQL.
• database size limited by OS file size.

Oracle's upcoming J/SQL allows you to write triggers in Java. A trigger is code stored as part of the database that automatically gets executed when a row is updated.) Oracle looks to be on the way to more thoroughly integrating Java and SQL than JDBC offers. Oracle has a family of upwardly compatible SQL databases.

Personal Oracle is single user only.

PostgresSQL has a more object-oriented interface than most. SQL columns and rows are mapped onto classes and instances. Jeevan too is object oriented.

Sequiter offers to sell you the source code for $800.00 USD . That is good insurance should the vendor drop the product.

Solid Software is from Finland. Despite the engine's low cost, it has referential integrity, stored procedures, database events, but no triggers. I'm told its even faster than SQLAnywhere. You can download a free evaluation copy.

Sybase's new jConnect bundle gives you their full SQL Server with unlimited access for a remarkably low price. This gives you Java thin client-only access. Sybase SQLAnywhere has three pricing schemes. You have to pick one scheme and stick with it.

Symantec's Visual Café Database Edition comes with dbAnywhere and Sybase SQLAnywhere for unlimited seats. It also comes with 5-seat dbAnywhere Workgroup drivers for Oracle, Sybase SQL Server, MS SQL Server and ODBC. If you want more seats, you have to go to dbAnywhere Server, which includes your choice of driver for one of Oracle, Sybase SQL Server, MS SQL Server or ODBC. You still have to buy the SQL server and seats. The Symantec documentation on installing dbAnywhere is full of errors. See the essay [an error occurred while processing this directive] I wrote on how to install it.

I am still unclear on whether you need to buy a second copy of Visual Café when you deploy your database using SQLAnywhere. The license agreement contradicts itself.

It is also possible that the SQLAnywhere/dbAnywhere package that comes with Visual Café Pro is intended to be limited to five seats even if that is not enforced by the software.

One notable feature of Sybase SQLAnywhere is you get code all platforms on the same CD, and the ability to migrate your database between platforms.

Thought Inc's SQL comes with source written in Java ??. It is accessible only via JDBC.

TimesTen and Hypersonic SQL can be run in memory-resident mode where the entire database is kept in RAM.

Yard's Database is free if the total database is under 5 MB. Unfortunately the JDBC interface is incomplete.

You can download a free report writer for JDBC.

Learning More

	Guide to the SQL Standard: A User's Guide to the Standard Database Language SQL
	0-201-96426-0
	Chris J. Date, yes the C.J. Date.
Considered the best on understanding the SQL "standard"

see the list of JDBC books.

Castor ¤ foreign key ¤ Hibernate ¤ JDBC ¤ JDO ¤ JDX ¤ Jeevan ¤ JSQL ¤ metadata ¤ O'Reilly On Java SQL essays ¤ Object-Relational Bridge ¤ ORDBMS ¤ POD ¤ XQuery

home	Canadian Mind Products
	mindprod.com IP:[24.87.56.253]
	Your IP:[80.134.30.163]
	You are visitor number 17809.
	Please send errors, omissions and suggestions
	to improve this page to Roedy Green.
	You can get a fresh copy of this page from:	or possibly from your local J: drive mirror:
	http://mindprod.com/jgloss/sql.html	J:\mindprod\jgloss\sql.html