Usage examples of the sqlways.ini options

About Ispirer Systems

Ispirer Home Page Database Migration Application Conversion Downloads

The purpose of this article is to demonstrate how the DISABLE_CONSTRAINTS option involves on conversion results.
Here below you may see the sample of the source code of the table with Primary key and Foreign Key constraints in the block 1, the converted code without the option in the block 2 and the converted code with option set.

 CREATE TABLE MyTable1 
 (
 Column1 varchar(10) NOT NULL, 
 Column2 integer,
 Column3 varchar(32),
 Column4 int,
 FOREIGN KEY (Column2, Column3) 
 REFERENCES MyTable2 (Column2, Column3)
 )
 UNIQUE PRIMARY INDEX pk01 (Column1)
 ;

smth text

(without option DISABLE_CONSTRAINTS or with option DISABLE_CONSTRAINTS=No/Empty value)

 CREATE TABLE MyTable1
 (    
 Column1 VARCHAR(10) NOT NULL,    
 Column2 INT,    
 Column3 VARCHAR(32),    
 Column4 VARCHAR(10),    
 CONSTRAINT fk_MyTable1 FOREIGN KEY(Column2,Column3) 
 REFERENCES MyTable2(Column2,Column3) ENABLED,    
 constraint pk01 PRIMARY KEY(Column1) ENABLED 
 );

 CREATE TABLE MyTable1
 (    
 Column1 VARCHAR(10) NOT NULL,    
 Column2 INT,    
 Column3 VARCHAR(32),    
 Column4 VARCHAR(10),    
 CONSTRAINT fk_MyTable1 FOREIGN KEY(Column2,Column3) 
 REFERENCES MyTable2(Column2,Column3) DISABLED,    
 constraint pk01 PRIMARY KEY(Column1) DISABLED 
 );

As you can see when the DISABLE_CONSTRAINTS option is not set, the ENABLED keyword is added by default. The same will happen if the option is set to “No” or the value is not specified. If the option is set to “Yes”, then the DISABLED keyword will be added to the constraints.

The purpose of this article is to demonstrate how the option CONVERT_SP_WITH_OUTPUT_PARAMS involves on conversion results.
Here below you may see the sample of the source code of the procedure in the block 1, the converted code without the option in the 2nd block and the converted code with option set.
It should also be taken into account that the option PROCEDURE_WITH_OUTPUT_PARAMS=No in both cases.

 CREATE PROCEDURE out_proc()
    RETURNING
       INTEGER AS col1, INTEGER AS col2, integer as col3;
 DEFINE v01         INTEGER;
 DEFINE v03         INTEGER;
 let v01 = 0;
 let v03 = 0;
 insert into tab1 values(v01, v03,v03);
 SELECT sum(y1), max(y2) INTO v01, v03 FROM tab1;
 RETURN v01,0,v03;
 END PROCEDURE;

 CREATE PROCEDURE dbo.out_proc AS
 BEGIN
    declare @v01 INT
    declare @v03 INT
    DECLARE @SWF_FLAG1 INT
    IF OBJECT_ID(N'tempdb..#swtmp_out_proc') IS NULL
    begin
       CREATE TABLE #swtmp_out_proc
       (
         col1 INT null,
         col2 INT null,
         col3 INT null,
         swc_identity INT identity
       )
       set @SWF_FLAG1 = 1
    end
    SET @v01 = 0
    SET @v03 = 0
    insert into dbo.tab1 values(@v01, @v03,@v03)
    SELECT @v01 = sum(y1), @v03 = max(y2) FROM dbo.tab1
    if @@ROWCOUNT = 0
       select @v01 = null, @v03 = null
    insert into #swtmp_out_proc
    select @v01 as col1,0 as col2,@v03 as col3
    if @SWF_FLAG1 = 1
       SELECT col1,col2,col3
       from #swtmp_out_proc  order by swc_identity
    RETURN
 END

 CREATE PROCEDURE dbo.out_proc @SWP_Ret_Value INT = NULL OUTPUT ,@SWP_Ret_Value1 INT = NULL OUTPUT ,@SWP_Ret_Value2 INT = NULL OUTPUT , @SWV_Output_Set BIT = 1
 AS
 BEGIN 
    declare @v01 INT
    declare @v03 INT
    SET @v01 = 0
    SET @v03 = 0
    insert into dbo.tab1 values(@v01, @v03,@v03) 
    SELECT @v01 = sum(y1), @v03 = max(y2) FROM dbo.tab1
    if @@ROWCOUNT = 0
       select @v01 = null, @v03 = null
    set @SWP_Ret_Value = @v01
    set @SWP_Ret_Value1 = 0
    set @SWP_Ret_Value2 = @v03
    if @SWV_Output_Set = 1
       select @v01 as col1,0 as col2,@v03 as col3
    RETURN
 END

The content of the swf_stored_procedures.txt:

 <Procedures>
       <Procedure Name="out_proc"/>
 </Procedures>

The purpose of this note is to demonstrate how the option PROCEDURE_WITH_OUTPUT_PARAMS changes conversion results.

Here below there are the sample of the source code in the block 1, the converted code with the option set to “Yes” in the block 2 and the converted code with option set to “No”.

 CREATE PROCEDURE colors_list()       
 RETURNING VARCHAR(30), CHAR(6)        
 RETURN 'Red', 'FF0000';   
 END PROCEDURE;

 CREATE PROCEDURE dbo.colors_list @SWP_Ret_Value VARCHAR(30) = NULL OUTPUT ,@SWP_Ret_Value1 CHAR(6) = NULL OUTPUT   
 AS
 BEGIN
    set @SWP_Ret_Value = 'Red'
    set @SWP_Ret_Value1 = 'FF0000'
    RETURN
 END

 CREATE PROCEDURE dbo.colors_list AS
 BEGIN
    DECLARE @SWF_FLAG1 INT
    IF OBJECT_ID(N'tempdb..#swtmp_colors_list') IS NULL
    begin
       CREATE TABLE #swtmp_colors_list
       (
          unnamed_col_1 VARCHAR(30) null,
          unnamed_col_2 CHAR(6) null,
          swc_identity INT identity
       )
       set @SWF_FLAG1 = 1
    end
    insert into #swtmp_colors_list
    select 'Red','FF0000'
    if @SWF_FLAG1 = 1
       SELECT unnamed_col_1,unnamed_col_2
       from #swtmp_colors_list  order by swc_identity
    RETURN
 END

The result values may be return from function with RETURN statement or from procedure with OUTPUT parameter, as displayed below:

 create function ret_one 
 RETURN NUMBER 
 as 
 begin
    return 1 ;
 end;

 CREATE function RET_ONE() 
 RETURNS FLOAT 
 AS 
 begin    
    return 1  
 end

 CREATE PROCEDURE RET_ONE @SWP_RET_VALUE FLOAT = NULL OUTPUT  
 AS 
 begin
    set @SWP_RET_VALUE = 1
    RETURN 
 end

The option may be set in GUI:

The result sets in PosgreSQL may be returned in the following ways: by using the table, the SETOF type and using the refcursor. The samples of the output with different options set displayed below:

 create table test_data(c1 int, c2 varchar(22))
 create procedure result_set_pr @p1 Date as
 select @p1 as c0, c1, c2 from test_data

Converted PostgreSQL code (RETURN_RESULT_FROM_SP_AND_FN=TABLE)

 CREATE OR REPLACE FUNCTION result_set_pr(v_p1 DATE)
 RETURNS table
 (
    c0 DATE,
    c1 INTEGER,
    c2 VARCHAR(22)
 ) LANGUAGE plpgsql
    AS $$
 BEGIN
    return query select v_p1 as c0, c1, c2 from test_data;
 END; $$;

Converted PostgreSQL code (RETURN_RESULT_FROM_SP_AND_FN=SETOF)

 CREATE TYPE result_set_pr_rs AS(c0 DATE, c1 INTEGER, c2 VARCHAR(22));
 CREATE OR REPLACE FUNCTION result_set_pr(v_p1 DATE)
 RETURNS SETOF result_set_pr_rs LANGUAGE plpgsql
    AS $$
 BEGIN
    return query select v_p1 as c0, c1, c2 from test_data;
 END; $$;

Converted PostgreSQL code (RETURN_RESULT_FROM_SP_AND_FN=REFCURSOR, by default)

 CREATE OR REPLACE PROCEDURE result_set_pr(v_p1 DATE, INOUT SWV_RefCur refcursor)
 LANGUAGE plpgsql
    AS $$
 BEGIN
    open SWV_RefCur for
    select v_p1 as c0, c1, c2 from test_data;
 END; $$;

PostgreSQL allows to use 2 ways of automatic integer numbers generation: by using the IDENTITY property or using the SERIAL pseudo-data type. The result of using the option with values No and Yes displayed in the table below. The data type of the source IDENTITY column will be converted into appropriate SERIAL column: SMALLINT will become SMALLSERIAL, INTEGER goes into SERIAL, and BIGINT into BIGSERIAL accordingly.

