Orindasoft

JDBCWizard Web Service Generator Reference Manual

Running JDBCWizard

Topics

1. Starting the JDBCWizard GUI
   
2. Step One: Log In
   
3. Step Two: Select Your Database Objects
   
4. Step Three: Choose your Code Options
   
5. Step Four: Set Your Service Options
   
6. Step Five: Extra Generation Options
   
7. Step Six: Generate Your Code
   

Starting the JDBCWizard GUI

Start JDBCWizard. Make sure to choose the correct version for your database.


The JDBCWizard (GUI) appears and you are ready to begin.

Step One: Log In

1. Enter the following database connection information in the 1. Log In screen:



Field	Requires
TCP/IP Hostname	The hostname or IP address of the database server.
Oracle SID	The Oracle instance identifier.
SQL*NET Port	The SQL*NET port used by the Oracle Listener, usually 1521
Oracle User	Your Oracle username.
Oracle Password	Your Oracle username's password.



2. Click the Connect button to begin a search for the database's stored procedures and functions.

Changing the User Interface

Use the JDBCWizard Skin menu to change the appearance of the graphical user interface. You must select a user interface supported by Java 2.0.

Step Two: Select Your Database Objects

Use the 2. Select Objects screen to choose the database PL/SQL Packages, Procedures, Functions, Sequences and SQL statements that require Java access code.

2.1 Objects Belonging To...

JDBCWizard generates code for your own objects and for those owned by other users, provided they have given you access rights.

1. Choose from the following options to select the required database objects:
   • The Current User check box allows you to retrieve the objects owned by the current username.
   • The User check box allows you retrieve the objects owned by users whose names match the pattern in the field next to the Refresh button. Enter a complete Oracle username or a partial username with a wildcard character like '%' or '_'.

     Note: If you use a wildcard character, wait until JDBCWizard completes the retrieval of all selected objects.

     
     
2. Choose the Refresh button.
   The Refresh function caches retrieved data on your computer. To add retrieved resources to the selection grid table or to remove them, you must disconnect and reconnect to the database.

2.2.1 PL/SQL Packages, Procedures and Functions

The 2.2.1 PL/SQL Packages, Procedures and Functions screen shows the PL/SQL objects for which JDBCWizard generates Java access code. Each row in the selection grid table represents a standalone procedure or a procedure within a package.


Choose Select All or Select None buttons to select or de-select all objects.

The grid table has the following fields:

Field	Description
Owner	The owner of the resource. If this is a synonym, this will be your username. If you have access rights to an object owned by another user, you see their username. The name PUBLIC means that the resource is a publicly owned synonym.
Name	The name of the object or the synonym name. For procedures inside packages, JDBCWizard adds the package name to the beginning of the procedure name. If another procedure exists with the same name but different parameters the name will be followed by " overload n".
Selected	Choose this check box to add the object to the list of objects that require generated code.
Accessed Via	There are four methods ways of accessing objects: User Object: An object owned by the current user Other User's Object: An object for which you have EXECUTE or SELECT privileges, but is owned by another user Private Synonym: A synonym you have created for an object owned by someone else. Public Synonym: A public synonym for an object.
Real Owner	The actual owner of the selected object.
Real Name	The actual name of the selected object.

2.2.2 Sql Statements

The 2.2.2 Sql Statements screen allows the user to select a directory of SQL statements to generate Java code for and to give names and data types to parameters.
Enter the directory where you keep your SQL statement files in step 2.2.3.1 and click on the 'Refresh' button. Use step 2.2.3.2 to select the SQL statements you want to use.

The Tree structure on the left shows the parent directory and its SQL files. Clicking on a file brings it up in the file parameters window.

File Parameters Window.

This window consists of a checkbox, a table of Parameters and a listing of the SQL Statement.

The checkbox is used to control whether matching Java code is generated or not.

Parameter Table

The parameter table contains the following fields:

