locked
Databases that require API connection and provide no ODBC driver RRS feed

  • Question

  • I am working with a software vendor that is implementing an application built on the LMDB database platform for one of my clients. LMDB is a non-relational, key-value database platform. We need to do our own reporting from outside their application, that is, from within Access. However, I cannot find an ODBC driver for LMDB and  am wondering if anyone here can help interpret what we are up against.

    I infer that LMDB corresponds, not to the suite of SQL server services that access data on our behalf and therefore offer a transparent ODBC driver that we can use to create and run queries directly without having to understand the actual method by which SQL server services interact with the actual data, but to the .mdb SQL server database file format; that is, the software vendor's application talks directly to the database, leaving us to do the same.

    When I asked them if they have an ODBC driver, they responded with this:

    "We haven't done much with ODBC, and instead have gone the way of GraphQL https://graphql.org/  It's a very powerful and modern query language for APIs"

    But my attempts to get any information about GraphQL reveal nothing regarding an ODBC driver. Unless I miss my guess, this is similar to interacting with QuickBooks in VBA, in which I must use an Intuit-provided API to interact with the data structures instead of having an ODBC driver for which I can create a DSN and access data transparently by creating Access queries or opening record sources within VBA. But I also assume that, unlike QuickBooks, which provides a relatively complete set of API documentation, we may be stuck with a vendor that does not wish to release any details so that we have to pay them for any reports and are effectively hamstrung if we want to query their data from our Access apps alongside data from other sources.

    Or is it likely that there is an LMDB API that will allow us to determine the data structure ourselves?

    I know that the questions here are specific to LMDB and GraphQL, so I am not looking for detailed information specific to those; I am just wondering if anyone else has dealt with this and if my inferences may be close. I just suspect that nothing in my 20+ years experience in Access, in which have always had either an ODBC driver or a well-documented API, has prepared me for this.

    Tuesday, October 27, 2020 10:53 PM

Answers

  • >1. By "web API", you mean standard APIs like Javascript, JSON, XML, REST, or SOAP, right?

    yes, that does. Since there is a server on site - you MIGHT have some other means here.

    But all in all, a lot of venders (say some of the big printing press makers) do allow direct hitting of the database. For example, Kormoi printers actually run SQL server on their systems that control the printing press.  So out of the box, you can actually connect to their printing press - and it running of all things sql server on that printing press.

    But, most of these "things" or "systems" limit hitting and getting of the data by a web service call. Some of the calls are not all that too hard in VBA/Access.

    However, if the web call is complex, or there are quite a few of them? Then I write such code in .net, and then consume that "COM" class from Access. Because .net has really amazing tools for web services, then it less work and effort to do it this way then using VBA. 

    However, for some simple web calls - the MSXML library and VBA is often used and is a good solution. So which road really depends on how many web calls and their complexity.

    So a lot of vendors are "less" then happy to allow some kind of direct use/connection to the database, but they are in general quite usually quite happy to provide information as to pulling some data via web service calls. I suspect this is the case here.

    Regards,

    Albert D. Kallal (Access MVP 2003-2017)
    Edmonton, Alberta Canada
     
    • Marked as answer by Brian D. Hart Thursday, October 29, 2020 9:05 PM
    Thursday, October 29, 2020 8:37 PM

