Unable to connect to sql server with odbc

[Todd Horst]

Im just trying to connect to a sql database and get a count of the rows in a table.

Pretty basic, I just need to do this to test that our odbc is working correctly.

Here is the code in the tcl proc:

Code:

proc tps_db_select { args } {
   keylget args MODE mode               ;# Fetch mode

   set dispList {} ;# Nothing to return

   switch -exact — $mode {

start {
# initialize the ODBC call level interface
package require odbc
odbc SQLAllocHandle SQL_HANDLE_ENV SQL_NULL_HANDLE henv

# application is ODBC 3
odbc SQLSetEnvAttr $henv SQL_ATTR_ODBC_VERSION SQL_OV_ODBC3 0

# allocate a connection handle
odbc SQLAllocHandle SQL_HANDLE_DBC $henv hdbc

# connect to the data source: data source name: MSSQLServer
# user name: med_adm password: aspirin
odbc SQLConnect $hdbc wshsql2 SQL_NTS thorst02 SQL_NTS %My_Password% SQL_NTS

# allocate a statement handle
odbc SQLAllocHandle SQL_HANDLE_STMT $hdbc hstmt

set query “SELECT count(*) OrderIndex1”
set rVal [odbc SQLExecDirect $hdbc $query SQL_NTS]
echo $rval

#odbc SQLFreeHandle SQL_HANDLE_STMT $hstmt
odbc SQLDisconnect $hdbc
odbc SQLFreeHandle SQL_HANDLE_DBC $hdbc
odbc SQLFreeHandle SQL_HANDLE_ENV $henv
return
}

       run {
# ‘run’ mode always has a MSGID; fetch and process it
keylget args MSGID mh
lappend dispList “CONTINUE $mh”
return $dispList
       }
   }
}

Here is the code in the odbc.ini file:

Code:

[wshsql2]
Driver=/qdxitest/qdx5.5/integrator/lib/lib/CVmsss22.so
Description=DataDirect 5.2 SQL Server Wire Protocol
Address=
AlternateServers=
AnsiNPW=Yes
ConnectionRetryCount=0
ConnectionRetryDelay=3
Database=EI_YorkImaging
LoadBalancing=0
LogonID=thorst02
Password=%My_Password%
QuotedId=No
SnapshotSerializable=0

Everything is exactly as i have it except the my_password areas have my actual password.

It fails allocating the statement handle. Here is my error:

Code:

[0:TEST] Tcl error:
msgId = none
proc = ‘tps_db_select’
args = ”
result = ‘invalid hstmt handle “hdbc0”
handle hdbc0 not found’
errorInfo: ‘
invalid hstmt handle “hdbc0”
handle hdbc0 not found
   while executing
“odbc SQLExecDirect $hdbc $query SQL_NTS”
   (”start” arm line 27)
   invoked from within
