Home > Archive > Cobol > May 2006 > cobol embedded sql source example
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
cobol embedded sql source example
|
|
| Serge Lacombe 2006-05-03, 7:55 am |
| Hi,
I need to obtain the list of elements (name of column,type..) of an Oracle
table.
Can it be done in a cobol embedded sql program?
If yes, a source dode example would be appreciated.
Thanks.
| |
| arrbee 2006-05-03, 7:55 am |
| I don't know if it is allowed to access an Oracle table via a COBOL
program. To my knowledge we can access DB2 table using embedded SQL
queries.
Let me know if my understanding is wrong.
Thanks.
| |
| Michael Mattias 2006-05-03, 7:55 am |
| "arrbee" <arrbee@gmail.com> wrote in message
news:1146658213.671007.236630@u72g2000cwu.googlegroups.com...
> I don't know if it is allowed to access an Oracle table via a COBOL
> program. To my knowledge we can access DB2 table using embedded SQL
> queries.
>
> Let me know if my understanding is wrong.
From all your posts on the subject of database access from COBOL this AM,
I'm pretty sure your understanding is NOT wrong, since you clearly have
insufficient understanding against which to make a statistically valid
comparison. ("Lacking" would be a better word choice than would be "wrong").
Having gotten in my cheap shot for today...
"Assuming" this is on IBM mainframe (as with other questions) and you want
to use ESQL, you need a precompiler for Oracle to access Oracle databases
from your COBOL program, just as you need the DB2 precompiler to access DB2
databases from your COBOL program. Check with your system administrator for
the availability of such a tool.
On your simple little program from the other message....
" have to read a flat file (100,000 records) and update a DB2 table
with the values from flat file. So, which call is preferable here?
Static or Dynamic? Any reasoning on which type of call to be chosen?"
Huh? What are you 'calling?' This is about a twenty-line Procedure
Division COBOL/DB2 program:
PERFORM UNTIL EOF-FLATFILE
READ flatfile
MOVE to DB2 table working storage
Execute Insert
END-PERFORM
"What care should be taken in case of an abend (program goes down after
updating 50,000 records)? How to restart it in such a situation."
See BEGIN TRANSACTION, COMMIT, ROLLBACK, END TRANSACTION in relevant manual.
Restarting requires the same logic as though you were writing to a QSAM/VSAM
dataset instead of a database. In this case I'd probably use some kind of
"record count" and maybe commit my database writes every 500 or so records,
separately saving/displaying/otherwise reporting how many records from flat
file I've actually processed and committed to the database. To restart, you
skip as many records as were successfully processed in the aborted run.
Hell, it's a thought.
MCM
| |
|
| In article < cKmdnSCeW8FeBsXZnZ2dnUVZ_smdnZ2d@umontre
al.ca>,
Serge Lacombe <serge.lacombe@umontreal.ca> wrote:
>Hi,
>
>I need to obtain the list of elements (name of column,type..) of an Oracle
>table.
On what platform/compiler/version of Oracle?
>
>Can it be done in a cobol embedded sql program?
Perhaps.
>
>If yes, a source dode example would be appreciated.
Please post what you have already tried so that others might not waste
time duplicating your efforts.
DD
| |
| Günther De Vogelaere 2006-05-03, 7:55 am |
|
"Serge Lacombe" <serge.lacombe@umontreal.ca> schreef in bericht
news:cKmdnSCeW8FeBsXZnZ2dnUVZ_smdnZ2d@um
ontreal.ca...
> Hi,
>
> I need to obtain the list of elements (name of column,type..) of an Oracle
> table.
>
> Can it be done in a cobol embedded sql program?
>
> If yes, a source dode example would be appreciated.
>
> Thanks.
>
The sql statement you could use:
select * from all_tab_columns
where table_name = 'insertherethetableyouwanttosee'
(replace the * by the columnames of the table all_tab_columns you want to
see)
Declare your hostvariables:
<http://www.lc.leidenuniv.nl/awcours...09/pco05sql.htm>
Opening a cursor, loop through cursor, and close it, should be trivial:
<http://www.lc.leidenuniv.nl/awcours...co05sql.htm#817>
You surely can find examples of cursors that use other tables.
| |
|
| In article <_y16g.1070$fb2.92@newssvr27.news.prodigy.net>,
Michael Mattias <michael.mattias@gte.net> wrote:
>"arrbee" <arrbee@gmail.com> wrote in message
>news:1146658213.671007.236630@u72g2000cwu.googlegroups.com...
[snip]
>
>From all your posts on the subject of database access from COBOL this AM,
>I'm pretty sure your understanding is NOT wrong, since you clearly have
>insufficient understanding against which to make a statistically valid
>comparison. ("Lacking" would be a better word choice than would be "wrong").
>
>Having gotten in my cheap shot for today...
Oh boy... the rest of the world will escape unscathed!
[snip]
>On your simple little program from the other message....
>
>" have to read a flat file (100,000 records) and update a DB2 table
>with the values from flat file. So, which call is preferable here?
>Static or Dynamic? Any reasoning on which type of call to be chosen?"
>
>Huh? What are you 'calling?' This is about a twenty-line Procedure
>Division COBOL/DB2 program:
>
>PERFORM UNTIL EOF-FLATFILE
> READ flatfile
> MOVE to DB2 table working storage
> Execute Insert
>END-PERFORM
>
>"What care should be taken in case of an abend (program goes down after
>updating 50,000 records)? How to restart it in such a situation."
>
>See BEGIN TRANSACTION, COMMIT, ROLLBACK, END TRANSACTION in relevant manual.
>
>Restarting requires the same logic as though you were writing to a QSAM/VSAM
>dataset instead of a database. In this case I'd probably use some kind of
>"record count" and maybe commit my database writes every 500 or so records,
>separately saving/displaying/otherwise reporting how many records from flat
>file I've actually processed and committed to the database.
Hmmmmm... our experiences are different, Mr Mattias. I ran into such a
situation a few years back, where the client said 'All ya gotta do is
merge data from these various datasets into a single record and INSERT it
into the tables.'
I said 'Ummmmm... initial analysis shows that one table will be getting
about twenty million inserts and another about thirty million... it might
be more efficient for me to use a COBOL program to generate a flat file
and then use a utility to load that into the table.'
The response was 'When we want your analysis we'll give it to you, just do
what we've asked for.'
After a few w s' worth of effort it was shown that Ops would cancel the
job after it ran longer than 22.5 hours. I was then given permission to
try it my way... after about a day-and-a-half I demonstrated that
end-to-end runtime was a couple-three hours and change, depending on
system load.
>To restart, you
>skip as many records as were successfully processed in the aborted run.
Ahhhhh, the advantage to the situation I described above was that the
tables were being loaded from empty.
DD
| |
| Michael Mattias 2006-05-03, 7:55 am |
| <docdwarf@panix.com> wrote in message news:e3ab8l$kmh$1@reader1.panix.com...
> Hmmmmm... our experiences are different, Mr Mattias....
>
> Ahhhhh, the advantage to the situation I described above was that the
> tables were being loaded from empty.
I think we can agree on one thing: the 'application description' provided
was a tad 'thin' in that all we know is that there about 100K flat file
records. How many INSERTs that number of flat file records results remains
a secret... for that matter, it may not even be INSERTs, it could be UPDATEs
or DELETEs for all we know....
MCM
..
| |
|
| In article <Kn26g.1078$fb2.502@newssvr27.news.prodigy.net>,
Michael Mattias <michael.mattias@gte.net> wrote:
><docdwarf@panix.com> wrote in message news:e3ab8l$kmh$1@reader1.panix.com...
>
>I think we can agree on one thing: the 'application description' provided
>was a tad 'thin' in that all we know is that there about 100K flat file
>records. How many INSERTs that number of flat file records results remains
>a secret... for that matter, it may not even be INSERTs, it could be UPDATEs
>or DELETEs for all we know....
Well... the description may have been thin, aye... but it was thick enough
that a certain smell might be discerned as emanating therefrom. Let's
see...
'I have to read a flat file (100,000 records) and update a DB2
table with the values from flat file'.
I've not run across a situation where a DELETE was described as an
'update... with the values from'... but where are the Tech Writers of
yesteryear? INSERTs and UPDATEs, without a question.
DD
| |
| Binyamin Dissen 2006-05-03, 6:55 pm |
| On Wed, 3 May 2006 08:02:11 -0400 "Serge Lacombe" <serge.lacombe@umontreal.ca>
wrote:
:>I need to obtain the list of elements (name of column,type..) of an Oracle
:>table.
In DB2 one would do a prepare of a "SELECT *" to get this information.
I presume Oracle has a similar type of function.
Of course, if the program has authority to read the catalog tables, it may be
easier.
:>Can it be done in a cobol embedded sql program?
I don't think COBOL is able to handle the DB2 one.
:>If yes, a source dode example would be appreciated.
I am sure that it would be.
--
Binyamin Dissen <bdissen@dissensoftware.com>
http://www.dissensoftware.com
Director, Dissen Software, Bar & Grill - Israel
Should you use the mailblocks package and expect a response from me,
you should preauthorize the dissensoftware.com domain.
I very rarely bother responding to challenge/response systems,
especially those from irresponsible companies.
| |
| Michael Mattias 2006-05-03, 6:55 pm |
| A "SELECT * from tablename" may not return the column names if the table
is empty. If available on whatever system you are working on a catalog
function might be a better choice.
| |
| Binyamin Dissen 2006-05-03, 6:55 pm |
| On Wed, 03 May 2006 15:59:31 GMT "Michael Mattias" <michael.mattias@gte.net>
wrote:
:>A "SELECT * from tablename" may not return the column names if the table
:>is empty. If available on whatever system you are working on a catalog
:>function might be a better choice.
You don't execute it, you prepare it.
--
Binyamin Dissen <bdissen@dissensoftware.com>
http://www.dissensoftware.com
Director, Dissen Software, Bar & Grill - Israel
Should you use the mailblocks package and expect a response from me,
you should preauthorize the dissensoftware.com domain.
I very rarely bother responding to challenge/response systems,
especially those from irresponsible companies.
| |
| Michael Mattias 2006-05-03, 6:55 pm |
| "Binyamin Dissen" <postingid@dissensoftware.com> wrote in message
news:0bmh52hnrqjpkdkv9icdcodpge1le2ff51@
4ax.com...
> On Wed, 03 May 2006 15:59:31 GMT "Michael Mattias"
<michael.mattias@gte.net>
> wrote:
>
> :>A "SELECT * from tablename" may not return the column names if the
table
> :>is empty. If available on whatever system you are working on a catalog
> :>function might be a better choice.
>
> You don't execute it, you prepare it.
Thanks.
I looked this up for ODBC, and it looks like that may be the answer to a
long-standing problem I have had.
The problem is, I have to work with ODBC version 2, in which the SqlColumns
catalog function does not return the ordinal position of a column within a
table. (I am checking that the user's database has all the right tables and
columns before I let him select that database as his current database)
But, following your lead, it looks like.........
SqlPrepare "SELECT * FROM Table_Name"
SqlNumParams ==> should return the number of columns in the result set
SqlDescribeCol ==> should return the name of the column.
From this I should be able to create list a of 'columns in this table 'IN
ORDER.'
(SqlColumns when using ODBC-3 DOES return the ordinal position of each
column in the table).
Sheesh, I can hardly wait to test this now!
--
Michael C. Mattias
Tal Systems Inc.
Racine WI
mmattias@talsystems.com
| |
| Frederico Fonseca 2006-05-03, 6:55 pm |
| On Wed, 3 May 2006 08:02:11 -0400, "Serge Lacombe"
<serge.lacombe@umontreal.ca> wrote:
>Hi,
>
>I need to obtain the list of elements (name of column,type..) of an Oracle
>table.
>
>Can it be done in a cobol embedded sql program?
>
>If yes, a source dode example would be appreciated.
>
>Thanks.
>
Answer is yes. (WITH ORACLE)
see
http://download-uk.oracle.com/docs/...357/ch12019.htm
for a full description. You may need to create a user to view the
document above.
Bat basics are
Syntax
DESC[RIBE] {[schema.]object[@db_link]}
Lists the column definitions for the specified table, view or synonym,
or the specifications for the specified function or procedure.
Terms
schema
Represents the schema where the object resides. If you omit schema,
SQL*Plus assumes you own object.
object
Represents the table, view, type, procedure, function, package or
synonym you wish to describe.
@db_link
Consists of the database link name corresponding to the database where
object exists.
The above method needs to be done with a dynamic SQL, and if you only
need the table information then you can hardcode the columns required.
Note. Never tried it with ESQL.
For another method that is sure to work see.
http://download-uk.oracle.com/docs/....htm#sthref1655
You will need to create a new user if you are not already registered.
DO IT if you are going to be dealing with Oracle, as it is required
for all type of access to their website.
Frederico Fonseca
ema il: frederico_fonseca at syssoft-int.com
| |
| Michael Mattias 2006-05-03, 6:55 pm |
| "Michael Mattias" <michael.mattias@gte.net> wrote in message news:...
> "Binyamin Dissen" <postingid@dissensoftware.com> wrote in message
> news:0bmh52hnrqjpkdkv9icdcodpge1le2ff51@
4ax.com...
> <michael.mattias@gte.net>
> table
>
> Thanks.
>
> I looked this up for ODBC, and it looks like that may be the answer to a
> long-standing problem I have had...
....
> But, following your lead, it looks like.........
>
> SqlPrepare "SELECT * FROM Table_Name"
> SqlNumParams ==> should return the number of columns in the result set
> SqlDescribeCol ==> should return the name of the column.
>
> Sheesh, I can hardly wait to test this now!
OK, I tested. I was close. The sequence which works is
SqlPrepare "SELECT * from empty_table"
SqlNumResultCols ==>. returns number of columns
SqlDescribeCol ==> returns full info on each returned column
But, SQLExecDirect does the same thing - at least ODBC-2 with MS-Jet
(Access) Driver it does.
However, saving the actual execution is worth something if all I need at the
time is the ordered list of columns.. since there is no actual data
retrieval and building of a result set for the DBMS to do.
MCM
| |
| arrbee 2006-05-04, 3:55 am |
| Thanks. It is very good information on Oracle front. I never wanted to
insert anything but wanted to update a DB2 table.
| |
|
| Hey DD! I had the same experience yesterday. Deja Vu!
> the client said 'All ya gotta do is merge data from these various datasets into a single record
> and INSERT it into the tables.'
>... it might be more efficient for me to use a COBOL program to generate a flat file
>and then use a utility to load that into the table.'
We had to spend 2 hours explaining to a consultant (who bragged about
his extensive mainframe experience) why we didn't do
all of our database programming in one cobol program. He didn't seem
to accept that we didn't have redundant
jobs in our system. We tried explaining that it was normal to have
cobol programs format the flat files first, then run a load utility to
our DB2 tables. I am afraid that in today's world some people have
trouble understanding that performance issues
affect the way we write our programs. Anyhoo - that made me laugh.
| |
|
| In article <1146789126.948377.225950@e56g2000cwe.googlegroups.com>,
Holly <anderschwan@hotmail.com> wrote:
>Hey DD! I had the same experience yesterday. Deja Vu!
>
>
>We had to spend 2 hours explaining to a consultant (who bragged about
>his extensive mainframe experience) why we didn't do
>all of our database programming in one cobol program. He didn't seem
>to accept that we didn't have redundant
>jobs in our system.
Well... I'm sure his clothes looked *very* nice. I'm not sure whether his
insistence on doing it his way was the result of a flawed - but somehow
reasoned - argument or simply a matter of ego; if it was the former then I
can muster a kind of respect for him... if the latter... well, I'm sure
his clothes looked *very* nice.
>We tried explaining that it was normal to have
>cobol programs format the flat files first, then run a load utility to
>our DB2 tables. I am afraid that in today's world some people have
>trouble understanding that performance issues
>affect the way we write our programs.
Oh, I *cannot* resist...
.... if this weren't a Family Newsgroup I'd mention a woman I used to live
with who didn't understand performance issues, either.
>Anyhoo - that made me laugh.
As you may be learning... 'every place is different, therefore every place
is the same'.
DD
| |
| Michael Mattias 2006-05-07, 6:55 pm |
| "Holly" <anderschwan@hotmail.com> wrote in message
news:1146789126.948377.225950@e56g2000cwe.googlegroups.com...
> .... We tried explaining that it was normal to have
> cobol programs format the flat files first, then run a load utility to
> our DB2 tables. I am afraid that in today's world some people have
> trouble understanding that performance issues affect the way we write our
programs.
It's not just the performance of hardware/software here- there's also the
performance of the 'wetware' - you know, that stuff between the ears?
I've done contract work at a couple of places where "database" programming
consisted of doing everything BUT the actual database access.
Those clients had found it made sense *personnel-wise* to just use an
external program or other executable module to do all the database
interface, as not all their programmers or outside contractors had the
necessary skills or experience to do the work accurately and efficiently -
but they all could develop software to create 'flat files' or "working
storage record image" to 'feed' to a database interface module.
This probably occurs more often in another form: the "copylib". Applications
programmers often don't develop any DB code, they instead simply copy in a
time-tested library of routines and 'follow the copylib rules' for updating
a database.
Many (including myself) believe this is also a great productivity
enhancer.... it allows any ( read: "junior") (read: "less expensive")
programmer be assigned to a task, not requiring that software requiring
database access wait for the availability of specific (read: "expensive")
personnel to get the job done. Sure, it can take a pretty substantial
up-front investment to develop generic modules or copylibs, but if you have
enough work to do it pays for itself over time.
Last example: non-relational or other 'older' DBMS systems. How many kids
coming out of school today can even spell "hierarchical", leave alone
understanding how to access a hierarchical DB such as IMS? Good luck
finding a programmer who is going to be efficient at that job on day one!
But create a 'record image' to pass to an interface module to add it to the
database in the correct location? I daresay you could have someone this
afternoon.
MCM
| |
|
| Oh Doc, they have prescriptions for that :)
>... if this weren't a Family Newsgroup I'd mention a woman I used to live
>with who didn't understand performance issues, either.
| |
| Pete Dashwood 2006-05-07, 6:55 pm |
|
"Holly" <anderschwan@hotmail.com> wrote in message
news:1146834310.662967.276150@g10g2000cwb.googlegroups.com...
> Oh Doc, they have prescriptions for that :)
>
>
Yeah, an' it really wor ... Ooops!
Of course, I wouldn't know. But I'll take your word for it Holly. :-)
Pete.
|
|
|
|
|