I have to refer to my notes and rmd,eep,mac’s (I call the extension many
different things depending on what type class they are in.)
As for as the reload ‘Load’ It is done at the R prompt and prior to I change
the db name from org to org_bak or just a sample I could find. It does have
errors so.
DISCONNECT
SET MESSAGES ON
SET ERROR MESSAGES ON
SET MULTI OFF
SET ERROR VAR vul_Error
WRITE 'Checking database for errors...'
AUTOCHK NHI
IF vul_Error = 0 THEN
WRITE 'AUTOCHK successful - No errors found'
ENDIF; goto NoError
IF vul_Error > 40 THEN
WRITE 'AUTOCHK has found errors in the database! '
OUTPUT autochk.chk
AUTOCHK dbname FULL
OUTPUT SCREEN -- First check for most errors
Launch autochk.chk
ENDIF
Label NoError
CONNECT db_name
SET NULL -0-
SET TIME FORMAT hhmmss
SET VAR vul_time = (CTXT(.#TIME))
SET VAR vul_jdate = (CTXT(JDATE(.#DATE)))
SET VAR vul_pass TEXT = 'RBaseRocks'
SET VAR vul_unique TEXT = ('db_name' + (.vul_jdate + .vul_time))
SET VAR vul_out TEXT = (.vul_unique + '.all')
OUTPUT .vul_out encrypt .vul_pass
UNLOAD ALL
OUTPUT SCREEN
DISCONNECT
SET VAR vul_db1 TEXT = ('Rename db_name.R?? ' + .vul_unique + '.R??') --
Covers the RB’s & RX’s
&vul_db1
SET VAR vul_db2 TEXT = ('Run ' + .vul_out + ' ' + .vul_pass)
&vul_db2 -- need to trap any error messages in the future. Need to also
compare db SYS_Info (tables, views, etc..) to see what got lost.
CLEAR VAR vul_%
PAUSE FOR 5 USING 'Rebuild is Complete - Now going to Re-Index !' CAPTION
'Rebuild status' ICON confirm BUTTON 'Ok!'
disconnect
set multi off
conn db_name
Pack all
disconnect
Set Multi on
connect db_name
RETURN
If you leave this out in the open, everyone could know your password is
‘RBaseRocks’ so if it is in a form it is safe (compressed). Almost from all
prying eyes.
I would insert some code to ‘Halt on Errors’ or trap the error’s. I even
better like the idea of running this from a rff form and then switch between
db’s and store the results for reference.
Oh, if you have not used a rff form yet. Try it, it is cool. It runs on
its own. Like a menu without a db. When you use the menu you include a db
name and connect stamen and run. If the user is prone to stay at the menu (as
most do) they are attached to Nothing! But they think they are. Power glitch
No Problem!
Sincerely,
Paul D.
I know this is going to get updated and a few people are going to add to this.
I would and appreciate ALL the feed back I get. Good and Bad.
From: Ed Rivkin [mailto:[email protected]]
Sent: Tuesday, September 15, 2009 12:13 PM
To: [email protected]
Subject: Re: RE: [RBASE-L] - RE: Strange Update
Paul,
Thanks again for the feedback/assist. This is becoming a wonderful education.
Your note reminded me that part of
the 7.6 documentation is a DB Maintenance manual. In it is the unload/reload
scenario that I believe you are
referencing.
I tried unload/reload using the "problem child" db and found that R:base locked
on a record in my RentHist table
during reload. No messages...just locked up R:Base. On the screen the record
was displayed with a double entry
for the last three fields...two of which are null and the third is autonum'd
rowid.
Both the current Rent table and RentHist table have a few entries with null
values in DueDate. I know why they
are there...user enters without a Duedate on her own for a Misc entry. This
particular record wasn't one of them.
Per the others on the forum who chimed in, do you think I should eliminate this
practice with a rule on Duedate
to insure a value and instruct the user to always reference a Duedate when
adding a misc entry?
I am also intrigued and taking very seriously your comments about weekly
unload/reload as part of good DB maint.
Assume you have this in a .rmd or equivalent. Do you output to screen on
reload. What types of messages do
you generate to the user. In my case I am typically 45 miles from the computer.
Perhaps you are on-site and
doing this yourself....kind of curious as to your scenario and process. BTW,
this is very new to me but becoming
obvious that something we'll need to do. In the 4.5 days and 19 years of
running we had only a few issues with
DB quality and going to a BU was my course of action. I always BU the DB before
any major update onto disk
and a BU as the user leaves the system to media daily with rotating copies and
one stored off-site weekly.
Take care...and thank,
Ed
Sep 15, 2009 12:46:17 AM, [email protected] wrote:
Ed-
Did you try unload and then load as with Razzak example as mentioned on this
list? Good Db maintenance. It has been on the R-List several times. I am
to the point I prefer it and just do it as a weekly routine prior to a backup.
I encrypt the unload (safer than just putting a backup out there) and the added
bonus is if a error occurs I have notice of it right then. Then I reach for
RScope etc…. then I reach out for employees!
I would recheck the bad db (when you have time)and find the cause and insert
code to prevent it. Try Razzaks example - it works. I found table errors a
long time ago. I also just recent had a person somehow add bogus data into a
field. It was found during a upgrade to eXetrme 9.0 dbMain I was doing
Unload-Load. I was able to save the 7.6 before the damage was done. At first I
thought the update did it- just glad I kept it to myself and then nobody knew
till now.
As for looking for extra control char’ I was referring to a strong
editing/viewing program. One the allows you to see the ‘hidden’ char’s also.
RBeditor is a good start – a whole lot better than ‘NotePad’ and this was on
the load of all those extra lines. Great comparison option available in there
also. Any ?’s email me or on the R-List back in Nov-Dec as I recall. (It was
one of these that caused a near 7.6 crash I spoke of the other day, recall
Mike B. and others asking about blanks and nulls? When is a blank = to null=
‘-0-‘, etc…. or is it Alt-255 etc……? or a ‘space’)
I am glad you had a backup to fall back on. So many times they are not there.
If you do not have any back examples of Razzak’s Unload and Db maintenance
routine (fall of ’08)along with other examples that were posted here I am sure
one or ?????? (somebody could forward the copies of the emails for your
reference). Emmitt and some others (names escape me!) have expanded the
routine and added a few little extra parts. I prefer encryption in my script,
JM.02
Have a great day.
Paul D.
From: [email protected] [mailto:[email protected]] On Behalf Of Ed Rivkin
Sent: Monday, September 14, 2009 10:32 PM
To: RBASE-L Mailing List
Subject: [RBASE-L] - RE: Strange Update
Thanks to all for the good ideas and support.
Here's what I did this afternoon.
a) Eliminated the print report to disk/load to table scenario. The problem still
manifested itself with exactly the same # of records added to the Rent Table.
b) Switched to another b/u copy of the DB. I generated the 140 records several
times and it worked as desired; i.e. 140 records added to the Rent Table. I
am sticking with Emmitt's advice and using the new code going forward for this.
So it appears that the problem was corruption somewhere along the way. I am
still wondering why R:Scope didn't pickup anything. I also discovered that the
date errors I was getting reloading the entire Rent table after an unload
(Believe
this was Karen's input) was likely due to 6 records that didn't have Duedate in
them. My user apparently added some special charges without Duedate.
George, still not sure what you saw that I am missing relative to date format.
I set date
in my app startup as mm/dd/yyyy and believe that is what is displayed in the
report
to disk as well as loaded table. At this point it may be somewhat moot because
I replaced the report with a simple program that reads the tenant file for
accounts,
looks up rent in the rent level file and inserts a record for each tenant in
the Rent
table.
Again...thanks for everyone's assistance/support and any follow-on comments
and suggestions are most welcome.
Take care,
Ed
Sep 14, 2009 01:53:59 PM, [email protected] wrote:
Paul,
Here's some of the code from the .rmd with comments. I've
included the code just before the report print to disk, the
report print, and load to my Rentemp table and finally to
Rentroll itself.
BTW, I have been using R:Base Editor when viewing and
working with the .rmd file. I assume this will show me any
strange/extraneous characters.
----Move all zero balance accounts to Rent History
DECLARE c1 CUR FOR SELECT acct FROM balate WHERE baldue = 0
OPEN c1
---- Get 1st Row
FETCH c1 INTO compare INDICATOR i1
WHILE (SQLCODE <> 100) THEN
UPDATE rentroll SET rcode = 'U' WHERE acct = .compare AND NOT rcode = 'P'
---- Get next row
FETCH c1 INTO compare INDICATOR i1
ENDWHILE
DROP CURSOR c1
---- Move rentroll records to renthist and delete from rentroll
UPDATE rentroll SET bcode = 'D' WHERE bcode = 'B' AND rcode = 'U'
UPDATE rentroll SET bcode = 'E' WHERE bcode = 'C' AND rcode = 'U'
INSERT INTO renthist SELECT duedate acct rent SPECIAL postdate bankdate late +
bounce amrec rcode bcode lcode spchg FROM rentroll WHERE rcode = 'U'
---- APPEND rentroll TO spectemp WHERE rcode = 'U' AND SPECIAL > 0
DELETE ROWS FROM rentroll WHERE rcode = 'U'
---- PRINT THE MONTHLY RENTROLL REPORT TO DISK
PRINT genrent1 ORDER BY asrt ASC OPTION ETXT |FILENAME +
c:\RBTI\RBG76\Galaxy\genrent.gen |SHOW_CANCEL_DIALOG OFF
CLEAR ALL VARIABLES
---- LOAD THE RENTEMP TABLE
LOAD rentemp FROM genrent.gen USING duedate acct rent
APPEND rentemp TO rentroll where rent > 0
---- rentemp is reduced to a "shell file" and genrent is deleted
DELETE ROWS FROM rentemp WHERE duedate EXISTS OR duedate IS NULL
ERASE genrent.gen
First 5 records from the report
09/05/2009 01JC 534.84 0.00
09/05/2009 02JC 534.84 0.00
09/05/2009 03JC 534.84 0.00
09/05/2009 04JC 534.84 0.00
09/05/2009 05JC 534.84 0.00
First 5 records from the Rentemp table after being loaded as .csv
09/05/2009,73893,"01JC","J01","$534.84","",-0- ,-0- ,"","","","-0- ","-0-
","-0- ","$534.84","-0- ",-0-
09/05/2009,73893,"02JC","J02","$534.84","",-0- ,-0- ,"","","","-0- ","-0-
","-0- ","$534.84","-0- ",-0-
09/05/2009,73893,"03JC","J03","$534.84","",-0- ,-0- ,"","","","-0- ","-0-
","-0- ","$534.84","-0- ",-0-
09/05/2009,73893,"04JC","J04","$534.84","",-0- ,-0- ,"","","","-0- ","-0-
","-0- ","$534.84","-0- ",-0-
09/05/2009,73893,"05JC","J05","$534.84","",-0- ,-0- ,"","","","-0- ","-0-
","-0- ","$534.84","-0- ",-0-
First field is Duedate, next is calculated field from Duedate to use for
sorting, third field is account, fourth field
is calculated field from account to use for sorting, fifth field is rent,
calculated balance due is other numeric
field displayed.
Apparently my computer has been talking to computer. We now have two in the
community
adding data without our wishes and desires....:-)
Take care,
Ed
Sep 14, 2009 12:59:55 PM, [email protected] wrote:
Perhaps post a part of the code. Also if you read this file in RAW format
(not notepad but something showing ALL char’s) does it appear to be normal and
this is prior to the load/insert etc…
Are there extra CRCL’s or odd char’s. Instead of running this export to file
with a report, can you output as csv and see if it compares and looks about
the same? “When I print the “report” from the R> prompt to disk, 140 records
are generated.” Only assuming from your post this is from a report. You may
have meant script/code etc… ?
Just some thoughts.
Sincerely,
Paul D.
Usually my computer is the only one out there making files and lines on its
own.
From: [email protected] [mailto:[email protected]] On Behalf Of Ed Rivkin
Sent: Monday, September 14, 2009 1:30 PM
To: RBASE-L Mailing List
Subject: [RBASE-L] - RE: Strange Update
Apparently my initial post wasn't clearly written. I apologize for any
confusion.
The monthly table update of 140 entries is new data. I am generating a rent
record
for every tenant in my system. Each new record has Rent Due Date, Account, and
Rent Amount. We have 30 different rent levels so it isn't just adding a record
for
each tenant. This is why I write the 140 records as a report to disk and then
load the report back into a table.
When I print the report from the R> prompt to disk, 140 records are generated.
When I load the table from the R> prompt, 140 rows are generated in the table.
When I run the .rmd, I am getting over 41,000 records in the loaded table. The
records include records from my RentHistory table yet there is nothing happening
in the .rmd that moves records from RentHistory back to Current Rent.
I appreciate everyone's noodling on this. Any thoughts as to what I am still
missing....
Sep 14, 2009 09:03:55 AM, [email protected] wrote:
My thought that perhaps the loading table may have error and besides reading
every line prior to inserting into his main table. Just to unload it where it
is and see what happens on reload. If it fails there also I would tend to
believe it would fail everywhere.
I also find the process so fast and it takes any questions out of the equation.
KISS & JM.02
Sincerely,
Paul D.
-----Original Message-----
From: [email protected] [mailto:[email protected]] On Behalf Of MikeB
Sent: Monday, September 14, 2009 7:44 AM
To: RBASE-L Mailing List
Subject: [RBASE-L] - RE: Strange Update
>From his original post, it begs the question why is the data printed to disk
at all. Is there any sound reason this is not done as an INSERT / SELECT
since the data goes to a Master Table from a source table?
----- Original Message -----
From: "Alastair Burr" <[email protected]>
To: "RBASE-L Mailing List" <[email protected]>
Sent: Monday, September 14, 2009 3:07 AM
Subject: [RBASE-L] - RE: Strange Update
If you are doing the unload and then an immediate load are you sure that the
data has been written to disk and the file closed before the load is
attempted?
Regards,
Alastair.
----- Original Message -----
From: Ed Rivkin
To: RBASE-L Mailing List
Sent: Monday, September 14, 2009 2:09 AM
Subject: [RBASE-L] - RE: Strange Update
Anne,
Thanks for the suggestion. It was certainly behaving like a corrupted
table. I ran the DB through
R:Scope and it came through clean on both structure and data.
Emmitt, good point but it seems odd that a Disc / Copy / Conn would cause
what I am seeing.
Since putting in a pause for 30 secs after the Conn (did that after
posting my question), it
is working fine. As I am updating 3 tables in the .rmd, I just thought it
easier to keep everything
together rather than 3 unloads....
Ed
Sep 13, 2009 06:34:30 AM, [email protected] wrote:
Try running R:Scope on the database.
I did have something similar happen when updating an operations table.
Every time a record was updated a 2nd copy of the record would appear.
Found the customer table, which is a feeder table, had corrupted.
Reloaded the customer table from a backup copy and all has been running
fine.
-------- Original Message --------
Subject: [RBASE-L] - Strange Update
From: Ed Rivkin <[email protected]>
Date: Fri, September 11, 2009 11:04 pm
To: [email protected] (RBASE-L Mailing List)
I am curious if someone has faced a similar situation.
The problem revolves around a .rmd file originally
written for my application 20 years ago using r:base 4.0.
I update a table monthly with 140 entries; one for each
account. To do so, I print a report to disk, load it into a table
and append the table to my master table which is the current
Rent table. In addition all rows for accounts with a zero balance
are moved to the Rent History table and deleted from the Rent Table
Prior to the print / load / append the DB is disconnected
and the DB is copied so I have a recovery point in case
something fails.
The only changes from 4.0-4.5 - 7.6 are the screen handling messages.
After testing the conversion everything seemed to work fine. Running
parallel we are having a strange problem. Many of the current Rent
records are duplicated, others tripled and quadrupled and some of the
history data is finding it's way back into the Rent file.
When I commented out the Disconnect / Copy to Disk / Connect
everything worked fine again.
Am I hitting some sort of DB corruption issue? Should I put a
Pause for 30-45 secs after the connect? Or am I totally missing
something else.
Thanks as always,
Ed
------------------------------------------------------------------------------
No virus found in this incoming message.
Checked by AVG - www.avg.com
Version: 8.5.409 / Virus Database: 270.13.94/2367 - Release Date: 09/13/09
05:50:00
