MSSQL connection status check

[Pascal Siekman]

Hello,

I’m using a MSSQL db for XLT’s. Is there a way to check is de DB is online? And if not wait till it is online again.

Greatings,

Pascal Siekman

Topic

[Jim Kosloskey]

It has been a while but try this:

odbc SQLGetConnectAttr $hdbc SQL_ATTR_CONNECTION_DEAD dbv 100 cnt]

Check the value in the “dbv” variable.  If it comes back as “SQL_CD_TRUE” then you don’t have a connection.  If it comes back as “SQL_CD_FALSE” then your connection is valid.

email: jim.kosloskey@jim-kosloskey.com 29+ years Cloverleaf, 59 years IT - old fart.

[Charlie Bursell]

Looks good Jim but only tells you there is a connection.  It does not tell you the DB is up and ready.

I would recommend a fast and simple query.

[Robert Kersemakers]

GIYF

This looks usable: http://stackoverflow.com/questions/3055026/tsql-check-database-instance-online

Zuyderland Medisch Centrum; Heerlen/Sittard; The Netherlands

[Jim Kosloskey]

I would be careful with the wait. If you decide to do a wait of any kind inside your Xlate you could cause the whole process to wait potentially impacting other integrations.

One way around that is to put the integration in its own process (so only this integration is impacted). I would put a limit as to how long you will wait before declaring foul.

Then you need to decide what do you do with the message in flight and the others stacked up behind it (which will likely fail if they get into the Xlate before the issue is corrected).

Obviously (at least obvious to me) you need to notify some humanoid of the situation when you time out no matter what you do with the messages.

email: jim.kosloskey@jim-kosloskey.com 29+ years Cloverleaf, 59 years IT - old fart.

[Steve Pringle]

Seems like “select 1” would be a simple, fast query.  I believe your HDBC handle already has the sql server and database to connect to, so all you should need to do is run “select 1”.

[Jeff Dinsmore]

Pascal,

In the post below, there’s a fairly extensive discussion of strategies for waiting for a DB if its not online.

<a href="https://usspvlclovertch2.infor.com/viewtopic.php?t=7188&highlight=&#8221; class=”bbcode_url”>https://usspvlclovertch2.infor.com/viewtopic.php?t=7188&highlight=

Here is an abbreviated copy of the code that I settled on that illustrates the flow of messages into and out of the SQLite queueing DB.

Code:

######################################################################
# Name:         pgn_rde_to_premier
# Purpose:
# UPoC type:    tps
# Args:         tps keyedlist containing the following keys:
#               MODE    run mode (”start”, “run” or “time”)
#               MSGID   message handle
#               ARGS    user-supplied arguments:
#                      
#
# Returns: tps disposition list:
#