Source code (DB2 LUW)

 CREATE TABLE TABIDENTCOLUMN
 (
 ID INTEGER GENERATED ALWAYS AS IDENTITY
 (START WITH 1, INCREMENT BY 1)   NOT NULL,
 NAME CHAR(5)
 );

 CREATE TABLE TABIDENTCOLUMN_2
 (
 ID SMALLINT GENERATED ALWAYS AS IDENTITY
 (START WITH 3, INCREMENT BY 1)   NOT NULL,
 NAME CHAR(5)
 )

Converted PostgreSQL code (IDENTITY_TO_SERIAL=No)

 CREATE TABLE TABIDENTCOLUMN
 (
    ID  INTEGER GENERATED ALWAYS AS IDENTITY(START 1 INCREMENT 1)    NOT NULL,
    NAME  CHAR(5)
 );

 CREATE TABLE TABIDENTCOLUMN_2
 (
    ID  SMALLINT GENERATED ALWAYS AS IDENTITY(START 3 INCREMENT 1)    NOT NULL,
    NAME  CHAR(5)
 );

Converted PostgreSQL code (IDENTITY_TO_SERIAL=Yes)

 CREATE TABLE TABIDENTCOLUMN
 (
    ID  SERIAL,
    NAME  CHAR(5)
 );

 CREATE TABLE TABIDENTCOLUMN_2
 (
    ID SMALLSERIAL,
    NAME  CHAR(5)
 );
 ALTER SEQUENCE TABIDENTCOLUMN_2_ID_seq RESTART WITH 3 INCREMENT BY 1;

PostgreSQL does not support table types, and there are 2 ways to simulate Oracle logic.

By default, table types (TYPE IS TABLE, VARRAYS) are converted using Arrays. Another way is to convert table types into arrays in PostgreSQL(using temporary tables) as it displayed in column 2 of the table.

Source code (Oracle)

 CREATE TYPE employee AS OBJECT (
    id NUMBER,
    Name VARCHAR(300)
 );
 CREATE TYPE employees_tab IS TABLE OF employee;
 
 CREATE OR REPLACE PROCEDURE hire(EMPLOYEES in out employees_tab,
 id NUMBER, Name VARCHAR) AS
 NEW_EMPLOYEES employees_tab := employees_tab();
 BEGIN
    EMPLOYEES.Extend(1);
    EMPLOYEES(EMPLOYEES.count) := employee(id, Name);
    FOR i IN EMPLOYEES.first..EMPLOYEES.last
    LOOP
       INSERT INTO emp_tab values (EMPLOYEES(i).id, EMPLOYEES(i).Name);
    END LOOP;
    INSERT INTO EMP_TAB SELECT * FROM TABLE(EMPLOYEES);
    NEW_EMPLOYEES.Extend(EMPLOYEES.count);
    NEW_EMPLOYEES.Delete;
 END;

Converted PostgreSQL code (TABLE_TYPE_CONVERSION=Tables)

 CREATE TYPE EMPLOYEE AS(id DOUBLE PRECISION,Name VARCHAR(300));
 -- CREATE TYPE EMPLOYEES_TAB IS TABLE OF employee;
 CREATE OR REPLACE PROCEDURE HIRE(id DOUBLE PRECISION,Name VARCHAR(4000))
 LANGUAGE plpgsql
    AS $$
 BEGIN
    create temporary table if not exists SWT_HIRE_NEW_EMPLOYEES
    (
       SWC_INDEX INTEGER NOT NULL,
       ID DOUBLE PRECISION,
       NAME VARCHAR(300)
    );
    DELETE FROM SWT_HIRE_NEW_EMPLOYEES;
    FOR i IN COALESCE((SELECT MAX(SWT_HIRE_EMPLOYEES.SWC_INDEX) FROM SWT_HIRE_EMPLOYEES)+1,1) .. COALESCE((SELECT MAX(SWT_HIRE_EMPLOYEES.SWC_INDEX) FROM SWT_HIRE_EMPLOYEES),
    0)+1
    LOOP
       INSERT INTO SWT_HIRE_EMPLOYEES(SWC_Index) VALUES(i);
    END LOOP;
    IF NOT EXISTS(SELECT 1 FROM SWT_HIRE_EMPLOYEES WHERE SWT_HIRE_EMPLOYEES.SWC_INDEX =(SELECT COUNT(*) FROM SWT_HIRE_EMPLOYEES)) then
       INSERT INTO SWT_HIRE_EMPLOYEES   VALUES((SELECT COUNT(*) FROM SWT_HIRE_EMPLOYEES),NULL);
    END IF;
    UPDATE SWT_HIRE_EMPLOYEES SET id = id,Name = Name
    WHERE SWT_HIRE_EMPLOYEES.SWC_INDEX =(SELECT COUNT(*) FROM SWT_HIRE_EMPLOYEES);
    FOR i IN(SELECT MIN(SWC_INDEX) FROM SWT_HIRE_EMPLOYEES) ..(SELECT MAX(SWC_INDEX) FROM SWT_HIRE_EMPLOYEES)
    LOOP
       INSERT INTO EMP_TAB  values((SELECT SWT_HIRE_EMPLOYEES.ID FROM SWT_HIRE_EMPLOYEES WHERE SWC_INDEX = i), (SELECT
    SWT_HIRE_EMPLOYEES.NAME FROM SWT_HIRE_EMPLOYEES WHERE SWC_INDEX = i));
    END LOOP;
    INSERT INTO EMP_TAB  SELECT * FROM EMPLOYEES;
    FOR i IN COALESCE((SELECT MAX(SWT_HIRE_NEW_EMPLOYEES.SWC_INDEX) FROM SWT_HIRE_NEW_EMPLOYEES)+1,1) .. COALESCE((SELECT
    MAX(SWT_HIRE_NEW_EMPLOYEES.SWC_INDEX) FROM SWT_HIRE_NEW_EMPLOYEES),0)+(SELECT COUNT(*) FROM SWT_HIRE_EMPLOYEES)
    LOOP
       INSERT INTO SWT_HIRE_NEW_EMPLOYEES(SWC_Index) VALUES(i);
    END LOOP;
    DELETE FROM SWT_HIRE_NEW_EMPLOYEES;
 END; $$;

Converted PostgreSQL code (TABLE_TYPE_CONVERSION=Arrays, by default)

 CREATE TYPE EMPLOYEE AS(id DOUBLE PRECISION,Name VARCHAR(300));
 -- CREATE TYPE EMPLOYEES_TAB IS TABLE OF employee;
 CREATE OR REPLACE PROCEDURE HIRE(INOUT EMPLOYEES EMPLOYEE[] ,
 ID DOUBLE PRECISION, NAME VARCHAR(4000))
 LANGUAGE plpgsql
    AS $$
    DECLARE
    EMPLOYEES_REC  EMPLOYEE;
    NEW_EMPLOYEES  EMPLOYEE[] default array[]::EMPLOYEE[] ;
 BEGIN
    EMPLOYEES[swf_array_length(EMPLOYEES)+1] := null;
    EMPLOYEES[swf_array_length(EMPLOYEES)] := row(ID,NAME);
    FOR i IN array_lower(EMPLOYEES,1) .. array_upper(EMPLOYEES,1)
    LOOP
       EMPLOYEES_REC := EMPLOYEES[i];
       INSERT INTO EMP_TAB  values(EMPLOYEES_REC.ID, EMPLOYEES_REC.NAME);
       EMPLOYEES[i] := EMPLOYEES_REC;
    END LOOP;
    INSERT INTO EMP_TAB  SELECT * FROM unnest(EMPLOYEES);
    for i in 1 .. swf_array_length(EMPLOYEES) loop
       NEW_EMPLOYEES[swf_array_length(NEW_EMPLOYEES)+1] := null;
    end loop;
    NEW_EMPLOYEES := array[]:: EMPLOYEE[];
 END; $$;

By default, the routine that returns the resultset will be converted into procedure with additional out parameters (column 2). In case of option value CONVERT_ROUTINE_TO_SP_RESULTSET=Yes, the result will be returned by the SELECT statement.

 CREATE PROCEDURE sp_call_sp_with_param2(p1 char(10), p2 integer)
 RETURNING INTEGER,CHAR,INTEGER;
 DEFINE v1 INTEGER;
 DEFINE v2 CHAR(2);
 DEFINE v3 INTEGER;
 select col1, col2, col3 INTO v1,v2,v3 from tab1;
    RETURN v1,v2,v3;
 END PROCEDURE;

 CREATE PROCEDURE dbo.sp_call_sp_with_param2 @p1 CHAR(10), @p2 INT,@SWP_Ret_Value INT = NULL OUTPUT ,@SWP_Ret_Value1 CHAR = NULL OUTPUT ,@SWP_Ret_Value2 INT = NULL OUTPUT 
 AS
 BEGIN
    declare @j_qte INT
    declare @v1 INT
    declare @v2 CHAR(2)
    declare @v3 INT
    select @v1 = col1, @v2 = col2, @v3 = col3 from dbo.tab1
    if @@ROWCOUNT = 0
       select @v1 = null, @v2 = null, @v3 = null
    set @SWP_Ret_Value = @v1
    set @SWP_Ret_Value1 = @v2
    set @SWP_Ret_Value2 = @v3
    RETURN
 END

 CREATE PROCEDURE dbo.sp_call_sp_with_param2 @p1 CHAR(10), @p2 INT 
 AS
 BEGIN
    declare @v1 INT
    declare @v2 CHAR(2)
    declare @v3 INT
    select @v1 = col1, @v2 = col2, @v3 = col3 from dbo.tab1
    if @@ROWCOUNT = 0
       select @v1 = null, @v2 = null, @v3 = null
    select @v1, @v2, @v3
    RETURN
 END

