litebase
Class LitebaseConnection

java.lang.Object
  litebase.LitebaseConnection

public class LitebaseConnection
extends java.lang.Object

This class is the one used to issue SQL commands. Read Litebase Companion chapters for more information.

Field Summary

static int	language The language of the Litebase messages.
static int	LANGUAGE_EN English language.
static int	LANGUAGE_PT Portuguese language.
static totalcross.util.Logger	logger The logger.
static int	version The integer corresponding to the current Litebase version.
static java.lang.String	versionStr The string corresponding to the current Litebase version.

Constructor Summary

LitebaseConnection()

Method Summary

void	closeAll() Releases the file handles (on the device) of a Litebase instance.
void	convert(java.lang.String tableName) Converts a table from the previous Litebase table version to the current one.
static int	deleteLogFiles() Deletes all log files found in the device.
void	execute(java.lang.String sql) Used to execute a create table or create index SQL commands.
ResultSet	executeQuery(java.lang.String sql) Used to execute queries in a table.
int	executeUpdate(java.lang.String sql) Used to execute updates in a table (insert, delete, update, alter table, drop).
boolean	exists(java.lang.String tableName) Indicates if the given table already exists.
int	getCurrentRowId(java.lang.String tableName) Returns the current rowid for a given table.
static totalcross.util.Logger	getDefaultLogger() Gets the default Litebase logger.
static LitebaseConnection	getInstance() Creates a Litebase connection for the default creator id, storing the database as a flat file.
static LitebaseConnection	getInstance(java.lang.String appCrid) Creates a Litebase connection for the given creator id, storing the database as a flat file.
static LitebaseConnection	getInstance(java.lang.String appCrid, java.lang.String params) Creates a LitebaseConnection for the given creator id and with the given connection param list.
static totalcross.util.Logger	getLogger() Gets the Litebase logger.
int	getRowCount(java.lang.String tableName) Returns the number of valid rows in a table.
int	getRowCountDeleted(java.lang.String tableName) Returns the number of deleted rows.
RowIterator	getRowIterator(java.lang.String tableName) Gets an iterator for a table.
java.lang.String	getSourcePath() Returns the path where the tables created/opened by this connection are stored.
PreparedStatement	prepareStatement(java.lang.String sql) Creates a pre-compiled statement with the given sql.
static LitebaseConnection	processLogs(java.lang.String[] sql, java.lang.String params, boolean isDebug) This is a handy method that can be used to reproduce all commands of a log file.
int	purge(java.lang.String tableName) Used to delete physically the records of the given table.
boolean	recoverTable(java.lang.String tableName) Tries to recover a table not closed properly by marking and erasing logically the records whose crc are not valid.
static void	setLogger(totalcross.util.Logger logger) Sets the litebase logger.
void	setRowInc(java.lang.String tableName, int inc) Sets the row increment used when creating or updating big amounts of data.

Methods inherited from class java.lang.Object

equals, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Field Detail

LANGUAGE_EN

public static final int LANGUAGE_EN

English language.

See Also:

Constant Field Values

LANGUAGE_PT

public static final int LANGUAGE_PT

Portuguese language.

See Also:

Constant Field Values

versionStr

public static final java.lang.String versionStr

The string corresponding to the current Litebase version.

See Also:

Constant Field Values

version

public static final int version

The integer corresponding to the current Litebase version.

See Also:

Constant Field Values

logger

public static totalcross.util.Logger logger

The logger.

language

public static int language

The language of the Litebase messages.

Constructor Detail

LitebaseConnection

public LitebaseConnection()

Method Detail

getInstance

public static LitebaseConnection getInstance()

Creates a Litebase connection for the default creator id, storing the database as a flat file. This method avoids the creation of more than one instance with the same creator id, which would lead to performance and memory problems. Using this method, the strings are stored in the unicode format.

Returns:

A Litebase instance.

getInstance

public static LitebaseConnection getInstance(java.lang.String appCrid)

Creates a Litebase connection for the given creator id, storing the database as a flat file. This method avoids the creation of more than one instance with the same creator id, which would lead to performance and memory problems. Using this method, the strings are stored in the unicode format.

Parameters:

appCrid - The creator id, which may (or not) be the same one of the current application and MUST be 4 characters long.

Returns:

A Litebase instance.

getInstance

public static LitebaseConnection getInstance(java.lang.String appCrid,
                                             java.lang.String params)
                                      throws DriverException

