Insert PIC N into NCHAR column in DB

Please, I have problem with inserting PIC N variable into NCHAR column in DB (MSSQL)

I read line from UTF-8 file to PIC N record - here is everything ok, but when I do SQL INSERT from this variable to NCHAR column in DB

exec sql
insert into AAA111 (TEXT)
values(:data1)
end-exec


I got complete rubbish data here. How to do this correctly?

Thanks

#SQL
#cobolnetexpress

PIC N fields are UTF-16 not UTF-8. How are you reading the UTF-8 file into your program? After you move the UTF-8 data to the PIC N field does the value look correct under the debugger? Is it stored as 2 bytes per character?

Do you have the directive NSYMBOL"NATIONAL" on? If you do not explicitly define the data as PIC N USAGE NATIONAL then this would be required in order to treat PIC N fields as UTF-16.

Please, I have problem with inserting PIC N variable into NCHAR column in DB (MSSQL)

I read line from UTF-8 file to PIC N record - here is everything ok, but when I do SQL INSERT from this variable to NCHAR column in DB

exec sql
insert into AAA111 (TEXT)
values(:data1)
end-exec


I got complete rubbish data here. How to do this correctly?

Thanks

#SQL
#cobolnetexpress

Mabye this is my problem, that I don't correctly process reading of UTF-8 file. My code is below.

Now I am able to write data to database, but in file I have chars in cyrillic, and these are destroyed.

Please can you help me with correct reading of UTF-8 file?

C $SET DIRECTIVES (SBODBC.DIR) NSYMBOL"NATIONAL"
IDENTIFICATION DIVISION.
************************
PROGRAM-ID. unicode1.
*
ENVIRONMENT DIVISION.
*********************
CONFIGURATION SECTION.
SPECIAL-NAMES.
input-output section.
file-control.
select bank-file assign to f-bank-file
organization line sequential
file status is fstat.
******************************************************************
DATA DIVISION.
**************
*
FILE SECTION.
*============
fd bank-file.
01 b-rec.
02 b-char pic n occurs 81 usage national.
*
WORKING-STORAGE SECTION.
*=======================
01 f-bank-file pic x(100) value 'soub.txt'.
01 podm pic n(100).
01 ucet pic n(15).
01 desc pic n(50).
01 banka pic n(34).
01 fstat pic xx.
01 konec pic x.
01 kr pic x.
01 w-x pic 9(4).
01 data2 pic n(100).
01 bom pic n value nx'FEFF'.
01 cr pic n value nx'000D'.
01 lf pic n value nx'000A'.
01 db pic x(100) value 'LLPSY_PK1_BPS_BMP'.

01 data1 pic n(100) usage national.
*
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
*
01 MFSQLMESSAGETEXT PIC X(600).
01 WS-CONNECTSTRING PIC X(150).
01 WS-DATABASE PIC X(32).
01 WS-SERVER PIC X(80).
01 WS-HOSTVAR PIC X(250).
01 H-SQL-STATEMENT PIC X(700).
01 H-DESC PIC N(4).

EXEC SQL END DECLARE SECTION END-EXEC.

EXEC SQL INCLUDE SQLCA END-EXEC.

********************
PROCEDURE DIVISION.
******************************************************************
* CONTROL LOOP FOR MAIN PROGRAM *
******************************************************************
MAIN SECTION.
*----------------------
MAIN-START.
*
* Change these 2 fields to point at your database server and DB
*
MOVE "CZDMV025" TO WS-SERVER
MOVE "SunSystemsData" TO WS-DATABASE
PERFORM SZ0000-PROCESS

.
MAIN-EXIT.
STOP RUN.

SZ0000-PROCESS SECTION.
PZ0000-START.
PERFORM SZ1000-CONNECT-TO-DB
IF SQLSTATE NOT = "00000"
EXIT SECTION
END-IF
*
perform soubor-test
perform test-sql
*
PERFORM SZ9900-DISCONNECT-FROM-DB
.
PZ0000-EXIT.
EXIT.

