Database Access

Requires Platform 4.5+

Accessing SQL Data Sources

Nexaweb 4.5 provides an SQL (Java Database Connectivity (JDBC)) data service that allows you to connect your application to a database and to:

Populate and modify data in the client UI from the database
Populate and modify data in the database from the client UI

In order to use the SQL data service, you need to:

Add the data service plug-in to the client application
Create connections in the nexaweb-sql.xml file, on the server, to:
- Specific external databases
- A connection pool
Define SQL statements:
- In the nexaweb-sql.xml, file to reference in dataRequestDefinitions
- Directly in dataRequestDefinitions in the nexaweb-data.xml file
- In embedded serviceRequests in the client UI file

Configuring the SQL Data Service

The SQL data service configuration file, nexaweb-sql.xml, contains the following sections:

Configuration Section	Description
connectionDefinitions	Defines connections over which client applications can execute sql data service requests to external data.
dataSourceConnectionDefinition	Defines a J2EE DataSource from which clients can obtain connections. A J2EE DataSource allows applications to leverage native driver connection pooling available through the JDBC optional javax.sql package.
statementDefinitions	Lists sql statement types defined on the server that your application can use by reference to access external data. Note: You can also specify sql statement types in the client UI within an embeddedServiceRequest.
statementHandlerDefinitions	Lists the defined statement handlers that you can use with any sql statements to transform data results when the statement executes.

Defining Connections

You can define connections as follows:

Connection	Description
connectionDefinitions	Defines connections over which client applications can execute sql data service requests to external data.
dataSourceConnectionDefinition	Defines a J2EE DataSource from which clients can obtain connections. A J2EE DataSource allows applications to leverage native driver connection pooling available through the JDBC optional javax.sql package.

connectionDefinitions

A connectionDefinition tag includes the following attributes and child elements:

Eelement	Type	Description
driver	Attribute	Specifies the driver class to use for this connection.
id	Attribute	Specifies an ID by which to identify this connection.
url	Attribute	A URL to the database that this connection accesses in the form jdbc:subprotocol:subname.
properties	Child	Specifies a list of properties for this connection. Child element: property - specifies a property name and value for that property.

For example:

<connectionDefinition id="engineering" driver="com.mysql.jdbc.Driver" 
  url="jdbc:mysql://dgnc/engineering">
  <properties>
    <property name="user">Ron</property>
    <property name="password">77$Lo</property>
  </properties>
</connectionDefinition>

dataSourceConnectionDefinitions

A dataSourceConnectionDefinition tag includes the following attributes:

Eelement	Type	Description
id	Attribute	Specifies an ID by which to identify this connection.
name	Attribute	The name of the dataSource resource to look up. For example: java:comp/env/jdbc/SalesDB
password	Attribute	The password required to establish a connection with this datasource.
username	Attribute	The username required to establish a connection with this datasource.

For example:

<dataSourceConnectionDefinition id="support" 
  name="java:comp/env/jdbc/SalesDB" password="P3aCh" username="BLame"> 
</dataSourceConnectionDefinition>

Defining statementHandlerDefinitions

You can define statement handlers to process the results of SQL statements after a statement has executed.

You define statement handlers in the statementHandlerDefinitions section using the statementHandlerDefinition tag.

The statementHandlerDefinition tag includes the following attributes:

Eelement	Type	Description
class	Attribute	Specifies the implementing class.
default	Attribute	Specifies whether all SQL statements use this statementHandlerDefinition as the default, if the statement does not inlcude any other statementHandlerDefinitions. True or false. True = Use this statementHandlerDefinition as the default.
id	Attribute	Specifies a unique ID by which to identify this statementHandlerDefinition.

For example:

 <statementHandlerDefinitions>
  <statementHandlerDefinition class="statementHandler" default="false" 
     id="NoItalics"/>
  <statementHandlerDefinition class="statementHandler" default="true"
     id="LowCase"/>
</statementHandlerDefinitions>

Defining statementDefinitions

You can define SQL statements in the nexaweb-sql. xml configuration file to make them available on the server. You can then reference these in dataRequestDefinitions defined in the nexaweb-data.xml file.

You create statementDefinitions in the statementDefinitions section of the nexaweb-sql.xml configuration file.

The SQL data service offers the following types of sql statements:

