[Search for users] [Overall Top Noters] [List of all Conferences] [Download this site]

Conference orarep::nomahs::sql

Title:SQL notes
Moderator:NOVA::SMITHI
Created:Wed Aug 27 1986
Last Modified:Thu Jun 05 1997
Last Successful Update:Fri Jun 06 1997
Number of topics:3895
Total number of notes:17726

3885.0. "possible COBOL BIGINT truncate problem" by M5::JAKUHN (the future isn't here yet) Wed Apr 30 1997 16:16

Rdb 7 / cobol

problem where cobol seems to be truncating instead of rounding digits in
a bigint var type. alpha. 


create table trun3
 (conv_lbs bigint(6) default 0,
  conv_res bigint(4) default 0);

insert into trun3
  values ( 4440.206682, 1 );
            ^^^^^^^^^^^---conv_lbs field 


In the cobol program you define temp host var:


  03 WS-CURR-COST-AM                        PIC S9(5)V9(4) COMP-3.

then select the conv_lbs from the trun3 table into the host var...

     EXEC SQL
        SELECT CONV_LBS  <-------select conv_lbs ( 4440.206682)      
        INTO :WS-CURR-COST-AM    ...into the host var
        FROM TRUN3
     END-EXEC.

Now, set the conv_res field to what conv_lbs was -

     EXEC SQL 
        UPDATE TRUN3 SET CONV_RES = :WS-CURR-COST-AM
     END-EXEC.

SQL> select * from trun3
cont> ;
                    CONV_LBS                    CONV_RES
                 4440.206682                   4440.2066
1 row selected


The CONV_RES field should(?) be updated with the rounded value 4440.2067 but
intead the result is truncated to 4440.2066. This does not happen with
the INT data type, but it does with the BIGINT data type independently of the
number of decimal digits in the BIGINT declaration. 

thank you.
jk
T.RTitleUserPersonal
Name		DateLines
3885.1NOVA::SMITHIDon&#039;t understate or underestimate Rdb!Wed Apr 30 1997 16:384
How is SQL declaring the message port, take a look at the intermediate COBOL
generated by the precompiler.

Ian
3885.2message portM5::JAKUHNthe future isn&#039;t here yetWed Apr 30 1997 17:504
    i'm sorry, i don't understand what a "message port" is. do you mean
    the precompiler generated host var that it uses in its calls?
    thanks
    jay
3885.3NOVA::SMITHIDon&#039;t understate or underestimate Rdb!Wed Apr 30 1997 18:087
yep.  sorry for the internals-speak

Look at the .COB generated by SQL, you will see COBOL code which is generated
by SQL, look for the SELECT ... INTO statement for clues to the generated host
variable names.

Ian
3885.4more infoM5::JAKUHNthe future isn&#039;t here yetWed Apr 30 1997 20:0020
    Here is what i see generated in the .cob file:
    
    the first select -
    
                       CALL "SQL$PROC_2_9B390D_799720" USING
                                SQLCA
                                ,WS_CURR_COST_AM.
    and here is the update:
    
                        CALL "SQL$PROC_3_9B390D_7AE50A" USING
                                SQLCA
                                ,WS_CURR_COST_AM.
    
    I can't find a way to see what type of var WS_CURR_COST_AM is though. 
    I've got sql$keep_prep_files "TRUE". Wading through the precompiler
    code, i think it does a spawn to create a macro library. I don't know
    how to stop it from deleting it after the compile. 
    thanks
    j
3885.5NOVA::SMITHIDon&#039;t understate or underestimate Rdb!Thu May 01 1997 07:483
There should be a .MAR file created and saved (if you define the logical).

Ian
3885.6NOVA::SMITHIDon&#039;t understate or underestimate Rdb!Thu May 01 1997 07:494
On seocnd thoughts, just define RDMS$DEBUG_FLAGS "Bc" and run it.  Please the
captured BLR here.

Ian
3885.7might already be fixedNOVA::HERREThu May 01 1997 15:318
    If this is what I think it is, the truncation happens on the select,
    not on the insert.  I can reproduce this using 6.1, but not with 7.0;
    the fix may not have made it into the initial 7.0 release.
    Try displaying the value after the select and see what it is.
    
    Also, sql only produces a .mar file on vax, so sql$keep_prep_files
    isn't as useful on alpha.
3885.8blr outputM5::JAKUHNthe future isn&#039;t here yetFri May 02 1997 14:25182
    Thank you Ian. here is the blr from the precompile. 
    
    
    
          DECLARE SCHEMA FILENAME I1_APLF_DBS
                  1
%SQL-I-DEPR_FEATURE, (1) Deprecated Feature: SCHEMA (meaning ALIAS)
0000 (00000) BLR$K_VERSION4
0001 (00001) | BLR$K_BEGIN
0002 (00002) |   BLR$K_MESSAGE 1 7
0006 (00006) |   | DSC$K_DTYPE_T 1
0009 (00009) |   | DSC$K_DTYPE_T 3
000C (00012) |   | DSC$K_DTYPE_L 0
000E (00014) |   | DSC$K_DTYPE_L 0
0010 (00016) |   | DSC$K_DTYPE_Q 0
0012 (00018) |   | DSC$K_DTYPE_L 0
0014 (00020) |   | DSC$K_DTYPE_L 0
0016 (00022) |   BLR$K_MESSAGE 2 0
001A (00026) |   BLR$K_MESSAGE 3 1
001E (00030) |   | DSC$K_DTYPE_T 31
0021 (00033) |   BLR$K_RECEIVE  3
0023 (00035) |   | BLR$K_BEGIN
0024 (00036) |   |   BLR$K_FOR
0025 (00037) |   |   | BLR$K_RSE  1
0027 (00039) |   |   |   BLR$K_RELATION RDB$RELATIONS 1
0037 (00055) |   |   |   BLR$K_BOOLEAN
0038 (00056) |   |   |   | BLR$K_EQL
0039 (00057) |   |   |   |   BLR$K_FIELD 1 RDB$RELATION_NAME
004D (00077) |   |   |   |   BLR$K_PARAMETER 3 0
0051 (00081) |   |   | BLR$K_END
0052 (00082) |   |   | BLR$K_BEGIN
0053 (00083) |   |   |   BLR$K_SEND  1
0055 (00085) |   |   |   | BLR$K_BEGIN
0056 (00086) |   |   |   |   BLR$K_ASSIGNMENT
0057 (00087) |   |   |   |   | BLR$K_LITERAL
0058 (00088) |   |   |   |   |   DSC$K_DTYPE_T 1  "N"
005C (00092) |   |   |   |   | BLR$K_PARAMETER 1 0
0060 (00096) |   |   |   |   BLR$K_ASSIGNMENT
0061 (00097) |   |   |   |   | BLR$K_FIELD 1 RDB$DBKEY_LENGTH
0074 (00116) |   |   |   |   | BLR$K_PARAMETER 1 2
0078 (00120) |   |   |   |   BLR$K_ASSIGNMENT
0079 (00121) |   |   |   |   | BLR$K_FIELD 1 RDB$RELATION_ID
008B (00139) |   |   |   |   | BLR$K_PARAMETER 1 3
008F (00143) |   |   |   |   BLR$K_ASSIGNMENT
0090 (00144) |   |   |   |   | BLR$K_FIELD 1 RDB$VIEW_BLR
009F (00159) |   |   |   |   | BLR$K_PARAMETER 1 4
00A3 (00163) |   |   |   |   BLR$K_ASSIGNMENT
00A4 (00164) |   |   |   |   | BLR$K_FIELD 1 RDB$SYSTEM_FLAG
00B6 (00182) |   |   |   |   | BLR$K_PARAMETER 1 5
00BA (00186) |   |   |   |   BLR$K_ASSIGNMENT
00BB (00187) |   |   |   |   | BLR$K_FIELD 1 RDB$FLAGS
00C7 (00199) |   |   |   |   | BLR$K_PARAMETER 1 6
00CB (00203) |   |   |   | BLR$K_END
00CC (00204) |   |   | BLR$K_END
00CD (00205) |   | BLR$K_END
00CE (00206) |   BLR$K_SEND  1
00D0 (00208) |   | BLR$K_BEGIN
00D1 (00209) |   |   BLR$K_ASSIGNMENT
00D2 (00210) |   |   | BLR$K_LITERAL
00D3 (00211) |   |   |   DSC$K_DTYPE_T 1  "Y"
00D7 (00215) |   |   | BLR$K_PARAMETER 1 0
00DB (00219) |   | BLR$K_END
00DC (00220) | BLR$K_END
00DD (00221) BLR$K_EOC
0000 (00000) BLR$K_VERSION4
0001 (00001) | BLR$K_BEGIN
0002 (00002) |   BLR$K_MESSAGE 1 24
0006 (00006) |   | DSC$K_DTYPE_T 1
0009 (00009) |   | DSC$K_DTYPE_T 3
000C (00012) |   | DSC$K_DTYPE_T 31
000F (00015) |   | DSC$K_DTYPE_T 1
0012 (00018) |   | DSC$K_DTYPE_L 0
0014 (00020) |   | DSC$K_DTYPE_L 0
0016 (00022) |   | DSC$K_DTYPE_Q 0
0018 (00024) |   | DSC$K_DTYPE_Q 0
001A (00026) |   | DSC$K_DTYPE_VT 255
001D (00029) |   | DSC$K_DTYPE_T 3
0020 (00032) |   | DSC$K_DTYPE_VT 255
0023 (00035) |   | DSC$K_DTYPE_T 3
0026 (00038) |   | DSC$K_DTYPE_L 0
0028 (00040) |   | DSC$K_DTYPE_L 0
002A (00042) |   | DSC$K_DTYPE_L 0
002C (00044) |   | DSC$K_DTYPE_Q 0
002E (00046) |   | DSC$K_DTYPE_L 0
0030 (00048) |   | DSC$K_DTYPE_L 0
0032 (00050) |   | DSC$K_DTYPE_T 31
0035 (00053) |   | DSC$K_DTYPE_T 1
0038 (00056) |   | DSC$K_DTYPE_T 31
003B (00059) |   | DSC$K_DTYPE_T 1
003E (00062) |   | DSC$K_DTYPE_Q 0
0040 (00064) |   | DSC$K_DTYPE_Q 0
0042 (00066) |   BLR$K_MESSAGE 2 0
0046 (00070) |   BLR$K_MESSAGE 3 1
004A (00074) |   | DSC$K_DTYPE_T 31
004D (00077) |   BLR$K_RECEIVE  3
004F (00079) |   | BLR$K_BEGIN
0050 (00080) |   |   BLR$K_FOR
0051 (00081) |   |   | BLR$K_RSE  2
0053 (00083) |   |   |   BLR$K_RELATION RDB$RELATION_FIELDS 1
0069 (00105) |   |   |   BLR$K_RELATION RDB$FIELDS 2
0076 (00118) |   |   |   BLR$K_BOOLEAN
0077 (00119) |   |   |   | BLR$K_AND
0078 (00120) |   |   |   |   BLR$K_EQL
0079 (00121) |   |   |   |   | BLR$K_FIELD 2 RDB$FIELD_NAME
008A (00138) |   |   |   |   | BLR$K_FIELD 1 RDB$FIELD_SOURCE
009D (00157) |   |   |   |   BLR$K_EQL
009E (00158) |   |   |   |   | BLR$K_FIELD 1 RDB$RELATION_NAME
00B2 (00178) |   |   |   |   | BLR$K_PARAMETER 3 0
00B6 (00182) |   |   |   BLR$K_SORT  1
00B8 (00184) |   |   |   BLR$K_ASCENDING
00B9 (00185) |   |   |   | BLR$K_FIELD 1 RDB$FIELD_POSITION
00CE (00206) |   |   | BLR$K_END
00CF (00207) |   |   | BLR$K_BEGIN
00D0 (00208) |   |   |   BLR$K_SEND  1
00D2 (00210) |   |   |   | BLR$K_BEGIN
00D3 (00211) |   |   |   |   BLR$K_ASSIGNMENT
00D4 (00212) |   |   |   |   | BLR$K_LITERAL
00D5 (00213) |   |   |   |   |   DSC$K_DTYPE_T 1  "N"
00D9 (00217) |   |   |   |   | BLR$K_PARAMETER 1 0
00DD (00221) |   |   |   |   BLR$K_ASSIGNMENT
00DE (00222) |   |   |   |   | BLR$K_FIELD 1 RDB$FIELD_NAME
00EF (00239) |   |   |   |   | BLR$K_PARAMETER 1 2
00F3 (00243) |   |   |   |   BLR$K_ASSIGNMENT
00F4 (00244) |   |   |   |   | BLR$K_FIELD 1 RDB$FIELD_POSITION
0109 (00265) |   |   |   |   | BLR$K_PARAMETER 1 4
010D (00269) |   |   |   |   BLR$K_ASSIGNMENT
010E (00270) |   |   |   |   | BLR$K_FIELD 1 RDB$FIELD_ID
011D (00285) |   |   |   |   | BLR$K_PARAMETER 1 5
0121 (00289) |   |   |   |   BLR$K_ASSIGNMENT
0122 (00290) |   |   |   |   | BLR$K_FIELD 1 RDB$QUERY_HEADER
0135 (00309) |   |   |   |   | BLR$K_PARAMETER 1 6
0139 (00313) |   |   |   |   BLR$K_ASSIGNMENT
013A (00314) |   |   |   |   | BLR$K_FIELD 2 RDB$QUERY_HEADER
014D (00333) |   |   |   |   | BLR$K_PARAMETER 1 7
0151 (00337) |   |   |   |   BLR$K_ASSIGNMENT
0152 (00338) |   |   |   |   | BLR$K_FIELD 1 RDB$EDIT_STRING
0164 (00356) |   |   |   |   | BLR$K_PARAMETER 1 8
0168 (00360) |   |   |   |   BLR$K_ASSIGNMENT
0169 (00361) |   |   |   |   | BLR$K_FIELD 2 RDB$EDIT_STRING
017B (00379) |   |   |   |   | BLR$K_PARAMETER 1 10
017F (00383) |   |   |   |   BLR$K_ASSIGNMENT
0180 (00384) |   |   |   |   | BLR$K_FIELD 2 RDB$FIELD_TYPE
0191 (00401) |   |   |   |   | BLR$K_PARAMETER 1 12
0195 (00405) |   |   |   |   BLR$K_ASSIGNMENT
0196 (00406) |   |   |   |   | BLR$K_FIELD 2 RDB$FIELD_LENGTH
01A9 (00425) |   |   |   |   | BLR$K_PARAMETER 1 13
01AD (00429) |   |   |   |   BLR$K_ASSIGNMENT
01AE (00430) |   |   |   |   | BLR$K_FIELD 2 RDB$FIELD_SCALE
01C0 (00448) |   |   |   |   | BLR$K_PARAMETER 1 14
01C4 (00452) |   |   |   |   BLR$K_ASSIGNMENT
01C5 (00453) |   |   |   |   | BLR$K_FIELD 2 RDB$COMPUTED_BLR
01D8 (00472) |   |   |   |   | BLR$K_PARAMETER 1 15
01DC (00476) |   |   |   |   BLR$K_ASSIGNMENT
01DD (00477) |   |   |   |   | BLR$K_FIELD 2 RDB$SEGMENT_LENGTH
01F2 (00498) |   |   |   |   | BLR$K_PARAMETER 1 16
01F6 (00502) |   |   |   |   BLR$K_ASSIGNMENT
01F7 (00503) |   |   |   |   | BLR$K_FIELD 2 RDB$FIELD_SUB_TYPE
020C (00524) |   |   |   |   | BLR$K_PARAMETER 1 17
0210 (00528) |   |   |   |   BLR$K_ASSIGNMENT
0211 (00529) |   |   |   |   | BLR$K_FIELD 1 RDB$QUERY_NAME
0222 (00546) |   |   |   |   | BLR$K_PARAMETER 1 18
0226 (00550) |   |   |   |   BLR$K_ASSIGNMENT
0227 (00551) |   |   |   |   | BLR$K_FIELD 2 RDB$QUERY_NAME
0238 (00568) |   |   |   |   | BLR$K_PARAMETER 1 20
023C (00572) |   |   |   |   BLR$K_ASSIGNMENT
023D (00573) |   |   |   |   | BLR$K_FIELD 1 RDB$DEFAULT_VALUE
0251 (00593) |   |   |   |   | BLR$K_PARAMETER 1 22
0255 (00597) |   |   |   |   BLR$K_ASSIGNMENT
0256 (00598) |   |   |   |   | BLR$K_FIELD 2 RDB$DEFAULT_VALUE
026A (00618) |   |   |   |   | BLR$K_PARAMETER 1 23
026E (00622) |   |   |   | BLR$K_END
026F (00623) |   |   | BLR$K_END
0270 (00624) |   | BLR$K_END
0271 (00625) |   BLR$K_SEND  1
0273 (00627) |   | BLR$K_BEGIN
0274 (00628) |   |   BLR$K_ASSIGNMENT
0275 (00629) |   |   | BLR$K_LITERAL
0276 (00630) |   |   |   DSC$K_DTYPE_T 1  "Y"
027A (00634) |   |   | BLR$K_PARAMETER 1 0
027E (00638) |   | BLR$K_END
027F (00639) | BLR$K_END
0280 (00640) BLR$K_EOC
3885.9NOVA::SMITHIDon&#039;t understate or underestimate Rdb!Fri May 02 1997 15:137
~    Thank you Ian. here is the blr from the precompile. 

Well that is nice, but I already know the queries executed by the PRECOMPILER
work!  How about running your program because it is the one which isn't
working, right?

Ian
3885.10blr from runM5::JAKUHNthe future isn&#039;t here yetFri May 02 1997 17:18114
    /Well that is nice, but I already know the queries executed by the
    /PRECOMPILER
    
    Glad I can add a little happiness to your day, Ian. :-) :-) :-) :-)
    Here is the blr from the RUN of the prog. thanks again. 
    j
    
    
    