SZ1000-CONNECT-TO-DB SECTION.
*******************************************************************
* Connect to database *
*******************************************************************
Z1000-START.

MOVE SPACES TO WS-CONNECTSTRING.
STRING "DRIVER={SQL Server};"
DELIMITED BY SIZE
";DATABASE=" WS-DATABASE
DELIMITED BY "|"
";SERVER=" WS-SERVER
DELIMITED BY "|"
";AutoTranslate=no" DELIMITED BY SPACE
";Trusted_Connection=yes"
DELIMITED BY SIZE
INTO WS-CONNECTSTRING
END-STRING

EXEC SQL
CONNECT USING :WS-CONNECTSTRING
RETURNING :WS-HOSTVAR
END-EXEC.
*
Z1000-EXIT.
EXIT.
*
soubor-test section.
*********************
initialize konec data1 w-x kr
open input bank-file
perform cteni until konec = 'y'
close bank-file
.
*
cteni section.
***************
read bank-file
at end
move 'y' to konec
not at end
perform zpracuj
end-read
.
*
zpracuj section.
*****************
initialize data1
perform varying w-x from 1 by 1 until w-x > 81
move b-rec(w-x:1) to data1(w-x:1)
end-perform
exec sql
insert into AAA111 (TEXT)
values(:data1)
end-exec
exec sql
commit
end-exec
.
*
test-sql section.
******************
initialize podm desc ucet banka
exec sql
select TRANS_DESC, ACCOUNT into
:desc,
:ucet
from LLPSY_PK1_BPS_BMP
where SEQNO = 1
end-exec

exec sql
select BANK_ACC_NUM into :banka
from :db
where TRANS_DESC = :desc
end-exec
display banka
.
*
SZ9900-DISCONNECT-FROM-DB SECTION.
*******************************************************************
* Connect to database *
*******************************************************************
Z9900-START.

EXEC SQL
DISCONNECT
END-EXEC.
*
Z9900-EXIT.
EXIT.
*
******************************************************************
* END OF SOURCE *
******************************************************************

Please, I have problem with inserting PIC N variable into NCHAR column in DB (MSSQL)

I read line from UTF-8 file to PIC N record - here is everything ok, but when I do SQL INSERT from this variable to NCHAR column in DB

exec sql
insert into AAA111 (TEXT)
values(:data1)
end-exec


I got complete rubbish data here. How to do this correctly?

Thanks

#SQL
#cobolnetexpress

UTF8 files normally begin with a 3 byte BOM of "EF BB BF" and your program seems to be looking for "FEFF" which actually indicates that the file contains UTF16 characters. See here:

A line sequential file should only contain printable ASCII characters and the default behavior if it encounters a null character when reading is to strip the null character out of the record. To change this you would need to set the file handler configuration option INSERTNULL to OFF or run with COBSW -N set.

There is no direct way to read a UTF8 file in COBOL. UTF8 characters are variable length and can contain from 1 to 4 bytes I believe. You could read these files using the byte stream routines like CBL_READ_FILE, etc but you would have to do your own processing for end of record, etc.

In Visual COBOL we have added a library routine called CBL_STRING_CONVERT which will convert a UTF8 string to UTF16 which would allow you to store UTF8 data in a PIC N field. This routine does not exist in Net Express/Server Express.

Do you have a small file that you could either attach to this post or send to me to look at? If I can determine what type of file it actually is then I can provide you with better direction... My email address is chris.glazier@microfocus.com. You could also open up a support incident with Customer Care and we could handle it that way.

Thanks.

Please, I have problem with inserting PIC N variable into NCHAR column in DB (MSSQL)

I read line from UTF-8 file to PIC N record - here is everything ok, but when I do SQL INSERT from this variable to NCHAR column in DB

exec sql
insert into AAA111 (TEXT)
values(:data1)
end-exec


I got complete rubbish data here. How to do this correctly?

Thanks

#SQL
#cobolnetexpress