Usage example for different values:

 create table nihon.tabchar(col1 char(1 char), col2 char(2 char), 
 col3 char(6), col4 char(12), col5 numeric)

Converted code (PostgreSQL)(ORACLE_GET_CHAR_COLUMN_SIZE=CHAR/Empty, by default)

 CREATE TABLE NIHON.TABCHAR
 (
    COL1 CHAR(2),
    COL2 CHAR(4),
    COL3 CHAR(6),
    COL4 CHAR(12),
    COL5 DECIMAL(38,0)
 );

Converted code (PostgreSQL)(ORACLE_GET_CHAR_COLUMN_SIZE=BYTE)

 CREATE TABLE NIHON.TABCHAR
 (
    COL1 CHAR(1),
    COL2 CHAR(2),
    COL3 CHAR(6),
    COL4 CHAR(12),
    COL5 DECIMAL(38,0)
 );

There are 3 views, 3 functions and 3 procedures in this example. Different pieces of each object kind include one of 3 tables: tab9 from default schema and tab9, tab10 from “db” schema.

 EMPTY_SCHEMA =
 OUTSCHEMA = schemaname2

If FULLY_QUALIFY_IDENTIFIERS=Yes, then each object has schemaname2 near its name (because schemaname2 was specified in the OUTSCHEMA).

If FULLY_QUALIFY_IDENTIFIERS=No, then parent objects (in this case it's views, functions, procedures) have schemaname2 near its name (because schemaname2 was specified in the OUTSCHEMA). But child objects (in this case tables) have same schema names as in the source code.

Usage example for different values:

Source code (Oracle)

 create or replace view viewfqi1 as
 select * from tab9
 
 create or replace view viewfqi2 as
 select * from db.tab9
 
 create or replace view viewfqi3 as
 select * from db.tab10

Converted code (PostgreSQL) (FULLY_QUALIFY_IDENTIFIERS=Yes, by default)	Converted code (PostgreSQL) (FULLY_QUALIFY_IDENTIFIERS=No)
CREATE OR REPLACE VIEW schemaname2.VIEWFQI1(COL1,COL2,COL3,COL4,COL5,COL6) AS select COL1,COL2,COL3,COL4,COL5,COL6 from schemaname2.TAB92; CREATE OR REPLACE VIEW schemaname2.VIEWFQI2(COL1,COL2,COL3,COL4,COL5,COL6) AS select COL1,COL2,COL3,COL4,COL5,COL6 from schemaname2.TAB9; CREATE OR REPLACE VIEW schemaname2.VIEWFQI3(COL1,COL2,COL3,COL4,COL5,COL6) AS select COL1,COL2,COL3,COL4,COL5,COL6 from schemaname2.TAB10;	CREATE OR REPLACE VIEW schemaname2.VIEWFQI1(COL1,COL2,COL3,COL4,COL5,COL6) AS select COL1,COL2,COL3,COL4,COL5,COL6 from tab9; CREATE OR REPLACE VIEW schemaname2.VIEWFQI2(COL1,COL2,COL3,COL4,COL5,COL6) AS select COL1,COL2,COL3,COL4,COL5,COL6 from db.tab9; CREATE OR REPLACE VIEW schemaname2.VIEWFQI3(COL1,COL2,COL3,COL4,COL5,COL6) AS select COL1,COL2,COL3,COL4,COL5,COL6 from db.tab10;

Source code (Oracle)

 create or replace FUNCTION funcfqi1 (p1 in number)
 RETURN number
 IS
 x number;
 BEGIN 
    select col1 into x from tab9;
    return 1;
 end;
 
 create or replace FUNCTION funcfqi2 (p1 in number)
 RETURN number
 IS
 x number;
 BEGIN 
    select col1 into x from db.tab9;
    return 1;
 end;
 
 create or replace FUNCTION funcfqi3 (p1 in number)
 RETURN number
 IS
 x number;
 BEGIN 
    select col1 into x from db.tab10;
    return 1;
 end;

Converted code (PostgreSQL) (FULLY_QUALIFY_IDENTIFIERS=Yes, by default)

 CREATE OR REPLACE FUNCTION schemaname2.FUNCFQI1(in P1 DOUBLE PRECISION)
 RETURNS DOUBLE PRECISION LANGUAGE plpgsql
   AS $$ 
   DECLARE 
   X  DOUBLE PRECISION; 
 BEGIN 
   select COL1 into STRICT X from schemaname2.TAB92; 
   return 1; 
 END; $$; 
        
 CREATE OR REPLACE FUNCTION schemaname2.FUNCFQI2(in P1 DOUBLE PRECISION) 
 RETURNS DOUBLE PRECISION LANGUAGE plpgsql 
   AS $$ 
   DECLARE 
   X  DOUBLE PRECISION; 
 BEGIN 
   select COL1 into STRICT X from schemaname2.TAB9; 
   return 1; 
 END; $$; 
                        
 CREATE OR REPLACE FUNCTION schemaname2.FUNCFQI3(in P1 DOUBLE PRECISION) 
 RETURNS DOUBLE PRECISION LANGUAGE plpgsql 
   AS $$ 
   DECLARE 
   X  DOUBLE PRECISION; 
 BEGIN 
   select COL1 into STRICT X from schemaname2.TAB10; 
   return 1; 
 END; $$;

Converted code (PostgreSQL) (FULLY_QUALIFY_IDENTIFIERS=No)

 CREATE OR REPLACE FUNCTION schemaname2.FUNCFQI1(in P1 DOUBLE PRECISION)
 RETURNS DOUBLE PRECISION LANGUAGE plpgsql
   AS $$ 
   DECLARE 
   X  DOUBLE PRECISION; 
 BEGIN 
   select COL1 into STRICT X from tab9; 
   return 1; 
 END; $$; 
        
 CREATE OR REPLACE FUNCTION schemaname2.FUNCFQI2(in P1 DOUBLE PRECISION) 
 RETURNS DOUBLE PRECISION LANGUAGE plpgsql 
   AS $$ 
   DECLARE 
   X  DOUBLE PRECISION; 
 BEGIN 
   select COL1 into STRICT X from db.tab9; 
   return 1; 
 END; $$; 
                        
 CREATE OR REPLACE FUNCTION schemaname2.FUNCFQI3(in P1 DOUBLE PRECISION) 
 RETURNS DOUBLE PRECISION LANGUAGE plpgsql 
   AS $$ 
   DECLARE 
   X  DOUBLE PRECISION; 
 BEGIN 
   select COL1 into STRICT X from db.tab10; 
   return 1; 
 END; $$;

Source code (Oracle)

create or replace procedure procfqi1(p1 IN number) is
begin
insert into tab9(col1, col2, col3, col4) values (p1, p1, p1, p1);
end;

create or replace procedure procfqi2(p1 IN number) is
begin
insert into db.tab9(col1, col2, col3, col4) values (p1, p1, p1, p1);
end;

create or replace procedure procfqi3(p1 IN number) is
begin
insert into db.tab10(col1, col2, col3, col4) values (p1, p1, p1, p1);
end;

Converted code (PostgreSQL) (FULLY_QUALIFY_IDENTIFIERS=Yes, by default)

CREATE OR REPLACE FUNCTION schemaname2.PROCFQI1(IN P1 DOUBLE PRECISION)
RETURNS VOID LANGUAGE plpgsql
  AS $$
BEGIN
  insert into schemaname2.TAB92(COL1, COL2, COL3, COL4) values(P1, P1, P1, P1);
RETURN;
END; $$;
  
CREATE OR REPLACE FUNCTION schemaname2.PROCFQI2(IN P1 DOUBLE PRECISION)
RETURNS VOID LANGUAGE plpgsql
  AS $$
BEGIN
  insert into schemaname2.TAB9(COL1, COL2, COL3, COL4) values(P1, P1, P1, P1);
RETURN;
END; $$;

CREATE OR REPLACE FUNCTION schemaname2.PROCFQI3(IN P1 DOUBLE PRECISION)
RETURNS VOID LANGUAGE plpgsql
  AS $$
BEGIN
  insert into schemaname2.TAB10(COL1, COL2, COL3, COL4) values(P1, P1, P1, P1);
RETURN;
END; $$;

Converted code (PostgreSQL) (FULLY_QUALIFY_IDENTIFIERS=No)

CREATE OR REPLACE FUNCTION schemaname2.PROCFQI1(IN P1 DOUBLE PRECISION)
RETURNS VOID LANGUAGE plpgsql
  AS $$
BEGIN
  insert into tab9(COL1, COL2, COL3, COL4) values(P1, P1, P1, P1);
RETURN;
END; $$;

CREATE OR REPLACE FUNCTION schemaname2.PROCFQI2(IN P1 DOUBLE PRECISION)
RETURNS VOID LANGUAGE plpgsql
  AS $$
BEGIN
  insert into db.tab9(COL1, COL2, COL3, COL4) values(P1, P1, P1, P1);
RETURN;
END; $$;

CREATE OR REPLACE FUNCTION schemaname2.PROCFQI3(IN P1 DOUBLE PRECISION)
RETURNS VOID LANGUAGE plpgsql
  AS $$
BEGIN
  insert into db.tab10(COL1, COL2, COL3, COL4) values(P1, P1, P1, P1);
RETURN;
END; $$;

In PostgreSQL it is allowed to specify different clauses for IDENTITY column. In order to use the required one, IDENTITY_COLUMN_TYPE option may be set.

Here below it is demonstrated how the option IDENTITY_COLUMN_TYPE impact the conversion result: there are the sample of the source code in the column 1, the converted code with the option set to “Always” (or with an empty option) in column 2 and the converted code with option set to “Default” in the last column.

Source code (Microsoft SQL Server)

CREATE TABLE ident_table_pg 
(
c1 INT IDENTITY, 
c2 VARCHAR(22)
);

Converted PostgreSQL code (with option IDENTITY_COLUMN_TYPE=ALWAYS, by default)

CREATE TABLE ident_table_pg
(
  c1 INTEGER GENERATED ALWAYS AS IDENTITY(START 1 INCREMENT 1)   NOT NULL,
  c2 VARCHAR(22) 
);

Converted PostgreSQL code (with option IDENTITY_COLUMN_TYPE=DEFAULT)

CREATE TABLE ident_table_pg 
( 
c1 INTEGER GENERATED BY DEFAULT AS IDENTITY(START 1 INCREMENT 1) NOT NULL, 
c2 VARCHAR(22) 
);

In order to avoid trigger execution that will fire the same or other triggers endless times, in other words the trigger recursion, the appropriate WHEN statement may be add to check the trigger nesting level.

The following example shows the triggers conversion results depend on option TRIGGER_RECURSION_LVL values: there are the sample of the trigger source code in the column 1, the converted trigger function and trigger code when the option value is not set in column 2 and the converted code with option set to TRIGGER_RECURSION_LVL=0 in column 3. The trigger in column 2 will cause infinite its execution that finally ends up with the error “ERROR: stack depth limit exceeded”. To resolve it, the check need to be add by the option -it may be done by specifying the option like it is demonstrated in column 3.

Source code (Microsoft SQL Server)

CREATE TRIGGER trigOnTab1 
ON tab1 
AFTER UPDATE 
AS 
BEGIN
  SET NOCOUNT ON
  update tab1
          set c2 = UPPER(i.c2)
          from tab1 t
          inner join inserted i on i.c1 = t.c1 
END

Converted PostgreSQL code (with option TRIGGER_RECURSION_LVL= , by default)

CREATE OR REPLACE FUNCTION trigOnTab1_TrFunc() 
RETURNS TRIGGER LANGUAGE plpgsql
  AS $$
BEGIN
  BEGIN
  update tab1 t
  set c2 = UPPER(i.c2)
  from new_table i WHERE i.c1 = t.c1;
  END;
  RETURN NULL; 
END; $$; 

DROP TRIGGER IF EXISTS trigOnTab1 ON tab1;
CREATE TRIGGER trigOnTab1
AFTER UPDATE 
ON  tab1 
REFERENCING NEW TABLE AS new_table 
FOR STATEMENT 
EXECUTE PROCEDURE trigOnTab1_TrFunc();

Converted PostgreSQL code (with option TRIGGER_RECURSION_LVL=0)

CREATE OR REPLACE FUNCTION trigOnTab1_TrFunc() 
RETURNS TRIGGER LANGUAGE plpgsql
  AS $$ 
BEGIN
  BEGIN
  update tab1 t
  set c2 = UPPER(i.c2)
  from new_table i WHERE i.c1 = t.c1;
  END;
  RETURN NULL; 
END; $$; 

DROP TRIGGER IF EXISTS trigOnTab1 ON tab1; 
CREATE TRIGGER trigOnTab1 
AFTER UPDATE 
ON  tab1 
REFERENCING NEW TABLE AS new_table 
FOR STATEMENT
  WHEN (pg_trigger_depth() <1)
  EXECUTE PROCEDURE trigOnTab1_TrFunc();

By default, IspirerToolkit converts table-returning functions to pipelined ones. But starting with Oracle 21c we can use SQL_MACRO functionality.

Source code PostgreSQL

create function get_film(p_title character varying)
returns table(film_title character varying, film_release_year integer)
language plpgsql as $$
begin
    return query 
    select title, year
    from   film_table
    where  title = p_title;
end; $$

Converted Oracle code by default

create or replace type dt_get_film as object(film_title varchar2(32767),film_release_year number(10,0));
create or replace type get_film_set as table of dt_get_film;

create or replace function get_film(p_title in varchar2)
return
get_film_set pipelined
   as
begin
   for retrow in(select title as swa_al1, year as swa_al2
      from   film_table
      where  title = p_title) 
   loop
      pipe row(dt_get_film(retrow.swa_al1,retrow.swa_al2));
   end loop; 
end;

Converted Oracle code version 21+ and option RETURN_TABLE_SQL_MACRO=Yes

create or replace function get_film(p_title in varchar2)
return varchar2 sql_macro(table)
   as
begin
   return q'{
      select
      retrow.swa_al1 as film_title,
      retrow.swa_al2 as film_release_year
      from(select title as swa_al1, year as swa_al2
          from   film_table
          where  title = p_title)retrow
}'; 
end;

