User`s Manual
Contents
1. 0 amp Softworkbench SPGenerator User Manual Step 1 Welcome screen I Don t show this welcome screen again Cancel Help vn sofworkbench com Back Next gt s This step introduces you to the wizard and some useful link e Check Don t show this welcome screen again check box will not show this welcome screen when you select re run wizard in last step e Click Next button go to next step 11 2015 softworkbench com All rights reserved amp Softworkbench SPGenerator User Manual Step 2 Select database screen Select Database Select a RDBMS type and fill in Data Source information Cancel Help vn softworkbench com lt Back ne gt e This step let you select RDBMS e Option to use either Windows Authentication or SQL server Authentication e Click Next button to go to next step Select Database Select a RDBMS type and fill in Data Source information Cancel Help vewow softworkbench com lt Back lL ne gt 2015 softbench com All rights reserved 12 o amp Softworkbench SPGenerator User Manual Step 3 Select table s and view s Select the table s or view s Select one or more tables views you wish to create procedures for BONUS DEPT EMP SALGRADE IV Select All IV Select All Cancel Help www softworkbench com lt Back lL he e This step let you select the table s and view s e Check Select2. All check box to select all tables or views e Uncheck Select All check box to un select all tables or views e Exclude table or views by uncheck it from list view e Click Next button go to next step 13 2015 softworkbench com All rights reserved amp Softworkbench SPGenerator User Manual Step 4 Extract Meta data screen Extract meta data Extract meta data based on table s or view s selected SQL Script Output Path m Stored Procedures Prefix proc_ Other Options Output as one file Extract Meta Data Extract meta data wait B RGE Cancel Help ywow softworkbench com lt Back Next s This step extract metadata for all tables and views show on the left e Fill stored procedures prefix text field if you need it e Click browser button to pick up SQL script output directory e Option to output generated script in one big file or one file per table e Click Extract Meta Data to begin extract metadata from database when finish it will go to next step 2015 softbench com All rights reserved 14 Na i amp Softworkbench SPGenerator User Manual Step 5 Edit stored procedure screen Edit stored procedure s This step allow you to change stored procedure s name and exclude column s Ej ORCL1 H E SCOTT BONUS 9 59 SCOTT DEPT EN proc_prDelete hf proc prinsert ig Proc _prFindAll proc_prUpdate H E SCOTT EMP H E SCOTT SALGRAD
3. LastName FirstName FirstName Title Title TitleOfCourtesy TitleOfCourtesy BirthDate BirthDate HireDate HireDate Address Address City City Region Region PostalCode PostalCode Country Country HomePhone HomePhone Extension Extension Photo Photo Notes Notes ReportsTo ReportsTo 2015 softbench com All rights reserved 18 3 Softworkbench SPGenerator User Manual PhotoPath PhotoPath WHERE EmployeeID EmployeeID GO Delete Stored Procedure based on primary keys if exists select from dbo sysobjects where id object_id N dbo Delete Employees and OBJECTPROPERTY id N IsProcedure 1 drop procedure dbo Delete Employees GO CREATE PROCEDURE dbo Delete Employees EmployeeID int AS SET NOCOUNT ON DELETE FROM Employees WHERE EmployeeID EmployeeID GO Select All Stored Procedures if exists select from dbo sysobjects where id object_id N dbo ReadAll Employees and OBJECTPROPERTY id N IsProcedure 1 drop procedure dbo ReadAll Employees GO CREATE PROCEDURE dbo ReadAll Employees AS SET NOCOUNT ON SELECT EmployeeIlD LastName FirstName Title TitleOfCourtesy BirthDate HireDate Address City Region PostalCode Country HomePhone Extension Photo Notes ReportsTo PhotoPath FROM Employees GO Read Stored Procedure based on prima
4. 255 Softworkbench SPGenerator User Manual Stored procedures generator Softworkbench Inc www softworkbench com HE D eee 4 we 225 Softworkbench SPGenerator User Manual CopyrightO Softworkbench Inc 2015 All Rights Reserved softworkbench com and the associated logo are trademarks of softworkbench com All other trademarks are the property of their respective owners The information in this guide is confidential and proprietary trade secret of Softworkbench Inc It may not be copied distributed without prior written permission This guide is subject to change without notice and does not represent a commitment on the part of softworkbench com The software described in this guide is furnished under license agreement and may be used or copies only accordance with the terms of the agreement Softworkbench Inc www softworkbench com 1 2015 softworkbench com All rights reserved we sae 3 Softworkbench SPGenerator User Manual Table of Contents INTRODUCTION 1 rvmemekem reeeo 2 CHAPTER 1 OVERVIEW OF SOFTWORKBENCH SPGENERATOR i rnnnernvsesnvnenen 3 1 1 WHAT IS SOFTWORKBENCH SPGENERATOR esse ee eee ee eee 4 1 2 WHAT THE SOFTWORKBENCH SPGENERATOR CAN DO sese esse eee eee 5 1 3 ARCHITECTURE OVEVIEW OF SOFTWORKBENCH SPGENERATOR ssescssesseseeseeeseees 6 CHAPTER 2 GET STARTED vatcscsasssnisinunssavasennsavaunninucasacssanassnsinataiwssiaveiensbevncnanniaansina 9 2 1 PREREQUISITIES ist cele ca wien in les
5. E Cancel Help www softworkbench com lt Back ne gt e This step let you change stored procedure s name and exclude columns e Select the stored procedure in the left hand tree view change the stored procedure s name and exclude columns by uncheck them from list view in the right hand then click Save button e For Oracle check Sequence checkbox and select sequence name from dropdown box Softworkbench SPGenerator will generate code to get sequence number and return it as out put parameter Note you need create sequence first e Click Next button to go to next step 15 2015 softworkbench com All rights reserved amp Softworkbench SPGenerator User Manual Step 6 Generating screen Now generating Softbench SPGenerator is now generating stored procedures please wait Table or view DEPT Cancel Her www softworkbench com lt Back Next gt s This step shows the process generating the stored procedures e Itwill go to next step when finished 2015 softbench com All rights reserved 16 amp Softworkbench SPGenerator User Manual Step 7 Completing screen SQL script fles were created in the following directory C Li Project SoftBench SPGenerator Deplopyment SQLScript Orade The following SQL scripts were generated SCOTT BONUS packages sq I Would you like to run this wizard again Cancel Heer www softworkbench com Back Finish e Th
6. E AS BEGIN open p cur for select EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO from EMP WHERE DEPTNO p DEPTNO END Read Stored Procedure based on primary keys PROCEDURE Read EMP p cur OUT cursor type p EMPNO IN EMP EMPNOSTYPE AS BEGIN open p cur for select EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO from EMP WHERE EMPNO p EMPNO END END EMP PKG End Package body if End of Package for table EMP 2015 softbench com All rights reserved 24
7. N EMP SALSTYPE p COMM IN EMP COMMSTYPE p DEPTNO IN EMP DEPTNOSTYPE num EMPNO NUMBER BEGIN select EMPNO SEQ NEXTVAL into num EMPNO from DUAL insert into EMP EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO values num EMPNO p ENAME p JOB p MGR p HIREDATE p SAL p COMM p_DEPTNO 1 p_EMPNO num_EMPNO END Update Stored Procedure based on primary keys PROCEDURE Update EMP p EMPNO IN EMP EMPNOSTYPE p ENAME IN EMP ENAMESTYPE p JOB IN EMP JOBSTYPE p MGR IN EMP MGRSTYPE p HIREDATE IN EMP HIREDATESTYPE p SAL IN EMP SALSTYPE p COMM IN EMP COMMSTYPE p DEPTNO IN EMP DEPTNOSTYPE AS BEGIN update EMP set ENAME p ENAME JOB p JOB MGR p MGR HIREDATE p HIREDATE SAL p SAL COMM p COMM DEPTNO p DEPTNO WHERE EMPNO p EMPNO END Select All Stored Procedures PROCEDURE ReadAll EMP p cur OUT cursor type AS 23 2015 softworkbench com All rights reserved BEGIN open p cur for select EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO from EMP END eo amp Softworkbench SPGenerator User Manual Delete Stored Procedure based on primary keys PROCEDURE Delete EMP p_EMPNO IN EMP EMPNOSTYPE AS BEGIN delete from EMP WHERE EMPNO p EMPNO END Read all Stored Procedure based on foreign key PROCEDURE ReadALL BY DEPTNO EMP p cur OUT cursor type p DEPTNO IN EMP DEPTNOSTYP
8. Y id N IsProcedure 1 drop procedure dbo Insert Employees GO CREATE PROCEDURE dbo Insert Employees EmployeeID int output LastName nvarchar 40 FirstName nvarchar 20 Title nvarchar 60 TitleOfCourtesy nvarchar 50 BirthDate datetime HireDate datetime Address nvarchar 120 City nvarchar 30 Region nvarchar 30 QO Qa A 2015 softbench com All rights reserved 20 3 Softworkbench SPGenerator User Manual PostalCode nvarchar 20 Country nvarchar 30 HomePhone nvarchar 48 Extension nvarchar 8 Photo image Notes ntext ReportsTo int PhotoPath nvarchar 510 AS SET NOCOUNT ON INSERT INTO Employees LastName FirstName Title TitleOfCourtesy BirthDate HireDate Address Cityl Region PostalCode Country HomePhone Extension Photo Notes ReportsTo PhotoPath VALUES LastName FirstName Title TitleOfCourtesy BirthDate HireDate Address City Region PostalCode Country HomePhone Extension Photo Notes ReportsTo PhotoPath DADDDADADOAA AD A AD A A AD SET EmployeeID IDENTITY GO End of Stored Procedure for table Employees 21 2015 softworkbench com All rights reserved we i 3 Softworkbench SPGenerator User Manual APPENDIX B ORACLE SQL SCRIPT SAMPLE SCOTT Package was auto generated by Softworkbench SPGenerator v1 1 Generated time Frida
9. ation gt lt prefix gt proc lt prefix gt lt suffix gt suffix lt suffix gt lt procedure type Insert gt tableName Insert lt procedure gt lt procedure type Update gt tableName Update lt procedure gt lt procedure type Delete gt tableName Delete lt procedure gt lt procedure type Read gt tableName GetSingle lt procedure gt lt procedure type ReadAll gt tableName GetAll lt procedure gt lt procedure type ReadAllByFK gt tableName GetAllBy columnName lt procedure gt lt procedure type View gt View tableName GetAll lt procedure gt lt database gt lt database type Oracle gt lt servername gt localhost lt servername gt lt port gt 1521 lt port gt lt databasename gt orcl lt databasename gt lt loginname gt scott lt loginname gt lt password gt tiger lt password gt lt prefix gt proc_ lt prefix gt lt suffix gt suffix lt suffix gt lt procedure type Insert gt prinsert lt procedure gt lt procedure type Update gt prUpdate lt procedure gt lt procedure type Delete gt prDelete lt procedure gt lt procedure type Read gt prGetSingle lt procedure gt lt procedure type ReadAll gt prGetAll lt procedure gt lt procedure type ReadAllByFK gt prGetAllBy columnName lt procedure gt lt procedure type View gt vwGetAll lt procedure gt lt database gt lt databases gt 2015 softbench com All rights reserved 10
10. dures to select all rows of tables and views e Generate one big sql script file or each file per table e Generate XML metadata definition file e Control output stored procedures format in runtime by modifying XSL style sheet e Control output stored procedure s name and columns in the run time e Generate package header and body for Oracle e Support composite primary key e Support Windows Authentication and SQL Server Authentication 2015 softbench com All rights reserved 6 ey Softworkbench SPGenerator User Manual 1 3 Architecture overview of Softworkbench SPGenerator Softworkbench SPGenerator stored procedures generator consists of a number of distinct processing phases as show in the following figure RDBMS Softworkbench SPGenerator SQL Server ad D S Extract CS Input S Meta data XML file 3 Oracle Extract metadata Build xmi definition file Transformation Process Process Collect metadata process Softworkbench SPGenerator use a powerful database meta data API collecting metadata from different database management system such as MS SQL server Oracle and store in the in memory database object for building xml definition file process Build XML metadata definition file process Softworkbench SPGenerator process each table and view you selected and build the XML definition file as input for XSLT transformation process XSLT Transform process Softworkbench SPGenerator load the XSL style sheet read the sourc
11. e XML definition file to produce the output source SQL script file and write it to the file system 7 2015 softworkbench com All rights reserved 3 Softworkbench SPGenerator User Manual Chapter 2 Get Started Softworkbench SPGenerator will process each table and view in the database which you selected and output SQL script files per table and view or one big file per database You can find sql scripts example for MS SQL Server and Oracle in Appendix A and B Before You Begin Download SPGenerator zip from www softworkbench com This chapter provides information on working with Softworkbench SPGenerator including e Prerequisites e General information e Walk through e Compile SQL script files 2 1 Prerequisites Ensure the following prerequisites are met prior to installing the Softworkbench SPGenerator e Microsoft NET framework 2 0 or later version is required 2015 softbench com All rights reserved 8 Cy Softworkbench SPGenerator User Manual 2 2 General information Softworkbench SPGenerator will process each table and view in the database which is selected in wizard step 4 Per table and view each field definition is read and stored in a ColumnCollection object Softworkbench SPGenerator tries to determine if a field is part of the Primary Key if it s a Foreign Key or if it has a UNIQUE constraint This information besides the type length precision and other field information is used to determine which
12. en es ilies orn inane 9 2 2 TEEN 10 ETE THROUGH P 11 APPENDIX A MS SQL SERVER SQL SCRITPT SAMPLE ccssssssseersseees 19 APPENDIX B ORACLE SQL SCRIPT SAMPLE rnrsvrnvnvrnvnvnnnnvnnnnvnnnnennnnennnnvnnenener 23 2015 softbench com All rights reserved 2 wes T GS Softworkbench SPGenerator User Manual Introduction Overview This guide provides information on the Softworkbench SPGenerator architecture and working with the stored procedures generator Audience The intended audience for this guide is database application developers and DBAs Document organization This document is divided into the following chapters e Chapter 1 provides an overview of the Softworkbench SPGenerator e Chapter 2 provides information on working Softworkbench SPGenerator 2015 softworkbench com All rights reserved 3 Softworkbench SPGenerator User Manual Chapter 1 Overview of SoftworkbenchSPGenerator Softworkbench SPGenerator is a XML XSLT template based stored procedures generator that you use to generate basic CRUD stored procedures This chapter provides an overview of Softworkbench SPGenerator including e What is SoftworkbenchSPGenerator e What can Softworkbench SPGenerator do e Architecture overview of Softworkbench SPGenerator 02015 softbench com All rights reserved 4 Cy Softworkbench SPGenerator User Manual 1 1 What is the Softworkbench SPGenerator Softworkbench SPGenerator is a stored procedure gene
13. is step show you the location and SQL script files generated e Click Finish button to complete the generating the stored procedures e Softworkbench SPGenerator wizard will run again if you checked Would you like to run this wizard again checkbox 17 2015 softworkbench com All rights reserved eo as Softworkbench SPGenerator User Manual APPENDIX A SQL SERVER SQL SCRIPT SAMPLE NORTHWIND Stored procedures were auto generated by Softworkbench SPGenerator vl 1 Generated time Thursday May 12 2015 9 36 12 PM Web http www softworkbench com Email info softworkbench com Stored Procedure generated for table Employees Update Stored Procedure based on primary keys if exists select from dbo sysobjects where id object _id N dbo Update Employees and OBJECTPROPERTY id N IsProcedure 1 drop procedure dbo Update Employees GO CREATE PROCEDURE dbo Update Employees EmployeeID int LastName nvarchar 40 FirstName nvarchar 20 Title nvarchar 60 TitleOfCourtesy nvarchar 50 BirthDate datetime HireDate datetime Address nvarchar 120 City nvarchar 30 Region nvarchar 30 PostalCode nvarchar 20 Country nvarchar 30 HomePhone nvarchar 48 Extension nvarchar 8 Photo image Notes ntext ReportsTo int PhotoPath nvarchar 510 DADDDADADAOA AD QA AD OQ AD AS SET NOCOUNT ON UPDATE Employees SET LastName
14. rator that supports Microsoft SQL Server and Oracle Softworkbench SPGenerator will process each table and view in the database which you selected and created stored procedures to insert update delete select single or multiple records using the fields you choose as well as using stored procedure name you desire Softworkbench SPGenerator supports the rapid development of database stored procedures by using XML as metadata input and XML Style sheet Language Transformations XSLT as syntax tree scripting mechanism this XML XSLT base code generation approach give you a great amount of feasibility to customize the output format you can modify the XSLT script to meet your special requirement Below is how it works e Collect metadata from different data source Oracle and SQL Server e Build xml metadata input definition file e Generate stored procedures using xslt with different xsl stlysheets 5 2015 softworkbench com All rights reserved 3 Softworkbench SPGenerator User Manual 1 2 What the Softworkbench SPGenerator can do Using Softworkbench SPGenerator you can performance the following tasks e Generate stored procedures to insert data rows and return identity id for SQL server and sequence number for Oracle e Generate stored procedures to select update delete single data row based on primary key definitions e Generate stored procedures to select all rows based on foreign key definitions e Generate stored proce
15. ry keys if exists select from dbo sysobjects where id object_id N dbo Read_Employees and OBJECTPROPERTY id N IsProcedure 1 drop procedure dbo Read Employees GO CREATE PROCEDURE dbo Read_Employees EmployeeID int AS SET NOCOUNT ON SELECT EmployeeID LastName FirstName TitleOfCourtesy 19 2015 softworkbench com All rights reserved BirthDate HireDate Address Cityl Region PostalCode Country HomePhone Extension Photo Notes ReportsTo PhotoPath FROM Employees WHERE EmployeeID EmployeeID GO Pa amp Softworkbench SPGenerator User Manual Read all Stored Procedure based on foreign key if exists select from dbo sysobjects where id object_id N dbo ReadALL BY ReportsTo Employees and OBJECTPROPERTY id N IsProcedure 1 drop procedure dbo ReadALL BY ReportsTo Employees GO CREATE PROCEDURE dbo ReportsTo int AS SET NOCOUNT ON SELECT EmployeeID LastName FirstName Title TitleOfCourtesy BirthDate HireDate Address City Region PostalCode Country HomePhone Extension Photol Notes ReportsTo PhotoPath FROM Employees WHERE ReportsTo ReportsTo GO Insert Stored Procedure ReadALL BY ReportsTo Employees if exists select from dbo sysobjects where id object id N dbo Insert Employees and OBJECTPROPERT
16. stored procedures should be generated and which fields should be passed as parameters Softworkbench SPGenerator wizard is very user friendly and takes you five steps to generate all necessary insert update delete select single or multiple records stored procedures for MS SQL Server and ORACLE Unlike many other stored procedures generator Softworkbench SPGenerator let you select tables or views change each stored procedure s name exclude columns you do not need from stored procedure you selected 9 2015 softworkbench com All rights reserved eo GS Softworkbench SPGenerator User Manual 2 3 Walk through Softworkbench SPGenerator uses a Configuration xml file to store database server name database name login name password stored procedure s prefix and suffix as well as format of stored procedure name You can edit this xml file to meet your requirement before run the Softworkbench SPGenerator For example you can change the Insert procedure format as Insert tableName so the name of insert procedure will look like Insert Employee instead of Employee Insert Here is Configuration xml file layout lt xml version 1 0 gt lt databases gt lt database type SQLServer gt lt servername gt localhost lt servername gt lt databasename gt Northwind lt databasename gt lt loginname gt lt loginname gt lt password gt lt password gt lt windowsauthentication gt true lt windowsauthentic
17. y May 12 2015 3 33 27 PM Web http www softworkbench com Email info softworkbench com Package generated for table EMP Begin Package header CREATE OR REPLACE PACKAGE EMP PKG AS TYPE cursor type IS REF CURSOR PROCEDURE Insert EMP EMPNO OUT EMP EMPNOSTYPE ENAME IN EMP ENAMESTYPE JOB IN EMP JOBSTYPE MGR IN EMP MGRSTYPE HIREDATE IN EMP HIREDATESTYPE SAL IN EMP SALSTYPE COMM IN EMP COMMSTYPE DEPTNO IN EMP DEPTNOSTYPE OO OG PPT z PROCEDURE Update EMP EMPNO IN EMP EMPNOSTYPE ENAME IN EMP ENAMESTYPE JOB IN EMP JOBSTYPE MGR IN EMP MGR TYPE HIREDATE IN EMP HIREDATESTYPE SAL IN EMP SALSTYPE COMM IN EMP COMM TYPE DEPTNO IN EMP DEPTNOSTYPE FS OS AO ES T FS TO ROC EDU A pa ReadAll EMP p cur OUT cursor type PROCEDURE Delete EMP p EMPNO IN EMP EMPNOSTYPE mg ROCEDURE ReadALL BY DEPTNO EMP p cur OUT cursor type p DEPTNO IN EMP DEPTNOSTYPE PROCEDURE Read EMP p_cur OUT cursor type p_EMPNO IN EMP EMPNOSTYPE 1 END EMP PKG End Package header Begin Package body CREATE OR REPLACE PACKAGE BODY EMP PKG AS Insert Stored Procedure 2015 softbench com All rights reserved 22 3 Softworkbench SPGenerator User Manual PROCEDURE Insert EMP p EMPNO OUT EMP EMPNOSTYPE p ENAME IN EMP ENAMESTYPE p JOB IN EMP JOBSTYPE p MGR IN EMP MGRSTYPE p HIREDATE IN EMP HIREDATESTYPE p SAL I
Download Pdf Manuals
Related Search