Databasteknik: Some Frequently Asked Questions about the Mimer Labs

Is there a manual or an introduction or something?
Can I do the labs from home?
How should I create the tables in the database?
Can I look at the definitions of the tables I have created?
I used alter table to add a foreign key, but it didn't work!
How do I create tables with circular references?
How do I insert data into tables with circular references?
How do I remove tables that have circular references?
Can I create an auto-increment column in Mimer?
Dates and times
When I try to run the ODBC example programs, using Windows, a black window opens, but then it closes before I have time to read what it says.
The ODBC example programs don't work at all with Visual Studio 2005.
My ODBC program doesn't work!
I have this other problem...

Is there a manual or an introduction or something?

Start by reading Getting started with Mimer.

There are also more manuals than you ever wanted at Mimer's web site, among them the full documentation for Mimer SQL Engine 10.0 and a number of specific HOWTO documents.

On Windows, you can get the documentation in a standard Windows help window. Click on Start, then on All Programs, then on Mimer SQL Engine 10.0, then on Online Documentation, and choose Mimer SQL Engine Documentation from the menu.

Can I do the labs from home?

Yes. You can download Mimer and install it on your home computer. Then you can connect to our Mimer server, basen.oru.se.

You can also create your own local database, and work with that one. In that case, you don't need an Internet connection, except for the initial download.

How should I create the tables in the database?

It is possible to define the database incrementally, using lots of alter table commands to change the original schema. But it is probably much easier to write all create table and insert into commands in a file, and then run the entire file using Mimer Batch SQL. Then you can edit the file and re-submit it to Mimer when you wish to change the database. Just remember to put some drop table commands first in the file, to remove the old versions of the tables.

Can I look at the definitions of the tables I have created?

The describe command can be used to list the tables, and other database objects, that you have defined. Write
describe schema dbk00;
(assuming that you are logged in as the user dbk00, and that you have created the tables in this schema, which is the default).

Then you can use the describe command to examine the schema of a table. This command:

describe table Department;
will give this output:
   Table name       : DEPARTMENT
   Schema name      : DBK00
 
   Column name      : NUMBER
   Datatype         : INTEGER
 
   Column name      : NAME
   Datatype         : CHARACTER VARYING(10)
   Collation schema : INFORMATION_SCHEMA
   Collation name   : ISO8BIT
 
   Column name      : MANAGER
   Datatype         : INTEGER
 
   Constraint type  : PRIMARY KEY
   Constraint name  : SQL_PRIMARY_KEY_1085
   Defined on       : (NUMBER )
 
   Constraint type  : FOREIGN KEY
   Constraint name  : SQL_FOREIGN_KEY_1086
   Foreign schema   : DBK00
   Foreign table    : EMPLOYEE
   Delete rule      : NO ACTION
   Update rule      : NO ACTION
   Columns          : MANAGER                    >> NUMBER
As we can see, the table Department is part of the schema called DBK00, and it has the columns Number, Name and Manager. Number is defined as primary key, and Manager is defined as a foreign key that references the column Number in the table Employee.

The definition of every table is stored in the internal system tables of the database. For example, to find the columns of the table Employee, you can run this SQL query:

select column_name from information_schema.columns
where table_name = 'EMPLOYEE';
(String literals in SQL are case-sensitive, and table and column names are converted to capital letters internally in Mimer, so you have to write EMPLOYEE here even if you called the table Employee when you created it.)

As another example of meta-data, the view information_schema.table_constraints contains data about constraints:

select constraint_name
from information_schema.table_constraints
where table_name = 'EMPLOYEE'
and constraint_type = 'FOREIGN KEY';

I used alter table to add a foreign key, but it didn't work!

In SQL, you can change the definition of a table with the alter table command, to add or remove columns, and to add or remove primary key or foreign key constraints. However, some versions of Mimer SQL seems to have a bug that causes the command alter table add foreign key to fail when there is data in the table. It seems to work well with empty tables.

How do I create tables with circular references?

If two tables, or a cycle of several tables, refer to each other with foreign key constraints, you can't write all of the foreign key declarations in the create table statements. You have to add at least one of the foreign key constraints after creating the tables.

An example: Employees work at departments, and departments have employees as managers. Both tables have a reference attribute that refers to the other table.

The obvious solution doesn't work:

create table Employee
(number integer,
name varchar(10),
works_at integer,
primary key(number),
foreign key (works_at) references Department (number));

create table Department
(number integer,
name varchar(10),
manager integer,
primary key(number),
foreign key (manager) references Employee (number));

The first create table command fails with an error message:

MIMER/DB error -12200 in function EXECUTE
         Table DEPARTMENT not found,
         table does not exist or no access privilege
You can't create Employee first, since there is no table Department that it can refer to, and you can't create Department first, since there is no table Employee that it can refer to.

What you can do is to add one of the foreign key constraints afterwards:

create table Employee
(number integer,
name varchar(10),
works_at integer,
primary key(number));

create table Department
(number integer,
name varchar(10),
manager integer,
primary key(number),
foreign key (manager) references Employee (number));