The example below shows the difference in the way the resulting procedure is returned in both cases.

Source code ASE

create procedure sp_tab_ret_stat as
declare @c_ch integer
begin
  select @c_ch=col2 from tab_ret_stat where col1=4
  IF ( @c_ch > 20 )
  BEGIN
	  SELECT 1
	  RETURN( -1 )
  END
  RETURN( 0 )
end

Converted MySQL code by default (option RETURN_STATUS_TO=INOUT)

DELIMITER //
DROP PROCEDURE IF EXISTS sp_tab_ret_stat;
//
create procedure sp_tab_ret_stat(INOUT SWP_Ret_Value INT)
   SWL_return:
BEGIN
   DECLARE v_c_ch INT;
   select   col2 INTO v_c_ch from tab_ret_stat where col1 = 4;
   IF (v_c_ch > 20) then
  
      SELECT 1;
      SET SWP_Ret_Value =(-1);
      LEAVE SWL_return;
   end if;
   SET SWP_Ret_Value =(0);
   LEAVE SWL_return;
END;
//
DELIMITER ;

Converted MySQL code and option RETURN_STATUS_TO=LOCAL

DELIMITER //
DROP PROCEDURE IF EXISTS sp_tab_ret_stat;
//
create procedure sp_tab_ret_stat()
   SWL_return:
BEGIN
   DECLARE v_c_ch INT;
   DECLARE SWP_Ret_Value INT;
   select   col2 INTO v_c_ch from tab_ret_stat where col1 = 4;
   IF (v_c_ch > 20) then
  
      SELECT 1;
      SET SWP_Ret_Value =(-1);
      LEAVE SWL_return;
   end if;
   SET SWP_Ret_Value =(0);
   LEAVE SWL_return;
END;
//
DELIMITER ;

The purpose of this article is to demonstrate how the IMPORT_CLIENT_OPTIONS option involves on conversion results. Here below you may see the sample of the command line of the table: Converted with empty value (IMPORT_CLIENT_OPTIONS=)

:create_table
echo Creating table using the MySQL command line utility"C:
Program Files
MySQL
MySQL Workbench 8.0 CE\mysql.exe" --host=localhost --user=root --password=root --comments --port=3306 --force -vvv test1 < 
test_add_opt_tab.sql >> test_add_opt.log
goto:eof

Converted with no empty value (IMPORT_CLIENT_OPTIONS=–init-command=“SET SESSION FOREIGN_KEY_CHECKS=0;”)

:create_table
echo Creating table using the MySQL command line utility"C:
Program Files
MySQL
MySQL Workbench 8.0 CE\mysql.exe" host=localhost --user=root --password=root --comments --port=3306 – -init-command="SET SESSION 
FOREIGN_KEY_CHECKS=0;"  -force -vvv test1 < test_add_opt_tab.sql >> test_add_opt.log
goto:eof

Source code Sybase ASE:

create view dbo.convert_dblink_to_schema as
select ccdesk..test_tab1.col1, ccdesk..test_tab1.col2, model..tab2.col3, model..tab2.col4
from ccdesk..test_tab1, model..tab2 where ccdesk..test_tab1.col2 = model..tab2.col3

Converted Sybase ASE code by default:

create or replace view convert_dblink_to_schema  as
select test_tab1.col1 as col1, test_tab1.col2 as col2, tab2.col3 as col3, tab2.col4 as col4
from test_tab1, tab2 where test_tab1.col2 = tab2.col3;

