How to use PGDBE in CXP programs

[Tom]

It is not noticeable for small databases (up to few 100k records total), but it shows weakness on everything larger.

This is not true in all situations.

First, some service functions used in ISAM-mode are (still) catastrophically slow, like DbPack() or any operations creating new indexes. Upsizing takes it's time, but Alaska is working on that both.

However, if the upsizing is done and the PGDBE is used in ISAM-mode without much changes in the code (except the ones needed and some workarounds for filters), the database performance will increase on local networks, and it will not slow down with more workstations added. We can confirm that for tables even with more than 100k records for all navigational functions, when it comes to seeks, if scopes are used/established and even with locates. Appending and updating records may take a little more time than with FOX or NTX. We don't have millions-of-records-situations, but quite large applications and table numbers.

If the monkey work is done, PGDBE is great. And since the rumours say, SAP will stop selling the ADS in 2023, it's the only way - if you don't want to rewrite tons of code.

[Diego Euri Almanzar]

Hello Mj, (K-Insis)

I totally agree with everything you tell me. Also, I greatly admire your domain and experience as a developer.

I have been developing a billing and accounting system since 1989. I started with DBASE III, then CLIPPER, and finally Alaska. It is very complex when a system is very large, and is full of ISAM instructions (seek, found(), locate, Set Scope, etc), to switch to SQL. It's like making a system all over again. And, many times, there is no time for that. Like me, there are many developers who fall into this trap of time, lack of time, and outdated.

And we have no other choice but to use interfaces, such as the PGDBE driver. As the trend now is the Web, I had to start a new project with CXP. But, the advantage is that I can offer my desktop system, while showing some basic options in CXP, such as quotes, invoices, customer creation, etc. From my point of view, it is amazing how CXP can combine Xbase++ code, with HTML, or JavaScript, as shown below:


<script type="text/javascript">

function HideAllCode(){
$('[id|="code"]').hide();
}

