Re: DBD::ODBC fails, SQL*Plus works
On Oct 5, 2011, at 1:53 PM, Bruce Johnson wrote: > Basic questions: the script is running in an environment where the env > variables $ORACLE_HOME and $TNS_ADMIN are available? Your tnsnames.ora file > is present and correct? I did have the env variables configured, and the tnsnames.ora is now correct. Thank you. I do have a question, though. In other installations, the combination of Oracle Instant Client and Microsoft's ODBC Data Source Administrator control panel have been sufficient to gain access to an Oracle db, with no need for a tnsnames.ora file. Indeed, Oracle touts that a a benefit of the Instant Client. In the ODBC Data Source Administrator, what they call the "TNS Service Name" is of the format //hostname:port/sid. Could it be that setting up the data source via the control panel only uses the EZCONNECT format, and if the db instance that I was querying didn't have EZCONNECT enabled via it's sqlnet.ora file ( NAMES.DIRECTORY_PATH=(ezconnect, tnsnames) ), I would see the issues that I did? It's possible that I've never run into the issue before as that (EZCONNECT enabled) is the default? Or does this not make sense at all? ;) Once again, thanks for taking the time to help... Scott...
Re: :ODBC fails, SQL*Plus works
On Oct 5, 2011, at 1:47 PM, Kong, Alan wrote: > > It is different than typing the whole connection description in the command > prompt as it will bypass tnsnames.ora entry. ODBC is looking for the > tnsnames.ora entry for connection description, so using the above connection > method, I bet it will fail also. If this is the case, that means your > tnsnames.ora entry is somehow corrupted by the unknown characters. What I am > usually do is to type the tnsnames.ora entry manually, it should fix your > problem. > > Hope this is the case. It appears that was a contributing factor. Thanks for your help! Scott...
Re: :ODBC fails, SQL*Plus works
On Oct 5, 2011, at 1:00 PM, Kong, Alan wrote: > Since you are using DBI::ODBC, you should create an ODBC data source > pointing to your Oracle database first, and then calling the connect > function by passing the data source name you just created for ODBC data > source. That's our standard config, but in this case that is failing also. Configuring the DSN via the ODBC Data Source Administrator control panel and hitting "Test Connection" yields the same error: Unable to connect SQLState=08004 [Oracle][ODBC][Ora]ORA-12154: TNS:could not resolve the connect identifier specified. I added the connection parameters and credentials to the script to see if it was windows talking to the Oracle InstantClient ODBC layer that was causing the problem... Scott...
Re: :ODBC fails, SQL*Plus works
I'm sorry - that was just fat fingers on the cut/paste...
In actuality it is $oracle_host = "192.168.15.200:1535"
On Oct 5, 2011, at 12:13 PM, Nelson, Erick [HDS] wrote:
> Your $oracle_host Perl var looks like it has more that just the host ip
> address and port in it. What is the 10.128 ?
>
> -Original Message-----
> From: Scott Stansbury [mailto:sstansb...@aol.com]
> Sent: Wednesday, October 05, 2011 9:09 AM
> To: dbi-users@perl.org
> Subject: DBD::ODBC fails, SQL*Plus works
>
> Folks,
>
> I could use a tip or lead with the following issue:
>
> I have a Windows Server 2008 R2 box with Oracle Instant Client 11.2,
> with the SQL*Plus and ODBC modules installed. No other Oracle software
> is installed.
>
> I am connecting to an Oracle 10g instance. The following SQL*Plus works:
>
> sqlplus
> user/passwd@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.
> 168.15.200)(PORT=1535)))(CONNECT_DATA=(SID=PROD77))).
>
> The perl script that contains the following does not:
>
> use DBI;
>
> # Oracle / Remedy info
>
> my $oracle_sid = "PROD77";
> my $oracle_host = "192.168.15.200:1535";
>
> my $oracle_userid = "user";
> my $oracle_passwd = "passwd";
>
>
> my $dsn = "Driver={Oracle in
> instantclient_11_2};server=$oracle_host;sid=$oracle_sid";
>
> my $dbh = DBI->connect("dbi:ODBC:$dsn", "$oracle_userid",
> "$oracle_passwd");
>
> It returns (after a few seconds) with an ORA-12154 error: TNS:could not
> resolve the connect identifier specified ( SQL-08004).
>
> Thanks for any help...
>
> Scott...
>
>
>
>
Scott...
DBD::ODBC fails, SQL*Plus works
Folks,
I could use a tip or lead with the following issue:
I have a Windows Server 2008 R2 box with Oracle Instant Client 11.2, with the
SQL*Plus and ODBC modules installed. No other Oracle software is installed.
I am connecting to an Oracle 10g instance. The following SQL*Plus works:
sqlplus
user/passwd@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.15.200)(PORT=1535)))(CONNECT_DATA=(SID=PROD77))).
The perl script that contains the following does not:
use DBI;
# Oracle / Remedy info
my $oracle_sid = "PROD77";
my $oracle_host = "10.128 192.15.200:1535";
my $oracle_userid = "user";
my $oracle_passwd = "passwd";
my $dsn = "Driver={Oracle in
instantclient_11_2};server=$oracle_host;sid=$oracle_sid";
my $dbh = DBI->connect("dbi:ODBC:$dsn", "$oracle_userid", "$oracle_passwd");
It returns (after a few seconds) with an ORA-12154 error: TNS:could not resolve
the connect identifier specified ( SQL-08004).
Thanks for any help...
Scott...
Re: DBD::ODBC make test fails - unixODBC/Easysoft/SQL Server 2008
Yes.
Thank you again.
Best regards,
Scott...
On Jun 30, 2009, at 11:52 AM, Martin Evans wrote:
Scott Stansbury wrote:
Getting closer :)
I'm trying to connect to a SQL Server 2008 instance from a CentOS5
box:
unixODBC: 2.2.14
FreeTDS:0.82
Easysoft: odbc-sqlserver-1.1.26-linux-x86 (this morning, thank you
again Martin and Wez...)
osql/isql work fine with both the FreeTDS and the Easysoft drivers.
Connecting to a SQL Server 2008 instance running on a Server 2008
box.
DBB::ODBC make test fails as follows:
[r...@server1 DBD-ODBC-1.22-D2_Zne]# make test
PERL_DL_NONLAZY=1 /usr/bin/perl "-MExtUtils::Command::MM" "-e"
"test_harness(0, 'blib/lib', 'blib/arch')" t/*.t
t/01base.t .. ok
t/02simple.t 3/65 #
# Perl 5.8.8
# osname=linux, osvers=2.6.18-53.el5, archname=i386-linux-thread-
multi
# Using DBI 1.609
# Using DBD::ODBC 1.22
# Using DBMS_NAME 'Microsoft SQL Server'
# Using DBMS_VER '10.00.1600'
# Using DRIVER_NAME 'libessqlsrv.so'
# Using DRIVER_VER '01.01.0026'
# odbc_has_unicode 0
t/02simple.t 35/65
# Failed test 'data sources test'
# at t/02simple.t line 259.
# '0'
# >
# '0'
# Looks like you failed 1 test of 65.
t/02simple.t Dubious, test returned 1 (wstat 256, 0x100)
Failed 1/65 subtests
t/03dbatt.t . 3/29 #
# N.B. Some drivers (postgres/cache) may return ODBC 2.0 column names
for the SQLTables result-set e.g. TABLE_QUALIFIER instead of
TABLE_CAT
t/03dbatt.t . ok
t/05meth.t .. ok
t/07bind.t .. ok
t/08bind2.t . ok
t/09multi.t . ok
t/10handler.t ... ok
t/12blob.t .. ok
t/20SqlServer.t . ok
t/30Oracle.t ok
t/40UnicodeRoundTrip.t .. ok
t/41Unicode.t ... ok
t/pod-coverage.t ok
t/pod.t . ok
t/rt_38977.t ok
t/rt_39841.t ok
t/rt_39897.t ok
t/rt_43384.t ok
t/rt_46597.t ok
t/rt_null_nvarchar.t ok
Test Summary Report
---
t/02simple.t (Wstat: 256 Tests: 65 Failed: 1)
Failed test: 56
Non-zero exit status: 1
Files=21, Tests=445, 33 wallclock secs ( 0.02 usr 0.26 sys + 0.27
cusr 2.17 csys = 2.72 CPU)
Result: FAIL
Failed 1/21 test programs. 1/445 subtests failed.
make: *** [test_dynamic] Error 255
As always, thanks!
Best regards,
Scott...
Scott,
Sorry for not answering sooner - somehow I missed your follow up.
The test that fails is looking for a data source i.e., it called
DBI->data_sources('ODBC') and it should be testing the returned
array to
check there is more than 0 entries but in fact it does:
cmp_ok($#data_sources, '>', 0, "data sources test");
so if you only have one data source it fails. You can rectify the test
yourself by changing the > above to >=.
This does not indicate any problem with the driver your are using or
your setup.
Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
DBD::ODBC make test fails - unixODBC/Easysoft/SQL Server 2008
Getting closer :) I'm trying to connect to a SQL Server 2008 instance from a CentOS5 box: unixODBC: 2.2.14 FreeTDS:0.82 Easysoft: odbc-sqlserver-1.1.26-linux-x86 (this morning, thank you again Martin and Wez...) osql/isql work fine with both the FreeTDS and the Easysoft drivers. Connecting to a SQL Server 2008 instance running on a Server 2008 box. DBB::ODBC make test fails as follows: [r...@server1 DBD-ODBC-1.22-D2_Zne]# make test PERL_DL_NONLAZY=1 /usr/bin/perl "-MExtUtils::Command::MM" "-e" "test_harness(0, 'blib/lib', 'blib/arch')" t/*.t t/01base.t .. ok t/02simple.t 3/65 # # Perl 5.8.8 # osname=linux, osvers=2.6.18-53.el5, archname=i386-linux-thread-multi # Using DBI 1.609 # Using DBD::ODBC 1.22 # Using DBMS_NAME 'Microsoft SQL Server' # Using DBMS_VER '10.00.1600' # Using DRIVER_NAME 'libessqlsrv.so' # Using DRIVER_VER '01.01.0026' # odbc_has_unicode 0 t/02simple.t 35/65 # Failed test 'data sources test' # at t/02simple.t line 259. # '0' # > # '0' # Looks like you failed 1 test of 65. t/02simple.t Dubious, test returned 1 (wstat 256, 0x100) Failed 1/65 subtests t/03dbatt.t . 3/29 # # N.B. Some drivers (postgres/cache) may return ODBC 2.0 column names for the SQLTables result-set e.g. TABLE_QUALIFIER instead of TABLE_CAT t/03dbatt.t . ok t/05meth.t .. ok t/07bind.t .. ok t/08bind2.t . ok t/09multi.t . ok t/10handler.t ... ok t/12blob.t .. ok t/20SqlServer.t . ok t/30Oracle.t ok t/40UnicodeRoundTrip.t .. ok t/41Unicode.t ... ok t/pod-coverage.t ok t/pod.t . ok t/rt_38977.t ok t/rt_39841.t ok t/rt_39897.t ok t/rt_43384.t ok t/rt_46597.t ok t/rt_null_nvarchar.t ok Test Summary Report --- t/02simple.t (Wstat: 256 Tests: 65 Failed: 1) Failed test: 56 Non-zero exit status: 1 Files=21, Tests=445, 33 wallclock secs ( 0.02 usr 0.26 sys + 0.27 cusr 2.17 csys = 2.72 CPU) Result: FAIL Failed 1/21 test programs. 1/445 subtests failed. make: *** [test_dynamic] Error 255 As always, thanks! Best regards, Scott...
DBD::ODBC make test fails - unixODBC/Easysoft/SQL Server 2008
I'm trying to connect to a SQL Server 2008 instance from a CentOS5 box: unixODBC: 2.2.14 FreeTDS:0.82 Easysoft: odbc-sqlserver-1.1.4-linux-x86 (trial for now) osql/isql worked fine with both the FreeTDS and the Easysoft drivers. Connecting to a SQL Server 2008 instance running on a Server 2008 box. DBB::ODBC make test fails as follows: [sc...@server1 DBD-ODBC-1.22-hGw3zr]$ make test PERL_DL_NONLAZY=1 /usr/bin/perl "-MExtUtils::Command::MM" "-e" "test_harness(0, 'blib/lib', 'blib/arch')" t/*.t t/01base.t .. ok t/02simple.t 1/65 # # Perl 5.8.8 # osname=linux, osvers=2.6.18-53.el5, archname=i386-linux-thread-multi # Using DBI 1.609 # Using DBD::ODBC 1.22 # Using DBMS_NAME 'Microsoft SQL Server' # Using DBMS_VER '10.00.1600' # Using DRIVER_NAME 'libessqlsrv.so' # Using DRIVER_VER '01.01.0004' # odbc_has_unicode 0 t/02simple.t ok t/03dbatt.t . 3/29 # # N.B. Some drivers (postgres/cache) may return ODBC 2.0 column names for the SQLTables result-set e.g. TABLE_QUALIFIER instead of TABLE_CAT t/03dbatt.t . ok t/05meth.t .. ok t/07bind.t .. ok t/08bind2.t . ok t/09multi.t . ok t/10handler.t ... ok t/12blob.t .. ok t/20SqlServer.t . 3/65 # DBD::ODBC::db do failed: [unixODBC] [Easysoft][SQL Server Driver 10.0][SQL Server]Statement(s) could not be prepared. (SQL-42000) # [unixODBC][Easysoft][SQL Server Driver 10.0][SQL Server]Must declare the scalar variable "@P1". (SQL-42000) # [unixODBC][Easysoft][SQL Server Driver 10.0][SQL Server]Line 1: Length or precision specification 0 is invalid. (SQL-42000) at t/ 20SqlServer.t line 78. # Failed test 'create PERL_DBD_TABLE1 and insert test data' # at t/20SqlServer.t line 82. # DBD::ODBC::db do failed: [unixODBC][Easysoft][SQL Server Driver 10.0][SQL Server]Statement(s) could not be prepared. (SQL-42000) # [unixODBC][Easysoft][SQL Server Driver 10.0][SQL Server]Must declare the scalar variable "@P1". (SQL-42000) # [unixODBC][Easysoft][SQL Server Driver 10.0][SQL Server]Line 1: Length or precision specification 0 is invalid. (SQL-42000) at t/ 20SqlServer.t line 78. # Failed test 'create PERL_DBD_TABLE1 and insert test data' # at t/20SqlServer.t line 82. t/20SqlServer.t . 65/65 # Looks like you failed 2 tests of 65. t/20SqlServer.t . Dubious, test returned 2 (wstat 512, 0x200) Failed 2/65 subtests (less 6 skipped subtests: 57 okay) t/30Oracle.t ok t/40UnicodeRoundTrip.t .. ok t/41Unicode.t ... ok t/pod-coverage.t 1/1 # Test::Pod::Coverage 1.04 required for testing POD coverage t/pod-coverage.t ok t/pod.t . ok t/rt_38977.t 2/11 DBD::ODBC::st execute failed: [unixODBC][Easysoft][SQL Server Driver 10.0][SQL Server]Statement(s) could not be prepared. (SQL-42000) [unixODBC][Easysoft][SQL Server Driver 10.0][SQL Server]Must declare the scalar variable "@P1". (SQL-42000) [unixODBC][Easysoft][SQL Server Driver 10.0][SQL Server]Line 1: Length or precision specification 0 is invalid. (SQL-42000) at t/ rt_38977.t line 90. # Failed test 'execute insert' # at t/rt_38977.t line 93. # Execute for insert into varchar(max) failed with DBD::ODBC::st execute failed: [unixODBC][Easysoft][SQL Server Driver 10.0][SQL Server]Statement(s) could not be prepared. (SQL-42000) # [unixODBC][Easysoft][SQL Server Driver 10.0][SQL Server]Must declare the scalar variable "@P1". (SQL-42000) # [unixODBC][Easysoft][SQL Server Driver 10.0][SQL Server]Line 1: Length or precision specification 0 is invalid. (SQL-42000) at t/ rt_38977.t line 90. # Some SQL Server drivers such as the native client 09.00.1399 driver fail this test with a HY104, "Invalid precision error". You have driver libessqlsrv.so at version 01.01.0004. There is a free upgrade from Microsoft of the native client driver to 10.00.1600 which you will need if you intend to insert into varchar(max) columns. DBD::ODBC::st execute failed: [unixODBC][Easysoft][SQL Server Driver 10.0][SQL Server]Statement(s) could not be prepared. (SQL-42000) [unixODBC][Easysoft][SQL Server Driver 10.0][SQL Server]Must declare the scalar variable "@P1". (SQL-42000) [unixODBC][Easysoft][SQL Server Driver 10.0][SQL Server]Line 1: Length or precision specification 0 is invalid. (SQL-42000) at t/ rt_38977.t line 127. DBD::ODBC::st execute failed: [unixODBC][Easysoft][SQL Server Driver 10.0][SQL Server]Statement(s) could not be prepared. (SQL-42000) [unixODBC][Easysoft][SQL Server Driver 10.0][SQL Server]Must declare the scalar variable "@P1". (SQL-42000) [unixODBC][Easysoft][SQL Server Driver 10.0][SQL Server]Line 1: Length or precision specification 0 is invalid. (SQL-42000) at t/ rt_38977.t line 127. # Looks like you planned 11 tests but ran 10. # Looks like you failed 1 test of 10 run. # Looks like your test exited with 13 just after 10. t/rt_38977.t .
