2 How to use the BetaDBC interface

2.1 Introduction

This section is a step-by-step guide to get you started using BetaDBC.

The first time you connect to a database from a Beta program, it is a 3 step process:

Set up your system
Initialize database and users
Write some Beta code

Step 1 is necessary once per platform, step 2 is necessary once per database/user and step 3 is always necessary :)

This may be the most troublesome of the 3 steps, but on the bright side you may not have to do it at all. Perhaps your systems administrator has already done it. If you are stuck with the job then proceed as follows:

2.2.1 On Windows

First: Install your database manager according to the installation guidelines that came with it.

Then:

If your Beta system uses Microsoft tools (as opposed to GNU tools) and your database manager is Oracle or Frontbase, you are in luck. No further setup is required.
If your Beta system uses Microsoft tools and your database manager is MySQL you must do the following:
Copy the file mysql\lib\opt\libmySQL.lib
into the directory beta\lib\nti_ms\
Otherwise you must connect to your database manager via ODBC. A driver for that purpose is either included in the standard database manager installation (example: Microsoft Access), or must be installed separately (example: MySQL).
To set up the driver, click Start > Settings > Control Panel > ODBC Data Sources (32-bit)
Click the tab 'User DNS'
Look at the list 'User Data Sources'
If you do not see a driver for your database manager, you need to download and install one.
Click the driver of your database manager and click 'Configure'
You can now adjust various parameters of the data source depending on the driver. In particular, you can probably set the name of the data source as well as a user name and password. These settings are used when you open a connection in a Beta program.

2.2.2 On Unix

First: Install your database manager according to the installation guidelines that came with it.

Then:

If your database manager is supported by a native BetaDBC library - currently Oracle, Frontbase and MySQL - you are in luck. No further setup is required.
Otherwise you must connect to your database manager via ODBC. You will need a .odbc.ini file in your home directory. Create such a file if it does not exist. Suppose a driver for the PostgreSQL database manager is located in /usr/local/lib/libcliPG.so. If you want to connect to the database 'mydb' on the database server 'delirium' insert the following in your .odbc.ini file ('ReadOnly = 0' tells the driver that you want to manipulate the 'mydb' database)
[mydatasource] # data source containing the 'mydb' db on delirium Driver = /usr/local/lib/libcliPG.so Database = mydb Servername = delirium ReadOnly = 0
This defines a data source named 'mydatasource', that you may use BetaDBC to connect to. In the example 'ReadOnly' is a driver specific attribute of the data source 'mydatasource'. See the documentation for the drivers used for definitions of driver specific attributes.

2.3 Initializing a database

2.3.1 Frontbase

sql92 is a small tool included in the FrontBase installation. It is used for creating databases and database users. By default it is located in FrontBase/bin/sql92

A small example:
start sql92 and type the following:

create database firstdb;
connect to firstdb user _system;
create user firstuser;
disconnect current;
connect to firstdb user firstuser;
disconnect current;

2.3.2 MySQL

When the MySQL database manager is up and running it has by default only one user called 'root' whose password is empty. New users are created using eg. GRANT statements as follows:

Start MySQL as user 'root' on the database 'mysql':

./bin/mysql -u root mysql

Create a user called 'firstusr' whose password is 'firstpswd':

mysql> GRANT ALL PRIVILEGES ON *.* TO firstusr@localhost IDENTIFIED BY 'firstpswd' WITH GRANT OPTION;
mysql> GRANT ALL PRIVILEGES ON *.* TO firstusr@"%" IDENTIFIED BY 'firstpswd' WITH GRANT OPTION;

Additional users can be created in a similar fashion. Now create a database for the user 'firstusr':

./bin/mysqladmin -u firstusr -p create firstdb

This creates a database 'firstdb' after prompting for the password. The user 'firstusr' can now log on to the database 'firstdb':

./bin/mysql -u firstusr -p firstdb

Tables can now be created using standard SQL statements:

mysql> CREATE TABLE test (name CHAR(200) NOT NULL, KEY index_name (name(10)));

2.4 Writing the Beta code

2.4.1 Including an interface

You must always INCLUDE the relevant interface

If you use ODBC:	`INCLUDE '~beta/betadbc/betadbc_odbc'`
If you use native Oracle:	`INCLUDE '~beta/betadbc/betadbc_oracle'`
If you use native Frontbase:	`INCLUDE '~beta/betadbc/betadbc_frontbase'`
If you use native MySql:	`INCLUDE '~beta/betadbc/betadbc_mysql'`

2.4.2 Opening a connection

You must first create an instance of the connection pattern.

sqlCon: @Connection;
stmt: @sqlCon.directSqlStatement;

Calling the open method on a connection...

('access','marius',none)->sqlCon.open

...then makes it possible to communicate with the data source. The statement opens a connection to the data source name "access" for the user "marius" without specifying a password.

2.4.3 SQL Statements

Data manipulation and definition is done using the SQL statement pattern directSQLStatement. To use a directSQLStatement stmt, one must first open it and then associate it with an SQL statement as in

stmt.open;
'SELECT title, length FROM Movie WHERE studioName = \'Disney\''->stmt;

or, equivalently

'SELECT title, length FROM Movie WHERE studioName = \'Disney\''->stmt.open;

Invoking stmt.execute will then cause the SQL statement to be executed at the database. A statement should be closed after use: stmt.close

2.4.4 Declaring Shared Variables

Definition of shared variables are done via the "declare..." methods. E.g. to declare a text studioName as a shared variable named "studioName" use

