Cobol - Microfocus & Oracle data types

Author

Microfocus & Oracle data types

Michael Russell

2004-08-09, 8:55 pm

Greetings,

I'm having trouble with ESQL:

Scenario:
Oracle 9i running on my PC (seemed a good idea)
Microfocus Cobol 3.014 ditto
I'm not using a pre-compiler.

When using a cursor to fetch an Oracle-defined Decimal(7,3), no data-type
I've tried as the receiving variable seems to work.
I've tried: MF's ESQL tool-generated s9(4)v9(3) Comp-3 and my own
interventions with plain Comp & then plain Display.

My 'evidence' is from inspecting via the Animator. Also, moving the variable
to another, 9(4)v999 display & inspecting that.

Any hints would be welcome. .... (no such apparent trouble using Sybase
<sigh> )

Regards

Michael

Simon Tobias

2004-08-09, 8:55 pm

Hi Michael.

What SQLCODE/SQLSTATE/SQLERRMC are you getting returned on the FETCH
statement? Could you also tell me which ODBC driver (and version) you're
using please, and also whether you're running the latest set of fixpacks for
NX 3.0 (which is quite an old product now)?

Finally, if you use the OpenESQL Assistant to generate the copybook -- which
using my copy of NX 4.0, generates the hvar as PIC S9(04)V9(03) COMP-3 --
and also an app to fetch the data from your table, does that run through
cleanly and return data?

Thx.

SimonT.

James J. Gavan

2004-08-09, 8:55 pm

Michael Russell wrote:

>Greetings,
>
>I'm having trouble with ESQL:
>
>Scenario:
>Oracle 9i running on my PC (seemed a good idea)
>Microfocus Cobol 3.014 ditto
>I'm not using a pre-compiler.
>
>When using a cursor to fetch an Oracle-defined Decimal(7,3), no data-type
>I've tried as the receiving variable seems to work.
>I've tried: MF's ESQL tool-generated s9(4)v9(3) Comp-3 and my own
>interventions with plain Comp & then plain Display.
>
>My 'evidence' is from inspecting via the Animator. Also, moving the variable
>to another, 9(4)v999 display & inspecting that.
>
>Any hints would be welcome. .... (no such apparent trouble using Sybase
><sigh> )
>
>
>
Oh dear, here we go again, (a plug for Answer Exchange). I'm using V
3.1 with MS Access. Had no interest in SQL in the past so wasn't even
aware V 3.0 had ESQL !

If you haven't already done so, go to M/F site and join Answer Exchange
(freebie), then post your message under 'Net Express' with a title
starting "SQL" - that should catch Barb's eye. If you do use Answer
Exchange, suggest you attach the contents of the generated copyfile to
your message - then nobody is left second-guessing. Alternatively given
access to the the latest on-line manuals covering Databases, you may be
able to figure it out for yourself . Here's the link to the N/E V 3.1
manuals :-

http://supportline.microfocus.com/d...p1/nx31indx.htm

Jimmy, Calgary AB

Mike Russell

2004-08-10, 8:55 am

"Simon Tobias" <Simon.Tobias@nospam.microfocus.com> wrote in message news:<cf8q0k$96g$1@hyperion.microfocus.com>...
> Hi Michael.
>
> What SQLCODE/SQLSTATE/SQLERRMC are you getting returned on the FETCH
> statement? Could you also tell me which ODBC driver (and version) you're
> using please, and also whether you're running the latest set of fixpacks for
> NX 3.0 (which is quite an old product now)?
>
> Finally, if you use the OpenESQL Assistant to generate the copybook -- which
> using my copy of NX 4.0, generates the hvar as PIC S9(04)V9(03) COMP-3 --
> and also an app to fetch the data from your table, does that run through
> cleanly and return data?
>
> Thx.
>
> SimonT.

Hi Simon,

I'm not at home, so the answers are from memory - will check later
tho' if anything else crops up...

1. SQLCODE remains at 0 - I don't need to check this later.

2. ODBC driver is titled Oracle92 - and became available, as far as I
can tell, after I installed Oracle 9i.

3. I haven't checked for fixpacks for NX - I believe this version is
outside the scope of MF support; it was a 'University edition' when I
bought it and I believe that means it's just about to fall out of
supported versions when you buy it, anyway.

