Instant Web services from Teradata Stored Procedures

Tools
Tools covers the tools and utilities you use to work with Teradata and its supporting ecosystem. You'll find information on everything from the Teradata Eclipse plug-in to load/extract tools.
Teradata Employee

Instant Web services from Teradata Stored Procedures

The iBatis (MyBatis) Stored Procedure Wizard allows you to right click on a Stored Procedure in the Teradata plug-in for Eclipse and quickly create a Web service.

The iBatis Stored Procedure Wizard wraps a Stored Procedure into an iBatis or MyBatis SQL Map. The generated SQL map can then be used to create a Web service or it can be used to create a Java application that uses the iBatis or MyBatis frame works.

Prerequisite for this Article

If you have not worked through the article Create a Teradata Project using the Teradata Plug-in for Eclipse, do so now before you continue. Once you know how to produce a Teradata Project, make a Teradata project called ProductPrj.

You will also need some database objects before you can start this tutorial. First create the following products table using either the SQL Editor or the Stored Procedure creation dialog from the Teradata Plug-in for Eclipse.

CREATE MULTISET TABLE guest.products ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
id INTEGER NOT NULL,
description VARCHAR(255) CHARACTER SET LATIN CASESPECIFIC,
price DECIMAL(15,2),
PRIMARY KEY ( id ));

Now create a Stored Procedure using the products table with the following DDL:

CREATE PROCEDURE "guest"."getProduct" (
IN "id" INTEGER,
OUT "description" VARCHAR(256),
OUT "price" DECIMAL(10 , 2))
BEGIN
Select
price, description into :price, :description
from guest.products where id=:id;
END;

Launch Wizard

The Wizard is launched from the DTP Data Source Explorer by right clicking on a Stored Procedure tree node in the explorer and selecting the "Create iBatis(MyBatis) SQL Map..." menu option.

Stored Procedure Selection

Once the Wizard is launched, the Stored Procedure Selection Wizard page will come up. This page shows the selected schema and procedure for the Wizard.

Create iBatis SQL Mapping XML File

The next page of the Wizard is the Create iBatis SQL Mapping XML File Wizard page. This page lets you define the location, name of the iBatis SQL mapping file and the mapping name for the selected Stored Procedure. The option of appending the mapping to an existing file will be default. You will need to select the option Launch iBatis DAO with Web Services Wizard  if you want to create a Web service directly after you have created a SQL Map for your stored procedure.

Domain Objects Source Location

The next page of the Wizard is the Domain Objects Source Location page. This page lets you define the location and package name of the domain object to be used as the result map for an SQL mapping.

Edit Classes

The next page is the Edit Classes Wizard page. This page lets you rename and edit the properties for the classes which will be created by the Wizard.

This page will show the parameter class and any result set classes that have been derived from the Stored Procedure. The default names of the classes can be renamed to names that make sense for your application. In this case change the Parameters class to Product. You should notice the members of the Parameter class correspond to the parameters for the Stored Procedure.

Generated Code

Once all of the required information is entered into the Wizard, the Finish button can be selected and the SQL Map is generated. The SQL Map contains a resultMap for the parameter class and a SQL statement to call the Stored Procedure. The Stored Procedure being executed has id as an "in" parameter and has description and price as "out" parameters.

?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="repository.ProductMap">

<!-- Define object mapping -->
<resultMap type="domain.Product" id="Product">
<result column="id" jdbcType="INTEGER" javaType="java.lang.Integer" property="id" />
<result column="description" jdbcType="VARCHAR" javaType="java.lang.String" property="description" />
<result column="price" jdbcType="DECIMAL" javaType="java.math.BigDecimal" property="price" />
</resultMap>

<!-- Define procedure SQL statement -->
<select id="getProduct" parameterType="domain.Product" statementType="CALLABLE">
call "guest"."getProduct"(#{id,mode=IN, jdbcType=INTEGER},#{description,mode=OUT, jdbcType=VARCHAR},#{price,mode=OUT, jdbcType=OTHER, typeHandler=com.teradata.commons.mybatis.extensions.NumberHandler})
</select>

</mapper> <!-- Do not edit or add anything below this comment -->

iBatis (MyBatis) DAO with Web Services Wizard

The iBatis (MyBatis) DAO with Web Services Wizard will be launched if the option was selected from the iBatis Stored Procedure Wizard. This Wizard will create a DAO and a Web service derived from the generated SQL Map.

iBatis DAO Definition

The First page of the Wizard defines the new DAO and options to create a Web Service.

Select the following options:

Create WSDL

Create Web Service

Save Password

Now hit the Next button:

iBatis DAO Methods

The iBatis DAO Methods Wizard Page allows you to select which SQL actions from your iBatis Map file to be used in your Web service. You can change your return type from returning a single result set object to returning a list instead. Once you hit the next button Your DAO and Web service Definition files will be created.

Web service Creation

The next page is the standard WTP Web services Wizard. Set your Client to test. Once you hit the Finish button your Stubs and Skeletons will be created for your Web service. The Implementation stub will be modified to use the new DAO you just created.

Web service Client

The Web service client will come up ready to use and connected to your Teradata database, through the Web service implementation. The generated client will show all of the members of the Parameter class but you are only required to enter the id because it is the "in" parameter of the Stored Procedure. The results will show all of the parameters of the stored procedure, id and the two "out" parameters, description and price.

iBatis (MyBatis)  Macro Wizard

A similar Wizard is the  iBatis (MyBatis) Macro Wizard. This Wizard wraps a Macro into an iBatis or MyBatis SQL Map. The newly generated SQL map can then be used to create a Web service as described above or it can be used to create a Java application that uses the iBatis or MyBatis frame works. The Wizard is launched from a Macro Tree node from the DTP Data Source Explorer.

Conclusion

Both the iBatis (MyBatis) Stored Procedure and Macro Wizards are easy to use because parameter and result classes are derived from the selected Stored Procedure or Macro. The Wizards generate DAOs and functional applications you can start using right away. You now can get a head start on your application development by leveraging the Stored Procedures and Macros you have already developed.

1 REPLY
Teradata Employee

Re: Instant Web services from Teradata Stored Procedures

I found if you increase the memory Eclipse uses by increasing the memory settings in the eclipse.ini file, the Web Services Wizard runs much better. (The more the better)

.... add after "-product"
-product
org.eclipse.epp.package.jee.product
--launcher.defaultAction
openFile
--launcher.XXMaxPermSize
512M
-showsplash
org.eclipse.platform
--launcher.XXMaxPermSize
512m
--launcher.defaultAction
openFile
-vmargs
-Dosgi.requiredJavaVersion=1.5
-Xms512m
-Xmx1024m

Also if you have a 64 bit machine, you should take advantage of it. The WTP is very memory intensive when creating a Web Service.
You can increase how much memory is used with Eclipse if you install the 64 bit versions of Java, Eclipse and the Apache Tomcat server.
Once you have done all this, you can increase the memory settings beyond the limitations of a 32 bit machine in your eclipse.ini file.

.... add after "-product"
-product
org.eclipse.epp.package.jee.product
--launcher.defaultAction
openFile
--launcher.XXMaxPermSize
1024M
-showsplash
org.eclipse.platform
--launcher.XXMaxPermSize
1024m
--launcher.defaultAction
openFile
-vmargs
-Dosgi.requiredJavaVersion=1.5
-Xms512m
-Xmx2048m