fixed length host variables for VARCHAR fields

[Frank Swarbrick]

I'm just learning about embedded SQL, so be gentle...

My basic question is, if I use a fixed length host variable for a column
defined as VARCHAR, will trailing spaces be removed (or not) upon INSERT or
UPDATE of this column? I tried it, and it appears they are *not* stripped.
However, the Programming Client Applications manual leads me to believe that
the spaces should be stripped. A quote from that manual:

-------------------------
In a CONNECT statement, such as shown below, COBOL character string host
variables dbname and userid will have any trailing blanks removed before
processing:
EXEC SQL CONNECT TO :dbname USER :userid USING :p-word END-EXEC.

However, because blanks can be significant in passwords, the p-word host
variable should be declared as a VARCHAR data item, so that your application
can explicitly indicate the significant password length for the CONNECT
statement as follows:
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 dbname PIC X(8).
01 userid PIC X(8).
01 p-word.
49 L PIC S9(4) COMP-5.
49 D PIC X(18).
EXEC SQL END DECLARE SECTION END-EXEC.

PROCEDURE DIVISION.
MOVE "sample" TO dbname.
MOVE "userid" TO userid.
MOVE "password" TO D OF p-word.
MOVE 8 TO L of p-word.
EXEC SQL CONNECT TO :dbname USER :userid USING :p-word END-EXEC.
-------------------------

Now here is some of my actual code:

EXEC SQL BEGIN DECLARE SECTION END-EXEC.

01 ppissue-data.
05 PPISSUE-ACCOUNT-X pic x(10).

05 PPISSUE-SERIAL-NBR PIC S9(09) comp-3.

05 PPISSUE-AMT PIC S9(9)V99 comp-3.

05 PPISSUE-ISSUE-DATE PIC X(26).

05 PPISSUE-PAYEE PIC X(150).

01 ppissue-ind-table.
05 ppissue-ind pic s9(4) comp-5
occurs 5.
EXEC SQL END DECLARE SECTION END-EXEC.

move "this is a test" to ppissue-payee
EXEC SQL UPDATE ICM_POSPAY_ISSU ED_CHECKS
SET PAYEE_NAME = :ppissue-payee
WHERE ACCOUNT_NUMBER = :ppissue-account-x
AND SERIAL_NUMBER = :ppissue-serial-nbr
END-EXEC

Column PAYEE_NAME is VARCHAR(150).

I was hoping that the PAYEE_NAME column for this update would show as a
length of 14, but it appears to have a length of 150. At least,this is what
it appears to me from the following query:

SELECT PAYEE_NAME, LENGTH(PAYEE_NA ME) AS PN_LEN FROM
FJSWARBR.ICM_PO SPAY_ISSUED_CHE CKS WHERE PAYEE_NAME = 'new payee here'

PAYEE_NAME

PN_LEN
----------------------------------------------------------------------------
--------------------------------------------------------------------------
-----------
new payee here

150

If this does not, in fact, strip the trailing spaces, then I have to wonder
why its stressed that the password on a connect should always be a "variable
length" host variable (leading length indicator) instead of a fixed length
one.

Personally, I am not looking forward to a whole bunch of things like the
following:

01 payee-name.
49 L PIC S9(4) COMP-5.
49 D PIC X(150).

move 'this is a test' to D of payee-name
move zero to trailing-spaces
inspect function reverse(D of payee-name) tallying trailing-spaces for
leading spaces
compute L of payee-name = function length(D of payee-name) -
trailing-spaces

Please tell me I'm just missing something obvious! :-)

Frank

---
Frank Swarbrick
Senior Developer/Analyst - Mainframe Applications
FirstBank Data Corporation - Lakewood, CO USA

[Phil Sherman]

Its been a while since I've worked with Cobol but I believe you haven't
missed anything. There are, however, other ways to do this that are a
bit easier.

MOVE SPACES TO D OF PAYEE-NAME.
UNSTRING 'This is a test' delimited by ' ' INTO D OF PAYEE-NAME
COUNT IN L OF PAYEE-NAME.

The trick here is to use a delimiter that includes more spaces than any
consecutive group of them that can occur in the source string. Normal
text always uses a single space between words with an occasional data
entry error placing two spaces between words. If the text almost fills
the field, then you may end up with a couple of extra blanks stored in
the table.

Assuming that you have an RTRIM SQL function available, you should be
able to use:
EXEC SQL UPDATE ICM_POSPAY_ISSU ED_CHECKS
SET PAYEE_NAME = RTRIM(:ppissue-payee)
WHERE ACCOUNT_NUMBER = :ppissue-account-x
AND SERIAL_NUMBER = :ppissue-serial-nbr
END-EXEC

Phil Sherman
Frank Swarbrick wrote:

I'm just learning about embedded SQL, so be gentle...

