I don't use ODBC, so I can only make some general observations (###).
--
Mac :})
** I normally forward private database questions to the DBI mail lists. **
Give a hobbit a fish and he'll eat fish for a day.
Give a hobbit a ring and he'll eat fish for an age.
----- Original Message -----
From: "Jack McKinney" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, May 09, 2001 13:11
Subject: DBI:ODBC connects, but queries fail.
I have installed unixodbc 2.0.6 and freetds 0.50 on my linux 2.2.17
system. I have created the following entry in /usr/local/etc/odbc.ini:
[TEST]
Driver=/usr/local/lib/odbc-i02.so
Server=mssql.mydomain.com
User=myuser
Pass=mypass
Database=testdb
Majorver=4
Minorver=2
ConnectTimeout=30
Trace=Yes
TraceFile=/tmp/odbc-mssql
If I run 'isql TEST' from the command line (isql is a command line
sql interpreter that comes with unixodbc), I am able to issue queries
and get correct response from the MSSQL server at mssql.mydomain.com.
THIS MEANS THAT MY ODBC IS INSTALLED CORRECTLY!
The entry above logs to /tmp/odbc-mssql. If I run the isql command,
issue one query, and then exit, this file contains:
[Inline TDS
Driver][/development/src/odbc/SQLConnect.c][/development/src/odbc/SQLConnect
.c][89]SQL_SUCCESS
[Inline TDS
Driver][/development/src/odbc/SQLGetInfo.c][/development/src/odbc/SQLGetInfo
.c][12]handle = $0804FA58
[Inline TDS
Driver][/development/src/odbc/SQLAllocStmt.c][/development/src/odbc/SQLAlloc
Stmt.c][15]handle = $0804FA58
[Inline TDS
Driver][/development/src/odbc/SQLDisconnect.c][/development/src/odbc/SQLDisc
onnect.c][7]handle = $0804FA58
So, I am ready for ODBC for perl. I install ODBC 0.28, and run the
following script (the query is the same one I executed from isql):
#!/usr/bin/perl
### -w in the #! line is worthwhile
### use strict; # is your friend
use DBI;
$query = "SELECT acctid,company FROM account";
#DBI->trace;
$db = DBI->connect("DBI:ODBC:TRACK") or die $DBI::errstr;
### You connected to 'TEST' from isql, not 'TRACK'.
### Error checking on connect() is always a good idea, but
### didn't do it for prepare() or execute().
### Perhaps you should set $db->{RaiseError} = 1;
$h = $db->prepare($query);
$h->execute;
$save = $count = $h->rows;
### The value from $h->rows() is not generally dependable
### until after all rows have been fetched.
### while ( @row = $h->fetchrow_array )
### # would probably do what you want better.
### If you are fetching large quantities,
### you should look into $h->bind_columns()
### and $h->fetch.
while($count > 0)
{
@row = $h->fetchrow;
$row = join("|",@row);
print "$row\n";
}
$h->finish;
### finish() shouldn't be necessary if you are fetching all rows
$db->disconnect;
I get the following error:
DBD::ODBC::st execute failed: [unixODBC][Driver Manager]Data source name not
found, and no default driver specified (SQL-IM002)(DBD:
describe/SQLColAttributes/SQL_COLUMN_LENGTH err=-1) at ./test2.pl line 11.
DBD::ODBC::db disconnect failed: (DBD: db_disconnect/SQLDisconnect err=-1)
at ./test2.pl line 20.
It claims that it could not find the DSN. Several points:
1. I removed /tmp/odbc-mssql before running this script. This file is back,
which indicates to me that it _is_ finding the DSN. How else would it know
where to write the trace?
/tmp/odbc-mssql:
[Inline TDS
Driver][/development/src/odbc/SQLConnect.c][/development/src/odbc/SQLConnect
.c][89]SQL_SUCCESS
[Inline TDS
Driver][/development/src/odbc/SQLGetInfo.c][/development/src/odbc/SQLGetInfo
.c][12]handle = $08181E28
[Inline TDS
Driver][/development/src/odbc/SQLSetConnectOption.c][/development/src/odbc/S
QLSetConnectOption.c][10]handle = $08181E28
[Inline TDS
Driver][/development/src/odbc/SQLEndTran.c][/development/src/odbc/SQLEndTran
.c][43]SQL_SUCCESS
[Inline TDS
Driver][/development/src/odbc/SQLSetConnectOption.c][/development/src/odbc/S
QLSetConnectOption.c][58]SQL_SUCCESS
[Inline TDS
Driver][/development/src/odbc/SQLSetConnectOption.c][/development/src/odbc/S
QLSetConnectOption.c][10]handle = $08181E28
[Inline TDS
Driver][/development/src/odbc/SQLSetConnectOption.c][/development/src/odbc/S
QLSetConnectOption.c][58]SQL_SUCCESS
[Inline TDS
Driver][/development/src/odbc/SQLAllocStmt.c][/development/src/odbc/SQLAlloc
Stmt.c][15]handle = $08181E28
[Inline TDS
Driver][/development/src/odbc/SQLDisconnect.c][/development/src/odbc/SQLDisc
onnect.c][7]handle = $08181E28
[Inline TDS
Driver][/development/src/odbc/SQLDisconnect.c][/development/src/odbc/SQLDisc
onnect.c][14]SQL_ERROR Active Statements exist. Can not disconnect.
2. If it could not find the DSN, why did the ODBC manager report so many
successful lines?
3. If it could not find the DSN, then why did ODBC fail on the
handle->execute
instead of the DBI->connect? I do check the return of DBI->connect, after
all.
I have tried many variations including setting ODBCHOME and DBI_DSN in
the environment, but always have basically the same problem.