Creates a LitebaseConnection for the given creator id and with the given connection param list. This method avoids the creation of more than one instance with the same creator id and parameters, which would lead to performance and memory problems.

Parameters:

appCrid - The creator id, which may be the same one of the current application and MUST be 4 characters long.

params - Only the folder where it is desired to store the tables, null, if it is desired to use the current data path, or chars_type = chars_format; path = source_path, where chars_format can be ascii or unicode, and source_path is the folder where the tables will be stored. The params can be entered in any order. If only the path is passed as a parameter, unicode is used. Notice that path must be absolute, not relative.

If it is desired to store the database in the memory card (on Palm OS devices only), use the desired volume in the path given to the method.

Most PDAs will only have one card, but others, like Tungsten T5, can have more then one. So it is necessary to specify the desired card slot.

Note that databases belonging to multiple applications can be stored in the same path, since all tables are prefixed by the application's creator id.

Also notice that to store Litebase files on card on Pocket PC, just set the second parameter to the correct directory path.

It is not recommended to create the databases directly on the PDA. Memory cards are FIVE TIMES SLOWER than the main memory, so it will take a long time to create the tables. Even if the NVFS volume is used, it can be very slow. It is better to create the tables on the desktop, and copy everything to the memory card or to the NVFS volume.

Due to the slowness of a memory card and the NVFS volume, all queries will be stored in the main memory; only tables and indexes will be stored on the card or on the NVFS volume.

An exception will be raised if tables created with an ascii kind of connection are oppened with an unicode connection and vice-versa.

Returns:

A Litebase instance.

Throws:

DriverException - If an IOException occurs oran application id with more or less than four characters is specified.

getSourcePath

public java.lang.String getSourcePath()
                               throws DriverException

Returns the path where the tables created/opened by this connection are stored.

Returns:

A string representing the path.

Throws:

DriverException - If the driver is closed.

execute

public void execute(java.lang.String sql)
             throws DriverException,
                    SQLParseException,
                    AlreadyCreatedException

Used to execute a create table or create index SQL commands.

Examples:

• driver.execute("create table PERSON (NAME CHAR(30), SALARY DOUBLE, AGE INT, EMAIL CHAR(50))");
• driver.execute("CREATE INDEX IDX_NAME ON PERSON(NAME)");

When creating an index, its name is ignored but must be given. The index can be created after data was added to the table.

Parameters:

sql - The SQL creation command.

Throws:

DriverException - If the driver is closed or an IOException occurs.

SQLParseException - If the table name or a default string is too big, there is a blob in the primary key definition, an invalid default value, or an unknown (on a create table) or repeated column name, or an InvalidDateException or an InvalidNumberException occurs.

AlreadyCreatedException - If the table or index is already created.

executeUpdate

public int executeUpdate(java.lang.String sql)
                  throws SQLParseException,
                         DriverException

Used to execute updates in a table (insert, delete, update, alter table, drop). E.g.:

driver.executeUpdate("drop table person"); will drop also the indices.

driver.executeUpdate("drop index * on person"); will drop all indices but not the primary key index.

driver.executeUpdate("drop index name on person"); will drop the index for the "name" column.

driver.executeUpdate("ALTER TABLE person DROP primary key"); will drop the primary key.

driver.executeUpdate("update person set age=44, salary=3200.5 where name = 'guilherme campos hazan'"); will update the table.

driver.executeUpdate("delete person where name like 'g%'"); will delete records of the table.

driver.executeUpdate("insert into person (age, salary, name, email) values (32, 2000, 'guilherme campos hazan', 'guich@superwaba.com.br')"); will insert a record in the table.

Parameters:

sql - The SQL update command.

Returns:

The number of rows affected or 0 if a drop or alter operation was successful.

Throws:

SQLParseException - If an InvalidDateException or InvalidNumberException occurs.

DriverException - If an IOException occurs or the driver is closed.

executeQuery

public ResultSet executeQuery(java.lang.String sql)
                       throws DriverException,
                              SQLParseException

Used to execute queries in a table. Example:

 ResultSet rs = driver.executeQuery("select rowid, name, salary, age from person where age != 44");
 rs.afterLast();
 while (rs.prev())
    Vm.debug(rs.getString(1) + ". " + rs.getString(2) + " - " + rs.getInt("age") + " years");
 

Parameters:

sql - The SQL query command.

Returns:

A result set with the values returned from the query.

Throws:

DriverException - If an IOException occurs or the driver is closed.

SQLParseException - If an InvalidDateException or an InvalidNumberException occurs.

