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!

delphi159, I have modified the query to provide column names while defining "dt1". Internally, in the "dt1" derived table definition, not every "select" from the UNIONs seem to name their columns. So, it would be nice for the external "dt1" to explicitly qualify them. Also, made a small correction when using "dt2.endqnts" in the upper level query. Hopefully, this works for you. FYI: InterBase 2020 (with updates) has support for Derived Tables, non-recursive and recursive CTEs. select G.Goods, G.Goods_id, coalesce(dt2.incomes,0)as incomes, coalesce(dt2.sales,0) as sales, coalesce(dt2.writeoffs,0) as writeoffs, coalesce(dt2.endqnts,0) as endqnts From Goods G Join ( select Goods_id, coalesce(sum(inc),0) as incomes, coalesce(sum(sale),0) as sales, coalesce(sum(writeoff),0) as writeoffs, sum(coalesce(inc,0)-coalesce(sale,0)-coalesce(writeoff,0)) as endqnts from( select i.goods_id, sum(i.qty) as inc, cast(0 as float) as sale, cast(0 as float) as writeoff from income i where cast(i.recdate as date) <= :d group by i.goods_id union all select s.goods_id, cast(0 as float), sum(s.qty) as sale, cast(0 as float) from sales s where cast(s.recdate as date) <= :d group by s.goods_id union all select w.goods_id, cast(0 as float), cast(0 as float), sum(w.Qty) as writeoff, from writeoff w where cast(i.recdate as date) <= :d group by w.goods_id) dt1 (Goods_id, inc, sale, writeoff) group by Goods_id) dt2 (Goods_id, incomes, sales, writeoffs, endqnts) on G.Goods_id=dt2.Goods_id order by Goods

Can you please state the InterBase version and the PLAN output? You may want to use 'isql' command-line (and SET PLAN ON) before running the SQL. The PLAN output will let you know if an index on "sp.deleted_for_me" column is being used for the SQL statements. Just FYI: InterBase 2020 version converts predicates with NOT condition to their equivalent condition to not use NOT, where possible if an index could be matched to optimize the retrieval. Earlier versions of InterBase did not do this. Users have in the past used the "NOT a = <boolean value>" as a way to instruct the optimizer to not use a really bad index (bad selectivity index) on column "a". You may be using an older version of InterBase that allows you to discard the use of such an index for this condition with the NOT condition. Caution: if you'd like to hint the optimizer to not use such a bad index in InterBase 2020 with the NOT condition, consider having a shadow column (deleted_for_me_copy COMPUTED BY deleted_for_me) and no index on deleted_for_me_copy. This will allow you to have the original index for good value matches (deleted_for_me = TRUE), and use the condition "deleted_for_me_copy = FALSE" for the large number of duplicate FALSE value records. Alternatively, if you do not want to create a shadow copy of the column, you can just provide the well optimized PLAN with your query; the one where the index is not used for the condition (NOT (sp.deleted_for_me=TRUE))

@corneliusdavid, The RAD Server production license you get with your RAD Studio Enterprise can definitely be used in the scenario mentioned above. I do not see any violation of licensing/EULA.

@corneliusdavid, thank you for posting your blog article in your response above. Now, I understand your issue a bit better. I am sorry you had a lot of difficulty using the product for your intended purpose. Here are a few things I want to clarify... You can use *any* number of InterBase instances on a single machine, as long as each of these instances are licensed and registered with a unique S/N Each InterBase instance has to be uniquely identified on your machine; this is typically the instance name you provide when you are installing either the normal InterBase product, or RAD Server (same installer as InterBase). The unique identification combination is the (1) instance name, and, (2) TCP/IP socket listening port for the InterBase server process. Each InterBase server instance will listen on their own unique socket port. Each install of InterBase (and RAD Server) comes with its own set of InterBase command-line tools, IBConsole, IBMgr, ibserver.exe etc. They are self-contained with their own user authentication database, admin.ib. The server instances do not share any database user accounts outside their own instance. On a single machine, you *can* have a mix of InterBase Developer Edition (downloaded from the product portal), InterBase Server Edition, RAD Server (production S/N), RAD Studio installed InterBase Developer IDE Edition (automatic install, optional in RAD installer) etc. And, you can install any number of InterBase versions as well, with any combination of Editions, provided you follow rule (2) above. If your InterBase Developer Edition or Server Edition has been used to Encrypt databases with their own SEP and Encryption keys, just remember that these databases should *not* be used with RAD Server (production) instance of InterBase; and, vice-versa, you should not try to access your RAD Server (production) databases from InterBase Developer Edition or Server Edition. The Developer Edition and Server Edition server instances do not know what the SEP should be to connect to your RAD Server (production) databases (as designed). By no means should a new install of InterBase should interfere with your current databases in use (by other instances), as long as you have not broken rule (2) above. From your application(s), my recommendation would be to use the TCP/IP localhost loopback going to that specific InterBase instance you want to connect. For e.g. your database URL would look like "localhost/<instance_name/portNumber>:<database_filepath>", as in "localhost/14064:C:/mypath/foo.ib", or, "localhost/gds_db:C:/mypath/foo.ib", or "localhost/ib2020:C:/mypath_ib2020/ib2020file.ib" etc. There is only one IBConsole.xml file used to store all configurations for the end-user of the system. So, I would always just launch the IBConsole from the most recent InterBase installation on your system, as this would have the most up-to-date feature set, and bug fixes. Whenever you install a new version of InterBase that supersedes other versions on your machine, just make a shortcut to the IBConsole from that install and launch that. In your IBConsole, when setting up a connection to a local/remote server, I would normally choose the localhost/loopback TCP/IP setup rather than the "Local Server" (IPC) connection. This helps me clearly identify which InterBase instance I am connecting to by specifying the instance name or the TCP port ID of the listening server. When you now use "Alias" (some more bugs for this are fixed in an upcoming release), the database alias is maintained in that InterBase server instance's admin.ib (user authentication database, stated above). For the InterBase Developer edition installed by RAD Studio, use the RAD Studio License Manager to manage it's licenses. For all other InterBase instances (including RAD Server production license), use the License Manager that is installed with InterBase (you can get it in the Windows shortcuts setup with your InterBase instance). Once you have done the above, please post any new observations. We can take it on from there, as this post is getting a bit long already. 🙂 Good luck! Best wishes, Sriram

