Robelle Sponsor Message

July 2002

Get the most out of your Robelle Tools

SQL Server and TurboIMAGE Data

By Bob Green

Last month we explored the process of transforming TurboIMAGE data into formats that are acceptable to Oracle. If you haven’t read that article, this would be a good time to read it, since it covers the basics of MPE/IMAGE datatypes, which are important regardless of why you are transforming your data. You can read it at www.robelle.com/tips/oracle-datatypes.html. The datatypes in Microsoft SQL Server look a lot like Oracle datatypes.

Character Datatypes

For character data, the most common datatype is VARCHAR(n) with a specified maximum number of characters. If all data values will be the same length, you can use CHAR(n) instead. If you want to use UNICODE values (i.e., characters beyond the 256 coded in ANSI such as appear in non-English languages), you will use NCHAR(n) and NVARCHAR(n).

Numeric Datatypes

For integer values without a decimal place, you can use Integer datatypes:

TINYINT = 8 bits, 1 byte, unsigned, values 0 to 255.

SMALLINT = 16 bits, 2 bytes, signed, values –32,768 to +32,767. Same as I1 in TurboIMAGE.

INT or INTEGER = 32 bits, 4 bytes, signed, values –2B to + 2B. Same as I2 in TurboIMAGE.

BIGINT = 64 bits, 8 bytes, signed integer, aka QUAD; for very large values, up to 18 digits. This was introduced in SQL Server 2000 and is the same as I4 in TurboIMAGE. Before you select this datatype, ensure that COBOL supports it on your platform.

NUM or NUMBER or DEC = numbers with decimal places. You specify a precision and scale for the values. Precision is the maximum total digits in the values, with 38 the largest allowed by SQL Sever. Scale is the number of places to the right of the decimal. The maximum number of digits that can be placed to the left of the decimal is precision-scale. For example, DEC(7,2) means the same as S9(5)V9(2) in COBOL. NUMERIC or FLOAT is the datatype for any value with a decimal place. NUMERIC in SQL Server is much like NUMERIC in Oracle, although it does not have the odd “negative scale factors” of Oracle (scale factor-3 in Oracle actually multiplies the value by 1000!).

FLOAT(n) = approximate numeric values in floating point format. Supported in 4 byte and 8 byte formats. A floating point number has an exponent and a mantissa. FLOAT(n) specifies number of bits for the mantissa, which can be up to 53. 1 through 24 specify a single precision real (4 bytes) and 25 through 53 specify Double Precision (8 bytes). Same as e2 and e4 in TurboIMAGE.

Other SQL Server datatypes that you will find useful are MONEY and DATETIME.

SQL Tables and TurboIMAGE Datasets

Take a look at Figure 1 below. In the figure, suppose you have a CUSTOMER table in your SQL Server database, and you have a M-CUSTOMER dataset in your TurboIMAGE database. (All of the X fields in TurboIMAGE have been converted to VARCHAR fields.)

Figure 1

CUSTOMER Table Data

Column Name          Datatype         Nullable

CustomerID           INT              No
FirstName            VARCHAR(10)      No
LastName             VARCHAR(16)      No
Address1             VARCHAR(26)      No
Address2             VARCHAR(26)      No
City                 VARCHAR(12)      No
State                VARCHAR(2)       No
ZipCode              VARCHAR(16)      No
CreditRating         DECIMAL(9,2)     No
CustomerStatus       VARCHAR(2)       No

     M-CUSTOMER       Master                  Set# 1
        Entry:                     Offset
           CITY                 X12     1
           CREDIT-RATING        J2     13   s9(7)V9(2)
           CUST-ACCOUNT         Z8     17  Search Field
           CUST-STATUS          X2     25
           NAME-FIRST           X10    27
           NAME-LAST            X16    37
           STATE-CODE           X2     53
           STREET-ADDRESS      2X25    55
           ZIP-CODE             X6    105
     Capacity: 211 (7)  Entries: 12  Bytes: 110
     

The CUST-ACCOUNT number field in TurboIMAGE was a Z8 field to enforce better hashing of the values, but Z indicates that it always contains numeric values. Therefore, INT is an appropriate SQL Server datatype for CustomerID.

The CREDIT-RATING field is a 32-bit integer on the HP 3000, defined with two decimal places in the COBOL programs. Since SQL Server has a NUMERIC datatype that is aware of decimal places, it is more appropriate to use that datatype than INT.

The repeated item STREET-ADDRESS (2X25) has been converted into ADDRESS1 and ADDRESS2 in SQL, since SQL does not have repeated data items.