0000 (00000) BLR$K_VERSION4
0001 (00001) | BLR$K_BEGIN
0002 (00002) |   BLR$K_MESSAGE 1 3
0006 (00006) |   | DSC$K_DTYPE_L 0
0008 (00008) |   | DSC$K_DTYPE_L 0
000A (00010) |   | DSC$K_DTYPE_Q -6
000C (00012) |   BLR$K_MESSAGE 2 0
0010 (00016) |   BLR$K_SEND  1
0012 (00018) |   | BLR$K_BEGIN
0013 (00019) |   |   BLR$K_ASSIGNMENT
0014 (00020) |   |   | BLR$K_LITERAL
0015 (00021) |   |   |   DSC$K_DTYPE_L 0  "100"
001B (00027) |   |   | BLR$K_PARAMETER 1 0
001F (00031) |   |   BLR$K_FOR
0020 (00032) |   |   | BLR$K_RSE  1
0022 (00034) |   |   |   BLR$K_RELATION TRUN3 1
002A (00042) |   |   |   BLR$K_FIRST
002B (00043) |   |   |   | BLR$K_LITERAL
002C (00044) |   |   |   |   DSC$K_DTYPE_W 0  "2"
0030 (00048) |   |   | BLR$K_END
0031 (00049) |   |   | BLR$K_BEGIN
0032 (00050) |   |   |   BLR$K_IF
0033 (00051) |   |   |   | BLR$K_EQL
0034 (00052) |   |   |   |   BLR$K_LITERAL
0035 (00053) |   |   |   |   | DSC$K_DTYPE_L 0  "100"
003B (00059) |   |   |   |   BLR$K_PARAMETER 1 0
003F (00063) |   |   |   | BLR$K_BEGIN
0040 (00064) |   |   |   |   BLR$K_ASSIGNMENT
0041 (00065) |   |   |   |   | BLR$K_FIELD 1 CONV_LBS
004C (00076) |   |   |   |   | BLR$K_PARAMETER2 1 2 1
0052 (00082) |   |   |   |   BLR$K_ASSIGNMENT
0053 (00083) |   |   |   |   | BLR$K_LITERAL
0054 (00084) |   |   |   |   |   DSC$K_DTYPE_L 0  "0"
005A (00090) |   |   |   |   | BLR$K_PARAMETER 1 0
005E (00094) |   |   |   | BLR$K_END
005F (00095) |   |   |   | BLR$K_BEGIN
0060 (00096) |   |   |   |   BLR$K_ASSIGNMENT
0061 (00097) |   |   |   |   | BLR$K_LITERAL
0062 (00098) |   |   |   |   |   DSC$K_DTYPE_L 0  "-811"
0068 (00104) |   |   |   |   | BLR$K_PARAMETER 1 0
006C (00108) |   |   |   | BLR$K_END
006D (00109) |   |   | BLR$K_END
006E (00110) |   | BLR$K_END
006F (00111) | BLR$K_END
0070 (00112) BLR$K_EOC
0000 (00000) BLR$K_VERSION4
0001 (00001) | BLR$K_BEGIN
0002 (00002) |   BLR$K_MESSAGE 1 2
0006 (00006) |   | DSC$K_DTYPE_L 0
0008 (00008) |   | DSC$K_DTYPE_L -4
000A (00010) |   BLR$K_MESSAGE 2 2
000E (00014) |   | DSC$K_DTYPE_L 0
0010 (00016) |   | DSC$K_DTYPE_L 0
0012 (00018) |   BLR$K_BEGIN
0013 (00019) |   | BLR$K_DCL_VARIABLE 2
0016 (00022) |   |   DSC$K_DTYPE_L 0
0018 (00024) |   | BLR$K_ASSIGNMENT
0019 (00025) |   |   BLR$K_LITERAL
001A (00026) |   |   | DSC$K_DTYPE_L 0  "0"
0020 (00032) |   |   BLR$K_VARIABLE 2
0023 (00035) |   | BLR$K_RECEIVE  1
0025 (00037) |   |   BLR$K_BEGIN
0026 (00038) |   |   | BLR$K_FOR
0027 (00039) |   |   |   BLR$K_CONTROL_BITS  8
                         (searched update)
