3

I've recently been tasked with writing a Sage import tool that imports Quantity Price Break Discounts.

The Sage 200 tables in question are:

  • StockItem - Main Product Table
  • StockItemDiscount - Main Discount Table
  • StockItemQtyDiscBreak - Discount Qty Price Breaks

I wont bore you with schema information as it's not relevant to my question, suffice to say - the primary key in all 3 tables is a BigInt without identity set (sigh), 1 StockItem can have many Discounts and 1 Discount can have many Qty Discount Breaks.

Now then, to create an import routine i first had to analyse what Sage 200 did on SQL if you created Discount and Breaks manually in sage (using SQL Profiler). As i say, Sage 200 does not make use of Identity columns, instead it uses a counter table.

Inserting a new row into StockItemDiscount did the following:

UPDATE [Counter] SET [NextValue] = [NextValue] + 10 WHERE [CounterID] = 1

It then selects the new ID:

SELECT NextValue FROM Counter WHERE CounterID = 1

It then inserts the new row using the new value it just selected from the counter:

INSERT INTO StockItemDiscount (StockItemDiscountID, /.../) VALUES (@NewID, /.../)

My question is this: Why on earth is Sage doing it this way? what could possibly be the reasoning behind it? (Specifically the +10 THEN reading the value)

All the tables share the same counter too, so 5 rows in 1 table would results in a gap in the id's of another table - i'm just really at a loss as to why they do it like this?

The reason i ask: After inserting a row into StockItemDiscount i then need to delete any related rows in StockItemQtyDiscBreak & insert replacements - however, using SQL profiler i cant see incrementing of the counter table unless i insert 5 or more discounts (the 6th causes it to hit the counter table again, it's almost as if the Sage UI is reserving those 10 ID's using them for a variety of inserts then reserving an additional 10 as it needs them - this just seems very very odd to me?

Samuel Lelièvre
  • 3,212
  • 1
  • 14
  • 27
HeavenCore
  • 7,533
  • 6
  • 47
  • 62
  • Maybe they did this for portability reasons. – PepperBob May 01 '12 at 08:12
  • Oh, and allocating 10 ids at once will decrease the database load when doing batch inserts. – PepperBob May 01 '12 at 08:20
  • I suppose that could be the case (although Sage blatantly sell 200 as a sql server product only) - I'll just wait and see if someone who knows sage 200 inside and out can give me a better answer / suggest a better way to insert these new rows. – HeavenCore May 01 '12 at 08:47
  • Are you asking "Why" or are you asking "How can you do your piece better"? – Tom Halladay May 07 '12 at 21:27

4 Answers4

2

Sage have not designed the database to be written directly to (and they don't support development where this is the case).

Sage have an SDK that enables you to write to the database via objects and this simplifies the process of writing routines to import Exchange Rates or discounts etc.

To get the SDK requires being an accredited Sage developer, which in turn involves money.

Atuitive
  • 273
  • 3
  • 7
0

Theory #1:

They are inserting on N0, and reserving N1-N9 for future edits?

Theory #2:

They are using N0 for a parent record, and using N1-N9 as child record ID's? Although you said all 5 tables use the same counter and method, so this doesn't seem likely.

Are there any rows where N1-N9 is being used?

Theory #1a & #2a:

They intended to implement one of these, and changed their mind, or they previously implemented this, and went away from this model, but never cleaned up their code / methods.

Tom Halladay
  • 5,651
  • 6
  • 46
  • 65
0

Theory #4 The developers were deliberately trying to obfuscate the logic of inserting records in an effort to keep inexperienced "developers" from writing data to their database, and to make it more difficult to interact with the database to protect their income stream.. I think they knew EXACTLY what they were doing

Mike Dickerson
  • 21
  • 3
0

The Sage API manages the ID by pulling an incremental value from dbo.SYSCounter in blocks of 10. Apparently they decided pulling in increments of 1 would be too slow. Any counters that are unused are dumped upon exit. You can safely push the SYSCounter forward xx million then use those IDs for your task.

Direct SQL edits are not supported. If you manipulate IDs it's obvious to anyone looking at the audit trail. Using the SDK documented API is recommended. Speak with your BP or a 3rd party dev for more info.

Adam Byrne
  • 71
  • 2