Some Stored Procedure Examples

Posted by paul on Dec 16, 2008 in Uncategorized | 1 comment

This article originally appeared in REALbasic Developer Magazine 4.3 (Jan/Feb 2006)

Now that we know what stored procedures are, let’s take a look at a few more examples. The accompanying Code Listing shows a simple stored procedure for MySQL, PostgreSQL, OpenBase, FrontBase, SQL Server and Oracle. As mentioned last time, the various syntax is similar but also just different enough to be annoying.

How is this related to REALbasic?

REALbasic does not provide a built-in way to call stored procedures, and it provides a nice set of database classes that work with a wide variety of databases. I guess you could say that REALbasic is encouraging you not to use stored procedures at all. That’s not entirely true, but most languages do make it a bit easier to work with them. For example, with VB.NET and ADO.NET you can use the ExecuteNonQuery method to call a stored procedure. Java with JDBC also provides a way to call stored procedures.

In the case of REALbasic, you’ll need to use the appropriate syntax for your database server to call the stored procedure using SQL.

For example, with Microsoft SQL Server (on Windows, connected via ODBC) you would use this code to run a stored procedure called INSERT_TEST:

mySQLServerODBCDatabase.SQLExecute("exec INSERT_TEST")

For Oracle you should enclose the stored procedure call in a block:

Dim spString As String
spString = "BEGIN" + EndOfLine + "INSERT_TEST" + EndOfLine + "END"
myOracleServerDatabase.SQLExecute(spString)

Of course, this technique provides no way for you to easily pass parameters to the procedure and certainly no easy way to get results or parameters back. A workaround for sending parameters would be to insert them directly into the string, which will work for simple datatypes. You could also insert your parameters into a special parameter table and then have the stored procedure look for them there. For return values and results, you could also insert the information into a table and then have your app get it with a SQL SELECT.

Obviously this doesn’t help you a whole lot of you are trying to hook up a REALbasic application to an existing database with stored procedures, which you may not be able to modify. One solution to this dilema is to create new wrapper procedures for the existing stored procedures. Your REALbasic code calls the wrapper which either puts or retrieves the data in the parameter tables and then can call the original stored procedure.

Using JDBC and MonkeyBread

Another option that has recently become available is to use the JDBC classes that are part of the Java plugin included in the MonkeyBread Plugin Suite. It contains a class, JavaCallableStatementMBS, specifically for calling stored procedures. Unfortunately the documentation and examples do not cover this feature well. It looks like you have to set up your parameters individually and then call the procedure using executeQuery.

OpenBase SQL 10

I should note that OpenBase SQL 10 has the ability to create stored procedures using REALbasic. This is not well documented, but apparently REALbasic console applications can be run as stored procedures on the database server.

Stored Procedure Code Samples

Oracle PL/SQL

CREATE PROCEDURE INSERT_TEST AS
BEGIN
INSERT INTO testlog (name, connectTime, status)
VALUES ('Bob Roberts', SYSDATE, 'Active');

COMMIT;

END;

SQL Server T-SQL

CREATE PROCEDURE INSERT_TEST AS
INSERT INTO TestLog (name, connectTime, status)
VALUES ('Bob Roberts', GetDate(), 'Active')

RETURN

GO

PostgreSQL PL/pgSQL

:

CREATE PROCEDURE INSERT_TEST AS
BEGIN
INSERT INTO testlog (name, connectTime, status)
VALUES ('Bob Roberts', now(), 'Active');

COMMIT;

END;

MySQL

CREATE PROCEDURE INSERT_TEST()
BEGIN
INSERT INTO TestLog (name, connectTime, status)
VALUES ('Bob Roberts', CURRDATE(), 'Active');
END

OpenBase OpenScript

START TRANSACTION TRY
REQUIRE EXECUTE SQL "INSERT INTO TestLog (name, connectTime, status) VALUES ('Bob Roberts', CURRDATE(), 'Active')"
REQUIRE COMMIT
CATCH
ROLLBACK
END TRY

FrontBase Stored Procedure

create procedure INSERT_TEST()
BEGIN
INSERT INTO TestLog (name, connectTime, status)
VALUES ('Bob Roberts', CURRDATE(), 'Active');
END;