Microfocus & Oracle data types

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

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.

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 variabl
e
>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

"Simon Tobias" <Simon.Tobias@nospam.microfocus.com> wrote in message news:<cf8q0k$96g$1@hyp
erion.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 f
or
> NX 3.0 (which is quite an old product now)?
>
> Finally, if you use the OpenESQL Assistant to generate the copybook -- whi
ch
> 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

"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

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
>
>

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.

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>...
> 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 nee
ds
> 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 lo
t.
>
> 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 

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
>
>

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.