Sriram

That's a fair assessment. You'll need to take any and all precautions before making opening your database for global access. In the original question from Bill, I did not see "public" internet apps usage mentioned. There are also many use cases for Enterprise or within the firewall (VPN) use of mobile applications (running on phone/tablets etc.) accessing an internal server database. In addition to the aforementioned RAD Server and other technologies for the middle-tier, which InterBase nicely fits in the backend as well, here is another InterBase specific solution. Use IBToGo (Embedded) library, same as IBLite but with license requirement for deployment for RAD Studio Professional customers. https://www.embarcadero.com/products/rad-studio/product-editions With IBToGo, you get a few additional features and other limitations from IBLite removed. One such feature is encrypted C/S communication via SSL. So, you can also use a SSL based connection from your IBToGo application running on the Mobile devices, connecting to a secure port on your database server on a remote machine with InterBase.

Bill, RAD Studio 11.3 comes with excellent connectivity for database applications using InterBase. IBLite (an embedded database, freely deployable) license is included in "Professional" edition as well. If you have a licensed InterBase Server Edition running on a remote server, you can build Mobile database applications to run on iOS and Android, and also other desktop applications running on Windows, macOS and Linux with either FireDAC or IBX to work with InterBase. Simply include the IBLite license and (Embedded ToGo library) to build your application. This library comes with a "client" portion that is capable of connecting to remote InterBase servers running on the network using the regular TCP/IP based database URL, <remote_hostname/IP>:<database_filepath> This will allow your need to have multiple phones read a table on a server database just fine. No intermediate layer of software required. The InterBase client API is the same for Server and ToGo Editions, and is thus compatible for your purpose as well.

For InterBase Change Views use with IBX (InterBase Express), I am told you can use the following to get the change state of each field in your dataset. Get the change state using the function https://docwiki.embarcadero.com/Libraries/Alexandria/en/IBX.IBSQL.TIBXSQLVAR.ChangeState https://docwiki.embarcadero.com/Libraries/Alexandria/en/IBX.IBSQL.TIBChangeState has various possible change states defined. for e.g. for fld in Dataset.Fields do a[fld.FieldNo - 1] := ChangeState(fld);

Jason, Here's a wild guess. I have no knowhow of Sage, and FD+ODBC. Please discard if it does not make any sense. #1. The column with the bad value (multi-row select) seems to be always the last column in your samples. Do you get the right values if the column is mentioned earlier in the list? #2. What is the definition of the column ITEMS_GROSS in the INVOICE table? Is it by chance a computed by value using any Window Function, perhaps LAST_VALUE()? The last value in a partitioned result set is returned for all the rows in the result set. That would explain the 214.32 value from row 84 coming for all rows. Perhaps the optimizer in Sage does not use an index when you mention invoice_number =1 or 2, and use all the rows (including row 84); and the Window Function is perhaps applied on all the rows (incorrectly) before the OR condition is applied.

Andrea, I see that you are using InterBase 2020 Update 3. We recently released InterBase 2020 Update 4; you can see the details and download from https://blogs.embarcadero.com/embarcadero-interbase-2020-update-4-released/ Just FYI.

Andrea, I got some additional feedback from Dmitry. hope this helps. " About "high firedac network traffic". There were no changes between 11.0 and 11.2 which may negatively affect FD / IB performance. Then as written in responses: use FD connection pooling, and try to analyze single connection traffic. I can add to that: is fiMeta included / excluded from FetchOptions.Items ? IOW, is FD querying IB dictionary on each FDQuery.Open how they are controlling transactions ? explicitly or implicitly ? And what is value of FetchOptions.AutoFetchAll ? To analyze traffic, they can use FireDAC monitor. "

Steve, I hear you. Thanks for the additional notes. InterBase, just by itself, does not have a protocol for providing/resolving datasets over WiFi/Bluetooth etc. Other than the Server, there is no "receiving" component over the network (local or otherwise) in the InterBase core product. One can use IBToGo on both the Desktop and Mobile platforms, and use Change Views. But, the syncing between the devices have to be custom coded (I am not much aware about RAD or other 3rd party packages in this area) to send the changed dataset rows (received from Change Views subscription) from one, to the target device where is received and resolved. If I get to hear of any package that tackles this, I'll surely post here. Am eager to know if anyone else has insight into potential solutions for this.