alter table Employee
add foreign key (works_at) references Department (number);
To make it easier to remove the foreign key constraints, which is something that you might have to do, you can give the constraint a name:
alter table Employee                                                       
add constraint emp_works                                                   
foreign key (works_at) references Department (number);
Then you can use alter table to drop the constraint:
alter table Employee drop constraint emp_works;

How do I insert data into tables with circular references?

In the example above, where employees work at departments and departments have employees as managers, how do you insert data into the tables? You can't insert a new employee until she has a department to work for, and you can't insert a new department until there is an employee who can be manager for it. For example:
insert into Employee (number, name, works_at) values (1, 'Bob', 10);
MIMER/DB error -10105 in function EXECUTE
         INSERT or UPDATE operation invalid because the referencing
         table DBK00.EMPLOYEE does not satisfy a referential constraint
A possibility, if you haven't used not null constraints, is to first insert a row with a null value, and then, when there is something to refer to, to update the value:
insert into Employee (number, name, works_at) values (1, 'Anne', null);
insert into Employee (number, name, works_at) values (2, 'Bob', null);
insert into Department (number, name, manager) values (10, 'Security', 1);
update Employee set works_at = 10 where number = 1;
update Employee set works_at = 10 where number = 2;

How do I remove tables that have circular references?

If two tables, or a cycle of several tables, refer to each other with foreign key constraints, it can be difficult to remove the tables. None of them can simply be removed with the drop table command, since all of the tables have one or more tables that refer to it. For example, if Employee and Department refer to each other:
drop table Employee;
This gives an error message:
MIMER/DB error -12592 in function EXECUTE
         Dependencies exist, RESTRICT specified

This doesn't work either:

drop table Department;
It gives an error message:
MIMER/DB error -12592 in function EXECUTE
         Dependencies exist, RESTRICT specified
You can remove one of the reference attributes, to break the cycle, and then drop the tables:
alter table Employee drop works_at;
drop table Department;
drop table Employee;  
Another way is to drop not a column, but only the constraint on that column. If you gave the constraint a name when it was created, you can simply drop it with alter table:
alter table Employee drop constraint emp_works;
If you didn't name the constraint, or if you have forgotten its name, you can use the describe table command to find out which foreign key constraints have been declared for a table:
describe table Employee;
If you don't give names to the constraints, Mimer generates a name, for example SQL_FOREIGN_KEY_1079.

You can remove a constraint, for example a foreign key constraint, if you know its name:

alter table Employee drop constraint sql_foreign_key_1079;   

But the simplest way is to specify cascade, instead of the implicit restrict. The following command removes the table Department, along with anything else that needs to be removed first, such as foreign key constraints pointing to it from other tables:

drop table Department cascade;

Can I create an auto-increment column in Mimer?

Yes. You have to use a "sequence". A sequence is an entity that is used to automatically generate a sequence of integers. Example:
    create unique sequence MY_SEQUENCE;
    create table MY_TABLE (
      NUMBER integer default next_value of MY_SEQUENCE,
      NAME char(20)
    );
    insert into MY_TABLE (name) values ('Foo');
    insert into MY_TABLE (name) values ('Fum');
After those commands, MY_TABLE will contain these data:

MY_TABLE

NUMBER NAME
1 Foo
2 Fum

Read more: http://developer.mimer.com/howto/howto_31.htm

Dates and times

Mimer has the data types DATE, TIME and TIMESTAMP, TIMESTAMP is a time on a certain date, and is called DAYTIME in some other SQL dialects.

To write a date literal, for example in an insert statement, use the syntax DATE '1997-02-19'. Example (where the English word invoice means faktura in Swedish):

create table Invoice
(number integer,
date date,
amount integer,
primary key(number));

insert into Invoice (number, date, amount) values (17, DATE '2003-02-31', 14000);
Oops! This gives an error, since Mimer is smart enough to know that there is no February 31:
MIMER/DB error -12280 in function PREPARE
         Invalid datetime literal
Try again with a real date:
insert into Invoice (number, date, amount) values (17, DATE '2003-02-28', 14000);
Some other examples of date and time literals:

When I try to run the ODBC example programs, using Windows, a black window opens, but then it closes before I have time to read what it says.

Run the programs in a command window instead. Compile your program with Borland or Visual Studio as usual, but also open a command window. Click Start, then Programs, then Accessories, and choose Command prompt. Change directory to the directory where you have placed the files. Run the executable, which is called for example odbc-test-01.exe.

A command window

Tip: If you have long and complicated file names, such as the directory name in the window above, you can drag and drop icons onto the command window.

Another way is to add some code that waits for user input before the program finishes, as shown in odbc-test-10.c. Remember to wait for user input not only when the program exits after running successfully, but also when exiting after an error.

The ODBC example programs don't work at all with Visual Studio 2005.

Microsoft seems to have changed their implementation of ODBC to use wide character strings, using the wchar_t character type. Use those instead in all calls to ODBC functions, except (if I rememeber correctly) for sending and receiving the actual database data.

My ODBC program doesn't work!

Some hints:

I have this other problem...

If you can't find a solution to your problem here, in the course material, or in the documentation, ask the teaching assistant for your lab group, or send an e-mail to thomas.padron-mccarthy@oru.se.


Thomas Padron-McCarthy (thomas.padron-mccarthy@oru.se), November 2, 2012