Differences

This shows you the differences between two versions of the page.

Link to this comparison view

knowledge-base:database-migration:ispirer-toolkit-faq:sql-code-testing [2018/05/24 12:27]
 knowledge-base:database-migration:ispirer-toolkit-faq:sql-code-testing [2023/11/27 13:29] (current)
Line 1: Line 1:
 +\\
 +[[https://​www.ispirer.com|Ispirer Website]]
 +[[https://​www.ispirer.com/​products/​migration-toolkit|Ispirer Toolkit Overview]]
 +[[https://​www.ispirer.com/​download/​download-demo|Free Trial]]
 +====== How to Test Converted SQL Code ======
 +
 +This article describes SQLWays testing options, their usage and output generated. ​
 +
 +The databases supported are: Sybase Adaptive Server Enterprise, Oracle and Microsoft SQL Server.
 +
 +===== Option TEST =====
 +
 +There are two ways of specifying the option TEST when running SQLWays:
 +  - /TEST - when running from command-line;​
 +  - TEST=Yes - in the **[COMMON]** section of the [[guides:​migration-toolkit:​command-line:​sqlways-ini|sqlways.ini]] or sqlways_wzd.ini.
 +
 +Option TEST is used to generate the calling statements for procedures or functions converted either from scripts or from the database. If this option is set the conversion is not performed, 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 the conversion to Oracle is 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 is 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 is additionally specified in **[COMMON]** section [[guides:​migration-toolkit:​command-line:​sqlways-ini|sqlways.ini]] or sqlways_wzd.ini.
 +
 +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 the 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, there is generated a set of calling statments based on the data extracted from the source database. 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 generated three sections of calling statments, 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 values are added as the 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 at [[[email protected]]]