Infor ERP – Visual Enterprise is something of a niche product. Try searching the internet for it and you’ll see what I mean. There are very few hits and even fewer sites that offer help documentation on how to develop for it.
Well, finally I found a blog written by a fellow that seems to know quite a bit about Visual, check it out.
One of my favourite articles is where he details how to easily decode the BINARY fields in Visual tables. He uses views for his solution so it requires you to write a query that joins the view to the table.
I extended his solution somewhat, though mine is not for the faint of heart. There is a great feature of SQL server called User Defined Fields (UDFs). To use UDFs to get decoded Binary fields in Visual without having to join to another table follow these steps:
1) Open the Visual database and then open the table you want to ‘fix’ in design mode. (that’s right, we’re changing the design of the Visual tables themselves. I told you this wasn’t for the faint of heart.)
2) Add a new column to the END of the list. I called mine ‘BITS_PLAIN’
3) Don’t give it a datatype, instead (using Microsoft SQL Server Management Studio) expand the ‘Computed Column Specification’ in the bottom and enter the following formula:
This will convert the first 5000 bits to 5000 chars for reading. If you are running on a newer version of SQL server you should be able to use this:
Which won’t truncate the data arbitrarily.
4) Save your changes.
The new SQL won’t slow down queries that don’t include that column in the select statement, but it’s great for standardizing where to get the binary data in your applications. Unfortunately this doesn’t solve the issue of writing the data back to the database as you can’t write to the UDF. More on that in a future article.