What’s New in OpenBase 10

 

Welcome to OpenBase!  This document highlights what’s new in OpenBase SQL 10.

New HTML Documentation

NEW and complete documentation on all OpenBase SQL 10 capabilities can be found at:

http://www.openbase.com/help

Performance & Memory Usage

OpenBase 10 offers huge performance gains for evaluating large sub-queries while using less memory.  Evaluating this query can be as much as 200 times faster in OpenBase 10:

SELECT a.name from tableA a where a.colA IN (select b.colA from veryLargeTable b where colB = ‘value’)

New Interfaces

JDBC Java 1.5 Compatibility

OpenBase 10 offers a new JDBC driver which is simultaneously compatible with both Java versions 1.5 and 1.4.

REALBasic Connectivity Module

The latest connectivity module for REALBasic provides Data Change Notification, auto-failover capabilities and auto-reconnect features.  It also provides 100% compatibility with all target platforms supported by REALbasic.

Omnis DAMs Universal Binary

OpenBase also comes with updated DAMs for Omnis Studio and Omnis 7.  Among the updates is the addition of a Universal Binary version of the DAM providing compatibility with Apple’s MacOS X INTEL platform.

Universal Binary C & C++ Libraries

For those who need to compile unix processes that communicate with OpenBase, we have a set of libraries compiled as Universal Binary.  OpenBase also provides source code to the C libraries upon request.

 

Universal Binary Cocoa Frameworks

All Mac OS X Frameworks have been recompiled for Universal Binary compatibility.

New Configuration Options

Relocatable Databases

Now databases can be easily relocated to a different directory or an external drive (such as a RAID device).  With OpenBase SQL 10, the default database directory has also changed to:

    /Users/Shared/OpenBase/Databases

 

This change was necessary for Mac OS X users because computer technicians usually do not backup /Library/OpenBase when servicing Mac computers.  Moving the databases to a directory that is commonly backed up ensures that databases will not be accidentally lost when a hard drive is replaced.

To control the location of your databases, edit the following file:

    /Library/OpenBase/Config/DatabasePath

 

Record Schema Changes

Now you can eliminate the risk of deploying schema changes to production environments.  Simply turn on the new schema change recorder, accessible from  the Database Configuration  Expert panel and it will record the changes you make to the schema in your development database, so that later you can apply them to your production database.

Interfaces & Applications

EOModeler Synchronization

Synchronizing schemas with the WebObjects tools can sometimes be a challenge.  That’s why OpenBase has recently extended Openisql to include a command that will take an EOModel schema and make the OpenBase schema match it.  Here is how to call the command:

    openbase 1> synchronize eomodel /path/myEOModelFile.eomodeld

 

OpenBase SQL GUI

The OpenBase SQL GUI application has been completely rewritten as a wrapper for the openisql terminal based application.  Now users can take advantage of all the advanced features of openisql using a friendly GUI.

OpenBase Monitor

OpenBase Monitor is now a separate application, for monitoring remote database activity.  

OpenBase Forms

OpenBase Forms is a database interface that makes it easy to organize complex database information on functional data input screens, so users can find what they need fast.

Only OpenBase Forms organizes hierarchical database information on panels, which create a “storyboard” of related information and keeps things simple!.  This unique approach eliminates the problems encountered when trying to fit too much information on a single window or forcing the user to try to manage many inter-related windows.

For more information: http://store.openbase.com/products-OBForms-Overview.html

OpenBase Forms comes with complete source code, as well as examples of how to embed the OpenBase Forms Framework into Cocoa applications. Using this source, you can add your own Forms menu to your applications and link your windows to Form panels directly. The source code is found in the OpenBase 10 DMG package, under Connectivity.

OpenBase Forms offers a full range of development tools:

-     The OpenBase Form Wizard takes any table or group of related tables and generate a fully functional input form, which can be used to edit and search data.

-     More advanced users can then edit those forms using OpenBase Form Designer to create full-featured business applications. (OpenBase Forms Designer is sold separately, and can be downloaded from the openbase.com website.)

Cocoa developers can integrate the source code to the OpenBase Forms client application into any of their Cocoa applications.to make it easy for users to extend the application and make it more specific to their needs. You can advertise this benefit simply by including the OpenBase Forms framework. There are no runtime fees for the OpenBase Forms client software.

For more information: http://store.openbase.com/products-OBForms-Developer.html

New Built-in Stored Procedures

SaveBlobToFile()

 

The SaveBlobToFile stored procedure allows you to export a blob (image or text) to the file system.  It takes two parameters: a blob key, which is an 8-character identifier for a blob; and a file path, which must be valid and greater than 5 characters in length.

 

