Read optionstring directly from SQL Server table

awinav

Hi,

I'm working on an external .NET application to show some data from Navision tables by accessing the SQL Server. If the table has an OptionString field, I would get an integer instead of the string representation.

Does anyone know where this string information is stored in SQL Server?

Currently I'm using enum to map the optionstring in my .NET application, but everytime I change the OptionString in Navision, I have to update the application also.

I know I could have used Code field combined with FlowField for this purpose, but wasting a table just for storing 3 options is too expensive for me.

Any thoughts and help will be very appreciated.

ClausHamann

Hi,

To my knowledge the optionstring values isn't stored in any SQL Server table. You will have to hardcode it in you application or store the values in a setup table.

Regards

Claus

awinav

But if I did an SQL Server backup and restored it in another server, Navision and all it's OptionStrings were all there. So it must be stored somewhere in the tables.

ClausHamann

That's true. The option strings are stored in the Navision objects definition which is stored in the object table as a blob field and I don't think it will be easy to extract the information from there.

Regards

Claus

kriki

ClausHamann wrote:

That's true. The option strings are stored in the Navision objects definition which is stored in the object table as a blob field and I don't think it will be easy to extract the information from there.

Regards

Claus

I think it is impossible...

SteveO

If you analyse how the objects are stored in the database you will see that some of the contents are readable.

Eg. I created a field called MyField with the options: Option,second,third

this is how it is represented (in hex):

4D 79 46 69 65 6C 64 00 64 28 28 00 01 00 09 00 4F 70 74 69 6F 6E 00 00 02 00 09 00 73 65 63 6F 6E 64 00 00 03 00 0A 00 74 68 69 72 64 00 00 00 00

This is: MyField 00 d ( ( 00 01 00 09 00 Option 00 00 02 00 09 00 second 00 00 03 00 0a 00 third 00 00 00 00 00

So you can see that it is: FieldName null d ( something null 01 something something something Value1 null null 02 something something something Value2 null null 03 Value3 a whole bunch nulls

So you can see that there is a definite pattern and thus the details can be extracted.

So it's not impossible, it's just not easy. 8)

kriki

SteveO wrote:

So it's not impossible, it's just not easy. 8)

Nice hack! =D>

kriki

[Topic moved from Navision Attain forum to Navision Tips & Tricks forum]

kriki

Moved a post to another topic : (http://www.mibuso.com/forum/viewtopic.php?t=27846)