One of the first questions of 2017 was what was the Error message associated with an ERROR-CODE returned from the PC REFLECT command. I googled REFLECT ERROR-CODE and I couldn't find the associated error message.
I tried to read thru some of the Attachmate/Micro-Focus Reflection manuals to no avail. Then I remembered the good old Help files in the Reflection directory. Of course you have to install the WinHelp helper program to read help files which hasn't been available since Windows 7. I vaguely remembered that the ERROR-CODES would be found in the RCL help file.
The following errors can be referenced with the ERROR-CODE function.
1 Transmit error. CTS or DSR required; if modem connection, no DCD present. 2 EOF on file read. 3 Timeout while waiting for expected or specified data. 4 Received unexpected data from host. 5 File transfer aborted by user. 6 Fatal block number sequence error on a file transfer. 7 Received a nonprocessable message during file transfer. 8 Data message received during file transfer was not the correct packet type. 9 File transfer aborted by host program. 10 Local file does not exist. 11 File transfer failed. 12 File transfer software is incompatible with the host file transfer software. 13 Disk error detected. 14 Too many consecutive NAKs received or sent during a file transfer. 15 Could not create local file. 16 Could not execute host file transfer program. 17 User requested termination of file transfer with STOP (not ABORT). 18 Local file already exists. 19 Unexpected error detected. 20 Unsupported terminal class (relates to IBM hosts). 21 VM in CP mode (relates to IBM hosts). 22 Bad syntax (relates to IBM hosts). 23 File transfer link failed. 24 File not updated. 25 No file selected. 26 Host software requires OLD-WRQ file transfer protocol. 101 No files were found to backup. 103 Backup terminated by user. 104 Backup terminated due to error. 120 Not a valid state file. 121 Error loading state file. 122 Must have an external LATCI to exit. 160 Bad command. 161 Command Language Syntax Error. 162 Error detected while executing command. 163 Decryption error. 164 Buffer overflow while transmitting. 201 System error, DDE operation not completed (DDE client error code). 202 Insufficient memory available for the operation (DDE client error code). 203 An invalid conversation was specified (DDE client error code). 204 Maximum conversation count was exceeded (DDE client error code). 205 Client message not acknowledged by server, timed out (DDE client error code). 206 Server sent negative acknowledgment (DDE client error code). 207 Server is busy, unable to complete DDE operation (DDE client error code). 208 Waiting for server acknowledgment of message (DDE client error code). 209 Invalid advise-link was specified (DDE client error code). 210 Maximum advise-link count exceeded (DDE client error code). 211 Advise-link to item already exists (DDE client error code). 212 Data from server is invalid or has invalid format (DDE client error code). 251 VINT14.386 not installed. 252 Invalid communications driver. 253 Not enough memory to load driver. 254 No more connection handles available. 255 Cannot load communications driver. 256 Cannot free overlay memory. 257 Invalid handle. 258 Can't find communication driver overlay. 259 Error opening device through Windows. 260 Required drivers not found. 261 VINT14 memory allocation error. 262 Port already in use. 263 Connection already in use. 264 Cannot connect to host. 265 No host name specified. 266 No more network handles available. 267 Invalid connection name. 268 Invalid password. 269 Character Delay value is out of range. 270 Line Delay value is out of range. 271 VINT14 not compatible with DLL. 272 Cannot delete default templates. 273 Cannot load a library. 274 Missing or invalid RCOMTASK.EXE. 275 Subfunction invalid or not supported. 276 Subfunction call failed. 277 Waiting for connection to come up. 278 Cannot change a default saved connection. 279 Error attaching to server. 280 Machine setup requires using the Windows serial driver. From command window type 'SET ENHANCED-SERIAL-DATACOMM NO'. 281 Could not create socket for yourconnection.Check your network software installation and configuration. 282 Could not resolve host name. 283 Host unreachable. 284 Connection refused by host. 285 Connection timed out. 286 General connection failure. 287 Host currently busy. 288 Insufficient resources for operation. 289 Host is not in service table. 290 LAT session control block is corrupt. 291 Connection terminated by user. 292 The VINT14.386 driver defined in SYSTEM.INIis a lower version level than RCOMNG.DLL. 293 Reflection communication module is an illegal version. 294 User canceled. 295 Invalid command issued to modem. 296 Failed to detect dialtone.Modem not hooked to telephone network. 297 Remote computer did not answer. NO ANSWER 298 Could not connect to remote computer. NO CARRIER 299 Could not connect to remote computer. Line is busy. 300 Could not communicate with the modem. Modem power is off or connection path is incorrect. 301 No modems installed. 302 Could not translate international phone number.No dialing plans defined. 303 User Changed Connection Options. 304 Could not connect using saved connection.The saved connection has been removed. 305 Required communication software is not supported in this environment. 306 Not attached to server. 307 Cannot detach from server while other NLAT connections are open. 308 Configuration string contains no keywords. 309 Value for keyword is invalid. 310 Configuration string contains an unknown keyword. 311 A keyword was found that has no value setting. 312 Unknown connection type requested. 313 Keyword value cannot be changed while a connection is open. 314 Changing a connection type while a connection exists is not allowed. 315 A value for a keyword exceeds the maximum allowable length. 316 Length of the keyword is too long. 317 Attempt to set a keyword to an unknown value. 318 Unable to open the reflect.ini file. 319 The reflect.ini file is write protected. 320 Could not translate phone number.Unbalanced dialing variable expression. 321 Could not translate phone number.Unknown dialing variable used. 322 Reflection cannot establish another session because this would exceed the limit established by your system administrator. 323 Invalid phone number. 324 Configuration string contains an inappropriate keyword for the current connection type. 325 Cannot open specified com port. 326 Cannot set transmit and receive buffer size for the specified com port. 327 Cannot set time out value for the specified com port. 328 Cannot configure the specified com port. 329 Connection failure. Please make sure your TCP/IP network is properly is installed. Push Help for network trouble shooting information. 330 Your modem responded to the command with an error. Click OK to ignore the error and continue. 331 NetWare User Name is invalid. 332 NetWare Password is required. 333 NetWare User Name or Password is invalid. 334 NetWare User Name required. 335 Unable to load all NLAT resources.Try quitting some other applications. 336 Unable to start NLAT.The NLATTask module is missing. 337 has a version level of .Reflection requires that this module have a version level greater than or equal to .
There are multiple ways of adding records to a database. The method and means depends upon the format of the data, however, the main goal is to get the data into a self-describing file, and then put the data into the database.
In order to add data to a dataset the best method is to create a self-describing file with the data you want to add and then simply put the data using the self-describing file. So the actual put of the data is very simple. Once you have a self-describing file in the same format for each field in order, the add of the data is as follows:
suprtool << EOD base MEMBRS,1,; in toadd put RESPONSE-FILE EXIT EOD
The above simply takes the self-describing file toadd and puts the records into the dataset RESPONSE-FILE.
Typically data comes in two ways:
So the trick really is how do I create a file that looks exactly like the data in RESPONSE-FILE with the data you want. The easy part is you can use RESPONSE-FILE.
suprtool << EOD base MEMBRS,5,; GET RESPONSE-FILE NUMRECS 1 EXT ACCOUNT=12345 EXT SUFFIX=0 EXT LINE-NUMBER=1 EXT TYPE-CODE="S" EXT ENTRY-DATE=20170116 EXT QUEST-CODE="SOME VALUE" EXT TELLER-INTLS="ABC" EXT MBR-BRANCH=1 EXT SUB-BRANCH=1 EXT RESPONSE="MEMBER RESPONSE TO QUESTION EXT CHANGE-DATE=20170116 out toadd,link exit EOD
Again if you have data in a file the premise is the same, which is to get the data in an SD file.
Now data can come in files in many formats12345 0 1 S 20170116 SOME VALUE ABC 1 1 MEMBER RESPONSE TO QUESTION 20170116You can use qedit to manipulate the data:
12345 0 1 S 20170116 SOME VALUE ABC 1 1 MEMBER RESPONSE TO QUESTION 20170116
You can take the above to the necessary commands by doing something like this:
qedit << EOD t mydata cq 1 :ACCOUNT=: 1 cq 1 :SUFFIX=: 2 cq 1 :LINE-NUMBER=: 3 cq 1 :TYPE-CODE=': 4 cq 1 :ENTRY-DATE=: 5 cq 1 :QUEST-CODE=': 6 cq 1 :TELLER-INTLS=': 7 cq 1 :MBR-BRANCH=: 8 cq 1 :SUB-BRANCH=: 9 cq 1 :RESPONSE=': 10 cq 1 :CHANGE-DATE=: 11 append :': 4,6,7,10 rm supruse k supruse,unn exit EOD
Then you could use the file you just created with Qedit in a suprtool task:
suprtool << EOD base MEMBRS,5,; GET RESPONSE-FILE NUMRECS 1 useq supruse out toadd,link exit EOD
The supruse file looks like this:
ACCOUNT=12345 SUFFIX=0 LINE-NUMBER=1 TYPE-CODE='S' ENTRY-DATE=20170116 QUEST-CODE='SOME VALUE' TELLER-INTLS='ABC' MBR-BRANCH=1 SUB-BRANCH=1 RESPONSE='MEMBER RESPONSE TO QUESTION CHANGE-DATE=20170116As mentioned the above could be used to add single records etc.
The most common method of receiving data that you want to add to a dataset is typically in .CSV format. Suprtool has been enhanced thru a series of enhancements that will allow a script to be built to import data from a .csv file.
Building a script to Import a .csv file to convert it to an SD file is a little complicated, but we have automated with the following Form. Instructions can be found on the page on how to operate, but following is a synopsis on what the form will generate.
Let's say you have a .csv file with the following data in it:"11111",1111,11111,+11111,+11111,11111,1,1111,11111,11111 "22222",2222,22222,+22222,+22222,22222,1,2222,22222,22222 "22222",2222,22222,+22222,+22222,22222,2,2222,22222,22222 "33333",3333,33333,+33333,+33333,33333,1,3333,33333,33333 "33333",3333,33333,+33333,+33333,33333,2,3333,33333,33333 "33333",3333,33333,+33333,+33333,33333,3,3333,33333,33333 "44444",4444,44444,+44444,+44444,44444,1,4444,44444,44444 "44444",4444,44444,+44444,+44444,44444,2,4444,44444,44444 "44444",4444,44444,+44444,+44444,44444,3,4444,44444,44444 "44444",4444,44444,+44444,+44444,44444,4,4444,44444,44444 "55555",5555,55555,+55555,+55555,55555,1,5555,55555,55555 "55555",5555,55555,+55555,+55555,55555,2,5555,55555,55555 "55555",5555,55555,+55555,+55555,55555,3,5555,55555,55555 "55555",5555,55555,+55555,+55555,55555,4,5555,55555,55555 "55555",5555,55555,+55555,+55555,55555,5,5555,55555,55555
The above is a common test file that we use to insure we select and can access all the different data types. Below is the structure of this SD file:
File: FILE1SD.NEIL.GREEN (SD Version B.00.00)
Entry: Offset
CHAR-FIELD X5 1
INT-FIELD I1 6
DBL-FIELD I2 8
PACKED-FIELD P12 12
PACKED*-FIELD P12 18
QUAD-FIELD I4 24
ID-FIELD I1 32
LOGICAL-FIELD K1 34
DBLLOG-FIELD K2 36
ZONED-FIELD Z5 40
If you paste the above into the form and Convert To Code the following gets generated:
!run qedit.pub.robelle
t infile.csv,data
set keep var off
k,yes
exit
!setvar myrecsize finfo('infile.csv',14)
!if !myrecsize < 0 then
!setvar myrecsize !myrecsize * -1
!else
!setvar myrecsize !myrecsize * 2
!endif
!run suprtool.pub.robelle
set varsub on
purge tempfile
purge outsdfile
in infile.csv
def record,1,!myrecsize
DEFINE x-CHAR-FIELD,1,7,byte
DEFINE x-INT-FIELD,1,6,byte
DEFINE x-DBL-FIELD,1,11,byte
DEFINE x-PACKED-FIELD,1,12,byte
DEFINE x-PACKED*-FIELD,1,12,byte
DEFINE x-QUAD-FIELD,1,20,byte
DEFINE x-ID-FIELD,1,6,byte
DEFINE x-LOGICAL-FIELD,1,5,byte
DEFINE x-DBLLOG-FIELD,1,10,byte
DEFINE x-ZONED-FIELD,1,6,byte
EXTRACT x-CHAR-FIELD=$split(record,first,',')
EXTRACT x-INT-FIELD=$split(record,',',1,',')
EXTRACT x-DBL-FIELD=$split(record,',',2,',')
EXTRACT x-PACKED-FIELD=$split(record,',',3,',')
EXTRACT x-PACKED*-FIELD=$split(record,',',4,',')
EXTRACT x-QUAD-FIELD=$split(record,',',5,',')
EXTRACT x-ID-FIELD=$split(record,',',6,',')
EXTRACT x-LOGICAL-FIELD=$split(record,',',7,',')
EXTRACT x-DBLLOG-FIELD=$split(record,',',8,',')
EXTRACT x-ZONED-FIELD=$trim($split(record,',',9,last))
output tempfile,link
xeq
in tempfile
DEFINE CHAR-FIELD,1,5,byte
DEFINE INT-FIELD,1,2,integer
DEFINE DBL-FIELD,1,4,double
DEFINE PACKED-FIELD,1,6,packed
DEFINE PACKED*-FIELD,1,6,packed
DEFINE QUAD-FIELD,1,8,integer
DEFINE ID-FIELD,1,2,integer
DEFINE LOGICAL-FIELD,1,2,logical
DEFINE DBLLOG-FIELD,1,4,logical
DEFINE ZONED-FIELD,1,5,display
DEFINE z-INT-FIELD,x-INT-FIELD,display
DEFINE z-DBL-FIELD,x-DBL-FIELD,display
DEFINE z-PACKED-FIELD,x-PACKED-FIELD,display
DEFINE z-PACKED*-FIELD,x-PACKED*-FIELD,display
DEFINE z-QUAD-FIELD,x-QUAD-FIELD,display
DEFINE z-ID-FIELD,x-ID-FIELD,display
DEFINE z-LOGICAL-FIELD,x-LOGICAL-FIELD,display
DEFINE z-DBLLOG-FIELD,x-DBLLOG-FIELD,display
DEFINE z-ZONED-FIELD,x-ZONED-FIELD,display
set cleanchar ""
clean '^34'
EXTRACT CHAR-FIELD=$TRIM($CLEAN(x-CHAR-FIELD))
EXTRACT INT-FIELD=$NUMBER(z-INT-FIELD)
EXTRACT DBL-FIELD=$NUMBER(z-DBL-FIELD)
EXTRACT PACKED-FIELD=$NUMBER(z-PACKED-FIELD)
EXTRACT PACKED*-FIELD=$NUMBER(z-PACKED*-FIELD)
EXTRACT QUAD-FIELD=$NUMBER(z-QUAD-FIELD)
EXTRACT ID-FIELD=$NUMBER(z-ID-FIELD)
EXTRACT LOGICAL-FIELD=$NUMBER(z-LOGICAL-FIELD)
EXTRACT DBLLOG-FIELD=$NUMBER(z-DBLLOG-FIELD)
EXTRACT ZONED-FIELD=$NUMBER(z-ZONED-FIELD)
output outsdfile,link
exit
The script used Qedit to make the file a fixed length file, then uses a $split task to divide up in to separate byte fields. Then the next task cleans out the quotes for byte type fields and converts to the proper number target field, using the $number function.
A customer asked recently how to test a portion of a field, for certain values, specifically a date field. This is relatively easy regardless of the type of the field, whether byte or numeric.
The specifics of the test the customer wanted was to determine the DD portion of a ccyymmdd date field.
>in dates
>form
File: dates (SD Version B.00.00) Has linefeeds
Entry: Offset
DUE-DATE-X X8 1 <>
DUE-DATE-NUM I2 9 <>
Entry Length: 12 Blocking: 1
>list
>xeq
>IN dates (0) >OUT $NULL (0)
DUE-DATE-X = 20170131 DUE-DATE-NUM = 20170131
>IN dates (1) >OUT $NULL (1)
DUE-DATE-X = 20170131 DUE-DATE-NUM = 20170131
>IN dates (2) >OUT $NULL (2)
DUE-DATE-X = 20170131 DUE-DATE-NUM = 20170131
>IN dates (3) >OUT $NULL (3)
DUE-DATE-X = 20170131 DUE-DATE-NUM = 20170131
>IN dates (4) >OUT $NULL (4)
DUE-DATE-X = 20170131 DUE-DATE-NUM = 20170131
>IN dates (5) >OUT $NULL (5)
DUE-DATE-X = 20170131 DUE-DATE-NUM = 20170131
>IN dates (6) >OUT $NULL (6)
DUE-DATE-X = 20170131 DUE-DATE-NUM = 20170131
>IN dates (7) >OUT $NULL (7)
DUE-DATE-X = 20170131 DUE-DATE-NUM = 20170131
>IN dates (8) >OUT $NULL (8)
DUE-DATE-X = 20170131 DUE-DATE-NUM = 20170131
>IN dates (9) >OUT $NULL (9)
DUE-DATE-X = 20170131 DUE-DATE-NUM = 20170131
>IN dates (10) >OUT $NULL (10)
DUE-DATE-X = 20170131 DUE-DATE-NUM = 20170131
>IN dates (11) >OUT $NULL (11)
DUE-DATE-X = 20170131 DUE-DATE-NUM = 20170131
>IN dates (12) >OUT $NULL (12)
DUE-DATE-X = 20170131 DUE-DATE-NUM = 20170131
>IN dates (13) >OUT $NULL (13)
DUE-DATE-X = 20170131 DUE-DATE-NUM = 20170131
>IN dates (14) >OUT $NULL (14)
DUE-DATE-X = 20170131 DUE-DATE-NUM = 20170131
IN=15, OUT=15. CPU-Sec=1. Wall-Sec=1.
There are two methods for determining a portion of any field, the following isolates the DD portion of a date field:
While this syntax is no longer documented, you may see this in an old job:
if due-date-x(1,7,2)="31"
The 1 is the first instance of a compound field, starting in the 7th byte for 2 bytes:
The syntax below allows you to document what it is that you are testing:
def dd,due-date-x[7],2,byte if dd="31"By giving the field you want a test a more meaningful name it simply allows the script to be self-documenting.
Since numeric fields are a series of bits you can easily define a portion of the data in a meaningful way, but you can use some simple math tricks to get at a portion of the data:
if $truncate(due-date-num mod 100) = 31
So the above statement simply takes the date, 20160131 and what mod does is take the remainder of a divide. So Suprtool, divides the date by 100, and takes the remainder, which in this case would be the last two digits of the date field, and then truncates that to a whole number, just in case of a rounding issue.
You can even look at the month with more math tricks:
if $truncate(due-date-num / 100 mod 100) = 01
The above, takes the date of 20160131, divides by 100, which becomes 201601 and then the mod 100 ends up with the MM portion, by taking the remainder of a divide by 100, which in this case is the 01.
input tmpnsf1
{ Define Fields as Byte, with length of Field }
def x-acct,account,4,byte
def x-suffix,suffix,2,byte
def x-date,entry-date,4,byte
def x-sequence,sequence,4,byte
def x-beg-bal,beg-bal,4,byte
def targkey,1,18
ext account
ext suffix
ext entry-date
ext amount-sh
ext balance-sh
ext description
ext beg-bal
ext sequence
{ Use String Addition to build the Key }
ext targkey = x-acct + x-suffix + x-date + x-beg-bal + x-sequence
out tmpnsf11,link
xeq
exit
In this case, we've made a key made up five fields, in one step.
How do you get the hour on HP-UX date command:
date "+%H" 06
Put in a variable:
export sthour=`date "+%H"` echo $sthour 06
Use in Suprtool:
suprtool
SUPRTOOL/UXia/Copyright Robelle Solutions Technology Inc. 1981-2017.
(Version 5.9.10 Internal) Fri, Mar 10, 2017, 6:59 AM Type H for help.
Build 2
{first set varsub on and you can reference the variable}
>set varsub on
>in x
>if newdate=$sthour
{There is also the varsubdebug setting that will show the variable as it is resolved}
>reset if
>set varsubdebug on
>if newdate=$sthour
vd:if newdate=06
>ea
vd:ea
We've had a few questions the past two weeks about removing duplicate records and controlling which records appear on top and are subsequently kept.
First off the premise of dup none keys is that you sort the file in the order you want to have and then with dup none keys suprtool keeps the first record in sort order, and throws the rest away.
Consider the following key values, Account and Suffix, sorted by Account (ascending) and Suffix descending, which would be created by something like:
get share-file sort account sort suffix,descending out sharefile,link xeqThe data would then look as follows:
11111 10 11111 09 11111 05 11111 00 22222 50 22222 50 22222 00 33333 00If you wanted to remove duplicates for both Account and Suffix you would do:
get share-file sort account sort suffix,descending dup none keys out sharefile,link xeqThe result would be:
11111 10 11111 09 11111 05 11111 00 22222 50 22222 00 33333 00If you only wanted the dup command to look at the account number, you specify the sort level for the dup command to look at by a simple addition to the dup command:
get share-file sort account sort suffix,descending dup none keys 1 out sharefile,link xeqWhen you specify the 1, which means look at the first sort key, or in the case above only look at the account. The result would be:
11111 10 22222 50 33333 00The above allows you to control which records are kept via the dup command.