prepareStatement

public PreparedStatement prepareStatement(java.lang.String sql)
                                   throws SQLParseException,
                                          DriverException

Creates a pre-compiled statement with the given sql. Prepared statements are faster for repeated queries. Instead of parsing the same query where only a few arguments change, it is better to create a prepared statement and the query is pre-parsed. Then, it is just needed to set the arguments (defined as ? in the sql) and run the sql.

Parameters:

sql - The SQL query command.

Returns:

A pre-compiled SQL statement.

Throws:

DriverException - If an IOException occurs or the driver is closed.

SQLParseException - If an InvalidDateException or an InvalidNumberException occurs.

getCurrentRowId

public int getCurrentRowId(java.lang.String tableName)
                    throws DriverException,
                           SQLParseException

Returns the current rowid for a given table.

Parameters:

tableName - The name of a table.

Returns:

The current rowid for the table.

Throws:

DriverException - If an IOException occurs or the driver is closed.

SQLParseException - If an InvalidDateException occurs.

getRowCount

public int getRowCount(java.lang.String tableName)
                throws DriverException,
                       SQLParseException

Returns the number of valid rows in a table. This may be different from the number of records if a row has been deleted.

Parameters:

tableName - The name of a table.

Returns:

The number of valid rows in a table.

Throws:

DriverException - If an IOException occurs or the driver is closed.

SQLParseException - If an InvalidDateException occurs.

See Also:

getRowCountDeleted(String)

setRowInc

public void setRowInc(java.lang.String tableName,
                      int inc)
               throws DriverException,
                      SQLParseException

Sets the row increment used when creating or updating big amounts of data. Using this method greatly increases the speed of bulk insertions (about 3x faster). To use it, it is necessary to call it (preferable) with the amount of rows that will be inserted. After the insertion is finished, it is NECESSARY to call it again, passing -1 as the increment argument. Without doing this last step, data may be lost because some writes will be delayed until the method is called with -1. Another good optimization on bulk insertions is to drop the indexes and then create them afterwards. So, to correctly use setRowInc(), it is necessary to:

 driver.setRowInc("table", totalNumberOfRows);
 // Fetches the data and insert them.
 driver.setRowInc("table", -1);
 

Using prepared statements on insertion makes it another a couple of times faster.

Parameters:

tableName - The associated table name.

inc - The increment value.

Throws:

DriverException - If an IOException occurs or the driver is closed.

SQLParseException - If an InvalidDateException occurs.

exists

public boolean exists(java.lang.String tableName)
               throws DriverException

Indicates if the given table already exists. This method can be used before a drop table.

Parameters:

tableName - The name of a table.

Returns:

true if a table exists; false othewise.

Throws:

DriverException - If an IOException occurs or the driver is closed.

closeAll

public void closeAll()
              throws DriverException

Releases the file handles (on the device) of a Litebase instance. Note that, after this is called, all Resultsets and PreparedStatements created with this Litebase instance will be in an inconsistent state, and using them will probably reset the device. This method also deletes the active instance for this creator id from Litebase's internal table.

Throws:

DriverException - If an IOException occurs or the driver is closed.

purge

public int purge(java.lang.String tableName)
          throws DriverException,
                 SQLParseException

Used to delete physically the records of the given table. Records are always deleted logically, to avoid the need of recreating the indexes. When a new record is added, it doesn't uses the position of the previously deleted one. This can make the table big, if a table is created, filled and has a couple of records deleted. This method will remove all deleted records and recreate the indexes accordingly. Note that it can take some time to run.

Important: the rowid of the records is NOT changed with this operation.

Parameters:

tableName - The table name to purge.

Returns:

The number of purged records.

Throws:

DriverException - If an IOException occurs or the driver is closed.

SQLParseException - If an InvalidDateException occurs

getRowCountDeleted

public int getRowCountDeleted(java.lang.String tableName)
                       throws DriverException,
                              SQLParseException

Returns the number of deleted rows.

Parameters:

tableName - The name of a table.

Returns:

The total number of deleted records of the given table.

Throws:

DriverException - If an IOException occurs or the driver is closed.

SQLParseException - If an InvalidDateException occurs

getRowIterator

public RowIterator getRowIterator(java.lang.String tableName)
                           throws DriverException,
                                  SQLParseException

Gets an iterator for a table. With it, it is possible iterate through all the rows of a table in sequence and get its attributes. This is good for synchronizing a table. While the iterator is active, it is not possible to do any queries or updates because this can cause dada corruption.