[quote user=""Chris"][/quote]

UTF8 files normally begin with a 3 byte BOM of "EF BB BF"

I would say "may begin". In my experience, the majority of UTF-8 text files do not have a BOM. Indeed, since ASCII is a subset of UTF-8 and a great many text files are ASCII, it's fair to say most UTF-8 text files do not start with a BOM.

The BOM is not useful for UTF-8 (there's only one byte ordering for UTF-8), so including it is not particularly useful anyway, except when it's important to flag a file as UTF-8 and there's no better metadata channel available.

That said, the UTF-8-encoded BOM can be a useful heuristic for deciding a file is likely UTF-8. Its absence just isn't any indication that the file isn't.

Please, I have problem with inserting PIC N variable into NCHAR column in DB (MSSQL)

I read line from UTF-8 file to PIC N record - here is everything ok, but when I do SQL INSERT from this variable to NCHAR column in DB

exec sql
insert into AAA111 (TEXT)
values(:data1)
end-exec


I got complete rubbish data here. How to do this correctly?

Thanks

#SQL
#cobolnetexpress

I am trying to read file via:

call "CBL_READ_FILE"
using file-handle
file-offset
byte-count
flags
buffer
end-call

But when buffer is PIC X, standard chars are ok, but cyrillic chars are destroyed. And when I try it with buffer as PIC N, I get complete garbage (chinese chars), when I store data to DB.

So how to correctly convert UTF-8 chars to PIC N?

Please, I have problem with inserting PIC N variable into NCHAR column in DB (MSSQL)

I read line from UTF-8 file to PIC N record - here is everything ok, but when I do SQL INSERT from this variable to NCHAR column in DB

exec sql
insert into AAA111 (TEXT)
values(:data1)
end-exec


I got complete rubbish data here. How to do this correctly?

Thanks

#SQL
#cobolnetexpress

I received your file by email and have confirmed that it is a UTF-8 text file with a BOM in it and it does contain characters of varying length.

The CBL_READ_FILE only reads the file as a sequence of bytes for the length specified. These will most likely not look like characters when debugging unless they are actual ASCII characters.

Like I mentioned previously in Visual COBOL we have added a routine to convert UTF-8 to UTF-16 but this is not available in NX. (upgrade to VC?) In NX your best bet might be to use a 3rd party conversion utility to convert the files from UTF-8 to UTF-16 so that you do not have to do the character conversion yourself.

Michael Wojcik might have some other ideas on this subject...

Please, I have problem with inserting PIC N variable into NCHAR column in DB (MSSQL)

I read line from UTF-8 file to PIC N record - here is everything ok, but when I do SQL INSERT from this variable to NCHAR column in DB

exec sql
insert into AAA111 (TEXT)
values(:data1)
end-exec


I got complete rubbish data here. How to do this correctly?

Thanks

#SQL
#cobolnetexpress

Ok, thanks. I solved it whit 3rd party conversion program. After conversion to UTF-16 everything is working.
But I have to read file char by char (defined as sequential). Is there any possibility to read UTF-16 file as line-sequential?

Please, I have problem with inserting PIC N variable into NCHAR column in DB (MSSQL)

I read line from UTF-8 file to PIC N record - here is everything ok, but when I do SQL INSERT from this variable to NCHAR column in DB

exec sql
insert into AAA111 (TEXT)
values(:data1)
end-exec


I got complete rubbish data here. How to do this correctly?

Thanks

#SQL
#cobolnetexpress

You could read the data as line sequential if you use the config option INSERTNULL=OFF so that it won't strip out the non-displayable characters on the read. When I convert your UTF-8 file to UTF-16 using an editor that I have the resultant file uses 00 0D 00 0A for the record delimiter which would not work in a line sequential file. Is this what your converted file looks like as well? If your converted file did not contain a BOM and had only fixed length records then you could probably define it so the record delimiter were part of the record area and read it that way but if these are variable length records or you have a BOM on the first record then would need to read it a byte at a time or use CBL_READ_FILE to read a buffer and then search for the record terminator manually.