Here is an example:

call SaveBlobToFile ("abcf0100", "/PATH")

 

RemoteQuery() and RemoteJDBCQuery()

 

OpenBase SQL supports the ability to perform queries in other database servers using remoteQuery() and remoteJDBCQuery() procedure calls.  

 

The remoteQuery() procedure takes login information for another OpenBase SQL Database, performs the query and then returns the results to the calling application. Here is the syntax:

call RemoteQuery(databaseName, ipaddress, login, password, sqlstring)

 

Here is an example of how it can be called:

 

    call RemoteQuery(“pubs”, “127.0.0.1”, “admin”, “mypw”, “select * from authors”)

 

The remoteJDBCQuery() procedure takes remote query a step farther by enabling remote queries to be performed in any JDBC-compliant data source, including Oracle and Sybase databases, all from your OpenBase SQL database.  Here is the syntax:

 

    call RemoteJDBCQuery(jdbcDriver, jdbcURL, login, password, sqlstring)

 

Here is an example of how it can be called:

 

    call RemoteJDBCQuery("com.sybase.jdbc2.jdbc.SybDriver",

        "jdbc:sybase:Tds:192.168.0.2:11222/pubs", "sa", "",

        "select * from authors")

 

Global Log

The Global Log stored procedure allows application designers to store error messages in a central log that is not specific to any one database.  Storing error messages centrally makes it easier for application designers to better monitor what’s going on with their apps.  Logging to a central file is accomplished using the GlobalLog() stored procedure.  Here is an example:

call GlobalLog(logName,logMessage)

This is an example of how it might be used:

call GlobalLog(“myGlobalLogFile”,”This is an error message”)

 

New OpenBase SQL Features

New OpenBase 10 Views

OpenBase 10 offers a new ‘view’ mechanism that manages view result sets in virtual tables which can contain their own indexes and calculated columns.  The new implementation will also accept any SQL select syntax in creating the view.  

Server-side Variables

OpenBase 10 provides server-side global variables to make it easy to coordinate between stored procedures and database clients.  The variables are thread-safe and can be safely incremented and decremented to provide specialized key generation or state management.

SQL Declaration:

    DECLARE GLOBAL invoice_number AS INTEGER INITIALIZE 1000

 

Setting a Variable Value:

    SET invoice_number TO 2000

 

Incrementing and Decrementing Variables (thread-safe & returns value):

    INCREMENT invoice_number

    DECREMENT invoice_number

 

Returning a variable value:

    PRINT VARIABLE invoice_number

 

Server-side Variables for OpenScript

Server-side variables have also been integrated into OpenScript 3.0 allowing for OpenScript and other clients to use it as an effective locking mechanism.  Here is an example:

on MyProcess ( )

         DECLARE GLOBAL MyProcess_lock AS INTEGER INITIALIZE 0

        

        -- create the lock and exit if more than one is running

        IF INCREMENT MyProcess_lock != 1 THEN

             DECREMENT MyProcess_lock

            RETURN “exited: “ & MyProcess_lock & “ processes running”

        END IF

         

        -- process source code here        

 

        -- decrement the value

         DECREMENT MyProcess_lock     

        RETURN “success”

    end MyProcess 

 

Temporary Tables

Now you can create temporary tables that are private to the creator’s client connection and exist only as long as the client stays connected.  Temporary tables act the same as regular tables.  You can join them to other tables, assign indexes and alter them just like a regular table.

Temporary table names always start with a “#” in the first position.  That is how OpenBase distinguishes a temporary table from a regular table.  Everything else works exactly the same.

Because temporary tables are specific to client the connection, multiple users can create temporary tables with the same name, without any conflict.

Stored Procedures as Subqueries

Now it’s possible to call stored procedures as subqueries, a capability that’s especially useful to query other databases.

select TITLE t1 from MOVIE t1 where t1.MOVIE_ID IN (call FindMoviesForRole(‘Luke Skywalker’) )

Calling the RemoteJDBCQuery() stored procedure as a subquery, for instance, allows a result from Sybase or Oracle to be used to evaluate a query in OpenBase.  RemoteJDBCQuery() allows any JDBC compliant database to be used.

The use of procedures as subqueries is limited to selects and inserts.  It cannot be used for updates or deletes.

It is also important to note that only non-correlated subqueries will work with stored procedures.  Each procedure is called only once, so parameters have to be static.  You cannot pass in column names as parameters and use it as a function.

New SQL Functions

OpenBase has added a few SQL functions that give clients easier access to blobs and relevance scoring functions.

FETCHBLOB(blob_key)

