Postgre ISAM command

[jezda]

I am working on switching my application to postgresql. Most things work fine but I have a problem with the isam command "SET FILTER TO...".
Does anyone have any replacement for this ISAM command?

[Wolfgang Ciriack]

DbSetFilter()

[k-insis]

dbsetfilter()

Change SELECT and requery , reattach+refresh to dcbrowse if needed.

What does PGDBE documentation say on USE on pgSQL VIEW , pgSQL stored procedure returned data?
Is there anything online like in doc.alaska-software.com ?

Those two are fastest.

I am working on switching my application to postgresql. Most things work fine but I have a problem with the isam command "SET FILTER TO...".
Does anyone have any replacement for this ISAM command?

[Diego Euri Almanzar]

I hope this helps:

I have been dragging SET FILTER since I used Clipper. I adapted to them, even knowing that they were synonymous with slowness, no matter how fast my computer was.

When using Xbase++, I gradually replaced them with Append From, as well as Set Scope.
For the first one I need to create a temporary table, and for the second have an index file with a key with the same structure as what I was looking for.

I have been using the Postgresql driver for 3 years, and I definitely had to delete the SET FILTER that I had left. The Posrgresql driver does not work with 99% of the filters that are generally used, including filters that have dates.

If it is for simple filters, like the following example, it could work:


mont1 := 100
mont2 := 3000

#ifdef USE_ISAM_FILTER
// Variant 1 using ISAM methodolgy
cb := "{|| DCMONTORIG >= " + Str(mont1) + ".AND. DCMONTORIG <=" + Str(mont2) + "}"
cb := &cb
DbSetFilter( cb )
#else
// Variant 2 using SQL query
oStmt := DACSqlStatement():fromChar( "select dccodigo, dcfechdocu, dcmontorig from maefactu where dcmontorig >= ::mont1 AND dcmontorig <= ::mont2" )
oStmt:mont1 := mont1
oStmt:mont2 := mont2
oStmt:build():query()
#endif

GO TOP
DO WHILE .NOT. EOF()
SKIP
ENDDO

[Diego Euri Almanzar]

Another thing,

regarding dated filters, the engineers at Alaska Software have published something that I never understood. Here I leave the link, in case anyone wants to study it:

https://ilx.alaska-software.com/index.p ... server.34/

Best regards.

[Tom]

the engineers at Alaska Software have published something that I never understood.

The problem is that filters (DbSetFilter/SET FILTER TO) are converted to WHERE-statements for the PG-server, which are executed by the server, and that means that you can use almost nothing in filter expressions. You can't use variables/variable names or workareas, you can't use your own functions or most of the functions provided by Xbase++, you can't build filters that are more complex than simple expressions like "name = 'Miller'". That means: filters are f*cking useless with the PGDBE. What is shown in the ilx-document explains how functions used in Xbase++-filters could be rebuild on the server side as something that is called "stored procedures". These are procedures (functions) programmed for an SQL server. You have to make sure that those functions work like your Xbase++-functions, that they are installed with every PG-server that your customers use and that they get adapted to newer versions of Postgres. Besides, you have to learn a new programming language. I don't think that this really is a solution, and there are lots of functions you can't build as stored procedures. And you still don't have your workareas or variables there. In my humble opinion, this is completely bullshit. As we try to provide all supported database structures in one code (DBFNTX, FOXCDX, both with ADS and PGDBE), we can't overload our code in that way.

But this is really a problem with filters only. If you have a very complex filter expression dealing with UDFs, lots of workareas, results from other filters and so on, you will never get this working with the PGDBE.

If you do something like this:

Code: Select all

DbSetFilter({||<MyVeryComplexExpression>})
DbGoTop()
DO WHILE !Eof() 
  * do something with the collected records 
  DbSkip(1)
ENDDO

You can adapt this code (for all database models) without any lost of whatever if you move it to this code:

Code: Select all

DbGoTop()
DO WHILE !Eof()
  IF Eval({||<MyVeryComplexExpression>})
    * do something with the collected records
  ENDIF
  DbSkip(1)
ENDDO

since "MyVeryComplexExpression" is now evaluated on the result set. This works with every code you used before.

If you want to have it more compact, use it this way:

Code: Select all