For "data change", one would use InterBase Change Views to subscribe to changes in tables. Read further at https://docwiki.embarcadero.com/InterBase/2020/en/Change_Views You can see further how to fetch and merge such changes in the database with your local list/resultset in your applications, using FireDAC. See the YouTube demo featuring this at https://www.youtube.com/watch?v=hFAyAqoZLrE&t=1745s You can use InterBase server (embedded with your Desktop system application), and IBToGo on the Mobile platforms for this. Your Desktop application of course cannot use the InterBase protocol to connect to the Mobile phone/tablet, but the other way around is definitely possible. The IBToGo application running on the smartphone/tablet can connect to remote InterBase servers running in the network (Windows/Linux/macOS InterBase Server Editions), and can thus subscribe/pull/push changes from/to the remote database, with InterBase Change Views. Am I missing something in your comment? If so, please elaborate. Thanks!

As it stands today, other than IBLite, there is no royalty-free distribution for general customers of InterBase today. RAD Studio allows some users of higher end editions to deploy IBToGo royalty free as well. The primary motivation of IBLite is to give an easy entry point for customers wanting to build and deploy with InterBase, in some capacity, without any deployment costs. IBToGo and InterBase Server/Desktop Edition are upscaled with additional features, and are thus setup to scale with our customer's (VARs and direct) needs in a customized fashion. From what I know, not all VAR agreements require a huge upfront payment. InterBase has traditionally functioned in a "royalty payment" mode where the VAR pays for the deployment at the end of the quarter, based on how many deployments they have done. Making a pre-payment (small or large) allows the deployments to be deducted against this pre-payment (as per the agreement), and the next renewal window (could be a multi-year agreement) is a good time to forecast based on past few years deployment and growth projections. This also allows Sales team to provide a better discounted price for large volume shipments. Many of our VARs are on these for the last few decades, and are happy with the arrangement. It gives them the flexibility to choose the necessary licenses (users and CPUs) to customize their deployments, and based on our royalty reports can judge the run rate accordingly. The request to contact Sales is purely with the mindset of understanding what a specific VAR's requirements are and how to customize the deal (prepayment, royalty reporting, OS platform choice, users scaling for single install, ToGo .vs. Server etc.), that will allow the Sales team to put together a package customized to the VARs needs. Spending a couple hours discussing this, and any shortcomings of the offerings, with the Sales team, is time well spent IMO. It allows our organization to understand the VARs needs, and make adjustments to our deployment strategies. IBLite, and IBToGo, came about over the years based on customer deployments for small applications, and mobile platforms. So, it is useful to us immensely to understand your distribution models. We currently do not have a generally available licensing model for Demos and free distribution of IBToGo or Server Editions. Neither do we have any "subscription" based model for pay-as-you-use (a la AWS, Azure instances etc.). Having said that, we are eager to hear from our customer community (current and potential) of any changing needs in their environments that we should consider for both our businesses to grow together. I would highly encourage you all to communicate when we fall short, to our Sales and Support teams. Our Sales and Support teams do not read these newsgroups, so stating it here won't be heard directly by the organization (but for occasional feedback such as now from interested people using the technologies). So, please connect with our Sales and Support teams to convey your needs; we are eager to understand and deliver where we can. Thanks!

Juan, InterBase VAR agreements are customized to suit each VAR's needs for deployment. Please connect with your local Embarcadero Sales contact to setup a VAR agreement that includes your custom requirements. Depending on your deployment plans/forecast, I am sure the Sales team would do their best to accommodate any Demo/free usage in the mix. Please do also state to the Sales team what your specific feedback is about IBLite restrictions (database size etc.). All the best!

@Joseph MItzen, InterBase never stores the "encryption value" that is used to encrypt/decrypt database pages (for Database Encryption) or column values (in the case of Column Level Encryption) directly in the database. What is stored in the RDB$ENCRYPTIONS.RDB$ENCRYPTION_VALUE is a derived/encrypted form of the aforementioned "encrypted value" using an encrypted derivation of the System Encryption password (SEP) as the key. If one does not have knowhow of the SEP, it is not possible to derive the correct encrypted derivation of the SEP to further decrypt the stored RDB$ENCRYPTION_VALUE of an encryption key. In the case of an Encryption Key having an additional PASSWORD associated with it, a further gauntlet is available for a stronger encryption filter that needs to be validated by the user/application, as an alternative to the SEP that may be more widely available in the application interface (though not recommended). When you mention "key file" in your comment, what are you alluding to? InterBase does not store Encryption Keys and their values in any adjunct "key file" for encrypting data at rest in the database file. So, I am not sure I follow your argument. Can you elaborate please? I would also be interested to know if your comments are based on some real tests, or speculative/thoughtful comments for engaging further discussion on this topic. Thanks.

