Exporting to SQL Server with Suprtool

Export from TurboIMAGE to SQL Server

Originally published in the 3000 NewsWire.

By Bob Green, Robelle

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

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 your Windows COBOL compiler supports it.

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

Suppose you have this CUSTOMER table in your SQL Server database:

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

And you have this M-CUSTOMER dataset in your TurboIMAGE database:

    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

All of the X fields in TurboIMAGE have been converted to VARCHAR fields.

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'

In this example, I used Suprtool's Extract command to reorder the columns to be the order of the SQL Server table. 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.

www.acucorp.com/Solutions/access3.html

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. To wrap up, below are 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 - 36MB
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

If you are migrating an application from MPE, consult our Migration Resource Page.

To learn more about exporting, including MS Access and Excel, read our tutorial, Sharing HP e3000 Data to the World.

You might also be interested in two Suprtool articles on exporting 3000 data to mySQL, exporting to Oracle, Oracle datatypes, and exporting to Excel.

Or another article on exporting to XML, with an introduction to XML for newcovers.