Mimer SQL Documentation TOC PREV NEXT INDEX

Mimer SQL Developer Site


Examples


Querying the database

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 THE_IDENT.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 THE_IDENT.FIRST_TABLE where COL1 > ? ORDER BY COL1",
                             MIMER_FORWARD_ONLY,&statementhandle); // 2
   if (!err) {
     err = MimerSetInteger(statementhandle,1,42); // 3
     if (!err) {
       err = MimerOpenCursor(statementhandle); // 4
       if (!err) {
         do {
           err = MimerFetch(statementhandle); // 5
           if (!err) {
             MimerGetInteger(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 example shows an example of retrieving data from a result set. The following major events occur:

  1. The 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.
  2. The next thing is that we prepare the SELECT statement for execution. This call will load the statement into memory, along with its metadata.
  3. This SELECT statement has one input parameter, an integer. This parameter is supplied, in this case 42.
  4. A cursor is opened. We are now ready to receive data from the database.
  5. Immediately when the cursor is opened, it is located before the first row (aka on row 0 of the result set). To advance to the cursor position to the next row, we call MimerFetch.
  6. Once 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.
  7. When we have finished reading, the result set is closed.
  8. If 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.
  9. Finally 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 >= current_timestamp-
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) {
           MimerGetInteger(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;
 MimerBlob blobhandle;
 void *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) {
     MimerSetInteger(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:

  1. In some way we obtain the location and length of the data to store in the database.
  2. The process of storing the blob is started. The total size of the blob is supplied.
  3. In 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.
  4. The final call to MimerSetBlobData supplies the remaining 7110 bytes.
  5. The actual INSERT operation is performed.

Scrolling through a result set

See Querying the database for the database schema used in this example.

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 THE_IDENT.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) {
   L"select COL1,COL2 from THE_IDENT.FIRST_TABLE where COL1 > ? ORDER BY COL1",
                             MIMER_SCROLL,&statementhandle); // 2
   if (!err) {
     err = MimerSetInteger(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);
 }
 
  1. We scroll one row forward, into the first row of the result set. The current row is now 1.
  2. We 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.
  3. Now, we scroll to the tenth row. The current row is now 10.
  4. One row backwards. The current row is now 9.
  5. We 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.
  6. We wish to see the last row. The current row is now 10.
  7. Now, we scroll back to the first row. The current row is now 1.

Mimer
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
info@mimer.se
Mimer SQL Documentation TOC PREV NEXT INDEX