4. I did indeed use the OpenESQL Assistant's copybook & it did
generate hvars of PIC S9(04)V9(03) COMP-3. The Fetch includes char &
varchars (I think both types, but some kind of char anyway) and these
hvars do get correctly populated, as does a Date-type column -> hvar.
Further, the correct rows are returned.
However, the fetch is in a 'perform until sqlcode < 0 or > 100' and
sometimes it returns 1 row, sometimes 2 (which would be correct) for
the same 'where' condition & when it returns only 1 row, sqlcode is
still 0 ... I'm taking this to be a kind of apparent sub-error that's
best ignored until the 'real' error of hvar definition is solved - as
I believe incompatibility between RDBMS & receiving application may
well cause some bad behaviuor & is usually signalled by Oracle
throwing some exception (just hearsay on my part, though).

Regards

Michael

Mike Russell

2004-08-10, 8:55 am

"James J. Gavan" <jjgavan@shaw.ca> wrote in message news:<6iSRc.62317$J06.5744@pd7tw2no>...
> Michael Russell wrote:
>
> Oh dear, here we go again, (a plug for Answer Exchange). I'm using V
> 3.1 with MS Access. Had no interest in SQL in the past so wasn't even
> aware V 3.0 had ESQL !
>
> If you haven't already done so, go to M/F site and join Answer Exchange
> (freebie), then post your message under 'Net Express' with a title
> starting "SQL" - that should catch Barb's eye. If you do use Answer
> Exchange, suggest you attach the contents of the generated copyfile to
> your message - then nobody is left second-guessing. Alternatively given
> access to the the latest on-line manuals covering Databases, you may be
> able to figure it out for yourself . Here's the link to the N/E V 3.1
> manuals :-
>
> http://supportline.microfocus.com/d...p1/nx31indx.htm
>
> Jimmy, Calgary AB

Thanks for your reply, James.

I do indeed have access to the MF manuals that came on the CD of the
NX product; not surprisingly, they're the same (? if not earlier?) as
the ones you linked to in your post. They do not seem to address this
question of 'proper' data-types to use for Oracle directly. A simple
table of Oracle data-types & matching Cobol hvar types would be ideal,
but so would an error message from one of the 3 components (Cobol,
ODBC, Oracle).

I will try the Answer Exchange as you suggest - & report back to this
NG on the progress made there.

Thanks

Michael

Michael Russell

2004-08-10, 3:55 pm

Well, thanks for the input, the two gentlemen ... & please excuse the
top-posting ...

I've added null-indicators to all appropriate parts of the 'fetch' - and
it's all OK now.

Oddly, when using Sybase, there was a SQLWARN that they were missing (I
think, even when no null had been returned, but please correct that if needs
be), but not in this scenario.

Nice what comes into your mind as you walk home from work, relatively
carefree, sometimes.

The finished work would have had them - but when you're (I'm) building an
application, trialing the important parts comes before coding the whole lot.

Thanks again

Michael
"Michael Russell" <michael.russell@spamex.dsl.pipex.com> wrote in message
news:4117e2e2$0$20253$cc9e4d1f@news-text.dial.pipex.com...
> Greetings,
>
> I'm having trouble with ESQL:
>
> Scenario:
> Oracle 9i running on my PC (seemed a good idea)
> Microfocus Cobol 3.014 ditto
> I'm not using a pre-compiler.
>
> When using a cursor to fetch an Oracle-defined Decimal(7,3), no data-type
> I've tried as the receiving variable seems to work.
> I've tried: MF's ESQL tool-generated s9(4)v9(3) Comp-3 and my own
> interventions with plain Comp & then plain Display.
>
> My 'evidence' is from inspecting via the Animator. Also, moving the
variable
> to another, 9(4)v999 display & inspecting that.
>
> Any hints would be welcome. .... (no such apparent trouble using Sybase
> <sigh> )
>
> Regards
>
> Michael
>
>

Simon Tobias

2004-08-11, 8:55 am

Hi Michael,

that's great that you figured this out.

Without indicator variables, I would expect that you may get a SQLCODE of
+1, with SQLWARN set appropriately, if NULLs were returned but no indicator
set. Under the Net Express refererence manual, under Database Access ->
OpenESQL -> Data Structures -> SQLCA, we state the following. Note that
SQLWARN2 will only be set if you return a NULL value from the FETCH :

