mx_value dbo.gobject

[kasajian]

Did you figure out what the contents of mx_value column of dbo.gobject is?

[eliotlandrum]

@kasajian I believe the MxValue Conversion.xslm document lays it all out. Let me know if it doesn't make sense. I haven't really poked around it myself yet.

[kasajian]

I believe the value mx_value column is or refers to a blob of some sort. The content is directly stremable by the MxValue COM object. Do QueryInterface on it. You should be able to obtain a IPersistStream or something similar then do a load to construct a new MxValue from the blob.

[arobinsongit]

Wow, that's some awesome insight. Can you post an example showing this?

[kasajian]

I don't have access to System Platform installation so I can't try it out myself.

When you say code, are you looking for C++ code ? C#? VBA?

It's easiest to do this in C++, and increasingly more complicated in the other languages.

On Fri, Jan 23, 2015 at 9:49 PM, Andrew Robinson notifications@github.com wrote:

Wow, that's some awesome insight. Can you post an example showing this?

— Reply to this email directly or view it on GitHub https://github.com/aaOpenSource/galaxy-db-tools/issues/1#issuecomment-71301760 .

[kasajian]

If you or anyone wants to try this, this is what I recommend that you do to. I'm writing the instructions the way I would go about it, so I admit some of this has to do with style and is quite subjective as to how to approach in solving this type of problem.

First, set up a system where you can effectively use GRAccess to access attribute values. I suspect that the attribute value is of type IMxValue. As you know, once you have an interface to an object, you can QI (QueryInterface) the object for other types. In higher level languages, QI is just another word for cast. So if you're using C#, try casting the attribute value to a variable of type IPersistStream. If that works, then the result will be non-null. Example:

 IPersistStream p = myValue As IPersistStream

IPersistStream is built-in to Windows, but you may have to add a reference like stdole.tlb or something like that. I'm sure you can Google to find out how.

Anyway, if 'p' is not null, then you have a persistStream access to the attribute. At that point, try to call the Save method, passing in a memory stream. Then, take a look at the content of the memory stream. I personally would go one step further at this point and export that value as hex to a data file

Then I would create a separate program which creates a new MxValue from scratch, gets the IPersistStream for it, then call Load, passing binary data that was loaded perviously. I would load the hex data from disk, convert it to binary, then load it that way. If all that worked, I should be able to get to the original value I originally read from GRAccess.

I would make sure all of the above works first. If I have any problems, then I've minimized the possible places where I probably did something wrong. Once all that works, now I know how to load/save MxValues binary data.

The next step, is to pull the binary data out of the database mx_value column of the dbo.gobject. Output that to a file as hex, since you now have code to do that.

Then use the program that loads hex data into MxValue and see if that still works. If it does, then you've now found a way to load attribute data from the database directly, bypassing GRAccess. This should let you create a set of tools that can be much faster than you could otherwise.

As I said, I don't have access to System Platform, or else I would try it. I believe the above should work if you take it one step at a time, and introduce something new incrementally. That's better than writing some code trying 27 unknown things and then trying to figure out what part is not working.

Good luck.

[arobinsongit]

Thanks. Will try to hack around with that.

[like-magic]

reviving the dead, is the soft of thing you were think of using it for(runs on galaxy sql server):

