I'm working on creating a customer table which will be synced to a customer ISAM file, so I can use it for more advanced search routines.

I'm having trouble deciding on what the best/easiest way to match A-Shell data types to MySQL data types.

The customer record is like the following:
What is the best/easiest way to store the binary arrays in the table while being able to retrieve them in A-Shell?

1. Is there a way to do this with an overlay?
2. Maybe have the Table value be a double and then calculate the value shifting each array element the necessary # of bytes?

In theory there should be no problem using overlays to combine multi-field structures (such as cust'salesman'numberx in the above example) using type BINARY(n). (where n is the size of the structure in bytes).

HOWEVER, there is definitely a problem in coming up with the appropriate syntax for an INSERT statement to insert it. (As it stands, the only method offered in ASQL for inserting data involves the application creating a valid INSERT statement, which is typically made up entirely of printable characters.) Offhand I'm not sure what standards exist for specifying binary data within an INSERT string made up of ASCII characters.

Packing an array of four 2-byte fields into a DOUBLE won't work either because of the difficulty of representing the resulting value in printable characters. (You might get close but are probably going to lose accuracy somewhere.)

You could, however, probably get away with packing four 1-byte fields into an INT, since nothing would be lost when displaying the resulting 32 bit INT in decimal notation.

I'm pretty sure that each database engine offers extensions in the form of functions, metacharacters, statement options, etc. for handling binary data, but I'm going to have to research this a bit before I can give you an answer.

On the retrieval side, I don't think there should be any problem, as long as you retrieve directly into the proper data type (using SQLOP_FETCH_ROW with the FETCHR_FIELDS option). Retrieving into a string and then converting will surely not work though.

Actually, after further investigation, the situation seems nearly reversed from how I described it. It does appear to me that you can output a series of unformatted (raw, binary) bytes, at least using the MySQL API, with the CHAR(n1,...,nx) function:

(Not that having to represent a large unformatted structure as a series of bytes using the CHAR function is particular convenient, but it does at least provide a path. And you can always overlay an array of bytes on top of any other structure.)

The problem is on the read side, where, at least in the MySQL API, the mechanism we are using starts with retrieving ASCII representations of the fields and only then converts them into the target types. That doesn't work so well with binary data.

For another perspective, here's what the MySQL command line query shows when I modified the sqltest4 program to add a binary/unformatted field (ratex) and populated it with CHAR(1,2,3,4,5,6,7,8):



That makes it clear that the binary data is getting written, but also that retrieving it in ASCII representation isn't very practical. So, I need to modify the connector to find a different method for retrieving fields that is more binary friendly. I also need to upgrade the connectors to be compatible with the new dynamic variable architecture.

As an aside, it seems odd to me that, at least under MySQL, the syntax for embedding control characters in strings by escaping them with backslash (e.g. '\t' = TAB) doesn't appear to support arbitrary numeric bytes (e.g. '\005' for chr(5)). It only supports a limited set of control characters that are commonly found in otherwise normal text. So the fancy routines which escape and unescape data don't really help us much.

One more aside: considering the complications of unformatted binary data, perhaps it would be easier to just define each of the array elements as separate columns (e.g TINYINT for B1, SMALLINT for B2, MEDIUMINT for B3, INT for B4, etc)?

Uh, amend that last post: I had a simple bug in my test program. Retrieving the binary data works fine, as long as you retrieve it into the proper (in this case unformatted) type variable directly and use the FETCHR_FIELDS mode of SQLOP_FETCH_ROW.

So to recap, my rec structure look something like the example above (with the unformatted structure xarray redefined as an array of 1 byte binaries, although it could also be over-layed with some other structure at the same time.) I defined the column as BINARY(8), wrote it out using "CHAR(";xa(1);",";xa(2);"," )" and then retrieved it using SQLOP_FETCH_ROW with the FETCHR_FIELDS option directly into the xarray field.

Here's a modified version of the sqltest4 test program that demonstrates it: sqltest4a.bp

One more note about this problem of inserting binary data:

Contrary to the "(not yet implemented)" note in the SQLOP_QUERY documentation, the feature is implemented, and does seem to work, as least in the MySQL connector. (I probably should have said "not yet tested").

Admittedly, it is a bit trickY building an "insert..." string containing components which may involve nulls, but one way to do it is via substring assignments to an unformatted variable, e.g.:

Just to give an update on my findings.

Using the QRYF_REAL mode is the only way I've gotten the results I was looking for; which, was to store an A-Shell binary array (i.e. r(4),b,2) in an SQL data type of Binary(4).

The only trouble I had was with "cmdhdr.cmdarg1 = 48". This was a problem for me because my SQL table contains Varchar() data types, so my SQL string is different lengths depending upon the data. So, I've just made my SQL variable a dynamic "X" variable, and all is well.

In the process of studying this, we've just identified some interesting idiosyncrasies involving concatenation of unformatted variables, which has motivated me to make a subtle change to the way dynamic X variables behave (which will be in 1161.0). As soon as I resolve that, I'll follow up with further explanation.

On a related note, we're giving some thought to coming up with an alternate/simpler mechanism for inserting fields containing binary data that doesn't require so much concatenation and escaping. More on that later too.

See http://www.microsabio.net/ubb2/ultimatebb.cgi?ubb=get_topic&f=5&t=000952 for a discussion of binary concatenation.