Go Back

APIs versus ODBC and linked tables

I have a client who wanted me to provide APIs or SQL replication to allow a web site to access their synergy data.

However they decided to use ODBC access to the data via linked tables in SQL.  So they are exposing the synergy data via SQL, and SQL is accessing the read only data via ODBC.

Its not the most modern or exciting of approaches, but is it a valid alternative for  read only data ?    What are the downsides ?

Apart from speed/performance, is there any advantage in replicating every update to synergy  files in SQL, if you could just query the files directly from SQL via ODBC linked tables ?


3 Answers
0   | Posted by Gordon Ireland to Other on 9/15/2020 8:43 AM
Gary Hoffmann
You could also look at using Harmony Core.  OData is going to be faster than ODBC.

Another "advantage" to replicating the tables would be they could be used for off-loading the reporting as well.  Right now, every hit to the website hits the main server to get the data.  If that data were replicated, this would be completely offloaded from the primary server.  Similarly, you could use BI tools for reporting and not hit the primary server.

9/18/2020 11:28 AM   0  
Gordon Ireland
Thanks Gary

When this was first mentioned they asked about whether I could provide APIs.  that would obviously have required cost and effort.  Harmony Core was certainly a possibility.

But they were able to use the ODBC access they already have at no external cost and minimal effort - around a day

The goal posts have moved several times with this and as far as I can tell the "really urgent web site that has to be live in 2 months" has now been downgraded to a proof of concept.

I suspect they will want a robust API based approach if this ever makes it into production.

The web site is currently passing me a CSV file for each order, rather than getting me to provide an API, and issues around calculation of price, discount, promotions and tax in the web seem undefined.  I am hoping/assuming the live operation will be based on APIs rather than CSV files.

But just because its ugly doesn't mean it doesn't work


9/18/2020 11:54 AM   0  
Steve Ives
Seems like most of the important things have already been hit on in this conversation. ODBC vs. Replication for read-only access, the main benefit is generally off-loading the overhead of reporting off of the application server and onto the database server. And for writeable access, OSBC vs. flat-file transfer vs API's; of course, updating anything via ODBC is fraught with risk, flat files are a simple solution that works, but not real-time, and requires much more work on the receiving end. Also one day they may want to send in orders some other way, from a mobile app for example, and they may not find it quite so easy to FTP CSV files in from an iOS application. Also, as you already identified, API's help you keep the important business logic in one place where it belongs; in the application server codebase. API's are definitely the way to go if you can.

9/24/2020 4:11 PM   0  
Please log in to comment or answer this question.