Author Topic: MASShadow Update Manager  (Read 1859 times)

0 Members and 1 Guest are viewing this topic.


  • Posts: 6
MASShadow Update Manager
« on: June 01, 2018, 05:09:32 pm »
Next up is the MASShadow Update manager.  I wrote this in VB.NET using SharpDevelop, an open source alternative to Visual Studio.  It maintains the shadow copy of the Sage 100 data in PostgreSQL (also open source), and uses a 3rd party opensource utility called ODBCView to dump MAS tables to text files which are then imported into PostgreSQL.   To set up, the user clicks the Setup button, which brings up a dialogue listing all of the tables currently available through the ODBC driver.  Multiple tables can be selected and then when the Setup button is clicked, the table is replicated in PostgreSQL including the indexes.  It also creates a temporary table and a function to copy and transform data from the temporary table to the main table.

Once a table is set up, it must be assigned an update frequency.  When in Auto mode, the program steps through all that shadowed tables and checks the Last Update timestamp.   If it finds that the time between the last update and now exceeds the update frequency, it updates the table.  Update frequency is shown in minutes, Duration (time it took to complete last update) is show in seconds.

The use of the temp table allows the the table data to be continuously available even during the sometimes long imports.  All data is imported into the temporary tables.  When the import is complete, the main table is cleared out and the data copied over from the temp table.  Since both tables are in the same Postgres database, this usually finishes in less than a second, so data is continuously available.  In 9 years I have never tried to get data from a table and found that it was in the brief second that it was being updated from the temporary table.  This also allows troublesome data type fields to be imported as character varying type fields, and then transformed to the appropriate type when data is moved to the main table.

John Spikowski

  • Posts: 52
Re: MASShadow Update Manager
« Reply #1 on: June 01, 2018, 07:26:17 pm »
Thanks for the cool example!

Where have you been all my BOI life?  :o

I need to hook you up with Script BASIC and its CallByName COM/OLE automation extension module. Using IUP for your UI will be like heaven for you. NOMADS will make you laugh after using it. (Emulation BASIC Toy)
« Last Edit: June 01, 2018, 07:30:47 pm by John Spikowski »


  • Posts: 6
Re: MASShadow Update Manager
« Reply #2 on: June 01, 2018, 10:30:34 pm »
LoL same as you, hiding from the know-it-all resellers and consultants, just plugging away and trying to make this ERP work for me by hook or by crook.  I'll be honest, if I wasn't stuck on ProvideX, I would be circumventing BOI and it's "business logic" altogether.  I am pretty well versed on every single table and update process in Sage 100/200 and I find most of their API annoying.  It's slow, there are too many layers, and there are too many limitations.  Heck, even VI has had limitations added on top of it rather than having its capabilities extended (like no more writing to gl_detailposting). 

Here's a dirty little secret.  The old 4.0 ODBC read/write driver actually works with the new framework tables, whereas in the old database structure, it was guaranteed to chew up your data like a garbage disposal.   Every once in a while, I dust it offand use it to handle some otherwise messy VI tasks, like importing all of our customer AP contact emails and salesperson emails into their respective customer paperless office set ups for invoice delivery.  But if it got out that I was doing that, I'd likely be exiled to a small island somewhere by a gang of Sage consultants.

I'm actually planning on taking a look at your Script BASIC this weekend, at least to read some docs and maybe kick the tires a little, ask some questions.  But last night I finally figured out how to get data from my 32-bit Sage 100 4.5 into a 64-bit SQL Server 2008 (no easy task), where my Infor CRM (aka Saleslogix) data lives.  I'm excited about being able to throw my Dynalink server into the nearest lake of fire.  I really though Bitelligent was going to do good things with that product but it hasn't gone anywhere past his first rewrite and it's junk.  Who writes a program that hard crashes when its own Windows Event Log fills up with transient errors?  It's bad enough that it takes a couple HOURS to process order details.


  • Posts: 6
Re: MASShadow Update Manager
« Reply #3 on: June 12, 2018, 05:41:03 pm »
Just a nudge to PM me with that link.  I've got a couple little projects I'd like to throw at it and see if its applicable