Tag	Description
<sqlStatement>	Specifies a generic sql statement, which contains a string to execute against a specified connection.
<sqlSelect>	Specifies a simple syntax for making a query to a single table over a specified connection.
<sqlUpdate>	Specifies a simple syntax for making an update to a single table over a specified connection.
<sqlInsert>	Specifies a simple syntax for making a row insert to a single table over a specified connection.
<sqlDelete>	Specifies a simple syntax for deleting rows from a single table over a specified connection.

A statementDefinition includes the following elements:

Eelement	Type	Description	Statement Type	Required/Optional
<connectionId>	Attribute	The ID of the connectionDefinition to use to execute the statement against.	All	Required
<id>	Attribute	Specifies an ID by which to identify this statementDefinition.	All	Required
<statementHandlerId>	Attribute	The ID of a statementHandler to use for processing the statement after successful execution. If none specified, the data service uses the default statement handler.	All	Optional
<statement>	Attribute	The statement to execute.	<sqlStatement>	Required
<table>	Child	Specifies the table from which the statement retrieves or updates rows.	<sqlSelect>, <sqlUpdate>, <sqlInsert>, <sqlDelete>	Optional
<columns>	Child	Specifies the columns to retrieve or update. If none specified, retrieves or updates all columns.	<sqlSelect>, <sqlUpdate>, <sqlInsert>	Optional
<where>	Child	Clause identifying the rows to retrieve or update.	<sqlSelect>, <sqlUpdate>, <sqlDelete>	Optional

Using SQL Data Sources in a serviceRequest

You can specify a SQL statement to use in a data request in any of the following ways:

Location	Description
nexaweb-sql.xml	Specify the statement in the statementDefinitions section of the server side nexaweb-sql.xml configuration file. Then reference it in a serviceResourceReference of a dataRequestDefinition in the nexaweb-data.xml file. (Finally, reference the dataRequestDefinition in a serviceRequest in the client UI file.)
nexaweb-data.xml	Specify a statement in a serviceResourceDefintion of a dataRequestDefinition in the nexaweb-data.xml file. (Then reference the dataRequestDefinition in a serviceRequest in the client UI file.)
Client UI	Specify a statement in an embeddedServiceRequest directly in the client UI file.

Client Side Data Request Plug-in Examples

The following examples demonstrate the two ways that you can define an SQL statement in a UI file.

In-line Example

<dr:dataRequest id=”getDepartmentRequest” 
   target=”dataSource://myDocumentSource” 
   xmlns:dr=”http://nexaweb/com/dataService/request”> 
  <ds:dataRequestDefinition xmlns:ds=”http://nexaweb/com/dataService”> 
    <ds:dataServiceRequest> 
      <jdbc:jdbcStatement connectionId="engineering" 
         xmlns:jdbc=”http://nexaweb/com/dataService/jdbc”> 
      <jdbc:statement>
         SELECT * FROM department where dept={0}
      </jdbc:statement> 
      </ jdbc:jdbcStatement> 
    </ds:dataServiceRequest> 
  </ds:dataRequestDefinition>
</dr:dataRequest>

Shortcut Example

The following example of defining an SQL statement in a cient UI file is equivalent to the preceding In-line example.

 <jdbc:jdbcStatement id=”getDepartmentRequest” connectionId="engineering" 
  target=”dataSource://myDocumentSource” 
  xmlns:jdbc=”http://nexaweb/com/dataService/jdbc”>
  <statement>SELECT * FROM department where dept={0}</statement>
</jdbc:jdbcStatement>
<jdbcService xmlns=http://nexaweb.com/dataService/sql>
  <connectionDefinitions>
    <connectionDefinition id="engineering" driver="com.mysql.jdbc.Driver" 
      url="jdbc:mysql://dgnco/engineering">
      <properties>
        <property name="user">venutian</property>
        <property name="password">venutian</property> 
      </properties> 
   </connectionDefinition>
     <connectionDefinition id="salesdata" driver="com.mysql.jdbc.Driver" 
     url="jdbc:mysql://dgnco/sales?user=venutian&amp;password=venutian"/>
     </connectionDefinitions> 
  <statementHandlerDefinitions>
  </statementHandlerDefinitions> 
  <statementDefinitions> 
     <sqlStatement id="getCustomers" connectionId="salesdata"> 
     <statement>select * from customers</statement> 
     </jdbcStatement>
  </statementDefinitions>
</jdbcService>