Converted Sybase ASE code + option CONVERT_DBLINK_TO_SCHEMA=Yes:

create or replace view convert_dblink_to_schema  as
select test_tab1.col1 as col1, test_tab1.col2 as col2, tab2.col3 as col3, tab2.col4 as col4
from ccdesk.test_tab1, model.tab2 where test_tab1.col2 = tab2.col3;

By default, IspirerToolkit converts all calls of procedures, functions to calls of Java methods. But the objects that are specified in CONVERT_EXCEPT option will be called directly from the database.

The example below shows the difference in conversion of calls of object by default and when they are specified in option.

Source code Oracle

  CREATE OR REPLACE PACKAGE BODY convertExceptOptionWithRegexp
AS
    PROCEDURE CRUDPROC1
    IS
      v1 number;
    BEGIN
    
        PKG_CRMNP_CRUD.CL_BAT;
        
        v1 := SP_CNR_GLPAM;
        
    END;
END;

Converted Java code by default

  public class Convertexceptoptionwithregexp {
   private PkgCrmnpCrud pkgCrmnpCrud;
   private SpCnrGlpam spCnrGlpam;
   public Convertexceptoptionwithregexp() throws Exception,SQLException {
         pkgCrmnpCrud = new PkgCrmnpCrud();
         spCnrGlpam = new SpCnrGlpam();
   }
   public void crudproc1() throws Exception {

         BigDecimal v1  = null;
    
         pkgCrmnpCrud.clBat();
         v1 = spCnrGlpam.spSpCnrGlpam();
   }
}

Converted Java code with option CONVERT_EXCEPT=(?i)([\w]+_CRUD\b), SP_CNR_GLPAM

public class Convertexceptoptionwithregexp {

    private static final org.slf4j.Logger LOGGER = org.slf4j.LoggerFactory.getLogger(Convertexceptoptionwithregexp.class);
    public void crudproc1() throws SQLException, Exception {
          Connection mConn = JDBCDataSource.getConnection();
          
          BigDecimal v1  = null;
          
          try(CallableStatement mStmt =  mConn.prepareCall("{call PKG_CRMNP_CRUD.CL_BAT()}");) {
          	mStmt.execute();
          	SQLCursorHandler.getInstance().setFound();
          }
          catch (SQLException se) {
          	SQLCursorHandler.getInstance().handleExeption(se);
          	throw se;
          }
          
          try(CallableStatement mStmt =  mConn.prepareCall("{? = call SP_CNR_GLPAM()}");) {
             mStmt.registerOutParameter(1, Types.DECIMAL);
             mStmt.execute();
             v1 = mStmt.getBigDecimal(1);
             SQLCursorHandler.getInstance().setFound();
          }
          catch(SQLException se) {
             SQLCursorHandler.getInstance().handleExeption(se);
             throw se;
          }
          
          if (!mConn.getAutoCommit()) {
          	mConn.commit();
          }
    }
}

Source code (Informix 4GL)

function window_style()
define xfontsize char(50)

display xfontsize

end function;

Converted Informix 4GL code (BL_ONLY=No)

static public void window_style()
{
	string xfontsize = string.Empty;
	GUIHelpers.DisplayString(xfontsize);
}

Converted Informix 4GL code (BL_ONLY=Yes)

public void window_style()
{
      string xfontsize = string.Empty;
	Stubs.display(xfontsize);
}

By default, Ispirer Toolkit converts string concatenation as is. But in order to reproduce the same behavior in the case of null strings in Oracle and PostgreSQL, we can add a check for the presence of nulls among the concatinating strings. And if there are null values, the resulting string becomes null.

The example below shows the difference in the way the resulting string is obtained in both cases. Source code PostgreSQL

create or replace procedure sp_concat_nulls()
LANGUAGE plpgsql
AS $$
DECLARE
   v_ret  varchar(20) = null;
   v_str_null  varchar(20);
   v_str1  varchar(20) = '1';
BEGIN
   v_ret := v_str_null || '' || v_str1;
   raise notice 'v_ret = %', v_ret;  
END; $$
-- output
v_ret = <NULL>

Converted Oracle code by default

  create or replace procedure sp_concat_nulls as
   v_ret  varchar2(20) := null;
   v_str_null  varchar2(20);
   v_str1 varchar2(20) := '1';
BEGIN
   v_ret := v_str_null || '' || v_str1;
   DBMS_OUTPUT.PUT_LINE('v_ret = ' || v_ret);  
END;
-- output
v_ret = 1

Converted Oracle code + option CONCAT_NULL_STRINGS_AS_NULL=Yes

create or replace procedure sp_concat_nulls as
   v_ret  varchar2(20) := null;
   v_str_null  varchar2(20);
   v_str1 varchar2(20) := '1';
BEGIN
   v_ret := case when greatest(v_str_null,v_str1) is null then null else v_str_null || '' || v_str1 end;
   DBMS_OUTPUT.PUT_LINE('v_ret = ' || v_ret);  
END;
-- output
v_ret = <NULL>

Source code Sybase ASE

create procedure tempdb_tables_to_temporary_tables
as 
create table tempdb..test_tab1(col1 int, col2 int)
insert into tempdb..test_tab1 values(1,2)
drop table tempdb..test_tab1

Converted code to PostgreSQL by default

  create or replace procedure tempdb_tables_to_temporary_tables()
LANGUAGE plpgsql
   AS $$
BEGIN
   drop table IF EXISTS test_tab1 CASCADE;
   create table test_tab1
   (
      col1 INTEGER, 
      col2 INTEGER
   );
   insert into test_tab1  values(1,2);   drop table IF EXISTS test_tab1 CASCADE;
END; $$;

Converted code to PostgreSQL + option TEMPDB_TABLES_TO_TEMPORARY_TABLES=Yes

  create or replace procedure tempdb_tables_to_temporary_tables()
LANGUAGE plpgsql
   AS $$
BEGIN
   drop table IF EXISTS test_tab1 CASCADE;
   create TEMPORARY table test_tab1
   (
      col1 INTEGER, 
      col2 INTEGER
   );
   insert into test_tab1  values(1,2);   drop table IF EXISTS test_tab1 CASCADE;
END; $$;

Source code (Informix 4GL)

function test()

call gethcall()

end function;

Converted Informix 4GL code (DESIGN_PATTERN=Empty)

static public void test()
  {
  	  Isp.gethcall();
  }

Converted Informix 4GL code (DESIGN_PATTERN=Swagger)

  [HttpGet("test")]
    [SwaggerOperation("Inst")]
  public void test()
  {
	  Isp.gethcall();
  }

Source code (PBScripts)

$PBExportHeader$w_test.srw
$PBExportComments$
forward
global type w_test from window
end type
end forward

global type w_test from window
integer x = 1083
integer y = 336
integer width = 1577
integer height = 896
end type
global w_test w_test

Converted PBScripts code (DESIGN_PATTERN=Empty)

public partial class w_test : WindowBase
{
	#region Singleton

	private static w_test _instance;
	public static w_test Instance
	{
		get
		{
			if (_instance == null || ! _instance.IsLoaded)
			{
				_instance = new w_test();
			}

			return _instance;
		}
	}
	#endregion

	#region Constructors

	public w_test()
	{
		InitializeComponent();
		_instance = this;
	}
	#endregion

}

Converted PBScripts code (DESIGN_PATTERN=MVVM)

[HttpGet("test")]
  [SwaggerOperation("Inst")]
	public partial class w_test_srwViewModel : WindowBaseViewModel
{
	#region Singleton

	private static w_test_srwViewModel _instance;
	public static w_test_srwViewModel Instance
	{
		get
		{
			if (_instance == null || _instance.IsClosed)
			{
				_instance = new w_test_srwViewModel();
			}

			return _instance;
		}
	}
	#endregion

	#region Constructors

	public w_test_srwViewModel() : this(null)
	{
	}
	public w_test_srwViewModel(UIElementViewModel parent) : base(parent)
	{
		_instance = this;
		_instance.Parent = parent;
		Name = "w_test";
		Height = 896;
		Width = 1577;
		Margin = new ThicknessViewModel(1083, 336, 0, 0);
	}
	#endregion

	#region ViewModel

	#endregion

	#region Classes
	#endregion

}

To use the information about the structure of tables for a more correct migration, you can save the tables ddl to a file and then use it as if it were a migration with a connection. The example below shows the difference in the result when the table structure is taken into account and when it is not.

Let's say we have such a table and a query in Oracle. Table:

create table table1 (id int, str varchar(20), dt DATE);
insert into table1 values (2, '10', current_date);

Query:

select
   case when id=str then 0 else 1 end, 
   dt+1 
from table1;

If we convert this query to PostgreSQL without having information about the structure of the table, it will remain the same. But when we run the query, we will get errors: 1) ERROR: operator does not exist: integer = character varying 2) ERROR: operator does not exist: timestamp without time zone + integer

To avoid such errors, we have to consider all types of columns. After adding the path to the file with the table ddl, we will get the following result:

Query:

select 
   case when id = cast(NULLIF(str,'') as INTEGER) then 0 else 1 end, 
   (dt+INTERVAL '1 day'):: date 
