Hi,

Firstly let me set the scene.

I have 2x identical logging type Access-based DBs
that have macros that perform maintenance type
functions. These macros also write status detail
to a table in the DB which we can then interogate to
get the precise run detail.

I have a JAL script which have a comma separated
string (i.e. that we split and interate over using
GetToken()). We have one "token" string for the
DB paths, and one for the 24x7 DB profile associated
with that DB.

Everything works fine, but the part of the script that
connects to the DB following the macro action and
retrieves the run status has a problem. It seems that
the second DaatbaseConnect actually connects to the
first database (even thought the 24x7 DB profile, and
it's related ODBC DSN are definately pointing to the
correct DB) as it gets a row count of 0 (you can see
the query in the following extract.

Any pointers to where I should be looking. I reversed the
order of the DBs and Profiles in my "token" strings
and it works correctly for the first and then has the
same problem for the second. I also tried a Wait(60) -
something I saw from another post - but to no avail.

CHECK_STATUS:

DatabaseConnect(DB_PROF)

ConcatEx("SELECT Successful, Comments FROM tblLock WHERE Action = '", JOB_ACTION, "' AND LockTime BETWEEN CDate('", JOB_START, "') AND CDate('", JOB_END, "')", SQL)

DatabaseRetrieve(SQL, COUNT)

IsGreaterOrEqual(COUNT, 1, SUCCESS)

If(SUCCESS, EXISTS, NOTEXISTS)

EXISTS:

DatabaseGet(1, 1, SUCCESSFUL)

DatabaseGet(1, 2, COMMENTS)

IsEqual(SUCCESSFUL, 1, SUCCESS)

Goto(DISCONNECT)

NOTEXISTS:

Set(COMMENTS, "Could not retrieve run status detail from database")

DISCONNECT:

DatabaseDisconnect

Any help is appreciated.

Peter

Do you call the DatabaseDisconenct first?

Now, if you call it, then check your ODBC profile settings and make sure all required parameters are entered so that the Access driver makes no guessing when opening database files.

Another tip, one JAL script can be connected to only one database at any given moment in time. However it is still possible to run multiple connections simultaneously just put different connections into different scripts. For instance, you can create different user-defined statements (Tools/Script Library menu) and call them from your main job script.

: Hi,

: Firstly let me set the scene.

: I have 2x identical logging type Access-based DBs
: that have macros that perform maintenance type
: functions. These macros also write status detail
: to a table in the DB which we can then interogate to
: get the precise run detail.

: I have a JAL script which have a comma separated
: string (i.e. that we split and interate over using
: GetToken()). We have one "token" string for the
: DB paths, and one for the 24x7 DB profile associated
: with that DB.

: Everything works fine, but the part of the script that
: connects to the DB following the macro action and
: retrieves the run status has a problem. It seems that
: the second DaatbaseConnect actually connects to the
: first database (even thought the 24x7 DB profile, and
: it's related ODBC DSN are definately pointing to the
: correct DB) as it gets a row count of 0 (you can see
: the query in the following extract.

: Any pointers to where I should be looking. I reversed the
: order of the DBs and Profiles in my "token" strings
: and it works correctly for the first and then has the
: same problem for the second. I also tried a Wait(60) -
: something I saw from another post - but to no avail.

: CHECK_STATUS: DatabaseConnect(DB_PROF)

: ConcatEx("SELECT Successful, Comments FROM tblLock WHERE Action =
: '", JOB_ACTION, "' AND LockTime BETWEEN CDate('",
: JOB_START, "') AND CDate('", JOB_END, "')", SQL)

: DatabaseRetrieve(SQL, COUNT)

: IsGreaterOrEqual(COUNT, 1, SUCCESS)

: If(SUCCESS, EXISTS, NOTEXISTS)

: EXISTS: DatabaseGet(1, 1, SUCCESSFUL)

: DatabaseGet(1, 2, COMMENTS)

: IsEqual(SUCCESSFUL, 1, SUCCESS)

: Goto(DISCONNECT)

: NOTEXISTS: Set(COMMENTS, "Could not retrieve run status detail from
: database")

: DISCONNECT: DatabaseDisconnect

: Any help is appreciated.

: Peter

I don't call DaatbaseConnect prior to the first DatabaseConncet, however there is a DatabaseDisconnect following the retrieval logic and obviously before the DatabaseConnect on the second iteration. Ideally I wanted to avoid multiple scripts doing the exact thing and that's why I went with just looping over the same logic with different MDB and DB Profile spec'ed.

: Do you call the DatabaseDisconenct first?

: Now, if you call it, then check your ODBC profile settings and make sure all
: required parameters are entered so that the Access driver makes no
: guessing when opening database files.

: Another tip, one JAL script can be connected to only one database at any
: given moment in time. However it is still possible to run multiple
: connections simultaneously just put different connections into different
: scripts. For instance, you can create different user-defined statements
: (Tools/Script Library menu) and call them from your main job script.

Please post your script.

: I don't call DaatbaseConnect prior to the first DatabaseConncet, however
: there is a DatabaseDisconnect following the retrieval logic and obviously
: before the DatabaseConnect on the second iteration. Ideally I wanted to
: avoid multiple scripts doing the exact thing and that's why I went with
: just looping over the same logic with different MDB and DB Profile
: spec'ed.

//*************************************************************
//*
//* Program Name: Strippit Logging: DB Backup
//* Written By..: Peter Ryder
//* Date Written: 15/11/04
//* Version.....: 0.01
//* Date........:
//* Purpose.....: Backup the DB to the location specified in tblSettings (this is just being cautious in case isn't backed up by Veritas due to it being in use)
//*
//* Notes.......:
//*
//* The database profiles Strippit1Logging and Strippit2Logging must be setup to point to the relevant ODBC DSN.
//* Note that 24x7 does not include a native driver for Access (if so I could have programmatically connected)
//* so I had to revert to this kludgy method.
//*
//* Revisions...:
//*
//*************************************************************

// File Locations

Dim ACCESS_EXE, String, "\"C:\\Program Files\\Microsoft Office\\OFFICE11\\MSACCESS.EXE\""

// Array
Dim DB_FILES, String, "\\\\BRBNESP2\\D$\\LOGGING\\STRIPPIT LOGGING.MDB,\\\\BRBNESP1\\D$\\LOGGING\\STRIPPIT LOGGING.MDB"
// These arrays are in reverse because the "Real" Strippit 1 is actually named BRBNESP2, and vice vera
Dim DB_PROFS, String, "Strippit1Logging,Strippit2Logging"

// Boolean

Dim SUCCESS, Boolean
Dim JOBSUCCESS, Boolean
Dim LOOP_FLAG, Boolean

// Strings

Dim DB_FILE, String
Dim DB_PROF, String

Dim JOB_START, String
Dim JOB_END, String
Dim JOB_ACTION, String, "DoBackup"
Dim JOB_MACRO, String, "macBackupDB"
Dim CMD, String
Dim SQL, String
Dim SUCCESSFUL, String
Dim COMMENTS, String

Dim TIME_STAMP, String
Dim DATE_STAMP, String
Dim E_SUBJECT, String
Dim E_BODYTXT, String
Dim E_TO_ADDR, String, "xxx@brenclosures.com.au"
Dim E_FR_ADDR, String, "xxx@brn.com.au"

Dim DB_FILES_TEMP, String
Dim DB_PROFS_TEMP, String

// Numbers

Dim COUNT, Number, 0
Dim PID, Number

//**********************************************************
//* Initialisation *
//**********************************************************

START_JOB:

Now(TIME_STAMP)

ConcatEx(E_BODYTXT, TIME_STAMP, ": \"@V"job_name"\" Started\n\n", E_BODYTXT)

RUN_MACRO:

Set(JOBSUCCESS, "True")

Set(DB_FILES_TEMP, DB_FILES)

Set(LOOP_FLAG, "False")

GetToken(",", DB_FILES_TEMP, DB_FILE)

IsEqual(DB_FILE, "", LOOP_FLAG)

// I am assuming that DB_PROFS will be correctly specified

Set(DB_PROFS_TEMP, DB_PROFS)

GetToken(",", DB_PROFS_TEMP, DB_PROF)

LoopUntil(LOOP_FLAG, END_LOOP)

FileExists(DB_FILE, SUCCESS)

If(SUCCESS, THEN3, ELSE3)

THEN3:

Now(TIME_STAMP)

Today(DATE_STAMP)

ConcatEx(DATE_STAMP, " ", TIME_STAMP, JOB_START)

ConcatEx(E_BODYTXT, TIME_STAMP, ": Macro \"", JOB_MACRO, "\" Started In \"", DB_FILE, "\"\n", E_BODYTXT)

ConcatEx(ACCESS_EXE, " /NOSTARTUP \"", DB_FILE, "\" ; ", JOB_MACRO, CMD)

RunAndWait(CMD, "", 0, PID)

Now(TIME_STAMP)

Today(DATE_STAMP)

ConcatEx(DATE_STAMP, " ", TIME_STAMP, JOB_END)

CHECK_STATUS:

DatabaseConnect(DB_PROF)

//DatabaseConnectEx("ODBC", DB_PROF, "", "", "", True)

ConcatEx("SELECT Successful, Comments FROM tblLock WHERE Action = '", JOB_ACTION, "' AND LockTime BETWEEN CDate('", JOB_START, "') AND CDate('", JOB_END, "')", SQL)

DatabaseRetrieve(SQL, COUNT)

IsGreaterOrEqual(COUNT, 1, SUCCESS)

If(SUCCESS, EXISTS, NOTEXISTS)

EXISTS:

DatabaseGet(1, 1, SUCCESSFUL)

DatabaseGet(1, 2, COMMENTS)

IsEqual(SUCCESSFUL, 1, SUCCESS)

Goto(DISCONNECT)

NOTEXISTS:

Set(COMMENTS, "Could not retrieve run status detail from database")

DISCONNECT:

DatabaseDisconnect

//IfThen(Not(SUCCESS), ENDIF2)

If(SUCCESS, THEN2, ELSE2)

THEN2:

ConcatEx(E_BODYTXT, TIME_STAMP, ": Macro \"", JOB_MACRO, "\" Complete In \"", DB_FILE, "\"\n\n", E_BODYTXT)

Goto(ENDIF2)

ELSE2:

Set(JOBSUCCESS, "False")

ConcatEx(E_BODYTXT, TIME_STAMP, ": Macro \"", JOB_MACRO, "\" Failed In \"", DB_FILE, "\"\n", E_BODYTXT)

ConcatEx(E_BODYTXT, TIME_STAMP, ": Failure Detail = ", COMMENTS, "\n\n", E_BODYTXT)

ENDIF2:

Goto(ENDIF3)

ELSE3:

Set(JOBSUCCESS, "False")

ConcatEx(E_BODYTXT, TIME_STAMP, ": File \"", DB_FILE, "\" Is Inaccessible \n\n", E_BODYTXT)

ENDIF3:

GetToken(",", DB_FILES_TEMP, DB_FILE)

GetToken(",", DB_PROFS_TEMP, DB_PROF)

// // Re-use variable
// IsEqual(DB_FILE, "", SUCCESS)
//
// IfThen(SUCCESS, ENDIF4)
// Wait(60)
// ENDIF4:

IsEqual(DB_FILE, "", LOOP_FLAG)

END_LOOP:

MAIL:

Now(TIME_STAMP)

ConcatEx(E_BODYTXT, TIME_STAMP, ": \"@V"job_name"\" Complete\n\n", E_BODYTXT)

If(JOBSUCCESS, SUCCEEDED, FAILED)

SUCCEEDED:

Set(E_SUBJECT, "\"@V"job_name"\" Successful")

Goto(ENDIF)

FAILED:

Set(E_SUBJECT, "\"@V"job_name"\" Failed")

ENDIF:

MailSend(E_FR_ADDR,"", E_TO_ADDR, E_SUBJECT, E_BODYTXT)

END:

: Please post your script.

Are you sure Strippit1Logging and Strippit2Logging database profiles in 24x7 refer to different ODBC profiles? If no, please fix them, otherwise try the following

1. In each database create a new table "test" with one column "id" as integer. In one database insert record with value 1 in another database insert record with value 2.

2. Create a temp JAL job in 24x7 with the following script

Dim(result, number)
DatabaseConnect("Strippit1Logging")
DatabaseRetrieve("SELECT id FROM test", result)
DatabaseGet(1, 1, result)
MessageBox(result)

3. Run this job and verify it connects to the right database. The message box should display "1".

4. If yes, change Strippit1Logging to Strippit2Logging in the job script and run it again. Does it connect to the same database or not?

5. If no, try the following script

Dim(result, number)
DatabaseConnect("Strippit1Logging")
DatabaseRetrieve("SELECT id FROM test", result)
DatabaseGet(1, 1, result)
MessageBox(result)
DatabaseDisconnect
DatabaseConnect("Strippit2Logging")
DatabaseRetrieve("SELECT id FROM test", result)
DatabaseGet(1, 1, result)
MessageBox(result)

Does it change the connection in this case? If yes the first message box should display "1" the second box should display "2"

: //*************************************************************
: //*
: //* Program Name: Strippit Logging: DB Backup
: //* Written By..: Peter Ryder
: //* Date Written: 15/11/04
: //* Version.....: 0.01
: //* Date........: //* Purpose.....: Backup the DB to the location specified
: in tblSettings (this is just being cautious in case isn't backed up by
: Veritas due to it being in use)
: //*
: //* Notes.......: //*
: //* The database profiles Strippit1Logging and Strippit2Logging must be setup
: to point to the relevant ODBC DSN.
: //* Note that 24x7 does not include a native driver for Access (if so I could
: have programmatically connected)
: //* so I had to revert to this kludgy method.
: //*
: //* Revisions...: //*
: //*************************************************************

: // File Locations

: Dim ACCESS_EXE, String, "\"C:\\Program Files\\Microsoft
: Office\\OFFICE11\\MSACCESS.EXE\""

: // Array
: Dim DB_FILES, String, "\\\\BRBNESP2\\D$\\LOGGING\\STRIPPIT
: LOGGING.MDB,\\\\BRBNESP1\\D$\\LOGGING\\STRIPPIT LOGGING.MDB"
: // These arrays are in reverse because the "Real" Strippit 1 is
: actually named BRBNESP2, and vice vera
: Dim DB_PROFS, String, "Strippit1Logging,Strippit2Logging"

: // Boolean

: Dim SUCCESS, Boolean
: Dim JOBSUCCESS, Boolean
: Dim LOOP_FLAG, Boolean

: // Strings

: Dim DB_FILE, String
: Dim DB_PROF, String

: Dim JOB_START, String
: Dim JOB_END, String
: Dim JOB_ACTION, String, "DoBackup"
: Dim JOB_MACRO, String, "macBackupDB"
: Dim CMD, String
: Dim SQL, String
: Dim SUCCESSFUL, String
: Dim COMMENTS, String

: Dim TIME_STAMP, String
: Dim DATE_STAMP, String
: Dim E_SUBJECT, String
: Dim E_BODYTXT, String
: Dim E_TO_ADDR, String, "xxx@brenclosures.com.au"
: Dim E_FR_ADDR, String, "xxx@brn.com.au"

: Dim DB_FILES_TEMP, String
: Dim DB_PROFS_TEMP, String

: // Numbers

: Dim COUNT, Number, 0
: Dim PID, Number

: //**********************************************************
: //* Initialisation *
: //**********************************************************

: START_JOB: Now(TIME_STAMP)

: ConcatEx(E_BODYTXT, TIME_STAMP, ": \"@V"job_name"\"
: Started\n\n", E_BODYTXT)

: RUN_MACRO: Set(JOBSUCCESS, "True")

: Set(DB_FILES_TEMP, DB_FILES)

: Set(LOOP_FLAG, "False")

: GetToken(",", DB_FILES_TEMP, DB_FILE)

: IsEqual(DB_FILE, "", LOOP_FLAG)

: // I am assuming that DB_PROFS will be correctly specified

: Set(DB_PROFS_TEMP, DB_PROFS)

: GetToken(",", DB_PROFS_TEMP, DB_PROF)

: LoopUntil(LOOP_FLAG, END_LOOP)

: FileExists(DB_FILE, SUCCESS)

: If(SUCCESS, THEN3, ELSE3)

: THEN3: Now(TIME_STAMP)

: Today(DATE_STAMP)

: ConcatEx(DATE_STAMP, " ", TIME_STAMP, JOB_START)

: ConcatEx(E_BODYTXT, TIME_STAMP, ": Macro \"", JOB_MACRO,
: "\" Started In \"", DB_FILE, "\"\n",
: E_BODYTXT)

: ConcatEx(ACCESS_EXE, " /NOSTARTUP \"", DB_FILE, "\"
: ; ", JOB_MACRO, CMD)

: RunAndWait(CMD, "", 0, PID)

: Now(TIME_STAMP)

: Today(DATE_STAMP)

: ConcatEx(DATE_STAMP, " ", TIME_STAMP, JOB_END)

: CHECK_STATUS: DatabaseConnect(DB_PROF)

: //DatabaseConnectEx("ODBC", DB_PROF, "", "",
: "", True)

: ConcatEx("SELECT Successful, Comments FROM tblLock WHERE Action =
: '", JOB_ACTION, "' AND LockTime BETWEEN CDate('",
: JOB_START, "') AND CDate('", JOB_END, "')", SQL)

: DatabaseRetrieve(SQL, COUNT)

: IsGreaterOrEqual(COUNT, 1, SUCCESS)

: If(SUCCESS, EXISTS, NOTEXISTS)

: EXISTS: DatabaseGet(1, 1, SUCCESSFUL)

: DatabaseGet(1, 2, COMMENTS)

: IsEqual(SUCCESSFUL, 1, SUCCESS)

: Goto(DISCONNECT)

: NOTEXISTS: Set(COMMENTS, "Could not retrieve run status detail from
: database")

: DISCONNECT: DatabaseDisconnect

: //IfThen(Not(SUCCESS), ENDIF2)

: If(SUCCESS, THEN2, ELSE2)

: THEN2: ConcatEx(E_BODYTXT, TIME_STAMP, ": Macro \"",
: JOB_MACRO, "\" Complete In \"", DB_FILE,
: "\"\n\n", E_BODYTXT)

: Goto(ENDIF2)

: ELSE2: Set(JOBSUCCESS, "False")

: ConcatEx(E_BODYTXT, TIME_STAMP, ": Macro \"", JOB_MACRO,
: "\" Failed In \"", DB_FILE, "\"\n",
: E_BODYTXT)

: ConcatEx(E_BODYTXT, TIME_STAMP, ": Failure Detail = ", COMMENTS,
: "\n\n", E_BODYTXT)

: ENDIF2: Goto(ENDIF3)

: ELSE3: Set(JOBSUCCESS, "False")

: ConcatEx(E_BODYTXT, TIME_STAMP, ": File \"", DB_FILE,
: "\" Is Inaccessible \n\n", E_BODYTXT)

: ENDIF3: GetToken(",", DB_FILES_TEMP, DB_FILE)

: GetToken(",", DB_PROFS_TEMP, DB_PROF)

: // // Re-use variable
: // IsEqual(DB_FILE, "", SUCCESS)
: //
: // IfThen(SUCCESS, ENDIF4)
: // Wait(60)
: // ENDIF4: IsEqual(DB_FILE, "", LOOP_FLAG)

: END_LOOP: MAIL: Now(TIME_STAMP)

: ConcatEx(E_BODYTXT, TIME_STAMP, ": \"@V"job_name"\"
: Complete\n\n", E_BODYTXT)

: If(JOBSUCCESS, SUCCEEDED, FAILED)

: SUCCEEDED: Set(E_SUBJECT, "\"@V"job_name"\"
: Successful")

: Goto(ENDIF)

: FAILED: Set(E_SUBJECT, "\"@V"job_name"\"
: Failed")

: ENDIF: MailSend(E_FR_ADDR,"", E_TO_ADDR, E_SUBJECT, E_BODYTXT)

: END: