SAGE Line 100 ODBC driver (hmmm)

OK I am not a SAGE professional, nor am I a SAGE developer, however, I would like to be able to access correct documentation online and actually speak to others / find out if something is correct…

The requirement

The requirement is simple, connect to a SAGE line 100 system via ODBC (DSN), then run a simple query on the data (stock information – read only of course) and then do something with that information (in my case call a secure web service and update it with the stock information). Simple, half a days task you would think…

It is a tad more complicated than that, as the requirement is for this to run as a windows service, scheduled in to run a number of times during the day. This in itself isn’t a problem, however checking up on error messages can be an issue – as you are reliant on interacting with the Event log (again nothing wrong with this. However you will see why this added to my problems)

The SAGE ODBC problems…

Ahh here we go. Well first off the SAGE ODBC drivers aren’t great (well not for SAGE 100). This caused no end of issues with just trying to connect to the database. There isn’t that much help online and to top it off, many discussion groups etc post questions but no one any real answers. My first problem was that the System DSN that had been set up didn’t appear to work. Using .NET 3.5 to connect to this just wasnt working. My event log was stating a number of issues, which when following the calling stack highlighted an “unsupported driver request”. With this in mind, I then adopted a DSN less connection approach, using a connection string.

My next problem isn’t really to do with SAGE that much, more lack of communications. You see the SAGE line 100 connection string isn’t your typical connection string, rather it needs pointers to particular files. I unfortunately had been given some slightly misleading information on the location of these files (though this may be more of a break down in communication than anything else).

At last I got connected. However, my next problem was that I couldn’t set the SQL command, nor more importantly execute the SQL. Again the SAGE driver was very very particular about its SQL and format, to the point that my SQL went through numerous iterations when to be frank, it was fine in the first place.

The big problem…ODBC read / fill

Everything was now finally moving along or so I thought, but then more problems. This time an unhandled error being raised in my code with the event log only giving me the following message “Event log messages can only be 32766 characters long”. Now thats quite an error message that wasnt getting displayed. I am very particular about my error handling in code so was surprised to see that I had an untrapped error. Anyway, I have spent many hours adding event log debug code after event log debug code and added error messages only to find none of them were getting called…

This meant I would start to post information messages to the event log stating where I was in the code (not great as this service will run forever and a day dealing with 1000s of items a number of times during a day). As I followed my event log I could see that my code was executing and behaving as expected almost all of the way through, then out of the blue an unhandled error. Refering back to my code to the location, it all seemed handled correctly. I have looked at this so hard that I even started to think maybe a timing issue was causing the problem…

However, no…The error is simple. All of my code works fine, but the SAGE line 100 drivers don’t. Why you ask. Well simple, I have read out all of the records placed them in my dataview and returned this to the calling function, all fine, but then there it is my un-handled error….Its great really, basically in my finally statement, all my ODBC objects are disposed and set to nothing. Alas, without any additional error handling around them. It appears that the ODBC reader once disconnected and disposed from SAGE (.dispose)  triggers an error message for every item that it has read, hence my event log unable to display me my message (maybe if it could, I would have had this solved earlier – but that’s not my point). This then caused a further error which meant my lovely windows service stopped working…

Something to remember

If you are using a SAGE Line 100 ODBC driver with .NET basically you need to trap every call and handle everything. This includes simple dispose and setting objects to nothing. The ODBC driver is ODBC 2 compliant so only use ODBC 2 compliant calls (this may mean some of the features you want to use from ODBC within .NET are unavailable). However, sticking to these and things should work fine(ish).  You can use the standard ODBC.open and then ODBCReader.read methods, but remember to trap all your code especially when destroying connections and objects….