litebase
Class LitebaseConnection

java.lang.Object
  |
  +--litebase.LitebaseConnection

public final 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

boolean	aspdb
protected java.lang.Object	bag
static int	LOG_LEVEL_MAX Maximum log level.
static int	LOG_LEVEL_NONE Default logging level: 0 (no logs generated).
static int	version
static java.lang.String	versionStr

Method Summary

void	closeAll() Close all catalogs, so that they are immediately written to disk (at desktop) or release the file handles (at device).
void	convert(java.lang.String tableName) Convert from stream (.pdb) to plain file.
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) Returns if the given table already exists.
int	getCurrentRowId(java.lang.String tableName) Returns the current rowid for a given table.
static LitebaseConnection	getInstance(java.lang.String appCrid) Creates a LitebaseConnection for the given creator id, storing the database in main memory, and as 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 paramList.
static java.lang.String	getLogName() Returns the name of the catalog used to store the logs, or null if log is disabled.
int	getRecordCount(java.lang.String tableName) Returns the total number of records for a table, which includes the deleted rows, but excluding the record 0, which is used to store the table metadata.
int	getRowCount(java.lang.String tableName) Returns the number of valid rows in a table.
RowIterator	getRowIterator(java.lang.String tableName) Gets an iterator for a table.
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 debug) 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.
static void	setLogLevel(int newLogLevel) Sets the current log level.
void	setRowInc(java.lang.String tableName, int inc) Set the row increment used when creating or updating big amounts of data.

Methods inherited from class java.lang.Object

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

Field Detail

bag

protected java.lang.Object bag

versionStr

public static final java.lang.String versionStr

version

public static final int version

LOG_LEVEL_NONE

public static final int LOG_LEVEL_NONE

Default logging level: 0 (no logs generated). If the log is on, setting to this closes the log file.

LOG_LEVEL_MAX

public static final int LOG_LEVEL_MAX

Maximum log level. Currently this just turns logging on, creating the file with a first record of 16000 bytes.

aspdb

public boolean aspdb

Method Detail

getInstance

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

Creates a LitebaseConnection for the given creator id, storing the database in main memory, and as 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.

Parameters:

appCrid - The creator id, which may (or not) be the same one of your application's.

Throws:

java.lang.RuntimeException - If the XPlatSqlPdb.dll/prc is not installed.

getInstance

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

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

Parameters:

appCrid - The creator id, which may be the same one of your application's.

params - There are two parameters that can be used, one at a time, in lowercase please!

1. oncard: (on Palm OS devices only - ignored in other platforms) specifies that the database will be stored in the memory card. The card used will always be the last card number. Most PDAs will only have one card, but others, like the Tungsten T5, can have more than one. So, you have to store the databases in the last card slot.

   The directory in the card from where the tables will be read and written is \PALM\PDBDATA. 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. If the pda don't support cards, the databases will be stored in main memory.

2. aspdb: (on Palm OS devices that have flash memory and desktop application (applet or win32)!): specifies that a pdb file will be created instead of a flat file. Note that this settings is applied to all open LitebaseConnection instances, I.E., setting an instance aspdb will overwrite any other instance's settings. Note also that after setting it aspdb, its impossible to set it back to use flat files on further instances. See the Litebase Companion for more information.

It is not recommended that you create the databases directly from the PDA. Memory cards are FIVE TIMES SLOWER than the main memory, so it will take a long time to create the tables. You may create the tables in the desktop, and copy everything to the memory card.

Due to the slownes of a memory card, all queries will be stored in main memory; only tables and indexes will be stored in the card.

Throws:

java.lang.RuntimeException - If the XPlatSqlPdb.dll/prc is not installed.

DriverException - if you had instancied a litebase as pdb, and try to instance not as pdb, or vice versa.

See Also:

getInstance(String)

execute

public void execute(java.lang.String sql)

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)"); // the index name is ignored but must be given
 

The index can be created after data was added to the table. An AlreadyCreatedException may be thrown if the index/table was already created.