SQLWARN
Eight warning flags, each containing a blank or "W" (those not listed below
are reserved): A warning flag will be set if SQLCODE contains a value of +1:

SQLWARN0 A summary of all warning fields. Blank means there are no warnings.

SQLWARN1 "W" indicates that data was truncated on output to a character host
variable.

SQLWARN2 "W" indicates that a null value exists, but no indicator variable
was provided.

SQLWARN3 "W" indicates that the number of columns is less than the number of
host
variables or that the number of host variables provided does not match the
number of parameter markers in the statement. The lower of the two numbers
has been used.

SQLWARN4 "W" indicates a singleton select that returns more than one row
(only the first row is returned).

Regards,
SimonT.

Robert Jones

2004-08-11, 3:55 pm

Top posting

Hello Michael

While the others have solved your immediate problem, you might
consider using the COALESCE function another time, it can be easier to
apply than using indicator variables for data retrieval. COALESCE is
a relative latecomer to SQL, in DB2 it was previously called VALUE,
Oracle might have another name for it. It is used as follows (from
rusty memory):

COALESCE(column-name,default-value-for-nulls), where the first
non-null value in the parameter list of the function is used as the
value to be retrieved in a SELECT.

Regards, Robert

"Michael Russell" <michael.russell@spamex.dsl.pipex.com> wrote in message news:<4118f3dd$0$20249$cc9e4d1f@news-text.dial.pipex.com>...[color=darkred]
> Well, thanks for the input, the two gentlemen ... & please excuse the
> top-posting ...
>
> I've added null-indicators to all appropriate parts of the 'fetch' - and
> it's all OK now.
>
> Oddly, when using Sybase, there was a SQLWARN that they were missing (I
> think, even when no null had been returned, but please correct that if needs
> be), but not in this scenario.
>
> Nice what comes into your mind as you walk home from work, relatively
> carefree, sometimes.
>
> The finished work would have had them - but when you're (I'm) building an
> application, trialing the important parts comes before coding the whole lot.
>
> Thanks again
>
> Michael
> "Michael Russell" <michael.russell@spamex.dsl.pipex.com> wrote in message
> news:4117e2e2$0$20253$cc9e4d1f@news-text.dial.pipex.com...
> variable

Michael Russell

2004-08-15, 3:55 am

Simon Tobias

2004-08-15, 8:55 am

Hi Jimmy.

I'm not sure that I suggested using any particular compiler directives to
resolve the issue -- purely making use of functionality available with
OpenESQL, which ships with Net Express and Server Express. SQLWARNx is part
of our standard SQLCA copybook structure, and is automatically populated
where necessary.

<shameless plug>

The OpenESQL Assistant, which is provided with Net Express, does allow for
the creation and testing of SQL queries via ODBC connections in situ,
without the need to code a significant amount manually. Once you're happy
with the query output, you can generate the bulk of your SQL application,
including the query itself and the appropriate host variable definitions,
with a few mouse clicks.

</shameless plug>

I do agree that using standard SQL syntax is the best approach when
initially creating database applications connecting via ODBC, simply as it
makes switching between differing RBDMSs on the back-end so much easier
(IMHO).

I am concerned regarding your experiences with the MF Answer Exchange
though -- if you're not getting satisfactory responses, please email me
off-list with details, and I'll endeavour to follow up internally.

SimonT.

docdwarf@panix.com

2004-08-15, 8:55 am

In article <411b943b$0$20250$cc9e4d1f@news-text.dial.pipex.com>,
Michael Russell <michael.russell@spamex.dsl.pipex.com> wrote:
>Top posting:
>
>Thanks, Robert.
>SQL is fairly new to me ... and it seems to be getting 'newer' the more I
>practice!
>(I'm yet to cross the pons assinorum - which, for RDBMSes, I define as a
>3-table join that works the way I intend :-))

Mr Russell, leaving aside the question of whether your intentions
accurately reflect what it is that SQL should do... a three-table join is
your equivalent of I.5? What would you call 'Dulcarnon', then?

DD

James J. Gavan

2004-08-15, 8:55 am

Michael Russell wrote:

>Top posting:
>
>Thanks, Robert.
>SQL is fairly new to me ... and it seems to be getting 'newer' the more I
>practice!
>(I'm yet to cross the pons assinorum - which, for RDBMSes, I define as a
>3-table join that works the way I intend :-))
>I'm about to read up on Coalesce now ... think I'd better also user null
>indicators, as well, though.
>
>
>
OK Michael,

I'm going to jump in here. Like you I am *NO* expert at SQL. Your
original message

me a little, no pre-compiler, then the null
problem and you subsequently *did* generate the ESQL copyfile - but I
got the impression that was a looksee and then you independently used
features. Now our good friend Simon points you at the warnings -
Directives - Ugh !. Then Robert throws in an extra with the coalesce.

Again no expert - but somebody who has been at the DB game since the
year dot - Pete Dashwood - words of wisdom, "Don't get seduced into
using the extras that come with different flavours of DB tools - use the
DB as a repository for your data". Translation - primarily stick to the
SQL issued Standard. That seems to me to be a very sensible starting
point, plus if you use the 'goodies' in current package, watch out for
portability if you switch.. Next one is me "Don't use Directives if you
don't have to; try and stick with the M/F defaults".

Where I got burned on directives. I produced a package (COBOL files)
and it worked just fine. A year later I went back to run the test data -
I couldn't get much beyond the selection from the Master Menu -
file-status errors - (35 I think ???). I tried the M/F Answer Exchange
and even wrote to the head man at Newbury (a Mr. Coffey I think).
Absolutely no success - my problem seemed to be buried in my application
somewhere. Six months later (that's a time span of 18 months), the penny
dropped.

In the original I had used the Directive $set compress (whatever the
syntax is). Why didn't it work. The second time around I did a rebuild
of the application to include support DLLs. Whatever it's called I
didn't include the compress.dll. Screw that - I have a very good memory
- but I don't want to run up against problems because I got tripped up
using features I'd forgotten I had included. If in doubt don't use
Directives - unless you can make a strong case for them. Typically if
you raise an obtuse query in Answer Exchange our M/F frineds invariably
ask " which version, which Fixpacks installed ?". Doesn't hurt to start
off your query with "I am using Defaults, only following ..x..
Directives have been used". (Of course our good friends with M/F will
also possibly say, "That's a very old versin you have. I would recommend
you upgrade". They've got V 4 with dot Net in front of them - and they
personally didn't spnd a penny :-) ) In the real world to go from V
3.0 to current I'm guessing you are looking around $3K - not chump change.

Now you say you have the ESQL and did use it to generate the copyfile to
track your problem. Have you also tried the feature that will generate
the code to be copied/pasted into your program ? Even before you reach
that stage you can confirm that it is kosher. Have your tried the
feature which let's you run your query from within the ESQL package and
will even produce tables with the results ?

The bloody NULL business was an absolute mystery to me - I queried with
folks like Pete and Thane - they saw no problems because they were
attuned to SQL from experience - to me it was still a bloody big
mystery. So now I let the ESQL module generate my syntax - the text
generated can sometimes be very verbose, particularly as it handles each
column with a NULL feature. I still don't know the full implications of
NULLs - but who gives a ..... ! Those EXEC..... END EXEC that I
copy/paste, compile absolutely 100% *clean*, work perfectly and I never
have errors. I really hate to say it - but Net Express is probably worth
its price just for the ESQL module alone !!!

Now I'd better cover my tracks before I get inundated with comments by
experts here using SQL. Depending upon which DB you are using, there
ARE many additional features. Use them cautiously, testing them within
the ESQL module as I suggested above. SQL has the edge over COBOL files
- yes you can use Alternate Keys and Split Keys (an M/F feature or is
that also included in the new Standard 2002 ?). But that means you have
got to anticipate your likely 'extracts' and design your COBOL file keys
accordingly. Using the basic SQL 92 or your appropriate tools
'additions' gives you greater flexibility. But there is another tool in
your personal arsenal - OO Collections - you have exactly the same as I
have in V 3.1. Some of the 'clever' stuff produced from SQL 'extensions'
can also be achieved through a combination of SQL and OO Collections.

As a starter either from \Net Express\Base or perhaps easier if you look
at the on-line help link to collections - take at look at the Collection
demo. It's a starting point and no way shows all the features. You
didn't indicate which mode you were working in - regardless of whether
(1) Procedural with Screen Section, (2) Dialog System or (3) Dialog
Editor and GUI classes, you can invoke (CALL) collection features from
Procedural programs. If it sets off a tick, tick, tick in your brain
and you want help - please feel free to contact me for suggestions -
but the e-mail address above is my Junk/Spammer address - drop the
second "J" in the address when e-mailing me.

You might want to do a search of current and archived messages in Answer
Exchange using "SQL" as the search key.

Jimmy, Calgary AB

Michael Russell

2004-08-15, 3:55 pm

Nicely presented information -- thank you, Simon.

Regards

Michael

"Simon Tobias" <Simon.Tobias@nospam.microfocus.com> wrote in message
news:cfcnr7$nko$1@hyperion.microfocus.com...
> Hi Michael,
>
> that's great that you figured this out.
>
> Without indicator variables, I would expect that you may get a SQLCODE of
> +1, with SQLWARN set appropriately, if NULLs were returned but no
indicator
> set. Under the Net Express refererence manual, under Database Access ->
> OpenESQL -> Data Structures -> SQLCA, we state the following. Note that
> SQLWARN2 will only be set if you return a NULL value from the FETCH :
>
> SQLWARN
> Eight warning flags, each containing a blank or "W" (those not listed
below
> are reserved): A warning flag will be set if SQLCODE contains a value of
+1:
>
> SQLWARN0 A summary of all warning fields. Blank means there are no
warnings.
>
> SQLWARN1 "W" indicates that data was truncated on output to a character
host
> variable.
>
> SQLWARN2 "W" indicates that a null value exists, but no indicator variable
> was provided.
>
> SQLWARN3 "W" indicates that the number of columns is less than the number
of
> host
> variables or that the number of host variables provided does not match the
> number of parameter markers in the statement. The lower of the two numbers
> has been used.
>
> SQLWARN4 "W" indicates a singleton select that returns more than one row
> (only the first row is returned).
>
>
> Regards,
> SimonT.
>
>

Michael Russell

2004-08-15, 3:55 pm

Top posting:

Thanks, Robert.
SQL is fairly new to me ... and it seems to be getting 'newer' the more I
practice!
(I'm yet to cross the pons assinorum - which, for RDBMSes, I define as a
3-table join that works the way I intend :-))
I'm about to read up on Coalesce now ... think I'd better also user null
indicators, as well, though.

