Re: DBI/DBD::DB2
On 24/06/06, Jonathan Leffler [EMAIL PROTECTED] wrote: On 6/23/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: snip If you have a pre-compiled module, then I think your problem is the absence of DB2 Connect (IIRC) or its equivalent. snip The basic connectivity requirement for DBD::DB2 on all platforms is the DB2 Runtime Client. If you're compiling DBD::DB2 yourself, then you need the DB2 Application Development Client instead (to get the headers required for the compile). Both of these are a free download from http://ibm.com/db2/udb/support/downloadv8.html. DB2 Connect (which isn't free) is only required if you are connecting to zSeries or iSeries servers. Dan
Re: DBD::DB2 execute and finish problem
WAG here: will the DBD::DB2 driver implicitly finish the resources if
you try calling $sth-fetchrow_array() again?
I don't know if DBD::DB2 can know whether there are more rows left in
the result set until you try fetching the next row, ergo it keeps the
statement handle active.
Dan
On 09/06/06, Martin J. Evans [EMAIL PROTECTED] wrote:
Hi,
I'm using DBD::DB2 0.78 and DBI 1.51. I am finding that code which is working
to DBD::ODBC and DBD::mysql fails with invalid cursor state but inserting a
call to finish makes it work. Up until now, I've never used finish because the
docs say:
If execute() is called on a statement handle that's still active
($sth-{Active} is true) then it should effectively call finish() to tidy up
the previous execution results before starting this new execution.
and
The finish method is rarely needed, and frequently overused, but can sometimes
be helpful in a few very specific situations to allow the server to free up
resources (such as sort buffers).
When all the data has been fetched from a SELECT statement, the driver should
automatically call finish for you. So you should not normally need to call it
explicitly except when you know that you've not fetched all the data from a
statement handle. The most common example is when you only want to fetch one
row, but in that case the selectrow_* methods are usually better anyway. Adding
calls to finish after each fetch loop is a common mistake, don't do it, it can
mask genuine problems like uncaught fetch errors.
An example is:
create table fred (a int not null primary key)
insert into fred values (1)
insert into fred values (2)
insert into fred values (3)
perl -w -e 'use DBI; my $dbh = DBI-connect(dbi:DB2:mydsn, xxx,
yyy); my @a = (1,2,3); $sth = $dbh-prepare(q/select * from fred where a
= ?/); foreach my $a (@a) {$sth-execute($a); my @row = $sth-fetchrow_array;}'
which returns:
DBD::DB2::st execute failed: [IBM][CLI Driver] CLI0115E Invalid cursor state.
SQLSTATE=24000 at -e line 1.
This seems to fall into the category of the first quote from the docs which
suggest finish should be called for you. I don't want to add the finish if it
should not be required and this is a huge amount of code to work through
anyway. I know I could possible avoid the issue if I used selectall_* but here
again, I'd have to check a lot of code to make this change.
Is this a bug in DBD::DB2?
Martin
--
Martin J. Evans
Easysoft Ltd, UK
http://www.easysoft.com
Re: DBD::DB2 where to get a client library
Hi Martin: You'll need the Application Development Client, which is a free download from http://ibm.com/db2/udb/support/downloadv8.html. The clients are installed as RPMs on Linux using the db2setup program (part of the client); you might want to check out the DB2 HOWTO (http://tldp.org/HOWTO/DB2-HOWTO/index.html) for a general understanding of the underlying dependencies. Or post here on what your system is and what problems you're encountering. Dan On 01/06/06, Martin J. Evans [EMAIL PROTECTED] wrote: I'm posting this on behalf of a colleague who is struggling to get DBD::DB2 working on a machine remote to the DB2 database. We have DB2 installed and DBD::DB2 working on that machine but where can we download only the DB2 client (or whatever DBD::DB2 needs) to use DBD::DB2 to connect to a remote DB2 database on Linux? So far all we've found is an RPM with some dependency-hell and this is not a redhat system. Thanks. Martin
Re: DB2.pm for Perl 5.8 needed
This is a nice thought, but the latest release of DBD::DB2 is actually 0.78 (released a long time ago), but ActiveState wasn't interested in updating their version of the package because DBD::DB2 prereqs the DB2 Runtime Client. Apparently ActiveState has made it a policy to avoid offering packages which are not entirely self-contained (because they don't want to be inundated with support calls complaining about a problem due to a missing third-party library). So until IBM can figure out how to avoid the dependency on the DB2 Runtime Client, ActiveState is apparently going to continue to offer a decrepit version of DBD::DB2. The last time esoftmatic.com disappeared from the Web was due to some DNS problems; more than likely this problem will clear up in a day or two. Dan On 3/31/06, John Scoles [EMAIL PROTECTED] wrote: Since you are in windows you might want to just to use Activeperl and ppm to get the latest version which I think is ' DBD-DB2 [0.76] DBD-DB2' - Original Message - From: Bergners, Andrei [EMAIL PROTECTED] To: dbi-users@perl.org Sent: Friday, March 31, 2006 10:01 AM Subject: DB2.pm for Perl 5.8 needed Hi, I am looking to install the Win32 DB2.pm library for perl 5.8.7 or 5.8.8. The previously recommended site of http://ftp.esoftmatic.com/DBI/ http://ftp.esoftmatic.com/DBI/ no longer seems to contain the necessary ppds. Can someone recommend another location? thanks andrei
Re: need DBD-DB2.ppd, DBI.ppd from www.esoftmatic.com, but server is unreachable.
Seems to be working now for me: http://ftp.esoftmatic.com/outgoing/DBI/ Dan On 31-Jan-2006 10:09:34 CET, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hello, I need DBI and DBD-DB2 packages for Activeperl, but esoftmatic server is unreachable. Can anybody help me with this problem or send me this files? Thanks for any help. Kind Regards Denis Baumgaertner System and Network Consultant __ SPIRIT/21 AG Otto-Lilienthal-Strasse 36 71034 Böblingen phone: +49 7031 / 714 96 00 fax: +49 7031 / 714 96 99 mailto: [EMAIL PROTECTED] internet: http://www.spirit21.de
Re: Connect to DB2
Hi Vincent: You just need to install the DB2 development headers and libraries (available with the free to download and use DB2 Application Development Client) on your Linux box and set DB2_HOME before compiling DBD::DB2; that will enable you to connect from your Linux box to a DB2 server running on a Windows box. For more information: * http://ibm.com/db2/perl/ * http://www.db2mag.com/showArticle.jhtml?articleID=59301551 * http://www.db2mag.com/story/showArticle.jhtml?articleID=60400732 (Note that Red Hat 9.0 is not officially supported by DB2, so you might run into some troubles with that part of the equation. If it doesn't work out, and you're willing to install a new flavour of Linux, check out http://ibm.com/db2/linux/validate for a list of Linux distros supported by DB2) Dan On 1/5/06, Vincent Deng [EMAIL PROTECTED] wrote: Dear dbi-helpers, My environment is RedHat 9.0. I got the following message while installing DBD::DB2 module. DB2_HOME environment variable must be set to installed location of DB2. Did this message indicate that I have to install DB2 in the same machine and set DB2_HOME environment variable for DBD installation? If I want not to install DB2 on the same machine but connect to the DB2 server installed on another windows machine, what should I do? Thanks in advance
Re: Connect to DB2
Let's keep this discussion on-list in case someone else has the same question in the future. http://ibm.com/db2/perl/ says that if you are compiling DBD::DB2 on Linux, you set DB2_HOME to the location of your DB2 instance--for example, /home/db2inst1/sqllib/ -- because DBD::DB2 needs the DB2 headers and libraries to compile successfully. When you run a Perl program, DBD::DB2 then uses those libraries to connect to your DB2 server running on Windows. Dan On 1/5/06, Vincent Deng [EMAIL PROTECTED] wrote: Dear Dan, So what value I should assign to DB_HOME? The DB2 home directory on windows box or? Thanks in advance On 1/5/06, Dan Scott [EMAIL PROTECTED] wrote: Hi Vincent: You just need to install the DB2 development headers and libraries (available with the free to download and use DB2 Application Development Client) on your Linux box and set DB2_HOME before compiling DBD::DB2; that will enable you to connect from your Linux box to a DB2 server running on a Windows box. For more information: * http://ibm.com/db2/perl/ * http://www.db2mag.com/showArticle.jhtml?articleID=59301551 * http://www.db2mag.com/story/showArticle.jhtml?articleID=60400732 (Note that Red Hat 9.0 is not officially supported by DB2, so you might run into some troubles with that part of the equation. If it doesn't work out, and you're willing to install a new flavour of Linux, check out http://ibm.com/db2/linux/validate for a list of Linux distros supported by DB2) Dan On 1/5/06, Vincent Deng [EMAIL PROTECTED] wrote: Dear dbi-helpers, My environment is RedHat 9.0. I got the following message while installing DBD::DB2 module. DB2_HOME environment variable must be set to installed location of DB2. Did this message indicate that I have to install DB2 in the same machine and set DB2_HOME environment variable for DBD installation? If I want not to install DB2 on the same machine but connect to the DB2 server installed on another windows machine, what should I do? Thanks in advance
Re: anyway to determine # rows before fetch loop ends and without seperate count(*)
On 11/16/05, Rob Craig [EMAIL PROTECTED] wrote:
I am using DBD::mysql and I can get the number of rows by assigning the
result of the execute to a scalar:
my $rows = $sth-execute or die Couldn't execute statement: .
DBI-errstr;
maybe this doesn't work with DBD::Oracle?
Rob
When you execute a SELECT statement in MySQL, it (by default)
immediately returns all of the rows back to you under the covers,
which is why it is able to give you a row count. Woe betide you if
your SELECT statement returns a million rows.
[EMAIL PROTECTED] wrote:
#Here's an example which shows what I am trying to accomplish. If I
can determine the number of rows before pushing the data, this can
simply things for #me when processing the data throught my scripts.
#
use warnings;
use strict;
use DBI;
use DBD::Oracle;
my $sql=q{ select name, location
from mytable
};
my $dbh;
eval {
$dbh = DBI-connect(dbi:Oracle:MYDB,
'dbuser', 'dbpass',
{
RaiseError = 1,
AutoCommit = 0,
ora_session_mode = 0
}
);
};
if ( $@ ) {
outprint('end',$DBI::errstr\n);
}
my $sth=$dbh-prepare($sql) or die Couldn't prepare statement: . DBI-
errstr;
$sth-execute or die Couldn't execute statement: . DBI-errstr;
my $ary;
while ($ary = $sth-fetchrow_array()) {
#I need to determine number of rows as this will affect
whether a matrix is used or not
#a boolean variable $matrix could be returned or a ref
check done so that the data
#processing code can act accordingly
#$sth-rows only shows total rows after the while loop
is processed
#Can I accomplish this without a seperate count(*)
statement?
#
#push @newary,[ @{$ary} ]; # if more than one row
#or
#push @newary, @{$ary} ; # single row
}
$sth-finish;
$dbh-disconnect;
#
#ActivePerl 5.8.7 813
#ppm
#-DBD-Oracle 1.16
#-DBI 1.48
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.362 / Virus Database: 267.13.3/173 - Release Date: 11/16/2005
Re: error while trying to install DBI
Strange -- just tried the exact same commands here and they worked as expected. Maybe there was a temporary FTP server glitch. Are you able to see the ftp.esoftmatic.com site? Dan On 11/3/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hello. I just installed perl 5.8.7 from active state. (I need to write perl scripts that run against DB2 databases on Windows platform). I then tried to run: ppm install ftp://ftp.esoftmatic.com/outgoing/DBI/5.8.4/DBI.ppd but I get the following back: C:\Perl\binppm install ftp://ftp.esoftmatic.com/outgoing/DBI/5.8.4/DBI.ppd Error: No valid repositories: Error: 500 Server closed connection without sending any data back Error: 500 Server closed connection without sending any data back I was going to run that and then run: ppm install ftp://ftp.esoftmatic.com/outgoing/DBI/5.8.4/DBD-DB2.ppd ftp://ftp.esoftmatic.com/outgoing/DBI/5.8.4/DBD-DB2.ppd but don't understand what the error means? Can someone please help? Thanks, Bob
Re: ANNOUNCE: MySQL 5.0 now final/stable/GA
Hi Darren: I think an update about the recommended minimum database version levels for your access modules is on-topic for this mailing list, but suspect that product advertisements are probably off-topic -- personal opinion, maybe, but I don't want to see this list become a dumping ground for the latest releases and associated features of Oracle, Postgres, Informix IDS, DB2, SQL Server, etc. Dan On 10/24/05, Darren Duncan [EMAIL PROTECTED] wrote: For those of you that use MySQL, this should be welcome news, that version 5.0.x is now officially stable, at 5.0.15. Speaking just personally, the recommended minimum versions of certain database products to use with my upcoming rewritten database access modules are the following; they are all stable, and what I will be testing with: - SQLite 3.x (3.2.7+) - MySQL 5.x (5.0.15+) - PostgreSQL 8.x (8.0.4+) I will also support several previous major versions of all 3, but at lower priority, based on user demand, partly to assist with upwards migrations. -- Darren Duncan --- Date: Mon, 24 Oct 2005 15:40:43 +0300 From: Kaj Arnö [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Announcing MySQL 5.0 Dear user of MySQL, It is my pleasure to announce the production release of MySQL 5.0, which is hereby GA (Generally Available). Since my announcement of the Release Candidate less than a month ago, no bugs have been reported that require a second Release Candidate. This, combined with the feedback from over two million downloads of MySQL 5.0 during its beta phase, give us the confidence to give MySQL 5.0 the status of Current Production Release, or GA. In the Release Candidate announcement less than a month ago, I described MySQL 5.0 as the most important release in MySQL's history, and that is certainly the case. Thus, I encourage you all to: - get your own copy at http://dev.mysql.com/downloads/mysql/5.0.html - do all of your new database development using MySQL 5.0 - upgrade your current MySQL environments to MySQL 5.0, as soon as you've properly verified your production applications against it (be sure to take a full backup of your data before upgrading, study the relevant documentation, and if you have a MySQL Network support contract, consult first with the MySQL Support Team) Let me also underline that we continue to offer some earlier versions of MySQL Server for download. However, you should expect maintenance releases for earlier versions only in limited form: - for MySQL 4.1, only when serious bugs affecting significant user groups are reported - for MySQL 4.0, only when security bugs are reported MySQL 5.0 is the most ambitious release to date for MySQL AB. We have added functionality that our users have requested from us over many years. However, everything we do at MySQL centers around our three priorities of Performance, Reliability, and Ease of Use. MySQL 5.0 is certainly true to these company-wide values. Key new features of MySQL 5.0 come in three groups: a) ANSI SQL standard features formerly unknown to MySQL b) ANSI SQL standard compliance of existing MySQL features c) New MySQL Storage Engines, Tools and Extensions The new ANSI SQL features include: - Views (both read-only and updatable views) - Stored Procedures and Stored Functions, using the SQL:2003 syntax, which is also used by IBM's DB2 - Triggers (row-level) - Server-side cursors (read-only, non-scrolling) Implementing ANSI SQL standard ways of using existing MySQL features means there will be fewer unpleasant surprises (gotchas) for those migrating to MySQL from other database systems: - Strict Mode: MySQL 5.0 adds a mode that complies with standard SQL in a number of areas in which earlier versions did not; we now do strict data type checking and issue errors for all invalid dates, numbers and strings as expected - INFORMATION_SCHEMA: An ANSI SQL-compliant set of tables that provide database metadata, in parallel with the MySQL-specific SHOW commands - Precision Math: A new library for fixed-point arithmetic, giving high accuracy for financial and mathematical operations - VARCHAR Data Type: The maximum effective length of a VARCHAR column has increased to 65,532 bytes; also, stripping of trailing whitespace no longer occurs New MySQL Storage Engines, Tools and Extensions are: - XA Distributed Transactions - ARCHIVE Storage Engine for storing large amounts of data without indexes in a very small footprint, intended for historical data that may be needed for future audit compliance (Sarbanes Oxley or otherwise) - FEDERATED Storage Engine for accessing data ín tables of remote databases rather than in local tables (only in MAX version) - Instance Manager: a tool to start and stop MySQL Server, even remotely To find out more details on what's new in MySQL 5.0, follow the pointers from
Re: bind_param_inout + DBD::ODBC
DBD::DB2 (http://search.cpan.org/dist/DBD-DB2/DB2.pod) supports bind_param_inout, but it would require you to use the DB2 Connect Personal Edition or DB2 Connect Unlimited for iSeries to access iSeries from your Linux, UNIX, or Windows box. Dan On 10/12/05, Stephen More [EMAIL PROTECTED] wrote: I am currently having a problem calling a stored procedure on an iSeries. DBD::ODBC::st execute failed: [unixODBC][IBM][iSeries Access ODBC Driver]Error in assignment. (SQL-22018)(DBD: st_execute/SQLExecute err=-1) In looking through the perldoc doc for DBI I found: bind_param_inout It is expected that few drivers will support this method. The only driver currently known to do so is DBD::Oracle (DBD::ODBC may support it in a future release). Therefore it should not be used for database independent applications. Does DBD::ODBC support bind_param_inout ?? If not, how can you call a stored procedure from perl using ODBC ? -Thanks Steve More
Re: Help with DBD-DB2 and Windows...
If you're willing to upgrade to Perl 5.8.x, then you can use the latest available version of DBD-DB2 from http://ftp.esoftmatic.com/DBI/ (it's okay to install the 5.8.4 version of DBD-DB2 on Perl 5.8.7). Dan On 10/7/05, Trent Ohannessian [EMAIL PROTECTED] wrote: Hello, I currently have Perl 5.6.1 and DBD-DB2 .76 installed on my Windows NT machine and everything works wonderfully. I would like to upgrade the DBD-DB2 to .78, but am having some difficulties. I can't find a PPM repository to upgrade it, and I using the make/nmake isn't an option on our machines. Does anyone have any ideas or suggestions? Thanks! Trent -- A chicken crossing the road is poultry in motion.
Re: How to set language on DB2 on AIX
[EMAIL PROTECTED] wrote: Hello, I'm now using Perl 5.005, DBI-1.15, DBD-DB2-0.75 on AIX 4.3. And like to upgrade them to Perl 5.8, DBI-1.48, DBD-DB2-0.78. Installation may be ok, but when I INSERT double bytes characters, I get the following error. [IBM][CLI Driver] CLI0109E String data right truncation. I'm using Japanese, and 80 byes wide char strings cann't be inserted into varchar(80), current version is ok. And smaller inserted strings seem to be unicode or utf-8. Env vars related is LANG=Ja_JP only. I tried NLS_LANG=American_America.JA16SJIS, which ended failed. What is the right way to set correct language specification? NLS_LANG is the correct thing to cope with? I search dbi ML archives goole but failed to find a good answer. Regards, Hirosi Taguti [EMAIL PROTECTED] Hello: I asked one of the developers here to help me with an answer, and this is what they responded with: * varchar(80) means that it can hold up to 80 bytes of data (not 80 characters). How many actual characters are there in your 80 byte Japanese string and what are they? What is the target database codepage ? How are you inserting the data ? Without this information, it is hard to answer your question . However here are some examples/hints: If database is Unicode (UTF-8), then data will be stored in UTF-8/1208 in varchar column. If environment is set to Ja_JP(943 most likely) then data will be converted from 943 to 1208. During conversion 80 bytes of 943 could expand to more then 80 bytes in 1208 - data will not fit into varchar(80) column. It is also necessary to know what has changed in your environment. If before you had English database or Japanese database, then data will not expand and will fit. You will need to drop/recreate your database to change it's codepage. Another possibility is that you have UTF-8 data, not Japanese data, then the problem is with settings on your client - proper LANG or db2set db2codepage=1208 could be missing. It is hard to advise until we know what data you are inserting into the database, how are you doing it and database codepage you need . You need to be very clear on what codepage the data is in and how you plan to store it. I hope this helps... Dan
Re: DBD-DB2 install issue
Ah, one other _very_ likely possibility is that the user running the tests (or any other script using DBD::DB2) had not inherited the DB2 instance profile before running the tests. The DB2 instance profile sets the environment variables that enable the DB2 client to find things like libdb2.so, catalog entries for DB2 databases servers, etc. It's an important step, but it often happens automatically because the user ID used to run the DB2 application just happens to be a DB2 instance owner. For example, if you create a DB2 instance using user 'db2inst1' in /home/db2inst1, and you log in as 'db2inst1' and run the Perl script, you'll already have the DB2 instance environment set up. But if you log in as user 'daniel' on the same system and try to run the Perl script, the script will fail (usually with a helpful error like Total environment allocation error). The proper fix for this is to inherit the DB2 instance environment before running the Perl script that relies on DBD::DB2 by sourcing the db2profile script that sets all of the environment variables for the DB2 instance user, e.g. [EMAIL PROTECTED]:\ . /home/db2inst1/sqllib/db2profile [EMAIL PROTECTED]:\ perl update_database.pl I would recommend deleting /usr/lib/libdb2.so.1 and trying this approach instead, as any other libraries that DB2 depends on will not be found using your approach. Dan Alec Brecher wrote: Thank you Dan. The make ran without complaint, but the make test failed 4 of 5 tests. The first time I went ahead and installed anyway and ran a test script which completely failed. The error was that libdb2.so.1 could not be found. Based on your theory below I copied libdb2.so.1 to /usr/lib and everything is running! Not pretty but it works. Thanks so much. -Alec Dan Scott wrote: Alec Brecher wrote: My DBD-DB2 installation on Fedora Core 3 is failing because it cannot find libdb2.so.1. The Application Dev client is installed and works fine. The DB2_HOME=/home/db2inst1/sqllib I saw some post from Owen Berry on another list regarding this issue. His fix: add -Wl,-rpath,/home/db2inst1/sqllib/lib to the makefile did not work for me. DBI = 1.48 DBD-DB2 = 0.78 perl = 5.8.5 Any help would be greatly appreciated. -Alec Hi: Is the problem occurring at install time, or at run time (specifically within the context of a Web application)? If the problem is occurring at install time, please post the exact set of commands you are issuing, along with the results of running 'set', and all of the output you get from trying to compile and install DBD::DB2. If the problem is occurring within the context of a Web application where Apache seems to be dying, you need to disable SELinux (/usr/sbin/setenforce 0) as the default policies for SELinux in Fedora Core 3 / RHEL 4 prevent httpd (Apache) from loading libraries like libdb2.so that exist in non-standard locations. As libdb2.so resides in /opt/IBM/db2/V8.1/lib/ rather than /usr/lib/ , the SELinux policy believes that someone is trying to load an evil harmful library and kills the process. I wish I knew enough about SELinux policies to offer a modification that would enable libdb2.so to be loaded, but I just don't seem to have enough room in my brain... Dan
Re: DBD-DB2 install issue
Alec Brecher wrote: My DBD-DB2 installation on Fedora Core 3 is failing because it cannot find libdb2.so.1. The Application Dev client is installed and works fine. The DB2_HOME=/home/db2inst1/sqllib I saw some post from Owen Berry on another list regarding this issue. His fix: add -Wl,-rpath,/home/db2inst1/sqllib/lib to the makefile did not work for me. DBI = 1.48 DBD-DB2 = 0.78 perl = 5.8.5 Any help would be greatly appreciated. -Alec Hi: Is the problem occurring at install time, or at run time (specifically within the context of a Web application)? If the problem is occurring at install time, please post the exact set of commands you are issuing, along with the results of running 'set', and all of the output you get from trying to compile and install DBD::DB2. If the problem is occurring within the context of a Web application where Apache seems to be dying, you need to disable SELinux (/usr/sbin/setenforce 0) as the default policies for SELinux in Fedora Core 3 / RHEL 4 prevent httpd (Apache) from loading libraries like libdb2.so that exist in non-standard locations. As libdb2.so resides in /opt/IBM/db2/V8.1/lib/ rather than /usr/lib/ , the SELinux policy believes that someone is trying to load an evil harmful library and kills the process. I wish I knew enough about SELinux policies to offer a modification that would enable libdb2.so to be loaded, but I just don't seem to have enough room in my brain... Dan
Re: DB2 Err Handling
Tim Bunce wrote: On Fri, Sep 02, 2005 at 05:37:57PM -0500, johnn wrote: Capacio, Paula J wrote: my $stmt = 'select count(*) from sysibm.badTblnm '; my $sth = $dbh-prepare($stmt) or die Prepare Failed\n; DBD::DB2 doesn't seem to return false values for failures. If that's true then it's broken. It may just be that the prepare() doesn't send the sql to the server until it's execute()'d - so any sql errors won't be noticed till then. Tim -- that's exactly the case. We're using deferred prepare by default in the DBD::DB2 driver. That's quite common across many drivers (as it saves round-trips to the server) and is, I think, mentioned in the DBI docs from prepare(). If you want to disable deferred prepare, you can: * set the db2_deferred_prepare statement attribute to false * or set DeferredPrepare = 0 in your db2cli.ini file Thanks, Dan P.S. Sorry for the delay -- just sold our house and have been focused on life outside of computers for the last month or so :)
Re: DB2 connection question
On 7/19/05, Keith Jackson [EMAIL PROTECTED] wrote: I have a project that I need to connect to a DB2 database on an AS400 from a perl application on a Linux box. I've been reading as much as I can but have a few questions for those with experience in a similar task. My options appear to be to use ODBC or DBD::DB2. What are the advantages/disadvantages of using either one? If I use DBD::DB2, according to the README file I will need the DB2 Application Development Client. Is this something I need to purchase a specific license for Linux or if I have an AS400 license can I get a Linux version and use it? Can I get the client only or do I need to get the whole DB2 Universal Developer's Edition? Sorry if I'm not asking the correct questions. The DB2 Application Development Client is a free download from http://ibm.com/db2/udb/support/downloadv8.html. It's much smaller than DB2 Universal Developer's Edition. You need the client so you can install the application development headers to compile DBD::DB2 against. See http://ibm.com/db2/perl/ for more information. If you're running Windows, you just need the DB2 Runtime Client + the binary DBI and DBD::DB2 modules installed from esoftmatic.com (you can install the 5.8.4 versions even though you might be running ActiveState Perl 5.8.7). Note that you _will_ need a license for DB2 Connect (http://ibm.com/db2/db2connect/) to connect from DB2 on Linux, UNIX, or Windows to DB2 on AS400. If I use ODBC, which ODBC driver is recommended (i'm running Red Hat 9)? Is there a good free one that I can use for development? In theory, you should be able to use the iSeries Access offering (http://www.ibm.com/servers/eserver/iseries/access/) to connect via ODBC from Linux to AS400 -- not sure, never tried that route personally. You might also want to double-check the supported Linux distributions: Red Hat 9 is unlikely to be an officially supported platform these days, as I believe Red Hat has already dropped support for that version. Any help is greatly appreciated. Thanks in advance Keith Jackson
Re: Problem running Perl DBD-DB2-0.78 on AIX 5.3 using the DB2 UDB v8.2 (on AIX 5.3) client libraries
Chris: This sounds very much like the problem noted under Hints at http://www-306.ibm.com/software/data/db2/perl/: Having trouble running perl with DB2 UDB Version 8 64-bit for AIX 5.2? Check out this technote. (http://www-1.ibm.com/support/docview.wss?rs=71context=SSEPGGuid=swg21181032loc=en_UScs=utf-8lang=en) which goes on to describe an incompatibility between 32-bit and 64-bit libraries (and a solution, thankfully!) Dan On 6/21/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hi, I am having problems running DBI-1.48 using DBD-DB2-0.78. The issue is that when I run a make test prior to running a make install for the compilation of the Perl DBD-DB2-0.78 module on AIX 5.3 using the client libraries that come with DB2 UDB v8.2 (installed on AIX 5.3 as well) only 60% of the tests pass (The IBM VAC v6.0 C/C++ compiler was used for compilation) . The make install when invoked complains that it cannot load the following two libraries: libdb2.a and libdb2osse.a even though the $DB2_HOME directory is set using the export DB2_HOME=/db2home/db2fald1; both of the aforementioned libraries exist under the $DB2_HOME/sqllib/lib directory. The make install for the DBD-DB2-0.78 Perl module will compile without any fatal errors. However, when the DBI-1.48/ DBD-DB2-0.78 combination is called within the body of a main Perl script the following errors are produced: Can't load '/usr/opt/perl5/lib/site_perl/5.8.2/aix-thread-multi/auto/DBD/DB2/DB2.so ' for module DBD::DB2: 0509-022 Cannot load module /db2home/db2fald1/sqllib/lib/libdb2.a(shr.o). 0509-150 Dependent module /db2home/db2fald1/sqllib/lib/libdb2osse.a(shr.o) could not be loaded. 0509-152 Member shr.o is not found in archive 0509-022 Cannot load module /usr/opt/perl5/lib/site_perl/5.8.2/aix-thread-multi/auto/DBD/DB2/DB2.so. 0509-150 Dependent module /db2home/db2fald1/sqllib/lib/libdb2.a(shr.o) could not be loaded. at /usr/opt/perl5/lib/5.8.2/aix-thread-multi/DynaLoader.pm line 229. at ./falcon_bmg.pl line 5 Compilation failed in require at ./falcon_bmg.pl line 5. BEGIN failed--compilation aborted at ./falcon_bmg.pl line 5. It still complains about not being able to load the shared object libraries libdb2.a and libdb2osse.a ; any thoughts and/or insights you might have to possibly solve this, would be highly appreciated. Thanks C.L. Grassi
Re: [dbi] DSN's
For the DSN-less connections, see http://www.connectionstrings.com -- great resource. Dan On 6/2/05, Martin J. Evans [EMAIL PROTECTED] wrote: Sharon, The string after ODBC: is usually a DSN name as in: DBI-connect(dbi:ODBC:mydsn,...) or DBI-connect(dbi:ODBC:DSN=mydsn) where mydsn is an entry in your unixODBC odbc.ini file defining host, port, dbname e.g. [mydsn] driver = freetds host = something.something.something port = 1521 dbname = name If you want to use DSN-less connections then you'll need to start with DRIVER=freetds or whatever. Note, you should have a freetds entry in odbcinst.ini also. You can find your user and system odbc.ini files with odbcinst -j. Perhaps http://www.easysoft.com/products//documentation/perl_tutorials/dbd_odbc_tuto rial1.html and http://www.easysoft.com/products//documentation/perl_tutorials/dbd_odbc_tuto rial3.html might help explain as they contain alot about unixODBC, DSNs etc. Martin On 01-Jun-2005 Smith, Sharon Michelle (OSLO) wrote: Hi, I'm trying to debug an install for my DBD::ODBC module, and since this is my first experiment with the DBI, I have a question on format/syntax. But first, here's some background info that may/not help: Environment is Linux I've already installed/tested Perl5, DBI, unixODBC (manager) Now, I'm trying to install the ODBC driver In order to install the ODBC driver, environment variables: DBI_DSN, DBI_USER, DBI_PASS, ODBCHOME, LD_LIBRARY_PATH, and LANG all had to be added to .bashrc I'm installing the DBD::ODBC module from CPAN. It comes with a series of connectivity tests that you can run after running 'make' by doing 'make test'. I'm trying to narrow down my list of things that could be wrong. Almost all of the errors seem to be complaining about my 'DSN not found or my driver not specified'. Can someone tell me if the following syntax is ok or if any other major no-no's jump out at you? export DBI_DSN='dbi:ODBC:host=something.something.something;port=1521;dbname=na me' I'm very new to all this and would appreciate as much detailed help as possible. Thanks for any info -- Martin J. Evans Easysoft Ltd, UK Development
Re: SQL Problems : No suitable driver
This is not the mailing list you're looking for. This mailing list is specifically about using the Perl DBI with the associated database drivers (for example, the DBD::DB2 driver, as documented at http://ibm.com/db2/perl/). If you want help calling Java applications from a shell script, you would be better off posting to the comp.databases.ibm-db2 newsgroup. Dan On 5/26/05, Vamsi_Doddapaneni [EMAIL PROTECTED] wrote: Hi all I have a problem with java application. My shell script uses some jar files and calls a class I have set up the DBAccessor.properties with driver=COM.ibm.db2.jdbc.app.DB2Driver # specify connection url url=jdbc:db2:dev_cms But iam getting SQL Problems : No suitable driver I have changed the driver to .net.DB2Driver also but still the problem persists. Could any body help me out .. Since the same file is working in one server (AIX)- java version 1.4.0 Java(TM) 2 Runtime Environment, Standard Edition (build 1.4.0) Classic VM (build 1.4.0, J2RE 1.4.0 IBM AIX 5L for PowerPC (64 bit JVM) build ca ix641401-20021126 (JIT enabled: jitc)) And is throwing error on another server(AIX)- java version 1.4.1 Java(TM) 2 Runtime Environment, Standard Edition (build 1.4.1) Classic VM (build 1.4.1, J2RE 1.4.1 IBM AIX build ca1411-20040301 (JIT enabled: jitc)) Thanks Vamsi DISCLAIMER: This email (including any attachments) is intended for the sole use of the intended recipient/s and may contain material that is CONFIDENTIAL AND PRIVATE COMPANY INFORMATION. Any review or reliance by others or copying or distribution or forwarding of any or all of the contents in this message is STRICTLY PROHIBITED. If you are not the intended recipient, please contact the sender by email and delete all copies; your cooperation in this regard is appreciated.
Re: DBI.ppd for 5.8.6
There is an archive of older ActiveState Perl releases at http://downloads.activestate.com/ActivePerl that includes 5.8.4. Dan On Mon, 14 Mar 2005 09:01:24 -0500, Marina Greenstein [EMAIL PROTECTED] wrote: Jeff, Any chance you will update repository with 5.8.6 binaries ? Or please let me know where I can download PERL 5.8.4 ? Regards, Marina Greenstein |-+ | | Jeff Urlwin| | | [EMAIL PROTECTED]| | | ic.com | | || | | 03/09/2005 03:42 | | | AM | |-+ -| | | | To: Marina Greenstein/White Plains/[EMAIL PROTECTED], dbi-users@perl.org | | cc: | | Subject: RE: DBI.ppd for 5.8.6 | -| I probably need to update my repository with 5.8.6 binaries, but I'm a bit swamped at the moment. Sigh. Regards, Jeff -Original Message- From: Marina Greenstein [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 08, 2005 4:01 PM To: dbi-users@perl.org Subject: DBI.ppd for 5.8.6 Where I can get DBI.ppd binary for Perl 5.8.6 ? I tried using 5.8.4 and it asked me to Test-Simple, when I tried to install Test-Simple.ppd , it's telling me no suitable implementation found for 'File-Spec' Regards, Marina Greenstein,
Re: DBIx::DBH - Perl extension for simplifying database connections
On Thu, 02 Dec 2004 08:53:38 +0100, Cosimo Streppone [EMAIL PROTECTED] wrote: Tim Bunce wrote: I know what it does, I'm trying to find real examples that demonstrate why people think it's needed. Nick has provided a good one. Any others? Something like DBIx::DBH is in use in our organization. This is because every sql source (meaning database table) is accessed with standard objects and an external definition file like: USER john PASS smith DBNAME default FILE sessions HOST my.hostname.com PORT 1521 # ... # Definition of table fields myfield1 ... bla bla blah myfield2 ... bla bla blah Thus, every piece of information needed for connection *should* be separated and a frontend is provided to be able to define new sources or modify existing sources from a browser. Then, we provide different sql driver modules, that assemble the DSN string given all the pieces. For some db servers like Sybase, DB2 (MySQL?) a use dbname is needed after connection to properly select the database. Of course this is handled by the driver class. Assuming that you're talking about IBM DB2 Universal Database, you're doing something strange and unnatural with DB2 if you have to 'use dbname' after making a connection. If you've cataloged the DB2 database in your DB2 client, the DBD::DB2 connection string syntax is simply: DBI-connect(dbi:DB2:$dbname, $user, $password); Dan
Re: DBD::DB2, Apache and mod_perl
On Mon, 8 Nov 2004 15:17:34 +0100, Miroslav Lau [EMAIL PROTECTED] wrote:
Hi all!
I'm using DBD::DB2 to access remote DB2 database on an AS400 machine. I've
managed it to work from command line.
But when trying to connect to remote database from a mod_perl application the
$DBI::errstr is set to this:
[iODBC][Driver Manager]Data source name not found and no default driver
specified. Driver could not be loaded
What the hell is ODBC doing here?
This is the source of the Mason component:
-- DB2test.html
%$DBI::errstr%
DB2DIR %$ENV{'DB2DIR'}%br
DB2INSTANCE %$ENV{'DB2INSTANCE'}%br
INSTHOME %$ENV{'INSTHOME'}%br
CLASSPATH %$ENV{'CLASSPATH'}%br
LD_LIBRARY_PATH %$ENV{'LD_LIBRARY_PATH'}%br
LIBPATH %$ENV{'LIBPATH'}%br
%INIT
use DBI;
use DBD::DB2::Constants;
use DBD::DB2;
my $loc = setlocale( LC_ALL, cs_CZ );
my $dbstr = dbi:DB2:as400tes;
my $dbuser = 'MLAUS';
my $dbpasswd = 'MLAUS';
my $dbh = DBI-connect($dbstr, $dbuser, $dbpasswd);
my $sql = SELECT
SULNME,SUFNME FROM CABLEDTA.SUBSMSTR AS MAIN WHERE MAIN.SULNME LIKE 'LAU%'
ORDER BY MAIN.SULNME,MAIN.SUFNME ASC;;
/%INIT
%ARGS
/%ARGS
--
If I try to run the CLI version as normal CGI, it works. I'm sure the
environment is setup allright.
I'm running Apache 1.3.28, mod_perl 1.28, HTML::Mason 1.05, DBI 1.45, DBD::DB2
0.78.
Miroslav Laus
My guess is that mod_perl ignores the environment settings you have at
the top of the Mason script; can you try setting the enviroment
variables in your Apache startup script instead of within the Mason
script?
Dan
Re: :ODBC with DB2
Ben: DB2 automatically takes the user ID you logged in with and uses that as your default schema. You can issue the SET SCHEMA statement to change your default schema if you want it to be different from your DSN user ID. For example SET SCHEMA rick would automatically prepend 'rick.' to all of the tables, views, etc that you work with. Dan On Fri, 8 Oct 2004 12:40:20 -0400, Jeff Urlwin [EMAIL PROTECTED] wrote: Ben, Please post these types of questions to the dbi-users list, rather than to me directly. There are a lot of other people with more experience than me with the various databases who can help. I believe that is standard behaviour for most ODBC drivers/logins, that if you l do not provide the schema, the login schema is presumed. With Oracle, for example, I can get around that with public synonyms. I'd check your DB2 documentation for that, because DBD::ODBC itself shouldn't (isn't) automatically prepending the schema. That may be happening in the driver or the database. Regards, Jeff -Original Message- From: Ben Hui [mailto:[EMAIL PROTECTED] Sent: Thursday, October 07, 2004 6:03 PM To: [EMAIL PROTECTED] Subject: DBD::ODBC with DB2 I am having some problems connect 2 a DB2 DB using DBI::DBD suppose the DBname is db2a I connect to it using $user sys and $pass mypass the connection to the DB all OK DB2 does not separate the schema's between systems there are separate schema's with different prefixes ie parts items chdocs if I do a simple query like select product_name from product catalogue; I get an error because the user login is prefixed into the query select product_name from sys.product catalogue; that table doesnt exist. Is there a way to change prefix to somthing else?? It appears this is being passed in from the DSN ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.
Re: DBD::DB2 and Apache::DBI problem
ricardo Guerra Flores wrote: Hi all... i'm having some problems trying to run a script that uses DBD::DB2 inside mod_perl and the Apache::DBI module, the program runs right when i execute it as a stand alone perl program. I've already set the ENV for db2home, db2instance, etc. in startup.pl This is the trace when connecting throug Apache: 8750 Apache::DBI PerlChildInitHandler - DBI-connect(dbi:DB2:DATABASE,USER, , HASH(0x20283528)) - DBI-install_driver(DB2) for aix perl=5.008 pid=65638 ruid=211 euid=211 - DBI-connect(DBI:DB2:DATABASE,USER, , HASH(0x20a1415c)) - DBI-install_driver(DB2) for aix perl=5.008 pid=65638 ruid=211 euid=211 install_driver: DBD::DB2 version undef loaded from /usr/opt/perl5/lib/site_perl/5.8.0/aix-thread-multi/DBD/DB2.pm - $DBI::errstr () FETCH from lasth=none And this is the trace when i run it as a stand alone program: 8750 Apache::DBI PerlChildInitHandler DBI 1.42-ithread default trace level set to Ox2/0 (in pid 58518) - DBI-connect(DBI:DB2:DATABASE, USER, ) - DBI-install_driver(DB2) for aix perl=5.008 pid=58518 ruid=0 euid=0 install_driver: DBD::DB2 version 0.76 loaded from /usr/opt/perl5/lib/site_perl/5.8.0/aix-thread-multi/DBD/DB2.pm - install_driver= DBI::dr=HASH(0x2016b5e8) - connect for DBD::DB2::dr (DBI::dr=HASH(0x2016b5e8)~0x20551850 'INFORMES' 'sige' HASH(0x20552af8)) thr#20019348 connect 'DATABASE', 'USER', 'pass'- connect= DBI::db=HASH(0x205518d4) at DBI.pm line 587 - STORE for DBD::DB2::db (DBI::db=HASH(0x20552b28)~INNER 'PrintError' 1) thr#20019348 does someone has any idea on what's happening?? thanks. Ricardo Guerra Hi Ricardo: Can't say I've seen the error condition where the extra HASH() issue shows up before. Have you tried the recent release of DBD::DB2 0.78 yet? Just thought I would ask before everybody else jumps in :) Dan