“switch -exact — $mode {
start {
# initialize the ODBC call level interface
package require odbc
odbc SQLAllocHandle SQL_HANDLE_ENV SQL_NULL_HANDLE he…”
   (procedure “tps_db_select” line 6)
   invoked from within
“tps_db_select {CONTEXT sms_ib_data} {ARGS {}} {MODE start} {VERSION 3.0}”‘

Does anyone have any ideas?

Topic

[Jonathan Presnell]

try this:

set rVal [odbc SQLExecDirect $hstmt $query SQL_NTS]

[John Hamilton]

Todd send me you email. I will send you a script that will run outside the engine to test things to make sure odbc is up and running.

You will have to modify it for your environment.

The big this is check’s all the return codes and lets you see the errors if any.  

THANK YOU CHARLIE B !!! (Charlie sent it too me sometime back).

[Todd Horst]

Jonathan Presnell wrote:

try this:

set rVal [odbc SQLExecDirect $hstmt $query SQL_NTS]

I did have it this way (and this is how it should be) but i had changed it because I was testing it.

Changing it back now though produces the same error as i was getting before.

However because i made that change the error i previously posted is incorrect. This is the actual error i was originally getting….sorry about the mixup.

Code:

[0:TEST] Tcl error:
msgId = none
proc = ‘tps_db_select’
args = ”
result = ‘hstmt is not open
handle hstmt0 not found’
errorInfo: ‘
hstmt is not open
handle hstmt0 not found
   while executing
“odbc SQLExecDirect $hstmt $query SQL_NTS”
   (”start” arm line 22)
   invoked from within
“switch -exact — $mode {
start {
# initialize the ODBC call level interface
package require odbc
odbc SQLAllocHandle SQL_HANDLE_ENV SQL_NULL_HANDLE he…”
   (procedure “tps_db_select” line 6)
   invoked from within
“tps_db_select {CONTEXT sms_ib_data} {ARGS {}} {MODE start} {VERSION 3.0}”‘

[garry r fisher]

Hi Todd,

The correct syntax is

set rVal [odbc SQLExecDirect $hstmt $query SQL_NTS]

I suspect that there is no statement handle because you failed to connect to the database. I tend to use the following t ocheck this:

# allocate a connection handle

set err [odbc SQLAllocHandle SQL_HANDLE_DBC $henv hdbc]

echo Connection Handle Status : $err

# connect to the data source: data source name: MSSQLServer

# user name: med_adm password: aspirin

set err [odbc SQLConnect $hdbc wshsql2 SQL_NTS thorst02 SQL_NTS %My_Password% SQL_NTS]

echo DB Connection Status : $err

etc…

Also you could catch the connection and only continue if you have connected successfully.

John/Charlie – I’d be interested in any code to check connectivity etc.

Regards

Garry

[John Hamilton]

Last time I tried to post an attachment I never seemed to get it correct.

Maybe this time I can get it too work.

Again the code will have to be modified for you site. I’m working on some ehancements too this it is very basic but at the same time very informative.  Thanks again Charalie.

[Todd Horst]

garry r fisher wrote:

I suspect that there is no statement handle because you failed to connect to the database. I tend to use the following to check this:

Correct you are! So now I’m assuming that this is an error with my odbc.ini file?

How do I ensure that i have the correct information? In other words, where in sql can I retireve these settings?

[Todd Horst]

John,

I have downloaded the file, and uploaded it to the tcl procs directory. When i open up a command prompt and go into tcl then source the file i get the following error

Is there something else i need to global, or should i be putting this file in a different location. Or should I not be sourceing the file and instead testing in some other manner?

Error: invalid command name “odbc”

Thanks

[John Hamilton]

You need to do a  package require odbc as well. It is not included in the script one of the things I’m working on with the ehancements.

[garry r fisher]

Hi Todd,

Couple of questions:

1. What OS are you on?

2. Why are you using the wire protocol – I haven’t had a lot of success with these and use the other SQL Server drivers or native if possible.

UPDATE::

Just downloaded the oconnect code – I use something similar but I do it inline – See below:

       set retcode [odbc SQLExecute $hstmt]

       echo Execute Status: $retcode

       if {![cequal $retcode SQL_SUCCESS]} {

           set recnum 1

           while {![cequal $retcode SQL_NO_DATA_FOUND]} {

               set retcode [odbc SQLGetDiagRec SQL_HANDLE_STMT $hstmt $recnum SQLState NativeError MessageText 511 TextLength]

               echo ($retcode) ($SQLState) ($NativeError) ($MessageText)

               incr recnum

           }

       }

Hope this helps somebody.

Regards

Garry

[Todd Horst]

One of my errors was in the odbc.ini file. I changed the value of the driver location. Now instead of erroring our immediately it takes a couple of seconds to error out. So now it seems like its going out and trying to sign in and then something errors out.

I’ve tried two different sets of username and password. Both should work. 1 I’m using in production in a different project (though its in asp.net).

Quote:

1. What OS are you on?

2. Why are you using the wire protocol – I haven’t had a lot of success with these and use the other SQL Server drivers or native if possible.

We are running AIX 5.2 or 5.3

I am using the wire because that is the only one in the odbc.ini file that has the word sql in it. If there are others id be interested in seeing them. Could someone else post thier odbc.ini contents for a sql connection.

Quote:

You need to do a

[garry r fisher]

Hi,

I don’t normally use ODBC.ini as I work on Windows boxes but is the name of the sql server? Have you tried putting in an IP address?

Regards

Garry

[Todd Horst]

Yes, thats the name of the sql server.

Ive tried just ip, ip and port, pc name, pc name and port, fqdn, fqdn and port.

I’ve tried pinging the machine from the cloverleaf server and it returns the packets fine, so there is definately that connection.

Im assuming that its using port 1433 but is there a way to check this?

here is my current odbc.ini section

Code:

[wshsql2]
Driver=/qdxitest/qdx5.5/integrator/lib/Connect5.2/lib/CVmsss22.so
Description=DataDirect 5.2 SQL Server Wire Protocol
Address=<192.168.135.19,1433>
AnsiNPW=Yes
ConnectionRetryCount=0
ConnectionRetryDelay=3
Database=EI_YorkImaging
LoadBalancing=0
LogonID=eiuser01
Password=%Password%
QuotedId=No
SnapshotSerializable=0

[garry r fisher]

Hi Todd,

Remove the<> from around the machine name/IP address. This is in the example to show where changes should be made.

Rather than

Address= it should be

Address=wshsql2

Regards

Garry

[Todd Horst]

If i remove the <> i get the following error:

Code:

ODBC SQLConnect call resulted in following error:

[DataDirect][ODBC SQL Serv
ODBC State:

Also instead of failing after several seconds it fails right away.

[John Hamilton]

I do not recognize  that error.

In looking at you setting and comparing too what I have they look good.

My next suggestion would be to call support.

It looks as if something did not install correctly with the libraries or in the environment settings. But this is getting beyond something I can help you with with out actually touching your machine and tracking things down.

[Todd Horst]

My one problem was the location of the odbc driver. The other was the brackets around the ip and port. Besides that things pretty much are the same..

I have it working now. This post is merely to say its resolved and if others should happen to look for the same thing.

[Author]

Replies