Field	Description
Line #	The number of the line in the SQL file that the parameter was found on.
Parameter Name	The Name that has been given to this parameter. This must be a valid Java identifier. When first selected the name will default to 'ParamX' where 'X' is the number of the parameter.If the same parameter name is reused it will be assumed that there is one parameter that is used in multiple locations. If This field is greyed out it means that the Parameter name has been set by a Hint in the SQL Statement.
Parameter Data Type	In addition to naming parameters you must also specify a data type. The list allows you to select from the following: STRING - Text Data types including VARCHAR and VARCHAR2 NUMBER - All numeric Oracle Data Types DATE - Date (to within 1 second) LONG - Oracle LONG columns CLOB - Oracle CLOB columns BLOB - Oracle BLOB columns BFILE - Oracle BFILE columns RAW  - Oracle RAW columns LONG RAW - Oracle LONG RAW columns TIMESTAMP - Oracle TIMESTAMP columns If this field is greyed out it means that the Parameter Data type has been set by a Hint in the Sql Statement.

SQL Statement listing table

This lists the SQL statement and shows which parameter is on which line. The Tooltip Text will reveal the name and data types that have been assigned to a parameter on any given line:

Support for SQL*Plus and JDBC Parameters

JDBCWizard supports SQL files that contain parameters in JDBC syntax ("empno = ?") or Sql*Plus syntax ("empno  = &empno"). If processing a SQL*Plus parameter quotes on either side of it will be ignored and the statement will be presented to the user in JDBC syntax.

Hints in SQL statements

If the parameter is followed by a comment consisting of a name and a data type then they will be used by JDBCWizard. For example:

where e.empno = ? /* empNumber NUMBER */

will lead to the parameter being called 'empNumber' and the data type being set to 'NUMBER'. If a comment is used then the Name and Data Type fields will be greyed out. Hints work slightly differently for SQL files that have been edited in SQL*Plus:

where e.empno = &empNumber /* NUMBER */

Requirements for SQL files

JDBCWizard makes the following assumptions about the SQL files it uses: 

• Only one statement per file
• Comments should be clearly marked with "/* ... */"
• The file should end in .SQL
• The SQL statement is a valid statement.

2.2.3 Tables

The 2.2.3 Tables screen shows the Tables for which JDBCWizard generates Java access code. Each row in the selection grid table represents a table you own or have the right to access.


Choose Select All or Select None buttons to select or de-select all tables.

The grid table has the following fields:

Field	Description
Owner	The owner of the table. In the case of a synonym this is your username. If you have access rights to a table owned by another user, you see their username. The name PUBLIC means that the table  is a publicly owned synonym.
Name	The name of the table or the synonym name.
Selected	Choose this check box to add the object to the list of objects that require generated code.
Accessed Via	There are four ways of accessing objects: User Object: An object owned by the current user Other User's Object: A table for which you have privileges, but is owned by another user Private Synonym: A synonym you have created for a table owned by someone else. Public Synonym: A public synonym for a table
Real Owner	The actual owner of the selected table.
Real Name	The actual name of the selected table.

Using Multiple Tables

If you want generated table code that allows you to retrieve all the child records for a parent record you must select both tables in the user interface.

Using Synonyms and other user's tables

JDBCWizard will happily generate code to access tables using synonyms or by prepending other peoples usernames.

Views and tables without Primary Keys

JDBCWizard relies on the existance of a primary key to write code to update, delete and retrieve records. If you select a view or a table that does not have a Primary Key constraint JDBCWizard will generate code but most of usual methods will be left out.

2.2.4 Sequences

Select the sequences for which to generate access code using the 2.2.4 Sequences screen:

3. Step Three: Code Options

Add control information such as comments and debug messages to the generated code using the 3. Code Options screen.

3.1 Name and Version fields

Enter your name and the version number of your code. This information appears as header comments in the generated code.
This field can be used in one of two ways:

1. Enter comments for your code. These comments appear at the beginning of your generated code.
2. Enter the path to a file in include text as a comment in your code. Use this approach for copyright and other standard notices.

Step 3.2 Code Options

Choose from the following options

Section	Option	Purpose
3.2.1 Messages in generated code	Debug Messages in code	Adding debug messages to the generated code
	Other Messages in Code	Adding other messages to the generated code