executeUpdate

public int executeUpdate(java.lang.String sql)

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

  try {driver.executeUpdate("drop table person");} // will drop also the indexes
  catch (DriverException e) {}

  try {driver.executeUpdate("drop index * on person");} // will drop all indexes
  catch (DriverException e) {}

  try {driver.executeUpdate("drop index name on person");} // will drop index for the "name" column
  catch (DriverException e) {}

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

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

  driver.executeUpdate("delete person where name like 'g%'");

  driver.executeUpdate("insert into person (age,salary,name,email) values"+
     "(32,2000,'guilherme campos hazan','guich@superwaba.com.br')");
 

executeQuery

public ResultSet executeQuery(java.lang.String sql)

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");
 

prepareStatement

public PreparedStatement prepareStatement(java.lang.String sql)

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, you create a prepared statement and the query is pre-parsed. Then, you just set the arguments (defined as ? in the sql) and run the sql.

See Also:

PreparedStatement

getCurrentRowId

public int getCurrentRowId(java.lang.String tableName)

Returns the current rowid for a given table. The last item inserted will have this rowid-1. If the table doesnt exists, a DriverException is thrown.

getRowCount

public int getRowCount(java.lang.String tableName)

Returns the number of valid rows in a table. This may be different from the number of records if a row has been deleted. If the table does not exists, -1 is returned.

See Also:

getRecordCount(java.lang.String)

setRowInc

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

Set 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, you must call it (preferable) with the amount of lines that will be inserted. After the insertion is finished, you MUST call it again, passing -1 as the inc argument. Without doing this last step, you may loose data because some writes will be delayed until you call it with -1. Another good optimization on bulk insertions is to drop the indexes and then create them afterwards. So, to correctly use setRowInc, you must:

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

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

convert

public void convert(java.lang.String tableName)

Convert from stream (.pdb) to plain file. You must specify the table name to be converted. If you call this method more once, it will convert the table again, you must handle this For more information of how to use this, see the Litebase Companion (section Migration) ,

exists

public boolean exists(java.lang.String tableName)

Returns if the given table already exists. You can use this method before drop table

closeAll

public void closeAll()

Close all catalogs, so that they are immediately written to disk (at desktop) or release the file handles (at device). 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 our internal table.

purge

public int purge(java.lang.String tableName)

Used to delete physically the records of the given table. Records are always deleted logically, to avoid the need of recreating the indexes; then, when a new record is added, it occupies the position of the previously deleted one. This can make the table big, if you create a table, fill it and delete a couple of records without never adding others, thus wasting space. 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.

getRecordCount

public int getRecordCount(java.lang.String tableName)

Returns the total number of records for a table, which includes the deleted rows, but excluding the record 0, which is used to store the table metadata.

getRowIterator

public RowIterator getRowIterator(java.lang.String tableName)

Gets an iterator for a table. With it you can 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, you must not do any queries or updates, unless you want to corrupt your data.

setLogLevel

public static void setLogLevel(int newLogLevel)

Sets the current log level. This enables log messages of all queries and statements of the Litebase and can be very useful to help find bugs in the system. Logs takes up memory space, so turn on only when necessary. Sample to put in the application's constructor:

LitebaseConnection.setLogLevel(LitebaseConnection.LOG_LEVEL_MAX)

To delete all log files, use the deleteLogFiles method. At device, the logs are stored in a PDB file named "LITEBASE_YYYYMMDDHHMMSS.CRTR.LOGS", where CRTR is the Litebase creator id passed on the getInstance method. At desktop, the log is sent directly to the console.

deleteLogFiles

public static int deleteLogFiles()

Deletes all log files found in the device. If log is enabled, the current log file is not affected by this command. Returns the number of files deleted.

getLogName

public static java.lang.String getLogName()

Returns the name of the catalog used to store the logs, or null if log is disabled.

processLogs

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

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 of 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);
 

Returns:

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

Since:

SuperWaba 5.66