On 15-Nov-2011 06:58 , Jerry C. Adams wrote:
We currently get some of our orders through a very convoluted process
that we are trying to streamline. This requires some changes to the
software that sits on our Point-of-Sales PCs.
The software vendor has suggested FTP of the orders to our system
(FWIW, Model 250 running V5R1) directly into a file. The latter part
(direct to a file) is okay by me and even preferred. My question to
the group is: Isn't FTP just a character-by-character transmission?
Effectively. Although there may be block-mode available in addition
to stream-mode, the data is still written according to the definition of
the "record" when not transmitted using IMAGE\BINary type. The stream
of non-delimiter bytes are written across the record without regard to
any external [to the FTP] description for where\how the data should be
aligned.
That is, the item number may be 1-5 numbers (1-99999). If only '1'
is transmitted, would not that mean that everything gets PUT into the
wrong locations of an SQL DDL file on our system in that case?
Assuming that is the only datum and there is only one column, the
alignment of the data would probably be moot for character data if using
casting [versus byte-mapping to a numeric type; i.e. each of the
following are understood to be the numeric value of one:
cast('1 ' as dec(5))
cast(' 1 ' as dec(5))
cast(' 1' as dec(5))
Otherwise, the string of data would need to match the layout of the
Record Format just as would the data in a DS or [trigger] buffer for any
other RLA I\O activity. To enable both cast and mapping of the
character bytes to a decimal numeric data type, the value '1' would need
to be transmitted as '00001' instead; i.e. fixed-length of five bytes of
all valid digits. For mapping, the fifth digit position can actually be
one of many other valid values representing various forms of
positive\negative values; e.g. '0000N' is negative five, mapped from an
alpha field to a logical zoned decimal five digit field. For casting,
for negative values, the 5-digit number would be limited to 4-digits
with a leading minus sign.
My preference is for them to just use an ODBC connection and use SQL
to INSERT the records into the file.
If the data must eventually get to a described database file, then
any database transport method [e.g. ODBC for example] can be preferable
to a data-only protocol [e.g. FTP]. Especially apropos if the goal in
"trying to streamline" intends to eliminate intermediate copies of the
data being produced and processed in separate step(s) between the two
database end-points. Though if at the other end, the data does not
originate as database records\rows, then the issue for having [keeping
or making] a streamlined process could just move to their end.? Also,
security mechanisms and requirements might dictate or just give
preference to one transport method over another.
Using a stored procedure call, versus an INSERT statement directly,
might be desirable. That can help resolve some of security issues that
might exist for use of an INSERT directly. Plus the target file can
change in any variety of ways [name, location, layout] without any
impact to the caller of the stored procedure [which would be updated to
reflect any changes to the TABLE into which the data will be inserted].
Regards, Chuck
midrange.com