0029 (00041) |   |   |   BLR$K_RSE  1
002B (00043) |   |   |   | BLR$K_RELATION TRUN3 1
0033 (00051) |   |   |   BLR$K_END
0034 (00052) |   |   |   BLR$K_BEGIN
0035 (00053) |   |   |   | BLR$K_ASSIGNMENT
0036 (00054) |   |   |   |   BLR$K_ADD
0037 (00055) |   |   |   |   | BLR$K_LITERAL
0038 (00056) |   |   |   |   |   DSC$K_DTYPE_L 0  "1"
003E (00062) |   |   |   |   | BLR$K_VARIABLE 2
0041 (00065) |   |   |   |   BLR$K_VARIABLE 2
0044 (00068) |   |   |   | BLR$K_MODIFY 1 2
0047 (00071) |   |   |   |   BLR$K_BEGIN
0048 (00072) |   |   |   |   | BLR$K_ASSIGNMENT
0049 (00073) |   |   |   |   |   BLR$K_PARAMETER2 1 1 0
004F (00079) |   |   |   |   |   BLR$K_FIELD 2 CONV_RES
005A (00090) |   |   |   |   BLR$K_END
005B (00091) |   |   |   BLR$K_END
005C (00092) |   |   BLR$K_END
005D (00093) |   | BLR$K_SEND  2
005F (00095) |   |   BLR$K_BEGIN
0060 (00096) |   |   | BLR$K_ASSIGNMENT
0061 (00097) |   |   |   BLR$K_VARIABLE 2
0064 (00100) |   |   |   BLR$K_PARAMETER 2 0
0068 (00104) |   |   | BLR$K_IF
0069 (00105) |   |   |   BLR$K_EQL
006A (00106) |   |   |   | BLR$K_LITERAL
006B (00107) |   |   |   |   DSC$K_DTYPE_L 0  "0"
0071 (00113) |   |   |   | BLR$K_VARIABLE 2
0074 (00116) |   |   |   BLR$K_ASSIGNMENT
0075 (00117) |   |   |   | BLR$K_LITERAL
0076 (00118) |   |   |   |   DSC$K_DTYPE_L 0  "100"
007C (00124) |   |   |   | BLR$K_PARAMETER 2 1
0080 (00128) |   |   |   BLR$K_ASSIGNMENT
0081 (00129) |   |   |   | BLR$K_LITERAL
0082 (00130) |   |   |   |   DSC$K_DTYPE_L 0  "0"
0088 (00136) |   |   |   | BLR$K_PARAMETER 2 1
008C (00140) |   |   BLR$K_END
008D (00141) |   BLR$K_END
008E (00142) | BLR$K_END
008F (00143) BLR$K_EOC
3885.11will the old precompiler be ok?M5::LWILCOXHow about Fireworks?Mon May 05 1997 16:5213
                       <<< Note 3885.7 by NOVA::HERRE >>>
                          -< might already be fixed >-

