a
Setting the JDBC Service Function
A JDBC Service Function is used to interface with Java Database Connectivity (JDBC) and Open Database Connectivity (ODBC) data sources. Typically these data sources are relational databases that are queried using Structured Query Language (SQL.)
JDBC Services require a JDBC driver. There are commercial drivers available for all major databases and free drivers available for most. See JDBC Drivers for a list. ODBC data sources are reachable via a JDBC-ODBC bridge driver, which can communicate
with the relevant ODBC driver. Sun provides a standard JDBC-ODBC bridge driver. ODBC drivers are available for all major databases. In general a JDBC driver provides better performance than using an ODBC driver and the JDBC-ODBC Bridge.
When accessing a remote database, JDBC uses the Internet's file addressing scheme. A file name looks much like a Web page address (or Uniform Resource Locator). For example a Java SQL statement might identify the database as: jdbc:odbc://www.somewhere.com:400/databasefile.
Connections can be made either via a database driver and URL, or via JNDI. Using JNDI allows connection pooling if your application server supports this.
Make sure the JDBC driver you want to use is available on your servlet engine's CLASSPATH.
From the Application Manager menu either:
Click on the New JDBC Services button in the toolbar.
Click on the Edit|New JDBC service menu item.
Right-click on the Services node in the Application Explorer and select New JDBC service in the context menu.
A Service Function is added to the list of Services with the default name of JDBCSERVICE1.
The following JDBC Service window will open:
On the Request
tab, enter the class name of the driver you are using.
For example:com.mysql.jdbc.Driver.
Enter the URL used to
connect to the database.
For example: jdbc:mysql://${db.server.name}/ros?user=test&password=test.
The driver class name, and URL syntax will be specific to the database
and driver you are using. The username and password may also be entered
separately from the URL. Check the database and driver documentation for
details.
Enter a SQL statement for the query you would like to use. Use ${ } notation for any substitutable parameters. You may want to try out the query directly against the database first to make sure the SQL syntax is correct.
Switch to the Test tab. This will automatically check for parameters and add them to a list so you can enter test values.
Click the Test button.
If there is a problem with your setup, or with your SQL query, then appropriate messages will be displayed. Once your service is working you can fine tune the SQL, TEMPLATE and return messages and then re-test.
Tick the Dynamic SQL option if SQL is dynamically passed in from the client. Use this feature with care because different queries may result in record sets not compatible with defined template.
The Request tab specifies how the JDBC service is executed.
Set the Connection Settings box. These control how the Presentation Server will connect to the back-end application. Either URL and DRIVER, or a JNDI name must be specified.
Tick whether Driver and URL or JNDI radio button.
In the Driver field (available for Driver and URL option) enter the class name of the JDBC driver you want to use. The SQL database must support a JDBC driver.
In the URL field (available for Driver and URL option) specify how to connect to a JDBC driver on a particular machine. It may include other parameters such as the name of the database schema, a username and password to connect with. You can specify dynamic value (for example: ${client.DATABASE}) but it only makes sense if you also set Connection type to Execution. In this case a new connection is created every time this service is invoked.
In the User field (available for Driver and URL option) enter the user name used in conjunction with URL to connect to database. This is optional. Most drivers also accept user name as a URL parameter. You can specify dynamic value (for example: ${client.USER}) but it only makes sense if you also set Connection type to Execution. In this case a new connection is created every time this service is invoked.
In the Password field (available for Driver and URL option) enter the password used in conjunction with URL to connect to database. This is optional. Most drivers also accept user name as a URL parameter. You can specify dynamic value (for example: ${client.DATABASE}) but it only makes sense if you also set Connection type to Execution.
From the Connection drop-down menu (available for Driver and URL option) choose one of the following way to create or close database connection:
Session: Once a db connection is created, it is cached and reused only by the same user (same HttpSession) among all JDBC services that are set to Session connection type. Connection is closed and dropped when user's session is invalidated.
Service: (Default mode) Once a db connection is created, it is kept in the service and used only by the same service. The connection is closed and dropped when application is off-loaded.
Execution: db connection is created on every service invocation. Connection is closed and dropped when service is finished.
In the JNDI Name field (available for JNDI option) enter the JNDI name to use to establish a JDBC connection.
Tick the Dynamic SQL option if SQL is dynamically passed in from the client. Use this feature with care because different queries may result in record sets not compatible with defined template.
In SQL field enter the SQL query to execute. Property references ${ } can be used. They will be substituted before each execution of the service.
The Response tab is used to define what type of data to expect from the Service Function.
Check this box if you want to use Enhanced JDBC Response Data Processing. This feature allows you to build more flexible output xml data, including multi-level xml structure and references to properties.
When JDBC Service Functions process result data, some attributes may contain no value. Check this box if you want to remove all attributes with no value.
This field is used to map data returned from SELECT queries into XML. This is optional, if you left blank for a SELECT statement, a default template will be used. For each row returned, each attribute value within the template is replaced by the value of the database column specified.
This button generates a sample template. This is used for SELECT statements to convert the data returned into XML format. You can customize the generated template if necessary, or write your own one. Note that you must have a connection set up first, and a SELECT statement entered in the SQL setting.
Please note: This only applies to XML Service Functions.
Check this box if you want the Timestamp to be extracted from a customized location. If this option is selected you can set the Element and Attribute for locating timestamp information. By default Timestamp are extracted from a Timestamp Attribute on the root element of the XML returned by the Service.
Please note: This option is useful when the return data doesn't contain Timestamp information. However, if you use MASTER-MASTER cluster configuration, data might be lost in failover situation, because the timestamp value for a data block can be different on two Presentation Servers.
Please also note: APS uses Java currentTimeMillis() to set the timestamp. Although its precision is down to 1 ms, the problem is that default timing systems on the native platforms are, in general, of low resolution. The currentTimeMillis is updated every 10ms ~ 16 ms. Therefore it is possible that 2 updates arrive in order but end up in the same millisecond. Check this box if you want the Presentation Server to use system clock for the Timestamp value.
Set this field to an XPath statement pointing to the element containing Timestamp information. By default the Presentation Server continues to find Timestamp information from root element. If more than one element matches, only the first element is used.
Specify the attribute name containing the Timestamp info or TEXT() to indicate that the element's text contains the timestamp. By default the Presentation Server finds Timestamp information from Timestamp default attribute.
Specify whether the Timestamp field is to be read as a Date/Time or as an Integer. By default the Type is set to Integer.
Specify the Format used to parse the Timestamp from the specified Element and Attribute. For a Date/Time this should be a format recognized by java.text.SimpleDateFormat. For an Integer this should be a format recognized by java.text.DecimalFormat. By default if the Type is set to Integer then the Timestamp Attribute is read as an Integer.
Check this box if you want this service to create a new transformer instance every time it does the transformation. By default this option is set to false.
The Acknowledgment tab is used to define how the success or failure of the Service Function is communicated to the client.
From the On success Source drop-down menu, choose one of the following:
NONE: no message will be sent to the client if the Service succeed.
DEFINE: will return the value entered in the Text field if the Service succeed.
If you have selected DEFINE, enter in the Text field the success message to send to the AltioLive client when a service succeed.
From the On failure Source drop-down menu, choose one of the following:
NONE: no message will be sent to the client if the Service failed.
DEFINE: will return the value entered in the Text field if the Service failed.
If you have selected DEFINE, enter in the Text field the fail message to send to the AltioLive client when a service failed.
The Test tab allows a JDBC Service to be tested. Whatever operation you test will be executed on your back-end application. You may need to reset values in your database after testing.
Please note: this is not a simulated test.
In the Name cell enter the name of parameters being used. This corresponds to a parameter in the SQL statement.
In the Test value cell enter a value to test the service.
Click the Test Service button. This displays the results of using the parameters specified in the table. You will be informed whether the test was successful or not, and if not, error messages will be displayed to help you deduce what went wrong.
If a WARNING message No Datakey is defined for element NAME_ELEMENT is displayed, you can click on the Extract Datakeys button in order to create a datakey for this element.
For more information: See the Setting up Datakeys section.
This example shows how a simple template is constructed:
|
Service Name: |
MYFUNCTION |
|
Request tab |
|
|
Connection Settings: |
Driver and URL |
|
Driver: |
com.mysql.jdbc.Driver |
|
URL: |
jdbc:mysql://localhost:3306/ros |
|
User |
test |
|
Password |
test |
|
SQL: |
SELECT THING FROM STUFF |
|
Response tab |
|
|
Template: |
<MY FUNCTION> |
This example updates the SALE_ITEM table, changing the PORT column. Test values have also been included here.
|
Service Name: |
EXISTING_ITEM_UPDATE |
|
Request tab |
|
|
Connection Settings: |
Driver and URL |
|
Driver: |
com.mysql.jdbc.Driver |
|
URL: |
jdbc:mysql://localhost:3306/ros?user=test&password=test |
|
SQL: |
UPDATE SALE_ITEM SET PORT=${client.PORT} WHERE PROD_ID=${client.PROD_ID} |
|
Response tab |
|
|
Template: |
none - this function does not select data |
|
Acknowledgement tab |
|
|
On success: Source |
Define |
|
Text |
Data has been updated |
|
On failure: Source: |
Define |
|
Text |
Error: data has not been updated |
|
Test tab |
|
|
Name |
PORT |
|
Value |
Rotterdam |
|
Name |
PROD_ID |
|
Value |
ID43 |
|
Service name: |
DEL_SEVENTEEN |
|
Request tab |
|
|
Connection settings: |
Driver and URL |
|
Driver: |
com.mysql.jdbc.Driver |
|
URL: |
jdbc:mysql://localhost:3306/ros?user=test&password=test |
|
SQL: |
DELETE FROM ITEM WHERE ITEM_ID='17' |
|
Response tab |
|
|
Template: |
none - this function does not select data |
|
Acknowledgement tab |
|
|
On success: Source: |
Define |
|
Text: |
Data has been deleted |
|
On failure: Source |
Define |
|
Text: |
Error: data has not been deleted |
This example uses a JOIN statement as part of the SELECT, and has a more complicated Template:
|
Service name: |
GET_FORSALE |
|
Request tab |
|
|
Connection settings: |
Driver and URL |
|
Driver: |
com.mysql.jdbc.Driver |
|
URL: |
jdbc:mysql://localhost:3306/ros?user=test&password=test |
|
SQL: |
SELECT S.QTY AS S_QTY, S.EXP_DATE AS S_EXP_DATE, S.PROD_ID AS S_PROD_ID, S.PROD AS S_PROD, S.SALE_ID, S.CTRY AS S_CTRY, S.PORT AS S_PORT, S.CURRENCY AS S_CURRENCY, SELLER_ID, ITEM_ID, CONTROL, S.PRICE AS S_PRICE, S.COMMENT AS S_COMMENT, S.SH_TRMS AS S_SH_TRMS, S.AL_ACTION AS S_AL_ACTION, CONVERT(INT, S.TIMESTAMP) AS S_TS, B.QTY AS B_QTY, BIDDER_ID, B.PROD_ID AS B_PROD_ID, B.PROD AS B_PROD, B.CTRY AS B_CTRY, B.PORT AS B_PORT, B.CURRENCY AS B_CURRENCY, B.EXP_DATE AS B_EXP_DATE, B.PRICE AS B_PRICE, B.COMMENT AS B_COMMENT, BID_ID, B.SH_TRMS AS B_SH_TRMS, B.AL_ACTION AS B_AL_ACTION, CONVERT(INT, B.TIMESTAMP) AS B_TS FROM sale_item S LEFT OUTER JOIN bid B ON B.SALE_ID = S.SALE_ID' |
|
Response tab |
|
|
Template: |
<FOR_SALE> <SALE_ITEM QTY='S_QTY' EXP_DATE='S_EXP_DATE' PROD_ID='S_PROD_ID' PROD='S_PROD' SALE_ID='SALE_ID' CTRY='S_CTRY' PORT='S_PORT' CURRENCY='S_CURRENCY' SELLER_ID='SELLER_ID' ITEM_ID='ITEM_ID' CONTROL='CONTROL' PRICE='S_PRICE' COMMENT='S_COMMENT' SH_TRMS='S_SH_TRMS' TIMESTAMP='S_TS'>; <BID QTY='B_QTY' BIDDER_ID='BIDDER_ID' PROD_ID='B_PROD_ID' PROD='B_PROD' CTRY='B_CTRY' PORT='B_PORT' CURRENCY='B_CURRENCY' EXP_DATE='B_EXP_DATE' PRICE='B_PRICE' COMMENT='B_COMMENT' BID_ID='BID_ID' SH_TRMS='B_SH_TRMS' AL_ACTION='B_AL_ACTION' TIMESTAMP='B_TS'/> </SALE_ITEM> </FOR_SALE> |
|
Acknowledgement tab |
|
|
On success: Source: |
NONE |
|
On failure: Source |
Define |
|
Text: |
Error: no data selected |
JDBC Services Troubleshooting, Enhanced JDBC Response Data Processing, Cache JDBC ResultSet, Offline , Set up Service Functions, Set up Datapools, Server request, Property References