Xojo Developer Conference
Join us from 25th to 27th April 2018 in Denver.

Platforms to show: All Mac Windows Linux Cross-Platform

SQLDatabaseMBS class

Super class: Database

class, SQL, MBS SQL Plugin (SQLDatabase), class SQLDatabaseMBS,
Plugin version: 9.3, Mac: Yes, Win: Yes, Linux: Yes, Console & Web: Yes, Feedback.

Function: The database class for the SQL plugin
Example:
dim db as new SQLDatabaseMBS

// where is the library?
db.SetFileOption SQLConnectionMBS.kOptionLibrarySQLite, getfolderitem("/usr/lib/libsqlite3.0.dylib", folderitem.PathTypeShell)

// connect to database
// in this example it is SQLite,
// but can also be Sybase, Oracle, Informix, DB2, SQLServer, InterBase, MySQL, SQLBase and ODBC

dim path as string

if TargetMacOS then
path = "/tmp/test.db" // put the database in the temporary folder
else
path = "test.db" // for Windows and Linux in the current folder the application is inside.
end if

db.DatabaseName = "sqlite:"+path

if db.Connect then

MsgBox "We are connected!"

// Disconnect is optional
// autodisconnect will ocur in destructor if needed
db.close

msgbox "We are disconnected!"
end if
Notes:
You can use the SQL plugin without using Xojo (Real Studio) built in database classes if you use the SQLConnectionMBS and SQLCommandMBS classes.

Or you use the SQLDatabaseMBS class which is a subclass of the database class and can be used with REALbasics RecordSet class. The current implementation is not complete. You can connect with passing the database URL in the DatabaseName property of the SQLDatabaseMBS class. You prefix this URL with the database type you are using.

You can use Execute and Select to run SQL statements. Errors can be queries with the lasterror properties. For the RecordSet, you can get the column count, the column names and values and move to the next row. All the other methods like deleting a record or updating a value are not implemented and you need to use SQL commands to do this.

Supported databases: Oracle, Microsoft SQL Server, DB2, Sybase, Informix, InterBase/Firebird, SQLBase, MySQL, PostgreSQL and ODBC and SQLite.

As field and table schema functions are not implemented, you can't use this database with the database browser features in the Xojo (Real Studio) IDE.
The plugin does not provide RecordCount on RecordSet class. For that you need to make a extra SELECT count(*) query.

With Xojo 2013r1, you only need a database server license from Xojo, Inc. if you use the SQLDatabaseMBS class. The SQLConnectionMBS class does not require this license. But some features like getting a recordset do need the license as they refer to the SQLDatabaseMBS class.

Please free all RecordSets and SQLCommand objects before you close the SQLConnection or the SQLDatabase. The plugin keeps references from RecordSets and SQLCommand to prevent automatic destruction of the database connection. If you close a database connection while you have RecordSets and SQLCommand in use, things may go wrong.

The plugin can cache the recordset locally. To enable you can call SQLCommandMBS.Cache or use the Option("AutoCache") = "true" on either command or connection or database objects. The plugin will than fetch all records and store them in memory. After this you can walk over the recordset and use FetchPos, FetchFirst, FetchLast, FetchPrev and FetchNext to locate the rows you need. When you call Field() you always get last row, but to read from cached result set, please use Value() function. When using RecordSet, the values are read via Value() functions automatically.

You can use InternalPostgreSQLLibraryMBS or InternalSQLiteLibraryMBS if you like to use our built in SQLite or PostgreSQL database libraries.
Subclass of the Database class.

Isolation Levels

Constant Value Description
kANSILevel0 0 ANSI Level 0
kANSILevel1 1 ANSI Level 1
kANSILevel2 2 ANSI Level 2
kANSILevel3 3 ANSI Level 3
kLevelUnknown -1 Unknown
kReadCommitted 1 Read committed.
kReadUncommitted 0 Read uncommitted.
kRepeatableRead 2 Repeatable read.
kSerializable 3 Serializable.

Values for autocommit property

Constant Value Description
kAutoCommitOff 0 Autocommit is off.
kAutoCommitOn 1 Autocommit is on.
kAutoCommitUnknown -1 Autocommit unknown

Command Types

Constant Value Description
kCommandTypeSQLStatement 1 Command is an SQL statement.
kCommandTypeSQLStatementRaw 2 Command is an SQL statement that mustn't be interpreted by SQLAPI.
kCommandTypeStoredProcedure 3 Command is a stored procedure or a function.
kCommandTypeUnknown 0 Used by default. Library detects command type automatically.

Error Codes

Constant Value Description
kErrorBindVarNotFound 7 Bind variable not found.
kErrorClientInitFails 6 Initialization failed for client.
kErrorClientNotSet 1 Client not set.
kErrorClientNotSupported 2 Unsupported client type for this platform.
kErrorClientVersionOld 5 Library file is too old.
kErrorFieldNotFound 8 Field not found.
kErrorGetLibraryVersionFails 4 Failed to query library version.
kErrorLoadLibraryFails 3 Failed to load a library. For example path could be wrong or 32/64bit mismatch.
kErrorNoMemory 0 Out of memory.
kErrorUnknownColumnType 11 Unknown column type.
kErrorUnknownDataType 9 Unknown data type.
kErrorUnknownParameterType 10 Unknown parameter type.
kErrorWrongConversion 12 Failed to convert a value, e.g. string to number.
kErrorWrongDatetime 13 Can't convert text to date.

Options to specify the library with SetFileOption

Constant Value Description
kOptionLibraryDB2 "DB2CLI.LIBS" for DB2.
kOptionLibraryFirebird "IBASE.LIBS" for Firebird.
kOptionLibraryInformix "INFCLI.LIBS" for Informix.
kOptionLibraryInterbase "IBASE.LIBS" for Interbase.
kOptionLibraryMySQL "MYSQL.LIBS" for MySQL. Library extension on Mac is ".dylib", on Linux ".so" and on Windows ".dll". You get this library with the MySQL download on their homepage.
kOptionLibraryODBC "ODBC.LIBS" for ODBC.
kOptionLibraryOracle "OCI8.LIBS" for Oracle.
kOptionLibraryPostgreSQL "LIBPQ.LIBS" for Postgre SQL.
kOptionLibrarySQLanywhere "SQLANY.LIBS" for SQL Anywhere.
kOptionLibrarySQLBase "SQLBASE.LIBS" for SQLbase.
kOptionLibrarySQLite "SQLITE.LIBS" for SQLite. Can also be the spatialite or SQLcipher library.
kOptionLibrarySybaseComn "SYBCOMN.LIBS" for Sybase.
kOptionLibrarySybaseCS "SYBCS.LIBS" for Sybase.
kOptionLibrarySybaseCT "SYBCT.LIBS" for Sybase.
kOptionLibrarySybaseIntl "SYBINTL.LIBS" for Sybase.
kOptionLibrarySybaseTCL "SYBTCL.LIBS" for Sybase.

This class has no sub classes.

Some examples which use this class:


The items on this page are in the following plugins: MBS SQL Plugin.


SQLConnectionMBS   -   SQLDataConsumerMBS




Links
MBS FileMaker tutorial videos