This is an old revision of the document!
How to Test Converted SQL Code
This article describes SQLWays Wizard testing options, their usage and generated output.
The supported databases are: Sybase Adaptive Server Enterprise, Oracle and Microsoft SQL Server.
Option TEST
There are two ways to specify the TEST option when running our toolkit:
- /TEST - when running from command-line;
- TEST=Yes - in the [COMMON] section of the sqlways.ini or sqlways_wzd.ini file.
Option TEST is used to generate calling statements for procedures or functions converted either from scripts or from a database. If this option is set the conversion is not performed and only calling statements are generated. Please refer to the following example:
Assume you have the source Sybase ASE procedure. If the TEST option is not specified, conversion to Oracle will be as follows:
Table1
Sybase ASE | Oracle |
---|---|
create procedure sp_proc as declare @x int select @x= 2 GO | create or replace procedure sp_proc as v_x NUMBER(10,0); BEGIN v_x := 2; END; |
When the TEST option is ON the following calling statement will be generated instead of the converted syntax:
Table2
Sybase ASE | Oracle |
---|---|
create procedure sp_proc as declare @x int select @x= count (distinct col1) from Tab1 return @x GO | CALL sp_proc(); |
Option MAX_NUMBER_OF_TEST_CALLS
This option can be additionally specified in [COMMON] section of sqlways.ini or sqlways_wzd.ini file.
Option MAX_NUMBER_OF_TEST_CALLS is used to specify the number of calling statements for procedures or functions converted either from scripts or from a database. Please refer to the following example:
Table3
Sybase ASE | Oracle |
---|---|
create procedure sp_params_select @par1 int, @par2 date, @par3 varchar(20) as DECLARE @var1 int DECLARE @var2 date DECLARE @var3 varchar(20) begin set @var2 = (SELECT tb.col2 from tab_sp_params_select tb, tab_sp_params_select1 tb1 where tb.col1 = @par1 and tb.col2=tb1.col2) set @var3 = (SELECT tb.col3 from tab_sp_params_select tb, tab_sp_params_select1 tb1 where tb.col2 = @par2 and tb.col2=tb1.col2) set @var1 = (SELECT tb.col1 from tab_sp_params_select tb, tab_sp_params_select1 tb1 where tb.col3 = @par3 and tb.col2=tb1.col2) end | CALL sp_params_select(1,null,'1'); CALL sp_params_select(1,null,'1'); CALL sp_params_select(2,null,'1'); CALL sp_params_select(3,null,'1'); CALL sp_params_select(4,null,'1'); CALL sp_params_select(5,null,'1'); CALL sp_params_select(6,null,'1'); CALL sp_params_select(7,null,'1'); CALL sp_params_select(8,null,'1'); CALL sp_params_select(9,null,'1'); CALL sp_params_select(10,null,'1'); CALL sp_params_select(1,TO_DATE('2010-11-01','YYYY-MM-DD'),'1'); CALL sp_params_select(1,null,'1'); CALL sp_params_select(1,null,'2'); CALL sp_params_select(1,null,'3'); CALL sp_params_select(1,null,'4'); CALL sp_params_select(1,null,'5'); CALL sp_params_select(1,null,'6'); CALL sp_params_select(1,null,'7'); CALL sp_params_select(1,null,'8'); CALL sp_params_select(1,null,'9'); CALL sp_params_select(1,null,'10'); |
Please note that there are three SELECT statements inside the SP that return result-sets based on the values specified through the parameters, while calling the SP. So if together with SP there are selected tables that are referenced inside the SP, a set of calling statements based on the data extracted from the source database will be generated. Please consider the following table for the data stored in the table tab_sp_params_select used in the example above:
Table4
tab_sp_params_select data |
---|
1,2010-11-01,1 2,2010-11-01,2 3,2010-11-01,3 4,2010-11-01,4 5,2010-11-01,5 6,2010-11-01,6 7,2010-11-01,7 8,2010-11-01,8 9,2010-11-01,9 10,2010-11-01,10 11,2010-11-01,11 12,2010-11-01,12 13,2010-11-01,13 14,2010-11-01,14 15,2010-11-01,15 16,2010-11-01,16 17,2010-11-01,17 18,2010-11-01,18 19,2010-11-01,19 20,2010-11-01,20 |
As you can see only one parameter is used per each select statement. That is why there are three sections of calling statements generated, each with up to 10 statements inside, as the MAX_NUMBER_OF_TEST_CALLS option is set to 10 for this example. Only DISTINCT values are taken. That is why for column col2 only one date value is used and only one calling statement is generated. The rest of the values are added as default.
DEFAULT values for option TEST
Each database has it's own set of data types, that can be specified differently from RDBMS to RDBMS. That is why the default values for TEST option are specified in their own database section. Please refer to the following table with the sections and options names and their values:
Table5
[Oracle] | [MSSQL] | [Sybase] |
---|---|---|
TEST_FPDV_FLOAT=1 TEST_FPDV_NUMBER=1 TEST_FPDV_DATE=SYSDATE TEST_FPDV_TIMESTAMP=SYSTIMESTAMP TEST_FPDV_CHAR='1' TEST_FPDV_VARCHAR2='1' TEST_FPDV_NVARCHAR2='1' TEST_FPDV_NCHAR='1' TEST_FPDV_RAW='1' | TEST_FPDV_TINYINT=1 TEST_FPDV_SMALLINT=1 TEST_FPDV_INT=1 TEST_FPDV_BIGINT=1 TEST_FPDV_NUMERIC=1 TEST_FPDV_FLOAT=1 TEST_FPDV_REAL=1 TEST_FPDV_SMALLMONEY=1 TEST_FPDV_MONEY=1 TEST_FPDV_SMALLDATETIME=getdate() TEST_FPDV_DATETIME=getdate() TEST_FPDV_CHAR='1' TEST_FPDV_VARCHAR='1' TEST_FPDV_NVARCHAR='1' TEST_FPDV_NCHAR='1' TEST_FPDV_BINARY='1' TEST_FPDV_VARBINARY='1' TEST_FPDV_BIT=1 | TEST_FPDV_TINYINT=1 TEST_FPDV_SMALLINT=1 TEST_FPDV_INT=1 TEST_FPDV_BIGINT=1 TEST_FPDV_NUMERIC=1 TEST_FPDV_FLOAT=1 TEST_FPDV_REAL=1 TEST_FPDV_SMALLMONEY=1 TEST_FPDV_MONEY=1 TEST_FPDV_SMALLDATETIME=getdate() TEST_FPDV_DATETIME=getdate() TEST_FPDV_CHAR='1' TEST_FPDV_VARCHAR='1' TEST_FPDV_NVARCHAR='1' TEST_FPDV_NCHAR='1' TEST_FPDV_BINARY='1' TEST_FPDV_VARBINARY='1' TEST_FPDV_BIT=1 |
If you will face with some difficulties or have any other questions, please contact our support team: support@ispirer.com