REALbasic Connectivity Module

The REALbasic Connectivity Module for OpenBase allows clients written in REALbasic to connect with OpenBase databases. Since the connectivity module is written in 100% REALbasic, applications that use the module are instantly compatible with other platforms. This section will help you get started with the connectivity module.

Here are a few instructional videos which will help you get started with RealBasic:

videoicon3.png Video: Building Database Applications in REALbasic I
.
videoicon3.png Video: Building Database Applications in REALbasic II
.
videoicon3.png Video: Using the Admin Interface through REALbasic

Download: OpenBase Connectivity Module

Installation

The OpenBase Connectivity Module package contains several examples, release notes and a folder called "OpenBase" which contains the entire encrypted source code for the module. To install the module by dragging the OpenBase folder into your project.

The next step is to identify what picture class you need to use in the module. We supply two because the regular picture class does not work for console applications.

The picture classes are in folders inside the OpenBase folder. If you are creating a Console Application or RB Stored Procedure then add the "For_Console_Applications". Any other applications that have a GUI should use the "For_GUI_Applications".

Getting Started (using REALbasic2005r4 on OS X)

 
While this sample uses RB 2005r4 it is very similar for all supported versions of REALbasic.

1. Make sure that you can log in to the pubs database supplied with OpenBase using openisql_ascii. You will need to know a userid, password, database and host. If this is a new installation then the userid is likely still "admin", no password, and the host should be "127.0.0.1".

MacBookPro:~ npalardy$ /Library/OpenBase/bin/openisql_ascii -use pubs@127.0.0.1 -login admin -password ""

OpenBase ISQL version 11.0 for MacOS X
Copyright (c) 1993-2009 OpenBase International. Ltd.
All Rights Reserved.

Using database 'pubs' on host 'macbookpro'

This confirms that you can actually use this database, it is running and you are able to log in. If you can not log in then this should be corrected before trying to proceed.

2. Start a new REALbasic project. When asked select the "Desktop Application" template.

3. Drag in the "OpenBase" folder from inside the "Connectivity Module" folder. You will notice that this adds a new folder inside your project. When you expand this folder you should see two other folders inside. One is "For_Console_Applications" and the other is "For_GUI_Applications". Notice that the second folder is bold indicating that it contains some items.

openbase.jpg

REALbasic has imported all the items and created appropriate folders based on what you dragged in. When you do this REALbasic imports the first OBPicture contained in the "For_Console_Applications" folder. When the "For_GUI_Applications" folder is imported the second OBPicture replaces the first one so you are left with an empty folder and the proper OBPicture for use in a GUI application.

5. Add a push button to the default Window1 that is in the new project

6. Double click the push button to edit the Action event
 
7. add the following code to the push button
 

  dim db as OpenBaseDBConnection
 
  db = new OpenBaseDBConnection
 
  db.host = "127.0.0.1"
  db.databaseName = "pubs"
  db.userName = "admin"
  db.password = ""
 
  if db.connect = false then
    msgbox "cannot connect to 'pubs'"
  else
    msgbox "connected to 'pubs'"
  end if

8. Run the project. You should get a message that says you have connected. If you cannot connect make sure the user name, password, host and database are for the database you logged into in step 1.
 
Congratulations! You’ve just started using REALbasic and OpenBase.
 
This example is the BasicConnect.rbp example in the OpenBase RB Examples.

Moving On

 
The BasicConnectToPort.rbp example shows another technique to use with RB when a database has been set up to run on a specific port.

This is useful when you want to ALWAYS have a certain database one a certain port, perhaps behind a firewall with a port forward.

You can make OpenBase always run a specified database on a specific port.

The only change to the program is the setting for the databaseName.

If pubs had been set to run on port 20229 you would substitute the following

db.databaseName = "#20229"

in the example above.

The Info connection Class

The OpenBaseInfoConnection is NOT used for querying the database.

The OpenBaseInfoConnection can be used to get information about the OpenBase server.

If you need to get information about the server you are connected to you can get that using the OpenBaseInfo class and connecting to the desired host.
 
1. Start a new REALbasic project. When asked select the "Desktop Application" template.

2. Drag in the "OpenBase" folder from inside the "Connectivity Module" folder as in the previous example.

3. Add a push button and an editfield to the default Window1 that is in the new project.

4. Double click the push button to edit the Action event
 
