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:
Video: Building Database Applications in REALbasic I.
Video: Building Database Applications in REALbasic II.
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.
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.
