bb.donnay-software.com

After Tom Liehr's review of the most current Xbase++ PGDBE, I decided to make another run at this.

I'm using SqlQuery to connect to PostGreSQL 8.3. I have used pgAdmin3.

I tried PostGreSQL Server 10. Apparently, it only works with pgAdmin4 which is a web-based client that I hate.

I upsized a few ADS databases and am doing browses in both ISAM mode and SQL using PGDBE using SqlQuery.
Performance looks really good under SQL and ISAM.

What experiences do any of you have with indexes under ISAM?
I cannot make OrdsetFocus(<tag name>) work as it did with ADS.
I can see the indexes in the schema under Pgadmin3, but dbInfo() doesn't return any index info.
Is it possible to use dbSeek() and dbSetScope()?

Also. dbinfo() returns incorrect Table name info.

We don't use filters, but indexes are important for our many One-to-Many screens, and other parent/child relations.

Am I wasting my time trying to use PGDBE in ISAM mode?

Hi, Roger.

DbInfo(DBO_FILENAME) with PGDBE in ISAM mode returns the correct filename, but it suggests the table resides in your app path (if you created the table with the PGDDBE, if you upsized it, it will return with the original path). Since the table doesn't have a path anymore, I just remove the path from the filename, but dealing with filenames is somethin you shouldn't do anymore when using the PGDBE. This is one part of the project, I still have some problems with - since schemas are not supported by the upsizing mechanism and, as far as I understand it, by the DBE itself, so the "public"-schema must be used, we have to find a way to implement a solution for multiple tenants. Schemas would be excellent for this, since they know a mechanism similar to SET PATH/SET DEFAULT, but Alaska suggests to use multiple database connections instead (there is a document about this topic you may request from Alaska). We are currently working on it.

You know our applications, they are really, really big, and they became much bigger since the last time you saw them. After struggling around with upsizing inside the app and controlling this, fiddling with Table() instead of File() and so on, it just worked. The app runs, it does the same as before. Scopes work, locate works, seek works, almost everything works, except filters, but you can evaluate any kind of very complex navigational condition on any record coming from the PGDBE, so as an interim solution, we removed most of the filters and created some workarounds. We are working on replacements for several service functions using SQL statements and talking directly to the server. But 98 percent of the really, really big shit simply works. It runs as before. No, it runs better. And we know support three engines (file based, ADS, PG) in one code, and, to be honest, two compiler versions, since we have some customers still using our software based on 1.9SL1.

Addition:

We use PostGreSQL 12 and 13. I highly recommend to switch to 12 at least, and to pgAdmin 4. I enjoy working with this. It's better than the management tools for MySQL and MS SQL.

OrdSetFocus() works. The only thing we needed which doesn't work is OrdKeyNo(). This is not supported by the engine, and I understand this. It would cost a huge overhead to do this.

The PGDBE approach is the best thing Alaska invented since 1998. It took ways too much time to do this, and it's still not finished, but it's usable right now, it's fast and reliable. And the PostGreSQL server doesn't cost nothing.

hi Roger,

1st. Question is : are your Customer "willing" to use those PostgreSQL Table with Alaska PgDBE only

to use PgDBE with "Index" it will create "internal" FIELD(s) which Value is Result of IndexKey() when "upsize" DBF

my Customer want to use "there" Data also with "other" App which does not know about "internal" FIELD(s)
so when "edit" Data with "other" App it will not update "internal" FIELD(s)

---

i recommend to use "Pass-Through" Mode or "native" using LibPQ.DLL with CLASS from Phil Ide
btw. i have used Ot4Xb Version for PGU but now revert to Phils Original Version now under harbour.

the Work are to "translate" xBase Command into SQL Statement or Query so "think SQL"
so "forget xBase" when you work with SQL. ISAM Style is not need under SQL

---

@Tom :

The only thing we needed which doesn't work is OrdKeyNo(). This is not supported by the engine, and I understand this. It would cost a huge overhead to do this.

Alaska only does not know "how"

If you have a really, really big file based application and the need to move to SQL to have a safe, robust, fast and up-to-date database system, PGDBE is an excellent option. None of our customers wants to dig through the data and tables, and we don't want them to do this. It's not only something connected with "internals fields", but with our processes, software integrity and much more.

If you have simple and small programs dealing with few tables, take a different way. Go ahead with whatever you want. But if you have systems with thousands of tables and millions of lines of code, you will be away from the market for several years to get this done.

Alaska only does not know "how"

This is just bullshit, Jimmy. This is not the way the PGDBE works.

Tom -

I very much appreciate your responses to my questions.
PGDBE is obviously working very well for you. Now I need to figure out how to make it work for me.

Am I to understand that my problems with OrdSetFocus(), dbSeek(), etc are due to PG 8.3 and this will be solved in PG 12?
dbInfo() will return no info about indexes, even though they show in the schema.

I don't care about OrdKeyNo(). That isn't important.

I may write my own PG Administrator and add it to SqlQuery if I find that I can't work with pgAdmin4, however if you consider it to be better than phpMyAdmin, then I will probably use it.

Here is some of my code from SqlQuery.prg. This is how I browse in ISAM. Am I supposed to be using an INDEX clause with the PGDBE in the USE statement?
If so, how do I do that? ADS never requires it because it opens indexes automatically. The documentation is very vague. The "Navigational Support (ISAM)" section says TBD.

I remember Steffen talking to us here in Boise at my conference in 2008, all about PostGreSql.
Almost 12 years later, I am still confused about whether or not I should invest my time in this.

I remember Steffen talking to us here in Boise at my conference in 2008, all about PostGreSql.
Almost 12 years later, I am still confused about whether or not I should invest my time in this.

Hi Roger, I have the same problem.

I have experimented with it years ago, upsized some tables to SQL and tried some little modules with it. I wasn't convinced at that time, since I saw no benefit in it. I have a lot of small customers, up to 5 users, and I'm afraid that the installation and maintainig of PostgreSql at the customers side, will be a lot of work.

I know that a system can be build so users can decide if they stay with dbf or upgrade to Sql. Tom mentioned he supports also ADS with the same source code, so the upgrade can be done for selected customers, where they will benefit of it. I suppose with more than 5 users SQL can be a better solution.

Maybe it is time to buy the latest version of the tools I use, and to spend some months to migrate our solution to SQL.

Hi, Roger.

We almost finished migrating our big project with PGDBE. It's not one hundred percent ISAM, but the need of changes isn't that big. There are some workarounds needed, but that's mainly it, and it's stable and it's fast. It's faster than DBF/CDX, even on a workstation locally. If you have big applications and no time to move all the code to native SQL, this is definitively the best way. The most work is to get rid off all the stuff like File/FErase a.s.o. And, as I mentioned - we are still working on a solution for multiple tenants.

I always open indexes with the INDEX command, so I have no answer to that question. Besides that, the way you showed is almost the same here.

Hi Tom,

We have a lot of dbf files for our data driven screens. They contain info for the screen layouts and browses. I suppose it is no problem to have the real data in PostGres and to use this kind of files as dbf files. It isn't a problem to use both DBE's at the same time?

Eureka!!

I figured out how to get indexes.
OrdlistAdd( <tablename> ) does the trick.

This is very vague in the documentation.
Also, there are no sample programs.

At least I have it working now in ISAM using SqlQuery.

This is working great under PostGreSql 8.3.