An ODBC Interface for the
Unicon Programming Language
Federico Balbi and Clinton L. Jeffery
Unicon Technical Report #1b
June 28, 2002
Abstract
The implementation of an ODBC interface for the Unicon language allows programmers to interface their applications to local and remote database management systems with a high level of interoperability and SQL language support.
Department of Computer Science
The University of Texas at San Antonio
San Antonio, TX 78255
Department of Computer Science
New Mexico State University
Las Cruces, NM 88003
This work sponsored in part by the National Library of Medicine.
1. Introduction
The Unicon ODBC interface consists of a data type and a set of new functions to enable Unicon programs to access database management systems (DBMS). The standard language to retrieve and manipulate data in a DBMS is the structured query language SQL.
1.1 Overview
ODBC is a programming interface (API) to access local and remote database management systems. ODBC is a de facto industry standard and works on many different operating systems and programming languages. It shields programmers from the complexity of different databases and the communications software used to access the data. ODBC defines an object called a "data source" that is referenced by name and maps its name to the exact location of data (network software, server name, database name and user information if needed).
1.2 ODBC Architecture
ODBC allows multiple applications to access to multiple data sources using an architecture (Figure 1) that consists of several layers:
Figure 1: ODBC Architecture
Applications call ODBC functions. The Driver Manager decides which driver is needed and loads it into memory. After this the Driver Manager routes ODBC calls to the driver.
The minimum capability required from a driver is to be able to connect to a server, send SQL statements and retrieve the results. What is important is that the Driver Manager hides all the details related to the server so the application does not need to know if the data is on a local file, a network file server or a remote host.
1.3 Client/Server Model
ODBC was designed to work with the client/server model (Figure 2) in order to satisfy the following requirements:
Figure 2: Client/Server Model
1.4 ODBC Installation
After deciding which DBMS an application is going to use it is necessary to install its related ODBC driver to let the application establish a connection with the DBMS.
For our ODBC tests we decided to use MySQL. MySQL is a free SQL server downloadable at www.mysql.org and available for most popular platforms. We tested both Linux and Sun/Solaris version. MySQL comes with MyODBC, the ODBC driver for the server, and it can be downloaded at the same web site. There are driver versions both for Unix and Windows.
MyODBC comes with a standard Windows setup.exe program. At one point during setup you have to physically click on MyODBC within a list-box to install successfully, but otherwise installation is uneventful. If installation is successful, when you are finished you will be able to see your MySQL data source(s) from the ODBC Data Sources control within the Windows Control Panel (Figure 3).
Figure 3: The Control Panel windows
Figure 4: The Driver Manager
This control comprises the main interface of the ODBC Driver Manager (Figure 4). The ODBC Driver Manager lists the ODBC drivers installed for different DBMSes. We can add new drivers, remove or configure existing ones within this control. The MyODBC setup program automatically adds and configures the driver in the Driver Manager's driver list.
After installing MyODBC we can open the Driver Manager and take a look at the different configuration parameters (Figure 5).
Figure 5: MyODBC configuration
The MyODBC configuration panel has the following fields:
All the other check-boxes are driver options. Of particular note are the following options:
1.5 Unicon ODBC Interface
The Unicon ODBC interface is a set of built-in functions that allow an easy way to write SQL database applications.
The function set can be divided in four main groups. A synopsis of these functions is given here. The reference section towards the end of this document describes them in detail.
2. An Example Phonebook Application
A typical database application performs the following tasks:
This section presents a simple Unicon phonebook application that takes advantage of the ODBC interface and work with MySQL server. The example will show how to use the main Unicon ODBC functions in order to connect, read and write data on a DBMS.
Our application will have the following menu:
Note that this application assumes a preexisting database server. a user account on that server, and a table on the server has been created to store the phone book information. Let’s create a phones table on our server with the following columns:
|
Column Name |
Type |
|
Name (KEY) |
VARCHAR(40) |
|
Phone |
VARCHAR(12) |
|
Address |
VARCHAR(60) |
We could create such a table within the application by appropriate Unicon ODBC calls, but perhaps it is more typical for such database administration tasks to be performed separately by a database administrator. From our server machine we invoke the mysql client program to talk to the SQL server:
[fbalbi@icon bin]$ ./mysql -ufbalbi -p mysql
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 96 to server version: 3.22.15-gamma
Type 'help' for help.
Now let's create the example table with the column Name as primary key:
mysql> create table phones (name varchar(40) primary key, phone varchar(12), address varchar(60));
Query OK, 0 rows affected (0.04 sec)
mysql> describe phones;
+---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | name | varchar(40) | | PRI | | | | phone | varchar(12) | YES | | NULL | | | address | varchar(60) | YES | | NULL | | +---------+-------------+------+-----+---------+-------+ 3 rows in set (0.01 sec)
Now the table is properly created and empty, in fact the following select commands returns an empty set:
mysql> select * from phones;
Empty set (0.00 sec)
Here the full list of our phonebook application. As an exercise, you may wish to consider how you would extend this application to include the above table-creation task as another menu option. Hint: the function sql() may come in handy.
# global variables
global db
global user, password
record person(name, phone, address) # database row
procedure main() # main program
write("*** Unicon ODBC phonebook ***\n\n")
login() # get user name and password
# connect to mysql data source and open table "phones"
db := open("mysql", "o", user, password)
if &errornumber~=0 then { # error during login
write(&errortext)
}
else {
getdbinfo() # print database information
repeat {
menu() # print menu options
option := read()
case option of {
"i": insertphone()
"d": deletephone()
"u": updatephone()
"l": listphones()
"q": break
default: write("*** wrong selection ***")
}
}
close(db) # close table and database connection
}
write("bye")
end
#
# user information
#
procedure login()
writes("user: ")
user := read()
writes("password: ")
password := read()
end
#
# get database name and version
#
procedure getdbinfo()
info := dbproduct(db)
write("\nDBMS: ", info["name"])
write("version: ", info["ver"])
end
#
# display menu options
#
procedure menu()
write("\nI)nsert")
write("D)elete")
write("U)pdate")
write("L)ist")
write("Q)uit\n")
end
#
# insert a new record
#
procedure insertphone()
writes("name: ")
name := read()
writes("phone: ")
ph := read()
writes("address: ")
addr := read()
sql(db, "INSERT INTO phones VALUES(" ||
name || "," || ph || "," || addr || ")")
if &errornumber~= 0 then
write("*** couldn't insert person ***")
end
#
# remove a record
#
procedure deletephone()
writes("name to remove: ")
name := read()
# delete row with specified name column
sql(db, "DELETE FROM phones WHERE name='"||name||"'")
end
#
# update a record
#
procedure updatephone()
writes("name to update: ")
name := read()
# select all columns of rows with specified name column
sql(db, "SELECT * FROM phones WHERE name='"||name||"'")
if row := fetch(db) then { # data found
writes("phone (",row["phone"],"): ")
row["phone"]:=read()
writes("address (",row["address"],"): ")
row["address"]:=read()
# update row on server
sql(db, "UPDATE phones SET " ||
"phone='" || row["phone"] || "'" ||
",address='" || row["address"] || "'" ||
" WHERE name='" || row["name"] || "'")
)
}
else write("\n\n*** person not found ***")
end
#
# list all people in the database
#
procedure listphones()
sql(db, "SELECT * FROM phones") # select all columns and all rows
while row := fetch(db) do { # while data found
# write row fields
every i:=(1 to *row) do writes("[",row[i],"]")
write()
}
end
3. Unicon ODBC Function Reference
With the exception of open() which returns a file
reference to an ODBC connection, all these functions generally take an
initial parameter (designated by f which must be an ODBC file
previously opened with open(...,"o"). Since it is used
consistently everywhere, this initial parameter is not given in the
detailed description of the rest of the function parameters.
Code Example
procedure main()
db:=open("mydb","o","federico","mypassword")
#
# ...program body...
#
close(db) # close table and disconnect
end
Parameters
Return Type: list of records with the following string fields:
Code Example
procedure main()
f := open("mysql","o","federico","") # open table
colinfo := dbcolumns(f) # get columns information
write("column info\n")
every i := 1 to *colinfo do { # for each column
writes("col #",i,": ")
every j := 1 to *colinfo[i] do # write column's info
writes("[",colinfo[i][j],"]")
write()
}
write()
close(f) # close table and connection to the database
end
Return Type: Record with the following string fields:
Code Example
procedure main()
f := open("mydb","o","fbalbi","") # open mytable
dinfo := dbdriver(f) # get driver information record
write("driver name : ", dinfo["name"])
write("driver version : ", dinfo["ver"])
write("driver ODBC ver : ", dinfo["odbcver"])
write("connections : ", dinfo["connections"])
write("statements : ", dinfo["statements"])
write("data source name: ", dinfo["dsn"])
close(db) # close database connection
end
Return Type: list of records with the following string fields:
Code Example
procedure main()
f := open("mydb","o","fbalbi","passwd") # open table
write(*f, " row(s) selected")
write("\ntable keys")
krec := dbkeys(f) # retrieve primary key information
every i := 1 to *krec do {
r := krec[i]
write("[", r["col"], "]") # print key name
}
close(f) # close table
end
Return Type: record with the following string fields:
Code Example
procedure main()
f := open("mydb","o","fbalbi","") # open mytable
dbl:=dblimits(f) # get DBMS limits information
# print out all DBMS limits
every i := 1 to *dbl do write(dbl[i])
close(f) # close table
end
Return Type: record with the following string fields:
Code Example
procedure main()
f := open("mydb","o","fbalbi","mypasswd") # open table
p := dbproduct(f) # get DBMS product information
write("product name: ", p["name"]) # print product name
write("product ver : ", p["ver"]) # print product version
close(f) # close table
end
Return Type: list of records with the following string fields:
Code Example
procedure main()
f := open("mysql","o","fbalbi","xxxxxxxx")
# get current database tables information
tablelist := dbtables(f)
# write number of tables
write("size list = ", *tablelist)
every i := 1 to *tablelist do { # for each table
r:=tablelist[i]
# print table information fields
every j := 1 to *r do writes("[",r[j],"]")
write()
}
close(f) # close table
end
Return Type: record with fields names equal to the selected table columns (use sql(db, "SELECT...") for column selection)
Code Example
procedure main()
f := open("mydb","o","fbalbi","mypass")
# select 3 existing columns from table mytable
sql(f, "SELECT id, name, amt FROM mytable")
# *f = number of selected rows
# may not work with some DBMS
write(*f, " row(s) selected")
write("\nrow values")
# fetch returns a record whose
# fieldnames are the column names selected with a SQL SELECT
# in this example we can reference fields using row["id"],
# row["name"] and row["amt"]
while row := fetch(f) do { # while rows to retrieve
every col := 1 to *row do # for each col of row
writes("[",row[col],"]") # write row field
write()
}
close(f) # close table
end
Parameters:
Code Example
procedure main()
# open "mytable" in mydb data source name defined in
# ODBC Data Sources (see Windows 9x Control Panel folder)
# using username "federico" and password "mypassword"
db := open("mydb","o","federico","password")
#
# ...program body...
#
close(db) # close table and disconnect
end
Parameters:
Code Example
procedure main()
# connect to DBMS and open table
db:=open("personnel_db","o","manager","passwd")
# prepare SQL query string to create an employees table
# of 5 columns
query := "CREATE TABLE employees (id INTEGER PRIMARY KEY,_
name VARCHAR(40), phone VARCHAR(12), DOB DATE,_
pay FLOAT)"
sql(db, query) # execute query
close(db) # close
end
4. Conclusions
This interface is a successful, albeit low-level interface to SQL databases. It has been tested and proven effective on large real world data sets. SQL commands are constructed as strings, and Unicon excels at such text manipulation. This interface does not provide Unicon programmers with higher level abstractions of database capabilities. For example one original design goal was to allow programmers to interact with a database with minimum knowledge of SQL. For example, the built-in table data type does not match the SQL table abstraction exactly, but with proper operator support interactions with SQL tables could look very similar to interactions with Unicon tables.
Appendix: Unicon ODBC Implementation Notes
The implementation of the ODBC interface includes changes to several files of the Unicon runtime system, as well as the addition of a new file for the new functions that were added.
New files
Modified files
ISQLFile type
In Unicon an ODBC connection to a database is similar to a file operation. Internally this is represented by the following C structure:
#ifdef ISQL /* ODBC support */
struct ISQLFile { /* SQL file */
SQLHDBC hdbc; /* connection handle */
SQLHSTMT hstmt; /* statement handle */
};
#endif
The field hdbc is used to keep the connection information associated to a particular ISQLFile file. hstmt is the statement structure that saves the results or dataset returned by an ODBC operation. The design of the interface is table oriented, which means that for each table we open a new connection.
In the future we will consider the possibility to associate a file to a database. This would let us open a connection for each database and share the same connection for each table within the same database. In this way we can open a file and use more than a table.
Actually when open(DSN,"o",user,password) is called Unicon allocates an ISQLFile object and initializes the structure fields in the following way:
References