How to use PGDBE in CXP programs

[SlavkoDam]

Hi, Tom.

I'm still struggling with complex filters.

PGDBE is used in ISAM without much changes in the code (except the ones needed and some workarounds for filters),
Appending and updating records may take a little more time than with FOX or NTX.

Why do you use filters when it don't work as expected in all situations, and you have to make workarounds? Its obvious why PGDBE update operations are slow, because PGDBE has to update several more columns to keep track with ISAM. Do you, as a DB designer, consider the efficiency of your DB? Or its no matter to you if a DBF file which has 3 fields, after PG migration, will have 7 fields more and a few triggers added. Each DBF file in PGDBE will have a significantly larger size, and needs more time for updating. If its ok to you - fine, keep on that way.

The main difference between set filter and my method, is that filter don't use index. So, my method is much faster than filter, you should try it and compare the results. Once again, its the same technique which use SQL SELECT command. That is why is SQL faster than DBF in data searching and retrieving, it don't use filters. Filters help to shorten programming time, but I think that a true programmer should always look for the best solution and not for the shortest one.

I want to believe in your words: "I can use SQL directly or a mix or whatever I want. Migrating to PGDBE is just the first step.". But, the fact is that you are not doing that, you are still on the shorter way. I wish you to overcome this first step soon. I think that each programming task should be done in the right way. SQL programming should be done with SQL language.

[Tom]

Hi, Slavko.

We have a user base of thousands of users, and we are in a highly competetive market, which is moving very fast, so we simply don't have the time to rewrite an existing, highly integrated, excellent working solution only because purists like you say that this has to be done. We have to provide support for all three database engines as long as they are used, but at the same time, we have to improve data security (that's why we integrated the "impersonation" approach as soon as it was available) and data access technologies. We don't care if somebody likes how we did this or not, we need solutions. And the PGDBE is a perfect way to do all this. We are not alone with this situation.

We created the workarounds in a way that they just don't change anything in relation with the file based database or the ADS. It's just one function wrapped around DbSetFilter(), and this is done automatically by the preprocessor. If the PGDBE is in use, this function creates a special filter condition - travelling through the whole result set, just like in your sample, and collecting records matching the filter condition. This works rather well with smaller tables (which are the only ones we apply filters to). We did some other changes effecting all three engines, but mainly, the application is still the same. And it works excellent. Maybe this is not the best way to move to SQL or to deal with the problem, but it works.

If we would set up a new application, we wouldn't work this way. We wouldn't even use Xbase++. But this is not what we are talking about. And I'm quite sure Alaska will provide PGDBE-support for CXP shortly.

[SlavkoDam]

Hi, Tom.

I understand you perfectly. Apps are made for users, and they don't know and don't care what is going on behind the scene and how it works. I have no intention to break down your programming principles and your company strategies, you know what is the best for you. If you are satisfied with them, that is only matters. But, its good to know that there are better and faster solutions, which are also implemented in a modern database programming language SQL. Besides, there is a saying: "It is not the same". PGBE is like a Xbase++ Hybrid mode. SQL is like a Xbase++ GUI mode. The difference is obvious and enormous.

I agree, I am a purist programmer, I am glad that you noticed that. I have a good education: BSc and MSc degrees from computer eng. and informatics, and Microsoft Development Certificate for desktop, SQL and web programming. For 40 years, I have been studying principles of different programming languages, code optimization and refactoring. I use my knowledge in software development. The results are impressive in speed, robustness, reliability and code clarity. I am aware that I am a "rare bird", but that is what I am and what you will get from me. I think that a programmer should capture the best from the programmer language he use, it is not the same and it is not enough that it just works.

To close this subject, we don't have to agree on our programming principles, but its no harm to exchange opinions. As it is said: 'Les gouts et les couleurs ont ne discute pas'.

[Tom]

To close this subject, we don't have to agree on our programming principles, but its no harm to exchange opinions. As it is said: 'Les gouts et les couleurs ont ne discute pas'.

Sounds excellent to me!

[Tom]

To come back to the point of this thread:

Diego, would you please send the errorlog you get when trying to access the PGDBE with your CXP application to the Alaska support? Even if though you don't have an active account anymore, they do care for technical problems or those connected to the product itself.

