The BetaDBC interface is built around the concept of a connection that models a connection to a relational DBMS. The BetaDBC interface to a connection is (see below for the full interface):
Connection: (*) (# <<SLOT ConnectionLib:Attributes>>; declareVar: (*) ...; declareInteger: (*) declareVar ...; declareReal: (*) declareVar ...; declareText: (*) declareVar ...; declareBoolean: (*) declareVar ...; declareTime: (*) declareVar ...; declareDate: (*) declareTime ...; declareClock: (*) declareTime ...; formatTime:< (*) ...; formatDate:< (*) ...; formatClock:< (*) ...; SQLStatement: (*) ...; directSQLStatement: (*) SQLStatement ...; preparedSQLStatement: (*) directSQLStatement ...; resultSet: (*) ...; open:< (*) ...; close:< ...; connectionException:< BetaDBCException ...; connectionNotification:< BetaDBCNotification ...; private: @<<SLOT ConnectionPrivate:Descriptor>> #)
In order to use a connection a data source must be created (See the tutorial for specifics.). To communicate with an existing data source the user must first create an instance of the connection pattern. Calling the open method on a connection:
open:< (*) (# name: ^text; userName: ^text; password: ^text; openConnectionException:< BetaDBCException (# do INNER #); openConnectionNotification:< BetaDBCNotification (# do INNER #) enter (name[],userName[],password[]) <<SLOT ConnectionOpen:DoPart>> #);
then makes it possible to communicate with the data source. When calling open the name of the data source must be supplied whereas user name and/or password may be omitted as appropriate. Consider as an example the statement
('ullman97','marius',none)->sqlCon.open
The statement opens a connection to the data source name "ullman97" for the user "marius" without specifying a password.
Definition of shared variables are done via the "declare..." methods. To e.g. declare a text studioName as a shared variable named "studioName" use
studioName: @text; ... do 'studioName'->declareText (# set::(# do value->studioName #); get::(# do studioName[]->value[] #) #)
Data manipulation and definition is done using the SQL statement patterns. The SQL statement patterns have the following interfaces:
SQLStatement:(*) (# <<SLOT SQLStatementLib:Attributes>>; cursorType:<(*) ...; resultSetType:<(*) ...; execute:<(*) ...; open:(*) ...; close:(*) ...; SQLStatementException:< BetaDBCException ...; SQLStatementNotification:< BetaDBCNotification ...; get:< ...; set:< ...; private: @<<SLOT SQLStatementPrivate:Descriptor>>; enter set do INNER exit get #); directSQLStatement:(*) SQLStatement (# <<SLOT DirectSQLStatementLib:Attributes>>; currentMarker:(*) @ ...; marker:(*) ...; b: (*) marker ...; c: (*) marker ...; d: (*) marker ...; f: (*) marker ...; i: (*) marker ...; s: (*) marker ...; t: (*) marker ...; setByName: (*) ...; setBooleanByName: (*) ...; setClockByName: (*) ...; setDateByName: (*) ...; setFloatByName: (*) ...; setIntegerByName: (*) ...; setTextByName: (*) ...; setTimeByName: (*) ...; execute:: ...; execDirectException:< BetaDBCException ...; execDirectNotification:< BetaDBCNotification ...; set::< ...; getExpanded:(*) ...; private: @<c;<SLOT DirectSQLStatementPrivate:Descriptor>>; do INNER #); preparedSQLStatement:(*) (# ... #)
To use a statement stmt, one must first open it and then associate it with an SQL statement as in
Invoking execute on stmt will then cause the SQL statement to be executed at the database. A statement should be closed after use.
A preparedSQLStatement differs from a directSQLStatement in that a prepared statement is parsed and prepared by the data source when the statement is initialised, i.e. executing
the statement above will, if stmt is a preparedSQLStatement, cause the contents of the SQL statement to be sent to the database in order for it to be parsed and prepared for future execution. If stmt is a directSQLStatement no communication with the database will occur before calling execute.
In this way a prepared SQL statement is a little slower to initialise than a direct SQL statement but much faster to execute. Use a preparedSQLStatement only when an SQL statement has to be executed several times [1].
The contents of a directSQLStatement can be any SQL statement with embedded shared variables and/or markers, i.e. a directSQLStatement stmt may be initialised as in
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
When executing the SQL statement the SQL contents of the statement will then conceptually be
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 inIn this case, the %i marker is named "theYear" and the statement may be used as
'Disney'->studioName; ('theYear',1990)->stmt.setIntegerByName
A preparedSQLStatement is used similarly to a directSQLStatement.
Executing an SQL statement stmt will yield an instance of resultSet (here rs is a reference to a resultSet):
A resultSet implements an interface to the result of an SQL query in the following way
resultSet:(*) (# <<SLOT ResultSetLib:Attributes>>; columnCount:(*) integerValue ...; rowCount:(*) integerValue ...; column: (*)...; getColumn: (*)...; getColumnByName:(*) ...; cursorName:(*) ...; tuple:(*) (# <<SLOT ResultLib:Attributes>>; marker:(*) ...; b: marker ...; c: marker ...; d: marker ...; f: marker ...; i: marker ...; s: marker ...; t: marker ...; private: @... #); EOT: (*) ...; set:(*) (# varNotDeclared:<(*) exception ...; columnNotFound:<(*) exception ...; pattern: ^text #); fetch:(*) ...; scan:(*) (# current: ^tuple; varNotDeclared:<(*) exception ...; columnNotFound:<(*) exception ...; pattern: ^text enter pattern[] ... #); resultSetException:< BetaDBCException ...; resultSetNotification:< BetaDBCNotification ...; private: @... #)
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
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,
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:
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.:
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.
Results can also be fetched one at a time by using "fetch" instead of "scan". This is, e.g., useful for interleaving the processing of two result sets. For each result set there is a pointer pointing to a current result. If rs is is a resultSet and current is a result,will fetch the current result and advance the pointer. "current" may be used as shown above.
| The BetaDBC Library - Reference Manual and Tutorial | © 1999-2002 Mjølner Informatics |
[Modified: Monday October 23rd 2000 at 22:18]
|