Bob, You can develop your application with a normal schema, and once completed, you can implement InterBase Change Views related subscription definitions, and update your application for those specific use cases. RAD Studio comes with a couple samples that go into further detail how to use InterBase Change Views with FireDAC components for fetching and merging changes to a local database, and vice versa. You may find this YouTube demo about InterBase Change Views and RAD Studio useful; Best wishes, Sriram

David, The RAD Server Production license is not compatible with normal InterBase Production licenses. Even within RAD Server, there are 2 modes; development, and, production. The RAD Server database from a "development" environment cannot be just copied over to a production environment, and vice-versa. Since the "development" RAD Server license does not stamp the database file with any SEP (InterBase System Encryption Password), this database file can be used with normal InterBase Developer Edition. But, when you want to work with RAD Server Production deployment, you will want to rebuild your RAD Server database in "production" mode, so it uses a database file that is already stamped with a InterBase SEP value (not known to any user); you can save data in this as allowed by RAD Server schema. You cannot use a RAD Server Single/Multi-site production license to build your own InterBase encrypted databases where you need to know the SEP value. To use your own InterBase databases with SEP/Encryption, you will need to purchase a normal InterBase deployment license for Server Edition. I am sure you have already gone through the detailed demos/tutorials etc. about RAD Server. If not, here they are. Hope you find them useful. https://blogs.embarcadero.com/new-rad-server-ems-articles-resources-and-ebook/ https://cc.embarcadero.com/item/30879 In summary, if you want to create your own database schema with InterBase encryption, you will need to buy InterBase Server Edition license. If you want to use RAD Server license to store data as per RAD Server guidelines, you will not have access to any SEP value as that is hidden in RAD Server, to be available only for RAD Server database files (automatically). You can use a RAD Server database file with normal InterBase license, and vice-versa. Should you have any specific questions that we can help answer, please contact Embarcadero Support team at https://www.embarcadero.com/support. I am sure they can guide you further effectively. Also, please note that some of the documentation above do refer to InterBase 2017 from some years ago; current RAD Server uses the latest InterBase 2020 version, but the documentation still applies. Also, any reference to "purchase" a RAD Server deployment license is old (from many years ago); I understand RAD Studio Enterprise/Architect users get a deployment license for RAD Server included. Thanks. ~Sriram

One cannot do this in InterBase. The database metadata is kept in system relations, and all the users have access to the system relations. So, they can see all metadata in the database. As the owner of the database, you can make the whole database metadata invisible to all users. Check $INTERBASE/examples/blindmeta.sql for this. There are also complimentary readmeta.sql and writemeta.sql to allow various access to the system metadata for all users. You either grant or revoke a (set of) privileges for users to the whole table (user defined or system tables); thus, you cannot grant a user a privilege for a subset of the records, OOTB. Now, you can modify blindmeta.sql to suit your needs, where you may want to make it all blind to a certain group of users, and give read/write privilege to others. Alternatively, a long winded way (I haven't tried this), would be to make system metadata blind to all users (using blindmeta.sql), and then write a custom stored procedure for users to see metadata for the tables/fields they have access to. You will need to grant "SELECT" privilege on the system relations for this stored procedure, and EXECUTE of this procedure to PUBLIC. Your stored procedure would internally validate the user against privileges they have on the "user tables" by looking up RDB$USER_PRIVILEGES, and only then execute the lookup to RDB$RELATIONS and RDB$RELATION_FIELDS/RDB$FIELDS which have the metadata for tables/fields. Your authentication logic is then centered inside the stored procedure, and achieves the control you want to have. Perhaps a lot more work than what you wanted, but possible I think.

I would only add one more point to this. IBLite is available for free for deployment to Windows, Linux, macOS, iOS and Android from RAD Studio. So, for your small local database file, if the database is going to be less than 100MB size, IBLite would work well. It will be free for your current needs, and if you need to upscale to C/S solution or other features like encryption or changed data tracking, it allows you to take your database file directly to InterBase ToGo or Server for a cost. https://www.embarcadero.com/products/interbase/product-editions

Roger, In your Project | Deployment section, please make sure you select the database file and have its "Remote Path" set to "assets\internal" for Android. The GetDocumentsPath() takes your database from this location. Also, the database file name is case sensitive; so make sure the database file name in your Project Deployment listing is the same as the one in your code where you are accessing it as thus... TPath.Combine(TPath.GetDocumentsPath, 'yourDatabase.ib'); I hope you have selected "InterBase ToGo" in the Featured Files selection to identify and deploy InterBase files to be deployed to Android, from within the IDE. See step (2) at http://docwiki.embarcadero.com/RADStudio/Sydney/en/Mobile_Tutorial:_Using_InterBase_ToGo_with_FireDAC_(iOS_and_Android)#Deploying_InterBase_ToGo_Required_Files_and_the_Database_File_to_Mobile