SQLExecute and SQLExecDirect cause process to hang

Hi

I’m now on Solaris 10 with Cloverleaf 5.6 Rev2. I’ve port over the same TCL scripts which are running fine on Windows 2003 sp1 with Cloverleaf 5.3. This TCL basically calls a store procedure in Microsoft SQL 2005 to fetch a field back. The process would run for a day and then hang.

pstack shows after executing  7aeba950 SQLExecDirect (12529348, 1251d698, fffffffd, 7b14e79d, 400, 630) + 104 then it stuck there waiting for completion.

After viewing this forum, some suggested SQLExecute would be less of a performance issue, and I tried this but the process still hangs. The CPU usage for this process would goes 12% when this happens.

I sincerely hope that someone would have a solution for this as I’m going nowhere with this problem. Thank you for your time.

Regards,

Chak

Here is my TCL;

###Import ODBC3.DLL Package at quovadxqdx5.2integratorbin

package require odbc        

odbc SQLAllocHandle SQL_HANDLE_ENV SQL_NULL_HANDLE henv

odbc SQLSetEnvAttr $henv SQL_ATTR_ODBC_VERSION SQL_OV_ODBC3 0

odbc SQLAllocHandle SQL_HANDLE_DBC $henv hdbc

###System DSN config: DSN Name,UID,Pwd

set gUID “xxxx”

set password “xxxx”

set server “SQL Server Wire Protocol”

set Response [odbc SQLConnect $hdbc $server SQL_NTS $gUID SQL_NTS $password SQL_NTS]

echo “RESPONSE: $Response”

#Check final connection status

if {[cequal $Response “SQL_ERROR”] } {

               set succInd “FAIL”

} else {

               set succInd “SUCCESS”

}

if { [cequal $succInd “FAIL”] } {

               return “{OVER $msgId}”

} else {

      set sqlStmt “”

      ## allocate a handle for the SQL statement

       set errno [catch {odbc SQLAllocHandle SQL_HANDLE_STMT $hdbc hstmt} result]

       echo $result

    # prepare an fetch statement with 1 parameter markers

       set sqlStmt “exec dbo.sp_getcheckdigit $CaseNo”

       odbc SQLPrepare $hstmt $sqlStmt SQL_NTS

   ###set for single statement for execution

    set errno [catch {odbc SQLSetStmtAttr $hstmt SQL_ATTR_PARAMSET_SIZE 1 0} result]

    echo “stntAttr: $result”

    ## execute the query

   #set errno [catch { odbc SQLExecDirect $hstmt $sqlStmt SQL_NTS } result]

    set errno [catch { odbc SQLExecute $hstmt } result]

    set errno [odbc SQLExecute $hstmt]

    #echo “SQLExecute: $result $errno”  

     ## bind the data

     set errno [catch { odbc SQLBindCol $hstmt 1 SQL_C_CHAR CheckDigit 2 CheckDigitLength } result]

    echo “Binding Column ” $result

 

      ## fetch the data

      set errno [catch { odbc SQLFetch $hstmt } result]

      if {![cequal $result “SQL_NO_DATA_FOUND”] } {

echo $CaseNo

set DBcheckdigit $CheckDigit        

      }

                                             

      ##Free resources

      odbc SQLFreeHandle SQL_HANDLE_STMT $hstmt

      odbc SQLDisconnect $hdbc

      odbc SQLFreeHandle SQL_HANDLE_DBC $hdbc

      odbc SQLFreeHandle SQL_HANDLE_ENV $henv