Par2: SQL tips -- Why not to let Clarion do certain things for you.
1998-10-07 -- Jack Toering
>- I currently have calculated fields in the DB which are perfectly
maintained by the front-ends. Is this also doable in a replication
situation?<
Doable, but I wouldn't do it. Unless there are a lot of complex multi-record lookups with
business math, it's awefully easy to do with the back end. It requires a trigger, which
btw is no big deal. If you are doing a lot of work at the embed, "before saving record
to disk" You may want to look at if it would be better off done on the server. Then you
would be getting into a trigger. The test for me is this. If it has to happen every
time,
and especially if the data is inaccurate or makes no sense without it being performed,
then move it to the server. If it fits the definition of a rule, it belongs with the
data.
I realize that you don't want to re-architect everything, but a couple of things you will
have to face whether you like it or not if you go to any RDBMS:
1. You HAVE to have at least one unique index for each table, and the RDBMS
and Clarion has to know about it. True, you can make it without, but wait
until you try to maintain a record. Desktop databases can resolve this
situation with a record number. RDBMSes don't have record numbers.
2. You may not have your data related in the optimal manner for an RDBMS.
If you are holding things together with strings etc., then at least you'd better have
something like DataModeler in C5EE to show you all of the areas where key values
are not the same type or length. You can get away with that on a desktop, but in
an RDBMS, it can get wierd on you. Make sure when you make the database, that
you say to ignore trailing blanks in comparisons also. You CAN do it from the Clarion
end if you wish, but that's a lot shakier than letting the RDBMS doing it. If you are
crossing databases, you have not choice but to have Clarion do it and framing is
also out then.
3. Don't assume that your code is ready to go because it comes up and runs
and you can browse your records. This is an area that I disagree strongly
with Topspeed. The Clarion VIEW on a desktop database does not act like the
Clarion VIEW when working with an RDBMS.
- When working with an RDBMS, only the fields PROJECTed in the VIEW are reliable
in the buffer. The others are what is termed, undefined. Furthurmore, if no fields are
PROJECTed, it fetches the entire record. If you simply add a filter to an existing
procedure to where you have to declare one of the record members HOT, you now are
PROJECTing a field. This means that you now lose all of the other fields that you had.
The embeds may have been depending on that code. So you must seach through
the embeds, and declare every variable HOT that you are using. Don't miss any.
If you do, you may be working with a variable that was not declared HOT, but
since the left over variable is in the record buffer from some other operation, it is used
in your calculation.
- When working with a desktop database, all of the fields are valid in the record buffer,
regardless of what fields are PROJECTed in the VIEW. E.G. the desktop database
gets the entire record whether or not the fields are in the VIEW.
Explanation: What this means is that your .app may be full of bugs that were hidden
by the "fortuitous" or "unfortuitous" "behavior" or "misbehavior" of the desktop VIEW.
You have some debugging to be done. You would have found these bugs before in
development IF these VIEWs worked the same. Now instead of one procedure, you
have many to debug, and it's no longer fresh in your mind. Fields that were previously
available in the buffer are suddenly unreliable. They are left overs from other records
that were last updated by a previous VIEW and/or a FORM. You could have a mix of
many records in the buffer.
Now you know the pitfalls. The ones that are the trickiest are the process procedures.
The browse takes care of itself in MOST cases, but check your embeds for calculated
non-browse fields. The FORM is no issue as it doesn't use a VIEW. The REPORT is
not often an issue unless you are doing some calculations appart from the visible fields
on the report. But any procedure that uses a VIEW is vulnerable.
Printed May 2, 2024, 3:39 am
This article has been viewed/printed 35127 times.