[SlavkoDam]

Hi, Tom!

To kindly inform you, I added new functions in PowerSql library, DsSetFilt() and DsSetScope(), for setting filters and scopes in a data set. Those guys you like to use them, will have those options in PowerSql. It works fine in all situations, there is not need for workarounds.

Anyway, here is a quote of an example from Alaska doc for DbSkip() function.

Filter condition with DbSeek() and DbSkip()

// In this example, the addresses for all customers
// with the name "Miller" are listed. Instead of using
// DbSetFilter(), the filter effect is accomplished
// faster using faster DbSeek() and then DbSkip() called
// in a DO WHILE loop.

PROCEDURE Main
LOCAL cLastName := "MILLER"

USE Customer NEW EXCLUSIVE
INDEX ON Upper(LastName+FirstName) TO CustA
DbSeek( cLastName )

DO WHILE cLastName == Trim(Upper(Customer->LastName))
? FirstName, LastName
? Street
? City + ",", State, Zip
?
DbSkip()
ENDDO

CLOSE Customer

RETURN

As you can see, Alaska also claims that using index and my method "seek-while-for" is faster than using filter in database searching. Only, it requires a few lines of code more. Some guys may not agree with this, but it is confirmed by Alaska, and that is enough verification for me that I am right.

[Tom]

Hi, Slavko.

I don't care about speed comparison. We use filters only on smaller tables (< 10.000 records), and this combined with the speed of modern systems makes that approach usable. Besides, our filter expressions are much too complex to migrate them into such simple seek-while-expressions - and some of our filters are dynamical (your construction isn't), so the result sets vary while the users navigate through them. Besides this, the PGDBE tries to translate filters directly into WHERE-clauses of SQL-statements, which should be the fastest way to get the data. But this only works (and it works!) with not so complex filters. It works excellent with conditions that can be translated into literal statements.

Add: If we use complex filters on larger tables, we combine them with scopes. Scopes use index search and are very fast. The filter expressions are evaluated only on their result sets. This is fast with all engines, even with the PGDBE (if you have a filter expression that can be used by it).

[Diego Euri Almanzar]

Hello Tom

I am definitely in love with CXP. I've learned a bit of JavaScript, HTML, and Jquery, and everything is going great for me. Except that I've been having issues with memory variables, as many times I need to swap values between CXP and JavaScript, and I haven't been able to. I did the following, but it works when it feels like it:

$("#fvprice").val(@(alltrim(globalprice)));

For that to work, you have to give the screen a reload, and sometimes it works using

this.submit();

In general, it presents you with delayed data, as if they were two worlds that communicate arbitrarily.

It seems that PHP is more logical in that respect. In PHP it is done as follows:

var jsVar = "<?php echo $phpVar; ?>";

But, since I am too old to leave XBASE++, I will continue as my CXP. That, notwithstanding what I explained above, from my point of view, it is easy, fast, and safe.

Taking your suggestion, I have sent an email to Alaska Software, to know about the integration of PGDBE to CXP.

Wish me luck!

I'm a bit limited on time, but tomorrow, I will read the other comments.

Greetings to all.

[Diego Euri Almanzar]

Hello Tom

your suggestion gave me the courage to write to Alaska, and the response was satisfactory: PGDBE, if it can be integrated into CXP. I haven't put it into practice yet, but I will in the course of the week. This is great news for me, because I am definitely in love with CXP. I was headed for obsolescence, and sudden death, technology-wise. And, thanks to you, I was able to react, and start my project in CXP, which is already very advanced.

Here is Alaska Software's response:

Mr Almanzar,

For accessing the PostgreSql backend you will just need to loade the pgdbe (if not loaded yet) and establish a connection via the class DacSession().

Please note the best way to do this is within a helper dll. You find a description about the concept here: https://doc.alaska-software.com/content ... lpers.html

For preparing the page processing use the function DbeList() to check whether the pgdbe is already available - load pgdbe otherwise.

Furthermore open a connection and make sure to disconnect before returning from the page.

Please note that I am sending you this response as a courtesy. Please contact sales@alaska-software.com to renew your support eligibility.

I hope this helps anyway.

With my best regards,

Andreas Herdt



with regards/mit besten Grüßen
Support