WARNING: This server provides a static reference view of the NetKernel documentation. Links to dynamic content do not work. For the best experience we recommend you install NetKernel and view the documentation in the live system .

Endpoint
Name:sqlQuery
Description:The active:sqlQuery service retrieves information from a relational database (RDBMS) using SQL query processing.
Id:mod-db:sqlQuery
Category:accessor
Identifier Syntax

sqlQuery is an accessor using Active URI syntax with the following base identifiers:

Base
active:sqlQuery

and the following arguments: (for more details on argument passing details see here)

ArgumentRulesTypingDescription
operand
Mandatory
Representation (java.lang.Object)SQL query statement to be executed
configuration
Optional
Representation (java.lang.Object)Connection pool configuration
Request Verbs

The following verb is supported:

Verb
SOURCE
Response

The response representation of this accessor for SOURCE requests is unknown.

This accessor throws no documented exceptions.

Import Requirements

To use sqlQuery accessor you must import the module urn:org:netkernel:mod:db:

<import>
  <uri>urn:org:netkernel:mod:db</uri>
</import>

When a request with the SOURCE verb is issued to active:sqlQuery the SQL query statement specified in the required operand argument is used with the database connection pool specified in the optional configuration argument. (If the configuration argument is not supplied then the service issues a request for the resource res:/etc/ConfigRDBMS.xml in the current space.) By default, the response representation is returned as an HDS structure in which a resultset node contains a row node for each row returned by the query.

<root>
  <resultset>
    <row />
    <row />
  </resultset>
</root>

SQL allows you to provide a new name for each column using the "AS ..." construct. For example, SELECT firstname AS fname FROM users will result in the HDS structure using fname instead of firstname as the node name.

Each row node contains a node for each column. The column node name is the column name specified in the query and the column node value is a POJO with the returned value.

For example, for the table "Users":

FIRSTNAMELASTNAMEAGE
MichaelSmith42
SusanJamison38

the query

SELECT firstname, lastname, age FROM users

would result in the following HDS (note that the lower case identifiers used in the SQL statement are converted to upper case node names in HDS):

<root>
  <resultset>
    <row>
      <FIRSTNAME>Michael</FIRSTNAME>
      <LASTNAME>Smith</LASTNAME>
      <AGE>42</AGE>
    </row>
    <row>
      <FIRSTNAME>Susan</FIRSTNAME>
      <LASTNAME>Jamison</LASTNAME>
      <AGE>38</AGE>
    </row>
  </resultset>
</root>

If a query returns no rows then an empty HDS is returned:

<root>
  <resultset />
</root>

The argument operand is requested by the service as a DeterminateStringRepresentation which allows the supplied representation type to be any of the forms that can be transrepted to a DeterminateString.

Example

The following code implements the example query illustrated above.

request = context.createRequest("active:sqlQuery");
request.addArgumentByValue("operand", "SELECT firstname, lastname, age FROM users;");
hds = context.issueRequest(request);

ResultSet Representation

For large resultsets it may be impractical in terms of memory usage to return all the rows from as query at once within a single document. In this situation it possible to request that the sqlQuery return a ResultSetRepresentation:

request = context.createRequest("active:sqlQuery");
request.addArgumentByValue("operand", "SELECT firstname, lastname, age FROM users;");
request.setRepresentationClass(org.netkernel.rdbms.representation.ResultSetRepresentation);
resultSetRep = context.issueRequest(request);

java.sql.ResultSet rs = resultSetRep.getResultSet();
try
{   rs.next();
    while(!rs.isAfterLast())
    {   name = rs.getString("firstname");
        //process row
        rs.next();
    }
}
finally
{ resultSetRep.release();
}