Retrieving from 2 different databases via a single perl DBI::ODBC connection
The poster could query his own database through Perl DBI::ODBC fine, but 'select count(*) from sysmaster:syschunks' failed with an Informix ODBC syntax error (SQL-42000), and he asked whether a second connection was needed. A suggestion to create a synonym for the sysmaster table worked in dbaccess but the CREATE SYNONYM statement itself failed when prepared through DBI::ODBC; he wanted it done in the script so the synonym could be cleaned up afterwards. Another poster noted the database:table form works under Win32::ODBC. No real fix emerged; the OP said he would simply open two separate connections, one per database.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I am using the perl DBI::ODBC module and can successfully retrieve data as follows (The connection ol_histinf_njrm is set up to use a database called 'srd') my %attr = ( RaiseError => 1, AutoCommit => 1);; my $dbh = DBI->connect("dbi:ODBC:ol_histinf_njrm", undef, undef, \\%attr); $sth = $dbh->prepare("select count(*) from systables;"); $sth->execute; This works fine. However I have also to retrieve data from the syschunks table in the sysmaster database which I tried as follows $sth = $dbh->prepare("select count(*) from sysmaster:syschunks;"); However this gives me the error DBI_RunSelectSqlFropmString Failed to prepare statement SELECT count(*) FROM sysmaster:syschunks; error -1 [Informix][Informix ODBC Driver][Informix]A syntax error has occurred. (SQL-42000)(DBD: st_prepare/SQLPrepare err=-1) Is there away of doing this on the existing connection or do I specifically need to open a separate ODBC connection for each database I am accessing ? Thanks
have you tried creating a synonym in the original sdatabase that points to sysmaster:syschunks? If that works you can then query your synonym on the origiinal database connection
scottishpoet wrote:
> have you tried creating a synonym in the original sdatabase that points
> to sysmaster:syschunks? If that works you can then query your synonym
> on the origiinal database connection
Unfortunately this does not seem to work. I hadn't used synonyms before
so I tried it in dbaccess first
H:\\Perl Scripts>dbaccess srd -
Database selected.
> CREATE SYNONYM snext FOR sysmaster:sysptnext;
> select first 1 * from snext;
pe_partnum pe_extnum pe_chunk pe_offset pe_size pe_log
1048577 0 1 13 250 0
1 row(s) retrieved.
>
This worked fine. However the perl script errors when attampting to
create the synonym.
use strict;
use warnings;
use DBI;
my %attr = ( RaiseError => 1, AutoCommit => 0);
my $dbh = DBI->connect('dbi:ODBC:ol_histinf_njrm', undef, undef,
\\%attr);
my $sqlstring = 'CREATE SYNONYM snext FOR sysmaster:sysptnext;';
my $sth = $dbh->prepare($sqlstring);
if(!defined ($sth))
{
print STDERR "\\n",
'Failed to prepare statement ' ,
$sqlstring, ' error ' , $DBI::err, ' ', $DBI::errstr, "\\n";
}
$dbh->disconnect;
exit(0);
This produces the following errors
H:\\Perl Scripts>ifxtest_synonym.pl
DBD::ODBC::db prepare failed: [Informix][Informix ODBC
Driver][Informix]A syntax
error has occurred. (SQL-42000)(DBD: st_prepare/SQLPrepare err=-1) at
H:\\Perl Scripts\\ifxtest_synonym.pl line 8.
DBD::ODBC::db prepare failed: [Informix][Informix ODBC
Driver][Informix]A syntax
error has occurred. (SQL-42000)(DBD: st_prepare/SQLPrepare err=-1) at
H:\\Perl Scripts\\ifxtest_synonym.pl line 8.
H:\\Perl Scripts>
Niall Macpherson wrote> >This worked fine. However the perl script errors when attampting to >create the synonym. Why would you need to create the synonym in perl? You can create it in DBACCESS and then use it just like it was a real table in your perl script.
Niall Macpherson schrieb: > I am using the perl DBI::ODBC module and can successfully retrieve data > as follows > > (The connection ol_histinf_njrm is set up to use a database called > 'srd') > > my %attr = ( RaiseError => 1, AutoCommit => 1);; > my $dbh = DBI->connect("dbi:ODBC:ol_histinf_njrm", undef, undef, > \\%attr); > $sth = $dbh->prepare("select count(*) from systables;"); > $sth->execute; > > > This works fine. > > However I have also to retrieve data from the syschunks table in the > sysmaster database which I tried as follows > > $sth = $dbh->prepare("select count(*) from sysmaster:syschunks;"); > > However this gives me the error > > DBI_RunSelectSqlFropmString Failed to prepare statement SELECT count(*) > FROM sysmaster:syschunks; error -1 [Informix][Informix ODBC > Driver][Informix]A syntax error has occurred. (SQL-42000)(DBD: > st_prepare/SQLPrepare err=-1) > > Is there away of doing this on the existing connection or do I > specifically need to open a separate ODBC connection for each database > I am accessing ? > > Thanks Yes, there is a way: my $sth1 = $dbh->prepare(...); my $sth2 = $dbh->prepare(...); Regards, try_and_err
Niall Macpherson schrieb: ... > $sth = $dbh->prepare("select count(*) from sysmaster:syschunks;"); ... > DBI_RunSelectSqlFropmString Failed to prepare statement SELECT count(*) > FROM sysmaster:syschunks; error -1 [Informix][Informix ODBC > Driver][Informix]A syntax error has occurred. (SQL-42000)(DBD: > st_prepare/SQLPrepare err=-1) This problem (database:table) do not exist with win32::odbc...
bozon wrote:
> Why would you need to create the synonym in perl? You can create it in
> DBACCESS and then use it just like it was a real table in your perl
> script.
The script is to be run on a customer site. I want to ensure that I
leave the system the way I found it , i.e all synonyms that are created
are also destroyed by the script on cleanup.
Previously my script made all it's database calls by calling dbaccess -
e.g (untested)
##------------------------------------------------------------------------------------------------------------
use strict;
use warnings;
use File::Spec::Functions;
my $nchunks = -1
open (SQLFILE, ">myfile.sql") || die "\\ncan't open $sqlfilename for
writing $!\\n";
my $sqlstring = 'select count(*) as nchunks from sysmaster:syschunks';
print SQLFILE $sqlstring;
close SQLFILE;
my $informixdir = $ENV{"INFORMIXDIR"} || die 'Cannot find path to
dbaccess';
my $dbaccess = catfile($informixdir, "bin", "dbaccess");
my $cmd_string = $dbaccess . " " . $dbname . " " . $sqlfile;
open(SQLPROC, "$cmd_string 2>NUL |");
while(<SQLPROC>)
{
$sqloutput .= $_;
}
if($sqloutput =~ /nchunks\\s*(\\d*)/)
{
$nchunks = $1;
}
else
{
## Error
}
##-------------------------------------------------------------------------------------------------------------
Trouble is that this is long winded and messy so this is why I decided
to use DBI instead of the system calls. I would therefore prefer that I
have no system calls to dbaccess unless absolutely neccessary. I would
think therefore if this is impossible to do I will need to open 2
connections at startup vi DBI->connect, one to the srd database and one
to the sysmaster database.