Jump to content


  • Content Count

  • Joined

  • Last visited

  • Days Won


Sriram last won the day on April 6

Sriram had the most liked content!

Community Reputation

3 Neutral

1 Follower

Recent Profile Visitors

The recent visitors block is disabled and is not being shown to other users.

  1. Sriram

    Interbase VAR Licence and Demos or Free Versions

    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!
  2. Sriram

    Interbase VAR Licence and Demos or Free Versions

    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!
  3. Sriram

    Interbase: Full encryption vs None, performance diff

    @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.
  4. Sriram

    Interbase: Full encryption vs None, performance diff

    @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.
  5. Sriram

    Change Views (2017 vs 2020 Server)

    @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. 🙂
  6. Sriram

    Change Views (2017 vs 2020 Server)

    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!
  7. 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!
  8. Sriram

    error on Interbase 2020 server

    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
  9. Sriram

    INTERBASE strange performance problem

    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))
  10. Sriram

    Copy Encrypted InterBase Database to another machine

    @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.
  11. Sriram

    Copy Encrypted InterBase Database to another machine

    @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
  12. Sriram

    Questions about Interbase change views

    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
  13. Sriram

    Copy Encrypted InterBase Database to another machine

    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
  14. Sriram

    Interbase Metadata View User Privileges

    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.
  15. Sriram

    InterBase or Firebird?

    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