We have a system that uses DB2 database. We need to have this system now use an Oracle Database for one particular customer. We thought the easiest way to achieve this would be to simply use ODBC so that (in theory) we should have minimum changes.
First test was to be able to access the existing DB2 database but via DB2 ODBC driver. First problem to arise was related to some strange (for historic reasons - no surprise) record layouts that we have. For example, we have the following code:
01 WS-DATA.
03 WS-A PIC X.
03 WS-B PIC S9(4) COMP-5.
03 WS-C PIC X.
CREATE TABLE MYTABLE (MY_CODE SMALLINT NOT NULL, MY_DATA CHAR(254) FOR BIT DATA)
MOVE "A" TO WS-A
MOVE 1 TO WS-B.
MOVE "C" TO WS-C.
MOVE 1 TO WS-SQL-CODE
INSERT INTO MYTABLE VALUES(:WS-SQL-CODE,:WS-DATA)
COMMIT
SELECT MY_DATA INTO :WS-DATA FROM MYTABLE WHERE MY_CODE = 1
At this point, the WS-A field has "A", WS-B is 'corrupt' and WS-C has spaces.
The reason why WS-B is corrupt is that prior to performing the INSERT, this field had X"00" and X"01" in it. This represented zero in comp-5. When I read it back in, I had X"00" followed by x"20". It looks to me like it is not handling the 'FOR BIT DATA' clause correctly. I don't know if this is a DB2 ODBC driver issue or a COBOL issue (and therefore not something I should be bothering MF with).
In an attempt to find out, I tried to run the same program using a Oracle database source and Oracle ODBC driver. Unfortunately, the program experiences a 907 error on the CREATE TABLE statement. This is being caused by the 'FOR BIT DATA' clause.
Any ideas chaps?
Problems with 'FOR BIT DATA'
- Rocketeer
+3- Moderator
We have a system that uses DB2 database. We need to have this system now use an Oracle Database for one particular customer. We thought the easiest way to achieve this would be to simply use ODBC so that (in theory) we should have minimum changes.
First test was to be able to access the existing DB2 database but via DB2 ODBC driver. First problem to arise was related to some strange (for historic reasons - no surprise) record layouts that we have. For example, we have the following code:
01 WS-DATA.
03 WS-A PIC X.
03 WS-B PIC S9(4) COMP-5.
03 WS-C PIC X.
CREATE TABLE MYTABLE (MY_CODE SMALLINT NOT NULL, MY_DATA CHAR(254) FOR BIT DATA)
MOVE "A" TO WS-A
MOVE 1 TO WS-B.
MOVE "C" TO WS-C.
MOVE 1 TO WS-SQL-CODE
INSERT INTO MYTABLE VALUES(:WS-SQL-CODE,:WS-DATA)
COMMIT
SELECT MY_DATA INTO :WS-DATA FROM MYTABLE WHERE MY_CODE = 1
At this point, the WS-A field has "A", WS-B is 'corrupt' and WS-C has spaces.
The reason why WS-B is corrupt is that prior to performing the INSERT, this field had X"00" and X"01" in it. This represented zero in comp-5. When I read it back in, I had X"00" followed by x"20". It looks to me like it is not handling the 'FOR BIT DATA' clause correctly. I don't know if this is a DB2 ODBC driver issue or a COBOL issue (and therefore not something I should be bothering MF with).
In an attempt to find out, I tried to run the same program using a Oracle database source and Oracle ODBC driver. Unfortunately, the program experiences a 907 error on the CREATE TABLE statement. This is being caused by the 'FOR BIT DATA' clause.
Any ideas chaps?
ODBC uses a different set of data types and the types used in DCL statements like CREATE TABLE are specific to the ODBC driver being used.
It does not appear that FOR BIT DATA is supported for Oracle at least.
Try adding the directive SQL(ALLOWNULLCHAR)
This allows programs to use PIC X(n) host variables, and to select/insert/update hexadecimal characters in CHAR columns without changing source to use SQL TYPE BINARY host variables.
I got your example to work under Oracle using the Oracle ODBC driver but I had to make a few changes because the group item was not valid as a host variable.
The following worked for me:
$set sql(dbman=odbc allownullchar)
program-id. oraodbc.
working-storage section.
exec sql include sqlca end-exec
01 my-field pic x(4).
01 WS-DATA redefines my-field.
03 WS-A PIC X.
03 WS-B PIC S9(4) COMP-5.
03 WS-C PIC X.
01 ws-sql-code pic s9(4) comp-5.
procedure division.
exec sql connect to "testora32"
user "scott.tiger"
end-exec
display sqlcode
exec sql drop table MYTABLE end-exec
display sqlcode
exec sql CREATE TABLE MYTABLE (MY_CODE SMALLINT NOT NULL,
MY_DATA CHAR(254)) end-exec
MOVE "A" TO WS-A
MOVE 1 TO WS-B
MOVE "C" TO WS-C
MOVE 1 TO WS-SQL-CODE
exec sql INSERT INTO MYTABLE (MY_CODE, MY_DATA)
VALUES(:WS-SQL-CODE,:my-field)
end-exec
exec sql COMMIT end-exec
move spaces to my-field
exec sql
SELECT MY_DATA INTO :my-field
FROM MYTABLE WHERE MY_CODE = 1
end-exec
display sqlcode
exec sql disconnect current end-exec
stop run.
- Rocketeer
We have a system that uses DB2 database. We need to have this system now use an Oracle Database for one particular customer. We thought the easiest way to achieve this would be to simply use ODBC so that (in theory) we should have minimum changes.
First test was to be able to access the existing DB2 database but via DB2 ODBC driver. First problem to arise was related to some strange (for historic reasons - no surprise) record layouts that we have. For example, we have the following code:
01 WS-DATA.
03 WS-A PIC X.
03 WS-B PIC S9(4) COMP-5.
03 WS-C PIC X.
CREATE TABLE MYTABLE (MY_CODE SMALLINT NOT NULL, MY_DATA CHAR(254) FOR BIT DATA)
MOVE "A" TO WS-A
MOVE 1 TO WS-B.
MOVE "C" TO WS-C.
MOVE 1 TO WS-SQL-CODE
INSERT INTO MYTABLE VALUES(:WS-SQL-CODE,:WS-DATA)
COMMIT
SELECT MY_DATA INTO :WS-DATA FROM MYTABLE WHERE MY_CODE = 1
At this point, the WS-A field has "A", WS-B is 'corrupt' and WS-C has spaces.
The reason why WS-B is corrupt is that prior to performing the INSERT, this field had X"00" and X"01" in it. This represented zero in comp-5. When I read it back in, I had X"00" followed by x"20". It looks to me like it is not handling the 'FOR BIT DATA' clause correctly. I don't know if this is a DB2 ODBC driver issue or a COBOL issue (and therefore not something I should be bothering MF with).
In an attempt to find out, I tried to run the same program using a Oracle database source and Oracle ODBC driver. Unfortunately, the program experiences a 907 error on the CREATE TABLE statement. This is being caused by the 'FOR BIT DATA' clause.
Any ideas chaps?
Can we clone you Chris?
Thanks
- Rocketeer
We have a system that uses DB2 database. We need to have this system now use an Oracle Database for one particular customer. We thought the easiest way to achieve this would be to simply use ODBC so that (in theory) we should have minimum changes.
First test was to be able to access the existing DB2 database but via DB2 ODBC driver. First problem to arise was related to some strange (for historic reasons - no surprise) record layouts that we have. For example, we have the following code:
01 WS-DATA.
03 WS-A PIC X.
03 WS-B PIC S9(4) COMP-5.
03 WS-C PIC X.
CREATE TABLE MYTABLE (MY_CODE SMALLINT NOT NULL, MY_DATA CHAR(254) FOR BIT DATA)
MOVE "A" TO WS-A
MOVE 1 TO WS-B.
MOVE "C" TO WS-C.
MOVE 1 TO WS-SQL-CODE
INSERT INTO MYTABLE VALUES(:WS-SQL-CODE,:WS-DATA)
COMMIT
SELECT MY_DATA INTO :WS-DATA FROM MYTABLE WHERE MY_CODE = 1
At this point, the WS-A field has "A", WS-B is 'corrupt' and WS-C has spaces.
The reason why WS-B is corrupt is that prior to performing the INSERT, this field had X"00" and X"01" in it. This represented zero in comp-5. When I read it back in, I had X"00" followed by x"20". It looks to me like it is not handling the 'FOR BIT DATA' clause correctly. I don't know if this is a DB2 ODBC driver issue or a COBOL issue (and therefore not something I should be bothering MF with).
In an attempt to find out, I tried to run the same program using a Oracle database source and Oracle ODBC driver. Unfortunately, the program experiences a 907 error on the CREATE TABLE statement. This is being caused by the 'FOR BIT DATA' clause.
Any ideas chaps?
Also, the reason you had the issue regarding the group item is that I also have it, but gave you the simplified version of events. Our live programs actually move the record to a dummy data item.
Sign up
Already have an account? Login
Welcome to the Rocket Forum!
Please log in or register:
Employee Login | Registration Member Login | RegistrationEnter your E-mail address. We'll send you an e-mail with instructions to reset your password.
77 4th Avenue
Waltham, MA 02451 USA
