Mimer Information Technology

qunload

Purpose

To unload data from a table in Mimer to a file. WARNING. Tables containing BINARY LARGE OBJECT or CHARACTER LARGE OBJECT will not work with this utility.

Usage

Run this program from a command line.
qunload table file -ddatasource -uusername -ppassword \n
[-s] [-tdelimiter] [-q] [-#nnn]\n
table table to load from
file file to load data into
username well..
password well..
datasource ODBC datasource of table
file outputfile
b stringdelimiter. Default "
q convert special characters
s turn off progress printout
t def. delimiter = tab
# nnn=max rows to unload. (For test purposes)
Example
qunload tabbe d:\tabbe.txt -dmydsn -unisse -ppasswd -t\; -q

NULL string values
To differ between NULL strings and strings with length 0 you must use the -b option. NULL strings will then be empty and zero length string will be surrounded by the stringdelimiter. Qload will then correctly differ between NULL and zero length.

Conversion of special characters, option -q
Char. with dec code 0-31, 255 and the delimiter chosen will be converted to /nnn where nnn is the decimal code.
Example:
If ; is the delimiter and the contents of a table field = abc;123 will be converted to abc/059123 in the output file. The created output file can be loaded into a database with the qload program. If you have used the -q option in qunload you also have to use the -q option when you load the data into a table using qload.

qload

Purpose

To load data from a file to a table in Mimer. WARNING. Tables containing BINARY LARGE OBJECT or CHARACTER LARGE OBJECT will not work with this utility.

Usage

Run this program from a command line.
qload table file [descfile] -ddatasource -uusername -ppassword
[-oout_file] [-t[delimiter]|ooo] [-b[stringdel] [-nnrows] [-q] [-w] [-s] [-r] [-#rows]

-o outputfile, default terminal
-t delimiter, default tab.
-tooo means make the character with octal code ooo to delimiter
Example: -t174 means use the pipe character as delimiter.
-b stringdelimiter. Default "
-n nr of rows/arrayfetch. Default 100
-q convert spec. chars, /nnn where nnn=decimal code for the char.
-w supress printout of warnings
-s supress progress printout
-r remove all rows in the table before insert
-#nnn insert max nnn rows from the input file

Example
qload tabbe d:\tabbe.txt d:\tmp\tabbe.des -dmydsn -unisse -ppasswd -t; -q

See documentation of qunload about the -q option.

If you you don't have the -t option (=delimiter separated data) you must have a description file that describes where to find the fields in the input file. Notice that you can have a description file even with the -t option. See below.

Null values
An empty value is default treated as a null value. You can use a descriptionfile to define a value for empty values. With the -b option qload can differ between empty strings(,'',) and NULL strings (,,).

The description file
A line in the description file beginning with a * in the first position describes the input fileds. Other lines are treated as comments.
Syntax
*<colno>,<startpos>,<field length>[,n=<null value>][d=<dateformat>]
       [,c=<const. value>] [,s [=scale]]
colno: column no in the table.
startpos: start position of the field
field length: length of the field
If you use the -t option, startpos and field length willl be ignored.
c=const value: every column in the table will have this value.
n= null value: the column will get this value if the input field is empty.
The value can be an empty string for character values, which means that you will get a string value of length 0 instead of NULL.
d=date format: describe the input dateformat. Ex. MM/DD/YYYY if date is 04/05/1999. If year has format YY it will be converted to 20YY if YY is 00 to 09 else 19YY.
s=scale: scale is no of decimal places. If the number has no decimal point put in one.
Example of a descr. file
create table TABBE(C1 Char(100),I2 integer(5),
F3 decimal(5,1),d4 date,DEC5 DECIMAL(4,2))
*1,1,10
*2,11,5,n=-99
*3,16,5,c=999.9
*4,21,,d=DD/MM/YYYY
*5,,,s=2
If an input line contains
field1;99999;;04/03/1999;1234
after the load the columns in the database will contain
field1,99999,-99,1999-04-03,12.34
Notice that and will be ignored if -t option is givenbut you have to write at least *1,,
You have to write one line for each column even if you use the -t option.

desctab

Purpose

To create create table statements from a datasource's tables. WARNING. Tables containing BINARY LARGE OBJECT or CHARACTER LARGE OBJECT will not work with this utility.

Usage

Run this program from a command line.
Usage: desctab -ddatasource -dusername -dpassword
[-etabexcl] [-itabinc] [-ooutput_file]

-e: exclude all tables beginning with tabexcl
-i: include all tables beginning with tabinc
-e, -i: tabexc, tabinc case sensitive
Default: create statements for all tables in the datasource
Non standard SQL identifiers are converted to standard: blank,,, to _,a,a,o
In the output file you will get comments if there is no Mimer datatype exactly corresponding to the input data type. Therefore maybe you have to modify the output file before you run it in Mimer to create the tables.
Example
desctab -dmyaccdsn -iTabT -ocremimtab.sql
Make create statements for all myuser's tables beginning with TabT in datasource myaccdsn.
Output:
create table TabTypes(
flong INTEGER,
fshort SMALLINT,
freal REAL,
fdouble DOUBLE PRECISION,
byte INTEGER(1)-- datatype BYTE
,
ftext VARCHAR(50),
fDate TIMESTAMP(0) -- Access datatype DATETIME
,
pm VARCHAR(10000) -- datatype LONGTEXT
,
MyMoney DECIMAL(19,4) -- Access datatype CURRENCY ??
,
fguid INTEGER-- datatype GUID. Must be solved with SEQUENCE in Mimer
,
Primary key(flong)
);
Since this example is an Access database you will get some Access specific comments. Note that Access always answers timestamp to this ODBC program even if it is a date, time or datetime type in Access.

qcopy

Purpose

To copy data between tables in two different datasources.

Usage

Run this program from a command line.
Usage: qcopy fromtable [totable] -Fdsn,user,pwd -Tdsn,user,pwd [-s]
-s turn off progress printout
Program specific errornumbers
8 alloc env error, 9 alloc connect error, 10 connection error
11 alloc statement error, 12 prepare error, 13 numresult error
14 alloc statement error, 15 prepare error
16 bindparameter error.

Comments
The program uses arrayfetch. It exits after the first error.