Re: ESQL vs. database APIs

Jim Carlisle wrote: > > We are considering whether or not to code our C language database > applications in Embedded SQL instead of using the native API calls > (currently Sybase Open Client). Does anybody have an opinion on which > method is better? > > Our initial testing shows the APIs to have much better performance than > the ESQL programs but we are also concerned about portability between > hardware platforms (RS/6000, AS/400, NT), migrating to different > databases (Oracle, Informix, etc...), and programmer considerations (ease > of use, learning curves, etc...). > > Any help would be much appreciated. > > Thanks, > Jim Carlisle Your observation that the DBMS specific API's are normally faster and more efficient mirrors my own experiences, but when it comes to making an application independant of the RDBMS, well written ESQL can rarely be beaten. 1. Cross platform - Most vendors are sane enough that their API's don't vary based upon the platform. In my experience the cross platform problems are in compilation and linking, not in the programming (with some minor caveats). 2. Migration across databases - As long as you stay out of the realm of dynamic SQL, embedded SQL wins hands down here. The existance of a standard makes this behave a lot better. There are still differences, but they are minor enough that the code commonality save a lot. When you start doing dynamic SQL, the rules change as the SQLDA is not very standard and you have to have code that handles this. 3. Programmer considerations - Most programmers will take to an API a lot faster than ESQL, so if you are starting from scratch I think the APIs win. If you know you have to have more than one version of the code (more than one RDBMS) then using the API doubles the learning and coding, so ESQL is probably a win there. So... If you know you need to be cross RDBMS, consider ESQL as the implementation, but realize that you are going to pay a price with regards to speed. If you think that you might have to be cross RDBMS, consider designing a very thin wrapper layer around your functionality. Designing your wrappers so that they can be either ESQL or API and write them in API using the ESQL as comments in the API code. Then when you find that you have to move to another RDBMS the code itself documents the 'short' migration path (ESQL) while providing enough context to allow you to substitute the other API. Now with all your RDBMS specific code wrapped up, your main body of code does not have to be messed about to migrate and all of the calls reside in one file (hopefully). Shawn Lavin As always, my opinions are my own