use sybsystemprocs
go
checkpoint
go
set nocount on
go
print 'Generic stored procedure used by Sybase Job Scheduler Template jobs'
go
-- Remove the sp_jst_purge_alphachars if it exists before attempting to
-- create it
if exists( select 1 from sysobjects
           where name = 'sp_jst_purge_alphachars'
             and type = 'P'
             and (sysstat & 7) =4 )
    drop proc sp_jst_purge_alphachars
go
/*
** sp_jst_purge_alphachars
** Version: 2.2
**
** This procedure takes an ASE version string and removes any alpha
** characters.  These characters are not useful in determining if an
** ASE constains support for a particular feature.
**
** This stored procedure were developed as part of the ASE Job Scheduler
** templates provided by Sybase. It must be installed on the target
** ASE prior to running jobs created from Sybase templates that
** specify its use.
**
** This stored proc is the called at job execution.  It is a support
** stored procedure and all values for it come from other stored
** procedures.
**
** Parameter definitions
** @ase_version - the version string for an ASE server.
**
** Returns 0
**
*/
create procedure sp_jst_purge_alphachars( @ase_version varchar(32) out )
as
begin
    declare @tmpStr varchar(1)
          , @pos int
          , @strLen int
          , @ascii_value int

    select @pos=1, @strLen = isnull(datalength( @ase_version ),0)

    --print 'version string is %1!', @ase_version
    --print 'string length is %1!', @strLen
    while( @pos <= @strLen )
    begin
        select @tmpStr = substring( @ase_version, @pos, 1 )
        --print 'tmpStr is %1!', @tmpStr
        select @ascii_value = ascii(@tmpStr)
        --print 'ascii value is %1!', @ascii_value
        if( @ascii_value >=79 and @ascii_value <= 129 )
        begin
          select @ase_version = stuff( @ase_version, @pos, 1, NULL )
          --print 'version string is %1!', @ase_version
          select @strLen = datalength( @ase_version)
        end
        select @pos = @pos+1
    end
    --print 'version string is %1!', @ase_version
    return 0
end
go
if exists( select 1 from sysobjects
           where name = 'sp_jst_purge_alphachars'
             and type = 'P'
             and (sysstat & 7) =4 )
begin
    print 'sp_jst_purge_alphachars has been created'
    grant execute on sp_jst_purge_alphachars to public
end
go

-- Remove the sp_jst_valid_ase_version if it exists before attempting to
-- create it
if exists( select 1 from sysobjects
           where name = 'sp_jst_valid_ase_version'
             and type = 'P'
             and (sysstat & 7) =4 )
    drop proc sp_jst_valid_ase_version
go
/*
** sp_jst_valid_ase_version
** Version: 2.2
**
** This procedure takes a minimum ASE version and determines if the 
** current server is that version or later.  It returns 0 if the
** current server version is later than or equal to the version passed
** in.  Else return -1.
**
** This stored procedure were developed as part of the ASE Job Scheduler
** templates provided by Sybase. It must be installed on the target
** ASE prior to running jobs created from Sybase templates that 
** specify its use.
**
** This stored proc is the called at job execution.  It is a support
** stored procedure and all values for it come from other stored
** procedures.
**
** Parameter definitions
** @earliest_version - the ASE version that the server should be.
**
** Returns 0 if the current ASE version is greater than or equal to
*  the earliest ASE version provided.
**
*/
create procedure sp_jst_valid_ase_version( @earliest_version varchar(32)
                                            = NULL )
as
begin

    declare @ase_version varchar(32)
          , @ear_ver_len int 
          , @ase_ver_len int
          , @ear_num int
          , @ase_num int
          , @ear_start_ndx int
          , @ear_end_ndx int
          , @ase_start_ndx int
          , @ase_end_ndx int
          , @sep_char char(1)
          , @keep_checking varchar(5)

    --initializations
    select @sep_char = '.'
         , @keep_checking = 'true'
         , @ase_start_ndx = 1
         , @ear_start_ndx = 1

    -- ensure we get a version; if we don't, we conclude that the current
    -- server version is valid
    select @ear_ver_len = isnull(datalength( @earliest_version ),0)
    if( @ear_ver_len = 0 )
        return 0

    --get the version text of the current ASE server
    exec sp_jst_version_num @ase_version output

    select @ase_ver_len = datalength( @ase_version )

    exec sp_jst_purge_alphachars @ase_version out

    while( @keep_checking = 'true' )
      begin
        select @ase_end_ndx = charindex( @sep_char, @ase_version )
        select @ear_end_ndx = charindex( @sep_char, @earliest_version )

        if( @ase_end_ndx = 0 )
            select @ase_end_ndx = datalength( @ase_version )+1

        if( @ear_end_ndx = 0 )
            select @ear_end_ndx = datalength( @earliest_version )+1

        select @ase_num = convert( int, substring( @ase_version, 
                                   @ase_start_ndx, @ase_end_ndx-1 ))

        select @ear_num = convert( int, substring( @earliest_version, 
                                   @ear_start_ndx, @ear_end_ndx-1 ))

        if( @ase_num > @ear_num )
            return 0

        if( @ase_num < @ear_num )
            return -1

        select @ase_version = stuff(@ase_version, 
                                    @ase_start_ndx,
                                    @ase_end_ndx,
                                    NULL)
        select @earliest_version = stuff(@earliest_version, 
                                         @ear_start_ndx,
                                         @ear_end_ndx,
                                         NULL)
        select @ase_ver_len = isnull(datalength(@ase_version),0)
        select @ear_ver_len = isnull(datalength(@earliest_version),0)
        if( @ear_ver_len = 0 )
          begin
            if( @ase_ver_len >= 0 )
                return 0
          end
        if( @ase_ver_len = 0 )
          begin
            return -1
          end
      end

    return 0
end
go
if exists( select 1 from sysobjects
           where name = 'sp_jst_valid_ase_version'
             and type = 'P'
             and (sysstat & 7) =4 )
begin
    print 'sp_jst_valid_ase_version has been created'
    grant execute on sp_jst_valid_ase_version to public
end
go