studioName: @text;
...
do 'studioName'->declareText
   (# set::(# do value->studioName #);
      get::(# do studioName[]->value[] #)
   #)

2.4.5 Using shared variables

The contents of a directSQLStatement can be any SQL statement with embedded shared variables and/or markers, i.e. a directSQLStatement stmt may be initialized as in

'SELECT title,length FROM Movie WHERE studioName = :studioName AND year = %i'->stmt;

Here "studioName" is the name of a shared variable declared as shown above. The value of the %i marker may be set using the i pattern in directSQLStatement. Now suppose that the following statements have been executed

'Disney'->studioName;
 1990->stmt.i;

When executing the SQL statement the SQL contents of the statement will then conceptually be

SELECT title,length FROM Movie WHERE studioName = 'Disney' AND year = 1990

i.e., before sending an SQL statement to a database the embedded shared variables and the markers are, conceptually, substituted for their current values. After execution the contents of the statement can be changed, the markers can be reset or the statement can be closed.

Also, embedded shared variables and markers can be named as in

'SELECT title,length FROM Movie WHERE studioName = :studioName AND year = theYear%i'->stmt;

In this case, the %i marker is named "theYear" and the statement may be used as

'Disney'->studioName; ('theYear',1990)->stmt.setIntegerByName

Please note that the use of shared variables is only supported in the ODBC interface. Suggested workaround for the other interfaces: build your SQL statements dynamically using append or putformat.

2.4.6 Results

Executing an SQL statement stmt will yield an instance of resultSet (in the following rs is a reference to a resultSet):

rs: ^connection.resultSet
...
do stmt.execute->rs[]

Given a resultSet, the scan method iterates over the tuples in the resultSet. There are three distinct ways to control the scan. First, one may simply execute

rs.scan(# do ... #)

In the do-part of the scan one may then refer to the values of the columns in the result. This is done sequentially by referring to the markers of the current result. If, e.g., rs was retrieved as shown above,

rs.scan(# do current.s -> putline; current.i->putint; newline #)

will scan over the results in the resultSet and print the values of their columns on the screen.

Second, one may enter a string when evaluating a scan pattern as in:

':title %i'->rs.scan(# do title->putline; current.i->putint; newline #)

Here title is a shared variable named "title". This statement prints the same as above but by providing an input string it is here specified that the first column of each result should be assigned to the shared variable "title" and that the second column of each result is an integer that will be fetched via the i marker. In general one may in this way specify how each column of a result should be treated.

The two ways of scanning shown above may, in some circumstances, be problematic in that they assume a specific ordering of columns in the results. Therefore, the third way of doing a scan names the columns in the resultSet, as in e.g.:

'length%i title:title'->rs.scan
   (# do title->putline; current.i->putint; newline #)

In this way the order of the columns in the result may be changed from "title, length" to "length, title" without any problems for the last way of scanning.

2.4.7 Specifics for the native Oracle interface

For obscure reasons the datatype boolean is not supported by Oracle. Furthermore, the use of time/date datatypes is discouraged, and therefore sparingly suppported by the Oracle interface. Oracle recommends storing dates as text.

A small example using Oracle:
The user scott with password tiger wants to connect to the default server (hence the empty text in the opening of the connection):

ORIGIN '~beta/betadbc/betadbc-oracle';
-- program: Descriptor --
(#
   oraCon: @Connection;
   stmt: @oraCon.DirectSQLStatement;

   do
   ('','scott','tiger')->oraCon.open;

   'create table Students("Name" varchar(100), "Age" integer);'->stmt.open;
   stmt.execute;
   stmt.close;
   'commit;'->stmt.open;
   stmt.execute;
   stmt.close;
   oraCon.close;
#)

2.4.8 Specifics for the native Frontbase interface

Selecting a host:
The Beta program must select the host on which the FrontBase server is running before opening a connection. Selecting the host is done by using selecthost

A small example using Frontbase:

ORIGIN '~beta/betadbc/betadbc-frontbase';
-- program: Descriptor --
(#
   frontbaseCon: @Connection;
   stmt: @frontbaseCon.DirectSQLStatement;

   do
   'brage.mjolner.dk' -> frontbaseCon.selecthost;
   ('firstdb','firstuser','')->frontbaseCon.open;

   'create table Students("Name" varchar(100), "Age" integer);'->stmt.open;
   stmt.execute;
   stmt.close;
   frontbaseCon.close;
#)

2.4.9 Specifics for the native MySQL interface

Selecting a database:
To utilize the MySQL interface in Beta you must select a database by using selectdb after opening a connection, as shown in this excerpt from the adhoc.bet demo:

ORIGIN '~beta/betadbc/betadbc-mysql';
INCLUDE '~beta/basiclib/formatio'
        '~beta/basiclib/numberio' '~beta/basiclib/file';
-- program: Descriptor --
(#
   sqlCon: @Connection; stmt: @sqlCon.DirectSQLStatement;
   res: ^sqlCon.resultSet
do
   'hi there'->putline;
   (2->arguments,3->arguments,4->arguments)->sqlCon.open;
   'firstdb'->sqlCon.selectdb;
...

adhoc is then invoked as follows:

./adhoc vertigo firstusr firstpswd

Where vertigo is the host on which the MySQL server is running. It is possible to use 'localhost'.

The BetaDBC Library - Reference Manual and Tutorial

[Modified: Friday September 7^th 2001 at 12:45]