Thanks & Regards

Michael

"Robert Jones" <rjones0@hotmail.com> wrote in message
news:6dd8322.0408111103.7a580e23@posting.google.com...
> Top posting
>
> Hello Michael
>
> While the others have solved your immediate problem, you might
> consider using the COALESCE function another time, it can be easier to
> apply than using indicator variables for data retrieval. COALESCE is
> a relative latecomer to SQL, in DB2 it was previously called VALUE,
> Oracle might have another name for it. It is used as follows (from
> rusty memory):
>
> COALESCE(column-name,default-value-for-nulls), where the first
> non-null value in the parameter list of the function is used as the
> value to be retrieved in a SELECT.
>
> Regards, Robert
>
> "Michael Russell" <michael.russell@spamex.dsl.pipex.com> wrote in message
news:<4118f3dd$0$20249$cc9e4d1f@news-text.dial.pipex.com>...[color=darkred]
needs[color=darkred]
an[color=darkred]
lot.[color=darkred]
message[color=darkred]
data-type[color=darkred]
Sybase[color=darkred]

James J. Gavan

2004-08-16, 3:55 am

Robert Jones

2004-08-16, 8:55 am

James J. Gavan

2004-08-16, 8:55 pm

Simon Tobias wrote:

>Hi Jimmy.
>
>I'm not sure that I suggested using any particular compiler directives to
>resolve the issue -- purely making use of functionality available with
>OpenESQL, which ships with Net Express and Server Express. SQLWARNx is part
>of our standard SQLCA copybook structure, and is automatically populated
>where necessary.
>
>
>
Yes I read that too quickly and *assumed* a directive was being set

><shameless plug>
>
></shameless plug>
>
>
>
No problem as a developer using the product I really like the product -
so plugs are in order :-)

