3 BetaDBC Interface Tutorial

3.1 Introduction

The sample programs shown in this tutorial may be found in the tutorial directory accompanying BetaDBC. The examples use a database schema and examples from [Ullman 97]. It supposes that the reader is familiar with basic SQL and focuses on teaching the essentials of using BetaDBC. All examples take (up to) three command line arguments: a data source, a user name, and a password. If the specific interface used permits, the data source, user name and/or password may be omitted.

Although this tutorial is based on the ODBC interface, it should be quite useful for all BetaDBC users.

In the following it is assumed that a suitable data source named "mysource" has been created.

3.2 Creating a Database

Check this section with the new setup

The next step will then be to create and insert values into a database. Let's use the following sample database schema

Movie (title, year, length, inColor, studioName, producerCNo) StarsIn (movieTitle, movieYear, starName) MovieStar (name, address, gender, birthdate) MovieExec (name, address, certNo, netWorth) Studio (name, address, presCNo)

A series of SQL statements creating the database schema may be found in createmoviedbtables.txt. The text files used in this section are shown in the section "Text Files".

Create the tables corresponding to this schema by running the executesqlfile program also found in the tutorials directory:

.../tutorial> ./executesqlfile createmovie.txt mysource marius

How the executesqlfile program is implemented will be discussed later. You may now insert some values in the database by running

.../tutorial> ./executesqlfile moviedbtables.txt mysource marius

The tables may later be deleted by running

.../tutorial> ./executesqlfile dropmovies.txt mysource marius

3.3 Querying and Retrieving from the Database

This section will introduce the basics of BetaDBC: connecting to data sources, executing simple queries and retrieving the results.

Consider the simple SQL statement

SELECT * FROM Movie WHERE studioName = 'Disney' AND year = 1990;

An application "simple"that uses the BetaDBC-ODBC interface, executes the above query and retrieves the result may look like:

ORIGIN '~beta/betadbc/betadbc-odbc'; -- program: Descriptor -- (# sqlCon: @connection; stmt: @sqlCon.directSqlStatement; rs: ^sqlCon.resultSet do (2->arguments,3->arguments,4->arguments)->sqlCon.open; 'SELECT title, length FROM Movie WHERE studioName = \'Disney\' AND year = 1990' ->stmt.open; stmt.execute->rs[]; rs.scan (# do 'title: '->puttext; current.s->putline; 'length: '->puttext; current.i->putint; newline; #); stmt.close; sqlCon.close; #)

The program starts out by declaring a connection, a directSQLStatement belonging to that connection and a resultSet belonging to that connection. The connection is used in order to connect to a data source in the first line of the program:

(2->arguments,3->arguments,4->arguments)->sqlCon.open;

Connection's open method takes as arguments a name of the connection, a username and a password. Thus an invocation of the program like

.../tutorial> ./simple mysource marius foobar

means that the first statement will be an attempt to connect the user "marius" with password "foobar" to the data source named "mysource". If this succeeds

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

will open the directSQLStatement "stmt" and set its content to the query we want to execute. Executing the query yields a resultSet holding a cursor for the result

stmt.execute->rs[];

The resultSet may then be scanned. During the scan 'current' will hold a reference to a tuple in the resultSet. The values of this result may then be accessed consecutively by using the marker attributes

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

Finally, in order to free resources, the directSQLStatement and the connection are closed.

3.4 Executesqlfile

The simple scheme presented in the last section can now be used for implementing the executesqlfile program. The executeLoop shows how to reuse an SQLStatement by simply replacing it's textual contents

executeTxt[]->sqlCon.stmt

3.5 Embedded SQL - Using Shared Variables

Using shared variables makes it possible to use the values of BETA objects in place of a concrete value in SQL statements. Since no preprocessor is used by BetaDBC, it is necessary to declare shared variables imperatively, as in

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

Here a shared text variable named "studioName" is declared. The set pattern is final bound to describe how the shared variable's value is to be set. get is final bound to describe how the value of the shared variable is to be fetched.

Then, using embedded SQL syntax, one may use shared variables in SQL statements:

stmt:@sqlCon.directSQLStatement; do ...; 'INSERT INTO Studio(name, address) VALUES (:studioName, :studioAddr)' ->stmt.open; ...

This means that when executing stmt, ":studioName" and ":studioAddr" will (conceptually) be replaced by the values of the BETA text variables "studioName" and "studioAddr", and the resulting SQL statement will then be executed.

Using BetaDBC it is possible to declare most commonly used objects as shared variables (i.e., boolean, integer, real, text, date and time). The figure below shows a full program that will execute the statement above. "stmt.getExpanded" returns in a text how the SQL statement would look if it was executed at that point.

ORIGIN '~beta/betadbc/betadbc-odbc'; -- program: Descriptor -- (# sqlCon: @connection; stmt: @sqlCon.directSqlStatement; studioName,studioAddr: @text do (2->arguments,3->arguments,4->arguments)->sqlCon.open; 'studioName' ->sqlCon.declareText (# set:: (# do value->studioName #); get:: (# do studioName[]->value[] #) #); 'studioaddr' ->sqlCon.declareText (# set:: (# do value->studioAddr #); get:: (# do studioAddr[]->value[] #) #); 'Input a studio name: '->puttext; getLine->studioName.puttext; ' and address: '->puttext; getLine->studioAddr.puttext; 'INSERT INTO Studio(name, address) VALUES (:studioName, :studioAddr)' ->stmt.open; stmt.getExpanded->putline; stmt.close; sqlCon.close #)

3.6 Embedded SQL - Fetching Results

Suppose that we are executing a statement that return a result. Embedded SQL can then also be used to fetch results directly into shared variables. In BetaDBC this is done through the use of the scan pattern. Suppose we are executing

'SELECT MovieExec.name, netWorth FROM Studio, MovieExec WHERE presCNo = certNo AND Studio.name = :studioName'

Then,

':presName :presNetWorth'->(stmt.execute).scan(# ... #)

will cause the first column in each result tuple to be assigned to the shared integer variable "presName", and the second column to "presNetWorth". The full code is shown below:

ORIGIN '~beta/betadbc/betadbc-odbc'; INCLUDE '~beta/basiclib/formatio'; -- program: Descriptor -- (# sqlCon: @connection; stmt: @sqlCon.directSqlStatement; studioName,presName: @text; presNetWorth: @integer do 'studioName' ->sqlCon.declareText (# set:: (# do value->studioName #); get:: (# do studioName[]->value[] #) #); 'presName' ->sqlCon.declareText (# set:: (# do value->presName #); get:: (# do presName[]->value[] #) #); 'presNetWorth' ->sqlCon.declareInteger (# set:: (# do value->presNetWorth #); get:: (# do presNetWorth->value #) #); (2->arguments,3->arguments,4->arguments)->sqlCon.open; 'Input a studio name: '->puttext; getLine->studioName.puttext; 'SELECT MovieExec.name, netWorth FROM Studio, MovieExec WHERE presCNo = certNo AND Studio.name = :studioName' ->stmt.open; stmt.getExpanded->putline; ':presName :presNetWorth' ->(stmt.execute).scan (# do 'The net worth of the president %s \nof %s is %i $\n' ->putFormat (# do presName[]->s; studioName[]->s; presNetWorth->i #) #); stmt.close; sqlCon.close #)

3.7 An Ad-hoc Query Evaluator

We now have most of the building blocks to create an ad-hoc query evaluator, i.e., a program that connects to a data source and in a loop prompts for SQL statements that are to be executed on this data source. The following implements such a program.

As long as the user inputs anything but an empty line this input is sent to the data source as an SQL statement:

getline->stmt; (if (stmt).empty then leave L if); stmt.execute->res[];

If successful, the result is examined. First the column information of the resultSet is extracted:

(for j: res.columnCount repeat '%s: %s\t'->putFormat (# do (j->res.getColumn).name[]->s; (j->res.getColumn).dataTypeName[]->s #) for);

Each resultSet has a columnCount yielding the number of columns in the resultSet. For each column, information such as name and dataTypeName (a DBMS specific datatype name) may be retrieved.

If the columnCount is non-zero, the results are fetched:

(if res.columnCount > 0 then res.scan (# do (for i: res.columnCount repeat (if (i->res.getColumn).DataType## // text## then current.s->puttext // integerObject## then current.i->putint // realObject## then current.f->putreal // booleanObject## then (if current.b then 'true'->puttext; else 'false'->puttext if) // time## then current.t->puttime else 'Unknown data type!!!'->puttext if); '\t'->puttext for); newline #) else 'DML/DDL statement executed successfully!'->putLine if)

Again the information about columns in the resultSet is used. By evaluating

(i->res.getColumn).DataType##

the BETA pattern corresponding to the SQL datatype in column i is found.