odbc question

[Mike Campbell]

I’m trying to change some tcl procs to use a different odbc connection.  Is there any SQL command that you can use to display what Host Name and Port Number the process is trying to use to connect?  

Thanks.

Topic

[Cullen Linn]

I’m not familiar with any SQL commands for that application, however if you wanted to manually check from a terminal / command prompt during the ODBC connection initiation or after it’s established you could use:

For windows, you can run the following from the server:

Code:

netstat -ano

That will display all IPs, ports and owning processes for everything on the box, then you can connect the dots with your Cloverleaf processes.

If you’re running Linux:

Code:

netstat -anp

[Tom Gilbert]

This is what I go through to connect…

incase you are calling the tcl from a xlate that is included otherwise just remove it.

I use the DNS on the cloverleaf server and I call a stored procedure:

set debug 1

package require odbc

upvar   xlateId       xlateId

       xlateInList   xlateInList

       xlateInTypes  xlateInTypes

       xlateInVals   xlateInVals

       xlateOutList  xlateOutList

       xlateOutTypes xlateOutTypes

       xlateOutVals  xlateOutVals

set usr “test”

set pass “password”

set dsn “Cloverleaf”

set stored_procedure “sp_What_ever_you_want_to_call_it”

set table “KDDH_PRM_PCIS_OrderNo_xRef”

echo [odbc SQLAllocHandle SQL_HANDLE_ENV SQL_NULL_HANDLE henv]

# Set the most current ODBC version (3.0)

echo [odbc SQLSetEnvAttr $henv SQL_ATTR_ODBC_VERSION SQL_OV_ODBC3 0]

# Allocate connection handle

set err [odbc SQLAllocHandle SQL_HANDLE_DBC $henv hdbc]

if {$debug} {puts “DEBUG: SQLAllocHandle err: $err”}

# Make a connection

set err [odbc SQLConnect $hdbc $dsn SQL_NTS $usr SQL_NTS $pass SQL_NTS]

if {$debug} {puts “DEBUG: SQLConnect err: $err”}

#If connection failed… will attempt to connect 3 times (5sec sleep between each retry)

set retries 3

set sleep_int 5

while {$retries && [cequal $err SQL_ERROR]} {

 sleep $sleep_int

 # Make a connection

 set err [odbc SQLConnect $hdbc $dsn SQL_NTS $usr SQL_NTS $pass SQL_NTS]

  incr retries -1

}

catch {odbc SQLFreeStmt $hstmt SQL_DROP}

set err [odbc SQLAllocHandle SQL_HANDLE_STMT $hdbc hstmt]

#if {$err ne “SQL_SUCCESS”} {return  “”}

if {$debug} {puts “DEBUG: SQLAllocHandle err: $err”}

# Prepare & Execute – SQL statement

set err [odbc SQLExecDirect $hstmt $stored_procedure SQL_NTS]

set result {}

set id “”

set prm_msgid “”

set pcis_orderno “”

set insertdatetime “”

set noorderno_emailflag “”

echo bind1 [odbc SQLBindCol $hstmt 1 SQL_C_NUMBERIC id 255 255]

echo bind1 [odbc SQLBindCol $hstmt 2 SQL_C_VARCHAR prm_msgid 255 255]

echo bind1 [odbc SQLBindCol $hstmt 3 SQL_C_VARCHAR pcis_orderno 255 255]

echo bind1 [odbc SQLBindCol $hstmt 4 SQL_C_VARCHAR noorderno_emailflag 255 255]

echo bind1 [odbc SQLBindCol $hstmt 5 SQL_C_TIMESTAMP insertdatetime 255 255]

set i 0

set err [odbc SQLFetch $hstmt]

This is how i disconnect…

catch {odbc SQLDisconnect $hdbc}

catch {odbc SQLFreeHandle SQL_HANDLE_DBC $hdbc}

catch {odbc SQLFreeHandle SQL_HANDLE_ENV $henv}

Hope this helps…

[Tom Rioux]

You might want to try SQLGetInfo.   It won’t get you the port number, but you can get either the server name or the database name where you are connecting.

We connect to a database that, if needed, can fail over to a replicated database.   We use SQLGet Info to check which database we are connected.

Thanks…Tom

[Author]

Replies