/* ============================================= Author : Maurice Butler when : 3 Oct 2018 What : PLC Tag to HMI tag dump Why : allow to find where tag used

Changes Who : Maurice Butler When : 20 Jun 2019 Why : added Shortdesc & Description & fixed missed stuff with auto reference Where : Extensive, require restructure of code Notes : based on supplied view 'internal_reference_primitive_attribute' and vDeviceTagDump

============================================= */ CREATE VIEW [dbo].[vTagDumpPLC] AS With Devices as (SELECT greferring3.hierarchical_name AS HierarchicalName , greferring3.tag_name AS Area , greferring2.tag_name AS BaseTag , greferring1.tag_name + '.' + pinst.primitive_name AS Object_Tag , CASE WHEN reference_string = N'---Auto---' THEN -- unravel auto reference to DI object (SELECT dio.tag_name FROM [dbo].[object_device_linkage] odl INNER JOIN gobject dio ON dio.gobject_id = odl.dio_id WHERE odl.gobject_id = ar.gobject_id )
ELSE -- Direct reference LEFT(ar.reference_string, CHARINDEX('.', ar.reference_string) - 1) END ConnectionObject , CASE WHEN reference_string = N'---Auto---' THEN -- unravel auto reference to PLC (SELECT sg_pi.primitive_name + '.---Auto---' FROM [dbo].[object_device_linkage] odl INNER JOIN gobject dio ON dio.gobject_id = odl.dio_id INNER JOIN primitive_instance sg_pi ON sg_pi.gobject_id = dio.gobject_id AND sg_pi.package_id = dio.checked_in_package_id AND sg_pi.mx_primitive_id = sg_mx_primitive_id WHERE odl.gobject_id = ar.gobject_id )

            ELSE -- Direct reference
                RIGHT(ar.reference_string, LEN(ar.reference_string) - CHARINDEX('.', ar.reference_string)) 
          END AccessString
        , dbo.udf_extract_hex_string(convert(nvarchar(500), dyn.mx_value)) as Object_Description
        , ar.gobject_id -- needed to find ShortDesc

FROM dbo.attribute_reference AS ar -- find tag to go with reference INNER JOIN dbo.gobject AS greferring1 ON ar.package_id = greferring1.checked_in_package_id -- walk back up hierachy to get base tag
INNER JOIN dbo.gobject AS greferring2 ON ar.gobject_id = greferring2.gobject_id -- walk back up next step to get area INNER JOIN dbo.gobject AS greferring3 ON greferring2.area_gobject_id = greferring3.gobject_id -- start looking for attribute data by finding attribute INNER JOIN dbo.primitive_instance AS pinst ON ar.package_id = pinst.package_id AND pinst.mx_primitive_id = ar.referring_mx_primitive_id -- get data associated with attribute INNER JOIN dbo.dynamic_attribute AS dyn ON dyn.gobject_id = pinst.gobject_id and dyn.package_id = pinst.package_id and dyn.attribute_name = pinst.primitive_name+'.Description' WHERE ((ar.resolved_gobject_id > 0) AND (ar.is_valid = 0) AND (ar.resolved_mx_primitive_id = 0) --filter out view tags AND ar.reference_string NOT LIKE '%.Tagname%') OR ar.reference_string = N'---AUTO---' ) -- uses the global_id from above to find the short description, then displays the data from above with short description select Devices.BaseTag , case when ad.mx_data_type = 4 then convert(nvarchar, dbo.udf_extract_hex_double(dbo.udf_extract_hex_attr_value(pinst.primitive_attributes, ad.mx_attribute_id, 0))) when ad.mx_data_type = 5 then dbo.udf_extract_hex_string(dbo.udf_extract_hex_attr_value(pinst.primitive_attributes, ad.mx_attribute_id, 0)) when ad.mx_data_type = 15 then dbo.udf_extract_hex_intl_string(dbo.udf_extract_hex_attr_value(pinst.primitive_attributes, ad.mx_attribute_id, 0)) when ad.mx_data_type = 69 then dbo.udf_extract_hex_custom_enum_def(dbo.udf_extract_hex_attr_value(pinst.primitive_attributes, ad.mx_attribute_id, 0)) else dbo.udf_extract_hex_attr_value(pinst.primitive_attributes, ad.mx_attribute_id, 0) end BaseTag_ShortDesc , Devices.Object_Tag , Devices.Object_Description , Devices.Area , Devices.HierarchicalName , Devices.ConnectionObject , Devices.AccessString FROM primitive_instance pinst inner join devices on Devices.gobject_id = pinst.gobject_id inner join attribute_definition ad on ad.primitive_definition_id = pinst.primitive_definition_id WHERE (ad.attribute_name ='ShortDesc' OR ad.attribute_name IS Null)

[carl-johanliedberg]

@like-magic

Great example on how to get actual content out of the mx_value columns. Been struggling with finding this content for some time.

In your example you are referring to some udfs, e.g. dbo.udf_extract_hex_string etc. Are these your own udfs or where did you find these? Is it possible for you to share these?

Thanks in advance

[like-magic]

custom developed udf_extracthex.zip

[carl-johanliedberg]

custom developed udf_extracthex.zip

Greatly Appreciated!

thank you