/*
by Emailx45, 2021-Jul-17 - 17:42hrs

Of course, my "frankstein" Stored Procedure can be better than this...
but it works this way!
*/

SET TERM ^ ;

create or alter procedure "prc_IP_To_Integer" (
    X_MY_IP_FULL_ADDRESS varchar(100) not null)
returns (
    X_IP_PART1 varchar(3),
    X_IP_PART2 varchar(3),
    X_IP_PART3 varchar(3),
    X_IP_PART4 varchar(3),
    X_IP_PARTX_ERROR varchar(100),
    X_IP_BIGINT_VALUE bigint,
    X_ERROR_ON_IP_PARTX varchar(100))
as
declare variable X_STRING_LEN integer;
declare variable X_POS integer;
declare variable X_IP_FULL varchar(100);
declare variable X_IP_VALUE_MORE_THAN_255 integer;
begin
    /*
    warning about "variable > n chars" ---> raise a exception because the "value" is more big than variable-size!!!
    then, i have defined variables with 100chars in some cases!!!
    */
    x_ip_part1 = '0';  /* each IP part it should receive just "3" chars, else, will be raised a exception */
    x_ip_part2 = '0';  /* more than 3char can raise a exception if "var = varchar(3) on definition" */
    x_ip_part3 = '0';
    x_ip_part4 = '0';
    x_ip_bigint_value = 0;
    x_pos = 0;
    x_string_len = 0;
    x_ip_value_more_than_255 = 0;
    x_ip_full = x_my_ip_full_address;
    x_ip_partx_error = 'none';
    x_error_on_ip_partx = '?';
    /* */
    /*x_my_ip_full_address = x_my_ip_full_address; '1.2.3.4   ';*/
    /*  */
    x_string_len = character_length(x_my_ip_full_address);
    /*  */
    if (x_string_len<1) then
    begin
        x_ip_full = x_my_ip_full_address;
        x_ip_partx_error = 'err: x_my_ip_full_address = empty';
        x_error_on_ip_partx = 'err: all parts of ip informed!';
        suspend;
        exit;
    end
    /*  */
    if (x_string_len>15) then
    begin
        x_ip_full = x_my_ip_full_address;
        x_ip_partx_error = 'err: x_my_ip_full_address with more than 15 chars';
        x_error_on_ip_partx = 'err: all parts of ip informed!';
        suspend;
        exit;
    end
    /******** x_ip_part1 BEGIN ********/
    x_my_ip_full_address = replace(x_my_ip_full_address, ' ', ''); /* eliminating blank-spaces? */
    x_string_len = character_length(x_my_ip_full_address);
    if (x_string_len=0) then
    begin
        x_ip_full = x_my_ip_full_address;
        x_error_on_ip_partx = 'err: "' || substring( x_my_ip_full_address from 1 for x_pos) ||'"';
        x_ip_partx_error = 'err: x_ip_part1 will be empty';
        suspend;
        exit;
    end
    /*  */
    x_pos = position('.', x_my_ip_full_address);
    if (x_pos < 2) then
    begin
        x_ip_full = x_my_ip_full_address;
        x_error_on_ip_partx = 'err: "' || substring( x_my_ip_full_address from 1 for x_pos) ||'"';
        x_ip_partx_error = 'err: x_ip_part1 will be = "" or contain"." ';
        suspend;
        exit;
    end
    /* testing if the ip-part1 have more than 3chars... */
    x_string_len = character_length( substring( x_my_ip_full_address from 1 for x_pos-1) );
    if (x_string_len>3) then
    begin
        x_ip_part1 = '0';
        x_ip_part2 = '0';
        x_ip_part3 = '0';
        x_ip_part4 = '0';
        x_ip_bigint_value = 0;
        x_ip_full = x_my_ip_full_address;
        x_error_on_ip_partx = 'err: "' || substring( x_my_ip_full_address from 1 for x_pos-1) ||'"';
        x_ip_partx_error = 'err: x_ip_part1 have more than 3 chars';
        suspend;
        exit;
    end
    /*  */
    /* testing if the ip-part1 have value more than 255... */
    x_ip_value_more_than_255 = cast( substring( x_my_ip_full_address from 1 for x_pos-1) as integer );
    if (x_ip_value_more_than_255>255) then
    begin
        x_ip_part1 = '0';
        x_ip_part2 = '0';
        x_ip_part3 = '0';
        x_ip_part4 = '0';
        x_ip_bigint_value = 0;
        x_ip_full = x_my_ip_full_address;
        x_error_on_ip_partx = 'err: "' || substring( x_my_ip_full_address from 1 for x_pos-1) ||'"';
        x_ip_partx_error = 'err: x_ip_part1 have value more than 255';
        suspend;
        exit;
    end
    /*  */
    x_ip_part1 = substring( x_my_ip_full_address from 1 for x_pos-1);
    /******** x_ip_part1 END ********/
    x_ip_bigint_value = x_ip_bigint_value + ((256*256*256) * cast(x_ip_part1 as integer));
    /******** x_ip_part2 BEGIN ********/
    x_string_len = character_length(x_ip_part1||'.');
    x_my_ip_full_address = substring( x_my_ip_full_address from (x_string_len+1));
    /*******************/
    x_string_len = character_length(x_my_ip_full_address);
    if (x_string_len=0) then
    begin
        x_ip_part1 = '0';
        x_ip_part2 = '0';
        x_ip_part3 = '0';
        x_ip_part4 = '0';
        x_ip_bigint_value = 0;
        x_ip_full = x_my_ip_full_address;
        x_error_on_ip_partx = 'err: "' || substring( x_my_ip_full_address from 1 for x_pos) ||'"';
        x_ip_partx_error = 'err: x_ip_part2 will be empty';
        suspend;
        exit;
    end
    /*  */
    x_pos = position('.', x_my_ip_full_address);
    if (x_pos < 2) then
    begin
        x_ip_part1 = '0';
        x_ip_part2 = '0';
        x_ip_part3 = '0';
        x_ip_part4 = '0';
        x_ip_bigint_value = 0;
        x_ip_full = x_my_ip_full_address;
        x_error_on_ip_partx = 'err: "' || substring( x_my_ip_full_address from 1 for x_pos) ||'"';
        x_ip_partx_error = 'err: x_ip_part2 will be = "" or contain"." ';
        suspend;
        exit;
    end
    /* testing if the ip-part2 have more than 3chars... */
    x_string_len = character_length( substring( x_my_ip_full_address from 1 for x_pos-1) );
    if (x_string_len>3) then
    begin
        x_ip_part1 = '0';
        x_ip_part2 = '0';
        x_ip_part3 = '0';
        x_ip_part4 = '0';
        x_ip_bigint_value = 0;
        x_ip_full = x_my_ip_full_address;
        x_error_on_ip_partx = 'err: "' || substring( x_my_ip_full_address from 1 for x_pos-1) ||'"';
        x_ip_partx_error = 'err: x_ip_part2 have more than 3 chars';
        suspend;
        exit;
    end
    /*  */
    /* testing if the ip-part1 have value more than 255... */
    x_ip_value_more_than_255 = cast( substring( x_my_ip_full_address from 1 for x_pos-1) as integer );
    if (x_ip_value_more_than_255>255) then
    begin
        x_ip_part1 = '0';
        x_ip_part2 = '0';
        x_ip_part3 = '0';
        x_ip_part4 = '0';
        x_ip_bigint_value = 0;
        x_ip_full = x_my_ip_full_address;
        x_error_on_ip_partx = 'err: "' || substring( x_my_ip_full_address from 1 for x_pos-1) ||'"';
        x_ip_partx_error = 'err: x_ip_part2 have value more than 255';
        suspend;
        exit;
    end
    /*  */
    x_ip_part2 = substring( x_my_ip_full_address from 1 for x_pos-1);
    /******** x_ip_part2 END ********/
    x_ip_bigint_value = x_ip_bigint_value + ((256*256) * cast(x_ip_part2 as integer));
    /******** x_ip_part3 BEGIN ********/
    x_string_len = character_length(x_ip_part2||'.');
    x_my_ip_full_address = substring( x_my_ip_full_address from (x_string_len+1));
    /*******************/
    x_string_len = character_length(x_my_ip_full_address);
    if (x_string_len=0) then
    begin
        x_ip_part1 = '0';
        x_ip_part2 = '0';
        x_ip_part3 = '0';
        x_ip_part4 = '0';
        x_ip_bigint_value = 0;
        x_ip_full = x_my_ip_full_address;
        x_error_on_ip_partx = 'err: "' || substring( x_my_ip_full_address from 1 for x_pos) ||'"';
        x_ip_partx_error = 'err: x_ip_part3 will be empty';
        suspend;
        exit;
    end
    /*  */
    x_pos = position('.', x_my_ip_full_address);
    if (x_pos < 2) then
    begin
        x_ip_part1 = '0';
        x_ip_part2 = '0';
        x_ip_part3 = '0';
        x_ip_part4 = '0';
        x_ip_bigint_value = 0;
        x_ip_full = x_my_ip_full_address;
        x_error_on_ip_partx = 'err: "' || substring( x_my_ip_full_address from 1 for x_pos) ||'"';
        x_ip_partx_error = 'err: x_ip_part3 will be = "" or contain"." ';
        suspend;
        exit;
    end
    /* testing if the ip-part3 have more than 3chars... */
    x_string_len = character_length( substring( x_my_ip_full_address from 1 for x_pos-1) );
    if (x_string_len>3) then
    begin
        x_ip_part1 = '0';
        x_ip_part2 = '0';
        x_ip_part3 = '0';
        x_ip_part4 = '0';
        x_ip_bigint_value = 0;
        x_ip_full = x_my_ip_full_address;
        x_error_on_ip_partx = 'err: "' || substring( x_my_ip_full_address from 1 for x_pos-1) ||'"';
        x_ip_partx_error = 'err: x_ip_part3 have more than 3 chars';
        suspend;
        exit;
    end
    /*  */
    /* testing if the ip-part1 have value more than 255... */
    x_ip_value_more_than_255 = cast( substring( x_my_ip_full_address from 1 for x_pos-1) as integer );
    if (x_ip_value_more_than_255>255) then
    begin
        x_ip_part1 = '0';
        x_ip_part2 = '0';
        x_ip_part3 = '0';
        x_ip_part4 = '0';
        x_ip_bigint_value = 0;
        x_ip_full = x_my_ip_full_address;
        x_error_on_ip_partx = 'err: "' || substring( x_my_ip_full_address from 1 for x_pos-1) ||'"';
        x_ip_partx_error = 'err: x_ip_part3 have value more than 255';
        suspend;
        exit;
    end
    /*  */
    x_ip_part3 = substring( x_my_ip_full_address from 1 for x_pos-1);
    /******** x_ip_part3 END ********/
    x_ip_bigint_value = x_ip_bigint_value + (256 * cast(x_ip_part3 as integer));
    /******** x_ip_part4 BEGIN ********/
    x_string_len = character_length(x_ip_part3||'.');
    x_my_ip_full_address = substring( x_my_ip_full_address from (x_string_len+1));
    /*******************/
    x_string_len = character_length(x_my_ip_full_address);
    if (x_string_len=0) then
    begin
        x_ip_part1 = '0';
        x_ip_part2 = '0';
        x_ip_part3 = '0';
        x_ip_part4 = '0';
        x_ip_bigint_value = 0;
        x_ip_full = x_my_ip_full_address;
        x_error_on_ip_partx = 'err: "' || substring( x_my_ip_full_address from 1 for x_pos) ||'"';
        x_ip_partx_error = 'err: x_ip_part4 will be empty';
        suspend;
        exit;
    end
    /* testing if the ip-part4 have more than 3chars... */
    x_string_len = character_length( substring( x_my_ip_full_address from 1) );
    if (x_string_len>3) then
    begin
        x_ip_part1 = '0';
        x_ip_part2 = '0';
        x_ip_part3 = '0';
        x_ip_part4 = '0';
        x_ip_bigint_value = 0;
        x_ip_full = x_my_ip_full_address;
        x_error_on_ip_partx = 'err: "' || substring( x_my_ip_full_address from 1) ||'"';
        x_ip_partx_error = 'err: x_ip_part4 have more than 3 chars';
        suspend;
        exit;
    end
    /*  */
    /* testing if the ip-part1 have value more than 255... */
    x_ip_value_more_than_255 = cast( substring( x_my_ip_full_address from 1 for x_pos-1) as integer );
    if (x_ip_value_more_than_255>255) then
    begin
        x_ip_part1 = '0';
        x_ip_part2 = '0';
        x_ip_part3 = '0';
        x_ip_part4 = '0';
        x_ip_bigint_value = 0;
        x_ip_full = x_my_ip_full_address;
        x_error_on_ip_partx = 'err: "' || substring( x_my_ip_full_address from 1 for x_pos-1) ||'"';
        x_ip_partx_error = 'err: x_ip_part4 have value more than 255';
        suspend;
        exit;
    end
    /*  */
    x_ip_part4 = substring( x_my_ip_full_address from 1); /* avoid catch more than 3chars*/
    x_string_len = character_length(x_ip_part4);
    /*  */
    x_ip_bigint_value = (x_ip_bigint_value + cast(x_ip_part4 as integer));
    x_ip_partx_error = 'IP Address OK';
    /*  */
   suspend;
end^

SET TERM ; ^

/* Existing privileges on this procedure */

GRANT EXECUTE ON PROCEDURE "prc_IP_To_Integer" TO SYSDBA;