How to use PGDBE in CXP programs

[Tom]

On user creation on @postgresql database

We suggest one high level database user for all. The data of this user is not know to the application users, since it's encrypted somewhere in the application settings (by the admin). The disadvantage of this is that all the information you may gather from the server (transaction management) is only about/for this user.

[skiman]

The disadvantage of this is that all the information you may gather from the server (transaction management) is only about/for this user.

Hm, this means that logfiles can't show who has done something? However maintaining a logfile table the same way as with a dbf table can still be done. The user as known in the Xbase application can be used, no matter if there is only one user for the SQL database.

[Tom]

However maintaining a logfile table the same way as with a dbf table can still be done.

Of course.

[Diego Euri Almanzar]

Hello Chris

I used to refuse to write in HTML, because I find it clumsy, it doesn't allow creating related loops with the repetition of content, nor does it set variables. It also does not have functions, or blocks of code that execute operations. That is, HTML lacks the essential features of programming languages. I spent many years waiting for a pseudo-language that would save me from writing HTML, even if it produced an HTML output behind the scenes. I think BOA is that great language, but I am already very advanced in CXP, and I have even grown fond of it.

I already love CXP as much as the Express library. Both have been my salvation in decisive and important moments of my life. Also, for WEB beginners like me, CXP is very appropriate and functional. It brings with it the curse of the DBF, but as I said before, for beginners it is very good.

Thank you very much.

[Diego Euri Almanzar]

Hello Tom

Excellent explanation, just what I needed.

Thank you very much.

[SlavkoDam]

Hello!

Diego: It is very complex when a system is very large, and is full of ISAM instructions, to switch to SQL. It's like making a system all over again.
Tom: If the monkey work is done, PGDBE is great. It's the only way - if you don't want to rewrite tons of code.

This is not true if you use PowerSql library. Here is the list of common DBF functions and their PowerSql counterparts:

DbUseArea() => DsUse(), DsOpen()
DbCloseArea() => DsClose(), DbCloseAll() => DsCloseAll()
Alias() => DsAlias(), Select() => DsNum(), DbSelectArea() => DsSel()
DbStruct() => DsStruct()
DbAppend() => DsIns(), REPLACE => DsUpd(), DbDelete() => DsDel(), DsRem()
INDEX => DsIndex()
DbSeek() => DsSeek(), DbLocate() => DsLoc(), DbContinue => DsCont()
DbSkip() => DsSkip(), DbGoTo() => DsGoTo(), DbGoTop() => DsGoTop(), DbGoBottom() => DsGoBtm()
Bof() => DsBof(), Eof() => DsEof()
DbCargo() => DsCargo()
DbSetRelation() => DsSetRelats(), DbClearRelation() => DsClrRelats()
DbRLock() => DsRLock(), DbRLockList() => DsRLockList(), DbRUnlock() => DsRUnlock()
DbRefresh() => DsRefRec(), DsRefrAll()
DbEval() => DsEval(), DsSetBlock()
DbExport() => DbCopy(), DbImport() => DsGetData(), DsGetRecs(), DsGetCols()
RecCnt() => DsRecCnt(), RecNo() => DsRecPos()
FieldPut() => DsSetFld(), FieldGet() => DsGetFld()
FCount() => DsFldCnt(), FieldName() => DsFldName(), FieldPos() => DsFldPos()
FieldInfo() => DsCols(), DsColType(), DsColLen(), DsColDec()
OrdKey() => DsKeyExp(), DsKeyCols()

Unlike SqlExpress, PowerSql don't deal with objects at all. As you see, its functions are straight forward and very similar to corresponding DBF functions, and its very easy and fast to replace them. Its not a great work and you don't have to make a new system. Only you don't have to be lazy to do that, it can't be done by itself.

I believe many of you have begun with Clipper, as me. And when you had to move from DOS to Win, you had to learn new language Xbase++ and to rewrite tons of your code, to make it work in Win. Now you want to move from DBF to SQL, but refuse to learn SQL and rewrite your code again. Why? Do you become so old and so lazy, to improve your knowledge and your apps? Diego, you have spent many months on useless work that did not and will not give you expected results, and you constantly comes to new problems that could not be resolved. You could use that time in a better way. Tom, to remind you, you are a human programmer, not a monkey. No offence, please, I respect you all.

I don't think that PGDBE is a good solution at all. Its not natural to use ISAM in SQL, because they don't work in the same way. I don't think that Alaska would ever fix PGDBE support in CXP. I don't believe that they are not aware of that unsupport. Did they give any CXP example for PGDBE? NO.

[Tom]

Hi, Slavko.

I don't think that PGDBE is a good solution at all.

And I think it's an excellent solution. We managed to migrate a really, really big application to PostgreSQL with the PGDBE. It runs perfect, it has a very good performance, there are only a few issues left. I understand your argumentation, but in my humble opinion, you're wrong.

Here is the list of common DBF functions and their PowerSql counterparts:

I'm missing everything around filters, scopes and other stuff. Anyway, the PGDBE is perfect to me. And others. But if there are different ways to get lucky - good for all!

[SlavkoDam]

Hello!

Alaska ODBCDBE has many shortcomings, including the following:

- it doesn't support different date formats for different DBMSs,
- it doesn't support different image binary formats for different DBMSs,
- it doesn't support auto-increment (sequential) data type for many DBMSs, such as PGSQL, MySQL, Oracle,
- it doesn't support unicode (wide character) data types.

PowerSql library has none of these problems. PowerSql can create both ODBC and ADO connections. Its functions are straight forward and its very simple to replace DBF functions with them. PowerSql is much faster than PGDBE, since it uses ODBC directly, and there are no ISAM to SQL transformations and maintaining.

[SlavkoDam]

Hi, Tom!

I'm missing everything around filters, scopes and other stuff.

In my beginning Clipper days, I tried DBF filters. I realized that for large DBFs its very slow and unefficient, I dropped that, and never used later. So, in my DBF apps I never use filters/scopes. I use index-sequential search, instead, like this:

SEEK seek_exp, or LOCATE FOR loc_exp
IF FOUND()
DO WHILE EVAL(while_cond) .AND. !EOF()
IF EVAL(for_cond)
... process code
ENDIF
SKIP
ENDDO
ENDIF

This is the same as SQL SELECT command do. This method is extremely fast and gives the best results. PowerSql DsCopy() function and PowerWin DbCopy() functions, are useful wrappers for the above code, to retrieve data.

There are no similar commands to filters, in SQL. So, there are no filters support in PowerSql, since filters doesn't exist in SQL language.

[Tom]

A WHERE clause is something like a filter, and your construction does the same a filter does on a table - filter expressions are evaluated with every navigational operation, and records not matching a filter are skipped. In addition, if you use "SMARTFILTER ON", the workarea object collects the record number of the records matching a filter, so the expression must not be evaluated with every move anymore. Anyhow - fine if you don't use filters or scopes in your applications, but we do, and lots of applications out there are based on those techniques. And the PGDBE is an excellent way to reuse code based on that, while this also gives the chance to move forward without building a whole data model. If I want, I can use SQL directly or a mix or whatever I want. Migrating to PGDBE is just the first step.