5. add the following code to the push button
 

   dim dbInfo as OpenBaseInfoConnection

  dbInfo = new OpenBaseInfoConnection

  dbInfo.host = "127.0.0.1"

  if dbInfo.Connect = false then
    msgbox "Cannot contact host"
    return
  end if

  editField1.text = dbInfo.HostInfo 
 

6. Run the project. The editfield should show you the complete information about the database server.
Note that this is a number of fields of information all in one long string. Each value is separated by a | symbol.

The fields are:

server host name | server MAC address | server IP | server version | server OS version

This is the HostInfo.rb example in the OpenBase RB Examples

The Administrative Connection Class

The OpenBaseAdminConnection is also NOT used for querying the database.

The OpenBaseAdminConnection can be used to start databases, stop databases, create databases and numerous other administrative tasks.

If you need to get information about the host you are connected to you can get that using the OpenBaseAdmin class and connecting to the desired host.
 
1. Start a new REALbasic project. When asked select the "Desktop Application" template.

2. Drag in the "OpenBase" folder from inside the "Connectivity Module" folder as in the previous example.

3. Add a push button and an editfield to the default Window1 that is in the new project.

4. Double click the push button to edit the Action event
 
5. add the following code to the push button

6. Run the project. The editfield should show you the complete information about the database server.

Organization of the rest of this document

Overview
The Query Related Classes
OpenBaseDBConnection
OpenBaseRecordSet
OpenBaseDatabaseField
OpenBaseDatabaseRecord
The Administrative Interface
OpenBaseInfoConnection
OpenBaseAdmin

Overview

In your applications you can use the module in the same manner you would have used the Database, DatabaseRecord, DatabaseField and RecordSet classes.

There are some differences noted below.

If you have an application that already uses :

the Database class -> substitute OpenBaseDBConnection
the DatabaseRecord class -> substitute OpenBaseDatabaseRecord
the DatabaseField class -> substitute OpenBaseDatabaseField
the RecordSet class -> substitute OpenBaseRecordSet

The Language Reference documentation on methods and properties applies to the new classes as it did to the classes available from the various database plugins.

There are some differences in the usage though.

Becuase the OpenBase Connectivity Module has been designed to be used in Console and GUI applications, the use of Pictures varies between these two style of applications. As well, in order to make using the Module in a Console application possible, a new class, OBPicture, is used instead of Picture. Console applications that try to use Picture will not compile.

So, there is an implementation of OBPicture for Console applications, and a different one for GUI applications.

Things to note

You should ALWAYS call close on a OpenBaseDBConnection when you are done with it as they DO NOT close when they go out of scope. This is different than most other database plugins. This has to do with the fact they are socket subclasses.

Timeouts

In general client side timeout are disabled by default. However, you may want to set the timeout ON for things like Connect, and then disable it once you are connected.

This way you can avoid the situation where you wait indefinitely for a connection.

You simply do :

dim db as OpenBaseDBConnection

db = new OpenBaseDBConnection
db.timeout = 30 // wait 30 seconds for a connection

… other required connection info …

if db.connect() then
        // stuff to do when connected
    else
        if db.lasterror = OpenBaseDBConnection.kTimeOut
            // some message sayng the connection attempt timed out
        else
            // some other message about not being able to connect
        end if
    end if

Notifications

If you examine the example window "testnotifications" in the GUI_and_admin app it will help as this sets everything required.

A notifcation listener MUST be "added" to a regular database connection. It cannot exist on its own.

A notification listener is just a class that impleemnts the interface "OpenBaseNotificationListenerEvents"
There is only one method you need to implement
Sub NotificationReceived(action as string, tablename as string, rowid as integer)

Action will be one of
insert
delete
update

Tablename will contain the name of the table that the action applied to.
Rowid is the rowid of the record that that the action was aplied to.

In order to listen for notification you need to "add" the listener and start it. This is all done in one step.
You can do this at any time, and you can have several listsners, although 1 is really all you need to have for dispatching purposes.

In order to add and start you would do something like this:

listener = new OpenBaseNotificationListener(myOpenBaseNotificationListenerEvents1) // create a listener
obConnection.StartNotificationListener( listener ) // add and start the listener

in this case obConnection is an ALREADY connected database connection (OpenBaseDBConnection)
The code in the push button on the TestNotifications window illustrates this.

After that you can do what ever you want with each notification.
In the example I make use of a simple dispatcher that simply passes each call to the method on to an event. This is in
the class "myOpenBaseNotificationListenerEvents" which implements the interface.
It then takes the call to the method and calls an event I defined so I can drop an instance on a windopw and put code in the
event handler.

That's it. Fairly simple.

The Query Related Classes

OpenBaseDBConnection
This class is intended to mimic the Database class in REALbasic.

Properties

Attribute Description
DatabaseName The name of the database to connect to
You can also connect to a specific port byt providing the port number preceded by a # (like #20224)
Error boolean if an error was encountered
ErrorCode the error code encountered
ErrorMessage the text of the error message
Host the IP address or domain name of the host
Password the password used for the account login
UserName the username to log in with
AutoFetchBlobs defaults to TRUE
If set false then blobs columns will return the blob KEYS and you can then manually fetch the blobs
SoftwareID the "name" of the prgram that will be displayed in the openBae Manager Monitor Active connectiosn window

Methods

Method Description
Close close the connection to the host
InsertRecord insert an OpenBaseData record into the named table on the host
Commit commit any open transactions
Rollback rollback any open transactions
Connect connect to the host specified by the host & databasename with the supplied usrname and password
SQLExecute run a SQL statement that does NOT return results
FieldSchema gets a record set describing the columns of the named table
SQLSelect run a SQL statement that DOES return results
GetProperty gets any of the available properties from the connetion.
Available properties:VERSION
TableSchema gets list of tables in the database
Encoding gets the conding the server is useing or sets the client encoding
GetBlobField get a blob with a given key
SetBlobField set a blob into the database. Returns the key as a result (string)

Connecting to a data source

When establishing a connection to any data source, do a test like this before proceeding:

Dim db as OpenBaseDBConnection

If db.Connect() then
    //connection is successful
else
    //connection failed
end if

You should use Username, Host, and Password properties of the OpenBaseDBConnection class to establish the database connection.

FieldSchema and TableSchema

FieldSchema returns an OpenBaseRecordSet with five fields: ColumnName ( String), FieldType ( Integer), IsPrimary ( Boolean), NotNull ( Boolean), and Length in bytes (Integer) for Text fields. FieldType uses the following values:

OpenBase Type REALbasic Type id
integer i 3
binary B 14
long l (small ell) 3
object @ 16
datetime ! 8
text X 15
date D 8
char * 5
float d 7
time T 8
timestamp S 8
money $ 11
longlong Q 99
boolean b 12

The values of IsPrimary and NotNull are set using the SQL Select statement that created the table.

TableSchema returns an OpenBaseRecordSet with one field, TableName (String).

The Property Method

The purpose of the Property method is to retrieve miscellaneous information from the data source.
The only property retrievable with this call is the Version.

dim version as integer 

version = db.GetProperty("Version")

This can also be retrieved using the global, OpenBaseGlobals.Version

dim version as integer 

version = OpenBaseGlobals.Version

SQLSelect and SQLExecute Methods

Use the SQLSelect and SQLExecute methods to communicate with your data source via SQL commands. Use SQLSelect to call the SELECT statement and the SQLExecute statement for all other statements.

Any call that returns data is accessed via the SQLSelect method. Calls to stored procedures that return results are performed using SQLSelect.

Dim db as OpenBaseDBConnection
dim rs as OpenBaseRecordSet

rs = db.SQLSelect("call GetStockQuote('AAPL')")

OpenBaseRecordSet

A record set is a collection of information returned from a SQL Select method invocation. This can be a single value, a single row, or multiple rows from the database.
The OpenBaseRecordSet is designed to be a replacement for the RecordSet found in REALBasic itself and so shares many of the same methods, events and properties and behaves as identically as possible.

Methods

Method Description
BOF Beginning of the set of records
Close Closes the open record set
ColumnName The name of the column
ColumnType The actual type of the column in the database
Delete Deletes the record (locks it first)
Edit Locks the record for editing
Note that records from a join may not always be editable
EOF End of the set of records
Field Gets an OpenBaseDatabaseField using the name of the column
FieldCount Gets the number of columns returns
IdxField Gets an OpenBaseDatabaseField using an index. Columns are numbered starting at 1
MoveFirst Moves to the first record in the set
MoveLast Moves to the last record in the set
MoveNext Moves to the Next record in the set
MovePrevious Moves to the previous record in the set
MoveTo Moves to a specific record in the record set
RecordCount Returns the number of records in the record set
TableName Returns the name of the table the clumn came from (derived and aggregates will return "derived")
Update Saves any edits to the record back to the database
Note that records from a join may not always be editable

