Hsqldb User Guide

    java -cp ../lib/hsqldb.jar org.hsqldb.Server -database.0 mydb -dbname.0 xdb

L' argument -? rappelle la liste des arguments disponibles.

Pour utiliser ce mode, replacer la classe main pour kle serveur dans la ligne de commande ci-dessus (above = ci-dessus ?) par la classe suivante :

    org.hsqldb.WebServer

Utilise le même protocole que celui utilisé par le mode Web Server. Utilisé lorsqu'un un moteur de Servelet externe (ou application Serveur), tel que Tomcat or Resin, accède directement à la Base. Ce mode est dépendant du moteur de servelt. La classe hsqlServlet du HSQLDB jar doit être installé dans l'application serveur pour se connecter. La BD à utiliser est préciser dans les propriété de l'application serveur.

Pour plus de détail, se referer au fichier source hsqlServlet.java

Les deux modes Web Server et Servlet ne peuvent acceder aux BD qu'en utilisant le pilote JDBC at the client end. They do not provide a web front end to the database. Le mode Servelet ne peut répondre qu'a une seule BD (-> connection mono-BD azu sein d'une appli serveur).

Please note that you do not normally use this mode if you are using the database engine in an application server.
__ Proposition : Vous ne devez pas utiliser ce mode si vous utilisez déjà un moteur de BD dans votre application serveur __

    try {
        Class.forName("org.hsqldb.jdbcDriver" );
    } catch (Exception e) {
        System.out.println("ERROR: failed to load HSQLDB JDBC driver.");
        e.printStackTrace();
        return;
    }

    Connection c = DriverManager.getConnection("jdbc:hsqldb:hsql://localhost/xdb", "sa", "");

    Class.forName("org.hsqldb.jdbcDriver").newInstance();

Ce mode utilise le moteur de BD comme partie intégrante de votre application au sein de la même JVM. Pour beaucoup d'application ce mode est plus rapide, car les données ne sont pas converties et ne transitent pas via le reseau.

Par défaut il n'est pas possible de se connecter à la BD en dehors de l'application. Par conséquent , il et impossible de vérifier le contenu de la BD par des outils externes comme le gestionnaire de BD tant que votre application tourne. Dans la version 1.8.0, vous pouvez lancer une instance de serveur dans un processus depuis la m^me machine virtuelle que votre application ce qui procure un accès externe à vos traitements de BD .

Il est recomandé d'utiliser le mode in-process dans les application utilisant une instance serveur HSQLDB pour la BD durant les phases de développement d'application et passer au mode In-Process mode pour le déploiment.

En Mode In-Process la BD est lancée depuis le JDBC, avec le chemin du ficher de la BD specifié dans l'URL de connexion.
Par exemple, si le nom de la BD est testdb et ses fichiers sont situés dans le mêm répertoire que celui où sont lancé les commandes pour executer l'application, le code suivant est utilisé pour établir la connexion:

    Connection c = DriverManager.getConnection("jdbc:hsqldb:file:testdb", "sa", "");

Le chemin de la BD peut contenir des "forward slashes" que la station tourne sous Windows ou sou linux. Les chemins relatifs ou les chemins se référenat au même répertoire du m^me disque peuvent être identique. Par exemple si votre chemin de BD sous Linux est /opt/db/testdb et que vous creez une arborescence de répertoires respectant la même structure sur le disque C: d'un poste Windows , vous pouvez utiliser la même URL pour Windows et pour Linux:

    Connection c = DriverManager.getConnection("jdbc:hsqldb:file:/opt/db/testdb", "sa", "");

Lorsque les chemins relatifs sont utilisés, ces chemins seront relatifs au répertoire dans lequel les commandes shell pour démarer la JVM sont executées. . Se referer à la Javadoc jdbcConnection pour plus de détail.

Il est possible d'executer HSQLDB de telle sorte que la BD ne soit pas persistente (??? un DBA saura certainement ce que cela signifie)) et existe entièrement chargée en mémoire vive . Aucune information ne seront écrite sur disque.

this mode should be used only for internal processing of application data, in applets or certain special applications
__ Proposition : Ce mode doit être utilisé uniquement pour les traitements internes d'application, dans les applets ou certaines applications spéciales __

    Connection c = DriverManager.getConnection("jdbc:hsqldb:mem:aname", "sa", "");

Une instance serveur peut être utilisée en mode memory-only en spécifiant la même URL dans server.properties.
Cette utilisation n'est pas courrante et est limitée aux solutions où le serveur de BD n'est utilisé que pour l'echange d' information entre clients, ou pour les données non-persistentes.

Lors de l'execution d'un SHUTDOWN, il est proceder un rollback sur toutes les transactions actoves.

Cas particulier la commande SHUTDOWN COMPACT. Cette commande ré-écrit le fichier .data, qui contient les informations stockées dans les tables CACHED (stockées en mémoire cache, voir les explications plus bas dans le paragraphe différents types de tables), en le compressant.
Cette commande doit être passée périodiquement, en particulier lorsque beaucoup d'INSERT , UPDATE ou de DELETE ont été effectués sur les tables. Changer la structure d'une BD, comme dropp, ou modifier les tables CACHED, ou indexer ont pour effet de laisser une large place perdue dans le fichier, cette commande permet de la recupérer.

HSQLDB supporte les tables temporaires TEMP et trois sortes de tables persistantes.

Les tables temporaires ne sont pas écrites sur le disque et leur durée de vie est limitée à celle de la connexion . Leur contenu n'est visible qu'au sein de la connexion utilisée lors de leur génération; Toutes autre connexion concurrente à la BD n' auront accès qu'a des copies
Depuis la version 1.8.0 La définition d'une table temporaire est conforme au type GLOBAL TEMPORARY du SQL standard.
La definition de la table perdure mais chaque nouvelle connexion ne voit que ses propres copies de cette table, laquelle est vide au début. Lorsque la connexion est lkancée, cette table est vidée. Si la définition de la table inclu ON COMMIT PRESERVE ROWS, alors le contenu sera garder lors de l'execution du commit.

Les 3 types de tables prsistantes sont MEMORY , CACHED et TEXT .

Memory tables est le type par défaut utilisé lors du CREATE TABLE . Les données sont stockées entièrement en mémoire mais les changement de dstructure ou de contenu sont écrits dans le fichier <dbname>.script . Ce fichier est lu lors de la prochaine ouverture de la BD, et les MEMORY tables sont recrées avec leur contenu Ainsi contrairement aux TEMP table, les MEMORY tables (default) sont persistantes.

CACHED tables sont crées avec la commande CREATE CACHED TABLE . Seules une partie des données ou index sont stockés en mémoire, permettant aux tables d'atteindre plusieurs centaines de megabit. Un autre avantage des cached tables est que le moteur de BD prend moins de temps à se lancer lorsque les cached table pour manipuler beaucoup de données (remarque personelle : cela ne doit pas être perceptible pour les petites BD, ou les ressources systèmes seront de toute les manières plus importantes que les ressources nécéssaire aux accès aux BD).
L'inconvénient est la réduction des performances. Ne pas utiliser les cached tables si le volume de vos données est petit.
Dans les applications avec quelques petites tables et quelque grosses tables,il est préférable d'utiliser par défaut le mode MEMORY pour les petites tables.

TEXT tables supportées depuis la version 1.7.0 et utilisent le format CSV (séparateur virgule) ou autre fichier texte délimité comme source de données. Vous pouvez spécifier un fichier CSV existant, tel un fichier Dump provenant d'un autre SGBDR, comme source d'une TEXT table. Sinon, vous pouvez spécifier un fichier vide qui sera remplis avec les données par le moteur de BD. TEXT tables sont efficaces en mode in memory
as they cache only part of the text data and all of the indexes.
Les sources des TEXT tables peuvent toujours être reassignés à d'autres fichier si nécéssaire.
Deux commandes sont utiles pour initialiuser une TEXT table . Elles sont détaillées dans le chapitre Text Tables .

Avec les BD en mode memory-only , les tables de type MEMORY et CACHED sont traitées comme des tables non-persistentes, tandisque les TEXT table sont interedites.

HSQLDB créé un indes interne pour supporter les contraintes PRIMARY KEY, UNIQUE et FOREIGN KEY : un index unique est créé pour chaque contrainte PRIMARY KEY ou UNIQUE ; un index ordinaire est créé pour chaque FOREIGN KEY. Aussi, vous ne devez pas creer d'index duplicate user-defined su r la même colonne que celle utilisé par ces contraintes. Cela alourdirait les performances sans intérèt. cf. la discussion à ce sujet au chapitre SQL Issues .

L'importance des indexes est crucial pour les performances . Lorsque une jointure sur de multiples tables est réalisée, il doit y avoir plusieurs index utilisés dans les colonnes intervenant dans chaque jointures entre 2 tables. Lorsque des conditions d'ordonnancement ou d'égalités sont utilisés e.g. SELECT ... WHERE acol >10 AND bcol = 0, un index est requis pour la colonne acol qui est utilisée dans la condition.
Les Indexes n'ont aucun effet dans les clause ORDER BY ou les conditions LIKE .

As a rule of thumb, HSQLDB peut réaliser des traitement de plus de 100,000 lignes par secondes . Une requete executée en beaucoup seconde devrait être étudiée et des indexes devraient être ajouter sur des colonnes intervenant dedans si nécéssaire.

Les commandes supportées sont listées au chapitre SQL Syntax . Pour un bon guide sur le SQL basique avec exemples consulter PostgreSQL: Introduction and Concepts ecrit par Bruce Momjian, disponible sur le web. La plus part des livre sur le SQL s'appliquent à HSQLDB.Il y a quelques differences de syntaxes d'un moteur de BD à un autre (OUTER, OID's, etc.) ou d'utilisation (IDENTITY/SERIAL, TRIGGER, SEQUENCE, etc.).

La contrainte unique sur plusieurs colonne (c1, c2, c3, ..) indique que vous ne pouvez pas avoir deux jeux de valeurs identiques sur ces colonnes à l'exception de la valeur NULL. En revanche chaque colonne seule peut avoir des doublons.

L'exemple suivant répond à la contrainte unique sur deux colonnes :

Depuis la version 1.7.2 le comportement des contrintes d'unicité et des indexes avec le respect de la valeur NULL a changé pour se conformer aux standarts du SQL. Une ligne dont la valeur d'une colonne à contrainte UNIQUE est NULL peut toujours être ajoutée à la table. Ainsi plusieurs lignes peuvent contenir la même valeur de colonne porteuse de la contrainte UNIQUE si cette valeur est NULL.

CONSTRAINT <name> UNIQUE créé toujours en interne un index unique sur les colonnes, comme dans les précédentes versions. Aussi cela a exactement le même effet que la déclaration, désuette, de l'index UNIQUE .

Chaque couple référence et colonne référencée au sein d'une clé etragère doivent être de même type.

Lorsque une clé étrangère est déclarée, la contrainte UNIQUE doit exister (ou clé-primaire) sur les colonnes référenceées dans la table des clés primaires. Un index non-unique est automatiquement créé sur les colonnes referencées. Exemple :

    CREATE TABLE child(c1 INTEGER, c2 VARCHAR, FOREIGN KEY (c1, c2) REFERENCES parent(p1, p2));

Ici, il doit y avoir une contrainte UNIQUE  sur les colonnes (p1,p2)dans la table nomée "parent".
Un index non-unique est automatiquement créé sur les colonnes (c1, c2) dans la table nommée "child".
Les colonnes p1 et c1 doivent être du même type (INTEGER).
Les colonnes p2 et c2 doivent être du même type (VARCHAR).

Les indexes sont particulièrement important dans les requetes à jointures multiples. SELECT ... FROM t1 JOIN t2 ON t1.c1 = t2.c2 est executée en prenant les lignes de T1 et en cherchant la corespondance dans t2 une par une. S'il n'y a pas d'index sur t2.c2 alors pour chaque ligne de t1; chaque ligne de t2 doit être pointée. Tandisque avec un index une ligne correspondant peut être trouvée en une fraction de temps.

Si la requete a la condition sur T1,ex: SELECT ... FROM t1 JOIN t2 ON t1.c1 = t2.c2 WHERE t1.c3 = 4 alors un indes sur t1.c3 devrait éliminer des vérifications, toutes les lignes de t1 une par une,et devrait réduire le temps de la requete à moins d'une miliseconde par ligne retournée. Ainsi si t1 et t2 contiennet chacune 10,000 lignes, la requete sans index devant vérifier une combinatoire égale à 100,000,000 lignes. Avec un index sur t2.c2 , cela est réduit à 10,000 lignes à vérifier and index lookups. Avec l'index additionnel sur t2.c2, seulement 4 lignes sont vérifiées pour obtenir la première ligne de résultat.

Les indexes sont automatiquement créés sur les clé primaires et sur les colonnes porteuse de la contrainte UNIQUE. Sinon vous pouvez utiliser la commande CREATE INDEX .

Remarque dans HSQLDB un index unique sur des colonnes multiples peut être utilisé en interne comme un index non-unique sur la premiere colonne de la liste. Par exemple : CONSTRAINT name1 UNIQUE (c1, c2, c3); est équivalent à CREATE INDEX name2 ON atable(c1); Donc vous n'avez pas besoin de spécifier d'index supplementaire sur la premiere colonne de la liste des colonnes visées par un index.

En v 1.8.0, un index multi-colonne augmentera la vitesse des requetes contenant une jointure ou valeur sur toutes les colonnes. (de l'index je suppose)
Vous n'avez pas besoin de declarer d'index individuel sur chacune de ces colonnes tant que vous utilisez des requetes qui recherche seulement un subset de ces colonnes. Par exemple, les lignes d'une tables ayant une PRIMARY KEY ou une contrainte UNIQUE sur 3 colonnes, ou simplement un index ordinaire sur l'une de ces colonnes peut être trouvé efficacement quand les valeur des 3 colonnes sont spécifiées dans les clauses du WHERE .
SELECT ... FROM t1 WHERE t1.c1 = 4 AND t1.c2 = 6 AND t1.c3 = 8 utilisera un index sur t1(c1,c2,c3)s'il existe.

Finalement, le gain du résultat via des indexes sur des clés multiples, l'oredre de déclarations des colonnes dans les indexes ou dans les contraintes a moins d'impacte sur la vitesse de recherche qu'avant. Si la colonne qui contient le plus de valeurs apparit en premier, la rechercher sera d'autant plus rapide.

Un index multi-colonnes n'augmentera pas la vitesse d'interrogation sur la seconde ou sur la troisième colonne uiniquement.. La première colonne doit être spécifiée dans les conditions JOI N, ON ou WHERE.

La vitesse dépend beaucoup de l'ordre des tables dans les jointures. Par exemple la seconde requete ci-dessous devrait être plus facile avec de grosses tables (parce qu'il y a un index sur TB.COL3). Cela est dû au fait que TB.COL3 peut être évalué très rapidement s'il est appliqué à la première table (et il y a un index sur TB.COL3):

    (TB is a very large table with only a few rows where TB.COL3 = 4)

    SELECT * FROM TA JOIN TB ON TA.COL1 = TB.COL2 AND TB.COL3 = 4;

    SELECT * FROM TB JOIN TA ON TA.COL1 = TB.COL2 AND TB.COL3 = 4;

La règle générale et de mettre en premier la table qui a ??? that has a narrowing condition on one of its columns.

en 1.7.3 ces caractèristiques étaient automatique, sur les index volants (???) des vues et les sous select (requete SQL imbriquées) utilisés dans les requetes. Un index est ajouté à une vue lorsqu'elle est joint à une table ou à une autre vue.

Cette requete implique TA.COL1 = TB.COL2 mais ne le met pas explicitement dans ses conditions. Si TA et TB contiennent chacun 100 lignes, 10000 combinaisons seront jointe avec TC pour appliquer la condition sur la colonne,Aussi il devrait y avoir un index pour joindre les colonnes. Avec le mot clé JOIN , la condition TA.COL1 = TB.COL2 est explicite et diminuent d'autant les combinaisons de TA et TB avant de les appliquer à la jointure avec TC , le resultat est beaucoup plus rapoide avec de grosses tables:

    SELECT ... FROM TA JOIN TB ON TA.COL1 = TB.COL2 JOIN TC ON TB.COL2 = TC.COL3 WHERE TC.COL4 = 1

La performance peut être encore augmentée en changeant l'ordre des tables,ainsi TC.COL1 = 1 est appliquer en premier et le plus petit jeu de lignes sont jontes par la suite:

    SELECT ... FROM TC JOIN TB ON TC.COL3 = TB.COL2 JOIN TA ON TC.COL3 = TA.COL1 WHERE TC.COL4 = 1

Dans cet exemple le moteur applique automatiquement TC.COL4 = 1 à TC et ne fait la jointure avec les autres tables que sur le jeu des lignes répondant à cette condition . Les index sur TC.COL4, TB.COL2 et sur TA.COL1 seront utilisés s'ils sont présents pour accelerer la requete.

    SELECT ... FROM TA WHERE TA.COL1 = (SELECT MAX(TB.COL2) FROM TB WHERE TB.COL3 = 4)

    SELECT ... FROM (SELECT MAX(TB.COL2) C1 FROM TB WHERE TB.COL3 = 4) T2 JOIN TA ON TA.COL1 = T2.C1

La seconde requete execute MAX(TB.COL2)dans une seule ligne de la table puis fais la jointure avec TA. Avec une index sur TA.COL1 cela sera vraiment très rapide.

La première requete testera chaque ligne TA et evaluera MAX(TB.COL2)encore et encore.

If the SELECT statement refers to a simple column or function, then the return type is the type corresponding to the column or the return type of the function. For example:

    CREATE TABLE t(a INTEGER, b BIGINT); SELECT MAX(a), MAX(b) FROM t;

would return a result set where the type of the first column is java.lang.Integer and the second column is java.lang.Long. However,

    SELECT MAX(a) + 1, MAX(b) + 1 FROM t;

would return java.lang.Long and BigDecimal values, generated as a result of uniform type promotion for all the return values.

There is no built-in limit on the size of intermediate integral values in expressions. As a result, you should check for the type of the ResultSet column and choose an appropriate getXXXX() method to retrieve it. Alternatively, you can use the getObject() method, then cast the result to java.lang.Number and use the intValue() or longValue() methods on the result.

When the result of an expression is stored in a column of a database table, it has to fit in the target column, otherwise an error is returned. For example when 1234567890123456789012 / 12345687901234567890 is evaluated, the result can be stored in any integral type column, even a TINYINT column, as it is a small value.

In SQL statements, numbers with a decimal point are treated as DECIMAL unless they are written with an exponent. Thus 0.2 is considered a DECIMAL value but 0.2E0 is considered a DOUBLE value.

When PreparedStatement.setDouble() or setFloat() is used, the value is treated as a DOUBLE automatically.

When a REAL, FLOAT or DOUBLE (all synonymous) is part of an expression, the type of the result is DOUBLE.

Otherwise, when no DOUBLE value exists, if a DECIMAL or NUMERIC value is part an expression, the type of the result is DECIMAL. The result can be retrieved from a ResultSet in the required type so long as it can be represented. This means DECIMAL values can be converted to DOUBLE unless they are beyond the Double.MIN_VALUE - Double.MAX_VALUE range. Similar to integral values, when the result of an expression is stored in a table column, it has to fit in the target column, otherwise an error is returned.

The distinction between DOUBLE and DECIMAL is important when a division takes place. When the terms are DECIMAL, the result is a value with a scale (number of digits to the right of the decimal point) equal to the larger of the scales of the two terms. With a DOUBLE term, the scale will reflect the actual result of the operation. For example, 10.0/8.0 (DECIMAL) equals 1.2 but 10.0E0/8.0E0 (DOUBLE) equals 1.25. Without division operations, DECIMAL values represent exact arithmetic; the resulting scale is the sum of the scales of the two terms when multiplication is performed.

REAL, FLOAT and DOUBLE values are all stored in the database as java.lang.Double objects. Special values such as NaN and +-Infinity are also stored and supported. These values can be submitted to the database via JDBC PreparedStatement methods and are returned in ResultSet objects.

Since 1.7.2, BIT is simply an alias for BOOLEAN. The primary representation of BOOLEAN column is 'true' or 'false' either as the boolean type or as strings when used from JDBC. This type of column can also be initialised using values of any numeric type. In this case 0 is translated to false and any other value such as 1 is translated to true.

Since 1.7.3 the BOOLEAN type conforms to the SQL standards and supports the UNDEFINED state in addition to TRUE or FALSE. NULL values are treated as undefined. This improvement affects queries that contain NOT IN. See the test text file, TestSelfNot.txt, for examples of the queries.

Since version 1.7.2 this support has improved and any serializable JAVA Object can be inserted directly into a column of type OTHER using any variation of PreparedStatement.setObject() methods.

For comparison purposes and in indexes, any two Java Objects are considered equal unless one of them is NULL. You cannot search for a specific object or perform a join on a column of type OTHER.

Please note that HSQLDB is not an object-relational database. Java Objects can simply be stored internally and no operations should be performed on them other than assignment between columns of type OTHER or tests for NULL. Tests such as WHERE object1 = object2, or WHERE object1 = ? do not mean what you might expect, as any non-null object would satisfy such a tests. But WHERE object1 IS NOT NULL is perfectly acceptable.

The engine does not return errors when normal column values are assigned to Java Object columns (for example assigning an INTEGER or STRING to such a column with an SQL statement such as UPDATE mytable SET objectcol = intcol WHERE ...) but this is highly likely to be disallowed in future. So please use columns of type OTHER only to store your objects and nothing else.

Prior to 1.7.2, all table column type definitions with a column size, precision or scale qualifier were accepted and ignored.

In 1.8.0, such qualifiers must conform to the SQL standards. For example INTEGER(8) is no longer acceptable. The qualifiers are still ignored unless you set a database property. SET PROPERTY "sql.enforce_strict_size" TRUE will enforce sizes for CHARACTER or VARCHAR columns and pad any strings when inserting or updating a CHARACTER column. The precision and scale qualifiers are also enforced for DECIMAL and NUMERIC types. TIMESTAMP can be used with a precision of 0 or 6 only.

Casting a value to a qualified CHARACTER type will result in truncation or padding as you would expect. So a test such as CAST (mycol AS VARCHAR(2)) = 'xy' will find the values beginning with 'xy'. This is the equivalent of SUBSTRING(mycol FROM 1 FOR 2) = 'xy'.

Each table can contain one auto-increment column, known as the IDENTITY column. An IDENTITY column is always treated as the primary key for the table (as a result, multi-column primary keys are not possible with an IDENTITY column present). Support has been added for CREATE TABLE <tablename>(<colname> IDENTITY, ...) as a shortcut.

Since 1.7.2, the SQL standard syntax is used by default, which allows the initial value to be specified. The supported form is(<colname> INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH n, [INCREMENT BY m])PRIMARY KEY, ...). Support has also been added for BIGINT identity columns. As a result, an IDENTITY column is simply an INTEGER or BIGINT column with its default value generated by a sequence generator.

When you add a new row to such a table using an INSERT INTO <tablename> ...; statement, you can use the NULL value for the IDENTITY column, which results in an auto-generated value for the column. The IDENTITY() function returns the last value inserted into any IDENTITY column by this connection. Use CALL IDENTITY(); as an SQL statement to retrieve this value. If you want to use the value for a field in a child table, you can use INSERT INTO <childtable> VALUES (...,IDENTITY(),...);. Both types of call to IDENTITY() must be made before any additional update or insert statements are issued on the database.

The next IDENTITY value to be used can be set with the

ALTER TABLE ALTER COLUMN <column name> RESTART WITH <new value>;

The SQL 200n syntax and usage is different from what is supported by many existing database engines. Sequences are created with the CREATE SEQUENCE command and their current value can be modified at any time with ALTER SEQUENCE. The next value for a sequence is retrieved with the NEXT VALUE FOR <name> expression. This expression can be used for inserting and updating table rows. You can also use it in select statements. For example, if you want to number the returned rows of a SELECT in sequential order, you can use:

    SELECT NEXT VALUE FOR mysequence, col1, col2 FROM mytable WHERE ...

Please note that the semantics of sequences is not exactly the same as defined by SQL 200n. For example if you use the same sequence twice in the same row insert query, you will get two different values, not the same value as required by the standard.

You can query the SYSTEM_SEQUENCES table for the next value that will be returned from any of the defined sequences. The SEQUENCE_NAME column contains the name and the NEXT_VALUE column contains the next value to be returned.

The primary design criterion of the init script is portability. It does not print pretty color startup/shutdown messages as is common in late-model Linuxes and HPUX; and it does not keep subsystem state files or use the startup/shutdown functions supplied by many UNIXes, because these features are all non-portable.

Offsetting these limitations, this one script does it's intended job great on the UNIX varieties I have tested, and can easily be modified to accommodate other UNIXes. While you don't have tight integration with OS-specific daemon administration guis, etc., you do have a well tested and well behaved script that gives good, utilitarian feedback.

The strategy taken here is to get the init script to run your single Server or WebServer first (as specified by TARGET_CLASS). After that's working, you can customize the JVM that is run by running additional Servers in it, running your own application in it (embedding), or even overriding HSQLDB behavior with your own overriding classes.

Follow the examples in the config file to add additional classes to the server JVM's classpath and to execute additional classes in your JVM. (See the SERVER_ADDL_CLASSPATH and INVOC_ADDL_ARGS items).

Do a ps to look for processes containing the string hsqldb, and try to connect to the database from any client. If the init script starts up your database successfully, but incorrectly reports that it has not, then your problem is with specification of urlid(s) or SqlTool setup. If your database really did not start, then skip to the next paragraph. Verify that the urlid(s) listed in the server.properties or webserver.properties are correct. and verify that you can run SqlTool as root to connect to the instances. (For the latter test, use the --rcfile switch if you are setting AUTH_FILE in the init script config file).

If your database really is not starting, then verify that you can su to the database owner account and start the database. The command su USERNAME -c ... won't work on most UNIXes unless the target user has a real login shell. Therefore, if you try to tighten up security by disabling this user's login shell, you will break the init script. If these possibilities don't pan out, then debug the init script or seek help, as described below.

To debug the init script, run it in verbose mode to see exactly what is happening (and perhaps manually run the steps that are suspect). To run an init script (in fact, any sh shell script) in verbose mode, use sh with the -x or -v switch, like

    sh -x path/to/hsqldb start

If you want troubleshooting help, use the HSQLDB lists/forums or email me at blaine.simpson@admc.com. If you email me, make sure to include the revision number from your hsqldb init script (it's towards the top in the line that starts like "# $Id:"), and the output of a run of

    sh -x path/to/hsqldb start > /tmp/hstart.log 2>&1

Each new JDBC Connection to a database can specify connection properties. The properties user and password are always required. In 1.8.0 the following optional properties can also be used.

Connection properties are specified either by establishing the connection via the:

    DriverManager.getConnection (String url, Properties info);

method call, or the property can be appended to the full Connection URL.

    jdbc:hsqldb:hsql://localhost/enrollments;get_column_name=false
                    

    jdbc:hsqldb:file:enrollments;ifexists=true

In addition, when a connection to an in-process database creates a new database, or opens an existing database (i.e. it is the first connection made to the database by the application), all the user-defined database properties can be specified as URL properties. This can be used to specify properties to enforce more strict SQL adherence, or to change cache_scale or similar properties before the database files are created. However, for new databases, it is recommended to use the SET PROPERTY command for such settings.

In both server.properties and webserver.properties files, supported values and their defaults are as follows:

In 1.8.0, each server can serve up to 10 different databases simultaneously. The server.database.0 property defines the filename / path whereas the server.dbname.0 defines the lowercase alias used by clients to connect to that database. The digit 0 is incremented for the second database and so on. Values for the server.database.{0-9} property can use the mem:, file: or res: prefixes and properties as discussed above under CONNECTIONS. For example,

    database.0=mem:temp;sql.enforce_strict_size=true;

Values specific to server.properties are:

Values specific to webserver.properties are:

All the above values can be specified on the command line to start the server by omitting the server. prefix.

If you want to start the server from within your application, as opposed to the command line or batch files, you should create an instance of Server or Web Server, then assign the properties in the form of a String and start the Server. An example of this can be found in the org.hsqldb.test.TestBase source.

Each database has its own <dbname>.properties file as part of a small group of files which also includes <dbname>.script and <dbname>.data. The properties files contain key/value pairs for some important settings.

In version 1.8.0 a new SQL command allows most database properties to be modified as follows:

    SET PROPERTY "property_name" property_value

Properties that can be modified via SET PROPERTY are indicated in the table below. Other properties are indicated as PROPERTIES FILE ONLY and can be modified only by editing the .properties file after a shutdown and before a restart. Only the user-defined values listed below should ever be modified. Changing any other value could result in unexpected malfunction in database operations. Most of these values have been introduced for the new features since 1.7.0:

hsqldb.cache_file_scale=8

When connecting to an in-process database creates a new database, or opens an existing database (i.e. it is the first connection made to the database by the application), all the user-defined database properties listed in this section can be specified as URL properties.

The property sql.compare_in_locale=true is no longer supported. If the line exists in a .properties file, it will switch the database to the collation for the current default. See the SET DATABASE COLLATION^[1] command.

When HSQLDB is used in OpenOffice.org, some property values will have a different default. The properties and values are:

hsqldb.default_table_type=cached hsqldb.cache_scale=13 hsqldb.log_size=10; hsqldb.nio_data_file=false sql.enforce_strict_size=true

The decision to run HSQLDB as a separate server process or as an in-process database should be based on the following:

TEXT tables are designed for special applications where the data has to be in an interchangeable format, such as CSV. TEXT tables should not be used for routine storage of data.

MEMORY tables and CACHED tables are generally used for data storage. The difference between the two is as follows:

JDBC Clobs are supported as columns of the type LONGVARCHAR. JDBC Blobs are supported as columns of the type LONGVARBINARY. When large objects (LONGVARCHAR, LONGVARBINARY, OBJECT) are stored with table definitions that contain several normal fields, it is better to use two tables instead. The first table to contain the normal fields and the second table to contain the large object plus an identity field. Using this method has two benefits. (a) The first table can usually be created as a MEMORY table while only the second table is a CACHED table. (b) The large objects can be retrieved individually using their identity, instead of getting loaded into memory for finding the rows during query processing. An example of two tables and a select query that exploits the separation between the two follows:

CREATE MEMORY TABLE MAINTABLE(MAINID INTEGER, ......);

CREATE CACHED TABLE LOBTABLE(LOBID INTEGER, LOBDATA LONGVARBINARY);

SELECT * FROM (SELECT * FROM MAINTABLE <join any other table> WHERE <various conditions apply>) JOIN LOBTABLE ON MAINID=LOBID;

The inner SELECT finds the required rows without reference to the LOBTABLE and when it has found all the rows, retrieves the required large objects from the LOBTABLE.

The files used for storing HSQLDB database data are all in the same directory. New files are always created and deleted by the database engine. Two simple principles must be observed:

With CACHED tables, the data is stored on disk and only up to a maximum number of rows are held in memory at any time. The default is up to 3*16384 rows. The hsqldb.cache_scale database property can be set to alter this amount. As any random subset of the rows in any of the CACHED tables can be held in the cache, the amount of memory needed by cached rows can reach the sum of the rows containing the largest field data. For example if a table with 100,000 rows contains 40,000 rows with 1,000 bytes of data in each row and 60,000 rows with 100 bytes in each, the cache can grow to contain nearly 50,000 rows, including all the 40,000 larger rows.

An additional property, hsqldb.cache_size_scale can be used in conjunction with the hsqldb.cache_scale property. This puts a limit in bytes on the total size of rows that are cached. When the default values is used for both properties, the limit on the total size of rows is approximately 50MB. (This is the size of binary images of the rows and indexes. It translates to more actual memory, typically 2-4 times, used for the cache because the data is represented by Java objects.)

If memory is limited, the hsqldb.cache_scale or hsqldb.cache_size_scale database properties can be reduced. In the example above, if the hsqldb.cache_size_scale is reduced from 10 to 8, then the total binary size limit is reduced from 50MB to 12.5 MB. This will allow the number of cached rows to reach 50,000 small rows, but only 12,500 of the larger rows.

To upgrade from 1.7.2 or 1.7.3 to 1.8.0, simply issue the SET SCRIPTFORMAT TEXT and SHUTDOWN SCRIPT commands with the old version, then open with the new version of the engine. The upgrade is then complete.

To upgrade from older version database files (1.7.1 and older) that do not contain CACHED tables, simple SHUTDOWN with the older version and open with the new version. If there is any error in the .script file, try again after editing the .script file.

To upgrade from older version database files (1.7.1 and older) that contain CACHED tables, use the SCRIPT procedure below. In all versions of HSQLDB and Hypersonic 1.43, the SCRIPT 'filename' command (used as an SQL query) allows you to save a full record of your database, including database object definitions and data, to a file of your choice. You can export a script file using the old version of the database engine and open the script as a database with 1.8.0.

In 1.8.0 the full range of ALTER TABLE commands is available to change the data structures and their names. However, if an old database cannot be opened due to data inconsistencies, or the use of index or column names that are not compatible with 1.8.0, manual editing of the SCRIPT file can be performed.

The following changes can be applied so long as they do not affect the integrity of existing data.

After completing the changes and saving the modified *.script file, you can open the database as normal.

Text Tables are defined similarly to conventional tables with the added TEXT keyword:

    CREATE TEXT TABLE <tablename> (<column definition> [<constraint definition>])

In addition, a SET command specifies the file and the separator character that the Text table uses:

    SET TABLE <tablename> SOURCE <quoted_filename_and_options> [DESC]

Text Tables cannot be created in memory-only databases (databases that have no script file).

This has changed since 1.7.2 to support both null values and empty strings.

The default field separator is a comma (,). A different field separator can be specified within the SET TABLE SOURCE statement. For example, to change the field separator for the table mytable to a vertical bar, place the following in the SET TABLE SOURCE statement, for example:

    SET TABLE mytable SOURCE "myfile;fs=|"

Since HSQLDB treats CHAR's, VARCHARs, and LONGVARCHARs the same, the ability to assign different separators to the latter two is provided. When a different separator is assigned to a VARCHAR or LONGVARCHAR field, it will terminate any CSV field of that type. For example, if the first field is CHAR, and the second field LONGVARCHAR, and the separator fs has been defined as the pipe (|) and vs as the period (.) then the data in the CSV file for a row will look like:

    First field data|Second field data.Third field data

The following example shows how to change the default separator to the pipe (|), VARCHAR separator to the period (.) and the LONGVARCHAR separator to the tilde (~). Place the following within the SET TABLE SOURCE statement, for example:

    SET TABLE mytable SOURCE "myfile;fs=|;vs=.;lvs=~"

HSQLDB also recognises the following special indicators for separators:

Furthermore, HSQLDB provides csv file support with three additional boolean options: ignore_first, quoted and all_quoted. The ignore_first option (default false) tells HSQLDB to ignore the first line in a file. This option is used when the first line of the file contains column headings. The all_quoted option (default false) tells the program that it should use quotes around all character fields when writing to the source file. The quoted option (default true) uses quotes only when necessary to distinguish a field that contains the separator character. It can be set to false to prevent the use of quoting altogether and treat quote characters as normal characters. These options may be specified within the SET TABLE SOURCE statement:

    SET TABLE mytable SOURCE "myfile;ignore_first=true;all_quoted=true"

When the default options all_quoted= false and quoted=true are in force, fields that are written to a line of the csv file will be quoted only if they contain the separator or the quote character. The quote character is doubled when used inside a string. When all_quoted=false and quoted=false the quote character is not doubled. With this option, it is not possible to insert any string containing the separator into the table, as it would become impossible to distinguish from a separator. While reading an existing data source file, the program treats each individual field separately. It determines that a field is quoted only if the first character is the quote character. It interprets the rest of the field on this basis.

The character encoding for the source file is ASCII by default. To support UNICODE or source files preprared with different encodings this can be changed to UTF-8 or any other encoding. The default is encoding=ASCII and the option encoding=UTF-8 or other supported encodings can be used.

Finally, HSQLDB provides the ability to read a text file from the bottom up and making them READ ONLY, by placing the keyword "DESC" at the end of the SET TABLE SOURCE statement:

    SET TABLE mytable SOURCE "myfile" DESC

This feature provides functionality similar to the Unix tail command, by re-reading the file each time a select is executed. Using this feature sets the table to read-only mode. Afterwards, it will no longer be possible to change the read-only status with SET TABLE <tablename> READONLY TRUE.

Text table source files are cached in memory. The maximum number of rows of data that are in memory at any time is controlled by the textdb.cache_scale property. The default value for textdb.cache_scale is 10 and can be changed by setting the property in the .properties file for the database. The number of rows in memory is calculated as 3*(2**scale), which translates to 3072 rows for the default textdb.cache_scale setting (10). The property can also be set for individual text tables:

    SET TABLE mytable SOURCE "myfile;ignore_first=true;all_quoted=true;cache_scale=12"

Just use one of the following protocol prefixes.

At this time, the latter will only work for clients running with Java 1.4.

If the server you wish to connect to is using a certificate approved by your default trust keystores, then there is nothing else to do. If not, then you need to tell Java to "trust" the server cert. (It's a slight over-simplification to say that if the server certificate was purchased, then you are all set; if somebody "signed their own" certificate by self-signing or using a private ca certificate, then you need to set up trust).

First, you need to obtain the cert (only the "public" part of it). Since this cert is passed to all clients, you could obtain it by writing a java client that dumps it to file, or perhaps by using openssl s_client. Since in most cases, if you want to trust a non-commercial cert, you probably have access to the server keystore, I'll show an example of how to get what you need from the server-side JKS keystore.

You may already have an X509 cert for your server. If you have a server keystore, then you can generate a X590 cert like this.

    keytool -export -keystore server.store -alias existing_alias -file server.cer

Now, you need to add this cert to one of the system trust keystores or to a keystore of your own. See the Customizing Stores section in JSSERefGuide.html to see where your system trust keystores are. You can put private keystores anywhere you want to. The following command will add the cert to an existing keystore, or create a new keystore if client.store doesn't exist.

    keytool -import -trustcacerts -keystore trust.store -alias new_alias -file server.cer

If you are making a new keystore, you probably want to start with a copy of your system default keystore which you can find somewhere under your JAVA_HOME directory (typically jre/lib/security/cacerts for a JDK, but I forget exactly where it is for a JRE).

Unless your OS can't stop other people from writing to your files, you probably do not want to set a password on the trust keystore.

If you added the cert to a system trust store, then you are finished. Otherwise you will need to specify your custom trust keystore to your client program. The generic way to set the trust keystore is to set the sytem property javax.net.ssl.trustStore every time that you run your client program. For example

    java -Djavax.net.ssl.trustStore=/home/blaine/trust.store -jar /path/to/hsqldb.jar dest-urlid

N.b. The hostname in your database URL must match the Common Name of the server's certificate exactly. That means that if a site certificate is admc.com, you can not use jdbc:hsqldb:hsqls://localhost or jdbc:hsqldb:hsqls://www.admc.com:1100 to connect to it.

If you want more details on anything, see JSSERefGuide.html on Sun's site, or in the subdirectory docs/guide/security/jsse of your Java SE docs.

Get yourself a JKS keystore containing a private key. Then set the system property javax.net.ssl.keyStore to the path to that file, and javax.net.ssl.keyStorePassword to the password of the keystore (and to the private key-- they have to be the same).

    java -Djavax.net.ssl.keyStorePassword=secret -Djavax.net.ssl.keyStore=/usr/hsqldb/db/db3/server.store  \
        -cp /path/to/hsqldb.jar org.hsqldb.Server

(This is a single command that I have broken into 2 lines using my shell's \ line-continuation feature. In this example, I'm using a server.properties file so that I don't need to give arguments to specify database instances or the server endpoint).

If there is any user demand, we will have a more secure way to supply the password before long.

I'm not going to tell you how to get a CA-signed SSL certificate. That is well documented at many other places.

Assuming that you have a standard pem-style private key certificate, here's how you can use openssl and the program DERImport to get it into a JKS keystore.

Because I have spent a lot of time on this document already, I am just giving you an example.

    openssl pkcs8 -topk8 -outform DER -in Xpvk.pem -inform PEM -out Xpvk.pk8 -nocrypt

    openssl x509 -in Xcert.pem -out Xcert.der -outform DER

    java DERImport new.keystore NEWALIAS Xpvk.pk8 Xcert.der

You need the program DERImport.class of course. Do some internet searches to find DERImport.java or DERImport.class and download it.

If DERImport has become difficult to obtain, I can write a program to do the same thing-- just let me know.

Run man keytool or see the Creating a Keystore section of JSSERefGuide.html.

This section lists changes to SqlTool since the last major release of HSQLDB. For this version of this document, that means, changes since HSQLDB versions 1.7.x.

There are many SQL types which SqlTool (being a text-based program) can't display properly. This includes the SQL types BLOB, JAVA_OBJECT, STRUCT, and OTHER. When you run a query that returns any of these, SqlTool will save the very first such value obtained to the binary buffer and will not display any output from this query. You can then save the binary value to a file, as explained in the Storing and retrieving binary files section.

There are other types, such as BINARY, which JDBC can make displayable (by using ResultSet.getString()), but which you may very well want to retrieve in raw binary format. You can use the \b command to retrieve any-column-type-at-all in raw binary format (so you can later store the value to a binary file).

Another restriction which all text-based database clients have is the practical inability for the user to type in binary data such as photos, audio streams, and serialized Java objects. You can use SqlTool to load any binary object into a database by telling SqlTool to get the insert/update datum from a file. This is also explained in the Storing and retrieving binary files section.

Desktop shortcuts and quick launch icons are useful, especially if you often run SqlTool with the same set of arguments. It's really easy to set up several of them-- one for each way that you invoke SqlTool (i.e., each one would start SqlTool with all the arguments for one of your typical startup needs). One typical setup is to have one shortcut for each database account which you normally use (use a different --urlid switch in each shortcut's Target specification.

Desktop icon setup varies depending on your Desktop manager, of course. I'll explain how to set up a SqlTool startup icon in Windows XP. Linux and Mac users should be able to take it from there, since it's easier with the common Linux and Mac desktops.

\i HSQLDB_HOME/src/org/hsqldb/sample/sampledata.sql

For memory-only databases, you'll need to run this every time that you run SqlTool. For other (persistent) databases, the data will reside in your database until you drop the tables.

This list here includes only the essential Special Commands, but n.b. that there are other useful Special Commands which you can list by running \?. (You can, for example, execute SQL from external SQL files, and save your interactive SQL commands to files). Some specifics of these other commands are specified immediately below, and the Generating Text or HTML Reports section explains how to use the "\o" and "\H" special commands to generate reports.

Be aware that the \! Special Command does not work for external programs that read from standard input. You can invoke non-interactive and graphical interactive programs, but not command-line interactive programs.

SqlTool executes \! programs directly, it does not run an operating system shell (this is to avoid OS-specific code in SqlTool). Because of this, you can give as many command-line arguments as you wish, but you can't use shell wildcards or redirection.

The \w command can be used to store any command in your SQL history to a file. Just restore the command to the buffer (which is the 0th element of the history) with a command like "\-4" before you give the \w command.

Note that PL commands are used to upload and download column values to/from local ASCII files, but the corresponding actions for binary files use the special \b commands. This is because PL variables are used for ASCII values and you can store any number of column values in PL variables. This is not true for binary column values. The \b commands work with a single binary byte buffer.

See the SqlTool Procedural Language section below for information on using variables in other ways, and information on the other PL commands and features.

You can upload binary files such as photographs, audio files, or serialized Java objects into database columns. SqlTool keeps one binary buffer which you can load from files with the \bl command, or from a database query by doing a one-row query for any non-displayable type (including BLOB, OBJECT, and OTHER). In the latter case, the data returned for the first non-displayable column of the first result row will be stored into the binary buffer.

Once you have data in the binary buffer, you can upload it to a database column (including BLOB, OBJECT, and OTHER type columns), or save it to a file. The former is accomplished by the special command \bp followed by a prepared SQL query containing one question mark place-holder to indicate where the data gets inserted. The latter is accomplished with the \bd command.

You can also store the output from normal, displayable column into the binary buffer by using the special command \b. The very first column value from the first result row of the next SQL command will be stored to the binary byte buffer.

    \bl /tmp/favoritesong.mp3
    \bp
    INSERT INTO musictbl (id, stream) VALUES(3112, ?);

    SELECT stream FROM musictbl WHERE id = 3112;
    \bd /tmp/favoritesong.mp3

You can also store and retrieve text column values to/from ASCII files, as documented in the Essential PL Command section.

The SQL history shown by the \s command, and used by other commands, is truncated to 20 entries, since the utility comes from being able to quickly view the history list. You can change the history length by setting the system property sqltool.historyLength to an integer like

java -Dsqltool.historyLength=40 -jar $HSQLDB_HOME/lib/hsqldb.jar urlid

The SQL history list explicitly does not contain Special, Buffer, or PL commands. It only contains SQL commands, valid or invalid, successful or unsuccessful. The reason for including bad SQL commands is so that you can recall and edit them if you want to. The same applies to the editing buffer (which is element 0 of the history).

You normally use non-interactive mode for piping. You specify "-" as the SQL file name. See the Piping and shell scripting subsection of the Non-Interactive chapter.

You can run SqlTool interactively, but have SqlTool behave exactly as if it were processing an SQL file (i.e., no command-line prompts, error-handling that defaults to fail-upon-error, etc.). Just specify "-" as the SQL file name in the command line. This is a good way to test what SqlTool will do when it encounters any specific command in an SQL file. See the Piping and shell scripting subsection of the Non-Interactive chapter for an example.

If you just have a couple SQL commands to run, you can run them directly from the comand-line or from a shell script without an SQL file, like this.

    java -jar $HSQLDB_HOME/lib/hsqldb.jar --sql 'SQL statement' urlid

Since SqlTool transmits SQL statements to the database engine only when a line is terminated with ";", if you want feedback from multiple SQL statements in an --sql expression, you will need to use functionality of your OS shell to include linebreaks after the semicolons in the expression. With any Bourne-compatible shell, you can include linebreaks in the SQL statements like this.

    java -jar $HSQLDB_HOME/lib/hsqldb.jar --sql 'SQL statement' urlid '
        SQL statement number one;
        SQL statement
            number two;
        SQL statement three;
    ' urlid

The --sql switch is very useful for setting shell variables to the output of SQL Statements, like this.

    # A shell script
    USERCOUNT=`java -jar $HSQLDB_HOME/lib/hsqldb.jar --sql 'select count(*) from usertbl' urlid` || {
        # Handle the SqlTool error
    }
    echo "There are $USERCOUNT users registered in the database."
    [ "$USECOUNT" -gt 3 ] && {   # If there are more than 3 users registered
        # Some conditional shell scripting

Just give paths to sql text file(s) on the command line after the urlid.

Often, you will want to redirect output to a file, like

java -jar $HSQLDB_HOME/lib/hsqldb.jar sql... > /tmp/log.sql 2>&1

(Skip the "2>&1" if you're on Windows).

You can also execute SQL files from an interactive session with the "\i"' Special Command, but be aware that the default behavior in an interactive session is to continue upon errors. If the SQL file was written without any concern for error handling, then the file will continue to execute after errors occur. You could run \c false before \i filename, but then your SqlTool session will exit if an error is encountered in the SQL file. If you have an SQL file without error handling, and you want to abort that file when an error occurs, but not exit SqlTool, the easiest way to accomplish this is usually to add \c false to the top of the script.

If you specify multiple SQL files on the command-line, the default behavior is to exit SqlTool if any of the SQL files encounters an error.

SQL files themselves have ultimate control over error handling. Regardless of what command-line options are set, or what commands you give interactively, if a SQL file gives error handling statements, they will take precedence.

You can also use \i in SQL files. This results in nested SQL files.

You can use the following SQL file, sample.sql, which resides in the src/org/hsqldb/sample directory of your HSQLDB distribution. It contains SQL as well as Special Commands making good use of most of the Special Commands documented below.

/*
    $Id: sample.sql,v 1.5 2005/05/02 15:07:27 unsaved Exp $
    Examplifies use of SqlTool.
    PCTASK Table creation
*/

/* Ignore error for these two statements */
\c true
DROP TABLE pctasklist;
DROP TABLE pctask;
\c false

\p Creating table pctask
CREATE TABLE pctask (
    id integer identity,
    name varchar(40),
    description varchar,
    url varchar,
    UNIQUE (name)
);

\p Creating table pctasklist
CREATE TABLE pctasklist (
    id integer identity,
    host varchar(20) not null,
    tasksequence int not null,
    pctask integer,
    assigndate timestamp default current_timestamp,
    completedate timestamp,
    show bit default true,
    FOREIGN KEY (pctask) REFERENCES pctask,
    UNIQUE (host, tasksequence)
);

\p Granting privileges
GRANT select ON pctask TO public;
GRANT all ON pctask TO tomcat;
GRANT select ON pctasklist TO public;
GRANT all ON pctasklist TO tomcat;

\p Inserting test records
INSERT INTO pctask (name, description, url) VALUES (
    'task one', 'Description for task 1', 'http://cnn.com');
INSERT INTO pctasklist (host, tasksequence, pctask) VALUES (
    'admc-masq', 101, SELECT id FROM pctask WHERE name = 'task one');

commit;

You can execute this SQL file with a Memory Only database with a command like

    java -jar $HSQLDB_HOME/lib/hsqldb.jar  --sql "create user 'tomcat' password 'x'" mem path/to/hsqldb/src/org/hsqldb/sample/sample.sql

(The --sql "create..." arguments create an account which the script uses).

You can of course, redirect output from SqlTool to a file or another program.

    java -jar $HSQLDB_HOME/lib/hsqldb.jar urlid file.sql > file.txt 2>&1

    java -jar $HSQLDB_HOME/lib/hsqldb.jar urlid file.sql 2>&1 | someprogram...

You can type commands in to SqlTool while being in non-interactive mode by supplying "-" as the file name. This is a good way to test how SqlTool will behave when processing your SQL files.

        java -jar $HSQLDB_HOME/lib/hsqldb.jar urlid -

This is how you have SqlTool read its input from another program:

        echo "Some SQL commands with '$VARIABLES';" |
        java -jar $HSQLDB_HOME/lib/hsqldb.jar urlid -

Make sure that you also read the Giving SQL on the Command Line section. The --sql switch is a great facility to use with shell scripts.

If you want your SQL scripts optimally compatible among other SQL tools, then don't use any Special or PL Commands. SqlTool has default behavior which I think is far superior to the other SQL tools, but you will have to disable these defaults in order to have optimally compatible behavior.

These switches provide compatibilty at the cost of poor control and error detection.

You don't have to worry about accidental expansion of PL variables, since SqlTool will never expand PL variables if you don't set any variables on the command line, or give any "* " PL commands. (And you could not have "* " commands in a compatible SQL file).

SQL comments of the form /*...*/ must begin where a (SQL/Special/Buffer/PL) Command could begin, and they end with the very first "*/" (regardless of quotes, nesting, etc. You may have as many blank lines as you want inside of a comment.

    SELECT count(*) FROM atable;
    /* Lots of
     comments interspersed among
     several lines */   SELECT count(*)
    FROM btable;

Notice that a command can start immediate after the comment ends.

    SELECT count(*) FROM
    /* atable */
    btable;

This comment is invalid because you could not start another command at the comment location (because it is within an SQL Statement).

You can try using /*...*/ in other locations, and -- style SQL comments, but SqlTool will not treat them as comments. If they occur within an SQL Statment, SqlTool will pass them to the database engine, and the DB engine will determine whether to parse them as comments.

Don't use Buffer Commands in your sql files, because they won't work. Buffer Commands are for interactive use only. (But, see the Raw Mode section for an exception).

SqlTool is ideal for mission-critical automation because, unlike other SQL tools, SqlTool returns a dependable exit status and gives you control over error handling and SQL transactions. Autocommit is off by default, so you can build a completely dependable solution by intelligently using \c commands (Continue upon Errors) and commit statements, and by verifying exit statuses.

Using the SqlTool Procedural Language, you have ultimate control over program flow, and you can use variables for database input and output as well as for many other purposes. See the SqlTool Procedural Language section.

Some script developers may run into cases where they want to run with sql files but they alwo want SqlTool's interactive behavior. For example, they may want to do command recall in the sql file, or they may want to log SqlTool's command-line prompts (which are not printed in non-interactive mode). In this case, do not give the sql file(s) as an argument to SqlTool, but pipe them in instead, like

java -jar $HSQLDB_HOME/lib/hsqldb.jar urlid < filepath1.sql > /tmp/log.html 2>&1

cat filepath1.sql... |
java -jar $HSQLDB_HOME/lib/hsqldb.jar urlid > /tmp/log.html 2>&1

PL Aliasing just means the use of a PL variable as the first thing in an SQL statement, with the shortcut notation /VARNAME.

/VARNAME must be followed by whitespace or terminate the Statement, in order for SqlFile to tell where the variable name ends.

If the value of a variable is an entire SQL command, you generally do not want to include the terminating ";" in the value. There is an example of this above.

PL aliasing may only be used for SQL statements. You can define variables for everything in a Special or PL Command, except for the very first character ("\" or "*"). Therefore, you can use variables other than alias variables in Special and PL Commands. Here is a hyperbolically impractical example to show the extent to which PL variables can be used in Special commands even though you can not use them as PL aliases.

        sql> * qq = p Hello Butch
        sql> \*{qq} done now
        Hello Butch done now

Logical expressions occur only inside of logical expression parentheses in PL statements. For example, if (*var1 > astring) and while (*checkvar). (The parentheses after "foreach" do not enclose a logical expression, they just enclose a list).

There is a critical difference between *{varname} and *VARNAME inside logical expressions. *{varname} is expanded one time when the parser first encounters the logical expression. *VARNAME is re-expanded every time that the expression is evaluated. So, you would never want to code * while (*{x} < 5) because the statement will always be true or always be false. (I.e. the following block will loop infinitely or will never run).

Don't use quotes or whitespace of any kind in *{varname} variables in expressions. (They would expand and then the expression would most likely no longer be a valid expression as listed in the table below). Quotes and whitespace are fine in *VARNAME variables, but it is the entire value that will be used in evaluations, regardless of whether quotes match up, etc. I.e. quotes and whitespace are not special to the token evaluator.

*VARNAMEs in logical expressions, where the VARNAME variable is not set, evaluate to an empty string. Therefore (*UNSETVAR = 0) would be false, even though (*UNSETVAR) by itself is false and (0) by itself is false.

When developing scripts, you definitely use SqlTool interactively to verify that SqlTool evaluates logical expressions as you expect. Just run * if commands that print something (i.e. \p) if the test expression is true.

Flow control works by conditionally executing blocks of Commands according to conditions specified by logical expressions.

The conditionally executed blocks are called PL Blocks. These PL Blocks always occur between a PL flow control statement (like * foreach, *while, * if) and a corresponding * end PL Command (like * end foreach).

The values of control variables for foreach and while PL blocks will change as expected.

There are * break and * continue, which work as any shell scripter would expect them to. The * break command can also be used to quit the current SQL file without triggering any error processing. (I.e. processing will continue with the next line in the including SQL file or interactive session, or with the next SQL file if you supplied multiple on the command-line).

The first general reason to chunk SQL commands is performance. For standalone databases, the most common performance bottleneck is network latency. Chunking SQL commands can dramatically reduce network traffic.

The second general reason to chunk SQL commands is if your database requires you to send multiple commands in one transmission. This is often the case when you need to tell the database the SQL or PL/SQL commands that comprise a stored procedure, function, trigger, etc.

The most simple way is enter as many SQL commands as you want, but just do not end a line with ";" until you want the chunk to transmit.

    INSERT INTO t1 VALUES (1)
    ; INSERT INTO t1 VALUES (2)
    ; SELECT * FROM t1;

The other method is by using Raw Mode. Go to the Raw Mode section to see how. You can enter any text at all, exactly how you want it to be sent to the database engine. Therefore, in addition to chunking SQL commands, you can give commands for non-SQL extensions to the database. For example, you could enter JavaScript code to be used in a stored procedure.

Le nom de l'index peut être changé tant qu'il ne rentre pas en conflit avec un autre nom d'objet défini par l'utilisateur ou un autre nom d'objet système.

Réinitialise la prochaine valeur retournée par la sequence.

Renome le shéma comme spécifié. Tous les objects du shéma seront accessibkle ensuite seulement avec le nouveau nom de shéma.

Nécéssite le privilèges administrateur.

Ajoute une colonne à la fin de la liste des colonnes. L'option BEFORE <existingcolumn> peut être utilisé pour indiquer avant quelle colonne doit être inserer la nouvelle colonne.

Sont acceptées le même définitions que celle de l'instruction CREATE TABLE. Si la condition NOT NULL est spécifiée et que la table n'est pas vide, une valeur par défaut doit être spécifiée.

Si une vue contient SELECT * FROM <tablename> dans son scripte de création, la nouvelle colonne et aussi ajoutée à la vue (ouaaaa ! super !) C'est une fonction non standart susceptible de changer dans le future. (ooohhh ! pfff !!!)

ALTER TABLE <tablename> DROP [COLUMN] <columnname>;

Enlève une colonne de la table. Les clé primaire sur colonne unique ou contraintes associées à la colonne à effacer tombent avec.
La commande est inopérante si'il existe une cle sur multiple colonne contenant cette dernière ou si la colonne à effacer est referencée par une cle etrangère. Cela plantera aussi si cette colonne est contenu dans le script SQL d'une vue.

It will also fail if an SQL view includes the column.

ALTER TABLE <tablename> ALTER COLUMN <columnname> RENAME TO <newname> 

Change le nom de colonne.

ALTER TABLE <tablename> ALTER COLUMN <columnname> SET DEFAULT <defaultvalue>};

Ajoute une valeur par défaut sur la colonne. Utiliser la valeur NULL pour enlever une valeur par défaut

ALTER TABLE <tablename> ALTER COLUMN <columnname> SET [NOT] NULL

Mets ou enlève la contrainte NOT NULL sur une colonne.

ALTER TABLE <tablename> ALTER COLUMN <columnDefinition>;

Cette combinaison de commande ALTER TABLE ALTER COLUMN accepte les Definitions de colonnes de la commande CREATE TABLE , avec les restrictions suivantes :

ALTER TABLE <tablename> ALTER COLUMN <columnname> RESTART WITH <new sequence vale>

This form is used exclusively for IDENTITY columns and changes the next automatic value for the identity sequence.

ALTER TABLE <tablename> ADD [CONSTRAINT <constraintname>] CHECK (<search condition>);

Adds a check constraint to the table. In the current version, a check constraint can reference only the row being inserted or updated.

ALTER TABLE <tablename> ADD [CONSTRAINT <constraintname>] UNIQUE (<column list>);

Adds a unique constraint to the table. This will not work if there is already a unique constraint covering exactly the same <column list>.

This will work only if the values of the column list for the existing rows are unique or include a null value.

ALTER TABLE <tablename> ADD [CONSTRAINT <constraintname>] PRIMARY KEY (<column list>);

Adds a primary key constraint to the table, using the same constraint syntax as when the primary key is specified in a table definition.

ALTER TABLE <tablename> ADD [CONSTRAINT <constraintname>] FOREIGN KEY (<column list>) REFERENCES <exptablename> (<column list>)
    [ON {delete | update} {CASCADE | SET DEFAULT | SET NULL}];

Adds a foreign key constraint to the table, using the same constraint syntax as when the foreign key is specified in a table definition.

This will fail if for each existing row in the referring table, a matching row (with equal values for the column list) is not found in the referenced tables.

ALTER TABLE <tablename> DROP CONSTRAINT <constraintname>;

Drop a named unique, check or foreign key constraint from the table.

ALTER TABLE <tablename> RENAME TO <newname>;

Change le mot de passe d'un utilisateur existant. Le mot de pass doit encadré de doubles quotes. Utiliser "" pour les mot de passe vides.

Nécéssite le privilèges administrateur.

Une expression peut être appelée comme une procedure stockée, Y compris, mais pas seulement des procedures Java ou des fonctions.

Cette commande retourne un résultat avec une colonne et une ligne comme si un select retournant une seule ligne une seule colonne avaut été passé.

Voir: Stored Procedures / Functions, SQL Expression.

Ferme le fichier de Base de données, réécrit les fichiers de scripts, efface le fichier de log et celui de la base ouverte.

Si l'option DEFRAG est spécifiée la commande optimise le fichier .data dans sa taille minimale.

Voir: SHUTDOWN, SET LOGSIZE.

Termine une transaction et effectuie les changements de manière permanente

Voir: ROLLBACK, SET AUTOCOMMIT, SET LOGSIZE.

Se connecte à la BD avec un utilisateur différent. Le mot de pass doit encadré de doubles quotes. Utiliser "" pour les mot de passe vides.

Voir: GRANT, REVOKE.

Créer un Alias pour une fonction JAVA. La fonction doit être accessible par la JVM avec laquelle la BD est executée. Exemple :

    CREATE ALIAS ABS FOR "java.lang.Math.abs";

Voir: CALL, Stored Procedures / Functions.

Crééer un index sur une ou plusiezurs colonnes dans la table.

Créer un index sur les colonnes utilisées dans les recherches peut augmenter les performances. Le complement DESC peut être présent pour rendre compatible la commande avec d'autre base de données, mais cela n'a pas d'autre effet. L'index unique peut être défini mais cela est desuet. Utiliser à la place la contrainte UNIQUE . Le nom de l'index doiot être unique au travers de toute la base.

Voir: CREATE TABLE, DROP INDEX.

Créer une rôle nomé sans membre. (??? j'ai laissé lla phrase d'origine endessous ???) Nécéssite le privilèges administrateur
Creates the named role with no members. Requires Administrative privileges.

Créé un shéma nomé.

Nécéssite le privilèges administrateur

Les options CREATE et GRANT ne peuven,t être données que pour les nouveaux objets du shéma. Seul le dernier état nested doit être terminé avec un poit virgule, car un point virgule rencontré apres un "CREATE SCHEMA" terminera la commande "CREATE SCHEMA". Dans l'exemple ci-dessous, un nouveau schéma,  ACCOUNTS est créé, ainsi que deux tables et des vues sont ajoutées à ce shéma, puis sont ajoutées quelque règle de droit sur ces objets.

    CREATE SCHEMA ACCOUNTS AUTHORIZATION DBA
    CREATE TABLE AB(A INTEGER, ...)
    CREATE TABLE CD(C CHAHR, ...)
    CREATE VIEW VI AS SELECT ...
    GRANT SELECT TO PUBLIC ON AB
    GRANT SELECT TO JOE ON CD;

Requires Administrative privileges.

Créé une sequence. Le type par défaut est INTEGER. La valeur par défaut est 0 et l'incrément est 1.
Les valeurs négatives ne sont pas autorisées.

If a sequence goes beyond Integer.MAXVALUE or Long.MAXVALUE, the next result is determined by 2's complement arithmetic.

La prochaine valeur pour une sequence peut être inclues dans un SELECT, INSERT et un UPDATE comme dans les exemples suivants:

SELECT [...,] NEXT VALUE FOR <sequencename> [, ...] FROM <tablename>

En SQL 200n et dans la version courrante il n'y a pas de possibilité de retrouver la dernière valeur de la sequence retournée.

CREATE [MEMORY | CACHED | [GLOBAL] TEMPORARY | TEMP [1] | TEXT[1]] TABLE <name>
( <columnDefinition> [, ...] [, <constraintDefinition>...] ) [ON COMMIT {delete | preserve} ROWS];

Creates a tables in memory (default) or on disk and only cached in memory. If the database is all-in-memory, both MEMORY and CACHED forms of CREATE TABLE return a MEMORY table while the TEXT form is not allowed.

See also: DROP TABLE.

CREATE TRIGGER <name> {before | after} {INSERT | UPDATE | DELETE} ON <table> [FOR EACH ROW] [QUEUE n] [NOWAIT] CALL <TriggerClass>;

TriggerClass is an application-supplied class that implements the org.hsqldb.Trigger interface e.g. "mypackage.TrigClass". It is the fire method of this class that is invoked when the trigger event occurs. You should provide this class, which can have any name, and ensure that this TriggerClass is present in the classpath which you use to start hsqldb.

Since 1.7.2 the implementation has been changed and enhanced. When the 'fire' method is called, it is passed the following arguments:

    fire (String name, String table, Object row1[], Object row2[])

where 'row1' and 'row2' represent the 'before' and 'after' states of the row acted on, with each column being a member of the array. The mapping of members of the row arrays to database types is specified in Data Types. For example, BIGINT is represented by a java.lang.Long Object. Note that the number of elements in the row arrays is larger than the number of columns by one or two elements. Never modify the last elements of the array, which are not part of the actual row.

If the trigger method wants to access the database, it must establish its own JDBC connection. This can cause data inconsistency and other problems so it is not recommended. The jdbc:default:connection: URL is not currently supported.

Implementation note:

If QUEUE 0 is specified, the fire method is execued in the same thread as the database engine. This allows trigger action to alter the data that is about to be stored in the database. Data can be checked or modified in BEFORE INSERT / UPDATE + FOR EACH ROW triggers. All table constraints are then enforced by the database engine and if there is a violation, the action is rejected for the SQL command that initiated the INSERT or UPDATE. There is an exception to this rule, that is with UPDATE queries, referential integrity and cascading actions resulting from ON UPDATE CASCASE / SET NULL / SET DEFAULT are all performed prior to the invocation of the trigger method. If an invalid value that breaks referential integrity is inserted in the row by the trigger method, this action is not checked and results in inconsistent data in the table.

Alternatively, if the trigger is used for external communications and not for checking or altering the data, a queue size larger than zero can be specified. This is in the interests of not blocking the database's main thread as each trigger will run in a thread that will wait for its firing event to occur. When this happens, the trigger's thread calls TriggerClass.fire. There is a queue of events waiting to be run by each trigger thread. This is particularly useful for 'FOR EACH ROW' triggers, when a large number of trigger events occur in rapid succession, without the trigger thread getting a chance to run. If the queue becomes full, subsequent additions to it cause the database engine to suspend awaiting space in the queue. Take great care to avoid this situation if the trigger action involves accessing the database, as deadlock will occur. This can be avoided either by ensuring the QUEUE parameter makes a large enough queue, or by using the NOWAIT parameter, which causes a new trigger event to overwrite the most recent event in the queue. The default queue size is 1024. Note also that the timing of trigger method calls is not guaranteed, so applications should implement their own synchronization measures if necessary.

With a non-zero QUEUE parameter, if the trigger methods modifies the 'row2' values, these changes may or may not affect the database and will almost certainly result in data inconsistency.

Please refer to the code for org.hsqldb.sample.Trigger and org.hsqldb.sample.TriggerSample for more information on how to write a trigger class.

See also: DROP TRIGGER.

Créer un nouvel utilisateur ou un nouvel administrateur de BD. Le mot de passe doit être entre double-quotes. Unmot passe peut être vide, auquel cas utiliser "". Il est possible de changer le mot de passe vai la comande ALTER USER^[1] .

Nécéssite les droits administrateur.

Voir: CONNECT, GRANT, REVOKE. ALTER USER^[1],

CREATE VIEW <viewname>[(<viewcolumn>,..) AS SELECT ... FROM ... [WHERE Expression];
[ORDER BY orderExpression [, ...]]
[LIMIT <limit> [OFFSET <offset>]];

A view can be thought of as either a virtual table or a stored query. The data accessible through a view is not stored in the database as a distinct object. What is stored in the database is a SELECT statement. The result set of the SELECT statement forms the virtual table returned by the view. A user can use this virtual table by referencing the view name in SQL statements the same way a table is referenced. A view is used to do any or all of these functions:

Views are created by defining the SELECT statement that retrieves the data to be presented by the view. The data tables referenced by the SELECT statement are known as the base tables for the view. In this example, is a view that selects data from three base tables to present a virtual table of commonly needed data:

    CREATE VIEW mealsjv AS
      SELECT m.mid mid, m.name name, t.mealtype mt, a.aid aid,
             a.gname + ' ' + a.sname author, m.description description,
             m.asof asof
        FROM meals m, mealtypes t, authors a
       WHERE m.mealtype = t.mealtype
        AND m.aid = a.aid;

You can then reference mealsjv in statements in the same way you would reference a table:

    SELECT * FROM mealsjv;

A view can reference another view. For example, mealsjv presents information that is useful for long descriptions that contain identifiers, but a short list might be all a web page display needs. A view can be built that selects only specific mealsjv columns:

    CREATE VIEW mealswebv AS SELECT name, author FROM mealsjv;

The SELECT statement in a VIEW definition should return columns with distinct names. If the names of two columns in the SELECT statement are the same, use a column alias to distinguish between them. A list of new column names can always be defined for a view.

    CREATE VIEW aview (new_name, new_author) AS
      SELECT name, author
      FROM mealsjv

See also: SQL Expression, SELECT^[1], DROP VIEW^[1].

Efface une occurence d'une table.

Voir SQL Expression, INSERT, SELECT^[1].

Ferme la connexion.
Il n'est pas nécéssaire d'appeler cette commande lorsque l'interface JDBC est utilisée car elle est appelée automatiquement. Une fois déconnecté, il n'ets plus possibkle d'executer d'autres requetes y compris CONNECT avec cette connexion.

Voir: CONNECT.

Retire l'index spécifié de la BD. Cela ne marche pas avec les indexes UNIQUE des clé étrangères FOREIGN KEY .

Voir: CREATE INDEX.

Retire tous les membres du rôle spécifié, puis efface le rôle lui même.

Removes the specified sequence from the database. When IF EXIST is used, the statement returns without an error if the sequence does not exist. The RESTRICT option is in effect by default, meaning that DROP will fail if any view reference the sequence. Specify the CASCADE option to silently drop all dependent database objects.

Removes the specified schema from the database. The RESTRICT option is in effect by default, meaning that DROP will fail if any objects such as tables or sequences have been defined in the schema. Specify the CASCADE option to silently drop all database objects in the schema.

Requires Administrative privileges.

Removes a table, the data and indexes from the database. When IF EXIST is used, the statement returns without an error even if the table does not exist.

The RESTRICT option is in effect by default, meaning that DROP will fail if any tables or views refer to this table. Specify the CASCADE option to silently drop all dependent views, and to drop any foreign key constraint that links this table with other tables.

See also:

CREATE TABLE.

Efface le trigger spécifié d'une BD.

Voir: CREATE TRIGGER^[1].

Efface l'utilisateur d'une BD

Nécéssite les droits administrateur.

Voir: CREATE USER.

Removes a view from the database. When IF EXIST is used, the statement returns without an error if the view does not exist. The RESTRICT option is in effect by default, meaning that DROP will fail if any other view refers to this view. Specify the CASCADE option to silently drop all dependent views.

See also: CREATE VIEW^[1].

EXPLAIN PLAN FOR can be used with any query to get a detailed list of the elements in the execution plan.

This list includes the indexes used for performing the query and can be used to optimise the query or to add indexes to tables.

GRANT <rolename> [,...] TO <grantee>[1];

<grantee> is either a user name, a role name, or PUBLIC. PUBLIC means all users.

The first form of the GRANT command assigns privileges to a grantee for a table or for a class. To allow a user to call a function from a class, the right ALL must be used. Examples:

    GRANT SELECT ON Test TO GUEST;
    GRANT ALL ON CLASS "java.lang.String" TO PUBLIC;

The second form of the GRANT command gives the specified <grantee> membership in the specified role.

Require Administrative privileges.

See also: REVOKE, CREATE USER, CREATE ROLE^[1].

Ajoute une ou plusieurs occurences dans la table.

REVOKE { SELECT | DELETE | INSERT | UPDATE | ALL } [,...]
ON { table | CLASS "package.class" } FROM <grantee>;

REVOKE <rolename> [,...] FROM <grantee>[1];

<grantee> is either a user name, a role name, or PUBLIC. PUBLIC means all users.

The first form of the REVOKE command withdraws privileges from a grantee for a table or for a class.

The second form of the REVOKE command withdraws membership of the specified <grantee> from the specified role.

Both forms require Administrative privileges.

See also: GRANT.

ROLLBACK used on its own, or with WORK, undoes changes made since the last COMMIT or ROLLBACK.

ROLLBACK TO SAVEPOINT <savepoint name> undoes the change since the named savepoint. It has no effect if the savepoint is not found.

See also: COMMIT.

Sets up a SAVEPOINT for use with ROLLBACK TO SAVEPOINT.

See also: COMMIT.

SCRIPT ['file'];

Creates an SQL script describing the database. If the file is not specified, a result set containing only the DDL script is returned. If the file is specified then this file is saved with the path relative to the machine where the database engine is located.

Only an administrator may do this.

SELECT [{LIMIT <offset> <limit> | TOP <limit>}[1]][ALL | DISTINCT]
{ selectExpression | table.* | * } [, ...]
[INTO [CACHED | TEMP  | TEXT][1] newTable]
FROM tableList
[WHERE Expression]
[GROUP BY Expression [, ...]]
[HAVING Expression]
[{ UNION [ALL | DISTINCT] | {MINUS [DISTINCT] | EXCEPT [DISTINCT] } | INTERSECT [DISTINCT] } selectStatement]
[ORDER BY orderExpression [, ...]]
[LIMIT <limit> [OFFSET <offset>]];

Rechercher les informations depuis une ou plusieurs table de la BD.

Voir: INSERT, UPDATE, DELETE.

SET AUTOCOMMIT { true | false };

Switches on or off the connection's auto-commit mode. If switched on, then all statements will be committed as individual transactions. Otherwise, the statements are grouped into transactions that are terminated by either COMMIT or ROLLBACK. By default, new connections are in auto-commit mode. This command should not be used directly. Use the JDBC equivalent method, Connection.setAutoCommit(boolean autocommit).

SET DATABASE COLLATION <double quoted collation name>;

Each database can have its own collation. Sets the collation from the set of collations in the source for org.hsqldb.Collation. Once this command has been issued, the database can be opened in any JVM and will retain its collation.

Once this command has been issued, the database can be opened in any JVM and will retain its collation.

SET CHECKPOINT DEFRAG <size>;

The parameter size is the megabytes of abandoned space in the .data file. When a CHECKPOINT is performed either as a result of the .log file reaching the limit set by "SET LOGSIZE size", or by the user issuing a CHECKPOINT command, the amount of space abandoned during the session is checked and if it is larger than size, a CHECKPOINT DEFRAG is performed instead of a checkpoint.

SET IGNORECASE { true | false };

Disables (ignorecase = true) or enables (ignorecase = false) the case sensitivity of text comparison and indexing for new tables. By default, character columns in new databases are case sensitive. The sensitivity must be switched before creating tables. Existing tables and their data are not affected. When switched on, the data type VARCHAR is set to VARCHAR_IGNORECASE in new tables. Alternatively, you can specify the VARCHAR_IGNORECASE type for the definition of individual columns. So it is possible to have some columns case sensitive and some not, even in the same table.

Only an administrator may do this.

SET LOGSIZE <size>;

Sets the maximum size in MB of the .log file. Default is 200 MB. The database will be closed and opened (just like using CHECKPOINT) if the .log file gets over this limit, and so the .log file will shrink. 0 means no limit.

See also: CHECKPOINT.

Change le mot de passe de l'utilisateur courrant connecté.

Sets a database property. Properties that can be set using this command are either boolean or integral and are listed in the Advanced Topics chapter.

SET REFERENTIAL_INTEGRITY { true | false };

This commands enables / disables the referential integrity checking (foreign keys). Normally it should be switched on (this is the default) but when importing data (and the data is imported in the 'wrong' order) the checking can be switched off.

Only an administrator may do this.

See also: CREATE TABLE.

SET SCHEMA <schemaname>;

Sets the current JDBC session's schema. The sole purpose for the session schema is to provide a default schema name for schema objects that do not have the schema name specified explicitly in the SQL command, or by association with another object of known schema. For example, if you run SELECT * FROM atbl;, HSQLDB will look for the table or view named atbl in the session's current schema.

Session schemas last only for the duration of the current session. When a new JDBC session is obtained, the new session will have the default schema.

SET SCRIPTFORMAT {TEXT | BINARY | COMPRESSED};

Changes the format of the script file. BINARY and COMPRESSED formats are slightly faster and more compact than the default TEXT. Recommended only for very large script files.

SET TABLE tableName INDEX 'index1rootPos index2rootPos ... ';

This command is only used internally to store the position of index roots in the .data file. It appears only in database script files; it should not be used directly.

Positionne la table en lecture seule.

SET TABLE <tablename> SOURCE <file and options> [DESC];

For details see the Text Tables chapter.

This command is used exclusively with TEXT tables to specify which file is used for storage of the data. The optional DESC qualifier results in the text file indexed from the end and opened as readonly. The <file and options> argument is a double quoted string that consists of:

    <file and options>::= <doublequote> <filepath> [<semicolon> <option>...] <doublequote>

Example:

    SET TABLE mytable SOURCE "myfile;fs=|;vs=.;lvs=~"

Only an administrator may do this.

SET WRITE_DELAY {{ true | false } | <seconds> | <milliseconds> MILLIS};

This controls the frequency of file sync for the log file. When WRITE_DELAY is set to FALSE or 0, the sync takes place immediately at each COMMIT. WRITE_DELAY TRUE performs the sync once every 20 seconds (which is the default). A numeric value can be specified instead.

The purpose of this command is to control the amount of data loss in case of a total system crash. A delay of 1 second means at most the data written to disk during the last second before the crash is lost. All data written prior to this has been synced and should be recoverable.

A write delay of 0 impacts performance in high load situations, as the engine has to wait for the file system to catch up.

To avoid this, you can set write delay down to 10 milliseconds. In practice, a write delay of 100 milliseconds provides better than 99.9999% reliability with an average one system crash per day, or 99.99999% with an average one system crash per 6 days.

Each time a SET WRITE_DELAY is issued with any value, a sync is immediately performed.

Only an administrator may do this.

Closes the current database.

Only an administrator may use the SHUTDOWN command.

Modifie les dottées de la table dans la BD.

Voir: SELECT^[1], INSERT, DELETE.