from table1;

As you can see, in this case, no errors will be issued, and the result of select will be correct. Source code Oracle

select
   case when id=str 
      then 0 else 1 end, 
   dt+1 
from table1;

Converted PostgreSQL code by default

select
   case when id=str 
      then 0 else 1 end, 
   dt+1 
from table1;

Converted PostgreSQL code + path to tables ddl

select 
   case when id = cast(NULLIF(str,'') as INTEGER) 
      then 0 else 1 end, 
   (dt+INTERVAL '1 day'):: date 
from table1;

If this option does not work as expected, please contact our technical team: [email protected]

By default, Ispirer Toolkit adds PRAGMA AUTONOMOUS_TRANSACTION if the function contains DML operations.

The example below shows the difference in the resulting finction in both cases. Please compare: PostgreSQL source

CREATE OR REPLACE FUNCTION fn_1() 
RETURNS text LANGUAGE plpgsql AS $$
DECLARE
   ret_val text;
BEGIN
   ret_val := 'Ok';
   insert into tab2 (a) VALUES (10);
   RETURN ret_val;
END;$$;

Oracle AUTONOMOUS_TRANS=No

  CREATE OR REPLACE FUNCTION fn_1 RETURN VARCHAR2 as
   ret_val  varchar2(4000);
BEGIN
   ret_val := 'Ok';
   insert into tab2(a) VALUES(10);
   RETURN ret_val;
END;

Oracle AUTONOMOUS_TRANS=Yes

CREATE OR REPLACE FUNCTION fn_1 RETURN VARCHAR2 as
   PRAGMA AUTONOMOUS_TRANSACTION;
   ret_val  varchar2(4000);
BEGIN
   ret_val := 'Ok';
   insert into tab2(a) VALUES(10);
   commit;
   RETURN ret_val;
END;

If this option does not work as expected, please contact our technical team: support@ispirer.com

The example below shows the difference in the resulting procedure in both cases. Please compare:

Oracle source

PostgreSQL
AUTONOMOUS_TRANSACTION_TO_DBLINK=No

PostgreSQL
AUTONOMOUS_TRANSACTION_TO_DBLINK=Yes

CREATE PROCEDURE         AUTO_TEST
 (id_value number, text_value varchar2)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   INSERT INTO DSSUSER.AUTONOMOUS_EVENT (id, value)
   VALUES(id_value, text_value);
   COMMIT;
END AUTO_TEST;

CREATE OR REPLACE PROCEDURE         AUTO_TEST(id_value DOUBLE PRECISION, text_value VARCHAR)
LANGUAGE plpgsql
   AS $$
BEGIN
   INSERT INTO AUTONOMOUS_EVENT(ID, VALUE)
   VALUES(id_value, text_value);
      COMMIT;
END; $$;

CREATE OR REPLACE PROCEDURE         AUTO_TEST(id_value DOUBLE PRECISION, text_value VARCHAR, IN is_recursive boolean DEFAULT false)
LANGUAGE plpgsql
   AS $$
   DECLARE
v_sql text;
BEGIN

  IF is_recursive = FALSE THEN  
      begin
       IF NOT EXISTS (SELECT 1 FROM dblink_get_connections() 
       WHERE dblink_get_connections@>'{myconn}') THEN
         PERFORM dblink_connect('myconn', 'SWL_j2p9ecom_link');
       END IF;
          
      v_sql := format('CALL dssuser.AUTO_TEST( id_value => %L, text_value => %L, is_recursive => TRUE)', id_value, text_value);
      
      PERFORM dblink_exec( 'myconn',  v_sql);
      
      end;
    ELSE 
    --procedure body
      insert into dssuser.autonomous_event values (id_value, text_value);
      commit;
    END IF;
END; $$;

If this option does not work as expected, please contact our technical team: support@ispirer.com

Source code Pascal

Converted Pascal code by default

Converted Pascal code + option MISSEDLOGIC=Yes

unit test1;

interface

implementation

procedure test();
var 
par3 : integer;

begin
par3 := sum_proc2();
end;

end.

public partial class test1Unit
	{
		public static void test()
		{
			int par3 = 0;
			par3 = sum_proc2();
		}
	}

public partial class test1Unit
	{
		public static void test()
		{
			int par3 = 0;
			par3 = ((int) MissedLogic.sum_proc2());
		}
	} 
+ new generated file in GeneratedCode\MissedLogic.cs:
namespace Missed
{
	public class MissedLogic
	{
		public static object sum_proc2()
		{
			return null;
		}
	}
}

If this option does not work as expected, please contact our technical team: support@ispirer.com

Please note: the option works only for the Oracle, SQL Server, Sybase ASE and Sybase ASA sources!

By default, Ispirer Toolkit uses the slf4j logging framework, but if the option is specified to log4j, the tool will use the log4j library.

The example below shows the difference between the default conversion and the conversion using the LOGGER=log4j option.

Source code Oracle

Converted Java code by default

Converted Java code + option LOGGER=log4j

CREATE PROCEDURE LOGGER_OPTION_EXAMPLE
AS
BEGIN
  INSERT INTO TAB_A VALUES (1, 'TEXT1');
END;

import java.sql.*;
import com.data.db.SQLCursorHandler;
import com.data.db.JDBCDataSource;
public class LoggerOptionExample {

	private static final org.slf4j.Logger LOGGER = org.slf4j.LoggerFactory.getLogger(LoggerOptionExample.class);
	public void spLoggerOptionExample() throws Exception {
		Connection mConn = JDBCDataSource.getConnection();

		try {
			try(PreparedStatement mStmt = mConn.prepareStatement("INSERT INTO TAB_A VALUES (1, 'TEXT1')");) {
				SQLCursorHandler.getInstance().setRowcount(mStmt.executeUpdate());
			}
			catch (SQLException se) {
				SQLCursorHandler.getInstance().handleExeption(se);
				throw se;
			}

			if (!mConn.getAutoCommit()) {
				mConn.commit();
			}
		}
		catch (Exception e) {
			LOGGER.error(String.valueOf(e));
			throw e;
		}
		finally {
			if (mConn != null) {
				mConn.close();
			}
		}
	}
}

import java.sql.*;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import com.data.db.SQLCursorHandler;
import com.data.db.JDBCDataSource;
public class LoggerOptionExample {

	private static final Logger LOGGER = LogManager.getLogger(LoggerOptionExample.class);
	public void spLoggerOptionExample() throws Exception {
		Connection mConn = JDBCDataSource.getConnection();

		try {
			try(PreparedStatement mStmt = mConn.prepareStatement("INSERT INTO TAB_A VALUES (1, 'TEXT1')");) {
				SQLCursorHandler.getInstance().setRowcount(mStmt.executeUpdate());
			}
			catch (SQLException se) {
				SQLCursorHandler.getInstance().handleExeption(se);
				throw se;
			}

			if (!mConn.getAutoCommit()) {
				mConn.commit();
			}
		}
		catch (Exception e) {
			LOGGER.error(String.valueOf(e));
			throw e;
		}
		finally {
			if (mConn != null) {
				mConn.close();
			}
		}
	}
}

If this option does not work as expected, please contact our technical team: support@ispirer.com

Please note: the option works only for the Oracle to Java, SQL Server to Java directions!

By default, Ispirer Toolkit creates object dependencies in the traditional way, but if you specify the Spring option, the tool will use the Spring DI container for dependency injection.

The example below shows the difference in object creation dependencies by default and using the Spring dependency injection option.

Source code Oracle

Converted Java code by default

Converted Java code with option DEPENDENCY_INJECTION=Spring

CREATE PACKAGE BODY PKG_WITH_DEPENDENCY
IS
    PROCEDURE SP
    IS
    BEGIN
       SP_TO_INJECT;
       PKG_TO_INJECT.SP_TO_INJECT;
    END;
END;

public class PkgWithDependency {

   private static final org.slf4j.Logger LOGGER = org.slf4j.LoggerFactory.getLogger(PkgWithDependency.class);
   private SpToInject spToInject;
   private PkgToInject pkgToInject;
   public PkgWithDependency() throws Exception,SQLException {
         spToInject = new SpToInject();
         pkgToInject = new PkgToInject();
   }
   public void sp() throws GeneralException {
      
      try {
         spToInject.spSpToInject();
         pkgToInject.spToInject();
      }
      catch(Exception e) {
         throw new GeneralException(e, LOGGER);
      }
   }
}

@Service
@RequestScope
public class PkgWithDependency implements IPkgWithDependency {

   private static final org.slf4j.Logger LOGGER = org.slf4j.LoggerFactory.getLogger(PkgWithDependency.class);
   @Autowired
   private ISpToInject spToInject;
   @Autowired
   private IPkgToInject pkgToInject;
   @Override
   public void sp() throws GeneralException {
      
      try {
         spToInject.spSpToInject();
         pkgToInject.spToInject();
      }
      catch(Exception e) {
         throw new GeneralException(e, LOGGER);
      }
   }
}

If this option does not work as expected, please contact our technical team: support@ispirer.com

Please note: the option works only for the Oracle to Java direction!

