Re: [RBASE-L] - Suggestions on Using AutoCHK in a Command File

Doug Hamilton Fri, 23 Apr 2021 09:20:23 -0700

Now /The Friend /suggests I write something to check for PK and FK columns.
Will the guy ever get his act together?!
D  :)


On 4/23/2021 7:41 AM, [email protected] wrote:

Doug,
Thanks for the code, I’ll look it over along with the rest. I’m sureglad I never remove autonum columns and forget to put them back. Cough!!!
Have a great weekend,

Paul
*From:* [email protected] <[email protected]> *On BehalfOf *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 permanenttables a db.It makes sure all the expected autonum cols are in the db and that theNext number is one more than the max value in the column.It assumes autonum increments by one, but the increment can be readfrom 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 permanentrecord.It errs out if there are same-named autonum cols in differentpermanent tables. I didn't try to trap that - it's bad design anyway.It has a hard coded list of the autonum columns because...occasionallyif one is working on the structure they might remove autonum and notput 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 AutoNumbercolumn.')
    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]<mailto:[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
    <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]
    <mailto:[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, visithttp://www.rbase.com/support/usersgroup_guidelines.php<http://www.rbase.com/support/usersgroup_guidelines.php>
---
You received this message because you are subscribed to the GoogleGroups "RBASE-L" group.To unsubscribe from this group and stop receiving emails from it, sendan email to [email protected]<mailto:[email protected]>.To view this discussion on the web visithttps://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, visithttp://www.rbase.com/support/usersgroup_guidelines.php<http://www.rbase.com/support/usersgroup_guidelines.php>
---
You received this message because you are subscribed to the GoogleGroups "RBASE-L" group.To unsubscribe from this group and stop receiving emails from it, sendan email to [email protected]<mailto:[email protected]>.To view this discussion on the web visithttps://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>.


--
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/083a608a-52f1-3c38-93b1-8165ecd33e4f%40wi.rr.com.

Re: [RBASE-L] - Suggestions on Using AutoCHK in a Command File

Reply via email to