3.2.2 Comments in generated code	Comments in Code	Adding detailed comments to the generated code.
3.2.3 Maintain usage statistics	Maintain Usage Statistics	Generating JDBCWizard performance metrics. Each generated class implements the StatsInterface Java Interface.
3.2.4 Naming convention for generated files	Java Naming Convention	Choosing the naming conventions for generated Java files: JavaStandard.java The filename starts with an upper case letter and then uses upper case letters where the previous character in the matching database object was an underscore. InitialCapitalLetters.java The filename is lower case except where the previous character was an underscore. This means that generated classes names will normally start with a lower case letter. spaces_between_words.java The filename is lower case. Underscores are left intact. This option is deprecated.
3.2.5 Target version of Oracle	Oracle Version	Generating code optimized for one of the following versions of Oracle: 8.1.5, 8.1.6, 8.1.7, 9.0.1, 9.2.0, 10.1.0, 10.2.0, 11.1.0, 11.2.0
3.2.6 Use the following numeric data types	A checkbox for each of the following Java data types: byte short int long float double java.lang.Byte java.lang.short java.lang.Integer java.lang.Long java.lang.Float java.lang.Double	Whenever JDBCWizard is writing a method for passing numbers to or from a procedure, SQL statement or table its default behaviour is to generate a seperate method for each of the data types on the left. By unticking the appropriate checkboxes it is possible to prevent JDBCWizard from generating code with datatypes such as short, which are rarely used in practice. This feature was added in build 4.0.1709
3.2.7 Table Specific Options	Check columns before insert or update	If checked generated table access code will make sure that all non-null fields have values and that numeric fields do not have decimal places if they aren't supposed to. This feature was added in build 4.0.1709
	Create TYPE definitions for tables in a file called 'extraObjects.sql'	If checked a SQL file called 'extraObjects.sql' will be created in the 'tables' subdirectory of the generated code. This file contains matching oracle TYPE definitions for the tables selected in step 2.2.4. For each table it will create a record type and an array type. This code is provided to assist developers who need to pass arrays into PL/SQL procedures. Because of limitations in JDBC the only kind of array of records that can be passed from Java to a PL/SQL procedure is one that uses ORACLE TYPEs. For further information on this subject see the manual entry for Working with PL/SQL Arrays that can not be directly accessed by JDBC.  This feature was added in build 4.0.1935.
3.2.8 Target JVM	Target JVM	Select the target JVM for generated code using the drop down list. Note that JSR-181 annotations are enabled if you select 1.6 or higher
3.2.9 Prefix for PL/SQL methods	Prefix for PL/SQL methods	Enter the prefix for methods in the Implementation class that call PL/SQL procedures. Note that if you change this to a non-default value it's possible (albeit unlikely) that you override one of the housekeeping methods in the Implementation class.
3.2.10 Prefix for PL/SQL methods	Prefix for PL/SQL methods	Enter the prefix for methods in the Implementation class that call SQL Statements. Note that if you change this to a non-default value it's possible (albeit unlikely) that you override one of the housekeeping methods in the Implementation class

Step Four: Service Options

The 4. Service Options screen allows you to

1. Pick a directory for generated code
2. Choose a package for generated code
3. Define how your Data Access Object Factory class will behave
4. Define how the Web Service classes behave.

Step 4.1 - Java code root directory

Enter the path to the directory on your computer for the generated code.

Step 4.2 - Package name

Enter the package name for the generated code. In the previous illustration, JDBCWizard generates code in the following directory:

C:/Test3/com/yourcompany/yourproduct

Step 4.3 - DAO Factory Class options

In addition to creating classes for each procedure and table in your database JDBCWizard can also create a single Data Access Object Factory class that handles logging, database  connections and the creation of access class instances.

This step has the following options:

Option	Purpose
Interface Class Name	.The name of the class. This can be any valid Java class name.If this field is blank no field will be created.
Log messages using...	There are five ways of logging messages JDBCWizard.s TextLog - Messages are written to text files. JDBCWizard.s ConsoleLog - Messages are written to System.out and System.err Java 1.4 logging - Messages are written using java.util.logging. Log4J - Messages are written using Log4J User Implemented LogInterface - Messages are written using a user created class that implements the com.orindasoft.pub.LogInterface Interface.
Log name/Directory	If messages are logged using TextLog this will be the name of the log file directory. If Java 1.4 logging or Log4J are in use this will be the logging context.
Get DB Connection using...	There are four methods of obtaining a Connection: Hard coded connect string: A connect string used by the thin driver is entered in the field below. JNDI used to get DataSource: JNDI is used to retrieve a DataSource object. the JNDI resource name is entered in the field below. DataSource provided at runtime: The generated code will be passed a DataSource at runtime. oracle.jbo.server.DBTransaction: The connection will be extracted from an instance of this class which is passed in at runtime.
Connection Name	An Oracle thin driver connect string or the name of the DataSource in JNDI
Implement javax.ejb.SessionBean	If selected the generated class will implement the SessionBean interface.
Add 'finalize()' method	If selected the generated class will have a finalize method that releases database resources.
Close Connections	Close connections when releaseConnection() is called
Commit Connections	Commit connections when releaseConnection() is called
Temporary Directory	Location for temporary files when working with LOBS. Can be a real operating system directory or a System property such as 'user.dir'.
Temporary File Prefix/Suffix	Prefix and suffix used by temporary files.

Full information on how to use the DAO Factory class is available in the DAO Factory Manual section.

Step 4.4 -Web Service Class options

The 'Create Web Services' checkbox controls whether Web Services classes are created or not. The other options are:

Option	Purpose
Interface Class Name	.The name of the interface class that contains web service methods
Implementing Class Name	The name of the class that implements the Web Service
Number Type used by service	By default generated code will use java.math.BigDecimal to represent  numbers, as this is the closest match in Java to Oracle's number datatype. For convenience JDBCWizard can also use the following data types to represent numbers in Web Services code: int long float double
Always release database connection after call.	If selected releaseConnection() will be called after each service method call.
Add pre call code stubs	An empty method is created that is called by each service method just before execution. By extending the implementing class and overiding this method users can add custom functionality without modifiying generated code.
Add post call code stubs	An empty method is created that is called by each service method after execution. By extending the implementing class and overiding this method users can add custom functionality without modifiying generated code.
Web Service Record Type	Generated web service records can either be: public, no access methods - Records have public variables and no 'set' and 'get' methods private, 'set' and 'get' methods - Records have protected variables and you must use 'set' and 'get' methods.
Uploaded BFILE naming is abstract	JDBCWizard will write code that under some circumstances allows the creation of BFILEs by web services. This option gives the user control over how BFILEs are named. For more information see Creation of BFILEs using a Web Service

Full information on how to use the Web Services classes is available in the Web Service Manual section.

Step Five: Extra Generation Options 

The Extra Options tab contains additional options for customizing code and running batch files after the generation process.

Step 5.1 - Extra Java code

Any code entered in this window will be added directly to the Implementation class without validation. You can use this window to overrode a JSR-181 @WebService annotation - a @WebService annotation entered here will override the one automatically created when you select a 1.6 or higher JVM.

Step 5.2 - Post Generation Script

This section has two fields - a script name and script contents. If you provide a value for both of these fields JDBCWizard will create the script and execute it as the last stage of the generation process.
This means that you can do things like compile your Java or package it for an Application Server.
Note that as with any script you can also cause harm if you write it incorrectly.
Do not write a script which runs a Java class that doesn't finish in a finite period of time (e.g. starts an Application Server). JDBCWizard waits for the script to finish executing before returning control back to the user, so a never ending script will cause a serious problem.
All scripts will be passed the following parameters in the following order:

1. Code Directory
2. Java package name (e.g. com.foo.bar)
3. Java package path (e.g. com\foo\bar)
4. Interface class name
5. Implementation class name

There is also a "Generic Batch File" template button which will create a default script for you.
 

Step 6: Generate Your Code 

 Choose the Generate Code button from the 5. Generate Code screen

JDBCWizard generates a Java file for your selected procedures, functions and sequences.



During code generation, JDBCWizard.

1. Checks the validity of your Orinda Software license.
2. Saves your information to a configuration file with a .PB2 extension
3. Looks for existing Java files in the location you specified for the generated code
4. Generates Java files extended or used by the generated code.

The PB2 Configuration File

JDBCWizard saves the information you entered during the code generation steps to a configuration file with a PB2 extension. JDBCWizard uses this file when you next launch the program.

Refer to the Configuration File Section for more information about editing and maintaining your configuration files.