Greetings,
For years, I've used the following construct for indexed, single-row
retrievals:
$sh = $dbh-prepare(select col from tbl where key = ?);
$sh-execute();
@ar = $sh-fetchrow_array();
if ($#ar -1) {...
I got bit today by a problem either in ADO.pm (2.96) or ADO itself.
Very small sample
On May 1, Bealach Na Bo scribed:
Hi folks,
Apologies if this has been asked and resolved - I've spent half a day
searching various archive, but have not found anything.
My problem is the following:
I have a very large postgresql table (ca. 3 mil records) and need to
replace a nested sql
DESCribe is a SQL*Plus command.
You can probably find what you are looking for by selecting from
sys.all_tab_columns. There may be a DBA view that does the same
thing, but I'm not up on them.
HTH,
Dave
On Apr 18, Mike Scott scribed:
I'm using DBI and DBD::Oracle.
I'd like to be able to
$ perldoc DBI
do
$rows = $dbh-do($statement) or die $dbh-errstr;
$rows = $dbh-do($statement, \%attr) or die $dbh-errstr;
$rows = $dbh-do($statement, \%attr, @bind_values) or die ...
Prepare and execute a single statement.
Does 'tnsping Mycad4prod' work from host(A)? I created a service
called test.xyz.com. 'tnsping test' fails (TNS-03505: Failed to resolve
name), but 'tnsping test.xyz.com' works. This doesn't sound like a DBI
issue at this point.
On Mar 23, Kairam, Raj scribed:
To those who could help me with
Thanks for the feedback everyone. I need to add that I tried another
test. In this one, I pulled out the insert and update statement from the
SP (just to confirm that what I wanted to do really would work) and the
result set was returned and the fetchrow_array call did succeed and
returned my
On Mar 2, Jenda Krynicky scribed:
Try to add
SET NOCOUNT ON
on top of the stored procedure. That should prevent the select into
and update statements from getting in the way.
Thanks! That's it. I forgot about that.
Dave
Hi all,
Since we seem to be on the subject of Stored Procedures, I thought
I'd throw my problem out there, since I can't figure out what I'm
doing wrong.
The SP builds a temp table, updates the source table from the temp
table and then returns a recordset with the contents of the temp
table.
not.
Thanks anyway.
Dave
On Mar 1, Moosmann, James scribed:
see if dropping the go works
GO is not going to be recognized by SQL Server ;-) , I use
@statements = split /\bgo\b/, $sql when I want to use a go in my sql
-Original Message-
From: David N Murray [mailto:[EMAIL PROTECTED]
Sent
What happens if you try the statement in sqlplus user/[EMAIL PROTECTED] This
doesn't look like a connectivity issue, except where user may not own
the table you are selecting from, i.e. try select ... from owner.table
if you are connecting as 'user' rather than 'owner'. Of course, 'user'
needs
DESCribe is a SQLPlus command (which Toad is emulating), not valid SQL.
There are other DBI functions to do the same thing that DESC does. See
table_info and column_info in the DBI docs.
HTH,
Dave
On Jan 25, [EMAIL PROTECTED] scribed:
Error Message is
[Tue Jan 25 04:53:23 2005] [error]
If you are trying something like
my $sql = select to_char(mf.ROW_MOD_DATE,'MM/DD/') Modify Date from
tbl mf;
then its perl that's balking. (List hint: actual code samples are
extremely helpful.) You need to escape the quotes within this string
(well, that's one way to do it, anyway):
my
Tony,
You seem to be missing libnss_files.so. I'm no familiar with SUSE, so I
can't tell you where to obtain it from, or if its even used. On my RedHat
box, it's in /lib. It implements the 'files' portion of the Name Service
Switch system (but I don't know what that is, either). On my box,
Pardon my jumping into the middle of this thread without reading the
previous, but this problem is well-documented (check google). With MySQL
4.1, you can't connect using DBI. The authentication method changed and
the DBD::MySQL module hasn't caught up yet. I went through this a couple
of weeks
How long does 'delete from table where year = 2003' take in SQLPlus?
On Nov 24, Mark Martin scribed:
Michael,
thanks for the quick response.
TABLE was just an example.
tried your error checking and nothing came up. So, went back to :
1. defining the SQL statement instead.
2. preparing
from DBI on varying subsets of data all the way
down to 1 record (the only delete that works))
At 10:57 24/11/2004 -0500, David N Murray wrote:
How long does 'delete from table where year = 2003' take in SQLPlus?
On Nov 24, Mark Martin scribed:
Michael,
thanks for the quick response
On Nov 24, Jeff Urlwin scribed:
I do what you originally did ($dbh-do(delete...) all the
time on Oracle 8.1.7. The only difference I have is
my $dbh = DBI-connect(dbi:Oracle:database, user, pass,
{ RaiseError = 1, AutoCommit = 0 });
i.e. I explicitly set
For version info print $DBI::Version and $DBD::Oracle::VERSION.
$ oerr ora 1555
01555, 0, snapshot too old: rollback segment number %s with name \%s\ too small
// *Cause: rollback records needed by a reader for consistent read are
// overwritten by other writers
// *Action: Use larger
Try this, it worked for me a couple of weeks ago:
my $dbh = DBI-connect(dbi:ADO:Provider=SQLOLEDB;...);
# do appropriate error checking
$dbh-{ado_commandtimeout} = 0; # turn off timeout
HTH,
Dave
On Sep 15, [EMAIL PROTECTED] scribed:
Hello,
Is there way to set timeout in DBD::ODBC?
I'm
Kirti,
When I've run into really wierd stuff like this with CPAN, I changed my
mirror. Its in wherever_perl_is_installed/CPAN/Config.pm. Change the
urllist to use another one. My primary mirror stopped being updated.
Switching to #2 solved my problems.
HTH,
Dave
On Sep 13, Kirti S. Bajwa
You missed all the dependencies of DBI. CPAN is your friend. Download and
install CPAN-1.76_01.tar.gz the same way you got and installed DBI (tar
xvfz, perl Makefile.PL, make, make test, make install). Then,
# perl -MCPAN -e shell;
You probably want to
cpan install Bundle::CPAN
next, and let it
If you don't know the columns that will be returned, I presume you're
doing something akin to 'select * from'. At any rate, if you know the
table name, why not ask the database for the table info?
#!/usr/bin/perl
use DBI;
my $dbh = DBI-connect(dbi:mysql:test,'','');
die Unable to connect:\n .
On Aug 19, Steffen Goeldner scribed:
David N Murray wrote:
Hi all,
I'm having a heck of a time querying a large table. What I'm trying to do
is:
[snip my sample code]
Does this
my $dbh = ...
$dbh-{ado_commandtimeout} = 0;
my $sth = ...
print $sth-{ado_comm
Hi all,
I'm having a heck of a time querying a large table. What I'm trying to do
is:
#!/usr/bin/perl -w
use DBI;
use strict;
$|++;
my $dbh = DBI-connect(dbi:ADO:Provider=SQLOLEDB;Integrated Security=SSPI;Data
Source=(local);Initial Catalog=db;CommandTimeout=900);
die Connect failed: .
You can't use a parameter for the table name. You have to use dynamic sql
(i.e. put it in the $sql var before the prepare). There's no point in the
prepare if the DB can't even figure out what table is going to be hit.
This should be a FAQ, but I didn't see it in there.
hth,
dave
On Jul 16,
google turns up http://search.cpan.org/search?module=DBD::ADO
ADO ODBC are your only options, AFAIK, on Windows.
Maybe someone more informed can comment on using the Sybase driver.
hth,
dave
On Jul 13, Janet Goldstein scribed:
Am I missing something? I searched CPAN for a DBI driver for
On Jul 13, Janet Goldstein scribed:
David N Murray wrote:
google turns up http://search.cpan.org/search?module=DBD::ADO
ADO ODBC are your only options, AFAIK, on Windows.
Maybe someone more informed can comment on using the Sybase driver.
Thanks for your reply, Dave. Actually, I'm
DESCRIBE is a SQLPLUS command, not a SQL statement. I'm looking at the
Oracle SQL Reference (8.1.7) and DESCRIBE isn't listed.
An alternative that works for me is
select * from sys.user_tab_columns
where table_name = table
order by column_id;
HTH,
Dave
On May 13, Jimmy Jauhal scribed:
Hi
$dateclosed = '' if !defined($dateclosed);
On Apr 26, Jesse, Rich scribed:
Howdy,
I have a question from one of our developers that's been bugging me and I
can't find an answer. Given this v1.37 DBI code fragement:
while ( my ($projectno,$description,$dateclosed) = $sth-fetchrow_array )
(I'm assuming Linux and the bash shell here, since you didn't specify.)
The problem is when you run './setup_vars' it runs in the context of
another process and environment changes don't go 'backwards'. What you
need to do is 'source' the file that contains the environment variables
using the
I think knowing which line its hanging up on would be benficial in
diagnosing this. In that regard, why not try the perl debugger. If
you're not familiar with the debugger, here's a quick rundown on the
commands you'll need:
$ perl -d script_name.pl and_any_args_for_script
DB1 b n*
n* is a line
I thought this worked, but when I test it now, I can't get it to work.
YMMV. -- Dave
#!/usr/bin/perl -w
# this does a 'describe' for MS access
use DBI;
use strict;
$|++;
#DBI-trace(8);
die usage: desc.pl DSN table_name\n if $#ARGV != 1;
my $dbh = DBI-connect(dbi:ODBC:$ARGV[0], '', '',
Apache (assuming you are using Apache, you didn't indicate) doesn't have
ORACLE_HOME set.
On Feb 21, ram kumar scribed:
This is the script:
#! /usr/bin/perl
# This should fix it:
$ENV{ORACLE_HOME} = '/opt/ora9/product/9.2';
# but there's probably a better way...
print Content-type:
On Jan 22, NIPP, SCOTT V (SBCSI) scribed:
OK. I am working on converting this to use placeholders and the
qq quoting option. I am obviously very new to placeholders, so this is
probably a stupid question, but here goes. I execute the script and receive
the following error:
[EMAIL
What's the error that's returned?
I looked at some code that I use for ODBC, and my format is
DBI-connect('dbi:ODBC:dsn','username','password'), not 'database=dsn'.
HTH,
Dave
On Jan 20, Herbold, John W. scribed:
I am trying to hit a MS SQL server using DBI:ODBC from an win2k box. I have
I got this same error and have seen others post with the same problem. I
d/l'd the whole oracle kit (dbd-oracle-1.14, from cpan) and built it on my
machine, and it works fine (DBI doesn't have any .dll dependencies that
I'm aware of). I have the full oracle 8.1.4 client installed, MSVC 6 SP3,
I'm kind of curious. If you aren't willing to use google to do the 30
seconds of searches needed to find ActiveState and ppm to install perl,
how are you going to write a program in perl using DBI?
Maybe you should start here:
http://www.catb.org/~esr/faqs/smart-questions.html
On Jan 9,
I got this same error and have seen others post with the same problem. I
d/l'd the whole oracle kit (dbd-oracle-1.14, from cpan) and built it on my
machine, and it works fine (DBI doesn't have any .dll dependencies that
I'm aware of). I have the full oracle 8.1.4 client installed, MSVC 6 SP3,
Sounds like a great test! Why don't you let us know which way is faster:
doing the substr's on the DB or in perl (my bet is perl). For that
matter, you could also test to see if join('|', @row) is faster. You
could certainly move the \n up to the first print line (via . or ,) and
cut out a
1) What's the error?
2) Does your sql statement work in mysql (interactive mode)?
3) What does WHERE 1 AND ... mean? IOW, what's the '1 AND' supposed to
be doing?
4) Start smaller when you are developing SQL statements that you feel are
complex:
- select * from memodata where readdate =
On Oct 6, David Kramer scribed:
Im new to using DBI, so this question is very simple Im sure. I have
searched the web and have read through O'Reilly's Programming the Perl DBI.
Im trying to connect to an Oracle 8.1.7 database running on Solaris 8. The
script itself resides on my app server
On Sep 26, Dan Anderson scribed:
I am trying to create a CGI perl script that uses DBI. I want to allow
it to use any database supported by DBI without having to code for
anything more then mySQL. Is this possible?
Will I need to include all supported DBI modules (or is it DBD?) with my
On Sep 9, Sharma, Amit scribed:
Hi,
While trying to execute the following SQL , I get the following error: Can't
call method execute on an undefined value at x.pl line 105.
..
my $sqlSch = SELECT SEQ_NO from $TableName;
The substitution of $TableName (if its populated) has already occurred
Is he using mysql? Or is it just installed. His answer looked like an
echo of your example. He never said he was using mysql, although he was
asked which db several times. Rudy, I admire your patience.
Dave
On Jun 30, Rudy Lippan scribed:
On Mon, 30 Jun 2003, [koi8-r] íÏÎÁÛ£× íÉÈÁÉÌ
I think I understand what's happening, I'm just struggling to put it into
words. What you are trying to do is use a placeholder for a value that
is required at 'prepare' time, rather than 'execute' time. Maybe someone
else can expound on the actual technical reasons for this. I have only
seen
Not a DBI question, but what platform?
Un*x:
perl script.pl logfile 21
Windows:
perl script.pl logfile 21
If you want errors to a different logfile, or output to come to the screen
with errors to a logfile,
perl script.pl 2error_log
HTH,
Dave
p.s. BTW, that's quite a sig you have.
On Jul
Remember, vote early and vote often. We'll have to work on ActiveState as
to why Tim's at the bottom of the ballot. He should be first
(alphabetically!) ;-)
BTW: http://www.activestate.com/Corporate/ActiveAwards
On Jun 12, [EMAIL PROTECTED] scribed:
...Has anybody mentioned that Tim is a
Some questions, that might help you answer yours:
What's the advantage of having a table per user, vs. a 'users' table with
one row per user?
How many rows do you envision in each user table, and what are their
columns?
If you are looking to create a per_user table that just contains 'key',
Which database? It usually varies.
On Jun 10, Anthony Nickolayev scribed:
Greetings. Maybe the answer for my question will be RTFM, but i still cant find
it myself. The problem is: i need to fetch just list, contains column names - and
i dont know how to do it. I didnt find it in DBI manual
Jun 2003 17:42:20 +
Received: from localhost (127.0.0.1) by nazgul.jsbsystems.com with SMTP; Mon, 09 Jun
2003 17:42:20 +
Date: Mon, 09 Jun 2003 13:42:20 -0400 (EDT)
From: David N Murray [EMAIL PROTECTED]
Subject: RE: Vague DBI Error
In-reply-to: [EMAIL PROTECTED]
X-X-Sender: [EMAIL
I haven't played with file uploads in awhile, but it seems to me that the
easiest way would be to upload the binary as a file, then read the file
and store it into the BLOB column, deleting the file when done. Are you
actually collecting the binary data off the form, as an INPUT item, or
will the
My initial reaction is to say RTFM, but since this is so urgent, that's
probably not an option. The error you seem to be reporting is a trivial
one and is covered quite well in the MySQL documentation regarding
security. If I were to be rude, I'd say 'use the correct password', or
'make sure you
Can post a sample of your connect statement? Searching Google Groups for
ORA-03106 yields something about not using BEQ. I've only ever done
TCP/IP connections, so the problem may be in your tnsnames.ora file. Can
you connect from sqlplus on the same machine?
Dave
On Apr 2, rbamidip scribed:
My silly, off-the-cuff guess is that you need DBD built using OCI8
libraries. Did you build DBD yourself, or download a package? Can you
build it yourself (perl Makefile.PL; make; make test; make install)? If
you are using Oracle 8, then you have the right libraries to do the build.
It looks
Of course, you can do it all with array references if your a glutton for
punishment. I can never remember how and always have to look it up with
working code.
my $rs = $dbh-selectall_arrayref(select * from table);
# count
print count: $#$rs\n;
# each row
my $rr;
foreach $rr (@$rs) {
print
Isn't it specified in your tnsnames.ora? I'm not familiar with a way to
specify a port to sqlplus. I thought it just pulled it from tnsnames.ora.
If you can connect using sqlplus as:
$ sqlplus scott/tiger@test
then I think
$dbh = DBI-connect('dbi:Oracle:test','scott','tiger');
should work. The
SQL select * from table device;
select * from table device
*
ERROR at line 1:
ORA-00906: missing left parenthesis
SQL
On Dec 17, Ford, Andy scribed:
I'm getting an error running DBD::Oracle:
DBD::Oracle::db do failed: ORA-00906: missing left parenthesis (DBD ERROR:
The argument for prepare is a SQL statement, not the name of a file.
Also, AFAIK, you can't prepare multiple statements at a time, only one at
a time (and including a ; may or may not work, depending on your platform;
I'm not DB2/DBI literate).
Have you gotten this to work?
$sth =
Hmmm, I don't know if I agree with the %$date is a reference sentiment:
$ cat t.pl
$d = 7-Aug;
print %$d%;
print \n;
$ perl t.pl
%7-Aug%
$
(I first did this with 'perl -e', bash needs too many backslashes to make
it readable.)
Now, if I try this, I find I can't get it to work:
SQL desc t1
Name
My experience with cron on Solaris is that your .profile is not run before
the script is executed. It looks like your environment may not be setup
properly (I'm assuming the script works when you run it interactively as
root).
HTH,
Dave
On Nov 14, [EMAIL PROTECTED] scribed:
Hello
I want to
Some good points in my experience with DBI, perl, Oracle, Apache:
1) Tim fixes things faster than my hold time on the phone with commercial
vendors :-) Seriously, security related fixes to OSS are RELEASED orders of
magnitude faster than commercial vendors.
2) Oracle and Solaris aren't open
Pardon my jumping in here, but I believe the answer to your question is
Yes.
On another track, is OS authentication an option for the DB you are using?
No password is required if OS authentication is used. The OS does the
authentication and the DB just asks the OS. Works pretty well for SQL
I don't think DBI is available in PPD format. You need to CPAN or do it
by hand (download the package and its dependencies, gzip -d, tar, perl
Makefile.pl, make, make tests, make install). I've had mixed success with
CPAN on Windows.
HTH,
Dave
On Sep 2, MURUGAN V V scribed:
Hi,
When I try
Any luck with RaiseError=0,PrintError=0? That seems to work for me.
On Aug 19, Levine, Peter W scribed:
Hi,
I'm using DBI in a cgi-bin script. I can't find a way to suppress the error
message DB-Library: Login incorrect if DBI-connect fails. This is an
issue because the raw output causes
Enterprise Manager has the ability to export the schema. You would then
have to convert it to whatever db you are going to implement.
On Jul 25, Brad Fike scribed:
I'm using MS SQL in my test environment but the production environment could
be just about anything
-Original
Why not just put it as a constraint on the table to supply a default value
of SYSDATE for the column? Then you don't have to deal with it from the
program side at all.
On Jun 14, Kipp, James scribed:
THANKS! I just found that out the hardway :)
Thanks all for your help. SYSDATE is the best
Put the oracle directory where libclntsh.so is located in the
LD_LIBRARY_PATH.
hth, Dave
On May 1, Eric Ngantchjon scribed:
How can I solve the problem below ?
Can't load
'/usr/local/lib/perl5/site_perl/5.6.1/sun4-solaris/auto/DBD/Oracle/Oracle..so'
for module DBD::Oracle: ld.so.1:
I think ORACLE_HOME may be what your looking for. Mine's set to
/opt/oracle/product/8.1.7.
HTH,
Dave
On Apr 24, Mahdi A. Sbeih scribed:
Hi all,
Is there a variable for Oracle like the one used before
installing DBD-Informix in case of relocatable INFORMIXDIR
or in oracle this is
Also, you may want to remove/rename the /usr/ucb/cc file and make sure gcc
is in your path. I ran into this when trying my first compile of any GNU
tools. Sun `thoughtfully' installs a `cc' command that says that C isn't
installed. Get rid of it if you are only using GCC.
Dave
On Apr 17,
If I remember my Perl history correctly, report formatting was one of
Larry's motivations for writing Perl. I'm not being facetious when
I say use Perl to format reports. Any decent Perl book (like the one
with the Camel on it, Chapter 2 in my copy) will show you how to write
reports. Use DBI
70 matches
Mail list logo