OpenBaseDatabaseField

A database field represents one of the columns in a set of records. This provides the accessors needed to get the value from the column for use in REALbasic.
Using an accessor for a value that is NOT the native darabase type will result in value conversion being performed. For instance, ifthe data type in the database is a DATE and you access the value using IntegerValue the date will be converted to OpenBase's Integer representation for the date.

Attribute Description
Altered whether or not this value was altered as part of an Edit
BooleanValue get/set the value for this column as a Boolean
DateValue get/set the value for this column as a Date
DoubleValue get/set the value for this column as a Double
IntegerValue get/set the value for this column as a integer
isBlob indicates that this column is a blob (stored in an OpenBase Object column)
isNull indicates that this column is / is not NULL
isText indicates that this column is a text blob (stored in an OpenBase Text column)
JPEGValue get/set the value for this column as a JPEG
MacPICTValue get/set the value for this column as a Mac PICT
Name The name of this column
StringValue get/set the value for this column as a String
TableName returns the table name that this column came from. Derived and aggregates will return "derived"
Type returns the OpenBase type for this column
Value get/set the value for this column as a variant

OpenBaseDatabaseRecord

Used to create new records in a table.

Attribute Description
BlobColumn get/set a blob value for this column in the record
BooleanColumn get/set a boolean value for this column in the record
Column get/set a string value for this column in the record
DateColumn get/set a date value for this column in the record
DoubleColumn get/set a double value for this column in the record
IntegerColumn get/set a integer value for this column in the record
JPEGColumn get/set a JPEG value for this column in the record
MacPICTColumn get/set a PICT value for this column in the record

Frequently Asked Questions about the RealBasic Module

Can I use Threads with OpenBase Database Connections ?

Yes, BUT you should NOT share a connection across threads. Each thread should have its own separate connection.
This is similar to the recommendation REAL Software makes regarding using threads and connections to the REAL SQL Database.

The reason for this in OpenBase is that SQL queries do not block while waiting for results.

The old plugins did block and so you could not issue a second query while waiting for the results of another to be returned.
You had to get the results from a query before you could issue another query.

In the Module this is not the case and you can actually issue another query while waiting for the rsults of the first. The problem is that
the module does not know which results are for which query and that the results can become interleaved leading to very odd and hard to track down problems.

If you use threads you may want to use a semaphore to control access to the Connection if you must share a connection across threads OR just use separate connections in each thread.

What is the OpenBaeLinuxConsts for?

If you use RB 5.5 on Linux you have to modify the value of the constant in this file to point to the actual LIBC library (not the shell script). On Linux, libc is NOT an actual library, its a script that the linker uses to locate the current version (you can look at it with "cat /usr/lib/libc.so")

Newer versions of REALbasic can handle this and track down the real library RB 5.5 cannot so you need to manually set the correct value for the library in this constant to the actual shared library name.

This should be in the last line of the script where there may be a line like

GROUP ( /lib/libc.so.6 /usr/lib/libc_nonshared.a )

The correct one is a shared library and in the example above you should use /lib/libc/so.6

Why do I get a KeyNotFoundException when inserting records?

With the very latest version you should NOT experience this issue any longer. If you do, please report it to moc.esabnepo|troppus#moc.esabnepo|troppus with sample code and the version of the Module you are using.

Why does my app reconnect to the database?

Auto reconnect has been implemented in the module. This means that when the client loses connection with the server it will try to automatically reconnect. However, it is still important to check your return codes and be aware that this can happen in your application. This is especially true if you are using transactions.

When transactions are involved, a disconnect will force your transaction to be rolled back on the server. As a safeguard, we also require you to do a rollback on the client before redoing your transaction. The rollback is required so you don't continue executing a the rest of your SQL outside of a transaction by mistake. If you detect a disconnect you need to always issue a rollback or your SQL will be ignored.

There are two error codes you should check for to detect a disconnect. Error 102 means that the server disconnected. Error 13 means it reconnected.

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License