Unable to connect to sql server with odbc

This topic has 16 replies, 4 voices, and was last updated 16 years, 10 months ago by Todd Horst.

Creator

Topic
February 18, 2008 at 8:36 pm #49835
Todd Horst
Participant
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?
Creator

Topic

Viewing 15 reply threads

Author

Replies
- February 18, 2008 at 9:24 pm #63790
  Jonathan Presnell
  Participant
  try this:
  
  set rVal [odbc SQLExecDirect $hstmt $query SQL_NTS]
- February 18, 2008 at 9:46 pm #63791
  John Hamilton
  Participant
  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).
- February 19, 2008 at 1:23 pm #63792
  Todd Horst
  Participant
  ~~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}”‘
- February 19, 2008 at 3:02 pm #63793
  garry r fisher
  Participant
  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
- February 19, 2008 at 3:15 pm #63794
  John Hamilton
  Participant
  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.
- February 19, 2008 at 3:15 pm #63795
  Todd Horst
  Participant
  ~~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?
- February 19, 2008 at 6:29 pm #63796
  Todd Horst
  Participant
  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
- February 19, 2008 at 10:15 pm #63797
  John Hamilton
  Participant
  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.
- February 20, 2008 at 9:08 am #63798
  garry r fisher
  Participant
  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
- February 20, 2008 at 1:04 pm #63799
  Todd Horst
  Participant
  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
- February 20, 2008 at 2:15 pm #63800
  garry r fisher
  Participant
  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
- February 20, 2008 at 2:23 pm #63801
  Todd Horst
  Participant
  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
- February 20, 2008 at 3:13 pm #63802
  garry r fisher
  Participant
  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
- February 20, 2008 at 3:23 pm #63803
  Todd Horst
  Participant
  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.
- February 20, 2008 at 4:06 pm #63804
  John Hamilton
  Participant
  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.
- February 21, 2008 at 8:36 pm #63805
  Todd Horst
  Participant
  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

Viewing 15 reply threads

The forum ‘Cloverleaf’ is closed to new topics and replies.