$(function(){

$('#tabs').tabs();

HideAllCode();
<%
nCnt := 0
FOR n:=1 TO CategorySorter():NumCategories()
aSamples := CategorySorter():GetAllByCategory(n)
FOR m:=1 TO Len(aSamples)
nCnt++

?? [$("#tip-]+StrZero(nCnt,4)+[").click(function () {]
?? 'HideAllCode(); $("#code-'+StrZero(nCnt,4)+'").slideDown(500); return true;});'

NEXT m
NEXT n
%>
})
</script>



However, it is deplorable and unusual that a driver has not been included to eliminate the obsolescence of the DBF.

I am very grateful.

Best regards.

[Diego Euri Almanzar]

Hello Tom

your words are hopeful, also your experience is noted, and great domain as a developer, thank you.

In a previous comment, I wrote about the examples sent to me from Alaska, regarding Set Filters.


Another thing, Tom, and excuse me for such a silly question, if I have 4 workstations, in a local network, should I create 4 users in POSTGRESQL, that is, one user for each workstation, or should I only create one user in POSTGRESQL for all workstations?


Best regard.

[Tom]

Hi, Diego.

Thanks for sharing the filter issue solutions. This (#1) is what we do already, but it's not even second best, and #2 is no solution since the code supports all three engines at the same time (FileDb, ADS, PG). We created another way which works for really complex filters, but only on small tables. We created a function "BuildSqlFilter" which is wrapped around "DbSetFilter". If PGDBE is not used, this function just returns the filter codeblock. If PGDBE is used, it travels through the table using DbEval() and evaluates the filter expression, which works, no matter how complex the expression is, even with PGDBE. The evaluation collects the record numbers (__RECORD) and creates a filter expression using those row numbers. As I mentioned, this workaround works with any kind of filter, but only on small tables (< 1k) and it travels through the table before the filter is set, so it slows down a little. But it takes the least additional amount of code changing time, and since we mainly use filters on small tables.

Besides, if you try to set up something on the web, you may take a closer look at the BOA project from Chris.

[Diego Euri Almanzar]

Hello Tom

I know you are a very advanced developer and I have learned a lot from your suggestions. I understand that you no longer need these PGDBE examples. However, here I copy, exactly as Alaska Software answered my email, just as a general culture, and in case someone is interested in a "solution" to the filters. Even further down, you will find a link, where they have examples of complex filters that include functions like at(), and substr(), inside.

1. I'm not an expert programmer, as most in this group are, I don't have that much experience. So I'll ask you a silly question, hopefully you can help me: if I have 4 workstations, in a local network, should I create 4 users in POSTGRESQL, that is, one user for each workstation, or should I only create one user in POSTGRESQL for all workstations?

2. As for BOA, I know it's a great thing, very functional, and very sophisticated. But, I'm already in CXP, I feel comfortable, and I can combine HTML + xbase, JavaScrip + xbase, I can use all the libraries for HTML, which make the views of my new project very modern. I'm not old enough to start all over again. And, when I thought that there was no longer a solution for me, that I would become obsolete, I learned the CXP technique. Which, by the way, is very simple, but there are moments in life when one feels confused, with a cloudy brain, and disinterested help is very scarce.

I'm not an expert programmer, but from my point of view, CXP is great. Except, the use of DBF, that is depressing. I left the DBE test for last, because Alaska Software support informed me in an email that PGDBE worked correctly with CXP, but that FOXDBE had to be left, because with FOXDBE the CXP handled the cookies. My support expired, but when I reactivate my support with Alaska, I will ask them why they promised me something that does not exist, or to teach me how to do it.


Alaska Software Response:

- If you're using local or private variables in your filters, make sure the variable's values are included in the filter expression as literals. Example:

cb := "{|| DCMONTORIG >= " + Str(mont1) + ".AND. DCMONTORIG cb := &cb
DbSetFilter( cb )

- Make sure your filters use valid SQL syntax. If you're using functions in your filter expressions, make sure these are available under SQL. More information is available here:

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

- Consider using an SQL query statement instead of the SET FILTER TO command. This will be much faster because SQL servers are optimized for this type of processing. Example:

oStmt := DACSqlStatement():fromChar( "select dccodigo, dcfechdocu, dcmontorig from maefactu where dcmontorig >= ::mont1 and dcmontorig oStmt:mont1 := mont1
oStmt:mont2 := mont2
oStmt:build():query()

[skiman]

Hi Diego,

I have been using Xbase++ in HTML and Javascript since 15 years, before cxp even existed. The biggest challenge was the navigation. I don't know if this is easy with cxp.

Hereby a sample of a HTML file for browsing customers I used in the past. You can see that there is Xbase++ code in it. Instead of using the <% and %>, I used <[ and ]>. I couldn't use a for next loop, so I implemented a <p><!-- [start] --></p> and <p><!-- [stop] --></p> to define a loop.

With this technique I developed a webversion for my invoicing software. It was a good solution 15 years ago. It was even responsible.

Since everything evolves very fast, I started 4 years ago with the BOA project. With BOA you don't need to use HTML or Javascript to build a web application.

Code: Select all

<!-- rows 7 -->
<!DOCTYPE html>
<html>
	<head>
		<title>ABO Remote - Customers</title>
		<link rel="stylesheet" href="www_root/Style.css" />		
		<link rel="shortcut icon" href="http://aboservice.be/favicon.ico" type="image/x-icon" />
		<meta charset="UTF-8">
		<script type="text/javascript" src="www_root/iscroll-min.js"></script>
		<script type="text/javascript">
			var myScroll;
			var a = 0;
			function loaded() {
				setHeight();
				myScroll = new iScroll('scroller', {desktopCompatibility:true});
			}
			function setHeight() {
				var headerH = document.getElementById('header').offsetHeight,
					footerH = document.getElementById('footer').offsetHeight,
					wrapperH = window.innerHeight - headerH - footerH;
				document.getElementById('wrapper').style.height = wrapperH - 30 + 'px';
			}
			window.addEventListener('onorientationchange' in window ? 'orientationchange' : 'resize', setHeight, false);
			document.addEventListener('touchmove', function (e) { e.preventDefault(); }, false);
			document.addEventListener('DOMContentLoaded', loaded, false);
		</script>
	</head>
<body>
<div id="header">
	<form name=header method=POST action="files">
	<input name="_SID" type=hidden value="_ID_">
	<input name="file" type=hidden value="klant">
		<!-- <table class="menu" > -->
		<table cellspacing="0" cellpadding="0" border="0" width="100%" class="menu">
			<tr>
				<td style="padding-left:5px;">
					<input name="actie.pageup" class="formbutton button-a" type="submit" value="  <  ">
				</td>
				<td style="padding-left:5px;">
					<input name="actie.pagedown" class="formbutton button-a" type=submit value="  >  ">
				</td>
				<td style="padding-left:5px;">
					<input name="actie.init" class="formbutton button-a" type=submit value=" Search ">
				</td>
				<td style="padding-left:5px;">
					<input name="actie.insert" class="formbutton button-a" type=submit value=" New ">
				</td>
				<td style="padding-left:5px;padding-right: 5px;">
					<input name="actie.menu" class="formbutton button-b" type=submit value=" Menu ">
				</td>
			</tr>
		</table>
	</form>
</div>
<div id="wrapper">
	<div id="scroller">
		<div id="content">
<p><!-- [start] --></p>
	<div class="block">
		<div class="inner_block" onclick="javascript: document.location.href = 'files?actie=edit&file=klant&record=<[klant->(recno())]>';">
			<table id="table">
				<tr>
					<td colspan="2">
						<span style="font-size: 0.8em;font-weight:bold;"><[klant->naam]></span>
					</td>
					<td align="right" colspan="1">
						<span style="font-size: 0.8em;"><[klant->email]></span>
					</td>
					
				</tr>
				<tr>
					<td style="vertical-align:middle;padding-right:5px; width: 80px;" rowspan="2">
						<img src="www_root/images/seek.png" border="0">
					</td>
					<td>
						<[klant->adres]>
					</td>
					<td align="right" >
						<span style="font-size: 0.8em;">GSM: <[klant->gsm]></span>
					</td>
				</tr>
				<tr>
					<td>
						<[postnrs->(dbseek(klant->newpost)), klant->postnr + postnrs->plaats]>
					</td>
					<td align="right">
						<span style="font-size: 0.8em;">Phone: <[klant->telefoon]></span>
					</td>
				</tr>
			</table>
		</div>
	</div>
<p><!-- [stop] --></p>
		</div>
	</div>
</div>
<div id="footer" style="display:none;"></div>
</body>
</html>

[Tom]

Hi, Diego.

I don't believe that there is a remarkable gap between your and mine programming experiences/skills, but thanks for the compliments!

There a several ways to deal with the filter problem in PGDBE:

1. Avoid filters. If needed, move the filter to a loop where the filter expression is evaluated, since this is done locally (and works with any kind of expression), while the PGDBE converts the filter expression to a WHERE-statement: DO WHILE !EoF() ; IF Eval(bFilterExpression) ; ... ; DbSkip(1) ; ENDDO

2. Rewrite all filters so they can be used. Take out symbols like #, ! and .T./.F. (the PGDBE even doesn't understand DbSetFilter({||.T.})). Translate all UDFs und Xbase++-functions to stored procedures.

3. Use SQL statements instead, use the ability of that to provide dynamic iVars. But this will kill compatibility and/or increase complexibility, if your app needs to support more DBEs than PGDBE with the same code.

4. Reconstruct filter codeblocks in a way that they don't contain vars and other expressions, but only values. With this, filters will not be dynamically anymore, so the filter codeblocks need to be rewritten with every change of data/information (how do you get that done?).

As I mentioned above, I'm working on a solution that is a mix of strategies. My suggestion to Alaska was to provide something the ADSDBE already has: If a filter expression can't be evaluated on the server side, there is a fallback to evaluation on the client side. But I'm afraid they still didn't find a way to find out whether a filter can be used or not. They are stuck with this.

Concerning CXP: I was suprised to hear that CXP doesn't support PGDBE out of the box. I'm quite sure they will fix that immediately. Both are core technologies.

[skiman]

Hi Tom,

Thanks for the post of the way you work with filters.

I understand that working with dynamically created filters is a problem or difficult. I'm using a lot of scopes in my application, I suppose this isn't a problem?

About CXP and PGDBE, I was also very surprised the combination isn't working. As you wrote, both are core technologies of Xbase++.

[Tom]

Hi, Chris.

Scopes and everything else work excellent, no problems.

[k-insis]

> if I have 4 workstations, in a local network, should I create 4 users in POSTGRESQL,
> that is, one user for each workstation, or should I only create one user in POSTGRESQL for all workstations?


On user creation on @postgresql database

Generally , dbadmins will do unspeakble things to you if you will need separate SQL user for each real life user of a application when that number is more than a few.

Mind that is not problem with four users on your own network but it becomes issue with large installs with a lot of users in shared environments where single postgresql runs many databases concurently while where you as developer will be denied access to production servers due to security/GDPR and simililliar requirements.

Easiest solution is to have single SQL user for all sql work and then manage user permissions from inside server or fat-client side application depending on login and its atributes in separate table which is part of you app database . Application connects via proper sql user/pass via secure channel to database internally.

Hello Tom

your words are hopeful, also your experience is noted, and great domain as a developer, thank you.

In a previous comment, I wrote about the examples sent to me from Alaska, regarding Set Filters.


Another thing, Tom, and excuse me for such a silly question, if I have 4 workstations, in a local network, should I create 4 users in POSTGRESQL, that is, one user for each workstation, or should I only create one user in POSTGRESQL for all workstations?


Best regard.