Mimer API Examples
The Mimer SQL distribution contains a set of complete Mimer API based example programs; mimerapiex1.c, mimerapiex2.c and mimerapiex3.c.
The following table is used in this example:
create table THE_IDENT.FIRST_TABLE (
COL1 integer primary key,
COL2 varchar(20));
The example is about retrieving all rows in the table FIRST_TABLE where COL1 is larger than a supplied integer value. The rows are returned in COL1 order.
int err=0;
MimerSession sessionhandle;
MimerStatement statementhandle;
wchar_t res1[100];
int res2;
unsigned char res3[100];
err = MimerBeginSession(L"THE_DATABASE", L"THE_IDENT", L"THE_PASSWORD",
&sessionhandle); // 1
if (!err) {
err = MimerBeginStatement(sessionhandle,
L"select COL1, COL2 from FIRST_TABLE where COL1 > ? ORDER BY COL1",
MIMER_FORWARD_ONLY, &statementhandle); // 2
if (!err) {
err = MimerSetInt32(statementhandle, 1, 42); // 3
if (!err) {
err = MimerOpenCursor(statementhandle); // 4
if (!err) {
do {
err = MimerFetch(statementhandle); // 5
if (!err) {
MimerGetInt32(statementhandle, 1, &res2); // 6
MimerGetString(statementhandle, 2, res1,
sizeof(res1)/sizeof(res1[0]));
// You probably want to do something useful with the result here.
}
} while (!err);
MimerCloseCursor(statementhandle); // 7
}
}
MimerEndStatement(&statementhandle); // 8
}
MimerEndSession(&sessionhandle); // 9
}
This is an example of retrieving data from a result set. The following major events occur:
1The MimerBeginSession call will start a session with the database. When a session is started we always specify which database ident (a database namespace) is the default. Above we use the THE_IDENT ident, which in practice means that we can refer to all database objects in that schema without the qualifying THE_IDENT.
2The next thing is that we prepare the SELECT statement for execution. This call will load the statement into memory, along with its metadata.
3This SELECT statement has one input parameter, an integer. This parameter is supplied, in this case 42.
4A cursor is opened using MimerOpenCursor. We are now ready to receive data from the database.
5Immediately when the cursor is opened, it is located before the first row (aka on row 0 of the result set). To advance the cursor position to the next row, we call MimerFetch.
6Once the fetch succeeded, we may retrieve data from the row. MimerFetch returns a non-zero value if an error occurred or the end of the result set was reached.
7When we have finished reading, the result set is closed.
8If we wish to do so, we may execute the query again by calling the appropriate data input functions, MimerOpenCursor, MimerFetch etc. again. But in this example we are done and will close things down. MimerEndStatement is called to release the resources held by the statement.
9Finally the database session is ended.
Retrieving a binary large object
The following table is used in this example.
create table PICTURES (
ID int primary key default next value for id_sequence,
CREATED timestamp,
PICTURE blob);
In this example we have a table PICTURES with three columns; one primary key column, one column for creation timestamp and one column for a picture. We wish to return all pictures created within the last week, and with recent pictures first.
int err=0;
MimerSession sessionhandle;
MimerStatement statementhandle;
MimerLob blobhandle;
void *blobdata;
size_t bloblen;
int res2;
err = MimerBeginSession(L"THE_DATABASE", L"THE_IDENT", L"THE_PASSWORD",
&sessionhandle);
if (!err) {
err = MimerBeginStatement(sessionhandle,
L"select CREATED, PICTURE from PICTURES where CREATED >= localtimestamp -interval'7' days order by CREATED desc",
MIMER_FORWARD_ONLY, &statementhandle);
if (!err) {
err = MimerOpenCursor(statementhandle);
if (!err) {
while (!err) {
err = MimerFetch(statementhandle);
if (!err) {
MimerGetInt32(statementhandle, 1, &res2);
MimerGetLob(statementhandle, 2, &bloblen, &blobhandle);
blobdata = malloc(bloblen);
if (blobdata) {
err = MimerGetBlobData(&blobhandle, blobdata, bloblen);
if (!err) {
// You probably want to do something useful with the blob here
}
free(blobdata);
}
}
}
MimerCloseCursor(statementhandle);
}
MimerEndStatement(&statementhandle);
}
MimerEndSession(&sessionhandle);
}
Inserting a binary large object into the database
The following table is used in this example.
create table THIRD_TABLE (
COL1 integer primary key,
COL2 blob);
This example features binary large objects which are numbered. We have created a new object whose identity number is 42411 that we want to insert into the database.
int err=0;
MimerSession sessionhandle;
MimerStatement statementhandle;
MimerLob blobhandle;
char *blobdata;
int bloblen;
int param1 = 42411;
unsigned char res3[100];
// Below, the location of the binary large object data is obtained.
blobdata = _some_interesting_location_; // 1
bloblen = 47110;
err = MimerBeginSession(L"THE_DATABASE", L"THE_IDENT", L"THE_PASSWORD",
&sessionhandle);
if (!err) {
err = MimerBeginStatement(sessionhandle,
L"insert into THIRD_TABLE (COL1, COL2) values (?, ?)",
MIMER_FORWARD_ONLY, &statementhandle);
if (!err) {
MimerSetInt32(statementhandle, 1, param1);
err = MimerSetLob(statementhandle, 2, bloblen, &blobhandle); // 2
if (!err) {
err = MimerSetBlobData(&blobhandle, &blobdata[0], 10000); // 3
err = MimerSetBlobData(&blobhandle, &blobdata[10000], 10000);
err = MimerSetBlobData(&blobhandle, &blobdata[20000], 10000);
err = MimerSetBlobData(&blobhandle, &blobdata[30000], 10000);
err = MimerSetBlobData(&blobhandle, &blobdata[40000], 7110); // 4
err = MimerExecute(statementhandle); // 5
}
MimerEndStatement(&statementhandle);
}
MimerEndSession(&sessionhandle);
}
The following interesting things takes place in this example:
1In some way we obtain the location and length of the data to store in the database.
2The process of storing the blob is started. The total size of the blob is supplied.
3In this case the object data is supplied in chunks of 10 000 bytes. MimerSetBlobData is therefore called five times. Error handling is omitted here for clarity.
Choosing the chunk size is a compromise between memory consumption and performance. If it is important to reduce memory usage, it may be appropriate to process the object sequentially in smaller pieces, rather than to read everything into memory.
4The final call to MimerSetBlobData supplies the remaining 7110 bytes.
5The actual INSERT operation is performed.
Scrolling through a result set
The following table is used in this example:
create table THE_IDENT.FIRST_TABLE (
COL1 integer primary key,
COL2 varchar(20));
This example is basically the same as in Querying the database, except that we are performing some scrolling operations on the result set. The number of rows in the result set in this example is 10.
Retrieve all rows in the table FIRST_TABLE whose primary key (an integer) is larger than a supplied value, and scroll through the result set.
int err=0;
MimerSession sessionhandle;
MimerStatement statementhandle;
int current_row;
err = MimerBeginSession(L"THE_DATABASE", L"THE_IDENT", L"THE_PASSWORD",
&sessionhandle);
if (!err) {
err = MimerBeginStatement(sessionhandle,
L"select COL1, COL2 from FIRST_TABLE where COL1 > ? ORDER BY COL1",
MIMER_SCROLLABLE, &statementhandle); // 2
if (!err) {
err = MimerSetInt32(statementhandle, 1, 42);
if (!err) {
err = MimerOpenCursor(statementhandle);
if (!err) {
current_row = MimerCurrentRow(statementhandle); // current_row=0
do {
err = MimerFetchScroll(statementhandle, MIMER_NEXT, 0); // 1
current_row = MimerCurrentRow(statementhandle); // current_row=1
[...]
err = MimerFetchScroll(statementhandle, MIMER_RELATIVE, -3); // 2
current_row = MimerCurrentRow(statementhandle); // current_row=0
[...]
err = MimerFetchScroll(statementhandle, MIMER_ABSOLUTE, 10); // 3
current_row = MimerCurrentRow(statementhandle); // current_row=10
[...]
err = MimerFetchScroll(statementhandle, MIMER_PREVIOUS, 0); // 4
current_row = MimerCurrentRow(statementhandle); // current_row=9
[...]
err = MimerFetchScroll(statementhandle, MIMER_ABSOLUTE, 20); // 5
current_row = MimerCurrentRow(statementhandle); // current_row=11
[...]
err = MimerFetchScroll(statementhandle, MIMER_LAST, 0); // 6
current_row = MimerCurrentRow(statementhandle); // current_row=10
[...]
err = MimerFetchScroll(statementhandle, MIMER_FIRST, 0); // 7
current_row = MimerCurrentRow(statementhandle); // current_row=1
[...]
} while (!err);
MimerCloseCursor(statementhandle);
}
}
MimerEndStatement(&statementhandle);
}
MimerEndSession(&sessionhandle);
}
1We scroll one row forward, into the first row of the result set. The current row is now 1.
2We now scroll three rows backwards. The current row is now before the result set. Even though we scroll three rows backwards, we cannot get further back than the row before the result set. The fetch call will return MIMER_NO_DATA and the current row is 0.
3Now, we scroll to the tenth row. The current row is now 10.
4One row backwards. The current row is now 9.
5We try to scroll to the twentieth row. Since there are only 10 rows in the result set, the scroll operation will return MIMER_NO_DATA, and the current row is now 11.
6We wish to see the last row. The current row is now 10.
7Now, we scroll back to the first row. The current row is now 1.