cross database queries?
I want to write a query like: select clients.client.client_id, columnar.sales.total_sales, web.page_hits from clients, columnar, web where clients.client_id = columnar.client_id and clients.client_id = web.client_id in a system where 'clients' is actually one or more relational databases, 'columnar' is one or columnar databases, and 'web' is the Apache logs on one or more web servers. The dbi driver would be configured to connect to the correct databases and filter web hits based on 'client_id'. Has somebody written that already? Thanks, Andrew
Re: cross database queries?
On 26.06.13 14:26, Andrew Snyder wrote: I want to write a query like: select clients.client.client_id, columnar.sales.total_sales, web.page_hits from clients, columnar, web where clients.client_id = columnar.client_id and clients.client_id = web.client_id in a system where 'clients' is actually one or more relational databases, 'columnar' is one or columnar databases, and 'web' is the Apache logs on one or more web servers. The dbi driver would be configured to connect to the correct databases and filter web hits based on 'client_id'. Has somebody written that already? As far as I understood: yes. The professional solution is available from http://www.easysoft.com/index.html When you're willing to write some pieces of code, SQL::Statement and DBI::DBD::SqlEngine will help. But in that case - all data is fetched into a Perl based SQL engine and I can confirm that it's highly unoptimized regarding mass data ... Cheers -- Jens Rehsack
Re: cross database queries?
On Wed, Jun 26, 2013 at 7:26 AM, Andrew Snyder a...@dancingjars.com wrote:
I want to write a query like:
select clients.client.client_id, columnar.sales.total_sales, web.page_hits
from clients, columnar, web
where clients.client_id = columnar.client_id
and clients.client_id = web.client_id
in a system where 'clients' is actually one or more relational databases,
'columnar' is one or columnar databases, and 'web' is the Apache logs on
one or more web servers. The dbi driver would be configured to connect to
the correct databases and filter web hits based on 'client_id'.
Has somebody written that already?
Thanks,
Andrew
it seems like the right thing to do here would be to do three queries,
against the three data sources, and store all the results in a hash of
arrays, then dump the results. Any solution that automates it will wind up
doing at least that anyway, and might not be optimized for the join.
Unless there really are so many client IDs that you need to process the
results as a stream or run out of memory, which is unlikely.
while (my ($c_id, $ar) = each %resultz){
$ar-[0] or next; # filter out client_id not appearing in
clients database
print join( \t, $c_id, 0+$ar-[1], 0+$ar-[2]),\n;
}
Two parallel hashes containing the web and columnar results, accessed once
for each result from querying the clients table, would also work.
Re: cross database queries?
On 26.06.13 15:25, David Nicol wrote:
On Wed, Jun 26, 2013 at 7:26 AM, Andrew Snyder a...@dancingjars.com
mailto:a...@dancingjars.com wrote:
I want to write a query like:
select clients.client.client_id, columnar.sales.total_sales,
web.page_hits from clients, columnar, web
where clients.client_id = columnar.client_id
and clients.client_id = web.client_id
in a system where 'clients' is actually one or more relational
databases, 'columnar' is one or columnar databases, and 'web' is the
Apache logs on one or more web servers. The dbi driver would be
configured to connect to the correct databases and filter web hits
based on 'client_id'.
Has somebody written that already?
Thanks,
Andrew
it seems like the right thing to do here would be to do three queries,
against the three data sources, and store all the results in a hash of
arrays, then dump the results. Any solution that automates it will wind
up doing at least that anyway, and might not be optimized for the join.
S::S is not bad in joining tables - it's bad in optimizing queries.
I cannot fetch the smalles table first or first query those tables which
have constants in where clauses. So bad queries might cause gigabytes
on memory are wasted for resulting 50 lines.
That is what I meant by not optimized :)
Unless there really are so many client IDs that you need to process the
results as a stream or run out of memory, which is unlikely.
while (my ($c_id, $ar) = each %resultz){
$ar-[0] or next; # filter out client_id not appearing in
clients database
print join( \t, $c_id, 0+$ar-[1], 0+$ar-[2]),\n;
}
Well - even if not optimized, the implementation of SQL::Statement is
even better. And the datasources for S::S are easy to write - it
finally requires an open_table and fetch_row method.
Two parallel hashes containing the web and columnar results, accessed
once for each result from querying the clients table, would also work.
Yes, that's something S::S can't do out of the box. But it could do, I
have a version of DBD::Sys where during open_table() the query is send
against the data-sources and first fetch_row() synchronized the results
of the appropriate queries.
Cheers
--
Jens Rehsack
Can't locate object method is_initial_req via package Apache::AuthDBI
Dear all, firts of all, I have already looked up this error on the net and I have found some, quite obvious, hints ⁽¹⁾ but they unfortunately don't help. The problem is Apache::AuthDBI uses method is_initial_req from the Apache2::RequestUtil package but this method is not recognized as a method of the Apache2::RequestUtil package. Actually, Apache::AuthDBI does *require* Apache2::RequestUtil, at least for mod_perl2, which is what I am using. The offendine line is n. 217 in AuthDBI.pm: 216return MP2 ? Apache2::Const::OK() : Apache::Constants::OK() 217unless $r-is_initial_req; # only the first internal request When I use DBI for authentication my Apache yelds: Can't locate object method is_initial_req via package Apache::AuthDBI at /usr/lib/perl5/Apache/AuthDBI.pm line 217 So I tried to call the method explicitly, adding to AuthDBI.pm: use Apache2::RequestUtil qw(is_initial_req); and/or: unless $r-Apache2::RequestUtil::is_initial_req; but in this case Apache said: Can't locate object method is_initial_req via package Apache2::RequestUtil at /usr/lib/perl5/Apache/AuthDBI.pm line 217 Last note: I have been using my same code and DBI authentication for years with mod_perl1 and everything was OK. Other information: - I'm running on Debian - Apache 2.2.22 - mod_perl2 2.0.7 - DBI 1.11 Have you any idea? Thank you very much in advance! Andrea Notes - (1) Eg. http://marc.info/?l=apache-modperl-devm=129667924220797 -- Noi siamo poveri perche' siamo onesti -- Cane Rosso
RE: cross database queries?
Date: Wed, 26 Jun 2013 08:26:36 -0400 From: a...@dancingjars.com To: dbi-users@perl.org Subject: cross database queries? I want to write a query like: select clients.client.client_id, columnar.sales.total_sales, web.page_hits from clients, columnar, web where clients.client_id = columnar.client_id and clients.client_id = web.client_id in a system where 'clients' is actually one or more relational databases, 'columnar' is one or columnar databases, and 'web' is the Apache logs on one or more web servers. The dbi driver would be configured to connect to the correct databases and filter web hits based on 'client_id'. Has somebody written that already? Yes but it depends largely on what DB you are hitting against. Really has nothing to do with DBI. The above query would work perfectly fine in oracle as long as the connection user has permission on all the schema. Now if you are asking if the above will work across one or more DBDs and different Databases (oracle, files MySQL) then the answer is no. There are drivers out there for example that let Oracle hit LDAP tables and do joins but that is part of the Oracle DB not DBI and DBD. DBI does not do the join it is the DB that does that. DBI only issues the command Cheers John Thanks, Andrew
ODBC Driver failing?
I suddenly lost the ability to connect to my ODBC database yesterday, after
years of using the same function to establish a connection:
sub dbaseconnect {
if (defined($testing)) {
if ($testing eq YES) {
$dsn = 'dbi:ODBC:test1' ;
print Using test database\n ;
} elsif ($testing eq TRAIN) {
$dsn = 'dbi:ODBC:train1' ;
print Using train1 database\n ;
} else {
$dsn = 'dbi:ODBC:prod1' ;
}
} else {
$dsn = 'dbi:ODBC:prod1' ;
}
$user = 'USER' ;
$passwd = 'PASSWORD' ;
my %adrivers = DBI-available_drivers();
print join(, , %adrivers), \n ;
print connecting to DATABASE $dsn $user $passwd\n ;
$dbh = DBI-connect($dsn, $user, $passwd,
{RaiseError = 1, AutoCommit = 0})
or die Could not connect to database: . DBI-errstr ;
print connected to DATABASE $dsn \n ;
}
So, to gather information about where the failure is, I ran the following
program:
#! /usr/bin/perl
use DBI ;
use DBD::ODBC ;
use strict ;
use warnings ;
print Available Drivers: ;
my @adrivers = DBI-available_drivers();
print join(, , @adrivers), \n ;
print Data Sources: ;
foreach my $driver ( @adrivers ) {
print Driver: $driver\n;
my @dataSources = DBI-data_sources( $driver );
foreach my $dataSource ( @dataSources ) {
print \tData Source is $dataSource\n;
}
print \n;
}
and the output I got was:
Available Drivers: DBM, ExampleP, File, ODBC, Proxy, Sponge
Installed Drivers:
Data Sources: Driver: DBM
Data Source is DBI:DBM:f_dir=.
Data Source is DBI:DBM:f_dir=CIGNA
Data Source is DBI:DBM:f_dir=Logs
Data Source is DBI:DBM:f_dir=ONCOURSE
Data Source is DBI:DBM:f_dir=autemp
Data Source is DBI:DBM:f_dir=config
Data Source is DBI:DBM:f_dir=fh.cob
Data Source is DBI:DBM:f_dir=perlscripts
Data Source is DBI:DBM:f_dir=pndspndwk
Data Source is DBI:DBM:f_dir=prgrun_dir
Data Source is DBI:DBM:f_dir=scripts
Driver: ExampleP
Data Source is dbi:ExampleP:dir=.
Driver: File
Data Source is DBI:File:f_dir=.
Data Source is DBI:File:f_dir=CIGNA
Data Source is DBI:File:f_dir=Logs
Data Source is DBI:File:f_dir=ONCOURSE
Data Source is DBI:File:f_dir=autemp
Data Source is DBI:File:f_dir=config
Data Source is DBI:File:f_dir=fh.cob
Data Source is DBI:File:f_dir=perlscripts
Data Source is DBI:File:f_dir=pndspndwk
Data Source is DBI:File:f_dir=prgrun_dir
Data Source is DBI:File:f_dir=scripts
Driver: ODBC
and the program just hangs when it looks for data sources using the ODBC
driver. So, I suspect that there are issues with the ODBC driver. Here are
the versions of the various DBI module components:
perl -MDBI -e 'DBI-installed_versions'
Perl: 5.008008(PA-RISC1.1-thread-multi)
OS : hpux(11.00)
DBI : 1.50
DBD::Sponge : 11.10
DBD::Proxy : install_driver(Proxy) failed: Can't locate
RPC/PlClient.pm in @INC
DBD::ODBC : 1.14
DBD::File : 0.33
DBD::ExampleP : 11.12
DBD::DBM: 0.03
I imagine that I may not have the latest versions of everything, and
updates are probably in order, but, while updates are desirable, I'd like
to be sure that I'm addressing the root cause of the problem, so I get it
resolved. This issue affects a lot of programs, and is critical to our
business.
Any help or suggestions will be greatly appreciated.
Re: ODBC Driver failing?
On 6/26/2013 9:28 AM, Dan Bent wrote: and the program just hangs when it looks for data sources using the ODBC driver. So, I suspect that there are issues with the ODBC driver. Here are the versions of the various DBI module components: do you have any non-perl ODBC tools? I suspect (purely on a hunch) that this problem isn't with DBI or Perl, but with the OS level UnixODBC implementation and its configuration. Having never seen or touched an HP-UX system, I can't offer any more suggestions. -- john r pierce 37N 122W somewhere on the middle of the left coast
Re: ODBC Driver failing?
On Wed, Jun 26, 2013 at 9:28 AM, Dan Bent db...@comcast.net wrote: I suddenly lost the ability to connect to my ODBC database yesterday, after years of using the same function to establish a connection: So, the question you must ask yourself is: What changed yesterday? Or, if not yesterday, since the previous time when you successfully used the code. Something crucial changed. If it wasn't the Perl plus ODBC infrastructure, then what changed outside that? The DBMS? The networking? Change analysis is likely to get you to the answer quicker than anything else. -- Jonathan Leffler jonathan.leff...@gmail.com #include disclaimer.h Guardian of DBD::Informix - v2013.0521 - http://dbi.perl.org Blessed are we who can laugh at ourselves, for we shall never cease to be amused.
Re: ODBC Driver failing?
Big thanks for the rapid response! non-Perl ODBC tools? I'm not sure. I've been working with Perl for a number of years and may have tools I am not aware of. I do have Windows users who access the same database without any issues, but I don't know of any tools I have in UNIX other than Perl. I haven't looked at the UnixODBC stuff, so that gives me an avenue to investigate. On Wed, Jun 26, 2013 at 11:34 AM, John R Pierce pie...@hogranch.com wrote: On 6/26/2013 9:28 AM, Dan Bent wrote: and the program just hangs when it looks for data sources using the ODBC driver. So, I suspect that there are issues with the ODBC driver. Here are the versions of the various DBI module components: do you have any non-perl ODBC tools? I suspect (purely on a hunch) that this problem isn't with DBI or Perl, but with the OS level UnixODBC implementation and its configuration. Having never seen or touched an HP-UX system, I can't offer any more suggestions. -- john r pierce 37N 122W somewhere on the middle of the left coast
Re: ODBC Driver failing?
On 26/06/2013 17:28, Dan Bent wrote:
I suddenly lost the ability to connect to my ODBC database yesterday,
after years of using the same function to establish a connection:
sub dbaseconnect {
if (defined($testing)) {
if ($testing eq YES) {
$dsn = 'dbi:ODBC:test1' ;
print Using test database\n ;
} elsif ($testing eq TRAIN) {
$dsn = 'dbi:ODBC:train1' ;
print Using train1 database\n ;
} else {
$dsn = 'dbi:ODBC:prod1' ;
}
} else {
$dsn = 'dbi:ODBC:prod1' ;
}
$user = 'USER' ;
$passwd = 'PASSWORD' ;
my %adrivers = DBI-available_drivers();
print join(, , %adrivers), \n ;
print connecting to DATABASE $dsn $user $passwd\n ;
$dbh = DBI-connect($dsn, $user, $passwd,
{RaiseError = 1, AutoCommit = 0})
or die Could not connect to database: . DBI-errstr ;
print connected to DATABASE $dsn \n ;
}
So, to gather information about where the failure is, I ran the
following program:
#! /usr/bin/perl
use DBI ;
use DBD::ODBC ;
use strict ;
use warnings ;
print Available Drivers: ;
my @adrivers = DBI-available_drivers();
print join(, , @adrivers), \n ;
print Data Sources: ;
foreach my $driver ( @adrivers ) {
print Driver: $driver\n;
my @dataSources = DBI-data_sources( $driver );
foreach my $dataSource ( @dataSources ) {
print \tData Source is $dataSource\n;
}
print \n;
}
and the output I got was:
Available Drivers: DBM, ExampleP, File, ODBC, Proxy, Sponge
Installed Drivers:
Data Sources: Driver: DBM
Data Source is DBI:DBM:f_dir=.
Data Source is DBI:DBM:f_dir=CIGNA
Data Source is DBI:DBM:f_dir=Logs
Data Source is DBI:DBM:f_dir=ONCOURSE
Data Source is DBI:DBM:f_dir=autemp
Data Source is DBI:DBM:f_dir=config
Data Source is DBI:DBM:f_dir=fh.cob
Data Source is DBI:DBM:f_dir=perlscripts
Data Source is DBI:DBM:f_dir=pndspndwk
Data Source is DBI:DBM:f_dir=prgrun_dir
Data Source is DBI:DBM:f_dir=scripts
Driver: ExampleP
Data Source is dbi:ExampleP:dir=.
Driver: File
Data Source is DBI:File:f_dir=.
Data Source is DBI:File:f_dir=CIGNA
Data Source is DBI:File:f_dir=Logs
Data Source is DBI:File:f_dir=ONCOURSE
Data Source is DBI:File:f_dir=autemp
Data Source is DBI:File:f_dir=config
Data Source is DBI:File:f_dir=fh.cob
Data Source is DBI:File:f_dir=perlscripts
Data Source is DBI:File:f_dir=pndspndwk
Data Source is DBI:File:f_dir=prgrun_dir
Data Source is DBI:File:f_dir=scripts
Driver: ODBC
and the program just hangs when it looks for data sources using the ODBC
driver. So, I suspect that there are issues with the ODBC driver. Here
are the versions of the various DBI module components:
perl -MDBI -e 'DBI-installed_versions'
Perl: 5.008008(PA-RISC1.1-thread-multi)
OS : hpux(11.00)
DBI : 1.50
DBD::Sponge : 11.10
DBD::Proxy : install_driver(Proxy) failed: Can't locate
RPC/PlClient.pm in @INC
DBD::ODBC : 1.14
DBD::File : 0.33
DBD::ExampleP : 11.12
DBD::DBM: 0.03
I imagine that I may not have the latest versions of everything, and
updates are probably in order, but, while updates are desirable, I'd
like to be sure that I'm addressing the root cause of the problem, so I
get it resolved. This issue affects a lot of programs, and is critical
to our business.
Any help or suggestions will be greatly appreciated.
You are in deed running VERY old versions - especially of DBD::ODBC.
You first need to think about what Jonathan said - he's probably right
that working out what changed yesterday is probably going to give the
quickest result.
Assuming you cannot find anything here are some suggestions.
DBD::ODBC is usually linked to an ODBC driver manager but back in the
days of 1.14 people still linked directly to an ODBC driver sometimes -
ODBC drivers did not support enumerating DSNs - only the driver manager
does that. So first thing is hwo was DBD::ODBC built? If you don't know
that look for ODBC.so in your perl tree and run the HPUX equivalent of
Linux's ldd command on it to find what libraries it depends on (right
now I cannot remember what the command is).
Once you've done that if the answer is libodbc.so.something then you are
probably using the unixODBC driver manager. In that case you should
hopefully have an isql binary and you should have an odbc.ini and
odbcinst.ini defining your drivers and DSNs. What is in those files. Can
you run:
isql -v TEST1 username password
isql -v TRAIN1 username password
isql -v PROD1 username password
as you didn't say which one you are using?
If you get back with this info I'll help more.
Martin
--
Martin J. Evans
Wetherby, UK
Re: ODBC Driver failing?
I agree, and I've been trying to identify what changed yesterday morning. The database, Perl,and the program all reside on the same machine, so I think we can rule out network issues. As far as I know, the DBMS, Perl and ODBC infrastructure have been stable for quite a while, and I haven't tinkered with any of that in recent memory. However, there is another administrator who might have unknowingly deleted files, and there are many users with access to this host (though most can only run one application, and shouldn't be able to get to the ODBC config stuff). I am really the only user who uses Perl and ODBC in the Unix environment. I suppose I could compare the backup tape with what is currently on the system to see if there are files obviously missing, but I'm not exactly sure what I would be looking for, and there could be thousands of files to compare. Am I thinking clearly on this? On Wed, Jun 26, 2013 at 11:46 AM, Jonathan Leffler jonathan.leff...@gmail.com wrote: On Wed, Jun 26, 2013 at 9:28 AM, Dan Bent db...@comcast.net wrote: I suddenly lost the ability to connect to my ODBC database yesterday, after years of using the same function to establish a connection: So, the question you must ask yourself is: What changed yesterday? Or, if not yesterday, since the previous time when you successfully used the code. Something crucial changed. If it wasn't the Perl plus ODBC infrastructure, then what changed outside that? The DBMS? The networking? Change analysis is likely to get you to the answer quicker than anything else. -- Jonathan Leffler jonathan.leff...@gmail.com #include disclaimer.h Guardian of DBD::Informix - v2013.0521 - http://dbi.perl.org Blessed are we who can laugh at ourselves, for we shall never cease to be amused.
Re: ODBC Driver failing?
On 26/06/2013 18:42, Dan Bent wrote: I agree, and I've been trying to identify what changed yesterday morning. The database, Perl,and the program all reside on the same machine, so I think we can rule out network issues. As far as I know, the DBMS, Perl and ODBC infrastructure have been stable for quite a while, and I haven't tinkered with any of that in recent memory. However, there is another administrator who might have unknowingly deleted files, and there are many users with access to this host (though most can only run one application, and shouldn't be able to get to the ODBC config stuff). I am really the only user who uses Perl and ODBC in the Unix environment. I suppose I could compare the backup tape with what is currently on the system to see if there are files obviously missing, but I'm not exactly sure what I would be looking for, and there could be thousands of files to compare. Am I thinking clearly on this? The files you want to look for are: odbc.ini odbcinst.ini any shared object referenced in any odbcinst.ini file any file DBD::ODBC's ODBC.sl depends on (e.g., libodbc.sl or the driver shared object) If you are not using unixODBC driver manager and you are using iODBC driver manager it is libiodbc.sl. Then you might have had something in your environment e.g., most ODBC driver managers will follow ODBCINI env var or ODBCINSTINI etc. Some ODBC drivers need env vars setting in the environment e.g., DB2 often needs DB2INSTANCE. SO also check any profile files for the user doing ODBC. But if you answer the other questions in my other reply I might be able to help more. Martin On Wed, Jun 26, 2013 at 11:46 AM, Jonathan Leffler jonathan.leff...@gmail.com mailto:jonathan.leff...@gmail.com wrote: On Wed, Jun 26, 2013 at 9:28 AM, Dan Bent db...@comcast.net mailto:db...@comcast.net wrote: I suddenly lost the ability to connect to my ODBC database yesterday, after years of using the same function to establish a connection: So, the question you must ask yourself is: What changed yesterday? Or, if not yesterday, since the previous time when you successfully used the code. Something crucial changed. If it wasn't the Perl plus ODBC infrastructure, then what changed outside that? The DBMS? The networking? Change analysis is likely to get you to the answer quicker than anything else. -- Jonathan Leffler jonathan.leff...@gmail.com mailto:jonathan.leff...@gmail.com #include disclaimer.h Guardian of DBD::Informix - v2013.0521 - http://dbi.perl.org Blessed are we who can laugh at ourselves, for we shall never cease to be amused.
Re: ODBC Driver failing?
Big thanks!
I did this:
ldd
/opt/perl_32/lib/site_perl/5.8.8/PA-RISC1.1-thread-multi/auto/DBD/ODBC/ODBC.sl
and got:
/usr/local/liant/lib/libodbc.sl.1 =/usr/local/liant/lib/libodbc.sl.1
/usr/lib/libc.2 = /usr/lib/libc.2
/usr/lib/libdld.2 =/usr/lib/libdld.2
/usr/lib/libc.2 = /usr/lib/libc.2
/usr/lib/libpthread.1 =/usr/lib/libpthread.1
I tried isql -v prod1 username password
and it just hung like other attempts to access the database. No error
messages.
On Wed, Jun 26, 2013 at 12:11 PM, Martin J. Evans boh...@ntlworld.comwrote:
On 26/06/2013 17:28, Dan Bent wrote:
I suddenly lost the ability to connect to my ODBC database yesterday,
after years of using the same function to establish a connection:
sub dbaseconnect {
if (defined($testing)) {
if ($testing eq YES) {
$dsn = 'dbi:ODBC:test1' ;
print Using test database\n ;
} elsif ($testing eq TRAIN) {
$dsn = 'dbi:ODBC:train1' ;
print Using train1 database\n ;
} else {
$dsn = 'dbi:ODBC:prod1' ;
}
} else {
$dsn = 'dbi:ODBC:prod1' ;
}
$user = 'USER' ;
$passwd = 'PASSWORD' ;
my %adrivers = DBI-available_drivers();
print join(, , %adrivers), \n ;
print connecting to DATABASE $dsn $user $passwd\n ;
$dbh = DBI-connect($dsn, $user, $passwd,
{RaiseError = 1, AutoCommit = 0})
or die Could not connect to database: . DBI-errstr ;
print connected to DATABASE $dsn \n ;
}
So, to gather information about where the failure is, I ran the
following program:
#! /usr/bin/perl
use DBI ;
use DBD::ODBC ;
use strict ;
use warnings ;
print Available Drivers: ;
my @adrivers = DBI-available_drivers();
print join(, , @adrivers), \n ;
print Data Sources: ;
foreach my $driver ( @adrivers ) {
print Driver: $driver\n;
my @dataSources = DBI-data_sources( $driver );
foreach my $dataSource ( @dataSources ) {
print \tData Source is $dataSource\n;
}
print \n;
}
and the output I got was:
Available Drivers: DBM, ExampleP, File, ODBC, Proxy, Sponge
Installed Drivers:
Data Sources: Driver: DBM
Data Source is DBI:DBM:f_dir=.
Data Source is DBI:DBM:f_dir=CIGNA
Data Source is DBI:DBM:f_dir=Logs
Data Source is DBI:DBM:f_dir=ONCOURSE
Data Source is DBI:DBM:f_dir=autemp
Data Source is DBI:DBM:f_dir=config
Data Source is DBI:DBM:f_dir=fh.cob
Data Source is DBI:DBM:f_dir=perlscripts
Data Source is DBI:DBM:f_dir=pndspndwk
Data Source is DBI:DBM:f_dir=prgrun_dir
Data Source is DBI:DBM:f_dir=scripts
Driver: ExampleP
Data Source is dbi:ExampleP:dir=.
Driver: File
Data Source is DBI:File:f_dir=.
Data Source is DBI:File:f_dir=CIGNA
Data Source is DBI:File:f_dir=Logs
Data Source is DBI:File:f_dir=ONCOURSE
Data Source is DBI:File:f_dir=autemp
Data Source is DBI:File:f_dir=config
Data Source is DBI:File:f_dir=fh.cob
Data Source is DBI:File:f_dir=perlscripts
Data Source is DBI:File:f_dir=pndspndwk
Data Source is DBI:File:f_dir=prgrun_dir
Data Source is DBI:File:f_dir=scripts
Driver: ODBC
and the program just hangs when it looks for data sources using the ODBC
driver. So, I suspect that there are issues with the ODBC driver. Here
are the versions of the various DBI module components:
perl -MDBI -e 'DBI-installed_versions'
Perl: 5.008008(PA-RISC1.1-thread-multi)
OS : hpux(11.00)
DBI : 1.50
DBD::Sponge : 11.10
DBD::Proxy : install_driver(Proxy) failed: Can't locate
RPC/PlClient.pm in @INC
DBD::ODBC : 1.14
DBD::File : 0.33
DBD::ExampleP : 11.12
DBD::DBM: 0.03
I imagine that I may not have the latest versions of everything, and
updates are probably in order, but, while updates are desirable, I'd
like to be sure that I'm addressing the root cause of the problem, so I
get it resolved. This issue affects a lot of programs, and is critical
to our business.
Any help or suggestions will be greatly appreciated.
You are in deed running VERY old versions - especially of DBD::ODBC.
You first need to think about what Jonathan said - he's probably right
that working out what changed yesterday is probably going to give the
quickest result.
Assuming you cannot find anything here are some suggestions.
DBD::ODBC is usually linked to an ODBC driver manager but back in the days
of 1.14 people still linked directly to an ODBC driver sometimes - ODBC
drivers did not support enumerating DSNs - only the driver manager does
that. So first thing is hwo was DBD::ODBC built? If you don't know that
look for ODBC.so in your
Re: ODBC Driver failing?
On 26/06/2013 19:35, Dan Bent wrote:
Big thanks!
I did this:
ldd
/opt/perl_32/lib/site_perl/5.8.8/PA-RISC1.1-thread-multi/auto/DBD/ODBC/ODBC.sl
and got:
/usr/local/liant/lib/libodbc.sl.1 =/usr/local/liant/lib/libodbc.sl.1
/usr/lib/libc.2 = /usr/lib/libc.2
/usr/lib/libdld.2 =/usr/lib/libdld.2
/usr/lib/libc.2 = /usr/lib/libc.2
/usr/lib/libpthread.1 =/usr/lib/libpthread.1
hmm - never heard of liant. It looks like the unixODBC driver manager
but I've never seen it installed in that location. Also, I see you've
got isql and that comes with unixODBC. Have you also got a binary called
odbcinst and if you have output from odbcinst -j would be useful.
I tried isql -v prod1 username password
and it just hung like other attempts to access the database. No error
messages.
OK, so we've ruled out a change in Perl and DBI and DBD::ODBC as it is
still going wrong without them. If this really is unixODBC you should
have an odbc.ini and odbcinst.ini file probably in /usr/local/liant/etc
or /usr/local/etc of /etc. What is in those files? There may also be a
.odbc.ini in the users home dir.
When we see the contents of those files we'll have a better idea of what
driver you are using and the shared library used so you can check that
too to see if it has been updated.
You could enable unixODBC tracing but it rarely outputs much before
connection is complete. I think you need to find the equivalent of
strace on HPUX and run it on the isql command to see what system calls
are being made.
Did you say everything is on one box, so networking off that box cannot
be the issue?
Martin
On Wed, Jun 26, 2013 at 12:11 PM, Martin J. Evans boh...@ntlworld.com
mailto:boh...@ntlworld.com wrote:
On 26/06/2013 17:28, Dan Bent wrote:
I suddenly lost the ability to connect to my ODBC database
yesterday,
after years of using the same function to establish a connection:
sub dbaseconnect {
if (defined($testing)) {
if ($testing eq YES) {
$dsn = 'dbi:ODBC:test1' ;
print Using test database\n ;
} elsif ($testing eq TRAIN) {
$dsn = 'dbi:ODBC:train1' ;
print Using train1 database\n ;
} else {
$dsn = 'dbi:ODBC:prod1' ;
}
} else {
$dsn = 'dbi:ODBC:prod1' ;
}
$user = 'USER' ;
$passwd = 'PASSWORD' ;
my %adrivers = DBI-available_drivers();
print join(, , %adrivers), \n ;
print connecting to DATABASE $dsn $user $passwd\n ;
$dbh = DBI-connect($dsn, $user, $passwd,
{RaiseError = 1, AutoCommit = 0})
or die Could not connect to database: . DBI-errstr ;
print connected to DATABASE $dsn \n ;
}
So, to gather information about where the failure is, I ran the
following program:
#! /usr/bin/perl
use DBI ;
use DBD::ODBC ;
use strict ;
use warnings ;
print Available Drivers: ;
my @adrivers = DBI-available_drivers();
print join(, , @adrivers), \n ;
print Data Sources: ;
foreach my $driver ( @adrivers ) {
print Driver: $driver\n;
my @dataSources = DBI-data_sources( $driver );
foreach my $dataSource ( @dataSources ) {
print \tData Source is $dataSource\n;
}
print \n;
}
and the output I got was:
Available Drivers: DBM, ExampleP, File, ODBC, Proxy, Sponge
Installed Drivers:
Data Sources: Driver: DBM
Data Source is DBI:DBM:f_dir=.
Data Source is DBI:DBM:f_dir=CIGNA
Data Source is DBI:DBM:f_dir=Logs
Data Source is DBI:DBM:f_dir=ONCOURSE
Data Source is DBI:DBM:f_dir=autemp
Data Source is DBI:DBM:f_dir=config
Data Source is DBI:DBM:f_dir=fh.cob
Data Source is DBI:DBM:f_dir=perlscripts
Data Source is DBI:DBM:f_dir=pndspndwk
Data Source is DBI:DBM:f_dir=prgrun_dir
Data Source is DBI:DBM:f_dir=scripts
Driver: ExampleP
Data Source is dbi:ExampleP:dir=.
Driver: File
Data Source is DBI:File:f_dir=.
Data Source is DBI:File:f_dir=CIGNA
Data Source is DBI:File:f_dir=Logs
Data Source is DBI:File:f_dir=ONCOURSE
Data Source is DBI:File:f_dir=autemp
Data Source is DBI:File:f_dir=config
Data Source is DBI:File:f_dir=fh.cob
Data Source is
Re: ODBC Driver failing?
can you run strace, or the HP equivalent, to see what system calls are
getting made and what files are and are not found?
the system call that doesn't return is likely where the problem is.
Check the time stamp of any file that is loaded to see if it was changed
recently. Any file that it looks for in more than one location is a
candidate for a file that might have been accidentally deleted.
On 06/26/2013 02:35 PM, Dan Bent wrote:
Big thanks!
I did this:
ldd
/opt/perl_32/lib/site_perl/5.8.8/PA-RISC1.1-thread-multi/auto/DBD/ODBC/ODBC.sl
and got:
/usr/local/liant/lib/libodbc.sl.1 = /usr/local/liant/lib/libodbc.sl.1
/usr/lib/libc.2 = /usr/lib/libc.2
/usr/lib/libdld.2 =/usr/lib/libdld.2
/usr/lib/libc.2 = /usr/lib/libc.2
/usr/lib/libpthread.1 = /usr/lib/libpthread.1
I tried isql -v prod1 username password
and it just hung like other attempts to access the database. No error
messages.
On Wed, Jun 26, 2013 at 12:11 PM, Martin J. Evans boh...@ntlworld.com
mailto:boh...@ntlworld.com wrote:
On 26/06/2013 17:28, Dan Bent wrote:
I suddenly lost the ability to connect to my ODBC database
yesterday,
after years of using the same function to establish a connection:
sub dbaseconnect {
if (defined($testing)) {
if ($testing eq YES) {
$dsn = 'dbi:ODBC:test1' ;
print Using test database\n ;
} elsif ($testing eq TRAIN) {
$dsn = 'dbi:ODBC:train1' ;
print Using train1 database\n ;
} else {
$dsn = 'dbi:ODBC:prod1' ;
}
} else {
$dsn = 'dbi:ODBC:prod1' ;
}
$user = 'USER' ;
$passwd = 'PASSWORD' ;
my %adrivers = DBI-available_drivers();
print join(, , %adrivers), \n ;
print connecting to DATABASE $dsn $user $passwd\n ;
$dbh = DBI-connect($dsn, $user, $passwd,
{RaiseError = 1, AutoCommit = 0})
or die Could not connect to database: . DBI-errstr ;
print connected to DATABASE $dsn \n ;
}
So, to gather information about where the failure is, I ran the
following program:
#! /usr/bin/perl
use DBI ;
use DBD::ODBC ;
use strict ;
use warnings ;
print Available Drivers: ;
my @adrivers = DBI-available_drivers();
print join(, , @adrivers), \n ;
print Data Sources: ;
foreach my $driver ( @adrivers ) {
print Driver: $driver\n;
my @dataSources = DBI-data_sources( $driver );
foreach my $dataSource ( @dataSources ) {
print \tData Source is $dataSource\n;
}
print \n;
}
and the output I got was:
Available Drivers: DBM, ExampleP, File, ODBC, Proxy, Sponge
Installed Drivers:
Data Sources: Driver: DBM
Data Source is DBI:DBM:f_dir=.
Data Source is DBI:DBM:f_dir=CIGNA
Data Source is DBI:DBM:f_dir=Logs
Data Source is DBI:DBM:f_dir=ONCOURSE
Data Source is DBI:DBM:f_dir=autemp
Data Source is DBI:DBM:f_dir=config
Data Source is DBI:DBM:f_dir=fh.cob
Data Source is DBI:DBM:f_dir=perlscripts
Data Source is DBI:DBM:f_dir=pndspndwk
Data Source is DBI:DBM:f_dir=prgrun_dir
Data Source is DBI:DBM:f_dir=scripts
Driver: ExampleP
Data Source is dbi:ExampleP:dir=.
Driver: File
Data Source is DBI:File:f_dir=.
Data Source is DBI:File:f_dir=CIGNA
Data Source is DBI:File:f_dir=Logs
Data Source is DBI:File:f_dir=ONCOURSE
Data Source is DBI:File:f_dir=autemp
Data Source is DBI:File:f_dir=config
Data Source is DBI:File:f_dir=fh.cob
Data Source is DBI:File:f_dir=perlscripts
Data Source is DBI:File:f_dir=pndspndwk
Data Source is DBI:File:f_dir=prgrun_dir
Data Source is DBI:File:f_dir=scripts
Driver: ODBC
and the program just hangs when it looks for data sources
using the ODBC
driver. So, I suspect that there are issues with the ODBC
driver. Here
are the versions of the various DBI module components:
perl -MDBI -e 'DBI-installed_versions'
Perl: 5.008008(PA-RISC1.1-thread-multi)
OS : hpux(11.00)
DBI : 1.50
DBD::Sponge : 11.10
DBD::Proxy : install_driver(Proxy) failed: Can't locate
RPC/PlClient.pm
Re: ODBC Driver failing?
I have strace, but don't know how to use it.
On Wed, Jun 26, 2013 at 2:01 PM, Martin J. Evans boh...@ntlworld.comwrote:
On 26/06/2013 19:35, Dan Bent wrote:
Big thanks!
I did this:
ldd
/opt/perl_32/lib/site_perl/5.**8.8/PA-RISC1.1-thread-multi/**
auto/DBD/ODBC/ODBC.sl
and got:
/usr/local/liant/lib/libodbc.**sl.1 =/usr/local/liant/lib/libodbc.
**sl.1
/usr/lib/libc.2 = /usr/lib/libc.2
/usr/lib/libdld.2 =/usr/lib/libdld.2
/usr/lib/libc.2 = /usr/lib/libc.2
/usr/lib/libpthread.1 =/usr/lib/libpthread.1
hmm - never heard of liant. It looks like the unixODBC driver manager
but I've never seen it installed in that location. Also, I see you've got
isql and that comes with unixODBC. Have you also got a binary called
odbcinst and if you have output from odbcinst -j would be useful.
I tried isql -v prod1 username password
and it just hung like other attempts to access the database. No error
messages.
OK, so we've ruled out a change in Perl and DBI and DBD::ODBC as it is
still going wrong without them. If this really is unixODBC you should have
an odbc.ini and odbcinst.ini file probably in /usr/local/liant/etc or
/usr/local/etc of /etc. What is in those files? There may also be a
.odbc.ini in the users home dir.
When we see the contents of those files we'll have a better idea of what
driver you are using and the shared library used so you can check that too
to see if it has been updated.
You could enable unixODBC tracing but it rarely outputs much before
connection is complete. I think you need to find the equivalent of strace
on HPUX and run it on the isql command to see what system calls are being
made.
Did you say everything is on one box, so networking off that box cannot be
the issue?
Martin
On Wed, Jun 26, 2013 at 12:11 PM, Martin J. Evans boh...@ntlworld.com
mailto:boh...@ntlworld.com wrote:
On 26/06/2013 17:28, Dan Bent wrote:
I suddenly lost the ability to connect to my ODBC database
yesterday,
after years of using the same function to establish a connection:
sub dbaseconnect {
if (defined($testing)) {
if ($testing eq YES) {
$dsn = 'dbi:ODBC:test1' ;
print Using test database\n ;
} elsif ($testing eq TRAIN) {
$dsn = 'dbi:ODBC:train1' ;
print Using train1 database\n ;
} else {
$dsn = 'dbi:ODBC:prod1' ;
}
} else {
$dsn = 'dbi:ODBC:prod1' ;
}
$user = 'USER' ;
$passwd = 'PASSWORD' ;
my %adrivers = DBI-available_drivers();
print join(, , %adrivers), \n ;
print connecting to DATABASE $dsn $user $passwd\n ;
$dbh = DBI-connect($dsn, $user, $passwd,
{RaiseError = 1, AutoCommit = 0})
or die Could not connect to database: . DBI-errstr ;
print connected to DATABASE $dsn \n ;
}
So, to gather information about where the failure is, I ran the
following program:
#! /usr/bin/perl
use DBI ;
use DBD::ODBC ;
use strict ;
use warnings ;
print Available Drivers: ;
my @adrivers = DBI-available_drivers();
print join(, , @adrivers), \n ;
print Data Sources: ;
foreach my $driver ( @adrivers ) {
print Driver: $driver\n;
my @dataSources = DBI-data_sources( $driver );
foreach my $dataSource ( @dataSources ) {
print \tData Source is $dataSource\n;
}
print \n;
}
and the output I got was:
Available Drivers: DBM, ExampleP, File, ODBC, Proxy, Sponge
Installed Drivers:
Data Sources: Driver: DBM
Data Source is DBI:DBM:f_dir=.
Data Source is DBI:DBM:f_dir=CIGNA
Data Source is DBI:DBM:f_dir=Logs
Data Source is DBI:DBM:f_dir=ONCOURSE
Data Source is DBI:DBM:f_dir=autemp
Data Source is DBI:DBM:f_dir=config
Data Source is DBI:DBM:f_dir=fh.cob
Data Source is DBI:DBM:f_dir=perlscripts
Data Source is DBI:DBM:f_dir=pndspndwk
Data Source is DBI:DBM:f_dir=prgrun_dir
Data Source is DBI:DBM:f_dir=scripts
Driver: ExampleP
Data Source is dbi:ExampleP:dir=.
Driver: File
Data Source is DBI:File:f_dir=.
Data Source is DBI:File:f_dir=CIGNA
Data Source is DBI:File:f_dir=Logs
Data Source is DBI:File:f_dir=ONCOURSE
Re: cross database queries?
Thank you all for your replies. I am indeed talking about working with multiple back ends (oracle, mysql, or any valid DBI module) in the same query. I'll post any follow-up questions to the dev list. Andrew On 06/26/2013 08:26 AM, Andrew Snyder wrote: I want to write a query like: select clients.client.client_id, columnar.sales.total_sales, web.page_hits from clients, columnar, web where clients.client_id = columnar.client_id and clients.client_id = web.client_id in a system where 'clients' is actually one or more relational databases, 'columnar' is one or columnar databases, and 'web' is the Apache logs on one or more web servers. The dbi driver would be configured to connect to the correct databases and filter web hits based on 'client_id'. Has somebody written that already? Thanks, Andrew
Re: ODBC Driver failing?
On 26/06/2013 20:29, Dan Bent wrote:
I have strace, but don't know how to use it.
Usually something like:
strace -o out.log isql -v mydsn username password
then after it hangs hit ctrl/c and look at (paste here) the last 100
lines of out.log
Martin
On Wed, Jun 26, 2013 at 2:01 PM, Martin J. Evans boh...@ntlworld.com
mailto:boh...@ntlworld.com wrote:
On 26/06/2013 19:35, Dan Bent wrote:
Big thanks!
I did this:
ldd
/opt/perl_32/lib/site_perl/5.__8.8/PA-RISC1.1-thread-multi/__auto/DBD/ODBC/ODBC.sl
and got:
/usr/local/liant/lib/libodbc.__sl.1 =
/usr/local/liant/lib/libodbc.__sl.1
/usr/lib/libc.2 = /usr/lib/libc.2
/usr/lib/libdld.2 =/usr/lib/libdld.2
/usr/lib/libc.2 = /usr/lib/libc.2
/usr/lib/libpthread.1 =/usr/lib/libpthread.1
hmm - never heard of liant. It looks like the unixODBC driver
manager but I've never seen it installed in that location. Also, I
see you've got isql and that comes with unixODBC. Have you also got
a binary called odbcinst and if you have output from odbcinst -j
would be useful.
I tried isql -v prod1 username password
and it just hung like other attempts to access the database. No
error
messages.
OK, so we've ruled out a change in Perl and DBI and DBD::ODBC as it
is still going wrong without them. If this really is unixODBC you
should have an odbc.ini and odbcinst.ini file probably in
/usr/local/liant/etc or /usr/local/etc of /etc. What is in those
files? There may also be a .odbc.ini in the users home dir.
When we see the contents of those files we'll have a better idea of
what driver you are using and the shared library used so you can
check that too to see if it has been updated.
You could enable unixODBC tracing but it rarely outputs much before
connection is complete. I think you need to find the equivalent of
strace on HPUX and run it on the isql command to see what system
calls are being made.
Did you say everything is on one box, so networking off that box
cannot be the issue?
Martin
On Wed, Jun 26, 2013 at 12:11 PM, Martin J. Evans
boh...@ntlworld.com mailto:boh...@ntlworld.com
mailto:boh...@ntlworld.com mailto:boh...@ntlworld.com wrote:
On 26/06/2013 17:28, Dan Bent wrote:
I suddenly lost the ability to connect to my ODBC database
yesterday,
after years of using the same function to establish a
connection:
sub dbaseconnect {
if (defined($testing)) {
if ($testing eq YES) {
$dsn = 'dbi:ODBC:test1' ;
print Using test database\n ;
} elsif ($testing eq TRAIN) {
$dsn = 'dbi:ODBC:train1' ;
print Using train1 database\n ;
} else {
$dsn = 'dbi:ODBC:prod1' ;
}
} else {
$dsn = 'dbi:ODBC:prod1' ;
}
$user = 'USER' ;
$passwd = 'PASSWORD' ;
my %adrivers = DBI-available_drivers();
print join(, , %adrivers), \n ;
print connecting to DATABASE $dsn $user
$passwd\n ;
$dbh = DBI-connect($dsn, $user, $passwd,
{RaiseError = 1, AutoCommit = 0})
or die Could not connect to database: .
DBI-errstr ;
print connected to DATABASE $dsn \n ;
}
So, to gather information about where the failure is, I
ran the
following program:
#! /usr/bin/perl
use DBI ;
use DBD::ODBC ;
use strict ;
use warnings ;
print Available Drivers: ;
my @adrivers = DBI-available_drivers();
print join(, , @adrivers), \n ;
print Data Sources: ;
foreach my $driver ( @adrivers ) {
print Driver: $driver\n;
my @dataSources = DBI-data_sources( $driver );
foreach my $dataSource ( @dataSources ) {
print \tData Source is $dataSource\n;
}
print \n;
}
and the output I got was:
Available Drivers: DBM, ExampleP, File, ODBC, Proxy, Sponge
Installed Drivers:
Data Sources: Driver: DBM
Re: ODBC Driver failing?
On 26/06/2013 20:25, Dan Bent wrote: Everything is on one box. Liant was a small development company out of Austin, TX that created a product called Relativity, which allows C-ISAM files to be manipulated as if they were a relational database. The developer of our primary business application (developed in COBOL) used Relativity as a reporting/data extract solution. Over the years Liant got acquired by MicroFocus, and for a number of reasons support is difficult to obtain. Interesting. My company do an ODBC driver for ISAM files too. $ odbcinst -j unixODBC 2.2.7 DRIVERS: /usr/local/liant/etc/odbcinst.ini SYSTEM DATA SOURCES: /usr/local/liant/etc/odbc.ini USER DATA SOURCES..: /home/dbent/.odbc.ini ok, so this is quite old but we now know definitely it is unixODBC. We also know your ODBC drivers are defined in /usr/local/liant/etc/odbcinst.ini and your DSNs are in the other 2 files - I'm presuming dbent is the logged in user. # cat /usr/local/liant/etc/odbc.ini [ODBC Data Sources] prod1 = Relativity Client verify = Relativity Client [prod1] Driver = /usr/local/liant/lib/relclient.sl http://relclient.sl ServerName = chicago.1583 ServerDSN = prod1 QryPlan = 0 ArrayFetchOn= 1 ArrayBufferSize = 8 This is a bit unusual. It looks more like an iODBC odbc.ini file but it doesn't really matter, unixODBC will just ignore [ODBC Data Sources] section. I'm also surprised by that http string but I'm guessing again this is ignored by unixODBC. Also Driver is usually set to the key from the odbcinst.ini file instead of the .sl file again but what you have also works. [test1] Driver = /usr/local/liant/lib/relclient.sl http://relclient.sl ServerName = chicago.1583 ServerDSN = test1 QryPlan = 0 ArrayFetchOn= 1 ArrayBufferSize = 8 [verify] Driver = /usr/local/liant/lib/relclient.sl http://relclient.sl ServerName = chicago.1583 ServerDSN = verify QryPlan = 0 ArrayFetchOn= 1 ArrayBufferSize = 8 # cat /usr/local/liant/etc/odbcinst.ini [ODBC Drivers] Relativity Client = Installed [Relativity Client] Driver = /usr/local/liant/lib/relclient.sl http://relclient.sl Setup = /usr/local/liant/lib/relclnsu.sl http://relclnsu.sl APILevel= 2 ConnectFunction = YYY DriverODBCVer = 02.50 FileUsage = 4 SQLLevel= 0 DefaultServer = chicago SvcEnableBroadcasting = 0 SvcPort = 1599 SvcServer = 127.0.0.1 SvcClntTimeOut = 1 SvcSystemDSN= 0 UpdateEveryXHourSec = 3600 SvcDirect = 1 UpdateAlways= 0 SvcSvrPort = 1583 EnableAutoUpdate= 0 don't really get that last entry. $ ll /usr/local/liant/lib/* lrwxrwxrwx 1 root sys 10 Nov 8 2007 /usr/local/liant/lib/libiodbc.sl http://libiodbc.sl - libodbc.sl http://libodbc.sl lrwxrwxrwx 1 root sys 14 Nov 8 2007 /usr/local/liant/lib/libiodbcinst.sl http://libiodbcinst.sl - libodbcinst.sl http://libodbcinst.sl lrwxrwxrwx 1 root sys 14 Nov 8 2007 /usr/local/liant/lib/libodbc.sl http://libodbc.sl - libodbc.sl.1.0 lrwxrwxrwx 1 root sys 14 Nov 8 2007 /usr/local/liant/lib/libodbc.sl.1 - libodbc.sl.1.0 -r-xr-xr-x 1 root sys2322328 Jan 18 2005 /usr/local/liant/lib/libodbc.sl.1.0 lrwxrwxrwx 1 root sys 18 Nov 8 2007 /usr/local/liant/lib/libodbcinst.sl http://libodbcinst.sl - libodbcinst.sl.1.0 lrwxrwxrwx 1 root sys 18 Nov 8 2007 /usr/local/liant/lib/libodbcinst.sl.1 - libodbcinst.sl.1.0 -r-xr-xr-x 1 root sys 582304 Jan 18 2005 /usr/local/liant/lib/libodbcinst.sl.1.0 -rwxrwxrwx 1 root root638976 May 30 2006 /usr/local/liant/lib/relclient.sl http://relclient.sl -rwxrwxrwx 1 root root 49152 May 30 2006 /usr/local/liant/lib/relclnsu.sl http://relclnsu.sl That explains some things. All the iodbc files are links to odbc and your driver really is named with a space in it. strace it is then - since this pretty much looks ok. Martin On Wed, Jun 26, 2013 at 2:01 PM, Martin J. Evans boh...@ntlworld.com mailto:boh...@ntlworld.com wrote: On 26/06/2013 19:35, Dan Bent wrote: Big thanks! I did this: ldd /opt/perl_32/lib/site_perl/5.__8.8/PA-RISC1.1-thread-multi/__auto/DBD/ODBC/ODBC.sl and got: /usr/local/liant/lib/libodbc.__sl.1 = /usr/local/liant/lib/libodbc.__sl.1 /usr/lib/libc.2 = /usr/lib/libc.2 /usr/lib/libdld.2 =/usr/lib/libdld.2 /usr/lib/libc.2 = /usr/lib/libc.2 /usr/lib/libpthread.1 =/usr/lib/libpthread.1 hmm - never heard of liant. It looks like the unixODBC driver manager but I've never seen
Re: ODBC Driver failing?
Everything is on one box. Liant was a small development company out of Austin, TX that created a product called Relativity, which allows C-ISAM files to be manipulated as if they were a relational database. The developer of our primary business application (developed in COBOL) used Relativity as a reporting/data extract solution. Over the years Liant got acquired by MicroFocus, and for a number of reasons support is difficult to obtain. $ odbcinst -j unixODBC 2.2.7 DRIVERS: /usr/local/liant/etc/odbcinst.ini SYSTEM DATA SOURCES: /usr/local/liant/etc/odbc.ini USER DATA SOURCES..: /home/dbent/.odbc.ini # cat /usr/local/liant/etc/odbc.ini [ODBC Data Sources] prod1 = Relativity Client verify = Relativity Client [prod1] Driver = /usr/local/liant/lib/relclient.sl ServerName = chicago.1583 ServerDSN = prod1 QryPlan = 0 ArrayFetchOn= 1 ArrayBufferSize = 8 [test1] Driver = /usr/local/liant/lib/relclient.sl ServerName = chicago.1583 ServerDSN = test1 QryPlan = 0 ArrayFetchOn= 1 ArrayBufferSize = 8 [verify] Driver = /usr/local/liant/lib/relclient.sl ServerName = chicago.1583 ServerDSN = verify QryPlan = 0 ArrayFetchOn= 1 ArrayBufferSize = 8 # cat /usr/local/liant/etc/odbcinst.ini [ODBC Drivers] Relativity Client = Installed [Relativity Client] Driver = /usr/local/liant/lib/relclient.sl Setup = /usr/local/liant/lib/relclnsu.sl APILevel= 2 ConnectFunction = YYY DriverODBCVer = 02.50 FileUsage = 4 SQLLevel= 0 DefaultServer = chicago SvcEnableBroadcasting = 0 SvcPort = 1599 SvcServer = 127.0.0.1 SvcClntTimeOut = 1 SvcSystemDSN= 0 UpdateEveryXHourSec = 3600 SvcDirect = 1 UpdateAlways= 0 SvcSvrPort = 1583 EnableAutoUpdate= 0 $ ll /usr/local/liant/lib/* lrwxrwxrwx 1 root sys 10 Nov 8 2007 /usr/local/liant/lib/libiodbc.sl - libodbc.sl lrwxrwxrwx 1 root sys 14 Nov 8 2007 /usr/local/liant/lib/libiodbcinst.sl - libodbcinst.sl lrwxrwxrwx 1 root sys 14 Nov 8 2007 /usr/local/liant/lib/libodbc.sl - libodbc.sl.1.0 lrwxrwxrwx 1 root sys 14 Nov 8 2007 /usr/local/liant/lib/libodbc.sl.1 - libodbc.sl.1.0 -r-xr-xr-x 1 root sys2322328 Jan 18 2005 /usr/local/liant/lib/libodbc.sl.1.0 lrwxrwxrwx 1 root sys 18 Nov 8 2007 /usr/local/liant/lib/libodbcinst.sl - libodbcinst.sl.1.0 lrwxrwxrwx 1 root sys 18 Nov 8 2007 /usr/local/liant/lib/libodbcinst.sl.1 - libodbcinst.sl.1.0 -r-xr-xr-x 1 root sys 582304 Jan 18 2005 /usr/local/liant/lib/libodbcinst.sl.1.0 -rwxrwxrwx 1 root root638976 May 30 2006 /usr/local/liant/lib/relclient.sl -rwxrwxrwx 1 root root 49152 May 30 2006 /usr/local/liant/lib/relclnsu.sl On Wed, Jun 26, 2013 at 2:01 PM, Martin J. Evans boh...@ntlworld.comwrote: On 26/06/2013 19:35, Dan Bent wrote: Big thanks! I did this: ldd /opt/perl_32/lib/site_perl/5.**8.8/PA-RISC1.1-thread-multi/** auto/DBD/ODBC/ODBC.sl and got: /usr/local/liant/lib/libodbc.**sl.1 =/usr/local/liant/lib/libodbc. **sl.1 /usr/lib/libc.2 = /usr/lib/libc.2 /usr/lib/libdld.2 =/usr/lib/libdld.2 /usr/lib/libc.2 = /usr/lib/libc.2 /usr/lib/libpthread.1 =/usr/lib/libpthread.1 hmm - never heard of liant. It looks like the unixODBC driver manager but I've never seen it installed in that location. Also, I see you've got isql and that comes with unixODBC. Have you also got a binary called odbcinst and if you have output from odbcinst -j would be useful. I tried isql -v prod1 username password and it just hung like other attempts to access the database. No error messages. OK, so we've ruled out a change in Perl and DBI and DBD::ODBC as it is still going wrong without them. If this really is unixODBC you should have an odbc.ini and odbcinst.ini file probably in /usr/local/liant/etc or /usr/local/etc of /etc. What is in those files? There may also be a .odbc.ini in the users home dir. When we see the contents of those files we'll have a better idea of what driver you are using and the shared library used so you can check that too to see if it has been updated. You could enable unixODBC tracing but it rarely outputs much before connection is complete. I think you need to find the equivalent of strace on HPUX and run it on the isql command to see what system calls are being made. Did you say everything is on one box, so networking off that box cannot be the issue? Martin On Wed, Jun 26, 2013 at 12:11 PM, Martin J. Evans boh...@ntlworld.com mailto:boh...@ntlworld.com wrote: On 26/06/2013 17:28, Dan Bent wrote: I
Re: cross database queries?
On 6/26/2013 12:29 PM, Andrew Snyder wrote: Thank you all for your replies. I am indeed talking about working with multiple back ends (oracle, mysql, or any valid DBI module) in the same query. thats never going to happen. DBI is not a sql processor, its just an interface, the SQL is passed directly to the database server you're connected to some databases have support for 'foreign data wrappers', where you can explicitly setup a connection to another database then use tables on that foreign database, so if you connected to a DB like this and got the FDW stuff all working, you could maybe achieve what you want, but its almost never optimal as the planner for the 'primary' database has no way to optimize JOIN operations etc involving other databases. -- john r pierce 37N 122W somewhere on the middle of the left coast
Re: ODBC Driver failing?
$ strace -o strace.log isql -v prod1 user password usage: [ mid sid level] ... So, that didn't get us what we wanted. The man page: NAME strace - write STREAMS event trace messages to standard output SYNOPSIS strace [ mod sub pri ] ... DESCRIPTION strace gets STREAMS event trace messages from STREAMS drivers and modules via the STREAMS log driver (strlog(7)), and writes these messages to standard output. By default, strace without arguments writes all STREAMS trace messages from all drivers and modules. strace with command-line arguments limits the trace messages received. The arguments, which must be specified in groups of three, are: mod Specifies the STREAMS module identification number from the streamtab entry. sub Specifies a subidentification number (often corresponding to a minor device). pri Specifies a tracing priority level. strace gets messages of a level equal to or less than the value specified by pri. Only positive integer values are allowed. The value all can be used for any argument in the strace command line to indicate that there are no restrictions for that argument. Multiple sets of the three arguments can be specified to obtain the messages from more than one driver or module. Only one strace process can open the STREAMS log driver at a time. When strace is invoked, the log driver compares the sets of command line arguments with actual trace messages, returning only messages that satisfy the specified criteria. STREAMS event trace messages have the following format: seq time tick pri ind mod sub text Components are interpreted as follows: seq Trace event sequence number. time Time the message was sent expressed in hh:mm:ss. tick Time the message was sent expressed in machine ticks since the last boot. Hewlett-Packard Company- 1 - HP-UX Release 11i: November 2000 strace(1M) strace(1M) pri Tracing priority level as defined by the STREAMS driver or module that originates the messages. ind Can be any combination of the following three message indicators: EThe message has also been saved in the error log. FThe message signaled a fatal error. NThe message has also been mailed to the system administrator. mod Module identification number of the trace message source. sub Subidentification number of the trace message source. text Trace message text. strace runs until terminated by the user. EXAMPLES Display all trace messages received from the driver or module identified by mod 28: strace 28 all all Display trace messages of any tracing priority level from the driver or module identified by mod 28 and its minor devices identified by the sub 2, 3, or 4: strace 28 2 all 28 3 all 28 4 all Display the trace messages from the same driver or module and subs but limit the priority levels to 0 for subs 2 and 3; 1 for sub 4, driver or module 28: strace 28 2 0 28 3 0 28 4 1 WARNINGS Running strace with several sets of arguments can impair STREAMS performance, particularly for those modules and drivers that are sending the messages. Also be aware that strace may not be able to handle a large number of messages. If drivers and modules return messages to strace too quickly, some may be lost. FILES /usr/lib/nls/msg/C/strace.cat NLS catalog for strace. suggests we need a module identification number, which might be obtained from streamtab, but I haven't been able to find streamtab. On Wed, Jun 26, 2013 at 2:42 PM, Martin J. Evans boh...@ntlworld.comwrote: On 26/06/2013 20:25, Dan Bent wrote: Everything is on one box. Liant was a small development company out of Austin, TX that created a product called Relativity, which allows C-ISAM files to be manipulated as if they were a relational database. The developer of our primary business application (developed in COBOL) used Relativity as a reporting/data extract solution. Over the years Liant got acquired by MicroFocus, and for a number of reasons support is difficult to obtain. Interesting. My company do an ODBC driver for ISAM files too. $ odbcinst -j unixODBC 2.2.7 DRIVERS: /usr/local/liant/etc/odbcinst.**ini SYSTEM DATA SOURCES: /usr/local/liant/etc/odbc.ini USER DATA SOURCES..: /home/dbent/.odbc.ini ok, so this is quite old but we now know
Re: cross database queries?
On 2013.06.26 12:47 PM, John R Pierce wrote: On 6/26/2013 12:29 PM, Andrew Snyder wrote: Thank you all for your replies. I am indeed talking about working with multiple back ends (oracle, mysql, or any valid DBI module) in the same query. thats never going to happen. DBI is not a sql processor, its just an interface, the SQL is passed directly to the database server you're connected to Never say never! I'm working on a Perl-native engine right now, and it will not only be functionally complete internally but also has the option to federate to multiple back-ends, so then you can do what you want. some databases have support for 'foreign data wrappers', where you can explicitly setup a connection to another database then use tables on that foreign database, so if you connected to a DB like this and got the FDW stuff all working, you could maybe achieve what you want, but its almost never optimal as the planner for the 'primary' database has no way to optimize JOIN operations etc involving other databases. In the short term that might be a good bet though, try using an existing SQL database's FDW capabilities. -- Darren Duncan
looking for a patch or workaround for err / errstr bug
Hi, I think I have run into this bug; https://rt.cpan.org/Public/Bug/Display.html?id=71555 I am working on a 64-bit Windows Server 2008 system and 32-bit Strawberry Perl (32-bit since it appears that there is a module I need which doesn't work with 64-bit). perl/vendor/lib/DBD/mysql.pm says $VERSION = '4.022' in it. Does anyone know of a patch or workaround for bug #71555? Thanks for any advice. best, Robert Dodier
please remove me from this list
dbi-users@perl.org -- www.linkedin.com/in/aomary
Re: please remove me from this list
On 6/26/2013 8:38 PM, Al Omary wrote: dbi-users@perl.org mailto:dbi-users@perl.org in the headers of every message, you'll see... Mailing-List: contact dbi-users-h...@perl.org; run by ezmlm Precedence: bulk List-Post: mailto:dbi-users@perl.org List-Help: mailto:dbi-users-h...@perl.org List-Unsubscribe: mailto:dbi-users-unsubscr...@perl.org List-Subscribe: mailto:dbi-users-subscr...@perl.org List-Id: dbi-users.perl.org so, sending a message to that dbi-users-unsubscr...@perl.org address will likely remove you. -- john r pierce 37N 122W somewhere on the middle of the left coast
