Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (4)

Resolved Need a no/low cost database recommendation

  1. How-to
0
Votes
Undo
  1. Olan Knight
    Olan Knight
  2. PowerBuilder
  3. Wednesday, 7 October 2020 15:38 PM UTC

PB2019R2
Windows 10, 64 bit platform

Summary
Can someone recommend an open source or low cost database that has high volume transaction capability and reasonable security options AND is case-insensitive?


Details
We are currently using Oracle as our database, but management has decided that the fees for Oracle are simply too high. Thus we are seeking an alternative database - cost and capability being coequal requirements.

Management has also, in its infinite wisdom, decided to use a database from a company called Enterprise DB that is a PostgreSQL database wrapped to look somewhat like an Oracle database to the developers.

The problem is that the PG database is case sensitive.

Like most of us, I have thousands of objects: dws, embedded sql in script, templates, database scripts, and utilities where case was never an issue, so items are cased for readability. Example:
     SELECT fccid
     FROM    bill_master
     WHERE ban = :ls_ban;

and

    select fccid
    from   BILL_MASTER
    where ban = :ls_ban;


EDB has so far refused to even consider the option of adding a simple change at the input/port to UPPER or LOWER all string input data. Doing so would, of course, make the database effectively case insensitive with zero impact to the rest of the database internals.


Thank You!

connection to oracle
Autocompiler.2019 installer
Responses (4)
Miguel Leeuwe
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Saturday, 10 April 2021 02:23 AM UTC
  2. PowerBuilder
  3. # 1

Hi Olan,

It might come a bit late, but we are using Oracle and Tibero. Tibero is waaay cheaper than Oracle, very fast (comparable to Oracle) and pretty solid. (they increased their prices lately, but they are still a lot cheaper).

The good thing of Tibero is that it's 99.8 % compatible with Oracle. It comes with a tool that allows you to migrate your Oracle db to Tibero. (I know that EnterpriseDB also has a tool, but that one still meant a lot of manual work, the last time I checked). So. ... this means we can affort ourselves to maintain only our Oracle database without having to worry about Tibero. Worst case scenario, we just migrate the complete db again, but to be honest, once you've done that, you can simply run the changes you have scripted for Oracle on Tibero too and done.

The 'bad' thing about Tibero is that it's based in South Korea, though they have offices in other countries, support is pretty bad, at least here in the UK, (documentation is okay though), and we have to connect using JDBC which gives the best results and for some things, like Crystal reports, we use their ODBC driver. There's also a driver for .Net.
Another bad thing is that Appeon doesn't support it, but to be honest, our setup works 100% with Appeon. No Powerserver possibilities though, which is a shame.

regards

Comment
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Saturday, 10 April 2021 03:07 AM UTC
Having said all that, I DO think you guys made the right decision to go with Postgresql

:)
  1. Helpful
Olan Knight
  1. Olan Knight
  2. Wednesday, 14 April 2021 15:26 PM UTC
Thanks, Miguel, I hope you are right! :)
  1. Helpful
There are no comments made yet.
Olan Knight
Olan Knight Accepted Answer Pending Moderation
  1. Saturday, 10 April 2021 00:22 AM UTC
  2. PowerBuilder
  3. # 2

Well, Management decided to go with a modified PostGreSQL  database. There's a company called "EDB", Enterprise DataBase", that has tweaked the PG database such that it "looks like an Oracle database".

The worst part of this is that the PG database is STILL case-sensitive. But it is what it is.

I'm going through every bit of code in PowerBuilder and changing schema, table, and columns names that are in UPPER case to LOWER case. I'm also getting rid of all of the quotes:  ~"  where possible; i.e. not comparing to a fixed string.

THEN I'll do the same thing in our Java component.
THEN I'll do the same thing in my 300+ database scripts.

I just tell myself if that's what they want to pay me to do, that's what I'll do.  :/

 

Later,
Olan

 

Comment
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Saturday, 10 April 2021 03:20 AM UTC
Just an idea:

The names of your views, tables, triggers, procedures, funcions, sequences, packages, etc. All of those could be retrieved from system tables.

You could write a program that finds each and every one of them (or go manual and use notepad++, find and replace in files). The program does have to ask for confirmation of each value, since you might have a table name as a string value or label, etc. somewhere. Also, maybe a tool like PBLPeeper could be of help to change your datawindows.

regards
  1. Helpful
Olan Knight
  1. Olan Knight
  2. Wednesday, 14 April 2021 15:27 PM UTC
That might be worth doing. Thanks for the idea!
  1. Helpful
There are no comments made yet.
mike S
mike S Accepted Answer Pending Moderation
  1. Wednesday, 7 October 2020 20:04 PM UTC
  2. PowerBuilder
  3. # 3

i'm confused, aren't you looking for the database that will work regardless of whether you coded 

bill_master or BILL_MASTER 

for table names?

 

"The problem is that the PG database is case insensitive."

isn't that what you want?

Comment
mike S
  1. mike S
  2. Wednesday, 7 October 2020 20:20 PM UTC
I think the database converts your table/column names on the fly to lower case if they are not quoted (is that right?). one of the database connection options in PB is to quote identifiers, so if that is off won't that work?
  1. Helpful
Olan Knight
  1. Olan Knight
  2. Wednesday, 7 October 2020 21:47 PM UTC
Thank you for your response, Mike!



That was Chris Pollach's suggestion.

I'll test it next week, when I get some room to breathe.
  1. Helpful
Olan Knight
  1. Olan Knight
  2. Wednesday, 7 October 2020 21:49 PM UTC
Heh.

Thanks, Mike. That is indeed what I want, but - sadly - not what I have.

I have corrected the original post.
  1. Helpful
There are no comments made yet.
Armeen Mazda @Appeon
Armeen Mazda @Appeon Accepted Answer Pending Moderation
  1. Wednesday, 7 October 2020 15:59 PM UTC
  2. PowerBuilder
  3. # 4

There is simple fix but a bit of monkey work to implement throughout your app... just put quotes around "bill_master" and it will be case sensitive.  Here is Stack Overflow Q&A confirming the solution: https://stackoverflow.com/questions/21796446/postgres-case-sensitivity

Comment
Load more comments
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Wednesday, 7 October 2020 16:51 PM UTC
Fingers crossed ... I hope that this setting works for you!
  1. Helpful
Armeen Mazda @Appeon
  1. Armeen Mazda @Appeon
  2. Wednesday, 7 October 2020 21:24 PM UTC
Postgres is good DB, especially for types of apps people usually develop with PowerBuilder. I really think you should focus your energy to figure out how to make Postgres work for your project rather than search for a different open-source DB that PowerBuilder doesn’t officially support.
  1. Helpful
Olan Knight
  1. Olan Knight
  2. Wednesday, 7 October 2020 21:48 PM UTC
I have since learned that the DB group will not support "another database", so I will focus on finding ways to make my code work with PG.



I still think it's idiotic to have a database that is case sensitive; or at least without the option to be case INsensitive.

Grrr.
  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.