Re: FTP Format

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