By default, Ispirer Toolkit specifies query lines directly in the code. With the EXTRACT_SQL=yes option, the tool extracts queries into XML files, one XML file per object, and retrieves the required query line from the XML file when it needs to be executed.

The example below shows the difference between the default conversion and the conversion using the EXTRACT_SQL=yes option.

Oracle source code

Converted Java code by default

Converted Java code with the EXTRACT_SQL=yes option

create procedure extract_sql_sample(id number)
as
  v1 number;
  v2 varchar2(50);
begin

  insert into tab1 values (1, 'text1');
  
  select col1, col2 into v1, v2 from tab1 where col1 = id;
end;

@Service
@Transactional
public class ExtractSqlSample implements IExtractSqlSample {

	@Autowired
	private JdbcTemplate jdbcTemplate;
	private static final org.slf4j.Logger LOGGER = org.slf4j.LoggerFactory.getLogger(ExtractSqlSample.class);
	private Integer errorCode = 0;
	private String sqlState = "";
	@Override
	public void spExtractSqlSample(BigDecimal id)
	throws GeneralException {
		try {
			Map<String, Object> resMap;
			BigDecimal v1  = null;
			String v2  = null;
			jdbcTemplate.update("insert into tab1 values (1, 'text1')");
			resMap = jdbcTemplate.queryForMap("select col1, col2 from tab1 where col1 = ?", id);
			v1 = QueryUtility.toObject(resMap.get("col1"), BigDecimal.class );
			v2 = QueryUtility.toObject(resMap.get("col2"), String.class );
		}
		catch (Exception e) {
			throw new GeneralException(e, LOGGER);
		}
	}
}

@Service
@Transactional
public class ExtractSqlSample implements IExtractSqlSample {

	@Autowired
	private JdbcTemplate jdbcTemplate;
	private static final org.slf4j.Logger LOGGER = org.slf4j.LoggerFactory.getLogger(ExtractSqlSample.class);
	@Autowired
	private final SqlQueryLoader sqlQueryLoader;
	private static Map<String, String> sqlQueryMap;
	private Integer errorCode = 0;
	private String sqlState = "";
	@PostConstruct
	public void initialize() throws Exception {
		sqlQueryMap = sqlQueryLoader.loadResource(AppConstants.EXTRACTED_SQL_BASE_PATH + AppConstants.EXTRACT_SQL_SAMPLE);
	}
	@Autowired
	public ExtractSqlSample(SqlQueryLoader sqlQueryLoader) {
		this.sqlQueryLoader = sqlQueryLoader;
	}
	@Override
	public void spExtractSqlSample(BigDecimal id)
	throws GeneralException {
		try {
			Map<String, Object> resMap;
			BigDecimal v1  = null;
			String v2  = null;
			jdbcTemplate.update(sqlQueryMap.get("extractSqlSample.spExtractSqlSample"));
			resMap = jdbcTemplate.queryForMap(sqlQueryMap.get("extractSqlSample.spExtractSqlSample2"), id);
			v1 = QueryUtility.toObject(resMap.get("col1"), BigDecimal.class );
			v2 = QueryUtility.toObject(resMap.get("col2"), String.class );
		}
		catch (Exception e) {
			throw new GeneralException(e, LOGGER);
		}
	}
}

public class AppConstants {

	public static final String EXTRACTED_SQL_BASE_PATH = "src/main/resources/sql";
	public static final String EXTRACT_SQL_SAMPLE = "/ExtractSqlSample.sql";
}

<queries>
	<query name="extractSqlSample.spExtractSqlSample">
insert into tab1 values (1, 'text1')
	</query>
	<query name="extractSqlSample.spExtractSqlSample2">
select col1, col2 from tab1 where col1 = ?
	</query>
</queries>

If this option does not work as expected, please contact our technical team: support@ispirer.com

Please note: the option works only for the Oracle, SQL Server, Sybase ASE and Sybase ASA sources!

By default, Ispirer Toolkit generates utility classes in the com.ispirer.<source_db_name> package, but the package name can be changed by specifying the required name in the ISPIRER_PACKAGE option.

The example below shows the difference in conversion using the ISPIRER_PACKAGE option with the default value and the changed value.

Oracle source code

Converted Java code by ISPIRER_PACKAGE=com.ispirer. {source_macro}

Converted Java code with the ISPIRER_PACKAGE=com.example.pkg option

CREATE PROCEDURE ISPIRER_PACKAGE_EXAMPLE
AS
   str varchar2(50);
BEGIN
   str := INITCAP(name);
END;

import com.ispirer.oracle.exception.GeneralException;
import com.ispirer.oracle.lang.Plsqlutils;
public class IspirerPackageExample {

	private static final org.slf4j.Logger LOGGER = org.slf4j.LoggerFactory.getLogger(IspirerPackageExample.class);
	public void spIspirerPackageExample() throws GeneralException {
		try {
			String str  = null;
			str = Plsqlutils.initcap(name);
		}
		catch (Exception e) {
			throw new GeneralException(e, LOGGER);
		}
	}
}

import com.example.pkg.exception.GeneralException;
import com.example.pkg.lang.Plsqlutils;
public class IspirerPackageExample {

	private static final org.slf4j.Logger LOGGER = org.slf4j.LoggerFactory.getLogger(IspirerPackageExample.class);
	public void spIspirerPackageExample() throws GeneralException {
		try {
			String str  = null;
			str = Plsqlutils.initcap(name);
		}
		catch (Exception e) {
			throw new GeneralException(e, LOGGER);
		}
	}
}

If this option does not work as expected, please contact our technical team: support@ispirer.com

The example below shows the difference between the resulting procedure in both cases. Please compare:

Oracle source code

PostgreSQL (by default, SECURITY_DEFINER=No or Empty)

PostgreSQL (with option SECURITY_DEFINER=Yes)

CREATE Procedure Pr_Test(p1 int default 0,p2 int, p3 int ) IS
BEGIN
insert into tab4 (col1, col2, col3)
select p1,p2,p3 from dual;
END Pr_Test;

CREATE OR REPLACE Procedure Pr_Test(p1 INTEGER default 0,p2 INTEGER DEFAULT NULL, p3 INTEGER DEFAULT NULL)
LANGUAGE plpgsql
   AS $$
BEGIN
insert into tab4(col1, col2, col3)
   select p1,p2,p3;
END; $$;

CREATE OR REPLACE Procedure Pr_Test(p1 INTEGER default 0,p2 INTEGER DEFAULT NULL, p3 INTEGER DEFAULT NULL)
LANGUAGE plpgsql SECURITY DEFINER
   AS $$
BEGIN
insert into tab4(col1, col2, col3)
   select p1,p2,p3;
END; $$;

CREATE or replace Procedure Pr_Test(p1 int default 0,p2 int, p3 int ) IS
BEGIN
insert into tab4 (col1, col2, col3)
select p1,p2,p3 from dual;
commit;
insert into tab4 (col1, col2, col3)
select 11110,p2,p3 from dual;
rollback;
END Pr_Test;

CREATE or replace Procedure Pr_Test(p1 INTEGER default 0,p2 INTEGER DEFAULT NULL, p3 INTEGER DEFAULT NULL)
LANGUAGE plpgsql
   AS $$
BEGIN
insert into tab4(col1, col2, col3)
   select p1,p2,p3;
   COMMIT;
   insert into tab4(col1, col2, col3)
   select 11110,p2,p3;
   ROLLBACK;
END; $$;

CREATE or replace Procedure Pr_Test(p1 INTEGER default 0,p2 INTEGER DEFAULT NULL, p3 INTEGER DEFAULT NULL)
LANGUAGE plpgsql SECURITY DEFINER
   AS $$
BEGIN
insert into tab4(col1, col2, col3)
   select p1,p2,p3;
   -- COMMIT
insert into tab4(col1, col2, col3)
   select 11110,p2,p3;
   -- rollback
END; $$;

If this option does not work as expected, please contact our technical team: support@ispirer.com

The example below shows the difference in the resulting procedure in both cases.
[POSTGRE]
TRIG_PROC_SCHEMA_PREFIX=Prefix_
TRIG_PROC_SCHEMA_SUFFIX=_Suffix
Please compare:

Informix source code

Converted PostgreSQL code (by default)

Converted PostgreSQL code (with options set)

create procedure schm.tr_proc_print()
  referencing new as n for my_table ;
  DEFINE v_message VARCHAR(255);
  LET v_message = 'Nailed it!';
end procedure ;

create trigger schm.tr_proc insert on schm.my_table referencing new as new
for each row
(execute procedure schm.tr_proc_print() with trigger references );

CREATE OR REPLACE FUNCTION schm.tr_proc_print_trfunc()
RETURNS TRIGGER LANGUAGE plpgsql
   AS $$
   DECLARE
   v_message VARCHAR(255);
BEGIN
   v_message := 'Nailed it!';
   RETURN NULL;
END; $$;

create trigger tr_proc AFTER insert on schm.my_table for each row
EXECUTE PROCEDURE schm.tr_proc_print_trfunc();

CREATE SCHEMA IF NOT EXISTS Prefix_schm_Suffix;

