Would it be possible to pass a DYNSTRUCT to SQLOP_FETCH_ROW and have all of the fields populated?

That would be much more flexible than having to pass all of the separate fields, and easier/performant than translating an ordamp to the dynstruct.

Seems like a possibility. Do you expect the operation to match up the result set to the DYNSTRUCT fields by name? Or do you expect to build the DYNSTRUCT to match the result set (in which case they could be matched up by number)?

I think the second option by number would be the best. If you need by name we can write a function to retrieve an ordmap and match the columns there

By number would definitely be the easiest, since that's how the result set is organized. Doing it by name would require separately fetching the column names and cross-referencing them.
I'll dig into it on Monday and see if there are any other hurdles to overcome.

Sounds great.

The only issue I can think of is how to handle when the resultset size doesn't match the DYNSTRUCT number of members. This could be valid with a log warning, or invalid and just an error, I'm not sure which would be the correct approach.

Not sure either. I'm also thinking it might be just as easy to match up the query results to the DYNSTRUCT field by name as it is by number. We already have that logic baked in to the ds.@fldnam runtime support, and it could just piggy back on the first part of the FETCHR_ROWMAP operation (in which the connector library function builds a list of fldname:value pairs that the XCALL wrapper in the main A-Shell module converts to the ordmap). Splitting the operation into two parts that way does involve a few more CPU cycles, but it allows the logic to be shared between the different connectors, and in any case, it was unavoidable because STL collections (like our ordmaps) are not shareable across dynamic library links.

We'd still have to deal with the possibility that a field in the result set does not exist in the DYNSTRUCT. But the reverse case, of fields in the DYNSTRUCT that are not in the result set could simply be ignored. The biggest upside would be that you could use a single general-purpose DYNSTRUCT for non-identical queries. But it would complicate the handling of result sets containing expressions that are not given explicit names.

Perhaps like with so many other things, indecision means we just have to support both methods?

I have a concern with unforeseen column name mismatches issues. The

All of our code uses SQLOP_FETCH_ROW with variables.


When I do this query in MySQL Workbench the IFNULL() is a part of the column, so unless add column alias manually the name is not going to match aryrec.order'rec.ship'via$.

Hence, I think using the option by number would avoid that issue.

If I'm not correct about that, then whichever way you think is best is good with me.

Right - w/o supplying a column name (i.e. , IFNULL(oeh.`ship_via`, '') as SHIPVIA, it would be difficult to deal with results like this.

I'm proceeding on the idea that the offering the choice of field number of name will be simple enough that there's little reason to stress over which is better.

I think I have this more or less working, except I'm not sure how to handle array members. They don't fit neatly into either the by-name or by-number paradigm. I guess I could imagine a scenario where the entire result set was logically an array and you wanted to retrieve it into an array, but in that case it seems that you would just use the FETCHR_ARRAY mode rather than the DYNSTRUCT mode. Combining result sets involving arrays into the DYNSTRUCT model seems like a stretch, if not an contortion. Do you agree?

As for how to handle errors, I think it's best to just set the cmdhdr.rc value to a newly defined error code indicating invalid field match (whether by name or number). In the by-number case, that would presumably only occur when the result set had more fields than the DYNSTRUCT, in which case it would fill up the structure with all the fields that fit, and then return the error code (effectively truncating the results). In the by-name case, it might as well keep processing the rest of the result set. The error code would warn you that at least one field couldn't be retrieved, but you could choose to use the ones that did get retrieved. In all cases, I think it makes the most sense to pre-clear the dynstruct on each SQLOP_FETCH_ROW call so that there's no confusion whether values returned in the structure were left over from before due to a failure to get updated.

We have a bunch old structures with arrays. These are stored in the db with each element having a column with a _subscript as the name. Then we use the array with subscript on retrieval. Those should be in the same order as all the other members in the dynstruct.

Ok, I guess there's no reason not to support this in the by-number version. Doing it by-name would require standardizing the naming convention, which I'm not sure we have the power to do.

I agree with that, and we can already use an ordmap on our end if we want to.

I think this is ready for beta testing...

ash70notes.txt
ash-7.0.1771.0-el7-upd.tz
ash-7.0.1771.0-d12-x86_64-upd.tz
ash-7.0.1771.0-w32-upd.zip
ash-7.0.1771.0-w32c-upd.zip
compil-7.0.1068-w32.zip

Hi Jack, when possible can you publish the "c" version?
No hurry, thanks

Hi Jorge - I added links for the 'c' version and standalone compiler to the post above. Just be aware that these should be considered 'beta' until there is further field testing!
-Jack

That's why I want it, to install in my development area and use it for a safe period.
Thank you

Perfect - that's exactly what we are hoping developers will do! Thank you!

I will work on testing this early next week.

Thanks,

I just finished testing this, and it works as expected.

Unfortunately, we have a ton of multidimensional arrays nested in DEFSTRUCTS. I think (x, y, z) is the largest dimension. I just looked at the documentation specific to FETCHR_DYNFLDNUM

How do you convert ary(x,y,z) into a single #?

((x-1) * .extent(ary(1))) + ((y-1) * .extent(ary(2))) + z ?

We're storing each array element as a separate column in the database.

So, inventory.price(2,2) will be price_1_1, price_1_2, price_2_1, price_2_2

The same goes for 3 dimensions.

Ok, I think it will support up to 4 dimensions now...

ash70notes.txt
ash-7.0.1771.1-el7-upd.tz
ash-7.0.1771.1-d12-x86_64-upd.tz
ash-7.0.1771.1-w32-upd.zip
ash-7.0.1771.1-w32c-upd.zip

That works. Thanks