In Jay's absence I just wanted to let whomever know that Jay has apparently
reproduced this with 7.0-01, and the customer has reproduced it on 6.1-1.

As a workaround, would it be ok for the customer to use the precompiler image
from 6.1 where it was not broken (according to the customer)?  I've seen
that kind of thing work for various other problems.

Thanks.

liz wilcox
3885.12NOVA::SMITHIDon&#039;t understate or underestimate Rdb!Mon May 05 1997 18:2145
The SELECT ... INTO looks fine
...
0002 (00002) |   BLR$K_MESSAGE 1 3
0006 (00006) |   | DSC$K_DTYPE_L 0
0008 (00008) |   | DSC$K_DTYPE_L 0
000A (00010) |   | DSC$K_DTYPE_Q -6			BIGINT (6)
...
001F (00031) |   |   BLR$K_FOR				SELECT statement
0020 (00032) |   |   | BLR$K_RSE  1
0022 (00034) |   |   |   BLR$K_RELATION TRUN3 1
...
0040 (00064) |   |   |   |   BLR$K_ASSIGNMENT
0041 (00065) |   |   |   |   | BLR$K_FIELD 1 CONV_LBS
004C (00076) |   |   |   |   | BLR$K_PARAMETER2 1 2 1	-> assigned to BIGINT(6)
..

The UPDATE looks broken

...
0002 (00002) |   BLR$K_MESSAGE 1 2
0006 (00006) |   | DSC$K_DTYPE_L 0
0008 (00008) |   | DSC$K_DTYPE_L -4	INTEGER (4)??
...
0026 (00038) |   |   | BLR$K_FOR			UPDATE statement
0027 (00039) |   |   |   BLR$K_CONTROL_BITS  8
                         (searched update)
0029 (00041) |   |   |   BLR$K_RSE  1
002B (00043) |   |   |   | BLR$K_RELATION TRUN3 1
0033 (00051) |   |   |   BLR$K_END
...
0044 (00068) |   |   |   | BLR$K_MODIFY 1 2		UPDATE action
0047 (00071) |   |   |   |   BLR$K_BEGIN
0048 (00072) |   |   |   |   | BLR$K_ASSIGNMENT
0049 (00073) |   |   |   |   |   BLR$K_PARAMETER2 1 1 0
004F (00079) |   |   |   |   |   BLR$K_FIELD 2 CONV_RES	-> assign an INT(4)
005A (00090) |   |   |   |   BLR$K_END

If this host variable is the same then there is trouble because the last few
digits will be list.

Can you post the program in full?

thanks,

Ian
3885.13Here it isM5::LWILCOXHow about Fireworks?Mon May 05 1997 19:28183
  <<< Note 3885.12 by NOVA::SMITHI "Don't understate or underestimate Rdb!" >>>