DbeEval({||IF(Eval({||<MyVeryComplexExpression>}), * do something ',NIL)})

With the standard DBEs, you won't get any negative effect with this, since it's mainly the same as filters work on standard DBEs (a filter expression is evaluated with every move in the table, and records not matching are skipped). But the PGDBE is different - the result set (the "cursor") is loaded into memory, and if your tables are large, a large result set is loaded, which may cost traffic and time. This marks the advantage of how Alaska implemented filters for the PGDBE - they create a much smaller result set, and they do it really fast, even without indexes involved. But, as mentioned above - those filters are very limited. You just can't do what you did before. If you search the Alaska Knowledge Base for "PGDBE", you will find a lot of open PDRs concerning filters, and I don't believe they will ever get closed, as long as Alaska builds a "fallback to local", as they did for the ADS.

But this is only the solution for this kind of usage of filter expressions. We built a function "BuildSQLfilter(bMyExpression)" which works for small tables. This collects the records which match the expression and create a filter expression for them which can be used by the PGDBE. It uses the "__record" field available in every table converted to PDGBE/ISAM and builds a string expression. But this is very limited. There are other strategies for dealing with filters, but in order to create a stable application, we are working on getting rid of all filters left.

[SlavkoDam]

It seems that Tom finally figured out that filters should not be used in PgSql, as he said they are "f*cking useless". I told him the same before, but he claimed that they work excellent.

Tom, it is be better to use this code for searching, since it works much faster than to do manual SKIP for each record.

LOCATE FOR <MyVeryComplexExpression>
DO WHILE FOUND()
* do something
CONTINUE
ENDDO

The best way is to use index and SEEK. If you don't have an index for the SEEK exp, you should create a temporary index that is the closest match to the SEEK exp. The time for creating an index is many, many times less than to do unnecessary SKIP through the whole database. This is a great advantage when working with large database and when the target for the search is near the bottom of the database. Then use this code.

SEEK exp
IF FOUND()
DO WHILE EVAL(condition) .AND. !EOF()
* do something
SKIP
ENDDO
ENDIF

Diego, it is very simple to create Xbase++ code for filter or SELECT ... WHERE which have a date value. There is no need to create stored procedures. If you want a help for that from me, just ask. Also, using appropriate PgSql functions it is possible to create Xbase++ code for complex filter/WHERE expressions with any data types. Yes, you have to read and learn them, but they are very simple and easy to use.

Slavko

[Tom]

It seems that Tom finally figured out that filters should not be used in PgSql, as he said they are "f*cking useless". I told him the same before, but he claimed that they work excellent.

This is not true. The PGDBE works excellent, but I mentioned several times that (complex) filters are a heavy problem if you need them.

I'm talking about migrating with the least amount of effort possible - and keeping the code alive for other database models. I believe this is the approach for most of us. So, if I use the DbLocate-approach, I lose performance with any file based DBE and even with the ADS. Besides, we only use filters in addition/combination with scopes or seeks - or on very small tables. I mentioned that before. And I said that there are several strategies of getting around the problem with filters with the PGDBE. Programming stored procedure seems to be not such a good idea.

But, hey, many ways lead to Rome.

[SlavkoDam]

You can't build filters that are more complex than simple expressions like "name = 'Miller'". That means: filters are f*cking useless with the PGDBE.
If you search the Alaska Knowledge Base for "PGDBE", you will find a lot of open PDRs concerning filters, and I don't believe they will ever get closed.
We are working on getting rid of all filters left.

We are talking about filters in PGDBE, not PGDBE in general. These are your words about filters in PGDBE, you can't deny. They show your great displeasure with them, and that they are not excellent at all. Diego said something similar to you.

When using Xbase++, I gradually replaced filters with Append From, as well as Set Scope.
With the Postgresql driver I definitely had to delete the SET FILTER that I had left. It does not work with 99% of the filters that are generally used, including filters that have dates.

Enlighten me, how you would lose performance by using LOCATE command? SKIP is a slow and primitive approach, and it is absurd to do that with large database, from top to bottom. You must use SEEK and LOCATE, that is the rule. They work many times faster than SKIP, in all DBEs and in all database models.

It does matter which way you go to Rome. If you don't care, the others do. In programming, the goal is the fastest way and the shortest time.

[SlavkoDam]

This is the solution how to create filter/WHERE expression with a date value in PgSql. I implemented that in my PowerSql library and it works excellent.

cDate = "'" + DTOS(dDate) + "'"

For example, if the date value is 31.03.2024, in German format, do this:

SET FILTER TO datefld >= '20240331'
DBSETFILTER ( {|| datefld >= '20240331'} )
SELECT ... WHERE datefld >= '20240331'

If you want to extract a year, month or day from datefld in the expression, you can use PgSql functions date_part() or extract().