Use the static instance of the class dbSession, called 'dbsession', to scan the available ODBC data sources and retrieve their names, it is not necessary to allocate a new instance of the class dbSession, in the file 'db.txt' an instance is defined. The first instance of this class attempts to load all the necessary ODBC software and initialise it. To check if initialization was sucessfull the method initialized/1 can be called, if false is returned the application cannot open any databases.
With the dbSession class it is possible to scan the available ODBC data sources and retrieve their names, see the methods iterateDatabases/3, firstDatabase/4 and nextDatabase/3.
| dbsession<-iterateDatabases(all, List, true) %Use the static instance inside the file 'db.txt' |
If a name for an ODBC data source is known then it is possible to open it by using an instance of the database class. This is done by allocating a new instance of this class and then call any of the open (open/1, open/2 and open/4) methods. The open/1 method can be used even if a database requires an username and password to open. The ODBC database driver or the database engine will show a dialog requesting more login information when required.
| DB new_obj database, DB<-open(DataBaseName, UserName, Password, Output), (Output == false -> (showMsg('Opening database failed!'), del_obj DB, fail) ; (newBrowseWindow(DB) )). |
In the example above the if-then-else operator is used to delete the new database instance if opening the database failed!
If an application opens and closes the same database more than once then it is better to use persistentOpen/4. If a database connection is opened with this method and then closed the actual internal ODBC database connection is not closed but stored for (possible) later use. If persistentOpen/4 is then once again used for the same database (with the same username and password) the already present ODBC database connection is used again. Re-using a database connection is much faster then closing an existing connection and opening a new connection.
After a database was opened it is possible to have SQL statements executed by the database. An instance of the class query must be used to pass SQL statements to the database. Allocate a (new) instance of the class query and then call the method putDatabase/2 to associate the query with the database, after these two steps the query instance is ready to execute SQL statements.
| _Query new_obj query, _Query<-putDatabase(_DB, true), |
Associating a query instance with a database is what some databases call opening a connection.
 There are some DBMS's that do not allow more than 1
connection from a single application to a single database. Make sure to check if a
database allows multiple connections to the same database before using two query instances
simultaneously. If the debug mode of ODBC is turned on then a message like 'Connection is
busy with results for another hstmt' might be displayed. |
SQL statements can be directly executed by calling the method execute/3.
| %Make sure query instance is closed before a new SQL
statement is executed _Query<-close, _Query<-execute('select * from customers', Result, ErrorCode), |
In the example above the method close/0 is first called, this is done so the same query instance can be used for all SQL statements instead of allocating a new instance for each separate SQL statement. The second parameter of execute/3 is assigned the boolean value true if the statement was correctly executed, if there was an error then was false assigned. The third parameter is, in case of an error, assigned a more specific error code, this error code can be translated to an error messages by the predicate dberror/2.
A result of a SQL 'SELECT' statement is a result set of records that can be iterated by using the query class, see retrieving results for more information about this.
A database can be closed by either deleting the database instance or by first calling the close/1 method and then deleting the instance. If a database is deleted or closed all the query instances associated with the same database (by using the method putDatabase/2) are also closed and deleted.
| del_obj _DB, %Delete the database instance, all query instances also deleted |
After the last instance of the class dbSession is deleted it is not possible to use any of the database classes anymore.
| del_obj dbSession. %Delete all instances of the class dbSession |