sqlProc

Home > Books > NetKernel API and Services Reference > Accessor Listing > RDBMS Accessors and Utilities > sqlProc

Module

The sqlProc accessor is exported by the urn:org:ten60:netkernel:mod:db module. Import this module to gain access to the accessor.

Syntax

URI

active:sqlProc

Argument	Rules	Description
operand	Mandatory	the <sql> call statement
configuration	Optional	optional configuration document

Example Usage

DPML

<instr>
  <type>sqlProc</type>
  <operand>
    <sql>{ call MyProc( 84744, "avon" ) }</sql>
  </operand>
  <configuration>/myconfiguration/configRDBMS.xml</configuration>
  <target>var:resultsetsDocument</target>
</instr>

NetKernel Foundation API

import org.ten60.netkernel.layer1.representation.StringAspect;
import org.ten60.netkernel.xml.representation.IAspectXDA;
main()
{   id=84744;
    location="avon";
    sql= "<sql>{call MyProc( "+id+",\""location"+"\") }</sql>";
    req=context.createSubRequest("active:sqlProc");
    req.addArgument("operand", new StringAspect( query ));
    req.setAspectClass(IAspectXDA.class)
    xda = context.issueSubRequestForAspect(req).getXDA();
}

Purpose

The sqlProc accessor executes a stored procedure on a database using a JDBC connection. The query is provided in the operand as a canonical <sql> document (see example above). Parameters should be pre-substituted into the sql document. Several technologies such as STM and XSLT are useful for this purpose. The result set is returned as unparsed XML of the form:

<resultsets>
  <result>
    <row>
      <columnName1>value</columnName1>
      <columnName2>value</columnName2>
    </row>
  </result>
  <result>
    <row>
      <columnName1>value</columnName1>
      <columnName2>value</columnName2>
    </row>
  </result>
</resultsets>

Like the underlying JDBC interface, sqlQuery cannot be used to update a document, use sqlUpdate or sqlBatch.

Configuration

By default the configuration will be read from ffcpl:/etc/ConfigRDBMS.xml. The document provides a driver and connection URI (see example below). Alternatively,the configuration can be provided in an optional <configuration> argument - this may be either a URI to a config document or a literal configuration document.

<config>
  <rdbms>
    <jdbcDriver>com.mysql.jdbc.Driver</jdbcDriver>
    <jdbcConnection>
      jdbc:mysql://localhost/mydatabase?user=myusername&amp;password=mypassword
    </jdbcConnection>
  </rdbms>
</config>

For more details on the configuration see the Mod DB Guide