CREATE OR REPLACE FUNCTION Prefix_schm_Suffix.tr_proc_print_trfunc()
RETURNS TRIGGER LANGUAGE plpgsql
   AS $$
   DECLARE
   v_message VARCHAR(255);
BEGIN
   v_message := 'Nailed it!';
   RETURN NULL;
END; $$;

create trigger tr_proc AFTER insert on schm.my_table for each row
   EXECUTE PROCEDURE Prefix_schm_Suffix.tr_proc_print_trfunc();

If this option does not work as expected, please contact our technical team: support@ispirer.com

Here is an example of using the option [SYBASE] BCP_COMMAND_LINE_CUSTOM_PARAMETERS

BCP command line by default

BCP command line with the option value changed

 ini:

[SYBASE]
BCP_COMMAND_LINE_CUSTOM_PARAMETERS=-J iso_1

 sh:

<...>
/sybase/PC/OCS-16_0/bin/bcp custom..TABLE1 in table1.txt -c -t!^ -r#^ -e table1.err -U ***** -P ***** -S PC -b1000 -J iso_1
<...>

 ini:

[SYBASE]
BCP_COMMAND_LINE_CUSTOM_PARAMETERS=-J utf8 -m1000

 sh:

<...>
/sybase/PC/OCS-16_0/bin/bcp custom..TABLE1 in table1.txt -c -t!^ -r#^ -e table1.err -U ***** -P ***** -S PC -b1000 -J utf8 -m1000
<...>

If this option does not work as expected, please contact our technical team: support@ispirer.com

Please note: the option works only for the Oracle to Java direction!

By default, Ispirer Toolkit converts NUMBER with non-zero precision and with scale greater 18 to BigDecimal, but if USE_DOUBLE is set to “yes”, the tool converts NUMBER to Double.

The example below shows the difference between the default conversion and the conversion using the USE_DOUBLE=yes option.

Oracle source code

Converted Java code by default

Converted Java code with the USE_DOUBLE=yes option

declare
   v1 number := 60;
   v2 number(5, 2) := 50;
   v3 number(19);
   v4 number(7) := 15;
begin
   v3 := v1 * v2;
   v3 := v3 + v4;
end;

BigDecimal v1  = new BigDecimal("60");
	BigDecimal v2  = new BigDecimal("50");
	BigDecimal v3  = null;
	Integer v4  = 15;
	v3 = v1.multiply(v2);
	v3 = v3.add(BigDecimal.valueOf(v4));

Double v1  = 60d;
	Double v2  = 50d;
	Double v3  = null;
	Integer v4  = 15;
	v3 = v1 * v2;
	v3 = v3 + v4;

If this option does not work as expected, please contact our technical team: support@ispirer.com

Please note: the option works only for the Oracle to Java, SQL Server to Java, Sybase ASE to Java, Sybase ASA to Java directions!

By default, Ispirer Toolkit adds a commit at the end of each method, but if the option is set to “No”, the tool doesn't add a commit to methods.

The example below shows the difference between the default conversion and the conversion using the COMMIT_EACH_METHOD=no option.

Oracle source code

Converted Java code by default

Converted Java code with the COMMIT_EACH_METHOD=No option

CREATE PROCEDURE PROC_DELETE_EMPLOYEE
IS
BEGIN
    DELETE EMPLOYEE; 
END;

public void spProcDeleteEmployee() throws SQLException, GeneralException {
		Connection mConn = JDBCDataSource.getConnection();

		try {
			try(PreparedStatement mStmt = mConn.prepareStatement("DELETE EMPLOYEE");) {
				SQLCursorHandler.getInstance().setRowcount(mStmt.executeUpdate());
			}
			catch (SQLException se) {
				SQLCursorHandler.getInstance().handleExeption(se);
				throw se;
			}

			if (!mConn.getAutoCommit()) {
				mConn.commit();
			}
		}
		catch (Exception e) {
			throw new GeneralException(e, LOGGER);
		}
		finally {
			if (mConn != null) {
				mConn.close();
			}
		}
	}

public void spProcDeleteEmployee() throws SQLException, GeneralException {
		Connection mConn = JDBCDataSource.getConnection();

		try {
			try(PreparedStatement mStmt = mConn.prepareStatement("DELETE EMPLOYEE");) {
				SQLCursorHandler.getInstance().setRowcount(mStmt.executeUpdate());
			}
			catch (SQLException se) {
				SQLCursorHandler.getInstance().handleExeption(se);
				throw se;
			}
		}
		catch (Exception e) {
			throw new GeneralException(e, LOGGER);
		}
		finally {
			if (mConn != null) {
				mConn.close();
			}
		}
	}

If this option does not work as expected, please contact our technical team: support@ispirer.com

Usage examples of the sqlways.ini options

[Usage example]:DISABLE_CONSTRAINTS

Source code (Teradata)

Converted Vertica Analytic Database code

(without option DISABLE_CONSTRAINTS or with option DISABLE_CONSTRAINTS=No/Empty value)

Converted Vertica Analytic Databaser code (with option DISABLE_CONSTRAINTS=Yes)

[Usage example]:CONVERT_SP_WITH_OUTPUT_PARAMS

Source code (Informix)

Converted MS SQL Server code (by default)

Converted MS SQL Server code (with option CONVERT_SP_WITH_OUTPUT_PARAMS=S:\\out\\swf_stored_procedures.txt)

[Usage example]:PROCEDURE_WITH_OUTPUT_PARAMS

Source code (Informix)

Converted MS SQL Server code (by default)

Converted MS SQL Server code (with option PROCEDURE_WITH_OUTPUT_PARAMS=No)

[Usage example]: CONVERT_SP_RETSTATUS_OUTPARAM

Source code (Oracle)

Converted code (MSSQL Server) (CONVERT_SP_RETSTATUS_OUTPARAM=No, by default)

Converted code (MSSQL Server) (CONVERT_SP_RETSTATUS_OUTPARAM=Yes)

[Usage example]: RETURN_RESULT_FROM_SP_AND_FN

Source code (MSSQL Server)

Converted PostgreSQL code (RETURN_RESULT_FROM_SP_AND_FN=TABLE)

Converted PostgreSQL code (RETURN_RESULT_FROM_SP_AND_FN=SETOF)

Converted PostgreSQL code (RETURN_RESULT_FROM_SP_AND_FN=REFCURSOR, by default)

[Usage example]: IDENTITY_TO_SERIAL

Source code (DB2 LUW)

Converted PostgreSQL code (IDENTITY_TO_SERIAL=No)

Converted PostgreSQL code (IDENTITY_TO_SERIAL=Yes)

[Usage example]: TABLE_TYPE_CONVERSION

Source code (Oracle)

Converted PostgreSQL code (TABLE_TYPE_CONVERSION=Tables)

Converted PostgreSQL code (TABLE_TYPE_CONVERSION=Arrays, by default)

[Usage example]:CONVERT_ROUTINE_TO_SP_RESULTSET

Source code (Informix)

Converted code (MSSQL) (CONVERT_ROUTINE_TO_SP_RESULTSET=No, by default)

Converted code (MSSQL) (CONVERT_ROUTINE_TO_SP_RESULTSET=Yes)

[Usage example]: ORACLE_GET_CHAR_COLUMN_SIZE

Source code (Oracle)

Converted code (PostgreSQL)(ORACLE_GET_CHAR_COLUMN_SIZE=CHAR/Empty, by default)

Converted code (PostgreSQL)(ORACLE_GET_CHAR_COLUMN_SIZE=BYTE)

[Usage example]: FULLY_QUALIFY_IDENTIFIERS

[Usage example]: IDENTITY_COLUMN_TYPE

[Usage example]: TRIGGER_RECURSION_LVL

[Usage example]: RETURN_TABLE_SQL_MACRO for [ORACLE]

[Usage example]: RETURN_STATUS_TO

[Usage example]:IMPORT_CLIENT_OPTIONS

[Usage example]:CONVERT_DBLINK_TO_SCHEMA

[Usage example]:CONVERT_EXCEPT

[Usage example]:BL_ONLY

[Usage example]:CONCAT_NULL_STRINGS_AS_NULL

[Usage example]:TEMPDB_TABLES_TO_TEMPORARY_TABLES

[Usage example]:DESIGN_PATTERN

[Usage example]:DESIGN_PATTERN1

[Usage example]:TABLES_DDL

[Usage example]:AUTONOMOUS_TRANS

[Usage example]: AUTONOMOUS_TRANSACTION_TO_DBLINK

[Usage example]: MISSEDLOGIC

[Usage example]: LOGGER

[Usage example]: DEPENDENCY_INJECTION

[Usage example]: EXTRACT_SQL

[Usage example]: ISPIRER_PACKAGE

[Usage example]: SECURITY_DEFINER

[Usage example]: TRIG_PROC_SCHEMA_PREFIX and TRIG_PROC_SCHEMA_SUFFIX

[Usage example]: BCP_COMMAND_LINE_CUSTOM_PARAMETERS

[Usage example]: USE_DOUBLE

[Usage example]: COMMIT_EACH_METHOD