Building Stored Procedures In REALbasic

REALbasic Stored Procedures

OpenBase SQL makes it easy to write server-side REALbasic stored procedures to implement business logic. By centralizing business logic inside the database, OpenBase SQL enables any type of database client (web and client/server) to access and use the same processes, significantly reducing code duplication, complexity and time-to-deploy.

videoicon3.png Video: RealBasic Stored Procedures

Download: RB Stored Procedure Examples

Download: Latest OpenBase Connectivity Module

Installing the Stored Procedure Template Project

Install the OpenBaseProcedure.rb Template into REALbasic’s Project Template’s directory by copying it into the Project Template’s directory located next to your copy of REALbasic.

You do not need to restart REALbasic to start a new project from this template.

Creating a Stored Procedure

To create an REALbasic stored procedure start a new REALbasic Project.

Select the OpenBaseProcedure template from the list of available templates.

This creates an empty Console Application that has much of the functionality required of a REALbasic stored procedure already implemented.

Open the App class and you will see that it already has the RUN event implemented. The code in this event simply calls the included dispatcher. The dispatcher takes care of calling the right method at the right stages.

The procedure will get called in 3 distinct phases of the OpenBase server starting. These phases each call the procedure with different parameters. At database start up your procedure is called to describe itself in two distinct steps; first you describe the procedure, then the parameters.

In the template application you implement the ProcedureDescription method to return the description of your procedure to the server that is starting. It is a simple call like:

output.WriteDescription "MyProc", 1,  "call MyProc(paramValue)"

which writes, to the binary stream output, the name of the procedure, the count of parameters, and a textual description of how you might call it like "call MyProc(paramValue)" .

The template provides an example.

The stored procedure is then queried for the list of parameters. You implement the ProcedureParameters method in order to describe the various parameters that can be passed to your stored procedure.
Describing parameters is equally simple and, continuing the example above, is like:

// my proc has 1 parameter of type char 
  Output.WriteParam "MyProc" ,"char" , "test parameter"

  // now the end tag after all parameters have been described
  Output.endTag

Again the template already has an example of of this.

The last, and most important call, is when the procedure is executed in OpenBase. At this point the executeProcedureWithParameters method in the application is called. It accepts all the parameters, runs, generates results, and then terminates.

The template provides a full example.

Implementing executeProcedureWithParameters

This is the heart and soul of your procedure and where everything you want to happen is going to take place.

If you need to access the database you are provided all the relevant info required to connect to the correct database, with a valid user name and password. NOTE : If you connect do not connect and disconnect. You should connect once, stay connected and then disconnect at the end of your procedure.

In the template you’ll see that it connects using the pass parameters.

All the parameters that you specified your procedure would need are in the array called procedureParams. This is an array of STRINGS.

In the template you’ll see that if the procedure is called without a parameter that it does a select * from authors but if there is a parameter than it uses the parameter to select only that record.

Once the select statement is run results are going to be sent back to the Server.
In the template if there was an error, a nil record set is returned from OpenBase. This should be flagged as an error and just returning does this. OpenBase will say that it was expecting results but got none.

If the query worked but found no matches then the record set simply has no rows. In the template this means the query worked and we should return a “success” indicator to OpenBase.

We do this by starting a result set: outputTo.StartReturnResults

writing the list of columns to be returned: outputTo.WriteFieldList array("return")

writing out the data values to be sent back: outputTo.writeValue "success"

and then marking the end of the result set: outputTo.EndTag

The OutputTo parameter we’ve used is passed to the method and is the means you use to pass data back to the OpenBase server so it can relay it to a client application or other procedures in OpenBase. It is a BinaryStream that has a few additional methods added to it.

The case where there are many results to return is just an extension of what we just did.

We start the results set: outputTo.StartReturnResults

write the field list to the output: outputTo.WriteFieldList rs

write each field value we want to return:

outputTo.writeValue obConnection.getBlobField(rs.idxField(i).StringValue)
or
outputTo.writeValue rs.idxField(i).StringValue

mark the end of each row if there will be another row after this one outputTo.EndReturnRow

and mark the end of the return values outputTo.EndTag

Installing your procedure

Note, that a REALbasic procedure, unlike most others, is in a subdirectory that REALbasic names the same as the name of the project.

REALbasic procedures MUST be in a directory. The directory name must end in ".rbsp" and the actual executable must be a name ending in ".proc"

The NAME of the directory for the procedure MUST be the same as the name of the procedure. If your procedure is called DoStuff, and reports that name as it's ProcedureDescription, then the folder must be called DoStuff.rbsp

Place the rbsp folder inside the Procedures directory of either the database, or the global procedures directory.

Reload the procedures with the command "reload_procedures" in openisql.

Note: You need to introduce a newly created procedure to your database. The best way to do this is from OpenIsql. When you have logged on to the relevant database, you need to do:

openbase 1>reload_procedures 
openbase 2>go

This will fill the various _SYS_ tables with metadata about the procedures and make them available for calling.

Alternatively you can restart your database and your procedure will be recognized.

Calling a Stored Procedure

A stored procedure call can be invoked exactly like any other SQL command. You can use openisql to execute a stored procedure or call it directly from your program. Return values are returned exactly the same way as fetching the results of a select statement.

Parameters can be of any type. However, keep in mind that they are converted to string representations before being passed to stored procedures. Parameter values have a maximum length of 4,096 characters. If you want to pass something bigger than 4,096 characters, you may need to store the value in the database as a BLOB and then retrieve it from the stored procedure using a primary key.

You can also call stored procedures from any other stored procedure. However, with transactions, you need to be very careful that you do not create a deadlock. There can be instances in which the stored procedure mechanism flies beneath the radar of the built-in OpenBase deadlock avoidance system. To avoid a deadlock, it is recommended that you do not call any other stored procedures while inside a transaction, unless the subprocedure does not access the database.

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