Doug,
Thanks for the code, I’ll look it over along with the rest. I’m sure
glad I never remove autonum columns and forget to put them back.
Cough!!!
Have a great weekend,
Paul
From:[email protected]<[email protected]>
<mailto:[email protected]>On Behalf Of Doug Hamilton
Sent: April 23, 2021 1:47 AM
To:[email protected]
Subject: Re: [RBASE-L] - Suggestions on Using AutoCHK in a Command
File
Wow, lots of great stuff has been posted.
I'll offer the code below that checks the autonum cols in permanent
tables a db.
It makes sure all the expected autonum cols are in the db and that the
Next number is one more than the max value in the column.
It assumes autonum increments by one, but the increment can be read
from the temp view and incorporated into the calculation...maybe
tomorrow.
Output is to R>, change to write it to a file if you want a permanent
record.
It errs out if there are same-named autonum cols in different
permanent tables. I didn't try to trap that - it's bad design anyway.
It has a hard coded list of the autonum columns because...occasionally
if one is working on the structure they might remove autonum and not
put it back. "I mention this for a friend"...ahem.
Doug
###
--DBAutonumChk
--04/21/21:DHH:Autonum cols: check next system # vs Max in table
CLS
SET MESSAGE OFF
SET ERROR MESSAGE OFF
CLEAR VAR +
vAutoNumCols, +
vColName, +
vCount, +
vMaxID, +
vMsg, +
vSysMaxID, +
vTableName
SET VAR +
vColName TEXT, +
vCount INTEGER = 1, +
vMaxID INTEGER, +
vMsg TEXT, +
vSysMaxID INTEGER, +
vTimeBeg TIME = .#TIME
--List all the Autonum columns in the DB to be checked
--This makes a permanent record of them for future reference
SET VAR vAutoNumCols TEXT = +
('DBLog_ID, +
PIKD_ID, +
DISPX_ID, +
SHPI_ID')
--Temp view of table names, col names & default values
--Only Autonum cols are in SYS_DEFAULTS, not same-named non-autonum
cols
SET ERROR MESSAGE 677 OFF
DROP VIEW tSysColumns
SET ERROR MESSAGE 677 ON
CREATE TEMPORARY VIEW tSysColumns +
AS SELECT +
SYS_COLUMN_NAME,SYS_COLUMN_ID,SYS_DATA_TYPE, +
SYS_NEXT,SYS_INCREMENT, +
SYS_TABLE_ID,SYS_TABLE_NAME,SYS_NUM_COLUMNS,SYS_NUM_ROWS +
FROM +
SYS_COLUMNS T1, SYS_DEFAULTS T2,SYS_TABLES T3 +
WHERE +
T1.SYS_COLUMN_ID = T2.SYS_COLUMN_ID AND +
T1.SYS_TABLE_ID = T3.SYS_TABLE_ID
--Get the first autonum col to check from the list, strip extra spaces
SET VAR vColName = (SSTRIPI((SSUB(.vAutoNumCols,.vCount)),' '))
WHILE vColName IS NOT NULL THEN
--Get the system Next value and the table name
SELECT +
SYS_TABLE_NAME,SYS_NEXT INTO +
vTableName INDICATOR vi,vSysMaxID INDICATOR vi +
FROM tSysColumns +
WHERE SYS_COLUMN_NAME =.vColName
--If the colname is not found
IF SQLCODE <> 0 THEN
SET VAR vMsg = ('ERR: ' +.vColName & 'not found as AutoNumber
column.')
GOTO WriteMsg
ENDIF
--Now get the max value for the autonum col from the table
SELECT MAX(&vColName) INTO vMaxID +
FROM &vTableName
--Build the status text line
IF vSysMaxID <> (.vMaxID + 1) THEN
--Error if System Next value is not = col max value + 1
SET VAR vMsg = ('ERR: ')
ELSE
--Not an error
SET VAR vMsg = ('OK : ')
ENDIF
--Build & print the message
SET VAR vMsg = +
(.vMsg & .vColName & 'Column Max' & (CTXT(.vMaxID)) +
+ ', System Next' & (CTXT(.vSysMaxID)) )
LABEL WriteMsg
WRITE .vMsg
--Get the next autonum col from the list
SET VAR vCount = (.vCount + 1)
SET VAR vColName = (SSTRIPI((SSUB(.vAutoNumCols,.vCount)),' '))
ENDWHILE
SET ERROR MESSAGE 677 OFF
DROP VIEW tSysColumns
SET ERROR MESSAGE 677 ON
SET VAR vTimeEnd TIME = .#TIME
SHOW VAR vTime%
CLEAR VAR +
vAutoNumCols, +
vColName, +
vCount, +
vi, +
vMaxID, +
vMsg, +
vSysMaxID, +
vTableName, +
vTime%
RETURN
On 4/22/2021 8:46 AM, [email protected] wrote:
Good morning,
I’m running the latest build of R:BASE X.5 Enterprise and I’m trying
to incorporate the AutoCHK command to run in a scheduled task every
night before doing a “Pack in Place”. Anyone have experience with
this? Anyone will to share your code? Thought I’d ask that last one
😊
Thanks,
Paul Buckley
--
For group guidelines, visit
http://www.rbase.com/support/usersgroup_guidelines.php
---
You received this message because you are subscribed to the Google
Groups "RBASE-L" group.
To unsubscribe from this group and stop receiving emails from it,
send an email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/rbase-l/!%26!AAAAAAAAAAAYAAAAAAAAAJ4mzQXf2gpHhGgkNNfb5kTCgAAAEAAAAPzkRWmLe5JOt01PLYI1e4kBAAAAAA%3D%3D%40buckleyandassoc.com
<https://groups.google.com/d/msgid/rbase-l/!%26!AAAAAAAAAAAYAAAAAAAAAJ4mzQXf2gpHhGgkNNfb5kTCgAAAEAAAAPzkRWmLe5JOt01PLYI1e4kBAAAAAA%3D%3D%40buckleyandassoc.com?utm_medium=email&utm_source=footer>.
--------------------------------------------------------------------------------
Avast logo
<https://www.avast.com/antivirus>
This email has been checked for viruses by Avast antivirus software.
www.avast.com <https://www.avast.com/antivirus>
--
For group guidelines, visit
http://www.rbase.com/support/usersgroup_guidelines.php
---
You received this message because you are subscribed to the Google
Groups "RBASE-L" group.
To unsubscribe from this group and stop receiving emails from it, send
an email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/rbase-l/08891303-7485-fc83-a190-508a6327bb10%40wi.rr.com
<https://groups.google.com/d/msgid/rbase-l/08891303-7485-fc83-a190-508a6327bb10%40wi.rr.com?utm_medium=email&utm_source=footer>.
--
For group guidelines, visit
http://www.rbase.com/support/usersgroup_guidelines.php
---
You received this message because you are subscribed to the Google
Groups "RBASE-L" group.
To unsubscribe from this group and stop receiving emails from it, send
an email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/rbase-l/!%26!AAAAAAAAAAAYAAAAAAAAAJ4mzQXf2gpHhGgkNNfb5kTCgAAAEAAAAMZrwFoKnNFIvoanVMec%2BQgBAAAAAA%3D%3D%40buckleyandassoc.com
<https://groups.google.com/d/msgid/rbase-l/!%26!AAAAAAAAAAAYAAAAAAAAAJ4mzQXf2gpHhGgkNNfb5kTCgAAAEAAAAMZrwFoKnNFIvoanVMec%2BQgBAAAAAA%3D%3D%40buckleyandassoc.com?utm_medium=email&utm_source=footer>.