I'll see what i can dig up from Jay.  Here it is:


IDENTIFICATION DIVISION.

*

PROGRAM-ID.    TRUN3 IS INITIAL.
AUTHOR.        CELITE CORPORATION.
DATE-WRITTEN.  04/12/1997.
DATE-COMPILED.

ENVIRONMENT DIVISION.
CONFIGURATION SECTION.

   SOURCE-COMPUTER.      VAX.
   OBJECT-COMPUTER.      VAX.


*****************************************************************
*                                                               *
*                       WORKING STORAGE                         *
*****************************************************************

DATA DIVISION.
WORKING-STORAGE SECTION.

01 BEGIN-OF-WS.
  03 LT-START-OF-WS                         PIC X(42)
     VALUE '***** BEGIN WORKING STORAGE TRUN3    *****'.

        EXEC SQL 
          DECLARE SCHEMA FILENAME I1_APLF_DBS
        END-EXEC.

        EXEC SQL INCLUDE SQLCA
        END-EXEC.


*****************************************************************
*                                                               *
*                  WORKING STORAGE VARIABLES                    *
*    -------------------------------------------------------    *
*    THIS  AREA  DEFINES  ALL  MISCELLANEOUS WORKING STORAGE    *
*    VARIABLES THAT ARE USED IN THIS APPLICATION.               *
*                                                               *
*****************************************************************

01 WORKING-STORAGE-VARIABLES.

  03 WS-CURR-COST-AM                        PIC S9(5)V9(4) COMP-3.

*****************************************************************
*                                                               *
* End of Date variables.                                        *
*                                                               *
*****************************************************************
/