Returns the first 4K of a text blob and allows it to be used inside an SQL operation.  Longer blob text is truncated by the function.  If the MOVIE table were to have a COMMENT column of type TEXT, the following SQL would work:

SELECT TITLE, FETCHBLOB(COMMENT) from MOVIE

 

FIGGSCORE(text, keywords)

The FIGG Score takes a text string and assigns a score to it based on how well it matches the keys passed into it.  The higher the score, the closer the match.

SELECT TITLE, FIGGSCORE(TITLE,”Star”) from MOVIE

 

FIGGSCOREBLOB(blob_key, keywords)

This version of the function takes a blob key as its first parameter and scores the entire Blob text , regardless of length.

SELECT TITLE, FIGGSCOREBLOB(COMMENT,”Star”) from MOVIE

 

“HAVING” has been implemented

The HAVING clause provides a way to eliminate grouped sets of records based on the results of aggregate functions.  It should always be used in conjunction with GROUP BY to  specify which groups of records you want included in the final result set. .  HAVING will only accept groups of records that satisfy the criteria.

Here is an example from the pubs database that includes all authors that live in states with other authors.

SELECT state, count(state) from authors group by state having count(state) >1

Sometimes you may need to require groups to satisfy multiple criteria.  This next example lists all states, which have more than one author but less than 10.

SELECT state, count(state) from authors group by state having count(state) > 1 and count(state)<10

The HAVING clause can only be used with the <, <=, !=, =, >= and > operators.  A static value must also be present on the right side of the HAVING constraint.

 

Inserting from a Select

INSERT ... INTO ... SELECT

In OpenBase 9.1 or later you can use sub-selects as part of an insert statement.

For example:

INSERT INTO TALENT (FIRST_NAME, LAST_NAME, TALENT_ID) VALUES (SELECT FIRST, LAST, TAL_ID FROM ANOTHER_TABLE)

 

You can also use the output of a stored procedure in your insert statement:

INSERT INTO TALENT (FIRST_NAME, LAST_NAME, TALENT_ID) VALUES (CALL findMyTalents(‘optional parameters’))

 

Server-Side SQL Cursors

New server-side SQL Cursors in OpenBase SQL 10 enable clients to perform a select or stored procedure call and cache a snapshot of the results on the server.  This allows clients to incrementally request portions of the result set or start fetching again from the beginning.

SQL Cursors are named so that clients can navigate the results of several cursors simultaneously.  Clients use the cursor name to specify which cursor you want to fetch.

Creating SQL Cursors

To create an SQL Cursor, start with an SQL Select or stored procedure call.  Here is an SQL statement that uses the pubs database:

SELECT au_fname, au_lname FROM authors ORDER BY au_lname

 

Then, to create a cursor that uses this SQL result set, precede the SQL with the CREATE CURSOR command.  Here is an example that creates a cursor named myAuthors:

CREATE CURSOR myAuthors AS SELECT au_fname, au_lname FROM authors

    ORDER BY au_lname

 

Creating a cursor will return nothing.  Before you can get the results you need to FETCH.

Fetching SQL Cursor Results

Once a SQL cursor is created, you retrieve the data using the FETCH command.  Here is an example of a FETCH command that specifies five result rows:

FETCH 5 FROM myAuthors

 

The label ‘myAuthors’ is the name of the cursor as specified in the CREATE CURSOR statement.  A second call to FETCH will get the next five in the result set.

Rewinding Cursor Results

There may be cases where you want to rewind a result set to the beginning so it can be fetched again. OpenBase SQL Cursors allow you to do this using the REWIND command.  Here is an example using the myAuthors cursor:

REWIND myAuthors

 

After a cursor result set has been reset using REWIND, the FETCH command will begin at the very beginning.

Closing Cursors

It is very important to close SQL cursors after you are finished with them.  To close a cursor, issue the CLOSE CURSOR command.  Here is an example that closes the myAuthors cursor:

CLOSE CURSOR myAuthors

 

OpenScript Stored Procedures

Process Control

OpenScript now allows you to control a process by reading and writing to the processes standard input/output.  This capability can be used for many things, including remote terminal operations, install scripts, or multi-step processes.

Here is an example of opening a terminal shell and waiting until the “.app” output is received before continuing.

 

 on checkForApp ( )

    OPEN PROCESS myProcess AT "/bin/sh" WITH TIMEOUT 10

    WRITELN "/bin/ls /Applications" TO myProcess

    SET ret TO EXPECT ".app" FROM myProcess

    CLOSE PROCESS myProcess

    return ret

END checkForApp

 

Note:  Communication with the process is only bi-directional on Mac OS X.