proc pgn_rde_to_premier { args } {

keylget args MODE mode                      ;# Fetch mode

global env

set dispList {}                             ;# Nothing to return
 
set warnCategory pharmacy
set warnGroups [list pharmacists all]

set deferDb pgnToPremierDbTimeoutQueue
 set deferDestination premierRde

set failsafeSendSec 120

switch -exact — $mode {
start {
# Perform special init functions
# N.B.: there may or may not be a MSGID key in args
}

run {
# ‘run’ mode always has a MSGID; fetch and process it

keylget args MSGID mh

set msg [msgget $mh]                                ;# Get message

# Parse message
crmcHL7::parseMsg $msg msgArray

set warnings “”

# Determine if this is a message we want to process
set killMsg 0
set dbQueueErr 0
set msgType [crmcHL7::readSegFieldComponent msgArray MSH 9 1]
switch -exact $msgType {
RDE {

# Added queueing to SQLite DB – 9/4/2014 – jld
#  this prevents problems with failures when the
#  Paragon DB is unavailable – as during an upgrade.

set clMsgIdKeyedList [msgmetaget $mh MID]
set cmid [keylget clMsgIdKeyedList DOMAIN].[keylget clMsgIdKeyedList HUB].[keylget clMsgIdKeyedList NUM]

if { ! [crmcDeferMsgsDb::writeMsg $deferDb $deferDestination $cmid $cmid $msg 0 0] } {
 
crmcMessagingDb::fetchAddresses $warnCategory $warnGroups foundAddrs

catch {crmcEmail::sendMessage “Paragon RDE to Premier ERROR” “ERROR: Failed to write message to defer database $deferDbnnmsg: $msg” $foundAddrs(aggregateEmail)}

msgmetaset $mh USERDATA “Failed to write message to defer database $deferDb”

set dbQueueErr 1
}

}
TPM {

# This is a trigger message – don’t store in SQLite – just process any queued messages
#
#   Triggers should be config’d to arrive in the outbound pool every few minutes
#   and will only come into play if the Paragon DB has been down for a while, but
#   a new message has not yet been received from the sending system.
#
#   In that situation, this will trigger any messages queued in SQLite to be sent.
#
#   Otherwise, in normal operations, it will be one message in/stored and
#   then immediately sent out.  During normal operations, this trigger will
#   find no messages queued in SQLite and will just be discarded.

#puts “Primer message (TPM) ignorednnmsg=$msg”

}
default {
set killMsg 1
}
}

if { $killMsg } {
lappend dispList “KILL $mh”
} else {
       
if { $dbQueueErr } {
lappend dispList “ERROR $mh”
} else {

# Process any messages stored in SQLite DB

set numMsgsProcessed [__pgn_rde_to_premier_worker__ $mh $deferDb $deferDestination $warnCategory $warnGroups dispList]

if { $msgType == “TPM” && $numMsgsProcessed } {
puts “Processed $numMsgsProcessed messages – Triggered by $msgType message”
}

# Must kill the original message since it’s successfully queued to defer DB
lappend dispList “KILL $mh”

}

}

}        

time {

# Timer-based processing
# N.B.: there may or may not be a MSGID key in args

}

       
shutdown {
#nothing to do
}
       
default {
error “Unknown mode ‘$mode’ in pgn_rde_to_premier”
}
       
}

return $dispList

}

proc __pgn_rde_to_premier_worker__ { mh deferDb deferDestination warnCategory warnGroups dispListName } {

upvar $dispListName dispList

 set numMsgs 0

if { [crmcParagonDb::dbOpen db] } {

if { ! [crmcDeferMsgsDb::getMatureMsgs $deferDb $deferDestination [clock seconds] matureDeferredMsgArray] } {
crmcMessagingDb::fetchAddresses $warnCategory $warnGroups foundAddrs
catch {crmcEmail::sendMessage “Paragon RDE to Premier ERROR” “ERROR: Failed to read deferred messages from $deferDb database for $deferDestination destination. Refer to [crmcSqliteUtils::dirPath $deferDb LOG] for details” $foundAddrs(aggregateEmail)}
} else {

for { set m 0 } { $m >>>> Do message processing here <<<<<

# Convert the modified message back into a string for msgset
crmcHL7::assembleMsg msgArray outBuf

# copy message if we have a message handle, otherwise create a new one
if { $mh eq "" } {
set newmh [msgcreate -recover]
} else {
set newmh [msgcopy $mh]
}

msgset $newmh $outBuf

lappend dispList "CONTINUE $newmh"

# delete deferred msg from DB
catch {crmcDeferMsgsDb::deleteMsg $deferDb $matureDeferredMsgArray($m,destinationSys) $matureDeferredMsgArray($m,appSpecificMsgId)}

catch {crmcDeferMsgsDb::setLastDeferredMsgSendSec $deferDb $deferDestination}

incr numMsgs

}

}

} else {
crmcMessagingDb::fetchAddresses $warnCategory $warnGroups foundAddrs
catch {crmcEmail::sendMessage "Paragon RDE to Premier DB WAIT" "Paragon DB unavailable – waiting" $foundAddrs(aggregateEmail)}
}

crmcParagonDb::dbClose db

return $numMsgs

}

Good luck!

Jeff Dinsmore
Chesapeake Regional Healthcare

[Author]

Replies