NetCobol .Net and Oracle ODBC access

[Luimarat]

I am migrating COBOL CICS DB2 applications to LAN environment. I am using Fujitsu Tools. The migration is very simple and work fine with CICS emulation and Batch JOBS.
The problem is the access to hte new database. The programs are migrated to .Net platform. They have to access Oracle database via ODBC.
Everything works fine except that some SELECT statments should return Too Many Rows SQL Exception but they do not.
In thise case I get SQLCODE = 0 SQLSTATE = '00000' and blanck message. The host variables get one row.
Of course this is erroneous.
Can someboby helps,
Luis

 

[Dimandja]

Hi Luis,

You are making a lot of assumptions by blaming the database. How do we know your program actually works otherwise. How do we your migration actually went well? How do we know the database is the culprit?

I suggest using the debugger, or finding a way to isolate the problem.

Then, when you get suspicious code, post it here. For example, your SELECT statement and your Host variables.

Dimandja

 

[Luimarat]

SQL Statement:
EXEC SQL SELECT USUARIO, OPID
INTO :TB-USUARIO, :TB-OPID
FROM USUARIOS
WHERE OPID = :TB-OPID
END-EXEC

Host varibles:
01 TB-TABLE.
03 TB-USUARIO PIC X(8).
03 TB-OPID PIC X(3).

What I mean is that all the rest of SQL statements work fine. I have now more or less 100 CICS migrated transactions transactions working. Only this return code is not properly returned. I do not know if the problem is the database, the odbc, or even cobol for .net environment; this is what I would like to fix.

Under the debugger I see the results in host variables (one of the rows) and I see the SQLCODE, SQLSTATE, and SQLMSG variables with 0,'00000' and spaces respectively.

I can send the complete NetCobol for .Net project

Luis

 

[Dimandja]

When you say that the "return code is not properly returned", what do you mean? Does it get returned sometimes? When?

I assume that TB-OPID is properly initialized at the time SELECT is executed?

1. Make sure SQLCODE, SQLSTATE, and SQLMSG are not being overwritten; it can happen: SQL has the propensity to invade COBOL space.

2. I am afraid you may have to post enough code to show the context of the code you posted.

3. The best thing to do is strip the program to a minimum and progressively add code until it eventually stops working.

4. Also make sure the query that fails does work manually or that the row(s) being sought are actually present in the database (or not).

Dimandja

 

[Luimarat]

There are only 3 SQL statement in the program:
The CONNECT
The SELECT
The DISCONNECT
If I test this select using Pl/SQL (oracle) it returns the too many rows error.
The values of the return codes are always like I said before.
The OPID variable is set lust before.
The same source code works perfectly (I mean returns the good SQLCODE) with Db2 or SQl Server.

Thanks for your response
Luis

 

[Dimandja]

Hi Luis,

The problem is not COBOL, but SQL. I'll answer it anyway since we came this far. The "problem" is that PL/SQL creates an implicit cursor and the SQL you are using does not.

If you have a SELECT statement that returns more than one row, you must use an explicit cursor for that query and then process the rows returned one at a time.

ANSI SQL dictates that a single-row query must not only fetch the first record, but must also perform a second fetch to determine if too many rows will be returned by that query. Thus, an implicit query always performs a minimum of two fetches, while an explicit cursor only needs to perform a single fetch.

The SQL flavor you are using in your program is not ANSI.
Correct you SQL: create an explicit cursor, and you are good to go.

Dimandja

 

[Luimarat]

Hi Dimandja,
We have traced the ODBC. Oracle always accesses the database using a Curosr in this case. All the rest of databases returns DUPSEL (Db2) or in general too many rows error.
It is like the no data found. Oracle return code is ... I do not remember the value, but the ODBC transform that value in +100 which is the standard value for no data found error.
If oracle needs to implement cursors or some other statement like select count() before the real select to fix if there are some other rows, we probably use Db2 as the production database. (too many programs are doing things like this and it is really dificult to know if a select statement can need some extra commands before).

Thanks,
LUIS