Re: a cheep interface to database
From: LX-i (lxi0007_at_netscape.net)
Date: 07/07/04
- Next message: LX-i: "Re: Cobol Opportunity Available"
- Previous message: docdwarf_at_panix.com: "Re: In answer to RW - again (was: Sorts (revised)"
- In reply to: Warren Simmons: "Re: a cheep interface to database"
- Next in thread: Pete Dashwood: "Re: a cheep interface to database"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Date: Tue, 06 Jul 2004 21:15:27 -0500
Warren Simmons wrote:
> LX-i wrote:
>
>> I'm not familiar with either of these tools. I just don't see SQL as
>> that tough.
>>
>> SQL COBOL
>> ------ -------
>> SELECT = READ
>> INSERT = WRITE
>> UPDATE = REWRITE
>> DELETE = DELETE
[snip]
>> I know this may not be what you're looking for, but I think involving
>> a third-party tool, unless you're only going to do this once and never
>> again (and never maintain it), it might be worth your effort to look
>> at it. If you've been coding a while, it shouldn't take long at all
>> to get up to speed on the basics.
>
> I thought your reply was great. I learned something I suspected
> all along. It's hard to review a method without examples, and
> what you did here is deserving a lot os cheers.
I'm glad it was beneficial. It does get a little more tricky when you
get into things like null indicators and date/time values, but there are
nuances in any technology. :)
Just to illustrate a little on the above - "indicator variables" are
variables you can use when retrieving data to or storing data in the
database. They're signed-leading-separate usage display numeric items.
These indicator variables have a couple of uses, and although you can
get along without them, if either of these behaviors are needed, you'll
get a runtime error.
- If a column is null, they contain -1. (This is important because
the data item you thought was getting the data may or may not be changed
- in my normal day-to-day environment, it isn't. I can't speak to
others since I haven't used them yet.)
- If there are characters truncated, the indicator variable will
contain the number of characters dropped.
So, given the table...
8<------ Table ------->8
create table people_table (
name varchar(50) not null primary key,
address varchar(255),
city varchar(50),
st char(02),
zip varchar(09),
last_date date; )
8<-------
...and the COBOL definitions....
8<------- Definition ------->8
01 my-name pic x(50) value "name".
01 my-address pic x(255) value "address".
01 my-address-ind pic s9(03) sign leading separate.
01 my-city pic x(25) value "city".
01 my-city-ind pic s9(03) sign leading separate.
01 my-st pic x(02) value "st".
01 my-st-ind pic s9(03) sign leading separate.
01 my-zip pic x(09) value "zip".
01 my-zip-ind pic s9(03) sign leading separate.
01 my-last-date pic x(26).
01 my-last-date-ind pic s9(03) sign leading separate.
8<--------
...and the following row in the table...
8<-------- Table data -------->8
name = "Daniel"
address = "123 Easy Street"
city = "abcdefghijklmnopqrstuvwxyz123"
st = "NY"
zip = NULL
8<--------
...the following SQL statement (known as a "singleton" select, as
opposed to a cursor, in my last example - you'll blow up if you try to
use one of these to go after more than one row, at least in some
implementations)...
8<-------- Embedded SQL --------->8
exec sql
select address, city, st, zip
into :my-address :my-address-ind,
:my-city :my-city-ind,
:my-st :my-st-ind,
:my-zip :my-zip-ind
from people_table
where name = "Daniel"
end-exec
8<---------
...here are the values for each COBOL variable.
8<--------- Variable values --------->8
my-address = "123 Easy Street"
my-address-ind = +0
my-city = "abcdefghijklmnopqrstuvwxy"
my-city-ind = +4
my-st = "NY"
my-st-ind = +0
my-zip = "zip" (initial value, not changed because it was null)
my-zip-ind = -1
8<---------
Dates are generally stored in internal binary format, but they're
exposed to the COBOL program as strings. This is pretty easy for
retrievals - it can be up to pic X(26), with the format
YYYY-MM-DD HH:MM:SS.PPPPPP
(dashes, colons, and dots included). However, going into the database,
you have to "cast" the string into the appropriate data type. So,
utilizing that last_date field we haven't referenced yet, let's update
to yesterday's date, by casting a string. (You can do it with intrinsic
functions and intervals, but that's something I rarely do, because this
way seems a lot more maintainable, especially to moderate neophytes...)
8<--------- COBOL / Embedded SQL ---------->8
*> This stores yesterday's date
move "2004-07-05" to my-last-date
exec sql
update people_table
set last_date = cast(:my-last-date as date)
where name = "Daniel"
end-exec
*> Now, we're going to set it to null, using the indicator variable
move -1 to my-last-date-ind
*> Note that it doesn't matter that my-last-date still has a value
exec sql
update people_table
set last_date = cast(:my-last-date :my-last-date-ind as date)
where name = "Daniel"
end-exec
8<---------
Hope that helps - maybe answers some questions that came to mind while
you read the other post. :)
-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~ / \ / ~ Live from Montgomery, AL! ~ ~ / \/ o ~ ~ ~ / /\ - | ~ LXi0007@Netscape.net ~ ~ _____ / \ | ~ http://www.knology.net/~mopsmom/daniel ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ I do not read e-mail at the above address ~ ~ Please see website if you wish to contact me privately ~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
- Next message: LX-i: "Re: Cobol Opportunity Available"
- Previous message: docdwarf_at_panix.com: "Re: In answer to RW - again (was: Sorts (revised)"
- In reply to: Warren Simmons: "Re: a cheep interface to database"
- Next in thread: Pete Dashwood: "Re: a cheep interface to database"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Relevant Pages
|