Parameters:

tableName - The name of a table.

Returns:

A iterator for the given table.

Throws:

DriverException - If an IOException occurs or the driver is closed.

SQLParseException - If an InvalidDateException occurs

getLogger

public static totalcross.util.Logger getLogger()

Gets the Litebase logger. The fields should be used unless using the logger within threads.

Returns:

The logger.

setLogger

public static void setLogger(totalcross.util.Logger logger)

Sets the litebase logger. This enables log messages for all queries and statements of Litebase and can be very useful to help finding bugs in the system. Logs take up memory space, so turn them on only when necessary. The fields should be used unless using the logger within threads.

Parameters:

logger - The logger.

getDefaultLogger

public static totalcross.util.Logger getDefaultLogger()
                                               throws DriverException

Gets the default Litebase logger. When this method is called for the first time, a new PDBFile is created and a log record started. In the subsequent calls, the same PDBFile is used, but in different log records.

Returns:

The default Litebase logger.

Throws:

DriverException - If an IOException occurs

deleteLogFiles

public static int deleteLogFiles()
                          throws DriverException

Deletes all log files found in the device. If log is enabled, the current log file is not affected by this command. It only deletes PDB log files.

Returns:

the number of files deleted.

Throws:

DriverException - If an IOException occurs.

processLogs

public static LitebaseConnection processLogs(java.lang.String[] sql,
                                             java.lang.String params,
                                             boolean isDebug)
                                      throws DriverException

This is a handy method that can be used to reproduce all commands of a log file. This is intended to be used by the development team only. Here's a sample on how to use it:

 String []sql =
 {
    "new LitebaseConnection(MBSL,null)",
    "create table PRODUTO (IDPRODUTO int, IDPRODUTOERP char(10), IDGRUPOPRODUTO int, IDSUBGRUPOPRODUTO int, IDEMPRESA char(20), 
                                DESCRICAO char(100), UNDCAIXA char(10), PESO float, UNIDADEMEDIDA char(3),
                                EMBALAGEM char(10), PORCTROCA float, PERMITETROCA int)",
    "create index IDX_PRODUTO_1 on PRODUTO(IDPRODUTO)",
    "create index IDX_PRODUTO_2 on PRODUTO(IDGRUPOPRODUTO)",
    "create index IDX_PRODUTO_3 on PRODUTO(IDEMPRESA)",
    "create index IDX_PRODUTO_4 on PRODUTO(DESCRICAO)",
    "closeAll",
    "new LitebaseConnection(MBSL,null)",
    "insert into PRODUTO values(1,'19132', 2, 1, '1', 2, '3', 'ABSORVENTE SILHO ABAS', '5', 13, 'PCT', '20X30', 10, 0)",
  };
  LitebaseConnection.processLogs(sql, true);
 

Parameters:

sql - The string array of SQL commands to be executed.

params - The parameters to open a connection.

isDebug - Indicates if debug information is to displayed on the debug console.

Returns:

The LitebaseConnection instance created, or null if closeAll was the last command executed (or no commands were executed at all).

Throws:

DriverException - If an exception occurs.

recoverTable

public boolean recoverTable(java.lang.String tableName)
                     throws DriverException,
                            SQLParseException

Tries to recover a table not closed properly by marking and erasing logically the records whose crc are not valid. The table must be closed in order to use this method and will be closed after it. When a table is not closed, a TableNotClosedException is thrown when one tries to access the table. One should, then, call this method to try to recover it.

Parameters:

tableName - The table to be recovered.

Returns:

true if it was in fact corrupted; falseotherwise.

Throws:

DriverException - If an IOException occurs, the driver is closed or the table was closed correctly.

SQLParseException - If an InvalidDateException occurs.

convert

public void convert(java.lang.String tableName)
             throws DriverException,
                    SQLParseException

Converts a table from the previous Litebase table version to the current one. If the table format is older than the previous table version, this method can't be used. It is possible to know if the table version is not compativel with the current version used in Litebase because an exception will be thrown if one tries to open a table with the old format. The table will be closed after using this method and must be closed before calling it. Notice that the table .db file will be overwritten.

Parameters:

tableName - The name of the table to be converted.

Throws:

DriverException - If the table version is not the previous one (too old or the actual used by Litebase), the driver is closed or an IllegalArgumentIOException, FileNotFoundException, or IOException occurs.

SQLParseException - If an InvalidDateException occurs.