My basic question is, if I use a fixed length host variable for a column
defined as VARCHAR, will trailing spaces be removed (or not) upon INSERT or
UPDATE of this column? I tried it, and it appears they are *not* stripped.
However, the Programming Client Applications manual leads me to believe that
the spaces should be stripped. A quote from that manual:

-------------------------
In a CONNECT statement, such as shown below, COBOL character string host
variables dbname and userid will have any trailing blanks removed before
processing:
EXEC SQL CONNECT TO :dbname USER :userid USING :p-word END-EXEC.

However, because blanks can be significant in passwords, the p-word host
variable should be declared as a VARCHAR data item, so that your application
can explicitly indicate the significant password length for the CONNECT
statement as follows:
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 dbname PIC X(8).
01 userid PIC X(8).
01 p-word.
49 L PIC S9(4) COMP-5.
49 D PIC X(18).
EXEC SQL END DECLARE SECTION END-EXEC.

PROCEDURE DIVISION.
MOVE "sample" TO dbname.
MOVE "userid" TO userid.
MOVE "password" TO D OF p-word.
MOVE 8 TO L of p-word.
EXEC SQL CONNECT TO :dbname USER :userid USING :p-word END-EXEC.
-------------------------

Now here is some of my actual code:

EXEC SQL BEGIN DECLARE SECTION END-EXEC.

01 ppissue-data.
05 PPISSUE-ACCOUNT-X pic x(10).

05 PPISSUE-SERIAL-NBR PIC S9(09) comp-3.

05 PPISSUE-AMT PIC S9(9)V99 comp-3.

05 PPISSUE-ISSUE-DATE PIC X(26).

05 PPISSUE-PAYEE PIC X(150).

01 ppissue-ind-table.
05 ppissue-ind pic s9(4) comp-5
occurs 5.
EXEC SQL END DECLARE SECTION END-EXEC.

move "this is a test" to ppissue-payee
EXEC SQL UPDATE ICM_POSPAY_ISSU ED_CHECKS
SET PAYEE_NAME = :ppissue-payee
WHERE ACCOUNT_NUMBER = :ppissue-account-x
AND SERIAL_NUMBER = :ppissue-serial-nbr
END-EXEC

Column PAYEE_NAME is VARCHAR(150).

I was hoping that the PAYEE_NAME column for this update would show as a
length of 14, but it appears to have a length of 150. At least,this is what
it appears to me from the following query:

SELECT PAYEE_NAME, LENGTH(PAYEE_NA ME) AS PN_LEN FROM
FJSWARBR.ICM_PO SPAY_ISSUED_CHE CKS WHERE PAYEE_NAME = 'new payee here'

PAYEE_NAME

PN_LEN
----------------------------------------------------------------------------
--------------------------------------------------------------------------
-----------
new payee here

150

If this does not, in fact, strip the trailing spaces, then I have to wonder
why its stressed that the password on a connect should always be a "variable
length" host variable (leading length indicator) instead of a fixed length
one.

Personally, I am not looking forward to a whole bunch of things like the
following:

01 payee-name.
49 L PIC S9(4) COMP-5.
49 D PIC X(150).

move 'this is a test' to D of payee-name
move zero to trailing-spaces
inspect function reverse(D of payee-name) tallying trailing-spaces for
leading spaces
compute L of payee-name = function length(D of payee-name) -
trailing-spaces

Please tell me I'm just missing something obvious! :-)

Frank

---
Frank Swarbrick
Senior Developer/Analyst - Mainframe Applications
FirstBank Data Corporation - Lakewood, CO USA

[Frank Swarbrick]

Phil Sherman<ps***** *@ameritech.net 10/10/06 10:03 AM >>>

>Its been a while since I've worked with Cobol but I believe you haven't
missed anything. There are, however, other ways to do this that are a
bit easier.

MOVE SPACES TO D OF PAYEE-NAME.
UNSTRING 'This is a test' delimited by ' ' INTO D OF PAYEE-NAME
COUNT IN L OF PAYEE-NAME.

The trick here is to use a delimiter that includes more spaces than any
consecutive group of them that can occur in the source string. Normal
text always uses a single space between words with an occasional data
entry error placing two spaces between words. If the text almost fills
the field, then you may end up with a couple of extra blanks stored in
the table.

Yeah, I'm not a fan of that one, because of the reasons you mention, among
others.

>Assuming that you have an RTRIM SQL function available, you should be
able to use:
EXEC SQL UPDATE ICM_POSPAY_ISSU ED_CHECKS
SET PAYEE_NAME = RTRIM(:ppissue-payee)
WHERE ACCOUNT_NUMBER = :ppissue-account-x
AND SERIAL_NUMBER = :ppissue-serial-nbr
END-EXEC

Now this is what I'm looking for! Thank you. Very simple, and no screwing
around with COBOL which doesn't really lend itself to such things.

Thanks for the tip!
Frank
---
Frank Swarbrick
Senior Developer/Analyst - Mainframe Applications
FirstBank Data Corporation - Lakewood, CO USA