ODBC process hangs and stops processing
› Clovertech Forums › Read Only Archives › Cloverleaf › Cloverleaf › ODBC process hangs and stops processing
- This topic has 6 replies, 4 voices, and was last updated 13 years, 8 months ago by
Jason Garrant.
-
CreatorTopic
-
March 29, 2011 at 6:49 am #52376
I have just built an ODBC interface however, I have noticed that it hangs after a while like every week then I have to stop and start the ODBC process in order to let the interface continue processing the pending messages. I checked the logs nothing indicates there is a problem with script. We are running Linux ,version 5.6 and patch rev1. Any suggestion why is this continue happening? -
CreatorTopic
-
AuthorReplies
-
-
March 29, 2011 at 9:10 pm #73958
Are you reconnecting to the database with each message and disconnecting after each message? Can you post your script?
-
April 4, 2011 at 10:29 am #73959
Yes Davis I’m connecting and disconnecting to DB for each message.
Have a look to my script
proc Venus_Interface { args } {
global HciConnName
keylget args MODE mode ;# Fetch mode
keylget args CONTEXT ctx
keylget args ARGS uargs
set rcList
- set debug “0” ; keylget uargs DEBUG debug
# The parameters needed to connect to the database
set dsnName “ICIS_Interface” ; keylget uargs DSNNAME dsnName
set dsnUser “venus” ; keylget uargs DSNUSER dsnUser
set dsnPass “venuS2OO9” ; keylget uargs DSNPASS dsnPass
# The parameters needed to stop the thread
set processName prod_receiver ; keylget uargs PROCESSNAME processName
set threadName bhandler ; keylget uargs THREADNAME threadName
# Module string for debugging purposes
set module “$HciConnName/[lindex [info level 0] 0]/$ctx”
set dispList {} ;# Nothing to return
switch -exact — $mode {
start {
# Perform special init functions
# N.B.: there may or may not be a MSGID key in args
if { $debug } {
echo “($module): Start using settings: $uargs”
}
}
run {
# ‘run’ mode always has a MSGID; fetch and process it
# Initiate connection to the database
# Set an i variable to count the number of tries for connecting to DB
# Sleep 5 seconds between every two tries
keylget args MSGID mh
set i 1
set trialNum 3
set sleepPeriod 5
set dhList [datlist]
set enc “cp1256”
set gh [grmcreate -msg $mh hl7 2.3 hnam.hl7 {}]
set msgcontent [msgget $mh]
set msgcontent [encoding convertfrom $enc $msgcontent]
regsub -all ‘ $msgcontent ” msgcontent
#puts stdout “…$msgcontent”
#Trying To connect to the Database (ICIS_Interface)
while { [catch {lassign [gc_dbConnect $dsnName $dsnUser $dsnPass $debug] henv hdbc} err] } {
puts stderr “[fmtclock [getclock] %c] $err”
if { $i > $trialNum } {
#Could not connect to the DB
puts stderr “$module: Tried connecting $trialNum times in $mode mode. Giving up.”
puts stderr “Error: Check the connection string in: thread outbound settings, J2EEFUNCTIONS settings”
puts stderr “Error: If set properly, check that the data source is defined in odbc.ini file”
#set repmh [venus_constructReply $gh AE]
set dispList “KILL $mh”
#lappend dispList “SEND $repmh”
return $dispList
}
# Inform about the inability to connect and sleep for sleepPeriod (e.g. 5 seconds)
puts stderr “$module: Connection failure in attempt $i. Retrying in 5 seconds…”
sleep $sleepPeriod
incr i
}
#puts stdout “We have succeeded connecting…”
set cmd “insert INTO MSG_QUEUE (MSG,CONTROL_ID) values (N’$msgcontent’,’P’)”
set cmd [encoding convertto utf-8 $cmd]
;#####################################here
if { [catch {
# Allocation of handler
puts stdout “$module: Doing SQLAllocHandle: [odbc SQLAllocHandle SQL_HANDLE_STMT $hdbc hstmt]”
# puts stderr “The command was: $cmd”
# Try to execute the command that contains the function call
# If you catch an error stop the thread and echo it
# Else validate the reply
if { [catch {set rc [odbc SQLExecDirect $hstmt $cmd SQL_NTS]} err] || [lsearch -exact $rcList $rc] < 0 } {
catch {append err “n[gc_dbGetODBCError $henv $hdbc $hstmt $debug]”}
puts stdout “Error: Could not execute the command, check your command”
puts stdout “Error: The Commeand was $cmd”
puts stdout “$module: Doing SQLExecDirect: $err”
} else {
#puts stdout “$module: Doing SQLExecDirect: $rc and the reply is $reply”
#
CONSTRUCTING REPLY
set repmh [venus_constructReply $msgcontent AA]
}
# Free the handle for the statement execution
puts stdout “$module: Doing SQLFreeHandle: [odbc SQLFreeHandle SQL_HANDLE_STMT $hstmt]”
} err] } {
puts stderr “$module: Dramatic error $err”
}
#
CLEANUP
# FREE handler, destroy grm.
catch {odbc SQLFreeHandle SQL_HANDLE_STMT $hstmt}
catch {grmdestroy $gh}
catch {hcidatlistreset $dhList}
# Disconnect from the database
gc_dbDisconnect $henv $hdbc
#
DISPOSITIONING
# Specify the disposition
lappend dispList “KILL $mh”
lappend dispList “SEND $repmh”
}
time {
# Timer-based processing
# N.B.: there may or may not be a MSGID key in args
}
shutdown {
# Doing some clean-up work
}
}
return $dispList
}
Thanks
- set debug “0” ; keylget uargs DEBUG debug
-
April 7, 2011 at 5:03 pm #73960
There are a limited number of connections you can make at one time. I encountered a similar issue. I resolved this by connecting to the database in startup, and just making sure I am still connected prior to sedning, if I am not connected, then I reconnect.
What was happening with me was during busy times, the connections woudl not time out and close in time for me to make more connections.
The other problem might be you log files are getting too big causing the process to choke. I would suggest limiting if not removing all of your echos.
-
April 25, 2011 at 1:58 pm #73961Jason Garrant wrote:
There are a limited number of connections you can make at one time.
-
April 25, 2011 at 9:59 pm #73962
Jason,
In your tcl code, how do you determine if you’re connected? Do you run a simple query (e.g., “select 1”) and if you get a success status you’re connected? Or?
thanks,
Steve
-
April 27, 2011 at 3:31 pm #73963
This is the code in the startup:
start {
set sql_stmt “Startup”
set conn_rVal [{namespace}::odbc_connect]
return “”
}
Where in the curly braces you putyour name space. By creating the procs int he namespace, makes it easier throughout the code if you lose connectivity to simply call it.
Here is the odbc_connect proc:
proc odbc_connect {args} {
global hdbc hstmt henv dsn usr pass
global HciConnName server from subject address
#Setting database connection variables
#Table to insert into
set dsn TableToInsertInto
#Username to use for access
set usr UserName
#Password
set pass Password
set err [odbc SQLAllocHandle SQL_HANDLE_ENV SQL_NULL_HANDLE henv]
set err [odbc SQLSetEnvAttr $henv SQL_ATTR_ODBC_VERSION SQL_OV_ODBC3 0]
#Allocate connection handle
set err [odbc SQLAllocHandle SQL_HANDLE_DBC $henv hdbc]
#Make an ODBC connection by passing user id and password
set err [odbc SQLConnect $hdbc $dsn SQL_NTS $usr
SQL_NTS $pass SQL_NTS]
set retries 3
set sleep_int 5
while {$retries && [string equal $err SQL_ERROR]} {
sleep $sleep_int
set err [odbc SQLConnect $hdbc $dsn SQL_NTS $usr
SQL_NTS $pass SQL_NTS]
incr retries -1
}
if {[string equal $err SQL_ERROR]} {
set rVal [{namespace}::odbc_error hdbc]
set logRet [{namespace::logSQLStatement $rVal]
}
return $err
}
This is the proc I use to send the statement:
proc odbc_sql {sql_stmt} {
global hdbc hstmt henv dsn usr pass
global HciConnName server from subject address
set err SQL_ERROR
echo ODBC_SQL:>>>>> $sql_stmt <<<<<<<
# Drop previous statement handle & create new handle
catch {odbc SQLFreeStmt $hstmt SQL_DROP}
#checking to see if we are still connected. Doing a get date from database to check connection
catch {set err [odbc SQLAllocHandle SQL_HANDLE_STMT $hdbc hstmt]}
if {[string equal $err SQL_SUCCESS]} {
set db_chk_sql “select getdate()”
set err [odbc SQLAllocHandle SQL_HANDLE_STMT $hdbc hstmt]
set err [odbc SQLExecDirect $hstmt $db_chk_sql SQL_NTS]
}
if {[string equal $err SQL_ERROR]} {
if {[string equal $err SQL_ERROR]} {
set err [{namespace}::odbc_connect]
}
}
if {[string equal $err SQL_SUCCESS]} {
catch {odbc SQLFreeStmt $hstmt SQL_DROP}
set err [odbc SQLAllocHandle SQL_HANDLE_STMT $hdbc hstmt]
set err [odbc SQLExecDirect $hstmt $sql_stmt SQL_NTS]
return $err
}
return hdbc
}
Let me know if this helps or if you need more info.
-
-
AuthorReplies
- The forum ‘Cloverleaf’ is closed to new topics and replies.
