BaH.Database: Functions Types Modinfo Source  

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

TDatabaseError Contains details of the last error from the driver.
TDatabaseQuery A Query object for executing queries and navigating the result sets.
TDBBlob A Blob-type field.
TDBColumn  
TDBConnection Represents a connection to a database.
TDBDate A Date-type field.
TDBDateTime A DateTime-type field.
TDBDouble A Double-type field.
TDBFloat A Float-type field.
TDBInt An Integer-type field.
TDBLong A Long-type field.
TDBString A String-type field.
TDBTable  
TDBTime A Time-type field.
TDBType The Base for database field types.
TQueryField A field definition, including a value if part of a result set record.
TQueryRecord A specific record (or row) for a result set.

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 )
DescriptionLoads a database engine of the specific dbType.
InformationOptionally, 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
DescriptionContains details of the last error from the driver.
Fields Summary
error , errorType , errorValue
Methods Summary
isSet Determines if there is an outstanding error.
toString Returns the full error details.
Field error:String
DescriptionThe error text.
Field errorType:Int
DescriptionThe type of error.
InformationCan be one of ERROR_NONE, ERROR_TRANSACTION, ERROR_CONNECTION, ERROR_STATEMENT or ERROR_UNKOWN.
Field errorValue:Int
DescriptionThe "native" error value.
InformationRefer to the specific database documentation for details.
Method isSet:Int()
ReturnsTrue if this represents an error.
DescriptionDetermines if there is an outstanding error.
Method toString:String()
DescriptionReturns the full error details.

Type TDatabaseQuery
DescriptionA 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.
Functions Summary
Create Creates a new TDatabaseQuery using the supplied connection.
Method addBindValue(value:TDBType)
DescriptionAdds a new TDBType bind value.
InformationThe value is added to the end of the current list of bind values.
Method addDouble(value:Double)
DescriptionAdds a new Double bind value.
InformationThe value is added to the end of the current list of bind values.
Method addFloat(value:Float)
DescriptionAdds a new Float bind value.
InformationThe value is added to the end of the current list of bind values.
Method addInt(value:Int)
DescriptionAdds a new Int bind value.
InformationThe value is added to the end of the current list of bind values.
Method addLong(value:Long)
DescriptionAdds a new Long bind value.
InformationThe value is added to the end of the current list of bind values.
Method addString(value:String)
DescriptionAdds a new String bind value.
InformationThe value is added to the end of the current list of bind values.
Method bindValue(position:Int, value:TDBType)
DescriptionBinds a TDBType value at the specified position.
InformationIf a bind value already exists at the specified position, it is replaced with the new one.
Method clearBindValues()
DescriptionClears the query bind values.
Method execute:Int(statement:String = Null)
ReturnsTrue if the execute succeeded.
DescriptionExecutes an SQL statement.
InformationFor a previously prepared statement, pass Null into this method.

Check connection hasError and error for details of any problems.

Method lastInsertedId:Long()
DescriptionReturns the id of the last inserted row.
InformationResults returned from this method on anything other than an insert on a table with an auto-incrementing field, are undetermined.
Method nextRow:Int()
ReturnsTrue if a row was retrieved.
DescriptionRetrieves the next row in the result set.
InformationEach 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)
ReturnsTrue if the prepare succeeded.
DescriptionPrepares an SQL statement for execution.
InformationCheck connection hasError and error for details of any problems.
Method rowRecord:TQueryRecord()
DescriptionReturns the record for the query.
Method rowsAffected:Int()
DescriptionReturns the number of rows affected by the previously executed statement.
InformationOnly really useful for inserts, updates and deletes. That is, results on selects are undetermined.
Method setDouble(position:Int, value:Double)
DescriptionBinds the Double value at the specified position.
InformationIf a bind value already exists at the specified position, it is replaced with the new one.
Method setFloat(position:Int, value:Float)
DescriptionBinds the Float value at the specified position.
InformationIf a bind value already exists at the specified position, it is replaced with the new one.
Method setInt(position:Int, value:Int)
DescriptionBinds the Int value at the specified position.
InformationIf a bind value already exists at the specified position, it is replaced with the new one.
Method setLong(position:Int, value:Long)
DescriptionBinds the Long value at the specified position.
InformationIf a bind value already exists at the specified position, it is replaced with the new one.
Method setString(position:Int, value:String)
DescriptionBinds the String value at the specified position.
InformationIf a bind value already exists at the specified position, it is replaced with the new one.
Method value:TDBType(index:Int)
ReturnsA TDBType object or Null.
DescriptionReturns the value of the field at index.
Function Create:TDatabaseQuery(connection:TDBConnection)
DescriptionCreates a new TDatabaseQuery using the supplied connection.