The ZIPCODE field has been expanded from X(6) to VARCHAR (16) to handle extended and foreign postal codes.

Moving TurboIMAGE Data to SQL Server

There are three ways to move your TurboIMAGE data to SQL Server:

• Export your data to a file, then use the BULK INSERT statement in SQL.

• Export your data to a file, then use the BCP command-line utility (BCP stands for Bulk Copy Program).

• Export your TurboIMAGE data to a file or create an ODBC link to it, then use Data Transformation Services (DTS).

BULK INSERT and BCP are very similar in their parameters and options, but BULK INSERT is faster because it doesn’t go through as many layers of network code. DTS is a much more sophisticated service, including a wizard to help define your import or export transaction.

The default SQL Server import file expects all field values to be in character format, then uses Tab as the field terminator and \newline as the row terminator.

Use Suprtool For SQL Server Import

Suprtool from Robelle has all the options needed to generate an import file that is compatible with SQL Server.

For example, to export the contents of the M-CUSTOMER dataset to the CUSTOMER table, you would use the following steps:

On the HP 3000, SUPRTOOL extracts the fields in the order they appear in the SQL table (or you could use a FORMAT file with SQL Server to do the reordering, but SUPRTOOL EXTRACT seems simpler to me):

:run suprtool

Base custdb.base

Get m-customer

Item credit-rating,decimal,2

Extract cust-account,name-first,name-list

Extract street-address(1),street-address(2)

Extract city, state-code,zip-code,credit-rating,cust-status

Output sdfile,link

Exit

This Suprtool task gets the column values out of the dataset and puts them in the proper order for the SQL table, but they are still in the native HP 3000 format. They still need to be converted to ASCII characters and have Tab terminators inserted. This is done using SUPRTOOL’s STExport utility. Here are the commands to take the self-describing file (SDFILE) created by SUPRTOOL and convert it into the file that SQL Server expects:

:run stexport

Input sdfile (created above by Suprtool)

Delimiter tab

Quote none

Date yyyymmdd

Output sqls01

Exit

By default, STExport creates a variable length record file for output. This is just what we need to copy to the Windows server.

(Note: Although there are no date fields in this table, I included the suggested date format, YYYYMMDD, since this format is always recognized by SQL Server.)

Use FTP to transfer the STExport output file to your SQL Server system, but remember to do it as an ASCII transfer, not a BINARY transfer. This is so that the \newline characters are translated properly at the end of each row.

:run ftp.arpa.sys

open wins2.Robelle.com

user admin passwd

ascii

put sqls01 sqls01.txt

quit

On the Windows Server, you can use BCP or BULK INSERT to insert the rows into the CUSTOMER table. In BCP you select the character option (-c):

bcp custdb..customer in sqls01.txt –c

–S servername –U userid –P password

With BULK INSERT, you want to select ‘char’ as the file type:

BULK INSERT custdb..customer from “c:\sqls01.txt”

WITH DATAFILETYPE=’char’

If the file from the HP 3000 does not have the columns in the order of the SQL Table, or you need to skip some fields, then you need to create a Format File. This is beyond the scope of this article, but is described well in the SQL Server user documentation.

Language Pre-processors

With Oracle, the package comes with pre-processor for Fortran and COBOL, but SQL Server seems to only come with a pre-processor for C++.

What if you want to do SQL Server functions in your COBOL program? You must look to your compiler vendor.

For example, AcuSQL has an Embedded SQL (ESQL) precompiler that lets you embed standard SQL directly into ACUCOBOL program. There’s more information at www.acucorp.com/Solutions/access3.html and www.acucorp.com/Solutions/acusql.html

I looked for a FORTRAN precompiler for SQL Server, but did not find one, so that problem is left to the reader. I am including some resources on SQL Server to help you with your migration.

SQL Server Resources

Teach Yourself Microsoft SQL Server 7 in 24 Hours by Matthew Shepker. Book for beginners.

Inside Microsoft SQL Server 2000, Kalen Delany. Advanced, internal structures.

SQL Server books online – Microsoft Web site: www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

SQL Team – news and forum site: www.sqlteam.com

SQL Server Resources: www.sqldts.com/dblinks.asp?nav=1,2,30,1

SQL City: www.mssqlcity.com

DTS Web site: www.sqldts.com

DTS FAQ: www.sqldts.com/catlist.asp?nav=1,6,6,2

SQL Web sites: www.mssqlcity.com/Links.htm