@Joe Sansalone, I don't have any hard numbers to share. But, we did run some informal benchmarks when testing our workload way back when encryption feature was being developed. On average, we found that full database encryption (for data at rest) introduced a 5-10% performance impact depending on the encryption key strength used. Of course, full database encryption encrypts only "user data" related data pages, but for the whole database. So, if you have a large enough database in-memory cache, and your "write" activity is minimal, you will mostly incur only "decrypt" cost when loading your database pages into memory. Pages are unencrypted when residing in in-memory cache. Simultaneous user requests don't add any further load to the decryption mechanism. If you have high level of "write" activity, the "encrypt" cost for these database pages will also add to the performance tax. You can offset some of this, if you use InterBase short-term Journaling for synchronous writes (database file writes will be async automatically at checkpoint intervals) resulting in only delta changes written to the journal rather than a whole database page immediately. OTOH, a Column-Level Encrypted database, will have far less negative impact on performance than a fully encrypted database for individual requests. Your mileage may vary as to how many columns are encrypted in your database, and in what strengths. But, column-level encryption could have a larger negative impact if you have hundreds of simultaneous requests accessing those encrypted column data. Each request now has to "decrypt" the column data when fetching the "row" from the database page. For OTW/SSL (network encryption), I understand that the performance impact could largely be felt at establishing the SSL "connection" as compared to an unencrypted TCP/IP socket connection. Since we use OpenSSL for our network encryption, I am sure the data packets encrypted/decrypted between InterBase Client and Server through this encrypted channel will incur some CPU charge (heard about 10%-25% addition from conversations many years ago). If your system architecture allows you to have a Connection Pool setup ahead of time, and then shared by your application threads communicating with the server, the "connection" time can largely be inconsequential over time. The data load though will continue to incur the encrypt/decrypt cost at either end of the encrypted channel. IIRC, you use InterBase Change Views. Providing your Change Views usage is factored in, you are already reducing large data sets from being sent to the client application; so, the impact should be fairly painless thanks to Change Views usage adding to bandwidth savings. You may already know this. But, encrypted (database and column-level) databases have a restriction that they can only be backed up and restored using the InterBase service manager component (GBAK, IBConsole supports this); so, network bandwidth impact is eliminated because of this. Backup/restore is most data intensive, and glad to note that encryption does not impact performance adversely here. A couple questions for you... 1. What kind of database connection and request load do you anticipate? 2. Do you have a middle tier using any custom connection pooling? 3. Have you though of using some VPN layer implementing encryption at the network level, and just use InterBase Client and Server to communicate through this network provider? 4. Do your client applications communicate to systems over WAN or to public cloud environments? Hope this helps.

@Joe Sansalone, if you have already established a connection with Embarcadero Support via an escalation, please get the latest build of InterBase 2020 from them to confirm the issue is truly resolved for your case, before making a purchase of InterBase 2020. You can use the latest build from Support over a 2020 Developer or Trial Edition to validate the fix for the issue you have discovered. A purchase/upgrade to InterBase 2020 for production can follow right after a successful test. Thanks, and good luck! Update:I had forgotten that you already have validated it works well against InterBase 2020. Go ahead with the upgrade then. Thanks. 🙂

Joe, Thanks for your detailed notes, and Jeff, for your responses. A fix is only available in InterBase 2020 Update 2 (circa Sep'2021) and later versions, not in InterBase 2017. InterBase 2017 is in "Passive Support", and we have no plans to deliver any more GA updates to InterBase 2017. The last public update to InterBase 2017 was done in Oct'2020. I recommend you transition to InterBase 2020 if you need this fix in production databases. Thanks!

Just a quick note... In addition to the cross-database platform FireDAC support for latest InterBase in RAD Studio releases, IBX continues to support all features of the latest InterBase releases (InterBase 2020, InterBase 2017 and earlier), on all platforms and architectures supported by RAD Studio 11. So, as a developer, you can use either of these for good connectivity to InterBase from RAD/Delphi/C++Builder, for both Server and ToGo/IBLite editions, on any of the supported platforms. InterBase runs on all target architectures supported for RAD application deployment, including Windows, Linux, macOS, Android and iOS. InterBase is available for 32bit and 64bit on all of the above platforms. I must say that both FireDAC and IBX continue to receive bug fixes as well in addition to feature updates, as necessary. Please connect with Embarcadero Support team should you have a specific question about IBX support of InterBase (or post here for others to respond). Thanks!