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]> 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] <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 --- 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> . _____ <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] <mailto:[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.