01 LT-END-OF-WS.
  03 LT-FINISH-OF-WS                        PIC X(40)
     VALUE '***** END WORKING STORAGE TRUN3    *****'.

/

/
*****************************************************************
*                                                               *
*                      PROCEDURE DIVISION                       *
*    ------------------------------------------------------     *
*                                                               *
*****************************************************************

/

PROCEDURE DIVISION.

/

A0100-MAINLINE.

*****************************************************************
*                                                               *
*                      A0100-MAINLINE                           *
*  -----------------------------------------------------------  *
*                                                               *
*  THE MAINLINE CODE SHOULD NOT NEED TO BE ALTERED IN ANY WAY.  *
*  THE CUSTOM PRINT CODE IS PERFORMED FOR AS MANY TIMES AS      *
*  THERE ARE TRANSACTION RECORDS AVAILABLE.  THIS NUMBER IS     *
*  KEPT IN THE PRINT-NO-RECS VARIABLE IN THE PRINT RECORD.      *
*                                                               *
*****************************************************************


	PERFORM A1000-HOUSEKEEPING.

	PERFORM A2000-START-TRANSACTION.

	PERFORM A3000-CUSTOM-BATCH.

	PERFORM A9000-EXIT-PROGRAM.


/

A1000-HOUSEKEEPING.

*****************************************************************
*                                                               *
*                      A1000-HOUSEKEEPING                       *
*  -----------------------------------------------------------  *
*  ANY INITIALIZATION OF  APPLICATION SPECIFIC WORKING STORAGE  *
*  FIELDS OR IGNORE CONDITIONS SHOULD BE PERFORMED HERE.        *
*                                                               *
*****************************************************************

        EXEC SQL WHENEVER SQLERROR
             CONTINUE
        END-EXEC.

        EXEC SQL WHENEVER SQLWARNING
             CONTINUE
        END-EXEC.

        EXEC SQL WHENEVER NOT FOUND
             CONTINUE
        END-EXEC.
/

A2000-START-TRANSACTION.

*****************************************************************
*                                                               *
*****************************************************************

        EXEC SQL
          SET TRANSACTION READ WRITE
        END-EXEC.


/
A3000-CUSTOM-BATCH.

*****************************************************************
*                                                               *
*                    A3000-CUSTOM-PRINT                         *
*                                                               *
*                APPLICATION SPECIFIC PROCEDURE                 *
*    ------------------------------------------------------     *
*                                                               *
*****************************************************************

     INITIALIZE WS-CURR-COST-AM.

     EXEC SQL
        SELECT CONV_LBS
        INTO :WS-CURR-COST-AM
        FROM TRUN3
     END-EXEC.

     EXEC SQL
        UPDATE TRUN3 SET CONV_RES = :WS-CURR-COST-AM
     END-EXEC.

     EXEC SQL COMMIT END-EXEC.

/

A9000-EXIT-PROGRAM.
*****************************************************************
*                                                               *
*                    A9000-EXIT-PROGRAM.                        *
*  -----------------------------------------------------------  *
*  THIS PARAGRAPH PASSES CONTROL BACK TO THE CALLING PROGRAM.   *
*                                                               *
*****************************************************************

	EXIT PROGRAM.
3885.14check SQL$USERNOVA::HERREMon May 05 1997 20:5156
I can reproduce the problem only if I link the test program with an old
version of SQL$USER.  Please check what you are linking with.
It looks like it was fixed in the SQL$USER that is in T7.0-05 (FT5?).
It is not fixed in T7.0-03.

When I add
     DISPLAY "VALUE IS " WS-CURR-COST-AM  WITH CONVERSION
between the select and the update, I get
VALUE IS   4440.2066
VALUE IS   4440.2066
with the old SQL$USER and
VALUE IS   4440.2067
VALUE IS   4440.2067
with the new SQL$USER (the one that should be in the 7.0 kit)


Ian:
SQL uses a longword in the message for the update because thats all that
is needed for S9(5)V(4); nine digits fit in a longword.  If I use S9(7)V(4)
instead, SQL uses a quadword in the message.

