Database Framework
The Database Framework module is the core of a set of cross-platform modules to allow you to connect
to a variety of different databases using a standard set of Types and Functions.
Using a standard framework means that you don't have to learn or know anything about the underlying database API, since the framework takes care of all the nitty-gritty for you. This way, you only have to worry about the data and the SQLs to access it.
Most databases use, or try to be close to the SQL92 (ANSI-standard) specification, which means that generally, you can re-use the same SQL statements on different databases without worrying about it. Obviously, if you decide to use database-specific SQL, you will have to be careful if you decide you want to then use a different type of database.
Since there are many good online resources, tutorials, and books available that discuss and teach SQL, its use won't be described in great detail in this documentation. We leave it up to you to find out what you need to know ;-)
Getting Started
To connect to a database you need one of the available Database Driver modules.
As of this version there are currently drivers available for :
Opening a Connection
A TDBConnection object is your interface to the database.
To create one, you should use the LoadDatabase function, passing in the relevant parameters. The most important parameter is dbType, which tells the Framework which kind of driver it should load for this connection.
It is much like the way other "loaders" work in BlitzMax, allowing you connect to several different types of database within the same application.
LoadDatabase takes other parameters, each of which may or may not be applicable for a certain driver - see the driver documentation for details.
LoadDatabase will return Null if no valid driver was found.
If you've provided enough information, the Framework will try to open a connection
to the database for you. You can check both hasError and the isOpen method on the connection
to determine whether or not it succeeded.
Communicating with the database
Once a connection is open, it's time to start working with the database.
The Framework has two ways of performing actions on a database.
The first is to simply execute a query. The second is to prepare the query, and then execute it.
The first method works like this:
db.executeQuery("DROP TABLE if exists person")
The statement is executed immediately on the database.
The second method, prepare then execute, requires a bit more work to use, but the advantage over the first method is that although the initial prepare may be relatively slow, it allows multiple subsequent executions of the SQL without having to re-process it each time. (and is therefore more efficient over all)
You begin by creating a TDatabaseQuery object,
Local query:TDatabaseQuery = TDatabaseQuery.Create(db)
The next step is to prepare the query,
query.prepare("INSERT INTO person values (NULL, ?, ?)")
With prepared statements/queries you can use placeholders to represent a value that you want to use when you execute it, much like a program variable. In the example above, there are two placeholders, specified by question marks. (Note: Check the database driver documentation for details of placeholder formats)
Before the executing the query you need to bind each placeholder with a value. For example:
For Local i:Int = 0 Until myArray.length
query.setString(0, myArray[i].forename)
query.setString(1, myArray[i].surname)
query.execute()
Next
As you can see, for each new "insertion" we bind a new piece of data to each placeholder. The execution itself is very fast because the SQL has already been prepared.
The add<dbtype>() methods are also available for the supported types, which adds a new bind value to the end of the bindings. (see addString, addInt, addLong, addFloat and addDouble).
For SELECT statements, the TDBConnection executeQuery method also returns a TDatabaseQuery object.
A TDatabaseQuery object can be used to process all the rows of data returned from the SELECT. For example:
Local query:TDatabaseQuery = db.executeQuery("SELECT * FROM person")
or, for prepared queries,
query.execute()
There are two ways to get the row data from the SELECT.
The rowsAffected method can be used to determine the number of rows affected by a delete, insert or update.
Transactions
Most modern databases support transactions of some kind.
A transaction is a block of work that doesn't become finalized on the database until you
commit it. If at some point you want to cancel the transaction, you can "roll" it back to
the state it was in before you started. This means that you won't have half-processed changes
in your data if the server/connection goes down half-way through.
To begin a transaction, you can use the startTransaction method.
Once the transaction is started, you should end it by calling either commit or rollback.
If you close the connection before ending the transaction, the state of the transaction is undetermined - see the specific database documentation for details. It is better to end the transaction yourself :-)
When not in a transaction, the default is for all database changing queries (like insert, delete, update, etc) to auto-commit. That is, the database will reflect the changes immediately.
Examples
The following examples use the DBSQLite module to demonstrate use of the Framework.
Functions Summary
| LoadDatabase |
Loads a database engine of the specific dbType.
|
Types Summary
Functions
| Function LoadDatabase:TDBConnection( dbType:String, dbname:String = Null, host:String = Null, port:Int = Null, user:String = Null, password:String = Null, server:String = Null, options:String = Null ) |
| Description | Loads a database engine of the specific dbType. |
| Information | Optionally, the function takes a set of parameters that can be used to connect to the
database at load time.
See the specific database module documentation for correct dbType name. |
Types
| Type TDatabaseError |
| Description | Contains details of the last error from the driver. |
| Methods Summary |
| isSet |
Determines if there is an outstanding error.
|
| toString |
Returns the full error details.
|
| Field error:String |
| Description | The error text. |
| Field errorType:Int |
| Description | The type of error. |
| Information | Can be one of ERROR_NONE, ERROR_TRANSACTION, ERROR_CONNECTION, ERROR_STATEMENT or ERROR_UNKOWN. |
| Field errorValue:Int |
| Description | The "native" error value. |
| Information | Refer to the specific database documentation for details. |
| Method isSet:Int() |
| Returns | True if this represents an error. |
| Description | Determines if there is an outstanding error. |
| Method toString:String() |
| Description | Returns the full error details. |
| Type TDatabaseQuery |
| Description | A Query object for executing queries and navigating the result sets. |
| Methods Summary |
| addBindValue |
Adds a new TDBType bind value.
|
| addDouble |
Adds a new Double bind value.
|
| addFloat |
Adds a new Float bind value.
|
| addInt |
Adds a new Int bind value.
|
| addLong |
Adds a new Long bind value.
|
| addString |
Adds a new String bind value.
|
| bindValue |
Binds a TDBType value at the specified position.
|
| clearBindValues |
Clears the query bind values.
|
| execute |
Executes an SQL statement.
|
| lastInsertedId |
Returns the id of the last inserted row.
|
| nextRow |
Retrieves the next row in the result set.
|
| prepare |
Prepares an SQL statement for execution.
|
| rowRecord |
Returns the record for the query.
|
| rowsAffected |
Returns the number of rows affected by the previously executed statement.
|
| setDouble |
Binds the Double value at the specified position.
|
| setFloat |
Binds the Float value at the specified position.
|
| setInt |
Binds the Int value at the specified position.
|
| setLong |
Binds the Long value at the specified position.
|
| setString |
Binds the String value at the specified position.
|
| value |
Returns the value of the field at index.
|
| Method addBindValue(value:TDBType) |
| Description | Adds a new TDBType bind value. |
| Information | The value is added to the end of the current list of bind values. |
| Method addDouble(value:Double) |
| Description | Adds a new Double bind value. |
| Information | The value is added to the end of the current list of bind values. |
| Method addFloat(value:Float) |
| Description | Adds a new Float bind value. |
| Information | The value is added to the end of the current list of bind values. |
| Method addInt(value:Int) |
| Description | Adds a new Int bind value. |
| Information | The value is added to the end of the current list of bind values. |
| Method addLong(value:Long) |
| Description | Adds a new Long bind value. |
| Information | The value is added to the end of the current list of bind values. |
| Method addString(value:String) |
| Description | Adds a new String bind value. |
| Information | The value is added to the end of the current list of bind values. |
| Method bindValue(position:Int, value:TDBType) |
| Description | Binds a TDBType value at the specified position. |
| Information | If a bind value already exists at the specified position, it is replaced with the new one. |
| Method clearBindValues() |
| Description | Clears the query bind values. |
| Method execute:Int(statement:String = Null) |
| Returns | True if the execute succeeded. |
| Description | Executes an SQL statement. |
| Information | For a previously prepared statement, pass Null into this method.
Check connection hasError and error for details of any problems. |
| Method lastInsertedId:Long() |
| Description | Returns the id of the last inserted row. |
| Information | Results returned from this method on anything other than an insert on a table with
an auto-incrementing field, are undetermined. |
| Method nextRow:Int() |
| Returns | True if a row was retrieved. |
| Description | Retrieves the next row in the result set. |
| Information | Each call to this method populates a TQueryRecord which can be
retrieved via the record method.
Check connection hasError and error for details of any problems. |
| Method prepare:Int(statement:String) |
| Returns | True if the prepare succeeded. |
| Description | Prepares an SQL statement for execution. |
| Information | Check connection hasError and error for details of any problems. |
| Method rowRecord:TQueryRecord() |
| Description | Returns the record for the query. |
| Method rowsAffected:Int() |
| Description | Returns the number of rows affected by the previously executed statement. |
| Information | Only really useful for inserts, updates and deletes. That is, results on selects are
undetermined. |
| Method setDouble(position:Int, value:Double) |
| Description | Binds the Double value at the specified position. |
| Information | If a bind value already exists at the specified position, it is replaced with the new one. |
| Method setFloat(position:Int, value:Float) |
| Description | Binds the Float value at the specified position. |
| Information | If a bind value already exists at the specified position, it is replaced with the new one. |
| Method setInt(position:Int, value:Int) |
| Description | Binds the Int value at the specified position. |
| Information | If a bind value already exists at the specified position, it is replaced with the new one. |
| Method setLong(position:Int, value:Long) |
| Description | Binds the Long value at the specified position. |
| Information | If a bind value already exists at the specified position, it is replaced with the new one. |
| Method setString(position:Int, value:String) |
| Description | Binds the String value at the specified position. |
| Information | If a bind value already exists at the specified position, it is replaced with the new one. |
| Method value:TDBType(index:Int) |
| Returns | A TDBType object or Null. |
| Description | Returns the value of the field at index. |
| Function Create:TDatabaseQuery(connection:TDBConnection) |
| Description | Creates a new TDatabaseQuery using the supplied connection. |
| Type TDBBlob Extends TDBType |
| Description | A Blob-type field. |
| Information | A blob is binary data, like an image.
Many databases allow storage of binary data. |
| Functions Summary |
| Set |
Creates an instance of TDBBlob with binary data of the specified size.
|
| Method setBlob(v:Byte Ptr, s:Int, copy:Int = True) |
| Information | Note: If copy is True, creates a COPY of the data (using MemAlloc/MemCopy). |
| Function Set:TDBBlob(value:Byte Ptr, size:Int, copy:Int = True) |
| Description | Creates an instance of TDBBlob with binary data of the specified size. |
| Information | Note: If copy is True, creates a COPY of the data (using MemAlloc/MemCopy). |
| Field defaultValue:TDBType |
| Type TDBConnection Abstract |
| Description | Represents a connection to a database. |
| Information | Usually, creating a TDBConnection object is done through a call to LoadDatabase with an
appropriate dbtype parameter.
|
| Method close() Abstract |
| Description | Closes the database connection. |
| Information | Check hasError and error for details of any problems. |
| Method commit:Int() Abstract |
| Returns | True if successful. |
| Description | Commits a database transaction. |
| Information | Calling this method is only valid for a previous call to startTransaction.
Check hasError and error for details of any problems. |
| Method error:TDatabaseError() |
| Returns | A TDatabaseError object. |
| Description | Returns the last database error. |
| Information | Will always return a valid TDatabaseError object. |
| Method executeQuery:TDatabaseQuery(sql:String) |
| Returns | A new TDatabaseQuery object. |
| Description | Executes an sql statement. |
| Information | Check hasError and error for details of any problems. |
| Method getDatabaseName:String() |
| Returns | The database name. |
| Description | Returns the database name. |
| Method getHost:String() |
| Returns | The host, or Null. |
| Description | Returns the connection host. |
| Information | Not all drivers require a Host. |
| Method getPortNumber:Int() |
| Returns | The port number, or 0. |
| Description | Returns the connection port number. |
| Information | Not all drivers require a Port number. |
| Method getTableInfo:TDBTable(tableName:String, withDDL:Int = False) Abstract |
| Method getTables:String[]() Abstract |
| Description | Returns a list of table names for the current database. |
| Method hasError:Int() |
| Returns | True if there is an error.
|
| Description | Determines if there is an outstanding error. |
| Method hasPrepareSupport:Int() Abstract |
| Returns | True if the driver supports Prepare/Execute statements. |
| Description | Determines if the database has support for Prepare/Execute statements. |
| Method hasTransactionSupport:Int() Abstract |
| Returns | True if the driver supports transactions. |
| Description | Determines if the database has transactioning support. |
| Method isOpen:Int() |
| Returns | True if the connection is open. |
| Description | Determines if the database connection is open. |
| Method open:Int(user:String = Null, pass:String = Null) Abstract |
| Returns | True if successful. |
| Description | Attempts to open a new database connection. |
| Information | Check hasError and error for details of any problems. |
| Method resetError() |
| Description | Resets error. |
| Method rollback:Int() Abstract |
| Returns | True if successful. |
| Description | Rolls back a database transaction. |
| Information | Calling this method is only valid for a previous call to startTransaction.
Check hasError and error for details of any problems. |
| Method startTransaction:Int() Abstract |
| Returns | True if successful. |
| Description | Starts a database transaction. |
| Information | Once a transaction has started, it should be eventually closed with a call to either
rollback (if the transaction should be abandoned) or commit (to save all database changes).
Check hasError and error for details of any problems. |
| Type TDBDate Extends TDBDateBase |
| Description | A Date-type field. |
| Information | Note: This type may change! |
| Methods Summary |
| format |
Formats the DateTime using the specified formatting.
|
| Function SetFromString:TDBDate(date:String) |
| Description | Creates a TDBDate from a string. |
| Information | The date should be in the format: YYYY-MM-DD. |
| Type TDBDateTime Extends TDBDateBase |
| Description | A DateTime-type field. |
| Information | Note: This type may change! |
| Methods Summary |
| format |
Formats the DateTime using the specified formatting.
|
| Functions Summary |
| SetFromString |
Creates a TDBDateTime from a string.
|
| Function SetFromString:TDBDateTime(date:String) |
| Description | Creates a TDBDateTime from a string. |
| Information | The datetime should be in the format: YYYY-MM-DD HH:MM:SS. |
| Type TDBDouble Extends TDBType |
| Description | A Double-type field. |
| Methods Summary |
| getDouble |
Returns the double value.
|
| getFloat |
Returns the float representation of the double value.
|
| setDouble |
Sets the double value.
|
| Functions Summary |
| Set |
Creates a new TDBDouble object with value.
|
| Method getDouble:Double() |
| Description | Returns the double value. |
| Method getFloat:Float() |
| Description | Returns the float representation of the double value. |
| Method setDouble(v:Double) |
| Description | Sets the double value. |
| Function Set:TDBDouble(value:Double) |
| Description | Creates a new TDBDouble object with value. |
| Type TDBFloat Extends TDBType |
| Description | A Float-type field. |
| Methods Summary |
| getDouble |
Returns the double representation of the float value.
|
| getFloat |
Returns the float value.
|
| setFloat |
Sets the float value.
|
| Functions Summary |
| Set |
Creates a new TDBFloat object with value.
|
| Method getDouble:Double() |
| Description | Returns the double representation of the float value. |
| Method getFloat:Float() |
| Description | Returns the float value. |
| Method setFloat(v:Float) |
| Description | Sets the float value. |
| Function Set:TDBFloat(value:Float) |
| Description | Creates a new TDBFloat object with value. |
| Type TDBInt Extends TDBType |
| Description | An Integer-type field. |
| Methods Summary |
| getInt |
Returns the int value.
|
| getLong |
Returns a long representation of the value.
|
| setInt |
Sets the int value.
|
| Functions Summary |
| Set |
Creates a new TDBInt object with value.
|
| Method getInt:Int() |
| Description | Returns the int value. |
| Method getLong:Long() |
| Description | Returns a long representation of the value. |
| Method setInt(v:Int) |
| Description | Sets the int value. |
| Function Set:TDBInt(value:Int) |
| Description | Creates a new TDBInt object with value. |
| Type TDBLong Extends TDBType |
| Description | A Long-type field. |
| Methods Summary |
| getInt |
Returns the int representation of the long value.
|
| getLong |
Returns the long value.
|
| setLong |
Sets the long value.
|
| Functions Summary |
| Set |
Creates a new TDBLong object with value.
|
| Method getInt:Int() |
| Description | Returns the int representation of the long value. |
| Method getLong:Long() |
| Description | Returns the long value. |
| Method setLong(v:Long) |
| Description | Sets the long value. |
| Function Set:TDBLong(value:Long) |
| Description | Creates a new TDBLong object with value. |
| Type TDBString Extends TDBType |
| Description | A String-type field. |
| Methods Summary |
| getString |
Returns the string value.
|
| setString |
Sets the string value.
|
| size |
Returns the size of the string.
|
| Functions Summary |
| Set |
Creates a new TDBString object with value.
|
| Method getString:String() |
| Returns | The string or Null. |
| Description | Returns the string value. |
| Method setString(v:String) |
| Description | Sets the string value. |
| Method size:Int() |
| Returns | The size, or 0 if Null. |
| Description | Returns the size of the string. |
| Function Set:TDBString(value:String) |
| Description | Creates a new TDBString object with value. |
| Field columns:TDBColumn[] |
| Type TDBTime Extends TDBDateBase |
| Description | A Time-type field. |
| Information | Note: This type may change! |
| Methods Summary |
| format |
Formats the DateTime using the specified formatting.
|
| Function SetFromString:TDBTime(date:String) |
| Description | Creates a TDBTime from a string. |
| Information | The datetime should be in the format: YYYY-MM-DD HH:MM:SS. |
| Type TDBType |
| Description | The Base for database field types. |
| Information | This type, and its sub-types are used to transparently convert data between BlitzMax
and the database.
Currently implemented types are,
- TDBString
- TDBInt
- TDBDouble
- TDBFloat
- TDBLong
- TDBDate (see note below)
- TDBBlob (see note below)
Please Note: Currently TDBDate and TDBBlob are not fully supported/implemented. I still haven't decided on the
best way to do these - especially the date stuff.
I'm thinking of adding support for Date, DateTime and Time type fields, which are generally supported on
most databases. The only problem is, Blitz doesn't really handle them yet !!
Perhaps I need to build a Date/Calendar module... :-p
|
| Type TQueryField |
| Description | A field definition, including a value if part of a result set record. |
| Field fType:Int |
| Description | Field type. |
| Field length:Int = -1 |
| Description | Field size. |
| Information | Dependent on the type of field. For a DBString field it would indicate number of characters.
A value of -1 means that this is undetermined by the database driver. |
| Field name:String |
| Description | The field name. |
| Field nullable:Int = -1 |
| Description | Whether this field is required or not. |
| Information | True if field is optional (can be NULL), False if field is required (NOT NULL).
A value of -1 means that this is undetermined by the database driver. |
| Field precision:Int = -1 |
| Description | Decimal precision. |
| Information | Only applicable for DBFloat and DBDouble field types.
A value of -1 means that this is undetermined by the database driver. |
| Type TQueryRecord |
| Description | A specific record (or row) for a result set. |
| Method count:Int() |
| Returns | The field count. |
| Description | A count of the number of fields in the record. |
| Method getDouble:Double(index:Int) |
| Description | Returns the double value at index. |
| Information | The result is undetermined if the value at index is not a double field. |
| Method getDoubleByName:String(name:String) |
| Description | Returns the double value for the field name. |
| Information | The result is undetermined if the value at name is not a double field. |
| Method getField:TQueryField(index:Int) |
| Description | Returns the TQueryField object at index. |
| Method getFieldByName:TQueryField(name:String) |
| Description | Returns the named TQueryField object. |
| Method getFloat:Float(index:Int) |
| Description | Returns the float value at index. |
| Information | The result is undetermined if the value at index is not a float field. |
| Method getFloatByName:Float(name:String) |
| Description | Returns the float value for the field name. |
| Information | The result is undetermined if the value at name is not a float field. |
| Method getInt:Int(index:Int) |
| Description | Returns the int value at index. |
| Information | The result is undetermined if the value at index is not an int field. |
| Method getIntByName:Int(name:String) |
| Description | Returns the int value for the field name. |
| Information | The result is undetermined if the value at name is not an int field. |
| Method getLong:Long(index:Int) |
| Description | Returns the long value at index. |
| Information | The result is undetermined if the value at index is not a long field. |
| Method getLongByName:Long(name:String) |
| Description | Returns the long value for the field name. |
| Information | The result is undetermined if the value at name is not a long field. |
| Method getString:String(index:Int) |
| Description | Returns the string value at index. |
| Information | The result is undetermined if the value at index is not a string field. |
| Method getStringByName:String(name:String) |
| Description | Returns the string value for the field name. |
| Information | The result is undetermined if the value at name is not a string field. |
| Method indexOf:Int(name:String) |
| Returns | The field index, or -1 if not found. |
| Description | The index (position) of the field name in the record. |
| Method value:TDBType(index:Int) |
| Returns | a TDBType value object or Null. |
| Description | Returns the value of the field at index. |
Module Information
| Version | 1.08 |
| Author | Bruce A Henderson |
| License | BSD |
| Copyright | Bruce A Henderson |
| Modserver | BRL |
| History | 1.08 |
| History | Fixed prepared statement reuse issue with some drivers. |
| History | Added some integrity checks to TQueryRecord methods. |
| History | Added getTableInfo(), TDBTable and TDBColum. |
| History | Improvements to TDBBlob. |
| History | 1.07 |
| History | Resets error status before execution of new query. |
| History | 1.06 |
| History | Implementation of Date, DateTime and Time types. |
| History | 1.05 |
| History | Improved object cleanup. |
| History | 1.04 |
| History | Improved getFieldByName efficiency. |
| History | Added TQueryRecord helper methods for type/name retrieval - getXXXByName(). |
| History | 1.03 |
| History | Fixed clearing of lasterror after successful query prepare/execute. |
| History | 1.02 |
| History | Added TDatabaseQuery helper binding functions for set/add values. |
| History | Docs update. |
| History | 1.01 |
| History | Fixed Null exception on re-prepare. |
| History | Added TDatabaseQuery clearBindValues() method. |
| History | Added getter methods to TQueryRecord for String, Int, Long, Float and Double. |
| History | Added hasPrepareSupport() and hasTransactionSupport() methods. |
| History | 1.00 |
| History | Initial Release. |