Type TDBBlob Extends TDBType
DescriptionA Blob-type field.
InformationA blob is binary data, like an image.
Many databases allow storage of binary data.
Methods Summary
setBlob  
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)
InformationNote: If copy is True, creates a COPY of the data (using MemAlloc/MemCopy).
Function Set:TDBBlob(value:Byte Ptr, size:Int, copy:Int = True)
DescriptionCreates an instance of TDBBlob with binary data of the specified size.
InformationNote: If copy is True, creates a COPY of the data (using MemAlloc/MemCopy).

Type TDBColumn
Fields Summary
dbType , defaultValue , name , nullable
Field dbType:Int
Field defaultValue:TDBType
Field name:String
Field nullable:Int

Type TDBConnection Abstract
DescriptionRepresents a connection to a database.
InformationUsually, creating a TDBConnection object is done through a call to LoadDatabase with an appropriate dbtype parameter.
Methods Summary
close Closes the database connection.
commit Commits a database transaction.
error Returns the last database error.
executeQuery Executes an sql statement.
getDatabaseName Returns the database name.
getHost Returns the connection host.
getPortNumber Returns the connection port number.
getTableInfo  
getTables Returns a list of table names for the current database.
hasError Determines if there is an outstanding error.
hasPrepareSupport Determines if the database has support for Prepare/Execute statements.
hasTransactionSupport Determines if the database has transactioning support.
isOpen Determines if the database connection is open.
open Attempts to open a new database connection.
resetError Resets error.
rollback Rolls back a database transaction.
startTransaction Starts a database transaction.
Method close() Abstract
DescriptionCloses the database connection.
InformationCheck hasError and error for details of any problems.
Method commit:Int() Abstract
ReturnsTrue if successful.
DescriptionCommits a database transaction.
InformationCalling this method is only valid for a previous call to startTransaction.

Check hasError and error for details of any problems.

Method error:TDatabaseError()
ReturnsA TDatabaseError object.
DescriptionReturns the last database error.
InformationWill always return a valid TDatabaseError object.
Method executeQuery:TDatabaseQuery(sql:String)
ReturnsA new TDatabaseQuery object.
DescriptionExecutes an sql statement.
InformationCheck hasError and error for details of any problems.
Method getDatabaseName:String()
ReturnsThe database name.
DescriptionReturns the database name.
Method getHost:String()
ReturnsThe host, or Null.
DescriptionReturns the connection host.
InformationNot all drivers require a Host.
Method getPortNumber:Int()
ReturnsThe port number, or 0.
DescriptionReturns the connection port number.
InformationNot all drivers require a Port number.
Method getTableInfo:TDBTable(tableName:String, withDDL:Int = False) Abstract
Method getTables:String[]() Abstract
DescriptionReturns a list of table names for the current database.
Method hasError:Int()
ReturnsTrue if there is an error.
DescriptionDetermines if there is an outstanding error.
Method hasPrepareSupport:Int() Abstract
ReturnsTrue if the driver supports Prepare/Execute statements.
DescriptionDetermines if the database has support for Prepare/Execute statements.
Method hasTransactionSupport:Int() Abstract
ReturnsTrue if the driver supports transactions.
DescriptionDetermines if the database has transactioning support.
Method isOpen:Int()
ReturnsTrue if the connection is open.
DescriptionDetermines if the database connection is open.
Method open:Int(user:String = Null, pass:String = Null) Abstract
ReturnsTrue if successful.
DescriptionAttempts to open a new database connection.
InformationCheck hasError and error for details of any problems.
Method resetError()
DescriptionResets error.
Method rollback:Int() Abstract
ReturnsTrue if successful.
DescriptionRolls back a database transaction.
InformationCalling this method is only valid for a previous call to startTransaction.

