VARCHAR columns in COBOL
Frank Swarbrick
The following pertails to DB2 LUW, but I imagine Oracle is similar (or
perhaps not?).
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 DB2 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_ISSUED_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_NAME) AS PN_LEN FROM
FJSWARBR.ICM_POSPAY_ISSUED_CHECKS 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! :-)
By the by, are the "DB2 for the COBOL Programmer" books from Murach &
Associates the only thing out there with regard to RDBMS COBOL development?
I haven't bought them, but I imagine they're z/OS-centric. Is there
anything out there more Linux/Unix/Windows-centric? Not that there are huge
differences, but just wondering. I'm especially interested in the best way
of handling NULL columns in COBOL. I know how the manuals say to do it, but
to me it seems to be a big PITA, enough that I'm considering now even
allowing columns with NULL values. I am also interested in best development
and testing practices (best way to clone databases for developers, copying
production data to dev, restoring and rerunning, etc.).
Frank
---
Frank Swarbrick
Senior Developer/Analyst - Mainframe Applications
FirstBank Data Corporation - Lakewood, CO USA
Alistair
Frank Swarbrick wrote:
> I'm just learning about embedded SQL, so be gentle...
>
>
> Associates the only thing out there with regard to RDBMS COBOL development?
Be careful using the acronym RDBMS as it refers to a specific product
which is not DB2.
Frank Swarbrick
management systems such as DB2, Oracle and SQL Server?
So what is the generic term?
---
Frank Swarbrick
Senior Developer/Analyst - Mainframe Applications
FirstBank Data Corporation - Lakewood, CO USA
>>> Alistair<alis...@ld50macca.demon.co.uk> 10/06/06 11:46 AM >>>
Richard
Alistair wrote:
> Be careful using the acronym RDBMS as it refers to a specific product
> which is not DB2.
Who told yer that ?
There are products that have RDBMS as _part_ of their name but it is a
generic term that may be applied to any product, including DB2.
Alistair
Frank Swarbrick wrote:
> Are you saying that RDBMS is not a generic acronym for relational database
> management systems such as DB2, Oracle and SQL Server?
> So what is the generic term?
At the risk of starting a Big-Endian v. Little-Endian war: I use rdbms
for relational databases (generic term) and RDBMS for the product by
that name. Strangely, trolls don't know the difference. Not that I'm
saying that you are a troll.....I reserve that particular name for
agents.
Alistair
There is a relational database product that uses RDBMS as it's brand
name. Any company using RDBMS in their product name is infringing
trademark rights for that product. A company may refer to its' product
as being a rdbms or relational dbms but can not use RDBMS in their name.
Richard
Alistair wrote:
> There is a relational database product that uses RDBMS as it's brand
> name. Any company using RDBMS in their product name is infringing
> trademark rights for that product. A company may refer to its' product
> as being a rdbms or relational dbms but can not use RDBMS in their name.
Which company do you think has an actual trademark on the name 'RDBMS'
?
and how do you intend to show your claim is any more than mistaken
assertion.
It is a common term and has been for decades.
Sergey Kashyrin
Unfortunately Cobol PIC X(150) will always have length of 150 in all named
databases (Well, not exactly, depends on MSSQL ESQL implementation you might
be able to use 0-terminated strings).
It is mapped to internal datatype CHAR and not VARCHAR.
To be able to use internal type VARCHAR you need a structure like you
decribed.
> 01 payee-name.
> 49 L PIC S9(4) COMP-5.
> 49 D PIC X(150).
To handle NULLs you'll need indicator variables.
And I think (I might be wrong) that at the moment of Codasyl RDBMS was just
a general term.
Regards,
Sergey
"Frank Swarbrick" <Frank.S...@efirstbank.com> wrote in message
news:4onhr2F...@individual.net...
Richard
Alistair wrote:
> At the risk of starting a Big-Endian v. Little-Endian war: I use rdbms
> for relational databases (generic term) and RDBMS for the product by
> that name. Strangely, trolls don't know the difference. Not that I'm
> saying that you are a troll.....I reserve that particular name for
> agents.
There are two pre-internet uses of the word spelt 'troll'. One rhymes
with doll and is the scandanavian beast that lives in dark holes and is
known from 'Bill Goat Gruff'. The other rhymes with stroll is a method
of fishing where a baited hook is pulled through the water hoping that
unwary fish will bite.
You may notice a similarity between the fishing term and an internet
troll. The troll is the message, not the poster. The poster is the
troller. An internet troll has nothing to do with living under bridges.
Strangely, posters that refer to trolls don't seem to know the
difference.
But then perhaps your message was a troll.
docd...@panix.com
Richard <rip...@Azonic.co.nz> wrote:
>
>Alistair wrote:
>
>> At the risk of starting a Big-Endian v. Little-Endian war: I use rdbms
>> for relational databases (generic term) and RDBMS for the product by
>> that name. Strangely, trolls don't know the difference. Not that I'm
>> saying that you are a troll.....I reserve that particular name for
>> agents.
[snip]
>You may notice a similarity between the fishing term and an internet
>troll. The troll is the message, not the poster. The poster is the
>troller.
Mr Plinston, this assertion appears to be contradicted by
http://catb.org/jargon/html/T/troll.html , which states that 'to utter a
message' is a use of the verb 'troll' while 'a person who chronically
trolls' is 'a troll' (noun).
You are, of course, free to debate the Jargon File as an accepted source
of UseNet terminology; if that is the case it might be interesting to
learn how you conclude that messages can be consume food, since even a
cursory use of the Google archives might indicate that the use 'do not
feed the troll' can be found in postings at least as far back as 1996.
DD
Michael Mattias
>>> for relational databases (generic term) and RDBMS for the product by
>>> that name. Strangely, trolls don't know the difference.
This semantic 'problem' would take care care of itself were posters more
facile in their use of the Engish language.
As a generic term, the acronym "rdbms" is used as a noun.
When "rdbms" is to serve as a brand name its proper use is as an adjective.
Ergo, which 'rdbms' is intended may be inferred from usage.
MCM
Alistair
Two decades, or more ago, I encountered the term RDBMS as the name for
a specific rdbms product. Having seen your other post I did a search
for supporting evidence but failed to find any. Although this has lead
me to doubt my own sanity (not for the first time) I will stand by my
assertion as: absence of proof is not proof of absence.
Alistair
I have only eaten goat once.
Alistair
Sergey Kashyrin wrote:
> And I think (I might be wrong) that at the moment of Codasyl RDBMS was just
> a general term.
>
> Regards,
> Sergey
Thanks, that should settle the RDBMS thread, er, troll.
docd...@panix.com
Alistair <alis...@ld50macca.demon.co.uk> wrote:
>
>Richard wrote:
>> Alistair wrote:
>>
>> > There is a relational database product that uses RDBMS as it's brand
>> > name. Any company using RDBMS in their product name is infringing
>> > trademark rights for that product. A company may refer to its' product
>> > as being a rdbms or relational dbms but can not use RDBMS in their name.
>>
>> Which company do you think has an actual trademark on the name 'RDBMS'
>> ?
>> and how do you intend to show your claim is any more than mistaken
>> assertion.
>>
>> It is a common term and has been for decades.
>
>Two decades, or more ago, I encountered the term RDBMS as the name for
>a specific rdbms product.
I have encountered a few names for specific products... some of said
names, however, are not suitable for printing in a Family-Friendly Forum.
>Having seen your other post I did a search
>for supporting evidence but failed to find any. Although this has lead
>me to doubt my own sanity (not for the first time) I will stand by my
>assertion as: absence of proof is not proof of absence.
Absence of documentation of an assertion, however, leaves just that... an
assertion absent of documentation.
DD
Richard
Alistair wrote:
> Two decades, or more ago, I encountered the term RDBMS as the name for
> a specific rdbms product.
There are plenty of uses of the term as if it were _part_ of the name
of the product. You will see reference to 'Oracle RDBMS', 'OpenEdge
RDBMS', 'Empress RDBMS', perhaps you were just confused.
Alternately it may have been that, say, 'Reflex DBMS' or 'Rational
DBMS', where Reflex and Rational _are_ trade names, may have casually
abbreviated to RDBMS when referring to the product prior to Relational
being commonly used. And then you got confused.
When using an acronym it is conventional to use the letters as upper
case. The acronym is RDBMS.
> Having seen your other post I did a search
> for supporting evidence but failed to find any. Although this has lead
> me to doubt my own sanity (not for the first time) I will stand by my
> assertion as: absence of proof is not proof of absence.
I think you will find 'proof of absence' in another thread where there
is no registration of the trademark that you asserted.
Richard
docd...@panix.com wrote:
> >You may notice a similarity between the fishing term and an internet
> >troll. The troll is the message, not the poster. The poster is the
> >troller.
>
> Mr Plinston, this assertion appears to be contradicted by
> http://catb.org/jargon/html/T/troll.html , which states that 'to utter a
> message' is a use of the verb 'troll' while 'a person who chronically
> trolls' is 'a troll' (noun).
Exactly my point. People who are unfamiliar with technical fishing
terms and remember 'Billy Goat Gruff' being read to them will make the
wrong association when they see the word written. It has obviously
been wrong for so long that it is irrepairable.
> if that is the case it might be interesting to
> learn how you conclude that messages can be consume food,
Well, duh, messages can contain cookies.
(Multics 'cookie' goes back to 1970).
Richard
docd...@panix.com wrote:
> >You may notice a similarity between the fishing term and an internet
> >troll. The troll is the message, not the poster. The poster is the
> >troller.
>
> Mr Plinston, this assertion appears to be contradicted by ...
For those who used to listen to the BBC's 'Round the Horne' in the 60s,
and especially to the parts played by Kennith Williams, they will
remember that 'troll', or especially 'trolling about', was used in the
same sense as the fishing usage but adapted to a different community:
dragging some 'bait' and hoping to attract a catch.
Bill Bailey is in the same field as Williams but he is a troll for a
completely different reason.
docd...@panix.com
Richard <rip...@Azonic.co.nz> wrote:
>
>docd...@panix.com wrote:
>
>> >You may notice a similarity between the fishing term and an internet
>> >troll. The troll is the message, not the poster. The poster is the
>> >troller.
>>
>> Mr Plinston, this assertion appears to be contradicted by
>> http://catb.org/jargon/html/T/troll.html , which states that 'to utter a
>> message' is a use of the verb 'troll' while 'a person who chronically
>> trolls' is 'a troll' (noun).
>
>Exactly my point.
So good of you to agree... but it's nice to see you trying so hard. Keep
up the good efforts.
DD
Robert Jones
I have heard of a product called "SQL Server" whose originator would
probably like people to think that they are the suppliers of SQL, etc.
Robert
Alistair
Robert Jones wrote:
> I have heard of a product called "SQL Server" whose originator would
> probably like people to think that they are the suppliers of SQL, etc.
>
> Robert
>
Is there any pie into which they won't stick their fingers? BTW
Squirrel or Sequel?
Richard
Robert Jones wrote:
> I have heard of a product called "SQL Server" whose originator would
> probably like people to think that they are the suppliers of SQL, etc.
The product name is actually "Microsoft SQL Server", the product is
actually Sybase.
Frank Swarbrick
>Frank,
>
>Unfortunately Cobol PIC X(150) will always have length of 150 in all named
>databases (Well, not exactly, depends on MSSQL ESQL implementation you
might
>be able to use 0-terminated strings).
>It is mapped to internal datatype CHAR and not VARCHAR.
>To be able to use internal type VARCHAR you need a structure like you
>decribed.
>> 01 payee-name.
>> 49 L PIC S9(4) COMP-5.
>> 49 D PIC X(150).
>
>To handle NULLs you'll need indicator variables.
What I'm getting at here, really, is not that I don't know how to do it,
just that I think that doing it that way is a pain. I guess if VARCHAR and
NULL are used minimally it would be OK, but using the COBOL VARCHAR
structure and COBOL indicator variables all over the place seems to me to
not be an efficient use of my time. I thought perhaps I was just missing a
better way to do things, but perhaps not.
Another related question... In our existing DL/I database we have a segment
that has over 300 fields in it. Of course not all programs accessing this
segment use all 300 fields (or even close to all 300, generally). This
segment is mapped directly to a COBOL copybook 01-level, with each field
being an 05-level. So all I have to do to read a segment is (assuming
record-area is the 01 level describing the segment):
move account-nbr to ssa-account-nbr
call 'cbltdli' using ghu, pcb-mask, record-area, ssa-record
and to update the segment we do
call 'cbltdli' using repl, pcb-mask, record-area
With relational databases it's a whole different ballgame. I am not
unwilling to learn. I just want to get some idea of best practices. For
instance, assuming the copybook above is written properly I know it's
*possible* to do something like the following:
exec sql select *
into record-area
from ACCOUNTS
where ACCOUNTS.ACCOUNT_NBR = :account-nbr
end-exec
This seems to be discouraged, though, in that all the examples I see specify
both the individual columns and the individual host variables. But for 300+
columns? Seems to me to not necessarily be the way to go.
And what about updates? As far as I can tell you can't update an entire row
in the same manner as you can select an entire row (using '*'). Is this
true? Do you really have to specify SET column_name = :column-var for each
column in a row that you wish to update? Again, seems like a major pain.
Don't get me wrong, I understand why these things work the way they do.
It's just quite a bit different than how we're doing things now, and in many
cases appears to be a lot more "grunt work", so I just want to make sure I'm
not missing something totally obvious.
Richard
Frank Swarbrick wrote:
> What I'm getting at here, really, is not that I don't know how to do it,
> just that I think that doing it that way is a pain. I guess if VARCHAR and
> NULL are used minimally it would be OK, but using the COBOL VARCHAR
> structure and COBOL indicator variables all over the place seems to me to
> not be an efficient use of my time. I thought perhaps I was just missing a
> better way to do things, but perhaps not.
I have a Python program around here that generates appropriate COBOL
copybooks. Being a dynamic language Python can access the database (I
use PostgreSQL) to obtain the column names and data types from the
returned data and use these when creating the COBOL source code.
> With relational databases it's a whole different ballgame. I am not
> unwilling to learn. I just want to get some idea of best practices. For
> instance, assuming the copybook above is written properly I know it's
> *possible* to do something like the following:
>
> exec sql select *
> into record-area
> from ACCOUNTS
> where ACCOUNTS.ACCOUNT_NBR = :account-nbr
> end-exec
>
> This seems to be discouraged, though, in that all the examples I see specify
> both the individual columns and the individual host variables. But for 300+
> columns? Seems to me to not necessarily be the way to go.
Not just the individual column names and host variable names, but also
indicators when value might be NULL. It is easy to generate the code.
> And what about updates? As far as I can tell you can't update an entire row
> in the same manner as you can select an entire row (using '*'). Is this
> true? Do you really have to specify SET column_name = :column-var for each
> column in a row that you wish to update? Again, seems like a major pain.
Yes, it is a pain.
This version only generates the structure.
gencobol.py
-------------------------------------------------------------------------
#!/bin/env python
system = 'GenCobol'
version = '0.1'
versdate = '9 July 2003'
import sys
import os
import os.path
import string
import time
import mx.DateTime
from pyPgSQL import PgSQL
import azgencfg
import azgenlog
class accessdb:
def __init__(self, cfg):
self.cfg = cfg
self.dsn = "%s:%s:%s:%s:%s" % (self.cfg.host,
"",
self.cfg.database,
self.cfg.username,
self.cfg.password)
def get_table(self, table):
self.db = PgSQL.connect(self.dsn)
self.st = self.db.cursor()
result = []
self.st.execute("SELECT * FROM %s LIMIT 1" % (table))
record = self.st.fetchone()
result = self.st.description
self.st.close()
self.db.close()
return result
def main():
cfg = azgencfg.gencfg(section='accruals')
print "%s version %s, %s" % (system, version, versdate)
for (table, prefix) in cfg.tables:
process_table(cfg, table, prefix)
def process_table(cfg, table, prefix):
cobfd = open(os.path.join(cfg.coboldir, table + '.sql', "w"))
db = accessdb(cfg)
description = db.get_table(table)
cobfd.write('\n')
cob = " 01 %s." % (prefix + string.replace(table, '_', '-'))
print cob
cobfd.write('%s\n' % (cob))
for (name, type_code, display_size, internal_size, precision,
scale, null_ok, x) in description:
field = string.replace(name, '_', '-')
if ( type_code == PgSQL.PG_NUMERIC ):
if ( scale == 0 ):
cob = " 03 %-22s PIC S9(%d)." % (prefix +
field, internal_size)
else:
cob = " 03 %-22s PIC S9(%d)V9(%d)." %
(prefix + field, internal_size - scale, scale)
print cob
cobfd.write('%s\n' % (cob))
elif ( type_code == PgSQL.PG_VARCHAR ):
cob = " 03 %s." % (prefix + field + '-var')
print cob
cobfd.write('%s\n' % (cob))
cob = " 49 %-22s PIC S9(4) BINARY." % (prefix +
field + '-len')
print cob
cobfd.write('%s\n' % (cob))
cob = " 49 %-22s PIC X(%d)." % (prefix + field,
display_size)
print cob
cobfd.write('%s\n' % (cob))
elif ( type_code == PgSQL.PG_DATE ):
cob = " 03 %-22s PIC S9(9)." % (prefix + field)
print cob
cobfd.write('%s\n' % (cob))
else:
print "%-20s %-10s %5d %5d" % (name, type_code,
display_size, internal_size)
cobfd.write('\n')
cobfd.close()
if ( __name__ == "__main__" ):
main()
---------------------------------------------------------------------------------
azgenlog.py just write out a log file
azgencfg.py reads the configuration file which is like this:
gencobol.cfg
--------------------------------------------------------------------
[default]
host=locahost
username=deskware
password=
account=
[accruals]
database=accruals
#coboldir=cobol
coboldir=/home/riplin/workspace/accruals/include
tables=control,lfuser,client,site,supplier,contract,user_contract,purchase,po_line,invoice,inv_line
[accruals-prefix]
control=CN-
lfuser=LU-
client=CL-
site=SI-
supplier=SU-
contract=CO-
user_contract=UC-
purchase=PO-
po_line=PL-
invoice=IN-
inv_line=IL-
[end]
Frank Swarbrick
>
>Frank Swarbrick wrote:
>
>> What I'm getting at here, really, is not that I don't know how to do it,
>> just that I think that doing it that way is a pain. I guess if VARCHAR
and
>> NULL are used minimally it would be OK, but using the COBOL VARCHAR
>> structure and COBOL indicator variables all over the place seems to me
to
>> not be an efficient use of my time. I thought perhaps I was just missing
a
>> better way to do things, but perhaps not.
>
>I have a Python program around here that generates appropriate COBOL
>copybooks. Being a dynamic language Python can access the database (I
>use PostgreSQL) to obtain the column names and data types from the
>returned data and use these when creating the COBOL source code.
Actually, DB2 already comes with a program to do this (db2dclgn).
>> With relational databases it's a whole different ballgame. I am not
>> unwilling to learn. I just want to get some idea of best practices.
For
>> instance, assuming the copybook above is written properly I know it's
>> *possible* to do something like the following:
>>
>> exec sql select *
>> into record-area
>> from ACCOUNTS
>> where ACCOUNTS.ACCOUNT_NBR = :account-nbr
>> end-exec
>>
>> This seems to be discouraged, though, in that all the examples I see
specify
>> both the individual columns and the individual host variables. But for
300+
>> columns? Seems to me to not necessarily be the way to go.
>
>Not just the individual column names and host variable names, but also
>indicators when value might be NULL. It is easy to generate the code.
I'm not sure of this "generate the code" you are referring to. Do you mean
the SELECT statement?
>> And what about updates? As far as I can tell you can't update an entire
row
>> in the same manner as you can select an entire row (using '*'). Is this
>> true? Do you really have to specify SET column_name = :column-var for
each
>> column in a row that you wish to update? Again, seems like a major
pain.
>
>Yes, it is a pain.
Lovely! :-)