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:

Defining Connections

You can define connections as follows:

connectionDefinitions

A connectionDefinition tag includes the following attributes and child elements:

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:

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:

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:

A statementDefinition includes the following elements:

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:

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>