Transaction integrity, message communication, and resource access are the major requirements of an Online-Transaction-Processing (OLTP) application.

This section provides a code sample that illustrates the ATMI transaction, buffer management, and communication routines operating together with SQL statements that access a resource manager. The example is borrowed from the ACCT server that is part of the Oracle Tuxedo banking application (bankapp) and illustrates the CLOSE_ACCT service.

The example shows how the set transaction statement (line 49) is used to set the consistency level and access mode of the transaction before the first SQL statement that accesses the database. (When read/write access is specified, the consistency level defaults to high consistency.) The SQL query determines the amount to be withdrawn in order to close the account based on the value of the ACCOUNT_ID (lines 50-58).

tpalloc() allocates a buffer for the request message to the WITHDRAWAL service, and the ACCOUNT_ID and the amount to be withdrawn are placed in the buffer (lines 62-74). Next, a request is sent to the WITHDRAWAL service via a tpcall() call (line 79). An SQL delete statement then updates the database by removing the account in question (line 86).

If all is successful, the buffer allocated in the service is freed (line 98) and the TPSVCINFO data buffer that was sent to the service is updated to indicate the successful completion of the transaction (line 99). Then, if the service was the initiator, the transaction is automatically committed. tpreturn() returns TPSUCCESS, along with the updated buffer, to the client process that requested the closing of the account. Finally, the successful completion of the requested service is reported on the status line of the form.

After each function call, success or failure is determined. If a failure occurs, the buffer allocated in the service is freed, any transaction begun in the service is aborted, and the TPSVCINFO buffer is updated to show the cause of failure (lines 80-83). Finally, tpreturn() returns TPFAIL and the message in the updated buffer is reported on the status line of the form.

Listing ACCT Server

001    #include <stdio.h>                         /* UNIX */
002    #include <string.h>                        /* UNIX */
003    #include <fml.h>                           /* ORACLE Tuxedo System */
004    #include <atmi.h>                          /* ORACLE Tuxedo System */
005    #include <Usysflds.h>                      /* ORACLE Tuxedo System */
006    #include <sqlcode.h>                       /* ORACLE Tuxedo System */
007    #include <userlog.h>                       /* ORACLE Tuxedo System */
008    #include "bank.h"                          /* BANKING #defines */
009    #include "bank.flds.h"                     /* bankdb fields */
010    #include "event.flds.h"                    /* event fields */
011
012
013    EXEC SQL begin declare section;
014    static long account_id;                    /* account id */
015    static long branch_id;                     /* branch id */
016    static float bal, tlr_bal;                 /* BALANCE */
017    static char acct_type;                     /* account type*/
018    static char last_name[20], first_name[20]; /* last name, first name */
019    static char mid_init;                      /* middle initial */
020    static char address[60];                   /* address */
021    static char phone[14];                     /* telephone */
022    static long last_acct;                     /* last account branch gave */
023    EXEC SQL end declare section;

024    static FBFR *reqfb;              /* fielded buffer for request message */
025    static long reqlen;              /* length of request buffer */
026    static char amts[BALSTR];        /* string representation of float */

027    code for OPEN_ACCT service

028    /*
029     * Service to close an account
030     */
031    void
032    #ifdef __STDC__
033    LOSE_ACCT(TPSVCINFO *transb)

034    #else

035    CLOSE_ACCT(transb)
036    TPSVCINFO *transb;
037    #endif

038 {
039    FBFR *transf;                 /* fielded buffer of decoded message */

040    /* set pointer to TPSVCINFO data buffer */
041       transf = (FBFR *)transb->data;

042    /* must have valid account number */
043    if (((account_id = Fvall(transf, ACCOUNT_ID, 0)) < MINACCT) ||
044     (account_id > MAXACCT)) {
045      (void)Fchg(transf, STATLIN, 0, "Invalid account number", (FLDLEN)0);
046       tpreturn(TPFAIL, 0, transb->data, 0L, 0);
047 }

048    /* Set transaction level */
049    EXEC SQL set transaction read write;

050    /* Retrieve AMOUNT to be deleted */
051    EXEC SQL declare ccur cursor for
052     select BALANCE from ACCOUNT where ACCOUNT_ID = :account_id;
053    EXEC SQL open ccur;
054    EXEC SQL fetch ccur into :bal;
055    if (SQLCODE != SQL_OK) {                 /* nothing found */
056     (void)Fchg(transf, STATLIN, 0, getstr("account",SQLCODE), (FLDLEN)0);
057     EXEC SQL close ccur;
058     tpreturn(TPFAIL, 0, transb->data, 0L, 0);
059   }

060    /* Do final withdrawal */

061    /* make withdraw request buffer */
062   if ((reqfb = (FBFR *)tpalloc("FML",NULL,transb->len)) == (FBFR *)NULL) {
063      (void)userlog("tpalloc failed in close_acct\n");
064      (void)Fchg(transf, STATLIN, 0,
065           "Unable to allocate request buffer", (FLDLEN)0);
066       tpreturn(TPFAIL, 0, transb->data, 0L, 0);
067       }
068       reqlen = Fsizeof(reqfb);
069      (void)Finit(reqfb,reqlen);
070    /* put ID in request buffer */
071    (void)Fchg(reqfb,ACCOUNT_ID,0,(char *)&account_id, (FLDLEN)0);

072    /* put amount into request buffer */
073    (void)sprintf(amts,"%.2f",bal);
074    (void)Fchg(reqfb,SAMOUNT,0,amts, (FLDLEN)0);

075    /* increase the priority of this withdraw */
076    if (tpsprio(PRIORITY, 0L) == -1)
077      (void)userlog("Unable to increase priority of withdraw");

078    /* tpcall to withdraw service to remove remaining balance */
079    if (tpcall("WITHDRAWAL", (char *)reqfb, 0L, (char **)&reqfb,
080       (long *)&reqlen,TPSIGRSTRT) == -1) {
081       (void)Fchg(transf, STATLIN, 0,"Cannot make withdrawal", (FLDLEN)0);
082       tpfree((char *)reqfb);
083       tpreturn(TPFAIL, 0,transb->data, 0L, 0);
084       }

085       /* Delete account record */

086       EXEC SQL delete from ACCOUNT where current of ccur;
087       if (SQLCODE != SQL_OK) {               /* Failure to delete */
088       (void)Fchg(transf, STATLIN, 0,"Cannot close account", (FLDLEN)0);
089        EXEC SQL close ccur;
090        tpfree((char *)reqfb);
091        tpreturn(TPFAIL, 0, transb->data, 0L, 0);
092    }
093    EXEC SQL close ccur;

094    /* prepare buffer for successful return */
095    (void)Fchg(transf, SBALANCE, 0, Fvals(reqfb,SAMOUNT,0), (FLDLEN)0);
096    (void)Fchg(transf, FORMNAM, 0, "CCLOSE", (FLDLEN)0);
097    (void)Fchg(transf, STATLIN, 0, " ", (FLDLEN)0);
098    tpfree((char *)reqfb);
099    tpreturn(TPSUCCESS, 0, transb->data, 0L, 0);
100 }