Wednesday, July 4, 2012

Databases that work with Real Studio

Real Studio is commonly used to create so-called "database applications."  To make this easy, Real Studio includes a standard database API (Database class and subclasses, RecordSet, DatabaseRecord, etc.)  With this standard API, you can connect to many different databases using very similar code.

In general, databases can be divided into two categories: single-user databases and database servers.

Databases included with Real Studio

SQLite (aka RealSQLDatabase) is the single-user database engine included with Real Studio.  SQLite is a public domain database and is used by all kinds of software from browsers such as Firefox to programming tools such as Real Studio and frameworks such as CoreData.

PostgreSQL is a full-blown database server.  It is open-source and free, available for use for just about any purpose.  It can be a bit tricky to install, but is powerful and fast.

PostgreSQL is cross-platform and works on Linux, OS X and Windows.

With Real Studio you use the PostgreSQLDatabase class to connect to a PostgreSQL database server.

Now owned by Oracle, MySQL is also an open-source database server, but has less comprehensible licensing.  Real Studio is able to connect to the Community (free) edition of MySQL, but this edition usually requires you to open source software that connects to it (due to its GPL license).  MySQL is very easy to install and is often available by default on many web servers.  If you choose to use it, be sure you understand its licensing.

MySQL is cross-platform and works on Linux, OS X and Windows.

Use the MySQLCommunityServer class to connect to MySQL.

Oracle Database
Oracle Database is one of the most powerful database servers around.  It is often used for enterprise software and by larger companies.  Oracle Database has a reputation for being a beast to manage and configure, but is also known to be fast.  It can also be rather expensive.  It runs on Windows and Linux and used to be available for OS X, but it does not look like that is an option any longer.

There is also a free Oracle Database Express Edition.

The OracleDatabase class is used to connect to Oracle database.

Microsoft SQL Server
Microsoft SQL Server is a database server that runs only on Microsoft Windows.  It is generally easy to use. Both paid and free versions are available.

The MSSQLServerDatabase class is used to connect to Microsoft SQL Server.  It can only be used with Windows applications.

ODBC is not a database, but rather a standard for connecting to databases.  It stands for Open DataBase Connectivity.  Originally created by Microsoft in the 1990s, it is now an industry standard.  Using Real Studio's ODBC plugin, you can connect to any database for which you have a driver.  Driver availability varies by platform, but I have successfully used ODBC to connect to Microsoft Access, FoxPro and even IBM DB/2 mainframes.

Use the ODBCDatabase class to connect to any database for which you have an ODBC driver.

Other Databases that work with Real Studio

These databases are provided by 3rd party developers and all easily work with Real Studio.

CubeSQL is a database server that uses SQLite as the database back-end.  It is fast and extremely easy to configure.  CubeSQL is cross-platform, running on Linux, OS X and Windows.

To use cubeSQL with Real Studio, you install its plugin and use the CubeSQLServer class to connect.

OpenBase SQL
OpenBase SQL is a database server that was originally created for OpenStep.  It works on OS X, Windows and Linux.

OpenBase SQL comes with a custom library (written in Real Studio itself) to connect to the database.

FrontBase is a database server.  It is freely available for Linux, OS X and Windows.  A native plugin for Real Studio is available from FrontBase, but it is Carbon-only.

Valentina is both a single-user database and a database server.  It is cross-platform for Linux, OS X and Windows and uses a native plugin for Real Studio.  Valentina is a "columnar" database and has a reputation for speed.

Studio Stable Database
Studio Stable Database is a database server that is built on top of SQLite.  It is intended to be a simple way to bring multi-user support to your SQLite applications.  SSD uses a library (written in Real Studio) to connect to the database and is available for free.

MBS SQL and JDBC Plugins
If you need to connect to a database that is not listed here, be sure to check out the SQL Plugin from MonkeyBread which has support for Oracle, Microsoft SQL Server, Sybase, DB2, Informix, Interbase/Firebird, Centura SQLBase, MySQL, PostgreSQL, ODBC and SQLite.

In addition, the JDBC plugin (Java DataBase Connectivity) is particularly useful, allowing you to connect to any database for which there is a JDBC driver.

Database Usage

Other than SQLite, databases that use the Real Studio database API require Real Studio Professional, Web or Enterprise.  When Real Studio 2012 Release 2 is available, databases that use the database API will need a Database Access license in order to use them in your built apps.

The following databases work with Real Studio Personal and will not require a database license for Real Studio 2012 Release 2 (mostly because they do not use the standard Real Studio database API):

  • SQLite (standard API)
  • OpenBase (proprietary class library)
  • Valentina (plugin with proprietary API, standard API is also available but not required)
  • Studio Stable Database (proprietary class library)
  • MBS (proprietary API)

All other databases connect to the database using our standard database API and require Real Studio Professional, Web or Enterprise for now and a Database license starting with Real Studio 2012 Release 2.


ruffin said...

Real Studio is able to connect to the Community (free) edition of MySQL, but this edition usually requires you to open source software that connects to it (due to its GPL license).

That's simply not true. As long as you're not changing the code of the database engine, you can use MySQL for whatever you want without giving anything away.

If you use LibreOffice to write The Next Great American Novel, you don't have to GPL the novel. If you use GNU/Linux to program a RealBasic app, you don't have to open source that either.

That's a pretty important distinction. Unless I've missed something bizarre, the post is pretty misleading.

Jason said...

I contacted Oracle some time ago, and discussed the muddled nature of their licensing. The representative conceded this as an issue, and said they'd work on the matter. The major issue I had is that the plugin itself is under GPL and, after investigation, I found that plugins constitute as code added to a project — requiring, via GPL, that the project itself follow the GPL license. Using a GPL project (e.g. LibreOffice) to create a file isn't the same as using code within another project. If I took a hunk of LibreOffice's code and added it to a project of mine, I'm required to use GPL.

The truth is, chances are this will not come back and bite you, but it could. My work has proprietary algorithms I absolutely can't risk exposing, and if someone came at me legally through the GPL clause aforementioned, I'd be required to share my code... So I switched to PostgreSQL, and I really like how much more capable it is. :)

ruffin said...

Ah, the RealBasic MySQL plugin/MySQLCommunityServer is GPL? That would be much thornier. My apologies if that's the case.

Isn't there a ["Commercial"] licensing option from Oracle that Real Software could use so that RealBasic's plugin doesn't have this issue, even if it's connecting to the Community Edition of MySQL? Or is that still an issue?

This is exactly why I greatly prefer the LGPL...

Unknown said...

Well written article. I develop for MS SQL, SQL Lite (Real SQL), & my SQL. Using the RS API it's all mostly just drop in the database.

Well done Paul

Rick said...

I did not understand. What does it mean? Does it mean that we will need to pay something to use free databases like PostGreSQL or MySQL (MariaDB)?

Unknown said...

Didn't work for older Oracle database version, eg: Oracle version 7 or older

Alyssa said...

@unknown, Oracle 8i and above are supported in Real Studio.