>I am concerned regarding your experiences with the MF Answer Exchange
>though -- if you're not getting satisfactory responses, please email me
>off-list with details, and I'll endeavour to follow up internally.
>
>
>
That stumped me. Believe me I *really* can complain when necessary. So
I had to read my previous message to get what you thought I was
complaining about. As an example I was cautioning about use of
Directives. To solve the problem there was no quick fix. Not knowing
precisely where it was occurring would have necessitated me sending a
HUGE zipfile for the project (OO) for somebody to sort out. Assuming
somebody would take on the challenge without saying 'This is gonna cost
money....", they would have rebuilt the project with their tools. Still
probably wouldn't have worked. If the recipient was real smart he might
have thought of doing a global search :-

..... another plug here :-)

on the project source looking to see if I had any '$set.......". Bingo
! From memory - he would find I used only one (no SQL being used in
those days and I don't even use any of the $sets.... for OO - it works
fine without them.).

so he would see "$set compress.....", include the DLL and find that it
runs smoothly. In a very polite and diplomatic way he would write me -
literal translation - "Make sure you include the compress.DLL - you
silly bugger !". Someone might find it fairly easily, but I think you
would agree, initially it might be a long shot.

Absolutely no problems with Answer Exchange. It's even better than its
predecessor M/F CompuServe Forum which Alan monitored and good old
stalwart Patrick was part of, and still is. Funnily enough a Colin
Ransom/Ranshaw (?) today posted he had a 'funny' with SQL using V 3.0
(No ! Don't tell him to upgrade - $$$$$$ !). I referred to the fact that
you had helped resolve Michael's problem here in c.l.c. and suggested he
use all the features of ESQL , setting up a dummy Table, generating
queries and perhaps running them in ESQL test mode. I think his problem
might be a little quirky. I should have suggested to him - take his
problem step-by-step ( having firstly generated the copyfile), then
copy/paste each step (Query), into a small COBOL program against a
series of perform PARAGRAPHS. From memory the problem is occurring at
arm's length over a network.

Now if you want to know about complaining - I think we are back in 1985
or thereabouts and I had switched from RM/COBOL to MS COBOL (not their
'badged' version of M/F COBOL - but the immediate predecessor). Can't
even remember my justification for the switch from RM to MS. Anyway I
had a problem but can't remember details. So I phoned M/S Tech Support-
they had a Canadian Office in either Quebec or Toronto. Nice young man
took my query - but even from the first telephone call ( and this *was*
my dime - no 1-800s in those days), it was clearly apparent that I knew
more about COBOL than he did. In fact I'm not even sure he knew anything
about COBOL. But I suppose on the basis he had taken a course at college
that's how he got his job. He would look into it.

Few w

s pass - I phone again "I'm still looking into it". I suggest I
snail-mail him a diskette with the offending source.

Few w

s pass - I phone again. "Sorry. Haven't received your diskette".

Boy ! I'm really pissed off by now. I think on the problem; , what's the
name of that man in Washington State who started this Microsoft thing.
Ah yes. Bill Gates. (Didn't realize that even back then he had enough
greenbacks to completely paper over Mount St. Helens and stop it blowing
its top). Screw it ! I'll phone and ask to speak to him direct. I know
he's the boss of a *small* company - still he's got to be concerned.
Truly - I get his secretary - who plays it real

and ensures me the
message will be passed on. Boom ! Within the hour on comes a bright-eyed
and bushy-tailed troubleshooter. "What's the problem. Details please. OK
leave it to me".. Gets back about half an hour later, obviously having
checked out the Canadian office. "OK send a copy of your diskette to
Beyonce Knowles here in Belliingham, (I WISH !). She will sort it out
and phone you back".

She did within one day of probably receiving the diskette and for full
measure offered to send me a couple of diskettes with sample programs.
One source I recall was taking a print heading routine, centering for
when it was printed on the page. Regrettably, I assume the young guy in
Canada probably got the boot and I doubt his manager was left as a very
happy camper either.

If yer gonna complain - do it big time !

I just reflected - that was human-to-human talking. Ignoring the size of
MS now - or any other company - would we get the same sort of action
with our modern dispassionate e-mail approach ?

Mind you ignoring COBOL - on reflection I think my time would have been
better spent, armed with huge bags, traipsing around the slopes of St.
Helens, collecting Bill's greenbacks.

Jimmy, Calgary AB

Michael Russell

2004-08-18, 3:55 am

James J. Gavan

2004-08-18, 3:55 pm

Sponsored Links