All replies

  • Well, first the term LMDB is "general".

    So, say I use the term:

    My vendor has a relational database. Well, then that could mean anything from Oracle, to MySQL or even Access.

    And then there are say multi-value database vendors.

    IBM (U2/Universe). Pick/d3 (now all owned by Rocket software).

    And there was even a MV version for the PC (Advanced Revelation - still in existence??).

    And then there is cache and a few more. 15+ years ago, the Universe, and U2 offerings were well known as mv-databases. And of course Prime computer - again a MV database vendor that was popular at one time. 

    My point is that there are "many" relational databases.

    And there are "many" LMDB databases. That term (say like "relational") does not describe the particular vendor.

    So LMDB = Lightning Memory-Mapped Database).

    They "tend" to be a file based, and lightweight (much like say sqlLite which is VERY small, and thus is so popular on Android it quite much the standard database on Android phones). And like SQLite, such system are not only small, but often don't even require a installer. And being "small" they are often used on imbedded devices, or even the Raspberry PI type of computer. Much the same seems to be the case for LMDB type of databases.

    So, we will need more information about this database. That graph link is simply a set of tools that let you work with a given API and translate that API into some form of consumable datasource. But that product is of little use unless you have some API documentation as to how one can interact and pull data from that custom database system.

    It not clear what kind of system or even device you working with and wanting to pull data from (my bets are some kind of machine controller system).

    However, I would be VERY surprised that there is not some means and documentation as to how to get or pull data. I mean even "large" commercial printers now have API's that you can talk to and pull information about say the print job being run, or even things like time taken to change printing plates. and these days, that printer (or even photocopier) not being web based, they MOST often offer some kind of web API.

    I mean, even a super cheap home router often provides a web interface, and you use your web browser to configure and setup that little home router. Much the same exists in regards to say that large commercial big printing press, or even these days even setup of a nice "work group" photo copier. So the "thing" in question often has a mini "built in" web site just like your home router that you can talk to.

    So, I have to think that whatever you are using MUST have some means to interface and talk to that "thing" but without additional information beyond "relational database" or "LMDB database", we have next to nothing here to go on.

    You not even mentioned what kind of device or system this is we are dealing with. 

    My best bests is that some kind of web api is available.  

    I mean, how does one now even talk to or use this "thing"? is this a dedicated computer, or some single stand alone software package that runs on your desktop? 

    So, not a lot to go on here at all. Surely there must be some means to use this "thing" you dealing with - it just a question of what the software is you are using and how you run/use/deal with/interface/use that software system.

    Obviously you using that "thing" now - but one will need a whole lot more details as to what this product is all about and how you now even using and interfacing with that software is required. The term LMDB does not limit this narrative anymore then saying I have a relational database, or a Multi-value one.

    Regards,

    Albert D. Kallal (Access MVP 2003-2017)
    Edmonton, Alberta Canada

    Wednesday, October 28, 2020 12:43 AM
  • Thank you, Albert. To be specific, the vendor is ProShop, a manufacturing ERP software provider that indicates only that their data platform is LMDB and that they provide no ODBC driver but use GraphQL instead. You are correct about us not having much to go on; that is everything they have told me. I do not know if it is because the sales staff are simply not technical  enough to answer the question or if they are being intentionally shrouding the product behind generalities; I have insufficient information to determine that.

    My client is considering purchasing their product to replace the manufacturing components of Sage 100, while retaining Sage100 for the accounting functionality. But we have been heavily involved in reporting from Sage and storing some non-Sage data in Access middleware applications. They have a full-time developer working in Access to do this.

     As it stands now, they have Sage 100 Advanced (with its latency-ridden ProvideX ODBC driver--in the process of migrating to the at least partially SQL-based Sage100 Premium), some Access middleware databases in the process of being migrated to SQL server middleware databases to match the new Sage SQL server platform, and--aside from the Sage applciations themselves, 100% Access middleware reporting & analysis tools. Sage, even with Crystal Reports, just cannot provide the level of detail they want.

    All of that involves predefined ODBC drivers: Sage, Jet, and SQL, so we have always started with a clear view of the table structures simply by creating the DSN, then linking tables.

    My chief concern, given the dearth of information from the vendor regarding relatively transparent data access methods (i.e. the starting point being those ODBC driver & DSNs), is that this change may put us into a situation where we cannot assume that we will have access to a driver for which we can simply create a DSN and begin linking tables. If we instead have to use an API to access the data, the way we do with QuickBooks, we can no longer assume that the starting point of all reporting development is with a clear view of the data structure, and we may find that everything the vendor has done to date has been internal to their application, and they may be highly unmotivated to provide any assistance in helping it be more transparent to us.

    Caveat, of course:  I may be somewhat spoiled by always having dealt with systems that had predefined ODBC drivers. I have written VBA code for various APIs, but those were purely to access functionality, not (except QuickBooks) to access data. Even my work some years back with MapPoint was focused primarily on using the API to allow the application to facilitate routing; it was never to interact with MapPoint data directly. So my ignorance is likely showing here.

    At this point, I am just trying to determine if this will be a sea change for the developer, who I trained and whose experience largely mirrors mine. If investing in this product means all development starts with having to dig deeply just to write the code to access the data, i fear it may mean that any drag-and-drop query-building, or even direct record source interaction within VBA, may be off the table. And that is a factor my client is weighing in the decision whether to purchase ornot.

    Wednesday, October 28, 2020 2:01 AM
  • Ok, that’s about as good as we can expect.

    I written interfaces from Access to Sage 50, Sage 300, and also for QuickBooks. In all 3 cases, I used their API kits, and wrote the interface in .net for MS-access.

    So, now from Access we can say push invoices from Access to any of the above 3 versions of accounting software.

    I used the given vendors SDK’s they offered, and we were not reporting, but in fact pushing invoices directly into the accounting packages from Access (so a different use case then what you are looking for).

    So the base starting point:

    The system is web based. So the workstations run a browser. What this means then just like the “on-line” versions of Sage or QuickBooks? Well once again, no ODBC type of connection is available (for the web based accounting systems, or in this case this ERP system).

    So just like you not get or have ODBC type connection for the newer Sage or QuickBooks products (they are browser based), the same applies here.

    So, this means that almost for sure that a web API is available.

    It is possible that an on-site server is provide for this system, but the client (workstations) are browser based. So, this does not follow an “old style” setup in which say you have SQL server, and then each station requires/has a thick client based software install. From what I can see, this system is browser based.

    So, this quite much suggests say how information is pulled from say most things these days (from a web based API).

    If for each customer site that some pre-built “server” is provided, then the ability to hit or read the database might still be possible.

    If the setup requires no server, and this system is 100% consumed like say the newer versions of QuickBooks/Sage (web based), then the ability to use an ODBC like connection is un-likely (just like this direct connect option is NOT available for web based Sage/QuickBooks).

    So, best guess so far?

    Well, if no on-site server is required, then this for sure is web based.

    If an on-site server is supplied, while the client (workstations) are 100% web based, you might have some better possibilities. (Some possible connection ability to the database – but more than likely, you uses a web API here).

    This suggests that a web API of some type is likely available. You have to dig further. But as noted, this is a browser based system, so a direct database connection is un-likely – just like you can’t direct connect to the database directly that drives the web based versions of accounting packages.

    So, not likely to find many users in say this group for Access users.

    However, some digging on your part should be able to point you in the direction of what web API’s they have and allow. That system looks to be rather extensive, and thus I would expect there are options to pull data – it just a question of what API’s they offer. But such options are certainly are most likely to be web based API calls.

    Regards,

    Albert D. Kallal (Access MVP 2003-2017)

    Edmonton, Alberta Canada

    Wednesday, October 28, 2020 3:42 AM

  • Thank you again, Albert.

    Just to clarify two (hopefully) final points to see if I am on the right track:

    1. By "web API", you mean standard APIs like Javascript, JSON, XML, REST, or SOAP, right?
    2. The web API details would have to be provided to us by the specific software vendor--assuming that they are willing to share it beyond their own developers--not something that provided generically as part of the LMDB platform, right? Or do you suppose that there are generic B+ tree database access methods that would allow us to query our way into inferring their data structures within their LMDB database?

    I am quite certain there is an onsite database, since they have a background (server-side) API that the indicate will interact with our (also onsite) Sage database (we are onsite, not cloud-based, Sage 100, soon moving from existing proprietary/ProvideX to SQL server).

    At a minimum, it seems clear that this will be a lot more complex than simply linking tables from an ODBC DSN. Thankfully, I get to pass all the actual development on to the developer. It is just my job to help determine 1) whether the software provider is willing to expose the API to us or we can figure it out on our own and 2) the degree to which our ability to create our own reports in a hybrid environment (ProShop, Sage, and other external data), including the learning curve to get there, affects our decision to purchase the product.


    Wednesday, October 28, 2020 5:20 AM
  • >1. By "web API", you mean standard APIs like Javascript, JSON, XML, REST, or SOAP, right?

    yes, that does. Since there is a server on site - you MIGHT have some other means here.

    But all in all, a lot of venders (say some of the big printing press makers) do allow direct hitting of the database. For example, Kormoi printers actually run SQL server on their systems that control the printing press.  So out of the box, you can actually connect to their printing press - and it running of all things sql server on that printing press.

    But, most of these "things" or "systems" limit hitting and getting of the data by a web service call. Some of the calls are not all that too hard in VBA/Access.

    However, if the web call is complex, or there are quite a few of them? Then I write such code in .net, and then consume that "COM" class from Access. Because .net has really amazing tools for web services, then it less work and effort to do it this way then using VBA. 

    However, for some simple web calls - the MSXML library and VBA is often used and is a good solution. So which road really depends on how many web calls and their complexity.

    So a lot of vendors are "less" then happy to allow some kind of direct use/connection to the database, but they are in general quite usually quite happy to provide information as to pulling some data via web service calls. I suspect this is the case here.

    Regards,

    Albert D. Kallal (Access MVP 2003-2017)
    Edmonton, Alberta Canada
     
    • Marked as answer by Brian D. Hart Thursday, October 29, 2020 9:05 PM
    Thursday, October 29, 2020 8:37 PM
  • Thank you again, Albert.

    As it turns out, my client is now turning toward a different provider whose product PostgreSQL-based. I am sure that at least part of that decision was based on the fact that it has been less than transparent attempting to get useful information from ProShop regarding data access methods (as evidenced by the discussion here, which relied heavily on inference) compared to the relative transparency implied simply by the fact that PostgreSQL has a long-standing and fully functional ODBC driver.

    Thursday, October 29, 2020 9:11 PM