Re: export in perl
On 06/22/2005 04:06 AM, Vamsi_Doddapaneni said: I have a doubt regarding the usage of export.pm. This is not a DBI question, you would be better off asking on a general Perl forum. -- Mac :}) ** I usually forward private questions to the appropriate mail list. ** Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age.
Re: Regarding DBI
On 06/19/2005 10:24 PM, prashanth said: i would like to know the detailed architecture and design of dbi. i would like to know how its been written and how it interacts with the drivers etc. i would like to know whats going on internally... any pointers to this would be appreciated. thanks in advance. The full source code is part of the distribution archive. The design philosophy is described in the documentation. http://search.cpan.org/~timb/DBI/ http://search.cpan.org/~timb/DBI/DBI.pm#Architecture_of_a_DBI_Application -- Mac :}) ** I usually forward private questions to the appropriate mail list. ** Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age.
Re: regarding DBI
On 06/20/2005 04:24 AM, vasundhar said: On 6/20/05, Clive Eisen [EMAIL PROTECTED] wrote: You want us to do your homework? For collective learning if we are asked to do so ... We must be ready Then read the fine manual with the URL I gave you before: http://search.cpan.org/~timb/DBI/DBI.pm -- Mac :}) ** I usually forward private questions to the appropriate mail list. ** Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age.
Re: Error while using Oraperl package.
On 06/16/2005 02:55 AM, Ashish Jain said: I am using Oraperl pack for my development. I get the following error at the line where I use Oraperl ( use Oraperl). You should not be using Oraperl for new development. It is a wrapper around DBI and DBD::Oracle intended to allow existing programs written for oraperl (Perl 4 with Oracle OCI compiled in) to run with minimal changes. New development should use DBI directly. Unknown error Compilation failed in require at /home/ebso/EM10103_agent/sysman/admin/scripts/semd_common.pl line 34. BEGIN failed--compilation aborted at /home/ebso/EM10103_agent/sysman/admin/scripts/semd_common.pl line 34. Compilation failed in require at /home/ebso/EM10103_agent/sysman/admin/discover/oracledb.pl line 80. I am clueless about this error. Could you please help me in resolving this. You haven't given us any clues to help you with. A copy of the relevant parts of the scripts would make it possible for us to make some better guesses. Try reducing the script to the minimum that shows the error. -- Mac :}) ** I usually forward private questions to the appropriate mail list. ** Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age.
Re: Seek help installing DBD::Oracle
On 06/14/2005 04:58 PM, McLean, Emmett (HQP) said: Hi, Opon attemping a DBD::Oracle install it appears that I must install OCI first to get oci.h ... But if I attempt to install OCI first it baulks that it can't find Oracle.pm Any suggestions? Here is the DBD::Oracle install attempt : System: perl5.008002 cygwin_nt-5.0 troubardix 1.5.5(0.9432) 2003-09-20 16:31 i686 unknown unknown cygwin Compiler: gcc -O2 -DPERL_USE_SAFE_PUTENV -fno-strict-aliasing Linker: /usr/bin/ld Sysliblist: Warning: If you have problems you may need to rebuild perl with -Uusemymalloc. Checking if your kit is complete... Looks good LD_RUN_PATH=c:/oracle/ora92/lib:c:/oracle/ora92/rdbms/lib Using DBD::Oracle 1.16. Using DBD::Oracle 1.16. Using DBI 1.48 (for perl 5.008002 on cygwin-thread-multi-64int) installed in /usr/lib/perl5/site_perl/5.8.2/cygwin-thread-multi-64int/auto/DBI/ Writing Makefile for DBD::Oracle *** If you have problems... read all the log printed above, and the README and README.help files. (Of course, you have read README by now anyway, haven't you?) /cygdrive/c/code/dbd/DBD-Oracle-1.16 make cp Oracle.pm blib/lib/DBD/Oracle.pm cp oraperl.ph blib/lib/oraperl.ph cp dbdimp.h blib/arch/auto/DBD/Oracle/dbdimp.h cp ocitrace.h blib/arch/auto/DBD/Oracle/ocitrace.h cp Oraperl.pm blib/lib/Oraperl.pm cp Oracle.h blib/arch/auto/DBD/Oracle/Oracle.h cp lib/DBD/Oracle/GetInfo.pm blib/lib/DBD/Oracle/GetInfo.pm cp mk.pm blib/arch/auto/DBD/Oracle/mk.pm /usr/bin/perl.exe -p -e s/~DRIVER~/Oracle/g /usr/lib/perl5/site_perl/5.8.2/cygwin-thread-multi-64int/auto/DBI//Drive r.xst Oracle.xsi /usr/bin/perl.exe /usr/lib/perl5/5.8.2/ExtUtils/xsubpp -typemap /usr/lib/perl5/5.8.2/ExtUtils/typemap -typemap typemap Oracle.xs Oracle.xsc mv Oracle.xsc Oracle.c gcc -c -Ic:/oracle/ora92/oci/include -Ic:/oracle/ora92/rdbms/demo -I/usr/lib/perl5/site_perl/5.8.2/cygwin-thread-multi-64int/auto/DBI/ -DPERL_USE_SAFE_PUTENV -fno-strict-aliasing -DUSEIMPORTLIB -O2 -DVERSION=\1.16\ -DXS_VERSION=\1.16\ -I/usr/lib/perl5/5.8.2/cygwin-thread-multi-64int/CORE -Wall -Wno-comment -DUTF8_SUPPORT -DNEW_OCI_INIT -DORA_OCI_VERSION=\9.2.0\ Oracle.c In file included from Oracle.h:20, from Oracle.xs:1: dbdimp.h:44:17: oci.h: No such file or directory dbdimp.h:46:20: ocidfn.h: No such file or directory In file included from Oracle.h:20, from Oracle.xs:1: The oci.h and ocidfn.h it is looking for is from Oracle's OCI or Pro*C packages, not the Perl Oracle::OCI module. It's odd that there isn't a complaint in the output of `perl Makefile.PL`. -- Mac :}) ** I usually forward private questions to the appropriate mail list. ** Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age.
Re: DBI module installation problem on solaris 10
On 06/13/2005 05:39 AM, Shailesh Mali said: I am trying to install DBI and DBD modules on Solaris 10 and getting following error. Please suggest is there any solution for this problem. Perl.xsc mv Perl.xsc Perl.c cc -c-D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -xarch=v8 -D_TS_ERRNO -xO3 -xspace -xildoff-DVERSION=\1.48\ -DXS_VERSION=\1.48\ -KPIC -I/usr/perl5/5.8.4/lib/sun4-solaris-64int/CORE -DDBI_NO_THREADS Perl.c sh: cc: not found *** Error code 1 make: Fatal error: Command failed for target `Perl.o' What's unclear about the message? You haven't installed the compiler that was used to build perl. You can either install that compiler or build your own perl with a compiler you have and then use it to build modules too. This is perl, v5.8.4 built for sun4-solaris-64int perl -v details # perl -V Summary of my perl5 (revision 5 version 8 subversion 4) configuration: Compiler: cc='cc', ccflags ='-D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -xarch=v8 -D_TS_ERRNO', ccversion='Sun WorkShop', gccversion='', gccosandvers='' -- Mac :}) ** I usually forward private questions to the appropriate mail list. ** Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age.
Re: Finding out the type expected for a placeholder
On 06/13/2005 02:56 AM, Avis, Ed said: Michael A Chase wrote: Placeholders are VARCHAR2 by default. If you know what columns you are inserting into (and you should), you can use $dbh-column_info() to find out the column types. Sorry, I gave the wrong example. In fact my query is just a select statement. I'd like to find out whether Oracle is expecting a given bind variable to be a datetime, an integer or whatever. The original example may have been wrong, but the advice still stands. You can control the type of each placeholder. To decide what type you want it to be, you have to know what column it is interacting with. -- Mac :}) ** I usually forward private questions to the appropriate mail list. ** Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age.
Re: Finding out the type expected for a placeholder
On 06/13/2005 06:28 AM, Avis, Ed said: Michael A Chase wrote: Ed said: I'd like to find out whether Oracle is expecting a given bind variable to be a datetime, an integer or whatever. The original example may have been wrong, but the advice still stands. You can control the type of each placeholder. No; because I don't know in advance what the query will be. The user is entering the SQL to use, with bind variables, and I would like to prepare the statement handle and find out from the database what the expected type of each bind variable will be. That's showing an awful lot of trust in the users' good intentions and skill. All placeholders are VARCHAR unless the type is explicitly given in the bind_param*() call. If any values provided by the user have to be treated differently, they'll have to tell you by some method unless you parse the SQL to figure it out. I think SQL::Statement might be able to help, but it is likely to take a lot of work to get right since many cases are likely to be ambiguous. -- Mac :}) ** I usually forward private questions to the appropriate mail list. ** Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age.
RE: Finding out the type expected for a placeholder
Quoting Avis, Ed [EMAIL PROTECTED]: Michael A Chase wrote: All placeholders are VARCHAR unless the type is explicitly given in the bind_param*() call. I'm not quite sure what you mean. It is quite possible for a placeholder to be of DATETIME type, for example. Perl passes the date as a string but nonetheless it has to be a date. I should have been more explicit. All DBI placeholders are VARCHAR unless explicitly defined differently in bind_param*(). This is not different from Oracle bind variables except that there is no default type for Oracle bind variables, you always have to define what type the Oracle bind variable will be before you use it. -- Mac :})
Re: Null Values Returned from a Stored Procedure
Quoting Denesa K Shaw [EMAIL PROTECTED]:
How does a script handle when null values are returned from a stored
procedure?
The same as any other NULL values.
http://search.cpan.org/~timb/DBI/DBI.pm#Notation_and_Conventions
But you are asking the wrong question. You want to know when the stored
procedure call failed. Even successful calls could pass back NULL, depending
on the procedure.
I checked for Null values in a individual string, but it seemed not to
make it to my if statement.
Where? I don't see any checks for NULL (or undef) in any of the code you
provided.
my $sql = qq(
BEGIN
package.procedure(:p1,:p2,TO_DATE(:p3,:fmt),:out_cursor);
END;
);
You didn't mention what RDBMS you are using, but this looks like Oracle PL/SQL.
my $func = $dbh-prepare($sql);
Unless you've set RaiseError to 1, the only hint you have if prepare() failed is
that the handle ($func) is undef and that doesn't tell you what went wrong.
http://search.cpan.org/~timb/DBI/DBI.pm#Outline_Usage
http://search.cpan.org/~timb/DBI/DBI.pm#ATTRIBUTES_COMMON_TO_ALL_HANDLES
You can also check the individual calls for error conditions. Look at most fo
the method call examples in the manual
(http://search.cpan.org/~timb/DBI/DBI.pm).
$func - bind_param(:fmt,mm/dd/ );
$func-bind_param(:p1,$TP_ID);
$func-bind_param(:p2,$INB_OUTB_IN);
$func-bind_param(:p3,$Formatted_RELOAD_FILE_TS);
$func-bind_param_inout(:out_cursor, \$out_cursor, 0,{ ora_type=ORA_RSET
} );
$func-execute;
if( $@) ###Check for failure of executing the Stored Procedure
I don't see an eval block and you haven't shown that you set RaiseError, so
there's no indication that $@ would be set for an error.
{
### warn Execution of stored procedure failed: $DBI::errstr\n;
warn TMH::writeConsole(*LOGFILE,Execution of stored procedure failed:
$DBI::errstr\n);
$dbh-rollback;
}
--
Mac :})
Re: Finding out the type expected for a placeholder
Quoting Avis, Ed [EMAIL PROTECTED]: Is there a way I can prepare a statement handle for some SQL like insert into some_table values (:a, :b) and then find out what types the database is expecting for the two placeholders? I'm using DBD::Oracle. Really I just want to know whether a string or an integer is expected. Placeholders are VARCHAR2 by default. If you know what columns you are inserting into (and you should), you can use $dbh-column_info() to find out the column types. http://search.cpan.org/~timb/DBI/DBI.pm#Database_Handle_Methods -- Mac :})
Re: How can I...
On 06/06/2005 02:06 PM, Peter Rabbitson said: On Mon, Jun 06, 2005 at 01:11:30PM -0700, Vergara, Michael (TEM) wrote: I want to connect to an Oracle database and test for the existence of a table. My first thought was to SELECT COUNT(*) FROM TABLE, but DBI catches the ORA-942 error and I don't know how to catch that. It throws the error during the $dbh-execute step; how do I catch an Oracle error returned from that step? Or...is there a better way? Yes there is, and it is even portable (or so they say) across different vendors. Look for the table_info () method at http://search.cpan.org/~timb/DBI-1.48/DBI.pm#Database_Handle_Methods Note that the same manual page also describes how to catch errors. You can also see the same page by running `perldoc DBI` from the command prompt. perldoc works for most other Perl modules and you can see everything about the modules in CPAN (http://search.cpan.org/). -- Mac :}) ** I usually forward private questions to the appropriate mail list. ** Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age.
Re: Seeking advice on DBI,DBD on Windows for multiple RDBMS
Quoting David Goodman [EMAIL PROTECTED]: As I understand it, I have choices of: --building under cygwin, which then requires me to run under cygwin. Cygwin also tends to be a bit slow. That may or may not matter in your target environment. On the other hand, it gets you the closest to a UNIX environment with most shells and tools being readily available. --downloading activeperl, which then requires that I install MS C runtime libraries. You won't need MSC runtime libraries, but you might need MS VC if you want to build any modules you can't find in PPD form. You can generally find PPDs from ActiveState or third parties though. --build under MinGW. I'm unsure of what the runtime dependecies would be here. It's been too long since I played with MinGW, so I can't really say anything about it. -- Mac :})
Re: problem with DBD::Oracle
On 06/01/2005 04:33 AM, Malka Cymbalista said: Thanks to everyone who tried to help. In the end I solved the problem by searching on google and finding the answer. The problem is specific to mod_perl2 which is what we are running. I'm not sure I really understood the explanation but it has to do with the fact that you can't set environment variables in the script but it should be done at startup time in the startup.pl script which is what we did and now things are working properly. I think I can explain further. The shared library loader runs in the context of the web server, not your script. That means that environment variable changes that occur in the script are too late to affect shared library loading. -- Mac :}) ** I usually forward private questions to the appropriate mail list. ** Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age.
Re: problem with DBD::Oracle
On 05/25/2005 04:46 AM, Malka Cymbalista said: Our web server is running Apache 2.0.48 with mod_perl 1.99_12 on a Sun machine running Solaris 9. We are running perl 5.8.1, DBI 1.39 and DBD-Oracle 1.16. We are trying to connect from our web server to an Oracle database that is on a different machine. The script that we wrote to do this runs perfectly well when we run it from the command line as a plain user. However, when we try running it from the web, we cannot connect to the database. As I said at the start, the script works perfectly well from the command line even when it is run from the same userid that the web runs under. Any suggestions to get this working from the web will be greatly appreciated. Thanks in advance. After environment variables, I'd check the access rights to Oracle.so and the files in $ORACLE_HOME/lib. -- Mac :}) ** I usually forward private questions to the appropriate mail list. ** Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age.
Re: Reg. perl module installation
On 05/24/2005 06:40 AM, shanthi nagarajan said: Dear Friend, I am shnathi Nagarajan working as a computer operator in Bioinformatics centre, Pondicherry University, India. Presently I am trying to connect perl and mysql. I have downloaded DBI-1.25.tar module and extracted into perl\lib\DBI-1.25. And also ran the makefile.pl it was creating different file with extension .t, .pm, .h, .pl. after that while running test.pl file with perl test.pl command It is not a good idea to extract the source archive for any Perl module in the perl tree. Read the README file that came in the archive. -- Mac :}) ** I usually forward private questions to the appropriate mail list. ** Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age.
Re: Cursor Values and Subroutines
On 05/13/2005 11:01 AM, Denesa K Shaw said:
How do I pass the value of the variable $FILE_NM returned
in the subroutine get_file to the subroutine
Update_T_Received? I'm needing this value to for my update
statement.
Normally I can easily return the string from a sub routinge,
then call it from another subroutine, but it seems to be
having an issues returning any value, since I'm looping
through my cursor. The update fails because $FILE_NM is null.
Naturally. You never passed the value to the subroutine. The way you
have the code below structured, you don't need to return the file name
from get_file(), it should be passed to Update_T_Received() instead.
You may want to reconsider the subroutine name.
You should also work on your indenting and consistent variable naming.
The code is much easier to read if it is consistent. Also include `use
strict;` and -w in the #! line.
This is not a DBI issue.
On the SQL side, it would be a good idea to establish a pattern for how
you write SQL. The parser will be able to reuse search plans if you
always pass it the same text (including letter case and whitespace) for
the same operation. As an example, I always captialize keywords,
lowercase column and table names, use one space between elements, and
use placeholders whenever the value might vary.
sub get_file
{
connect_dev_app();
$out_File_Ref_Cursor;
Should this be a my statement?
$sql = qq(
BEGIN
package.procedure(TO_DATE(:p1,:fmt),TO_DATE(:p2,:fmt
),:out_File_Ref_Cursor);
END;
);
$func = $dbh - prepare($sql);
$func-bind_param(:p1,$starttimeStamp);
$func-bind_param(:p2,$endtimeStamp);
$func -bind_param(:fmt,mm/dd/ hh24:mi:ss);
$func -bind_param_inout(:out_File_Ref_Cursor,
\$out_File_Ref_Cursor, 0,{ora_type=ORA_RSET});
$func-execute;
##
# Loop through Cursor Returned from package.procedure
##
while (($FILE_NM,$RECV_FILE_TS,$LAST_ATTEMPT_TS,$INB_OUT,$LOGICAL_NM) =
$out_File_Ref_Cursor-fetchrow_array)
{
(LOGFILE, SP returned FILE_NM: . $FILE_NM . \n);
You probably meant to have a subroutine name at the front of the line above.
return $FILE_NM;
You are returning before you call Update_T_Received().
Update_T_Received();
You should be calling this with an argument, probably $FILE_NM.
}
$func-finish;
}
sub Update_T_Received
{
You should be passing varying values as parameters. For example:
my ( $FILE_NM ) = @_;
$updatestmt =UPDATE T_Received SET CMPLT_TS =SYSDATE WHERE FILE_NM =
'$FILE_NM';
$dbh-do($updatestmt) || warn prepare: $updatestmt: $DBI::errstr;
return $dbh;
You don't always have to return a value, especially when the value won't
be useful to the calling context.
Placeholders would be a good idea here. It's very expensive to keep
reparsing the statement. Instead of the `if`, it would probably be
better to call prepare() at some point before this subroutine starts
getting called, but TMTOWTDI.
if ( ! $sthUpd ) {
## Put `my` for $sthUpd before the subroutine.
$sthUpd = $dbh - prepare(
UPDATE t_received SET cmplt_ts = SYSDATE WHERE file_nm = ? )
or die Update prepare() failed: $DBI::errstr\n;
}
$sthUpd - execute( $FILE_NM )
or warn Update failed for '$FILE_NM': $DBI::errstr\n;
}
return get_file();
return is inappropriate outside sub{}.
$dbh-disconnect();
--
Mac :})
** I usually forward private questions to the appropriate mail list. **
Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html
Give a hobbit a fish and he eats fish for a day.
Give a hobbit a ring and he eats fish for an age.
Re: insert column if not exist
On 05/12/2005 04:50 AM, Rachel Llorenna said:
Use SHOW COLUMNS on the table first; check if the desired column name
is in there, or ALTER TABLE as necessary. I think there is a
MySQL-specific command you can probably use; IF NOT .. clause, but
that's bad practise. It's probably better to use SHOW COLUMNS, since
it will allow you to easily adopt different RDBMS's. Really makes
DBI's strengths shine. :)
Do you mean column_info()?
http://search.cpan.org/dist/DBI/DBI.pm#Database_Handle_Methods
On 5/12/05, Ing. Branislav Gerzo [EMAIL PROTECTED] wrote:
I'm making script, and I'd like add column, if column not exist. My
DBS is MySQL, and I didn't find direct SQL command for this. Is
there some workaround for this ?
I'd do something like this:
my $sth = $dbh - column_info( undef, $sSchema, $sTable, $sColumn );
my @sColumnInfo = ();
eval { # The eval isn't needed if RaiseError == 0.
@sColumnInfo = $sth - fetchrow_array() || ();
};
if ( ! @sColumnInfo ) {
# The ALTER TABLE statement is likely to be RDBMS specific.
$dbh - do( ALTER TABLE my_table ADD my_column VARCHAR(100) );
}
--
Mac :})
** I usually forward private questions to the appropriate mail list. **
Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html
Give a hobbit a fish and he eats fish for a day.
Give a hobbit a ring and he eats fish for an age.
Re: Fetching BIGINT Failed
On 05/10/2005 07:10 AM, Mohankumar said: I am using the Perl version 5.005_02, DBD::ASAny::VERSION = '1.12' and ASA 9.0.0. Fetching BIGINT failed when they contained more than 8 digits. I have a workaround by converting the BIGINT to VARCHAR using convert(). But this conversion is not possible for all kinds of customized SQL Statements. I have also tried to upgrade the DBD:: ASAny VERSION 1.13. But the recommended Perl version for this upgrade is 5.6.0 and I am not ready to upgrade the perl version. You certainly aren't rushing into things. Your version is over 6 years old and the latest release (5.8.6) is over 6 months old. Your are going to run into this problem more and more often. The latest versions of DBI require 5.6.1, for example. Is there any fix for this issue without upgrading the Perl version? If so, please kindly let me know ASAP. Probably not. One major reason for new releases is to fix bugs and expand capabilities. It's unlikely anyone will remember enough about such old versions to be much help. Please note that this discussion doesn't belong on dbi-dev and the dbi-*-help addresses are for problems with the mailing list, not DBI questions. -- Mac :}) ** I usually forward private questions to the appropriate mail list. ** Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age.
Re: Spreadsheet::WriteExcel question
On 05/09/2005 12:45 PM, Robert said:
I am pulling data out of Oracle and putting it into an Excel spreadsheet.
Using this:
while ( $row = $sth-fetchrow_arrayref ) {
# this is a fast and simple way to deal with nulls:
foreach (@$row) { $_ = '' unless defined }
push (@results, @$row);
This puts all the columns fetched into one linear array. For database
contents:
row0: s, 1, A
row1: s, 3, B
row2: s, 4, C
@results = ( s, 1, A, s, 3, B, s, 4, C )
If you want to keep straight which rows are which, you need something
like this:
# Use [] to make sure each row is a separate array reference.
push @results, [ @$row ];
}
my $workbook = Spreadsheet::WriteExcel-new(report.xls);
$worksheet = $workbook-add_worksheet();
$worksheet-write_row('A2', [EMAIL PROTECTED]);
I can't say what this does because this isn't the Spreadsheet list.
--
Mac :})
** I usually forward private questions to the appropriate mail list. **
Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html
Give a hobbit a fish and he eats fish for a day.
Give a hobbit a ring and he eats fish for an age.
Re: Installing DBI and DBD::TSM
On 05/09/2005 03:47 PM, De Joe, Jackie said: I have two questions, first do I understand correctly to use DBD::TSM 1.48 I must have DBI installed? You understand correctly. Second, I am having trouble installing DBI. Here's some info: I am very much a newbie at compiling code, any help will be so appreciated!! AIX unix 5.2.0.0 chewbacca:/adsm2/perl/DBI-1.48 # which gcc /usr/bin/gcc chewbacca:/ # gcc -v Reading specs from /usr/local/lib/gcc-lib/powerpc-ibm-aix5.2.0.0/3.3.2/specs Configured with: ../gcc-3.3.2/configure : (reconfigured) ../gcc-3.3.2/configure --disable-nls : (reconfigured) ../gcc-3.3.2/configure --disable-nls Thread model: aix gcc version 3.3.2 This is irrelevant. Your local perl was build with cc_r, so you need that to build any Perl modules that have XS components. chewbacca:/ # perl -v The output from `perl -V` would be more interesting. In particular, it would tell you what compiler was used to build perl. chewbacca:/adsm2/perl/DBI-1.48 # make ... cc_r -c-D_ALL_SOURCE -D_ANSI_C_SOURCE -D_POSIX_SOURCE -qmaxmem=16384 -qnoansialias -DUSE_NATIVE_DLOPEN -DNEED_PTHREAD_INIT -q32 -D_LARGE_FILES -qlon glong -O-DVERSION=\1.48\ -DXS_VERSION=\1.48\ -I/usr/opt/perl5/lib/5.8 .0/aix-thread-multi/CORE Perl.c /bin/sh: cc_r: not found. This error means exactly what it says. The right compiler is either not installed or not in a directory in your $PATH. http://search.cpan.org/src/TIMB/DBI-1.48/README Read the section starting with IF YOU HAVE PROBLEMS. -- Mac :}) ** I usually forward private questions to the appropriate mail list. ** Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age.
Re: FW: Build dbd::oracle for both Oracle 8.x And Oracle 9.x
On 05/06/2005 05:22 PM, Saiyed, Khatir said:
I have build DBD::Oracle for 9.2.0 and works good on target Oracle 9.2.0 but
when I try to connect to 8.1.7 I get following error:
DBI connect('','lut',...) failed: ERROR OCIEnvNlsCreate (check ORACLE_HOME
and NLS settings etc.) at dbi8.pl line 11
You can connect to 8.1.7 instances using a DBD::Oracle compiled with
9.2.0, but ORACLE_HOME and the related environment variables ___MUST___
point to an Oracle installation of the version DBD::Oracle was built with.
Look at what I said in my previous email to you.
-Original Message-
From: Michael A Chase [mailto:[EMAIL PROTECTED]
Sent: Sunday, May 01, 2005 6:00 AM
To: Saiyed, Khatir
Cc: dbi-users@perl.org
Subject: Re: Build dbd::oracle for both Oracle 8.x And Oracle 9.x
...
DBD::Oracle is built for a particular API, Oracle 8.1.7 or 9.2.0 in your
case, and you must have ORACLE_HOME and the other associated variables
pointing to an installation of that version when you run your perl script.
--
Mac :})
** I usually forward private questions to the appropriate mail list. **
Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html
Give a hobbit a fish and he eats fish for a day.
Give a hobbit a ring and he eats fish for an age.
Re: Loading DBI Problem
On 05/03/2005 11:42 AM, [EMAIL PROTECTED] said: /usr/ucb/cc: language optional software package not installed *** Error code 1 make: Fatal error: Command failed for target `Perl.o' What language optional software package (name) does the error message refer to? The one that includes the same compiler that was used to build the perl provided by Sun. Where can I find this language optional software package? From Sun. -- Mac :}) ** I usually forward private questions to the appropriate mail list. ** Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age.
Re: Problem calling External Procedure with REFCURSOR
On 05/04/2005 02:40 PM, Denesa K Shaw said:
Any Ideas on why I'm getting this error? It appears not to like my date
that I'm passing to it. I printed the date to a log and it looks ok, any
ideas?
Error:
DBI::st=HASH(0x20391b00)-bind_param(...): attribute parameter
'TO_DATE('01/02/200','mm/dd/ hh24:mi:ss')'
is not a hash ref at CallTest_firstsp.pl line 90.
The error is exactly what it says it is. You put a string in a
subroutine parameter where a hash refrence is expected.
My Code:
#!/usr/bin/perl -w
use lib /usr/xxx/xxx/xxx/scripts;
use TMH;
use DBD::Oracle qw(:ora_types);
##
# Declarations Needed for Scripts
##
use constant SCRIPT_ERROR = RELOAD_HISTORICAL_DATA;
$RELOAD_FILE_NM = ;
$LAST_ATTEMPT_TS = ;
$RELOAD_FILE_TS = ;
$INB_OUTB_IN = ;
$LOGICAL_NM = ;
$starttimeStamp= '01/01/2005 08:49:00';
$endtimeStamp= '01/02/2005 08:49:00';
$In_Start_File_Ts = TO_DATE( . $starttimeStamp . ,
'mm/dd/ hh24:mi:ss');
$In_End_File_Ts = TO_DATE( . $endtimeStamp . ,'mm/dd/ hh24:mi:ss');
. . .
$sql = qq(
BEGIN
PACKAGE.PROCEDURE(:p1,:cursor_name);
END;
);
my $func = $dbh-prepare($sql);
$func-bind_param(:p1,$In_Start_File_Ts,$In_End_File_Ts);
$func-bind_param_inout(:cursor_name, \$cursor_name, 0,{
ora_type=ORA_RSET } );
$func-execute;
Placeholders (AKA bind variables) take __VALUES__, not substitution
strings. Also you can only provide one value for each placeholder. If
the procedure you are calling takes a start datetime and an end
datetime, it needs two parameters for that.
Try this (requires a procedure that takes 2 date arguments and returns
one cursor):
my $sql = qq(
BEGIN
PACKAGE.PROCEDURE( TO_DATE( :start, :fmt ), TO_DATE( :end, :fmt ),
:cursor );
END;
);
my $func = $dbh - prepare( $sql );
$func - bind_param( :start, $In_Start_File_Ts );
$func - bind_param( :end, $In_End_File_Ts );
$func - bind_param( :fmt, mm/dd/ hh24:mi:ss );
$func - bind_param_inout( :cursor, \$cursor, 0,
{ ora_type=ORA_RSET } );
--
Mac :})
** I usually forward private questions to the appropriate mail list. **
Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html
Give a hobbit a fish and he eats fish for a day.
Give a hobbit a ring and he eats fish for an age.
Re: Build dbd::oracle for both Oracle 8.x And Oracle 9.x
On 04/30/2005 10:33 PM, Saiyed, Khatir said: Can I build single dbd::oracle to work with both Oracle 8.1.7 and Oracle 9.2.0 running on the Server (Solaris 8) ? If yes, how ? If not, any other better means to acheive this. DBD::Oracle is built for a particular API, Oracle 8.1.7 or 9.2.0 in your case, and you must have ORACLE_HOME and the other associated variables pointing to an installation of that version when you run your perl script. That said, the actual connections to instances are normally made via SQL*Net which will connect to instances of either version. I recommend you build DBD::Oracle with the Oracle 9.2.0 API. -- Mac :}) ** I usually forward private questions to the appropriate mail list. ** Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age.
Re: Can't locate DBI.pm
On 04/27/2005 01:22 PM, Chekuri, Lalitha said: It is fresh install: magma:oracle $uname -a SunOS magma 5.9 Generic_117171-12 sun4u sparc SUNW,Ultra-60 My perl -v magma:oracle $perl -v This is perl, v5.6.1 built for sun4-solaris-64int (with 48 registered patches, see perl -V for more detail) For CC: magma:oracle $what cc can't open cc (26) magma:oracle $which cc /usr/ucb/cc magma:oracle $cc /usr/ucb/cc: language optional software package not installed It is exactly as Ron and Michael Nhan said, you need to either install the same compiler that was used to build your perl or you need to build perl with the comipler you have Modules __MUST__ be built with the same compiler that was used to build perl. -- Mac :}) ** I usually forward private questions to the appropriate mail list. ** Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age.
Re: DBD::Oracle and XMLType
On 04/28/2005 05:32 AM, Job Miller said: After a little searching of archives, I found the same exact problem and the same self-proposed workaround almost a year ago. Is there a better way without creating another column in a temp table? I couldn't find a follow-up to this post. There is an ORA_CLOB ora_type, but there is a 32KB limit when passing values to PL/SQL. So It appears that the workaround you found using an intermediate table is still needed for larger CLOBs. http://search.cpan.org/dist/DBD-Oracle/Oracle.pm#Placeholder_Binding_Attributes http://search.cpan.org/dist/DBD-Oracle/Oracle.pm#Handling_LOBs Job Miller [EMAIL PROTECTED] wrote: Is there any way to bind a CLOB into the XMLType.createxml() call? -- Mac :}) ** I usually forward private questions to the appropriate mail list. ** Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age.
Re: Can't locate DBI.pm
On 04/28/2005 06:54 AM, Chekuri, Lalitha said: My problem is: To install perl it is asking cc I downloaded gcc from freeware, To install gcc: if I have cc in path it is giving /usr/ucb/cc: language optional software package not installed error. If I don't have cc in the path it is saying cc not found. I am not sure what is wrong. What is wrong is that you keep coming back to the list looking like you never read our previous responses. Even when they are quoted in your email. For questions concerning building perl, start with http://search.cpan.org/~nwclark/perl-5.8.6/INSTALL and http://search.cpan.org/~nwclark/perl-5.8.6/README.solaris#GCC . You may find prebuilt gcc perls at http://www.sunfreeware.com/ and http://www.blastwave.org . For questions concerning building gcc start at http://gcc.gnu.org/install/ . -- Mac :}) ** I usually forward private questions to the appropriate mail list. ** Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age.
Re: Need Help to install DBD-Oracle on Windows 2000
On 04/26/2005 03:39 PM, Wang, Weili said: I have downloaded DBD-Oracle DBD-Oracle9-1.16-5.8.4.tar.gz from ftp.esoftmatic.com ftp://ftp.esoftmatic.com/ website and unzipped it. Could you tell me how to install DBD-Oracle on Windows 2000 system? Did you read the instructions in http://ftp.esoftmatic.com/DBI/ ? -- Mac :}) ** I usually forward private questions to the appropriate mail list. ** Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age.
Re: Oracle ref cursors - documentation patch.
On 04/27/2005 03:01 AM, Charles Jardine said: Here is a patch to the pod documentation of DBD::Oracle-1.16. The patch alters the section headed 'Binding Cursors'. diff -ur DBD-Oracle-1.16.cursor-docs/Oracle.pm DBD-Oracle-1.16/Oracle.pm Your diff has the source files reversed. Thank you for the research and improved examples. -- Mac :}) ** I usually forward private questions to the appropriate mail list. ** Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age.
Re: Can't locate DBI.pm
On 04/27/2005 01:13 PM, Reidy, Ron said: b. Install gcc properly (**AND** you will need to downgrade from the 3.x version to 2.96 as described in the Oracle installation manuals) Where did you see this? The advice is several years out of date. I've used a couple different gcc 3.x versions to build DBI and DBD::Oracle. -- Mac :}) ** I usually forward private questions to the appropriate mail list. ** Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age.
Re: DBD::Oracle Installation problem
On 04/26/2005 03:54 AM, PerlDiscuss - Perl Newsgroups and mailing lists said: When I install DBD::Oracle module, I get the following error. = ld: fatal: file /oracle/product/10/lib//libclntsh.so: wrong ELF class: ELFCLASS64 ... Summary of my perl5 (revision 5 version 8 subversion 6) configuration: Platform: ... use64bitint=undef use64bitall=undef uselongdouble=undef The build is trying to link a 64-bit library to a 32-bit perl. Look for /oracle/product/10/lib32. If you find it, edit Makefile to use that directory instead of /oracle/product/10/lib. You'll probably need to `make realclean` before making again. This appears to be fixed in the next version of DBD::Oracle. -- Mac :}) ** I usually forward private questions to the appropriate mail list. ** Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age.
Re: Transaction handling with Oracle DBD
On 04/23/2005 08:30 AM, RKris said:
Hi, I am getting data corruption when I attempt to run
several processes in parallel when making changes to
two tables.
DBD version 1.16, DBI version 1.48, Linux 2.6, Oracle 9i.
I am trying to select a counter value from table 1,
insert it into table 2 and update table 1 counter
value. This comprises a transaction. I do this 1000
times in a 'for' loop. Even after I clear out the
counter value in table 1 and run the test with two
processes, I am getting a value of 3000 in the
counter!
I'll restate that to make sure I understand. You are running two
processes that read and update the same tables. You expect them to each
loop 1000 times which would make the counter 2000, but you get a counter
value of 3000 instead.
I included the code below. I moved the connect into
the 'for' loop as I read connect automatically starts
a transaction. Please let me know if I am doing
something wrong! Thanks a lot.
You did not include the code. Your connect would have failed
immediately and the loop counter you give below is 100 instead of 1000.
Saying that connect() restarts a transaction understates what it does by
a long shot. It establishes an entirely new connection to the database.
The documentation for DBI and DBD::Oracle are available at CPAN.
http://search.cpan.org/dist/DBI/DBI.pm
http://search.cpan.org/~timb/DBD-Oracle/Oracle.pm
# #!/usr/bin/perl
#!/usr/bin/perl -w
use strict;
# It's hard to have too much error checking.
use DBI;
# DB name used by Perl Script
$PS_DBNAME = DBI:Oracle:xyz;
$DB_USERID=abc;
$DB_PASS=def;
# The connect() definitely does not belong inside the loop.
# Further, you didn't turn off autocommit in your example, so
# each INSERT or UPDATE statement gets committed immediately.
my $dbh = DBI - connect( $PS_DBNAME, $DB_USERID, $DB_PASS,
{ AutoCommit = 0, RaiseError = 1, PrintError = 0 } )
or die Couldn't connect to database: $DBI::errstr\n;
# You should also prepare all your statements outside the loop.
# I set RaiseError so individual error checks aren't needed.
my $sth = $dbh - prepare( 'SELECT counter FROM count_tab' );
my $sthI = $dbh - prepare(
INSERT INTO data_tab (counter) VALUES (?) );
my $sthU = $dbh - prepare( UPDATE count_tab SET counter = ? );
$counterval = 0;
$i = 0;
# for($i=0; $i100; $i++)
for ( $i = 1; $i 1000; ++$i )
{
#my $dbh = DBI-connect(PS_DBNAME, $DB_USERID,
# $DB_PASS)
# or die Couldn't connect to database: .
# DBI-errstr;
#$dbh-begin_work;
# begin_work isn't needed in this case.
eval
{
#my $sth = $dbh-prepare('SELECT counter FROM count_tab')
# or die Couldn't prepare statement: . $dbh-errstr;
$sth-execute() or die Couldn't execute statement: .
$sth-errstr;
# there should be only one row
#while (@data = $sth-fetchrow_array())
#{
# $counterval = $data[0];
#}
# If you only want one row, just fetch one.
$counterval = $sth - fetchrow_array();
$sthI - execute( $counterval );
#$dbh-do(INSERT INTO data_tab (counter) VALUES($counterval))
# or die Couldn't prepare statement: . $dbh-errstr;
$counterval = $counterval + 1;
#$dbh-do(UPDATE count_tab SET counter = $counterval)
# or die Couldn't prepare statement: . $dbh-errstr;
$sthU - execute( $counterval );
$sth - finish;
# Finish is not needed here since you immediately
# reexecute the statement.
# It might be useful immediately before the disconnect
# to avoid a complaint about an open statement.
};
if ($@)
{
$dbh-rollback;
# eval{} keeps error messages from being written to STDOUT,
# so pass the error message along.
die $@;
}
else
{
$dbh-commit;
}
# $dbh-disconnect;
}
# Connecting and disconnecting are both very expensive,
# so don't do them inside a data loop.
$sth - finish if $sth;
$dbh - disconnect;
exit 0;
--
Mac :})
** I usually forward private questions to the appropriate mail list. **
Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html
Give a hobbit a fish and he eats fish for a day.
Give a hobbit a ring and he eats fish for an age.
Re: Problem in connecting the DB
On 04/21/2005 09:16 PM, [EMAIL PROTECTED] said: Thanks for the reply. Can u give me link for downloading the perl oracle dbd driver for WINDOWS XP operating system.I cannot ftp in my place so i like to get it from any http site.Before installing the new one i need to uninstall the already existing driver using ppm unistall dbd-oracle' right? http://24.249.249.7/DBI/ It's usually http://ftp.esoftmatic.com/DBI/, but Jeff Urlwin's having DNS problems with his new ISP. Read the instructions carefully. -- Mac :}) ** I usually forward private questions to the appropriate mail list. ** Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age.
Re: Stored Procedures
On 04/22/2005 09:30 AM, Denesa K Shaw said: How do I pass an one parameter to a External Stored Procedure? I don't think anyone here has a clue. Without knowing what DBD driver you are using, the answer would be pure guesswork. Please read the URL in my signature. If you have a DBI 1.38 or newer, you can run installed_versions to get the information we need. perl -MDBI -e DBI-installed_versions You can read the documentation for any Perl module by running perldoc. The documentation is also available online. perdoc DBI http://search.cpan.org/ -- Mac :}) ** I usually forward private questions to the appropriate mail list. ** Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age.
Re: Problem in connecting the DB
On 04/21/2005 06:46 AM, [EMAIL PROTECTED] said: I am facing poblem in connecting a database thru perl program Here are the details about the packages which i installed in my Windows machine ... ppm describe dbd-oracle === Name: DBD-Oracle Version: 1.12 Author: Tim Bunce (dbi-users@perl.org) Title: DBD-Oracle Abstract: Oracle database driver for the DBI module Location: ActiveState Package Repository Available Platforms: 1. MSWin32-x86-multi-thread === ppm describe dbi Name: DBI Version: 1.48 Author: Tim Bunce (dbi-users@perl.org) Title: DBI Abstract: Database independent interface for Perl Location: ActiveState PPM2 Repository Available Platforms: 1. MSWin32-x86-multi-thread-5.8 Script which i was trying to run: use strict; use DBI; my $dbh = DBI-connect( 'dbi:oracle:tar', 'scott', 'tiger', ) || die Database connection not made: $DBI::errstr; $dbh-disconnect; While running the above script i am getting the following error C:\perl C:\Documents and Settings\Administrator\Desktop\connect.pl install_driver(oracle) failed: Can't locate DBD/oracle.pm in @INC (@INC contains : C:/Perl/lib C:/Perl/site/lib .) at (eval 1) line 3. Perhaps the DBD::oracle perl module hasn't been fully installed, or perhaps the capitalisation of 'oracle' isn't right. Available drivers: DBM, ExampleP, File, ODBC, Proxy, Sponge. at C:\Documents and Settings\Administrator\Desktop\connect.pl line 3 The problem is exactly what it says (perhaps the capitalisation of 'oracle' isn't right), you don't have DBD::oracle installed. The correct DSN string is dbi:Oracle:tar. Note the capital 'O'. -- Mac :}) ** I usually forward private questions to the appropriate mail list. ** Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age.
Re: inserting data coming from a hash
On 04/21/2005 10:11 AM, Robert said:
This was a solution:
my $insert_stmt = Insert into TABLE (name, date, type, federal, active)
values( :name, :date, :type, :federal, :active );
my $sth = $dbh-prepare( $insert_stmt );
my $holidays;
foreach my $name ( keys %holidays ) {
$sth-bind_param( ':name', $name );
$sth-bind_param( ':'.$_, $holidays-{$name}-{$_} ) foreach keys
%{$holidays-{$name}};
$sth-execute() or die Cannot execute SQL statement:
$DBI::errstr\n;
}
This may only work with DBD::Oracle. You also have to be very careful
to have exactly the same hash keys as your bind variable names.
--
Mac :})
** I usually forward private questions to the appropriate mail list. **
Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html
Give a hobbit a fish and he eats fish for a day.
Give a hobbit a ring and he eats fish for an age.
Re: DBD::Oracle install problem On Solaris 8
On 04/19/2005 10:18 AM, Hemanth Kumar said: I am trying to install DBD 1.3 with DBI 1.37 on Solaris and the database version is Oracle 9.2. When I run the make command it returns the following error: In file included from Oracle.h:20, from Oracle.xs:1: dbdimp.h:44: ocidfn.h: No such file or directory dbdimp.h:57: ociapr.h: No such file or directory make: *** [Oracle.o] Error 1 I don't have an Oracle installation kit handy, so I'm not sure of the exact name, but you need to install OCI or Pro*C. Which ever package, it will contain those files and several others you'll also need. -- Mac :}) ** I usually forward private questions to the appropriate mail list. ** Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age.
Re: having a problem with trailing spaces
On 04/19/2005 10:42 AM, Ian Harisay said: I am having problem with spaces. I am loading data from a normalized schema into a denormalized table. Many of the fields I am copying originally come from free form fields and we don't attempt to clean them up at all. What we grab in the form is what we enter into the database. The loader program is Java based. My program to synchronize data between the normalized tables and the denormalized table is written in perl. My perl program appears to be stripping trailing spaces. I dynamically build my sql statement with the following function. Please don't ask me why I don't just use a view or a materialized, because that would be my choice if I had one in this matter. http://search.cpan.org/dist/DBD-Oracle/Oracle.pm#Database_Handle_Attributes Look at ora_ph_type. http://search.cpan.org/dist/DBD-Oracle/Oracle.pm#Placeholder_Binding_Attributes Look at ora_type. By default the Oracle OCI removes trailing spaces from VARCHAR placeholders which are what DBD::Oracle uses by default. You can change the connection default placeholder type with ora_ph_type or you can change the type for a particular placeholder with ora_type. -- Mac :}) ** I usually forward private questions to the appropriate mail list. ** Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age.
Re: Describe table
On 04/18/2005 10:49 AM, Mike Scott said: I'm using DBI and DBD::Oracle. I'd like to be able to describe a table, but 'describe table' fails no matter how I try to pass it to DBI. I feel certain that this is a FAQ and so apologise in advance. I have tried to track down an answer, but describe and table are hard words to seach for. Can anyone help me out? I just want the info describing the table that I can see in sqlplus. You've gotten several responses that query Oracle metadata tables. A more portable method is to use the DBI methods. http://search.cpan.org/dist/DBI/DBI.pm#Catalog_Methods Search for the individual method names (e.g., column_info) for more details. -- Mac :}) ** I usually forward private questions to the appropriate mail list. ** Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age.
Re: how to get datatype of columns of a table in perl script
Quoting Vamsi_Doddapaneni [EMAIL PROTECTED]: I am new to perl just (1 1/2 month experience.). I am developing perl script which connects to oracle database and selects rows from a (x) table and then inserts them into its corresponding table in db2. So, while inserting i need to find what is the datatype of columns , since we need to use quotes for varchar,date etc. You don't need to worry about number or varchar types if you use placeholders. Both DBD::Oracle and DBD::DB2 support them. Dates will need some special handling to convert the strings you get from Oracle (use TO_CHAR() to make sure you get what you want), but even with them placeholders will minimize problems. http://search.cpan.org/~timb/DBI/DBI.pm The CPAN site is the first place to look for modules and the search site makes it easy to find them and their documentation. You may want to get some books as well. Nearly anything from O'Reilly is worth the cost: http://www.oreilly.com/ . I particularly recommend the following titles. Programming Perl, 3rd Edition Programming the Perl DBI: Database programming with Perl Once you've gotten further, these titles may be useful too. Perl Cookbook, 2nd Edition Advanced Perl Programming -- Mac :})
Re: return parameter value if query does't match
On 04/07/2005 04:05 AM, xaver biton said:
i've 2 select query. the first one is like
my $sql = $dbh-prepare(q{select * from test});
If all you want is col4, it would be safer to use SELECT col4
the second:
my $sql1 = $dbh-prepare(q{select * from test1 where test1.row = ?})
$sql-execute();
while(my @row = $sql-fetchrow_array){
$sql1-execute($row[3]);
You don't need to put quotes around $row[3].
while(my @row1 = $sql1-fetchrow_array){
print $row1[1]\n;
}
}
now the question how do I get the valuew of $row[3] if the query return
undef as well doesn't match.
I am not sure I understand.
Do you mean that $row[3] query is undef? If so, that means that the
fourth column in the query is NULL. It might be safer to list the
columns you want instead of using *.
Do you mean the second query didn't return a row? That is not an error,
but then the print won't happen. If you want to detect that situation,
add a counter and do something if it remains 0 after the inner loop.
Perhaps if you list what you want to happen and what is happening in
more detail we can help.
--
Mac :})
** I usually forward private questions to the appropriate mail list. **
Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html
Give a hobbit a fish and he eats fish for a day.
Give a hobbit a ring and he eats fish for an age.
Re: mysqlhotcopy + rsync and incremental backups
On 03/26/2005 04:12 PM, Jesus Altuve said: I was wondering if it is possible to use mysqlhotcopy to use rsync instead of cp in order to make an incremental backup of a database. cp is the only copy method supported, but if you included the 'only method supported' statement it is because you were thinking on maybe using rsync. is this correct? or complete nonsense? I'm experimenting... and I will try it, I just find it very strange that a search for mysqlhotcopy rsync and incremental doesn't come up with someone suggesting the same thing. You may have written to the wrong list. I don't find any mention of msqlhotcopy in the documentation for either DBI or DBD::mysql. -- Mac :}) ** I usually forward private questions to the appropriate mail list. ** Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age.
Re: Sending Mail
On 03/24/2005 05:31 PM, Gav said:
Can someone point me as to what is wrong with this :-
#!/usr/bin/perl
use CGI::Carp qw(fatalsToBrowser);
use CGI qw(:standard);
use DBI;
$|=1;
open(MESSAGE, /home/site/public_html/cgi-bin/email.txt) or die cannot
open message: $!;
flock(MESSAGE, 2) or die cannot lock message exclusively: $!;
my @msgfile = MESSAGE;
close (MESSAGE) or die cannot close message file: $!;
I don't think you need to lock the file to read it, especially since you
are closing it so quickly. It might make some sense if the file is
updated frequently by other processes.
my ($dbh, $sth, @row);
$dbh = open_dbi();
my $sth = $dbh-prepare(SELECT email_address FROM users);
$sth-execute();
while(@row = $sth-fetchrow_array){
Since you aren't checking for errors in any of the three lines above,
you haven't a clue whether an error occured or not.
I would either set RaiseError or do something like this:
my $sth = $dbh-prepare(SELECT email_address FROM users)
or die prepare() failed, $DBI::errstr\n;
$sth-execute();
or die execute() failed, $DBI::errstr\n;
while(@row = $sth-fetchrow_array){
...
}
die fetchrow_array() failed, $DBI::errstr\n if $DBI::err;
$address = $row[0];
chomp($address);
The chomp() shouldn't be needed for values stored in the database.
open (SENDMAIL, |/usr/sbin/sendmail -t) or die cannot open sendmail: $!;
print SENDMAIL To:$address\n;
print SENDMAIL From: [EMAIL PROTECTED];
print SENDMAIL Subject: ITgazette $address\n\n;
print SENDMAIL @msgfile;
}
sub open_dbi
{
my $host = 'localhost';
my $db = 'databasename';
my $db_user = 'username';
my $db_password = 'password';
my $dbh = DBI-connect(dbi:mysql:$db:$host, $db_user, $db_password,
{RaiseError = 0, PrintError = 0} )
or err_trap(Cannot connect to the database);
If you are going to disable error reporting when you connect, you need
to explicitly check for errors at nearly every DBI method call. You
would probably be better off with {RaiseError = 1}.
http://search.cpan.org/~timb/DBI/DBI.pm#Simple_Examples
I don't find your DSN syntax in the DBD::mysql manual, but since this is
the one place where you're checking for errors, I guess it is working.
http://search.cpan.org/~rudy/DBD-mysql/lib/DBD/mysql.pm
return $dbh;
}# end: open_dbi
sub err_trap
{
my $error_message = shift(@_);
die $error_message\n
ERROR: $DBI::err ($DBI::errstr)\n;
}# end: err_trap
$sth-finish;
$dbh-disconnect();
print Location: http://sitename/index.php\n\n;;
---
It does not produce an error message, it just does not send the emails
out,but it does redirect to the location.
You are getting an error somewhere, but you aren't checking for errors
except in the connect().
--
Mac :})
** I usually forward private questions to the appropriate mail list. **
Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html
Give a hobbit a fish and he eats fish for a day.
Give a hobbit a ring and he eats fish for an age.
Re: Perl/postgres problems
On 03/21/2005 12:34 PM, Simon Windsor said: I am having problems accessing postgres from mod-perl on one box. The error I am getting is install_driver(Pg) failed: Can't load '/usr/lib/perl5/site_perl/5.8.5/i386-linux-thread- multi/auto/DBD/Pg/Pg.so' for module DBD::Pg: libpq.so.3: failed to map segment from shared object: Permission denied at /usr/lib/perl5/5.8.5/i386-linux-thread-multi/DynaLoader.pm line 230.\n at (eval 173) line 3\nCompilationfailed in require at (eval 173) line 3.\nPerhaps a required shared library or dll isn't installed where expected I have run ldconfig, and a simple test scripts work ok. Has anyone seen this before, and know a solution? Make sure the process running mod-perl has the same environment variables set as the process you ran the test scripts in. Make sure the account running mod-perl has permission to use Pg.so and all the Postgres shared libraries. http://search.cpan.org/dist/DBI/DBI.pm#Why_doesn't_my_CGI_script_work_right? http://search.cpan.org/dist/DBI/lib/DBI/FAQ.pm (search for 4.4) -- Mac :}) ** I usually forward private questions to the appropriate mail list. ** Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age.
Re: Oracle client and DBD::Oracle
On 03/19/2005 06:44 AM, John said: Has anyone tried to install DBD::Oracle with success while Oracle Client is installed? In my case oci.h and some other headers were not found and i ultimately enforced to install the whole database server for the job. Is there any known trick about that? Oracle Client is necessary but not sufficient. oci.h and the other required files are usually in Pro*C or OCI or some other package like that. Which package varies with Oracle version and platform. -- Mac :}) ** I usually forward private questions to the appropriate mail list. ** Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age.
Re: How to get trace from Class::DBI ?
Please do *not* start a new question by replying to an old one as this causes several problems: 1) most email software will correctly think that your email is a response to the email you are replying to, and will indicate this by displaying your email as a child of the original email 2) because of this, people not interested in the original email may ignore your question 3) people may think that the original email has a reply (yours) so that they do not have to give it a reply So in conclusion, more people will read your email if it looks like the start of a new thread rather than the reply to an old one. On 03/18/2005 08:50 AM, Tom Gazzini said: What's the best way to use the 'trace' function of DBI to get debug output while using Class::DBI ? I'm using the standard Class::DBI-connection method (so not passing a DBI handle myself). Since I'm not familiar with Class::DBI, I used Google to find the Class::DBI main page. There I searched for 'trace' and found two pages. In the Beginner's Guide page, I found: = Turning on tracing to see your sql my $dbh = Music::Artist-db_Main; $dbh-trace(2); = In the See All SQL page, I found: = ...or log to a file using the DBI_TRACE environment variable... DBI_TRACE=2=/tmp/logfile ./myscript.pl ...or by adding the following... DBI-trace(2 = /tmp/logfile); in your code. See the documentation for DBI::Profile, DBI::ProfileDumper and DBI::Trace for more info. = http://www.class-dbi.com/cgi-bin/wiki/index.cgi?HomePage http://www.class-dbi.com/cgi-bin/wiki/index.cgi?BeginnersGuide http://www.class-dbi.com/cgi-bin/wiki/index.cgi?SeeAllSQL -- Mac :}) ** I usually forward private questions to the appropriate mail list. ** Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age.
Re: DBD::Oracle - closing cursors returned from PL/SQL
On 03/16/2005 08:25 AM, Charles Jardine said:
perldoc DBD::Oracle says, under Binding Cursors:
To close the cursor you (currently) need to do this:
$sth3 = $dbh-prepare(BEGIN CLOSE :cursor; END;);
$sth3-bind_param_inout(:cursor, \$sth2, 0, { ora_type =
ORA_RSET } );
$sth3-execute;
This works, and demonstrates that a cursor got from PL/SQL
may be passed back to PL/SQL. However, I doubt that it is
necessary. Tracing shows that, if I let $sth2 go out of scope,
the destructor frees the OCI statement handle. Surely this
will cause the cursor's resources will be released.
Does anyone know why this was once thought to be necessary?
It frees the client side handle, but probably doesn't tell the RDBMS
that the cursor is no longer needed.
--
Mac :})
** I usually forward private questions to the appropriate mail list. **
Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html
Give a hobbit a fish and he eats fish for a day.
Give a hobbit a ring and he eats fish for an age.
Re: NULLs in Text::CSV_XS and DBD::CSV
On 03/16/2005 09:50 AM, NIPP, SCOTT V (SBCSI) said:
Still banging my head into a wall... Now I am getting NULLs
inserted as expected into the database, but I am getting errors on the
compare.
my @old = $test-fetchrow_array ();
foreach $n (0..20) {
chomp($file_val = $data[$n]);
$file_val =~ s/\s*$//;
chomp() is unneeded if it's followed by s/\s*$//.
#chomp($db_val = $old[$n+1]);
chomp() is unneeded and probably dangerous for values fetched
from the database. Is there a reason you are fetching an extra column
from the database? You're comparing column[1] to field[0] ...
column[21] to field[20].
#if ($file_val eq ) {
# print NULL found in $n value. $file_val\n;
# $file_val = 0;
#}
# print Comparing $file_val to $db_val. \n; # Testing line
if (defined $old[$n+1]) {
if ($file_val eq $$old[$n+1]) {
You already noticed the extra '$'. Is this line 65?
$update = 1;
} else {
$update = 0;
# print Comparing $file_val to $db_val. \n; # Testing line
last;
}
}
}
Here are the errors.
Name main::old used only once: possible typo at ./host_tbl_update2.pl
line 65.
Use of uninitialized value in string eq at ./host_tbl_update2.pl line
65, CSV line 1.
Use of uninitialized value in string eq at ./host_tbl_update2.pl line
65, CSV line 2.
You probably need to change the NULLs you are now getting in the
database back to . I'd feel better about my diagnosis if I knew which
line is line 65. Another possiblity is that $db_val is being used
somewhere, but you've commented out where it was set.
--
Mac :})
** I usually forward private questions to the appropriate mail list. **
Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html
Give a hobbit a fish and he eats fish for a day.
Give a hobbit a ring and he eats fish for an age.
RE: Value deletion
Quoting Moreno, Javier [EMAIL PROTECTED]: Actually it is now getting worse. It has AutoCommit set to 0 so I am doing this: $::sql = UPDATE GLOBALSettings SET Lang = ?; $::crt = $::lang_code; Debug(About to prepare: $::sql with criteria: $::crt); $::sth = $::dbh-prepare($::sql) or Error(Unable to prepare statement.); $::rec = $::sth-execute ($::crt) or Error(Unable to execute statement); $::dbh-commit; As I recommended before, please add $DBI::errstr to your error messages, otherwise you get no clues about what went wrong. But it is now writing a NULL value on the DB. That Debug line shows: About to prepare: UPDATE GLOBALSettings SET Lang = ? with criteria: SP So the value is fine but it is not updating right. What I found was that the second field on the SQL table was set to no nulls so the update was failing on the webserver log because of that. BTW responding to the app bombs ambiguity, I use the value for an auto META REFRESH value to find out the directory of the language I am running. The problem may be in placeholder handling in the driver. Add `DBI-trace( 4, log_file.log);` (without the outer quotes) before prepare() to get more information about what is happening. Higher numbers up to about 9 give you more details. http://search.cpan.org/~timb/DBI/DBI.pm#TRACING http://search.cpan.org/~timb/DBI/DBI.pm#trace http://search.cpan.org/~timb/DBI/DBI.pm#Why_doesn't_my_CGI_script_work_right? -- Mac :})
Re: Oracle SQL V8
On 03/10/2005 01:52 PM, Graeme St. Clair said: I just tripped over a curious thing. It turned out that my d-b contained the occasional column with accidental leading blanks. I altered the query to:- SELECT TRIM(LEADING ' ' FROM CUST_NAME) AS CN, COUNT(*) AS C FROM DB GROUP BY CN ORDER BY CN and ir barfed 00904 Invalid column name... at:- ...GROUP BY *CN... It worked when I put the full TRIM expression in place of the abbreviation/alias CN. Is it meant to work like that? If so, why? It's just how Oracle handle that type of statement. -- Mac :}) ** I usually forward private questions to the appropriate mail list. ** Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age.
Re: PLease remove me from the list
On 03/09/2005 01:02 PM, James B Schmidt said: Please remove me from the list. List-Help: mailto:[EMAIL PROTECTED] List-Unsubscribe: mailto:[EMAIL PROTECTED] If you are sending the unsubscription request from a different email account than the one being sent to (see Return-Path and Received in the email headers), send an email to the Help address to get more instructions. -- Mac :}) ** I usually forward private questions to the appropriate mail list. ** Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age.
RE: DBI connect as sysdba
Quoting edward pena [EMAIL PROTECTED]: Actually, that does not work consistently. The most common cause for it to not work is mentioned in the last paragraph of the document extract below. Many Oracle instances are set up to refuse remote connections as DBA or OPER. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 08, 2005 12:38 PM To: [EMAIL PROTECTED]; dbi-users@perl.org Subject: Re: (Fwd) FW: DBI connect as sysdba ora_session_mode ... It has been reported that this only works if $dsn does not contain a SID so that Oracle then uses the value of the ORACLE_SID (not TWO_TASK) environment variable to connect to a local instance. Also the username and password should be empty, and the user executing the script needs to be part of the dba group or osdba group. -- Mac :})
Re: Running stored procedures
Quoting Moreno, Javier [EMAIL PROTECTED]: This is probably a dumb question, but how do I run stored procedures with the DBI? I am using DBD::ODBC. It's pretty low level, but you may find some of the links in the perldoc useful. http://search.cpan.org/~jurl/DBD-ODBC/ODBC.pm A Google search may turn up more. -- Mac :})
Re: perl oracle connection not working from browser
On 02/24/2005 02:21 AM, davinder kohli said: I am having Perl 5.8 version and Oracle 10g installed on Windows server 2003. I am able to connect as well as execute Oracle queries from the command line. But wfrom the browser I am not able to connect to ORACLE database. Please help me. Thanks in advance. http://search.cpan.org/~timb/DBI-1.47/lib/DBI/FAQ.pm, search for 4.4 -- Mac :}) ** I usually forward private questions to the appropriate mail list. ** Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age.
Re: Oracle error handling
On 02/24/2005 08:08 AM, susan lam said:
I'm new to perl. I apologize if this is not the right
place to post newbie questions.
This is the right forum for DBI and DBD questions.
If mytable does not exist, I would like to trap the
Oracle error message and write the message to an
Oracle table. I know how to write to a table but I do
not know how to trap the message.
http://search.cpan.org/~timb/DBI/DBI.pm#RaiseError
http://search.cpan.org/~timb/DBI/DBI.pm#Transactions
If RaiseError = 0, you can check each individual method for failure
similar to the way you checked connect(). That check actually has no
effect below because {RaiseError = 1} will cause connect() to die
instead of returning if it fails.
If RaiseError = 1, you can wrap the method calls in eval{} and then
check the contents of $@ and $DBI::errstr.
$dbh =
DBI-connect(dbi:Oracle:mydb,scott,tiger,{RaiseError
= 1, AutoCommit = 0}) || die Database connection
failed: $DBI::errstr;
$sql = qq {select * from mytable};
$sth = $dbh-prepare($sql);
$sth-execute;
$dbh-disconnect;
Also, if mytable does exist, how can I obtain the row
count returned from the above query and assign it to a
variable?
http://search.cpan.org/~timb/DBI/DBI.pm#rows
The only reliable way to get a count of rows is to count them.
--
Mac :})
** I usually forward private questions to the appropriate mail list. **
Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html
Give a hobbit a fish and he eats fish for a day.
Give a hobbit a ring and he eats fish for an age.
Re: New User-Defined Function Syntax in SQL::Statement
On 02/21/2005 02:23 PM, Jeff Zucker said: $dbh-do(CREATE FUNCTION foo); # pre-declares function foo, a perl subroutine in current package $dbh-do(CREATE FUNCTION foo AS Bar::baz); # pre-declares function foo, using baz, a subroutine in package Bar Since AS usually introduces an alias, this might make more sense as: $dbh - do( CREATE FUNCTION Bar::baz AS foo ); # predeclares function foo, using baz, a subroutine in package Bar -- Mac :}) ** I usually forward private questions to the appropriate mail list. ** Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age.
Re: DBI tand subroutine
On 02/19/2005 02:25 PM, xaver biton said:
wht's wrong with the following code, I should recieve
'49201','49203',
'49','49203',
'49203','49203'
and if I don't use the sub I do that, but if I use the sub I recieve only
'491'
To make sure I understand, if you take the guts out of area() and run
that immediately after the prepare() call you get three rows of two
elements each, but if you run area(), you get just one row of one
element? That doesn't seem possible. Make sure you aren't calling
finish() or overwriting the statement handle.
I don't think you are showing us the running code. For example, there
is no quoting in the print statement below and all the output would be
scrunched together on one line (492014920349492034920349203).
The DBI code looks valid. I do have some improvements to suggest.
sub area{
my $para = @_;
$zone_area_code-execute($para) or die;
# Always include some information about the error.
$zone_area_code - execute( $para )
or die area(): execute failed, $DBI::errstr\n;
# Fetching with bind_columns() is more effecient and
# easier to set up once you are used to it.
# http://search.cpan.org/dist/DBI/DBI.pm#bind_columns
my ( $area_code_from, $area_code_to );
$zone_area_code - bind_columns( \( $area_code_from, $area_code_to ) );
while ( $zone_area_code - fetch ) {
print '$area_code_from', '$area_code_to'\n;
}
die area(): fetch failed, $DBI::errstr\n if $DBI::err;
while (my @zone_area_code_row = $zone_area_code-fetchrow_array){
my $area_code_from = $zone_area_code_row[0];
my $area_code_to = $zone_area_code_row[1];
print $area_code_from, $area_code_to;
}
}
area(49203);
--
Mac :})
** I usually forward private questions to the appropriate mail list. **
Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html
Give a hobbit a fish and he eats fish for a day.
Give a hobbit a ring and he eats fish for an age.
Re: Can't save edited values to database
It's nice that everyone is so helpful with the CGI problem, but this isn't a CGI
list. DBI comments below.
Quoting MCMULLIN, NANCY [EMAIL PROTECTED]:
($pnotes) = $dbh-selectrow_array(SELECT notes FROM table WHERE t1 =
'$pt');
http://search.cpan.org/~timb/DBI/DBI.pm#Placeholders_and_Bind_Values
Oracle can avoid the search planning steps if you use a placeholder instead of
pasting the value into the SQL text. For a frequently used query, the affect
of system performance can be quite dramatic.
http://search.cpan.org/~timb/DBI/DBI.pm#ATTRIBUTES_COMMON_TO_ALL_HANDLES
http://search.cpan.org/~timb/DBI/DBI.pm#Outline_Usage
See RaiseError.
You also really need some form of error checking for this statement. In the
example below, I use explilcit error checking on the individual statement. You
could also set RaiseError either on connect() or sometime prior to the
statements you want implicit error checking for.
($pnotes) = $dbh - selectrow_array(
SELECT notes FROM table WHERE t1 = ?, {}, $pt )
or die Failed to fetch notes, $DBI::errstr\n;
my $sth = $dbh1-prepare(UPDATE table SET notes='$notes' WHERE t =
'$pt') or die Preparing: , $dbh-errstr;
$sth-execute or die Executing: , $sth-errstr; # this is just
It is *very* dangerous to blindly paste strings from external sources into your
SQL. You are also likely to have problems with notes containing aprostrophes
('). Using placeholders avoids both issues along with being much more
efficient.
my $sth = $dbh1 - prepare( UPDATE table SET notes = ? WHERE t = ? )
or die Note update prepare failed, $DBI::errstr\n;
$sth - execute( $notes, $pt )
or die Note update execute failed, $DBI::errstr\n;
--
Mac :})
Re: Curretn DBD for Oracle
On 02/18/2005 02:33 PM, Douglas Greenwalt said: What DBD version of Oracle is the most current and where do I get it? Currently put on DBI/5.8.3/DBD-Oracle.ppd from esoftmatic.com. If you're running ActivePerl in MSWin and not ready to buuild it yourself, esoftmatic.com is the best source. http://search.cpan.org/dist/DBD-Oracle/ The absolute latest version is 1.16. -- Mac :}) ** I usually forward private questions to the appropriate mail list. ** Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age.
Re: Cannot Insert into Oracle
On 02/14/2005 11:10 AM, [EMAIL PROTECTED] said:
If I don't use strict, I get the following errors:
You should (nearly) always use 'use strict;'. It helps prevent single
errors from combining into real rats' nests.
Database error: DBI::st=HASH(0x239094)-bind_param(...): attribute
parameter 'SQL_VARCHAR' is not a hash ref at ./snmp.pl line 38, INFILE
line 7867.
If I do use strict, I get the following errors:
Bareword SQL_VARCHAR not allowed while strict subs in use at
./snmp.pl line 38.
All those errors have the same cause, SQL_VARCHAR wasn't defined before
it was used.
See http://search.cpan.org/dist/DBI/DBI.pm and search for /sql_types/.
Here is my modified code:
#!/usr/bin/perl
use strict;
use DBI;
# Define the SQL_* values.
use DBI qw(:sql_types);
eval {
$sth-bind_param( 1, $db-quote($mib), SQL_VARCHAR );
$sth-bind_param( 2, $db-quote($var), SQL_VARCHAR );
$sth-bind_param( 3, $db-quote($vartype), SQL_VARCHAR );
$sth-bind_param( 4, $db-quote($varvalue), SQL_VARCHAR );
$sth-execute();
Use either placeholders or quote(), not both. Since all your variables
are SQL_VARCHAR (the default type), you can use:
$sth - execute( $mib, $var, $vartype, $varvalue );
instead of everything else in the eval{} block. That also means you
don't need to define the SQL_* types.
};
--
Mac :})
** I usually forward private questions to the appropriate mail list. **
Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html
Give a hobbit a fish and he eats fish for a day.
Give a hobbit a ring and he eats fish for an age.
Re: Mixing select and print statements using DBI, DBD::Sybase
Quoting David Goodman [EMAIL PROTECTED]: Hello Michael: You write: Theoretically yes - at least for queries (select statements). Print and raiserror statements *should* normally be sent back in the same way as you'd get them in isql, but you may be seeing buffering differences where the error handler prints to STDERR and your data rows (from fetchrow) are printed to STDOUT. In my case both the messages and the rows are inserted to an array and later printed to a file. So I don't think that the difference between buffering of STDERR and STDOUT is at work. Would you be able to think of an alternative explanation for messages coming back to the message handler before the rows arrive? Just a SWAG since I don't use Sybase. Could the SELECT var=value statements be being processed in the local client instead of the database? -- Mac :})
Re: What happened to dbish?
On 02/04/2005 10:38 PM, Jeff Macdonald said: It seems it is no longer included, and scanning the Changelogs doesn't give me a clue why. Is there a better alternative? http://search.cpan.org/~tlowery/DBI-Shell/ From DBI/Changes: =head2 Changes in DBI 1.34,28th February 2003 ... Removed old DBI::Shell from distribution and added Tom Lowery's improved version to the Bundle::DBI file. -- Mac :}) ** I usually forward private questions to the appropriate mail list. ** Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age.
Re: problems building DBD::Oracle 1.16 - help - please
Quoting Adam Stoller [EMAIL PROTECTED]: On Feb 3, 2005, at 1:45 PM, Adam Stoller wrote: For the record - incompatible in this case apparently means that we had a 64-bit version of the library (client installation) on the machine where everything else was 32-bit (gcc, perl, etc.) - and has **nothing** to do with using a version of perl that was built on this machine *or* using the same version of gcc that was used to build perl. Thank you for the update. As for adding something to one of the READMEs, please suggest a place where you would have been likely to notice it. Once we fixed that, we found that with ORACLE_HOME being protected (750 directory access) using 'sudo make' wasn't sufficient - and we had to change the access rights on ORACLE_HOME to 755 That seems to be a common problem. We got past that, it builds, but now 'make test' seems to fail to connect to the DB - and we're working on tracking that down. Have you set $ORACLE_HOME, ($ORACLE_SID or $TWO_TASK), and $ORACLE_USERID as mentioned in README? -- Mac :})
Re: get one column
On 01/28/2005 03:55 AM, Ing. Branislav Gerzo said:
Hi all!
I have subroutine, that checks, if I already have in DB specified row:
sub image_exist {
my ($id, $date) = @_; #id is _always_ number
my $sth = $mydbh-prepare_cached(select ID from pic where id = ?);
$sth-execute($id);
my $rows = $sth-fetchall_arrayref();
if ( @$rows[0] ) {
print $id exist\n;
} else {
print $id not exist\n;
}
}
I am pretty sure, this can be done better. Also I am curious about
this - when I change statement to:
my $sth = $mydbh-prepare_cached(select ID from pic where id = $id);
$sth-execute();
In addition to Thilo's suggestions, if you are calling this routine
often, you can prepare once and execute many times.
my image_exist_sth;
sub image_exist {
if ( ! $image_exist_sth ) {
my $sql = select ID from pic where id = ?;
$image_exist_sth = $mydbh - prepare( $sql )
or die Can't prepare '$sql', $DBI::errstr\n;
}
my ( new_id ) = $mydbh - selectrow_array( $image_exist_sth,
{}, $id ) or die Can't check for image, $DBI::errstr\n;
print( defined $new_id
? $id exists\n : $id does not exist\n );
}
--
Mac :})
** I usually forward private questions to the appropriate mail list. **
Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html
Give a hobbit a fish and he eats fish for a day.
Give a hobbit a ring and he eats fish for an age.
Re: CAN-2005-0077
Quoting Thomas L Shinnick [EMAIL PROTECTED]: At 09:26 1/28/2005, Peter J. Holzer wrote: I just read a Debian advisory about CAN-2005-0077, A link would have been nice... http://cve.mitre.org/cgi-bin/cvename.cgi?name=CAN-2005-0077 says under review http://archives.neohapsis.com/archives/linux/debian/2005-q1/0178.html says there's a fix released for debian ? The diff at http://security.debian.org/pool/updates/main/libd/libdbi-perl/libdbi-perl_1.21-2woody2.diff.gz is quite long That's because the patch includes documentation changes and a bunch of Debian control information. You can find the actual code changes by searching for /\.PL/ and /\.pm/ . Where can one find just the + * Applied patch by Javier Fernández-Sanguino Peña to not create an +insecure temporary file anymore [dbiproxy.PL, lib/DBI/ProxyServer.pm, +CAN-2005-0077] And how can you figure out what DBI version the libdbi-perl-1.21-2 is supposed to be equivalent to? Wow, debian is getting strange looks from me, bygosh! The first digits after the first dash (1.21) indicate the source package version, so the report is on a fairly old version, but the problem existed in the source package until this week. RPMs use a similar numbering scheme for version and revision. If Woody is that far back (1.21 vs 1.46), you may want to load DBI from unstable. The urgency level assigned to the advisory was low, so it may take a while for a version from the fixed source to get distributed. claiming Javier Fernández-Sanguino Peña from the Debian Security Audit Project discovered that the DBI library, the Perl5 database interface, creates a tmporary PID file in an insecure manner. This can be exploited by a malicious user to overwrite arbitrary files owned by the person executing the parts of the library. I certainly hope no one is running their webserver as root. I haven't seen this discussed on this list yet. Will this be fixed in DBI 1.47, and if so, when can we expect that release? The advisory was reported on dbi-dev earlier this week and Tim has fixed the upcoming version. His comment at the time indicated that he intends to release the new version soon. Just because you were the second one to report this, doesn't mean we don't appreciate your letting us know. -- Mac :})
Re: how to retrieve data from postgresql database
On 01/28/2005 12:51 AM, sudheer raghav said: when am running this program with command perl dh.pl it is working fine.But whenever i run it on web browser it is not retrieving records from database. This is not a DBI issue. Go to http://search.cpan.org/~adesc/DBI-FAQ/FAQ.pm then search for 4.4 (sans quotes). Also read the page at the URL in my signature. -- Mac :}) ** I usually forward private questions to the appropriate mail list. ** Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age.
Re: DBD::Oracle
On 01/25/2005 01:29 AM, Waghmare, Satish (IE03x) said: Please help me to know how to I download the DBI packages from FTP site. I'm using ActiveState ActivePerl 5.8 on Win-XP system. Please see below Error log: C:\Documents and Settings\SatishWppm install http://ftp.esoftmatic.com/outgoing/DBI/5.8.3/DBI.ppd Error: No valid repositories: Error: 500 Can't connect to proxy:80 (Bad hostname 'proxy') Error: 500 Can't connect to proxy:80 (Bad hostname 'proxy') You have an invalid proxy defined (proxy:80). http://aspn.activestate.com/ASPN/docs/ActivePerl/faq/ActivePerl-faq2.html#ppm_and_proxies If you need to use a proxy server, set the environment variables to point to that server instead of proxy:80. If you are connected directly to the internet, remove the applicable environment variables from your system. -- Mac :}) ** I usually forward private questions to the appropriate mail list. ** Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age.
Re: how to connect postgres database
On 01/21/2005 06:59 AM, sudheer raghav said: am new to perl. how to connect postgresql database to perl. I replied to an identical message from you dated 01/17/2005 11:43 PM with: = Please look at http://www.catb.org/~esr/faqs/smart-questions.html . For DBI, http://search.cpan.org/~timb/DBI/DBI.pm http://search.cpan.org/~timb/DBI/ http://search.cpan.org/~timb/DBI/lib/DBI/FAQ.pm For DBD::Pg, http://search.cpan.org/author/RUDY/DBD-Pg/Pg.pm http://search.cpan.org/author/RUDY/DBD-Pg/ I am not an expert on DBD::Pg, so replying directly to me is useless. Reply to the list so someone else can help you. = I will ignore any further emails from you. The correct forum is dbi-users@perl.org . -- Mac :}) ** I usually forward private questions to the appropriate mail list. ** Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age.
Re: Truncating trailing spaces - looking for alternative solutions
Quoting Chad Terry [EMAIL PROTECTED]: Having the notorious problem where trailing spaces are being truncated from bind variables. I have found two solutions, one is to rtrim the column in the SQL statement, the other is to set the type to CHAR with bind_param. The problem is, I will be looking at changing up to 100 standalone scripts. I'm wondering if something else can be done in the environment or setup. This only started happening to us after we upgraded our environment to more current versions of Perl, DBD, and DBI. Can somone explain what it was in this upgrade that would start causing this to happen? We went from: Perl 5.00404 DBI .90 DBI.pm 1.83 DBD .47 DBD.pm 1.6 Oracle.pm 1.47 1997/09/08 We went to: Perl 5.6.0 DBI 1.32 DBI.pm 11.23 DBD 1.14 DBD.pm 11.11 Oracle.pm 1.1 2002/07/05 That's quite a jump. Is there a reason you didn't go to Perl 5.8 during your great leap forward? I forget exactly when, but somewhere between the two DBD::Oracle versions the default type for bind variables changed to ORA_VARCHAR2. That's why the spaces are getting stripped after you changed. Perhaps using ora_ph_type can make the change a little less painful. It will have to be set every time you open a handle that you want to avoid having trailing spaces removed in, but that's better than doing it for every placeholder. http://search.cpan.org/~timb/DBD-Oracle/Oracle.pm#Database_Handle_Attributes -- Mac :})
Re: 2 cursors? at the same time
On 01/19/2005 05:01 PM, Nina Markova said: I found solution myself reading information from the google group. I should autocomit off. I did it and works. Thank you for the followup with the solution you found. It may be useful to someone else later. -- Mac :}) ** I usually forward private questions to the appropriate mail list. ** Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age.
Re: how to connect postgresql database with perl
On 01/17/2005 11:43 PM, sudheer raghav said:
Please look at http://www.catb.org/~esr/faqs/smart-questions.html .
For DBI,
http://search.cpan.org/~timb/DBI/DBI.pm
http://search.cpan.org/~timb/DBI/
http://search.cpan.org/~timb/DBI/lib/DBI/FAQ.pm
For DBD::Pg,
http://search.cpan.org/author/RUDY/DBD-Pg/Pg.pm
http://search.cpan.org/author/RUDY/DBD-Pg/
I am not an expert on DBD::Pg, so replying directly to me is useless.
Reply to the list so someone else can help you.
am new to Perl.
How to connect postgresql database with perl ? here
is the Perl code follows:
use DBI;
use strict;
print Content-type: text/html\n\n;
use CGI::Carp qw/fatalsToBrowser/;
$query = new CGI;
...
$dbh = DBI-connect(dbi:Pg:dbname=test, postgres,
, {AutoCommit = 1, RaiseError = 1});
This looks reasonable, but you may be able to use for the username
(postgres) argument.
if($query-param(submit) eq save) {
$sth = $dbh-prepare(SELECT ip_add from firewall
where $source_add ='10.0.0.10');
I don't see you retreiving the actual IP address from any of the fields
you wrote to the request page.
} else {
print error;
}
if(!defined($sth)) {
print ERROR: Unable to execute database query:
$DBI::errstr\n;
exit;
}
Because of the {RaiseError = 1} in the connect() call, you will never
reach this point if $sth is not defined. Search for RaiseError,
prepare, and execute in the DBI manual.
$sth-execute;
$sth-finish;
...
print $query-popup_menu(-name='IP Adress',
Some software may not respond well to field names with spaces in them.
Adres should be Address at least for the text that is displayed.
-Values=['10.0.0.1','10.0.0.2','10.0.0.3','10.0.0.4',10.0.0.5,10.0.0.6],
print Source IP Adress:;
print $query-textfield('numeric');
print Destination IP Adress:;
print $query-textfield('Dest');
...
The aim of this code is to get input i.e; ip addresss
from postgresql database called test which has
firewall table and save the contents in text file.
What error are you seeing or what response you expected are you not seeing?
If this is failing to run in your webserver, try running it from the
command line. If that works, either the account running the web service
either doesn't have permission for the script or some of the Perl
modules, or some environment variables are missing in the webserver process.
--
Mac :})
** I usually forward private questions to the appropriate mail list. **
Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html
Give a hobbit a fish and he eats fish for a day.
Give a hobbit a ring and he eats fish for an age.
Re: DBD::Oracle
On 01/17/2005 05:02 AM, Waghmare, Satish (IE03x) said: Send me the PPM package for DBD::Oracle.Activestate 5.8 http://ftp.esoftmatic.com/DBI/ -- Mac :}) ** I usually forward private questions to the appropriate mail list. ** Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age.
Re: DBD Oracle DBI driver
On 01/17/2005 12:04 PM, Moreno, Javier said: Does anyone know of a repository where I can find DBD::Oracle? I have tried the ActiveState default repository but it reports it is not available. It would be worthwhile for you to read the responses sent to you before you ask the same question again. -- Mac :}) ** I usually forward private questions to the appropriate mail list. ** Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age. ---BeginMessage--- From: Moreno, Javier [EMAIL PROTECTED] Date: 2005/01/17 Mon AM 11:04:56 CST All, Does anyone know of a repository where I can find DBD::Oracle? I have tried the ActiveState default repository but it reports it is not available. You found the list, did you not find the archives? This was asked and answered just today... http://www.mail-archive.com/dbi-users@perl.org/msg23620.html Regards, Javier Moreno HTH, amonotod -- `\|||/ amonotod@| sun|perl|windows (@@) charter.net | sysadmin|dba ooO_(_)_Ooo _|_|_|_|_|_|_|_| ---End Message---
Re: Which ORACLE_HOME was used to build DBD?
On 01/12/2005 06:39 AM, [EMAIL PROTECTED] said: I have a Redhat box with 2 ORACLE_HOMEs. DBI and DBD_Oracle have both been installed. How can I tell which ORACLE_HOME was used to build the DBD? Call oraenv for each environment and try connecting to a database through each. If one fails, don't use it. If both work, you should be able to use both. -- Mac :}) ** I usually forward private questions to the appropriate mail list. ** Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age.
Re: Unable to obtain a TAR-able binary image of DBI
On 01/10/2005 04:32 PM, Jacob Salomon said: I have no trouble building perl 5.8.0 and the DBI 1.4.6 module on a development system with a compiler (and having just untarred into a virgin directory). I wish to create a TAR-able image so I can also install it on a box with no compiler. Following directions in the INSTALL file for perl itself, I included the option DESTDIR=... with the make install command. It performed as promised, creating a /usr/local.. hierarchy under the DESTDIR directory. Now I wished to do the same for DBI, similarly specifying a DESTDIR option on the make install command for dbi. In this case the DESTDIR remained empty. Building perl and building a module are slightly different operations. DESTDIR= should be on the `perl Makemaker.PL` line, not the `make install` line. http://search.cpan.org/~mschwern/ExtUtils-MakeMaker/lib/ExtUtils/MakeMaker.pm I have output of the first 3 steps as your README suggests. There is no need for make test VERBOSE=1 because the make test completed with no grief. I am, however, attaching the output of the make install to show what I was up to. Please be more specific what the problem is. It looks like DBI is being installed in the /usr/local/ hierarchy of the perl you intend to tar just like I would expect it to. DESTDIR isn't needed if that is adequate. If you want DBI to be somewhere else, try PREFIX=/dir/ or DESTDIR=/dir/ in the `perl Makefile.PL` line. -- Mac :}) ** I usually forward private questions to the appropriate mail list. ** Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age.
Re: Upgrading Perl and Solaris on a unix box
On 01/11/2005 07:26 AM, ruimvelds said: I have inherited a Unix box with solaris 5.7 and Perl 5.6.0 installed. It was created in 2001. I have had problems accessing an Oracle 9i database. One recomendation was to install the 9i client on the box, but the 9i client software only has installation modules for solaris 5.8 and 5.9. People are recomending we upgrade the machine from solaris 5.7 to 5.9. If this were to be done, does this mean I would have to re-install all perl modules? Probably, but it's a good time to ugrade everything in the Perl tree. I don't know any modules off hand that haven't changed since 2001. Also if we upgrade to solaris 5.9, would it be reasonable to upgrade to a later version of Perl and if so what version. The latest (5.8.6) unless someone has heard of any show-stoppers with it. This whole trouble seems to spark around having PErl 5.6.0 on a solaris 5.7 machine, but I am not sure what the best recomendation to the problem is as I am not a system administrator and I don't understand the issues involved. With so much that is so old, upgrading is almost a no brainer. The only thing that might stop you is if some of the old hardware is not supported by the newer version of the OS. -- Mac :}) ** I usually forward private questions to the appropriate mail list. ** Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age.
Re: Question on DBI connection to Oracle
On 01/09/2005 09:00 AM, Reidy, Ron said:
The error message says it all. You need to set (at a miniumu) ORACLE_HOME and
ORACLE_SID.
Read all the README* files in the DBD::Oracle distribution.
There's a reason that the standard install sequence for Perl modules
includes `make test`. That should have failed. If you installed a PPD
in ActivePerl, you miss that step.
-Original Message-
From: Zhou, Qing [mailto:[EMAIL PROTECTED]
Sent: Saturday, January 08, 2005 5:46 PM
To: 'dbi-users@perl.org'
Subject: Question on DBI connection to Oracle
First of all, thank you for taking the time to help me. I am a beginner to
DBI, and I am very frustrated with not being able to connect to Oracle from
my Sun Solaris box. I installed DBI and DBD modules, and tried to run the
following test script:
#!/usr/bin/perl
use CGI;
use DBI;
my $dbname = 'skidev01';
my $dbaddress = 'oraskidev01.bosptc.intuit.com';
my $username = 'dev2';
my $pass = 'X;
my $db = DBI:Oracle:$dbname:$dbaddress;
I don't know where you found this DSN syntax, I don't see it in the fine
manual.
http://search.cpan.org/dist/DBD-Oracle/Oracle.pm#CONNECTING_TO_ORACLE
my $dbh = DBI-connect($db, $username, $pass)
|| die Database connection failed: $DBI:errstr ;
$dbh-disconnect();
The following error occurred:
$ perl test.pl
ORACLE_HOME environment variable not set!
DBI connect('skidev01:oraskidev01.bosptc.intuit.com','dev2',...) failed:
Error while trying to retrieve text for error ORA-06401 (DBD ERROR:
OCIServerAttach) at test.pl line 13
Database connection failed: :errstr at test.pl line 13.
I don't have Oracle server or client installed on the box. Is that the cause
of the problem?
DBI is a thin interface between your program and DBD::Oracle (in this
case) and DBD::Oracle is a somewhat thicker interface to the local
Oracle client, so at least that must be installed on the same machine.
--
Mac :})
** I usually forward private questions to the appropriate mail list. **
Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html
Give a hobbit a fish and he eats fish for a day.
Give a hobbit a ring and he eats fish for an age.
Re: DBI for two database versions
On 01/05/2005 11:33 PM, Sam Vilain said: Thilo Planz wrote: The Oracle 10g client libraries used by your DBI should be able to connect to older versions of Oracle as well. So ideally, your 8.1.7 users can use the DBI you already installed. Careful, there's a compatibility matrix there. I vaguely remember the Oracle DBA here mentioning that the 10g libraries can't connect to Oracle 8. However, the version 8 libraries can definitely connect to 10g. One important thing to remember is that you need to set the environment for the Oracle version that DBD::Oracle was compiled for. That is, if DBD::Oracle was compiled with Oracle 8i, ORACLE_HOME and other environment variables need to point to an Oracle 8i installation. You can specify an Oracle 10g SID in the connect string as long as it is described in the tnsnames.ora of the Oracle 8i directories. -- Mac :}) ** I usually forward private questions to the appropriate mail list. ** Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age.
Re: Oracle-Perl Connection Problem
This discussion belongs on the DBI users list (dbi-users@perl.org), so
I'm sending this response there instead of to oracle-oci.
On Tuesday 04 January 2005 09:47, Sandeep Warikoo wrote:
I'm trying the access oracle through perl, but am getting the error
ERROR OCIEnvInit.
My perl script is:
-
use DBI;
$ENV{ORACLE_HOME}='/data/oracle/product/9.2.0';
my $dbh = DBI-connect(dbi:Oracle:d7_bilab03,hpbi_owner,hpbi)
or die couldn't connect to database:$DBI::errstr\n;
--
Does anyone know how to solve this?
You haven't told us your operating system and its version or your perl,
DBI, and DBD::Oracle versions.
Starting with DBI 1.38, you can use
perl -MDBI -e 'DBI-installed_versions'
to get that information (use instead of ' in MSWin). For earlier
versions use something like
perl -v
perl -MDBI=99
perl -MDBD::Oracle=99
to see the Perl, DBI, and DBD::Oracle versions.
On 01/04/2005 03:09 AM, Richard Foley said:
You usually need to have quite a few environment variables set before
you can access Oracle, as the error message (OCIEnvInit) roughly
indicates. Try reading the docs that came with the DBI (and
DBD::Oracle particularly), I believe there are many examples in
there.
You are at least short of
ORACLE_SID or TWO_TASK
$ORACLE_SID or $TWO_TASK are ignored if the SID (d7_bilab03) is given
explicitly. $ORACLE_HOME and other environment variables may need to be
set before starting your script. In *NIX running oraenv before starting
perl sets the needed environment variables.
Can you connect to that instance using SQL*Plus in the same account as
you are running the Perl script?
--
Mac :})
** I usually forward private questions to the appropriate mail list. **
Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html
Give a hobbit a fish and he eats fish for a day.
Give a hobbit a ring and he eats fish for an age.
Re: DBD-Oracle question for Win2000
On 01/04/2005 10:31 AM, Boris Volf said: I tried installing DBD-Oracle using ppm, and was unsuccessful. Then I downloaded this module from CPAN, and copied Oracle.pm into C:\Perl\lib, but I am still getting the same error., Naturally you are still getting the error, just copying Oracle.pm to some random directory is not sufficient, you need to Makefile.PL, make compile, make test, and make install in accordance with the instructions in README. Because most MSWin machines don't have the right compiler, ActiveState provides pre-compiled packages to install using PPM. Since those aren't always up to date, you might have more luck with packages from http://ftp.esoftmatic.com/DBI/, get both DBI and DBD::Oracle from there so the versions will be compatible. -- Mac :}) ** I usually forward private questions to the appropriate mail list. ** Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age.
Re: Can't install DBI
On 12/29/2004 08:05 AM, Bender Eliyahu-Yosef-BEB067 said: I installed ActivePerl- 5.8.6.811-MSWin32 opened MS-DOS window wrote ppm and then install DBI. The respond I got was : Error : No Valid repositories: Error : 500 Can't connect to ppm.Activestate.com:80 Bad hostname 'ppm.ActiveState.com' What should I do ? I just pinged that address, so there may have been a temporary DNS problem. Try again later, or go to http://ftp.esoftmatic.com/DBI/ instead. The most recent binaries there were built for Perl 5.8.4, but they should probably work for you. -- Mac :}) ** I usually forward private questions to the appropriate mail list. ** Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age.
Re: DBD::Oracle and m2o.pl
On 12/29/2004 10:56 AM, Maldonado, Daniel CW2 CTARNG said: Hi there again, this may not be the best forum to ask this question but I don't think there is a DBD::Oracle list available. This is the right place. The DBI list is a good first stop for all DBI and DBD user issues. Does anyone have a script that can convert a mysqldump file to a sqlplus script? That's not really a DBI issue, if you know the format of the file you can convert it to anything you want. You could also write it directly to Oracle using DBI and DBD::Oracle. Oreilly lists it at: http://www.oreillynet.com/oracle/os_dir/commandline.csp http://www.google.com/search?q=m2o.pl I think I found the source in an email at http://bincang.net/forum/archive/index.php/t-55513.html It looks like development ended with version 1.91 in 2001, but it should give you a start. If you have to re-write it much, you might want to generate sqlloader control and data files instead of SQL*Plus. -- Mac :}) ** I usually forward private questions to the appropriate mail list. ** Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age.
Re: Problems Installing DBD::MySQL
On 12/23/2004 07:55 PM, Tony McCray said: Thanks Dave, you helped a lot actually. It turns out that I have a file in /lib called libnss_files.so.2. I made a copy of the file and got rid of the '.2' extension. I had to do the same thing with libnss_dns.so. I haven't tested it yet, but make, make test, and make install all seemed to work fine. As Lincoln mentioned, you should create a soft link instead of a copy. With a copy, if the .2 file is updated without replacing the copy, you'll have two different versions which can cause a very hard to track down problem. Run the command below to create the link: ln -s /lib/libnns_files.so.2 /lib/libnns_files.so -- Mac :}) ** I usually forward private questions to the appropriate mail list. ** Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age.
Re: DBD Errors for New Oracle Install
On 12/23/2004 12:15 PM, Peter Barnett said: We have a new DBD install that is giving us trouble. The perl files are linked from a directory /obase/tools/perl/perl to /usr/bin/perl. The DBD is installed in the /obase/tools/perl/dbd directory. Not much to go on but the developers are very new to perl and I am not too far ahead of them. The URL in my sig may help you organize a better question so someone here can help you. -- Mac :}) ** I usually forward private questions to the appropriate mail list. ** Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age.
Re: perl module Oracle::OCI availability?
On 12/20/2004 11:57 AM, Teddy Choi said: Could you please let me know what the status is on the availability of Oracle::OCI ? It isn't in CPAN yet, and I haven't been able to google up a beta version. The version on CPAN is a bit old, or is that stable? http://search.cpan.org/dist/Oracle-OCI/ -- Mac :}) ** I usually forward private questions to the appropriate mail list. ** Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age.
Re: DBI like
On 12/18/2004 07:55 PM, xaver biton said:
how do I use like as bind variables, eg:
select * from users where user.tel like '49755%'.
could xou make an example?
Note: users ne user.
http://search.cpan.org/dist/DBI/DBI.pm#Placeholders_and_Bind_Values
http://search.cpan.org/dist/DBI/DBI.pm#bind_param
http://search.cpan.org/dist/DBI/DBI.pm#execute
http://search.cpan.org/dist/DBI/DBI.pm#bind_columns
$dbh-{RaiseError} = 1; # do this, or check every call for errors
$sth = $dbh-prepare( q{select * from users where users.tel like ?} );
$sth-execute( '49755%' );
# Bind Perl variables to columns:
my @col = @{$sth-{NAME_lc}};
$rv = $sth-bind_columns( \( @col ) );
# Column binding is the most efficient way to fetch data
while ( $sth-fetch ) {
print 49755%: . join( , ,
map { defined $_ ? '$_' : NULL } @col ) . \n;
}
--
Mac :})
** I usually forward private questions to the appropriate mail list. **
Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html
Give a hobbit a fish and he eats fish for a day.
Give a hobbit a ring and he eats fish for an age.
Re: dbi packages required
On 12/18/2004 12:23 AM, govind tarcar said: I am presently working on a project on CGi for my 7sem Cs course Am unable to trace a DBI and DBD-ODBC packages for perl. Tried some sources (ftp://ftp.esoftmatic.com/outgoing/DBI/DBI.ppd ) but couldnt get it Can u help me and send me the above mentioned packages. Sorry for disturbin u on such a petit probleme. neways i found the reqd packages but only for Perl ver 5.8.4 while i have Perl 5.8.5 installed on my m/c. Will the packages work(i.e. will Perl 5.8.5 support these packages). Could u help me out with any other details or nuances i have to worry abt. The PPDs offered at ftp://ftp.esoftmatic.com/ are only used by ActiveState's perl. The ultimate source for the source code of Perl modules is http://search.cpan.org/. You did not say what hardware, operating system, and perl distribution you are using. For anyone here to give meaningful answers, we would have to guess those things and that could result in giving you incorrect advice. Please read the site given in my signature to help you ask for help more effectively. -- Mac :}) ** I usually forward private questions to the appropriate mail list. ** Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age.
Re: Perl DBI Documentation Proposal: Using NULLs in Placeholders
Suggested changes {before : after}.
On 12/16/2004 09:33 AM, CAMPBELL, BRIAN D (BRIAN) said:
Undefined values, or Cundef, are used to indicate NULL values.
You can insert {: or} update columns with a NULL value as you would a
non-NULL value. These examples insert and update the column
Cage with a NULL value:
However, care must be taken {in the particular case of : when} trying to use
NULL values {to qualify : in} a CWHERE clause. Consider:
--
Mac :})
** I usually forward private questions to the appropriate mail list. **
Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html
Give a hobbit a fish and he eats fish for a day.
Give a hobbit a ring and he eats fish for an age.
Re: Nested query problem
On 12/13/2004 06:09 AM, Hardy Merrill said: I realize I'm splitting hairs here, and I'm no database expert, but I'm curious about your answer to this - wouldn't this be even slightly more efficient to write the WHERE clause conditions as most restricting first? In other words, SELECT feature.id FROM feature, reporter WHERE reporter.attributes_id = ? === most restrictive 1st AND feature.reporter_id = reporter.id === next most restrictive I was once told (or read?) that it is most efficient to put the most restrictive conditions first in the WHERE - is that right? I've always tended to put my joins towards the end of the WHERE when I have other criteria that I'm looking for - just curious to know if I've been doing it wrong. The general answer is that it all depends. A RDBMS builds its search plans based on a lot of factors; the order of the arguments may or may not be one of them. -- Mac :}) ** I usually forward private questions to the appropriate mail list. ** Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age.
Re: Cannot insert large files in MySQL from PERL
On 12/13/2004 09:33 AM, OIT said:
I am trying to save windump network trace files in MySQL. The following
piece works, and am able to insert into MySQL, but only a small piece of
this 50MB file is in there!!!
Is that a binary or text file? quote() is not likely to work correctly
on binary data.
Does anybody know what would be truncating my file?
open($fh, '12-6-2004---15-49-29' ) or die $!;
read( $fh, $var, -s $fh);
$sql = INSERT INTO traces values ( . ' . $begin_datetime . ' . ,
. ' . $end_datetime . ' . , . ? . , . ' . $device_name .
' . , . ' . $device_port . ' . ) . ; ;
$sql = INSERT INTO traces values ('$begin_datetime','$end_datetime', .
?,'$device_name','$device_port');;
It is not necessary to split the string up that much, all those dots
make my eyes cross and also make it hard to tell if the SQL is valid.
You should probably also pass the other values via placehloders.
$sth = $dbh-prepare($sql);
$file1=$dbh-quote($var);
$sth-bind_param( 1, $file1, SQL_LONGVARCHAR);
If you use a placeholder to pass the value, you shouldn't quote() it.
--
Mac :})
** I usually forward private questions to the appropriate mail list. **
Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html
Give a hobbit a fish and he eats fish for a day.
Give a hobbit a ring and he eats fish for an age.
Re: database query
Quoting dbsavvy2002-perl:
HERE IS THE CODE.CAN U PLEASE HELP ME WITH THIS. I
TRIED
%hasha= ();
@A=();
@A_F=();
TO UNINITIALIZE BUT STILL DONT WORK
#!/usr/bin/perl
use strict;
use Win32::ODBC;
my($db) = new Win32::ODBC('All Records1207');
my $keya;
my $ia=0;
my $ifa=0;
my @A;
my @A_F;
my $j;
my $RecordType;
my $fa;
sub loopa
{
my $s=$_[0];
$db-Sql(SELECT * FROM A where F101=$s);
$db-FetchRow();
my %hasha = $db-DataHash;
foreach $keya (sort(keys %hasha))
{
if ($hasha{$keya}=~m/[^\s]/)
{
$A[$ia]=$hasha{$keya};$A_F[$ia]=$keya;$ia++;
}
}
my $len_A=scalar @A;
$db-Close();
print @A\n @A_F \n length=$len_A;
%hasha= ();
@A=();
@A_F=();
}
loopa (4);
printhjgvjyg;
loopa (4);
Quoting dbsavvy2002-perl's earlier messsage:
I am trying to query an database through a for loop
(multiple query).As soon as it goes to second loop the
hash (i use for storing sql results) gives a
problem.it seems like hash need to be initialized or
to be killed . can someone please help me with that.
You haven't told us what you mean by 'gives a problem', so it is nearly
impossible for anyone to help you. Just calling a sub 'loopa' does not make it
a loop. I also don't see you saving anything in a hash, you have arrays @A and
@A_F that would save information between calls if they weren't being reset, but
the only hash I see (%hasha) appears to be being used to receive the results
from the first row.
Further, this program does not use DBI, so your question doesn't really belong
here. If you are using ActiveState Perl, you might ask on their lists. Visit
http://aspn.activestate.com/ASPN/Mail/ and see if either perl-win32-database or
perl-win32-users suits you.
No personal replies, please.
--
Mac :})
Re: database query
Quoting dbsavvy2002-perl:
...
#!/usr/bin/perl
use strict;
use Win32::ODBC;
my($db) = new Win32::ODBC('All Records1207');
...
sub loopa
{
...
$db-Close();
...
}
loopa (4);
printhjgvjyg;
loopa (4);
While re-reading my original response, I noticed you are closing the database
object near the bottom of loopa().
It is quite natural that the second call to loopa() fails if you close the
database object it depends on before you exit the first time. That has nothing
to do with any hash. This problem would probably have been noticed sooner by
someone here if you had given the exact error message instead of a vague
statement.
--
Mac :})
Re: DBI use failure with DBI/DBD::Oracle on AIX 5.2
On 11/26/2004 08:14 AM, Chris Holt said: Thanks , I needed some one to tell me it was definately permissions. It made me look harder at the perms on higher level directories. Now I'll look at my connection problem . Can't connect to data source exua, no database driver specified and DBI_DSN env var not set at ./ora.pl line 24 The example you sent earlier does specify a driver in dbi:Oracle:$dbname. You'll have to show what you are actually using for anyone to give a better hint. Please keep this on list. I am not the sole source of all wisdom. -- Mac :}) ** I usually forward private questions to the appropriate mail list. ** Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html Cthulhu in 2004. Don't settle for the lesser evil.
Re: DBI use failure with DBI/DBD::Oracle on AIX 5.2
On 11/25/2004 08:03 AM, Chris Holt said:
After being prepped by experiences of others in the README.aix
file. I beleive (see below), I have a good installation. But
on running my fist test prog I appear to have a permissions
problem. This litlle ditty does not fail when run as root,
but anyone else and it aborts on the 'use DBI;'.
That is certainly an indication that you don't have permission for
something. Especially since root succeeds.
$ cat ora.pl
#!/bin/perl -w
use DBI;
use strict;
my ($dbname, $user, $passwd) = ('exua', 'remote', 'remote');
#$dbh = DBI-connect(dbi:Oracle:$dbname, $user, $passwd);
#$dbh-do(SELECT * FROM DUAL);
$
$ ./ora.pl
Can't locate loadable object for module DBI in @INC
(@INC contains: /usr/opt/perl5/lib/5.8.0/aix-thread-multi
/usr/opt/perl5/lib/5.8.0
/usr/opt/perl5/lib/site_perl/5.8.0/aix-thread-multi
/usr/opt/perl5/lib/site_perl/5.8.0
/usr/opt/perl5/lib/site_perl .) at
/usr/opt/perl5/lib/site_perl/5.8.0/aix-thread-multi/DBI.pm line 254
BEGIN failed--compilation aborted at
/usr/opt/perl5/lib/site_perl/5.8.0/aix-thread-multi/DBI.pm line 254.
Compilation failed in require at ./ora.pl line 3.
BEGIN failed--compilation aborted at ./ora.pl line 3.
...
permissions on the directories and files under /usr/opt/perl5 appear
to be OK, at least world readable. Any ideas?
DBI.so is executable which was my first guess at the problem, make sure
the directories have executable permisson for you too.
...
364664 260 -r-xr-xr-x 1 root exua 264738 Nov 25 10:14
/usr/opt/perl5/lib/site_perl/5.8.0/aix-thread-multi/auto/DBI/DBI.so
--
Mac :})
** I usually forward private questions to the appropriate mail list. **
Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html
Cthulhu in 2004. Don't settle for the lesser evil.
Re: Oracle problem
On 11/24/2004 05:07 AM, Hardy Merrill said:
Tim, can you elaborate a little? I've tried to find the referenced
documentation but can't. I don't quite understand how a CHAR datatype
can cause this problem.
CHAR column values are padded with spaces to the length of the column.
When compared with a literal ('PAYMENT'), the SQL engine automatically
adds the padding to the literal. When compared with a placeholder, the
blank padded semantics are not available unless the placeholder type is
CHAR.
The reference Tim gave describes this, look for comparison semantics in
the Oracle SQL Reference for more information.
Tim Bunce [EMAIL PROTECTED] 11/24/04 07:35AM
On Tue, Nov 23, 2004 at 10:13:30PM +, Bart Kelsey wrote:
I'm having some trouble with DBD::Oracle...
When I execute this code:
***
$sth = $dbh-prepare(select * from abbrev where type = ?);
$sth-execute(PAYMENT);
while((@row) = $sth-fetchrow_array) {
print(join(, , @row), \n);
}
$sth-finish;
[Don't call finish at the end of fetch loops. See the docs.]
... no rows are returned. However, when I execute this code here:
$sth = $dbh-prepare(select * from abbrev where type =
'PAYMENT');
... it correctly returns a row. Does anyone know what the problem
might be
I'd guess the type column is a CHAR field.
Try:
use DBD::Oracle qw(:ora_types);
...
$dbh-{ora_ph_type} = ORA_CHAR
before the prepare(). See
http://search.cpan.org/~timb/DBD-Oracle-1.16/Oracle.pm#Database_Handle_Attributes
See also the String Comparison section in the Datatypes chapter of the
Oracle OCI manual for more details.
--
Mac :})
** I usually forward private questions to the appropriate mail list. **
Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html
Cthulhu in 2004. Don't settle for the lesser evil.
Re: Why wont my script finish?
On 11/24/2004 06:19 AM, Mark Martin said:
I have a very simple script to delete records from an Oracle table :
#!/usr/bin/perl
use DBI;
use DBD::Oracle;
$dbh = DBI-connect( dbi:Oracle:database, user, password) or die
Can't connect to Oracle database: $DBI::errstr\n;
$dbh-do( DELETE FROM TABLE WHERE YEAR=2003 );
exit;
the script never finishes and the records remain
If that is the actual SQL, TABLE is a reserved word so it would need to
be TABLE.
You have no idea what happened because you aren't checking for errors in
do(). The examples below show some ways (only one is needed), see
http://search.cpan.org/~timb/DBI-1.46/DBI.pm for more information;
search for errstr, RaiseError and PrintError.
$dbh = DBI-connect( dbi:Oracle:database, user, password,
{ RaiseError = 1 } )
or die Can't connect to Oracle database: $DBI::errstr\n;
$dbh - {RaiseError} = 1;
$dbh-do( qq(DELETE FROM TABLE WHERE YEAR=2003) )
or die Can't delete, $DBI::errstr\n;
--
Mac :})
** I usually forward private questions to the appropriate mail list. **
Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html
Cthulhu in 2004. Don't settle for the lesser evil.
Re: prepared statement: automatically removed trailing spaces
On 11/24/2004 04:55 AM, Steinhauer, Frank (CAM) said: ... What actually happens is that the default binding for DBD::Oracle is SQL_VARCHAR. When Oracle OCI sees that type, it automatically strips trailing spaces leaving '' if that's all there was. Then '' is interpreted as a NULL by Oracle. Binding as SQL_CHAR prevents the space stripping. ... See http://groups.google.com/groups?hl=enlr=threadm=E3A8A8F741B2D611ACA800508B 6F33D4381467%40chitmd03.nt.il.nbgfn.comrnum=2prev=/groups%3Fq%3Dbind_param %2Bspace%26hl%3Den%26lr%3D%26selm%3DE3A8A8F741B2D611ACA800508B6F33D4381467%2 540chitmd03.nt.il.nbgfn.com%26rnum%3D2 Why there is no hint about something important like that in the documentation of DBI and DBD-Oracle? I think that's a really important issue!!! It's been a basic problem with Oracle since forever that it treats '' as NULL. The automatic removal of trailing spaces is mentioned in `perldoc DBD::Oracle` or http://search.cpan.org/dist/DBD-Oracle/Oracle.pm. Search for ora_ph_type. = ora_ph_type The default placeholder data type for the database session. The TYPE or ora_type attributes to bind_param in DBI and bind_param_inout in DBI override the data type for individual placeholders. The most frequent reason for using this attribute is to permit trailing spaces in values passed by placeholders. = -- Mac :}) ** I usually forward private questions to the appropriate mail list. ** Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html Cthulhu in 2004. Don't settle for the lesser evil.
Re: compatibilty issue DBI 1.46 dbd-odbc 1.13
Quoting nishikant uppal [EMAIL PROTECTED]: can some one help me with this issue when iam trying to install dbd-odbc 1.13 using makefile.the error i am getting is C:\DBD-ODBC-1.13perl makefile.pl DBI object version 1.37 does not match $DBI::VERSION 1.46 at C:/Perl/lib/DynaLoa der.pm line 225. BEGIN failed--compilation aborted at C:/Perl/lib/DBI.pm line 254. Compilation failed in require at makefile.pl line 15. BEGIN failed--compilation aborted at makefile.pl line 15. There appears to be an old version of DBI.dll hanging around. Use Windows Explorer to search for all examples of DBI.* under c:\Perl and delete (or rename) any files not associated with the latest install of DBI. -- Mac :})
Re: Fetch the DBD driver type from a database handle?
Quoting Daniel Kasak [EMAIL PROTECTED]:
Michael A Chase wrote:
Quoting Daniel Kasak [EMAIL PROTECTED]:
I'm writing some code that needs to be able to figure out what DBD
driver is working with a current database *handle* ( so I can take
advantage of more advanced features of some drivers ).
How do I do that?
From DBI.pm:
Driver (handle)
Holds the handle of the parent driver. The only recommended use for this is
to
find the name of the driver using:
$dbh-{Driver}-{Name}
Aha!
Looking inside the source, eh?
Makes sense. I was looking in the docs.
Thanks :)
The documentation is in the source file. You can see it by running `perldoc
DBI` (without the quotes).
--
Mac :})
