Originally published in the 3000 NewsWire.
The datatypes in Microsoft SQL Server look a lot like Oracle 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.
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.
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.
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.
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.
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.
"Inside Microsoft SQL Server 2000", Kalen Delany. Advanced, internal structures.
SQL Server books online - Microsoft web site - 36MB
SQL Team - news and forum site
SQL Server Resources
DTS web site
SQL Web sites
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.
Or another article on exporting to XML, with an introduction to XML for newcovers.