There are also more manuals than you ever wanted at Mimer's developer web site, among them the full documentation for Mimer SQL 11.0 and a number of specific HOWTO articles. Among other things, you can look up the exact syntax for each SQL command.
On Windows, you can get the documentation in a standard Windows help window. Click on the Windows symbol to open up the start menu, scroll down to Mimer SQL 11.0.5A, and choose on Mimer SQL Documentation.
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.
(assuming that you are logged in as the user dbtek0, and that you have created the tables in this schema, which is the default).describe schema dbtek0;
Then you can use the describe command to examine the schema of a table. This command:
will give this output:describe table Department;
As we can see, the table Department is part of the schema called DBTEK0, 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.Table name : DEPARTMENT Schema name : DBTEK0 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 : DBTEK0 Foreign table : EMPLOYEE Delete rule : NO ACTION Update rule : NO ACTION Columns : MANAGER >> NUMBER
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:
(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.)select column_name from information_schema.columns where table_name = 'EMPLOYEE';
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';
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:
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: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);
Then you can use alter table to drop the constraint:alter table Employee add constraint emp_works foreign key (works_at) references Department (number);
alter table Employee drop constraint emp_works;
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 DBTEK0.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;
This gives an error message:drop table Employee;
MIMER/DB error -12592 in function EXECUTE
Dependencies exist, RESTRICT specified
This doesn't work either:
It gives an error message:drop table Department;
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:
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 works_at; drop table Department; drop table Employee;
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:alter table Employee drop constraint emp_works;
If you don't give names to the constraints, Mimer generates a name, for example SQL_FOREIGN_KEY_1079.describe table Employee;
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;
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
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):
Oops! This gives an error, since Mimer is smart enough to know that there is no February 31: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);
MIMER/DB error -12280 in function PREPARE
Invalid datetime literal
Try again with a real date:
Some other examples of date and time literals:insert into Invoice (number, date, amount) values (17, DATE '2003-02-28', 14000);
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.