Example 12-35: Inserting AuthorizationRequest with XPath and SQLJ

import java.sql.*;
import oracle.xml.parser.v2.XMLElement;
import org.w3c.dom.Document;
import sqlj.runtime.ref.DefaultContext;
import Examples;

public class CreditAuthorization {

  public static long newRequest(Document authDoc)
  throws Exception {

    // Connect to the Database
    DefaultContext.setDefaultContext(new DefaultContext(Examples.getConnection()));

    XMLElement req = (XMLElement)authDoc.getDocumentElement();

    // Get String values of important elements in XML Document
    // in preparation for Insert
    String cardNumber  = req.valueOf("CardNumber");
    String expiration  = req.valueOf("Expiration");
    String amount      = req.valueOf("Amount");
    String currency    = req.valueOf("Amount/@Currency");
    String merchantId  = req.valueOf("MerchantId");
    String requestDate = req.valueOf("Date");

    // Split up the MM/YYYY expiration value for insert
    String expMonth    = expiration.substring(0,2);
    String expYear     = expiration.substring(3);

    // Request Id assigned to this request during the insert
    String requestId;

    // Insert the information content into appropriate
    // database columns, using a Sequence for generating
    // a unique request Id.

    #sql{BEGIN
          INSERT INTO authorization_request ( request_id,
                                              request_date,
                                              card_number,
                                              exp_month,
                                              exp_year,
                                              amount,
                                              currency,
                                              merchant_id )
          VALUES(authorization_request_seq.nextval,
                 TO_DATE(:requestDate,'DD-MM-YYYY HH24:MI:SS'),
                 :cardNumber,
                 :expMonth,
                 :expYear,
                 :amount,
                 :currency,
                 :merchantId )
          RETURNING request_id INTO :OUT requestId;
          COMMIT;
        END;
        };
    return Long.parseLong(requestId);
  }
}