0000 (00000) BLR$K_VERSION4
0001 (00001) | BLR$K_BEGIN
0002 (00002) |   BLR$K_MESSAGE 1 2
0006 (00006) |   | DSC$K_DTYPE_L 0
0008 (00008) |   | DSC$K_DTYPE_Q -4
000A (00010) |   BLR$K_MESSAGE 2 1
000E (00014) |   | DSC$K_DTYPE_L 0
000E (00014) |   | DSC$K_DTYPE_L 0
0010 (00016) |   BLR$K_BEGIN
0011 (00017) |   | BLR$K_DCL_VARIABLE 2
0014 (00020) |   |   DSC$K_DTYPE_L 0
0016 (00022) |   | BLR$K_ASSIGNMENT
0017 (00023) |   |   BLR$K_LITERAL
0018 (00024) |   |   | DSC$K_DTYPE_L 0  "0"
001E (00030) |   |   BLR$K_VARIABLE 2
0021 (00033) |   | BLR$K_RECEIVE  1
0023 (00035) |   |   BLR$K_BEGIN
0024 (00036) |   |   | BLR$K_FOR
0025 (00037) |   |   |   BLR$K_CONTROL_BITS  8
0027 (00039) |   |   |   BLR$K_RSE  1
0029 (00041) |   |   |   | BLR$K_RELATION TRUN3 1
0031 (00049) |   |   |   BLR$K_END
0032 (00050) |   |   |   BLR$K_BEGIN
0033 (00051) |   |   |   | BLR$K_ASSIGNMENT
0034 (00052) |   |   |   |   BLR$K_LITERAL
0035 (00053) |   |   |   |   | DSC$K_DTYPE_L 0  "1"
003B (00059) |   |   |   |   BLR$K_VARIABLE 2
003E (00062) |   |   |   | BLR$K_MODIFY 1 2
0041 (00065) |   |   |   |   BLR$K_BEGIN
0042 (00066) |   |   |   |   | BLR$K_ASSIGNMENT
0043 (00067) |   |   |   |   |   BLR$K_PARAMETER2 1 1 0
0049 (00073) |   |   |   |   |   BLR$K_FIELD 2 CONV_RES
0054 (00084) |   |   |   |   BLR$K_END
0055 (00085) |   |   |   BLR$K_END
0056 (00086) |   |   BLR$K_END
3885.15Is 6.1-11 SQL$USER too old?M5::LWILCOXHow about Fireworks?Tue May 06 1997 00:2219
                       <<< Note 3885.14 by NOVA::HERRE >>>
                              -< check SQL$USER >-

>>I can reproduce the problem only if I link the test program with an old
>>version of SQL$USER.  Please check what you are linking with.
>>It looks like it was fixed in the SQL$USER that is in T7.0-05 (FT5?).
>>It is not fixed in T7.0-03.

Thanks!!!!

How old is old?  I mean, 6.1-11 seems reasonably new to me.  If it will take
a Version 7.0 or Version 7.1-01 SQL$USER what are the implications of linking
against that yet running with 6.1-11?

Any chance that chocolate speaks louder than bugs?

Thanks for the example too.

Liz
3885.166.1-11 SQL$USER is too oldNOVA::HERRETue May 06 1997 11:358
    First, make sure that this is the cause of the problem the customer is
    having.  They claim to be running 7.0; it could be a case of the same
    symptoms in different environments for different reasons.
    
    The change in SQL$USER was not backported to 6.1.  I built and ran
    the test case with 6.1 linking with the 7.0 SQL$USER, and it worked,
    but I don't know what problems could occur doing this with other code.
3885.17M5::LWILCOXHow about Fireworks?Tue May 06 1997 16:5722
                       <<< Note 3885.16 by NOVA::HERRE >>>
                        -< 6.1-11 SQL$USER is too old >-

>>    having.  They claim to be running 7.0; it could be a case of the same
>>    symptoms in different environments for different reasons.

No, Jay claimed to be using 7.0-01, not the customer.  The customer is on
6.1-11.
    
>>    The change in SQL$USER was not backported to 6.1.  I built and ran
>>    the test case with 6.1 linking with the 7.0 SQL$USER, and it worked,
>>    but I don't know what problems could occur doing this with other code.

OK, how much would it take to backport it?  Meaning how much time/chocolate?
Seriously, I realize a formal bug is the way to go, but even at a sev 2
I'm guessing the customer wont' be happy.  He considers a month too long to
have to wait for the fix.  

Thanks.

liz
3885.18NOVA::SMITHIDon&#039;t understate or underestimate Rdb!Tue May 06 1997 17:527
~  03 WS-CURR-COST-AM                        PIC S9(5)V9(4) COMP-3.

The easiest fix would be to make WS-CURR-COST-AM of type S9(5)V9(6)

(drop the COMP-3 stuff that just uses extra CPU to do the packing)

Ian
3885.19M5::LWILCOXHow about Fireworks?Tue May 06 1997 20:1813
  <<< Note 3885.18 by NOVA::SMITHI "Don't understate or underestimate Rdb!" >>>

>>The easiest fix would be to make WS-CURR-COST-AM of type S9(5)V9(6)

Ian, thanks for the suggestion it's just not easy/practicle/whatever to
have the customer do this in 2,000+ program files, then have to retest
everything.  The analogy I might use is when a customer says that an Rdb
bug would take a "one line fix" and we all know that even if it is just
a one line fix that the code gets run through reg testing again, etc..

I'll bug this if Jay hasn't already.  Thanks again for all the inputs.

Liz
3885.20Fix backported from V7.0NOVA::PFAUFri May 16 1997 17:537
I backported the fix to cosi_iquad_divqlq, in the module QUADARITH.C, from 
V7.0 to V6.1 of COSI. I just checked in the change. It should be available in
the next ECO.

This problem is BUG #490729 in the BUG database.

Ray Pfau