Check hasError and error for details of any problems.

Method startTransaction:Int() Abstract
ReturnsTrue if successful.
DescriptionStarts a database transaction.
InformationOnce 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
DescriptionA Date-type field.
InformationNote: This type may change!
Methods Summary
format Formats the DateTime using the specified formatting.
Functions Summary
SetFromString Creates a TDBDate from a string.
Method format:String(fmt:String = "%Y-%m-%d")
DescriptionFormats the DateTime using the specified formatting.
Function SetFromString:TDBDate(date:String)
DescriptionCreates a TDBDate from a string.
InformationThe date should be in the format: YYYY-MM-DD.

Type TDBDateTime Extends TDBDateBase
DescriptionA DateTime-type field.
InformationNote: This type may change!
Methods Summary
format Formats the DateTime using the specified formatting.
Functions Summary
SetFromString Creates a TDBDateTime from a string.
Method format:String(fmt:String = "%Y-%m-%d %H:%M:%S")
DescriptionFormats the DateTime using the specified formatting.
Function SetFromString:TDBDateTime(date:String)
DescriptionCreates a TDBDateTime from a string.
InformationThe datetime should be in the format: YYYY-MM-DD HH:MM:SS.

Type TDBDouble Extends TDBType
DescriptionA 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()
DescriptionReturns the double value.
Method getFloat:Float()
DescriptionReturns the float representation of the double value.
Method setDouble(v:Double)
DescriptionSets the double value.
Function Set:TDBDouble(value:Double)
DescriptionCreates a new TDBDouble object with value.

Type TDBFloat Extends TDBType
DescriptionA 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()
DescriptionReturns the double representation of the float value.
Method getFloat:Float()
DescriptionReturns the float value.
Method setFloat(v:Float)
DescriptionSets the float value.
Function Set:TDBFloat(value:Float)
DescriptionCreates a new TDBFloat object with value.

Type TDBInt Extends TDBType
DescriptionAn 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()
DescriptionReturns the int value.
Method getLong:Long()
DescriptionReturns a long representation of the value.
Method setInt(v:Int)
DescriptionSets the int value.
Function Set:TDBInt(value:Int)
DescriptionCreates a new TDBInt object with value.

Type TDBLong Extends TDBType
DescriptionA 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()
DescriptionReturns the int representation of the long value.
Method getLong:Long()
DescriptionReturns the long value.
Method setLong(v:Long)
DescriptionSets the long value.
Function Set:TDBLong(value:Long)
DescriptionCreates a new TDBLong object with value.

Type TDBString Extends TDBType
DescriptionA 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()
ReturnsThe string or Null.
DescriptionReturns the string value.
Method setString(v:String)
DescriptionSets the string value.
Method size:Int()
ReturnsThe size, or 0 if Null.
DescriptionReturns the size of the string.
Function Set:TDBString(value:String)
DescriptionCreates a new TDBString object with value.

Type TDBTable
Fields Summary
columns , ddl , name
Field columns:TDBColumn[]
Field ddl:String
Field name:String

Type TDBTime Extends TDBDateBase
DescriptionA Time-type field.
InformationNote: This type may change!
Methods Summary
format Formats the DateTime using the specified formatting.
Functions Summary
SetFromString Creates a TDBTime from a string.
Method format:String(fmt:String = "%H:%M:%S")
DescriptionFormats the DateTime using the specified formatting.
Function SetFromString:TDBTime(date:String)
DescriptionCreates a TDBTime from a string.
InformationThe datetime should be in the format: YYYY-MM-DD HH:MM:SS.

Type TDBType
DescriptionThe Base for database field types.
InformationThis 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
DescriptionA field definition, including a value if part of a result set record.
Fields Summary
fType , length , name , nullable , precision
Field fType:Int
DescriptionField type.
Field length:Int = -1
DescriptionField size.
InformationDependent 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
DescriptionThe field name.
Field nullable:Int = -1
DescriptionWhether this field is required or not.
InformationTrue 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
DescriptionDecimal precision.
InformationOnly applicable for DBFloat and DBDouble field types.
A value of -1 means that this is undetermined by the database driver.

Type TQueryRecord
DescriptionA specific record (or row) for a result set.
Methods Summary
count A count of the number of fields in the record.
getDouble Returns the double value at index.
getDoubleByName Returns the double value for the field name.
getField Returns the TQueryField object at index.
getFieldByName Returns the named TQueryField object.
getFloat Returns the float value at index.
getFloatByName Returns the float value for the field name.
getInt Returns the int value at index.
getIntByName Returns the int value for the field name.
getLong Returns the long value at index.
getLongByName Returns the long value for the field name.
getString Returns the string value at index.
getStringByName Returns the string value for the field name.
indexOf The index (position) of the field name in the record.
value Returns the value of the field at index.
Method count:Int()
ReturnsThe field count.
DescriptionA count of the number of fields in the record.
Method getDouble:Double(index:Int)
DescriptionReturns the double value at index.
InformationThe result is undetermined if the value at index is not a double field.
Method getDoubleByName:String(name:String)
DescriptionReturns the double value for the field name.
InformationThe result is undetermined if the value at name is not a double field.
Method getField:TQueryField(index:Int)
DescriptionReturns the TQueryField object at index.
Method getFieldByName:TQueryField(name:String)
DescriptionReturns the named TQueryField object.
Method getFloat:Float(index:Int)
DescriptionReturns the float value at index.
InformationThe result is undetermined if the value at index is not a float field.
Method getFloatByName:Float(name:String)
DescriptionReturns the float value for the field name.
InformationThe result is undetermined if the value at name is not a float field.
Method getInt:Int(index:Int)
DescriptionReturns the int value at index.
InformationThe result is undetermined if the value at index is not an int field.
Method getIntByName:Int(name:String)
DescriptionReturns the int value for the field name.
InformationThe result is undetermined if the value at name is not an int field.
Method getLong:Long(index:Int)
DescriptionReturns the long value at index.
InformationThe result is undetermined if the value at index is not a long field.
Method getLongByName:Long(name:String)
DescriptionReturns the long value for the field name.
InformationThe result is undetermined if the value at name is not a long field.
Method getString:String(index:Int)
DescriptionReturns the string value at index.
InformationThe result is undetermined if the value at index is not a string field.
Method getStringByName:String(name:String)
DescriptionReturns the string value for the field name.
InformationThe result is undetermined if the value at name is not a string field.
Method indexOf:Int(name:String)
ReturnsThe field index, or -1 if not found.
DescriptionThe index (position) of the field name in the record.
Method value:TDBType(index:Int)
Returnsa TDBType value object or Null.
DescriptionReturns the value of the field at index.

Module Information

Version1.08
AuthorBruce A Henderson
LicenseBSD
CopyrightBruce A Henderson
ModserverBRL
History1.08
HistoryFixed prepared statement reuse issue with some drivers.
HistoryAdded some integrity checks to TQueryRecord methods.
HistoryAdded getTableInfo(), TDBTable and TDBColum.
HistoryImprovements to TDBBlob.
History1.07
HistoryResets error status before execution of new query.
History1.06
HistoryImplementation of Date, DateTime and Time types.
History1.05
HistoryImproved object cleanup.
History1.04
HistoryImproved getFieldByName efficiency.
HistoryAdded TQueryRecord helper methods for type/name retrieval - getXXXByName().
History1.03
HistoryFixed clearing of lasterror after successful query prepare/execute.
History1.02
HistoryAdded TDatabaseQuery helper binding functions for set/add values.
HistoryDocs update.
History1.01
HistoryFixed Null exception on re-prepare.
HistoryAdded TDatabaseQuery clearBindValues() method.
HistoryAdded getter methods to TQueryRecord for String, Int, Long, Float and Double.
HistoryAdded hasPrepareSupport() and hasTransactionSupport() methods.
History1.00
HistoryInitial Release.