RE: Problem with Oracle ODBC connection
If sqlplus DOES return data: What version of Oracle ODBC and Oracle client are you using, and what version is oracle on the far end? hm, how can I find out the client version? I see nothing in the ODBC connection about the version, but when I click the Help Button, I get the help for version 10.1.0.2.0. On the Server it should be 10.1, too. We have installed three client versions on our standard Desktops, 8/9/10, but there is no ORACLE_HOME set as I know it from earlier days. So how is defined which client the ODBC connection uses? Lars
RE: Problem with Oracle ODBC connection
All we can say now is that you successfully issue a query on a table and it returns now rows. ok, I have to apologize for my dumbness and stealing your time... I'm not used to oracle and to have to commit... With the PostgreSQL's I usually work with we have Autocommit running, but not on that oracle. I just missed to commit the data to the table ;) Lars
RE: Problem with Oracle ODBC connection
following instrctions from
http://www.easysoft.com/developer/languages/perl/dbd_odbc_tuto
rial_part_1.html#dsn_defn
have you supplied the requested DataSource name ?
the DSN is the last part of the DBI-connect string, i.e.
dbi:ODBC:LABELPRINT. That DSN does exist, and a test connection works.
Also, if the DSN wouldn't work, I wouldn't get the table descriptions in
the ODBC trace...
$ odbcinst -j
unixODBC 2.2.9
DRIVERS: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
USER DATA SOURCES..: /home/martin/.odbc.ini
cat /home/martin/.odbc.ini
I'm on windows here, so DSN works a bit different... (but as said, the
DSN should be ok, otherwise the RaiseError and PrintError should catch
in).
I made a small testscript, just to be sure, and it also fails:
--snip
use strict;
use DBI;
my $msdbh = DBI-connect(dbi:ODBC:LABELPRINT, user, password, {
RaiseError = 1, PrintError = 1, AutoCommit = 1 } ) or do {myError
($DBI::errstr);};
my $sth = $msdbh-prepare('SELECT SPEDITION,KEP FROM
LABELPRINT_SPED_IN');
$sth-execute;
while (my @row=$sth-fetchrow_array) {
print @row\n;
}
--snip---
again, no error message...
Lars
Re: Problem with Oracle ODBC connection
Oeschey, Lars (I/ET-83, extern) wrote:
Hi,
I have a script, that I have to migrate from a MSSQL-DB to Oracle. before I
used an ODBC connection to the MSSQL, so I just changed that ODBC connection.
Everything *should* work as before, but it doesn't. I don't get data from the
Oracle DB. Here's the facts:
connect:
my $msdbh = DBI-connect(dbi:ODBC:LABELPRINT, user, password, { RaiseError = 1, PrintError
= 1, AutoCommit = 1 } ) or do {myError ($DBI::errstr);};
Select:
my $mssth=$msdbh-prepare(SELECT SPEDITION,KEP FROM $speditionen);
$mssth-execute;
my $rh_speditionen=$mssth-fetchall_hashref('SPEDITION');
print Dumper $rh_speditionen;
that is the exact same code as used with the MSSQL, but it doesn't work
anymore... where could I else check for the problem?
I made a ODBC Log, but don't understand it too well... (there's a connect error
in the beginning for example, but afterwards the select works?)
Your fetch did not return any rows - see below:
--ODBC LOG--
perl -w labelpr 11f8-1614 ENTER SQLPrepare
HSTMT 02801EA8
UCHAR * 0x022B5644 [ 44] SELECT SPEDITION,KEP FROM
LABELPRINT_SPED_IN
SDWORD44
this is the sql you issued.
perl -w labelpr 11f8-1614 EXIT SQLPrepare with return code 0
(SQL_SUCCESS)
HSTMT 02801EA8
UCHAR * 0x022B5644 [ 44] SELECT SPEDITION,KEP FROM
LABELPRINT_SPED_IN
SDWORD44
and the driver accepted it ok.
perl -w labelpr 11f8-1614 ENTER SQLExecute
HSTMT 02801EA8
perl -w labelpr 11f8-1614 EXIT SQLExecute with return code 0
(SQL_SUCCESS)
HSTMT 02801EA8
sql now executed
perl -w labelpr 11f8-1614 ENTER SQLRowCount
HSTMT 02801EA8
SQLLEN *0x022B5170
perl -w labelpr 11f8-1614 EXIT SQLRowCount with return code 0
(SQL_SUCCESS)
HSTMT 02801EA8
SQLLEN *0x022B5170 (-1)
perl -w labelpr 11f8-1614 ENTER SQLNumResultCols
HSTMT 02801EA8
SWORD * 0x0140FBB6
perl -w labelpr 11f8-1614 EXIT SQLNumResultCols with return code 0
(SQL_SUCCESS)
HSTMT 02801EA8
SWORD * 0x0140FBB6 (2)
2 columns in result set
various removed.
perl -w labelpr 11f8-1614 ENTER SQLFetch
HSTMT 02801EA8
perl -w labelpr 11f8-1614 EXIT SQLFetch with return code 100
(SQL_NO_DATA_FOUND)
HSTMT 02801EA8
no data in result set. i.e., you select returned no rows.
perl -w labelpr 11f8-1614 ENTER SQLMoreResults
HSTMT 02801EA8
perl -w labelpr 11f8-1614 EXIT SQLMoreResults with return code 100
(SQL_NO_DATA_FOUND)
HSTMT 02801EA8
There are no errors because no errors have occurred. You need to run
your SQL to the database directly and check what you get. Are you in the
schema you expected?
Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
RE: Problem with Oracle ODBC connection
no data in result set. i.e., you select returned no rows. yup, that's what I also see with the small testscript (see other mail). There are no errors because no errors have occurred. You need to run your SQL to the database directly and check what you get. Are you in the schema you expected? hm, I'm in some huge Oracle environment far away from me (some cluster thing I guess), and I just have that one schema assigned to me. I can connect to the same schema with sqldeveloper, though I use the SID name there. I have no idea if I have to switch schema, since the ODBC connection already points to it, I shouldn't? Lars
RE: Problem with Oracle ODBC connection
There are no errors because no errors have occurred. You need to run your SQL to the database directly and check what you get. Are you in the schema you expected? oh, what I forgot: in the ODBC trace I do see the properties of the columns, so I *should* be in the correct schema, no? Lars
Re: Problem with Oracle ODBC connection
Oeschey, Lars (I/ET-83, extern) wrote: There are no errors because no errors have occurred. You need to run your SQL to the database directly and check what you get. Are you in the schema you expected? oh, what I forgot: in the ODBC trace I do see the properties of the columns, so I *should* be in the correct schema, no? Lars But you may just have that table in more than one schema but it is empty in the one you are using - so it is not that simple. What about permissions? Really, we are flying blind here; you need to get connected from sqlplus or sqldeveloper and check what is in the table - connected as the same user. All we can say now is that you successfully issue a query on a table and it returns now rows. Martin -- Martin J. Evans Easysoft Limited http://www.easysoft.com
RE: Problem with Oracle ODBC connection
the error states your DSN is incorrect if you fix DSN name to already configured DSN this should work.. Martin __ Verzicht und Vertraulichkeitanmerkung / Disclaimer and confidentiality note Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen. This message is confidential and may be privileged. If you are not the intended recipient, we kindly ask you to please inform the sender. Any unauthorised dissemination or copying hereof is prohibited. This message serves for information purposes only and shall not have any legally binding effect. Given that e-mails can easily be subject to manipulation, we can not accept any liability for the content provided. Subject: RE: Problem with Oracle ODBC connection Date: Tue, 31 Mar 2009 16:45:25 +0200 From: extern.lars.oesc...@audi.de To: dbi-users@perl.org There are no errors because no errors have occurred. You need to run your SQL to the database directly and check what you get. Are you in the schema you expected? oh, what I forgot: in the ODBC trace I do see the properties of the columns, so I *should* be in the correct schema, no? Lars _ Internet Explorer 8 – Get your Hotmail Accelerated. Download free! http://clk.atdmt.com/MRT/go/141323790/direct/01/
Re: Problem with Oracle ODBC connection
Martin Gainty wrote: the error states your DSN is incorrect if you fix DSN name to already configured DSN this should work.. Martin That is just because DBD::ODBC calls SQLDriverConnect first (since it accepts DSN=xxx and is way more flexible) but if that fails it goes on to try SQLConnect - which in this case worked. You can avoid the wasted call by using dbi:ODBC:DSN=XXX. DBD::ODBC has been like this for ages for some still very good reasons. It is not why Lars is getting no errors or data - he did get connected and successfully issued the query and fetched no data. Martin -- Martin J. Evans Easysoft Limited http://www.easysoft.com ___ Verzicht und Vertraulichkeitanmerkung / Disclaimer and confidentiality note Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen. This message is confidential and may be privileged. If you are not the intended recipient, we kindly ask you to please inform the sender. Any unauthorised dissemination or copying hereof is prohibited. This message serves for information purposes only and shall not have any legally binding effect. Given that e-mails can easily be subject to manipulation, we can not accept any liability for the content provided. Subject: RE: Problem with Oracle ODBC connection Date: Tue, 31 Mar 2009 16:45:25 +0200 From: extern.lars.oesc...@audi.de To: dbi-users@perl.org There are no errors because no errors have occurred. You need to run your SQL to the database directly and check what you get. Are you in the schema you expected? oh, what I forgot: in the ODBC trace I do see the properties of the columns, so I *should* be in the correct schema, no? Lars _ Internet Explorer 8 – Get your Hotmail Accelerated. Download free! http://clk.atdmt.com/MRT/go/141323790/direct/01/
Re: Problem with Oracle ODBC connection
On Mar 31, 2009, at 8:23 AM, Martin Evans wrote: Martin Gainty wrote: the error states your DSN is incorrect if you fix DSN name to already configured DSN this should work.. Martin That is just because DBD::ODBC calls SQLDriverConnect first (since it accepts DSN=xxx and is way more flexible) but if that fails it goes on to try SQLConnect - which in this case worked. You can avoid the wasted call by using dbi:ODBC:DSN=XXX. DBD::ODBC has been like this for ages for some still very good reasons. It is not why Lars is getting no errors or data - he did get connected and successfully issued the query and fetched no data. If sqlplus DOES return data: What version of Oracle ODBC and Oracle client are you using, and what version is oracle on the far end? This is similar to some aggravating results I've gotten on my (admittedly) very esoteric system: connecting from Oracle 11 via database link to an RDB database made available via an RDB OCI server. I have to use an ancient (1.17) version of DBD::Oracle or I get the very same results, a successful query with no rows returned (and I know there are rows there, if I build DBD:Oracle 1.17 against Oracle 8.1.7 libs it works...); sqlplus on my Oracle 11 system does return rows. It's possible that the Oracle ODBC client is doing the same sort of thing to you. -- Bruce Johnson University of Arizona College of Pharmacy Information Technology Group Institutions do not have opinions, merely customs
Re: Problem with Oracle ODBC connection
On 2009-03-31 09:55:42 -0700, Bruce Johnson wrote: If sqlplus DOES return data: What version of Oracle ODBC and Oracle client are you using, and what version is oracle on the far end? This is similar to some aggravating results I've gotten on my (admittedly) very esoteric system: connecting from Oracle 11 via database link to an RDB database made available via an RDB OCI server. I have to use an ancient (1.17) version of DBD::Oracle or I get the very same results, a successful query with no rows returned (and I know there are rows there, if I build DBD:Oracle 1.17 against Oracle 8.1.7 libs it works...); sqlplus on my Oracle 11 system does return rows. I assume that the RDB OCI server emulates an Oracle 8i server (or something even older). Oracle 11 clients aren't compatible with Oracle 8i databases any more. The last client to work with 8i databases was 10g. The 10g client also talks to Oracle 11 servers, so that's currently the most compatible client. hp -- _ | Peter J. Holzer| Auf jedem Computer sollte der Satz Ludwigs II |_|_) | Sysadmin WSR | eingeprägt stehen: Ein ewig Rätsel will ich | | | h...@wsr.ac.at | bleiben, mir und andern. __/ | http://www.hjp.at/ |-- Wolfram Heinrich in desd pgpFuKrD4RhUJ.pgp Description: PGP signature
Re: Problem with Oracle ODBC connection
On Mar 31, 2009, at 10:25 AM, Peter J. Holzer wrote: I assume that the RDB OCI server emulates an Oracle 8i server (or something even older). The current version of the software emulates a Oracle 10.2 server. This isn't the issue; it's something internal to the way that oracle handles a database link, because if you connect directly to the RDB OCI interface using the modern client libs, (or directly to RDB itself through the RDB ODBC or JDBC client software) it works, the only way the problem emerges is through the database link in Oracle 11 to the OCI interface. As I said it's a weird thingit's that the 'thwe query is successful but doesn't return rows' of the OP in this thread is the same symptom that I have. -- Bruce Johnson University of Arizona College of Pharmacy Information Technology Group Institutions do not have opinions, merely customs
RE: Problem with Oracle ODBC connection
can you see the DB_LINK in Oracle select * from user_db_links? Martin I write the songs - Bruce Johnston __ Disclaimer and confidentiality note This message is confidential and may be privileged. If you are not the intended recipient, we kindly ask you to please inform the sender. Any unauthorised dissemination or copying hereof is prohibited. This message serves for information purposes only and shall not have any legally binding effect. Given that e-mails can easily be subject to manipulation, we can not accept any liability for the content provided. From: john...@pharmacy.arizona.edu To: dbi-users@perl.org Subject: Re: Problem with Oracle ODBC connection Date: Tue, 31 Mar 2009 11:56:37 -0700 On Mar 31, 2009, at 10:25 AM, Peter J. Holzer wrote: I assume that the RDB OCI server emulates an Oracle 8i server (or something even older). The current version of the software emulates a Oracle 10.2 server. This isn't the issue; it's something internal to the way that oracle handles a database link, because if you connect directly to the RDB OCI interface using the modern client libs, (or directly to RDB itself through the RDB ODBC or JDBC client software) it works, the only way the problem emerges is through the database link in Oracle 11 to the OCI interface. As I said it's a weird thingit's that the 'thwe query is successful but doesn't return rows' of the OP in this thread is the same symptom that I have. -- Bruce Johnson University of Arizona College of Pharmacy Information Technology Group Institutions do not have opinions, merely customs _ Quick access to Windows Live and your favorite MSN content with Internet Explorer 8. http://ie8.msn.com/microsoft/internet-explorer-8/en-us/ie8.aspx?ocid=B037MSN55C0701A
