Re: Upward Compatibility of SQR Versions

[Franck Masson <franck.masson@SQRIBE.COM>]

hi,
the upgrade from sqr 3 to 4 is well supported.
The source code is 99,9% compatible.
This a document that explain some point that you have to be care.

Franck

This document describes a number of possible errors and warnings that
customers upgrading from versions 2.5.x or 3.0.x to version 4.x.x may
encounter.

1. Using an edit mask when moving an SQR string variable to a numeric
variable worked in version 3, but in version 4, a 7100 error is
generated. This change is by design. Here is some sample code:

                move $var to #var edit 09999999
                print #var (1,1)

        The complete error is:

(SQR 7100) The use of an edit mask or the keywords NUMBER, MONEY or DATE
is not legal when storing numeric variables.

One workaround is to not use the edit mask and print the #var with the
NUMBER edit mask after altering the number-edit-mask value. For example:

                move $var to #var
                alter-locale number-edit-mask='09999999'
                print #var (1,1) number

        Another workaround is to move the string variable to itself with a
string edit mask. For example:

                move $var to $var edit '09999999'
                print $var (1,1)

2. The date-time command can no longer be used inside of a select
statement. Here is some sample code:

                begin-select
                col1
        date-time (1,1)
                from table1
                end-select

        The complete error is:

                (SQR 3702) Command not allowed in this section: date-time

        One workaround is to use the datenow function that was introduced in
version 4. For example:

                begin-select
                col1
                let $date = datenow()
                print $date (1,1)
                from table1
                end-select

Another workaround is to put the date-time command into a procedure and
call the procedure from the select statement. For example:

                begin-select
                col1
                do date_time
                from table1
                end-select
                end-procedure

                begin-procedure date_time
                date-time (1,1)
                end-procedure

3. Input of dates with SQR for Oracle has changed. With V3, the default
format for Oracle was a 2-digit
year. With V4, the default is a 4-digit year. This means that input
statements that worked in V3 need
an additional option to work the same in V4. For example:

                input $date type=date

        Must be changed to:

                input $date type=date format='dd-mon-yy'

4. Sybase changed the format of how char columns with blanks at the end
are returned with CT-Lib versus DB-Lib. This means that SQR for CT-Lib
(V3 and above) acts differently than SQR for DB-Lib (V3 and before).
With DB-Lib a char column did not include the blanks at the end, but
with CT-Lib, the blanks are included. To emulate DB-Lib functionality
with SQR for CT-Lib, the -TB flag should be used on the command line.

5. SQR for Sybase CT-Lib no longer supports distinct database
connections for the begin-select, begin-sql, and execute commands. SQR
instead uses the Sybase cursors feature that was introduced with CT-Lib.
Any -Cnn flag for specifying the logical connection number is no longer
needed. SQR will ignore the flag if still used. One consequence is that
temporary tables with the same name can no longer be created in separate
connections. For example, the following code which was acceptable in
V2.5:

begin-setup
begin-sql -C1
create table ##temp
(col1 varchar(10))
end-sql
begin-sql -C2
create table ##temp
(col1 varchar(10))
end-sql
end-setup

        Will give the following error in SQR for CT-Lib versions:

                (2714) There is already an object named '#temp' in the database.

6. SQR 2.5 did not complain about nested begin-procedure sections such
as this:

        begin-procedure main
        print 'test' (+1,1)
        begin-procedure main2
        print 'test2' (+1,1)
        end-procedure
        end-procedure

SQR 4.0.x and above will correctly give the following error:

        (SQR 3702) Command not allowed in this section: begin-procedure

7. Potentially ambiguous code that did not give a warning message in
V2.5, will give a warning with V3.0 and higher. For example, the
following command:

let #n2 = #n1-1

        Gives the warning:

                (SQR 4039) '#n1-1' assumed to be a variable name, not an 
expression.

To not get the warning, the command should be written as follows and the
dash character should be used carefully in variable names:

        let #n2 = #n1 - 1

8. With SQR version 2.5, when using the -D flag to display SQR output as
it is printed, there were formfeed characters placed in the output. To
achieve the same output in V4, version 4.3.1 or higher is required and
the following line must be added to the [Default-Settings] section of
the SQR.INI file:

OutputFormFeedWithDashD = TRUE | FALSE

9. Using a date edit mask on a string variable that is a partial date
string does not work the same in V4 as it did in prior releases. For
example, the following code would work before V4:

        begin-report
        let $d = 'Aug'
let $v = edit($d,'mm')
show $v
end-report

        But in V4, the following error would occur:

(SQR 1944) The date 'Aug' is not in the format specified by
SQR_DB_DATE_FORMAT or in one of the accepted formats listed below
Mon DD YYYY [HH:MI[:SS[.NNN]][AM | PM]]
Mon DD YYYY [HH:MI[:SS[:NNN]][AM | PM]]
YYYYMMDD [HH:MI[:SS[.NNN]][AM | PM]]
YYYYMMDD [HH:MI[:SS[:NNN]][AM | PM]]
SYYYYMMDD[HH24[MI[SS[NNNNNN]]]]

This error is caused by changes that were made when the date datatype
was introduced in version 4. The changes make it mandatory for strings
that have date edit masks applied to them to be in one of the default
date

Since this is how SQR now functions, you will need to workaround this
problem by using the strtodate function as follows:

        let $v = edit(strtodate($d,'mon'),'mm')

10. SQR 4.3.1 or higher now generates warning messages when an edit mask
contains a 2-digit year (YY or RR).

(SQR 7501) Using YY edit mask from (MM/DD/YY) against (1998)

        This message is controlled by a parameter in the [Default-Settings]
section of the SQR.INI file:

OutputTwoDigitYearWarningMsg = TRUE | FALSE


White, Denise wrote:
>
> I'm hoping someone can save us some time in our upgrade testing by either
> assuring me of upward compatibility between versions 3 and 4, or pointing me
> towards potential trouble spots.
>
> We currently are running SQR 3.0.18.1.1.  We are an Oracle shop, and have
> two systems that use SQR - PeopleSoft HRMS, and a non-PeopleSoft ERP system
> (Point.Man).  Our ERP system is so heavily customized that a decision has
> been made to never upgrade it (I'd like to state that I support HRMS and not
> ERP!).  We need to upgrade PeopleSoft to Tools 7.05.10 and HRMS 7.02.  This
> involves an upgrade to SQR 4.3.2.  Our DBA strongly feels that he wants only
> one version of SQR running for both systems.  He plans to do some testing of
> the ERP SQRs, but he assumes that any SQR written in version 2 or 3 will run
> successfully in version 4.  I have heard at a PeopleSoft User Group meeting
> that it is extremely important to thoroughly test every SQR program, which
> leads me to believe there may be some incompatibilities.  Can anyone shed
> some light on this issue?
>
> One thing I have heard is that in SQR 4, you cannot do "alter session set
> nls_date_format = 'DD-MON-YYYY';".  Unfortunately, I have just recently
> modified most of my SQRs to use this, after having found that my date math
> was not working correctly and was not including century in the calculations
> (another interesting decision had been made here to leave the Oracle default
> at DD-MON-YY and just program around it for Y2K compliance)!  This had
> seemed by far the easiest solution - how do I do it in SQR 4?
>
> Thanks in advance!
> Denise White
> Sr IT Application Developer
> Textron Systems