Re: Mysql DBI Select Syntax ?
This is a method that I do now.
I am just thinking about that program need to connect the extra table
'diploma'.
If the Mysql have a internal function can rank the ENUM in table Personnel
that will be wonderful.
- Original Message -
From: "Steve Howard" <[EMAIL PROTECTED]>
To: "About-tw.com ??" <[EMAIL PROTECTED]>
Sent: Wednesday, July 04, 2001 7:44 AM
Subject: RE: Mysql DBI Select Syntax ?
Mornings are not a good time for me to answer questions...I have a little
more time this evening, and since SQL is really what I do, I think I can
give you an example of what I'm talking about and maybe you can do it in the
future. I am typing this directly into the e-mail here, so there may be a
syntax error somewhere, but I think I can get the idea across:
CREATE TABLE Diplomas (
diploma ENUM ("junior_high", "senior_high", "junior_college", "university",
"master", "doctor",
diploma_code int not null,
diploma_rank int not null)
CREATE TABLE Personnel (
FIRST_NAME varchar(20) not null,
MIDDLE_NAME varchar(20) null,
LAST_NAME varchar(20) not null,
SSN varchar(11) not null,
diploma_code int null)
INSERT INTO Diplomas (diploma, diploma_code, diploma_rank)
VALUES
("Junior High", 1,1)
INSERT INTO Diplomas (diploma, diploma_code, diploma_rank)
VALUES
("Senior High", 2,2)
INSERT INTO Diplomas (diploma, diploma_code, diploma_rank)
VALUES
("Junior College", 3,3)
INSERT INTO Diplomas (diploma, diploma_code, diploma_rank)
VALUES
("Master", 4,5)
INSERT INTO Diplomas (diploma, diploma_code, diploma_rank)
VALUES
("UNIVERSITY", 5,4)
INSERT INTO Diplomas (diploma, diploma_code, diploma_rank)
VALUES
("Doctorate", 6,6)
(At this point, you might want to select from your table to see what it
looks like. This is now a domain table giving valid diploma_codes for your
other tables. Here, you can go ahead and insert into the Personnel Table
several people with different diploma codes. Now, here's where you can now
do what you were saying, and I'll tell you when it comes to join syntax,
throw away the MySQL documentation, and what 95% of the MySQL people tell
you about how to do joins, and learn the ANSI join syntax. As you write
more, and more complex SQL you'll be very happy you have done this, and if
you ever need an outer join, you'll just be stuck if you try to do it using
the old SQL Syntax. I'll give you an example of old syntax, but first,
here's how to now do what you wanted to do using ANSI Join syntax);
SELECT l.FIRST_NAME,
case
when l.MIDDLE_NAME is null then ''
else l.MIDDLE_NAME
end as MIDDLE_NAME,
l.LAST_NAME,
case
when r.diploma is null then 'No Diploma'
else r.diploma
end as DIPLOMA
FROM Personnel l LEFT OUTER JOIN Diplomas r on l.diploma_code =
r.diploma_code
where r.diploma_rank > 3
ORDER BY r.diploma_rank
Now I used an outer join in this case even though it was not really
necessary. If you change your where clause to not restrict by diploma rank
using the outer join will return all the people, and if they have a diploma,
it will tell you which diploma they have. If you use an inner join, it will
only return people with diplomas.
Now, the syntax that you want to stay away from:
/*bad bad bad bad bad bad bad*/
SELECT l.FIRST_NAME,
case
when l.MIDDLE_NAME is null then ''
else l.MIDDLE_NAME
end as MIDDLE_NAME,
l.LAST_NAME,
case
when r.diploma is null then 'No Diploma'
else r.diploma
end as DIPLOMA
FROM Personnel l, Diplomas r
where r.diploma_rank > 3 and l.diploma_code = r.diploma_code
ORDER BY r.diploma_rank
/* end bad end bad end bad end bad end bac */
That is how 90 % of the MySQL people tell you to join a table, but I ask,
what if you want something besides a cross join, or an inner joinyou'd
better be very good at writing multiple nested subqueries...and they'll be a
nightmare to debug, and read, and they'll be very inefficient. It's so much
better to just learn the ANSI join syntax.
One step further, if you're not sure what the diploma_code is for a certain
diploma, you can use a subquery like this:
SELECT l.FIRST_NAME,
case
when l.MIDDLE_NAME is null then ''
else l.MIDDLE_NAME
end as MIDDLE_NAME,
l.LAST_NAME,
case
when r.diploma is null then 'No Diploma'
else r.diploma
end as DIPLOMA
FROM Personnel l LEFT OUTER JOIN Diplomas r on l.diploma_code =
r.diploma_code
where r.diploma_rank > (SELECT diploma_rank from Diplomas
where diploma = "Junior College")
ORDER BY r.diploma_rank
Anyway, that's how to use a domain table in your definitions, and use it to
accomplish what you were asking. It just takes a little fooling with to get
the SQL down like you want it to be. Hopefully, I've given you enough
examples you can get a good start on design and programming with it.
Enjoy,
Steve Howard
-Original Message-
From: About-tw.com ?? [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, July 03, 2001 9:19 AM
To: Steve Howard
Subject: Re: Mysql DBI Select Syntax ?
Anyway , Thanks a lot .
Sincerelly
Tom Wu
- Original Message -
From: "Steve Howard" <[EMAIL P
installation errors of "DBD-Oracle-1.07.tar.gz"
Hello all, please let me start by saying I'm fairly new to 'perl administration'. I am having difficulty adding the following module, "DBD-Oracle-1.07.tar.gz". I hope you can add some insights. Here is what I did: 1) downloaded, untared and unzipped "DBD-Oracle-1.07.tar.gz" 2) ran "perl Makefile.PL", which resulted in an error, stating: "The ORACLE_HOME environment variable must be set. It must be set to hold the path to an Oracle installation directory on this machine (or a machine with a compatible architecture). See the README.clients file for more information. ABORTED!" 3) I did not find any relevant information, or at least relevant to me, the unknowing, in the file, "README.clients". What am I missing? I'm sorry if this is too basic a question, but I'm not sure how to climb this installation hurdle. Any insights would be greatly appreciated. Thanks, -- Regards, Jason * * * * \\\|/// * * \\ - - // * * (/ @ @ \) * * -oOOo-(_)-oOOo * * * * Skepticism: * * * * Even a broken clock is * * accurate twice a day.* * * * * *
Re: DBD:Sybase Version Dependancies
Ed Lipson writes: > We are installing DBD:Sybase and SybPerl on Solaris. Is there any > requirement to rebuild the modules when we change Sybase versions > (from 11.9.2 to 12.0 to 12.5)? Is it just dependant upon the Open > Client library version and not the ASE versions? DBD::Sybase and sybperl depend on the version of OpenClient at build time, and some features may be enabled or disabled at run-time depending on the server you connect to. In general DBD::Sybase and sybperl will continue to work without recompilation when you upgrade the OpenClient libraries. However it's probably a good idea to rebuild the binaries anyway, especially with 12.5 as there are some new features that can only be enabled if the 12.5 libraries are present at build time. Michael -- Michael Peppler - Data Migrations Inc. - http://www.mbay.net/~mpeppler [EMAIL PROTECTED] - [EMAIL PROTECTED] International Sybase User Group - http://www.isug.com
DBD:Sybase Version Dependancies
We are installing DBD:Sybase and SybPerl on Solaris. Is there any requirement to rebuild the modules when we change Sybase versions (from 11.9.2 to 12.0 to 12.5)? Is it just dependant upon the Open Client library version and not the ASE versions? Thanks, Ed __ Get free email at http://nytoday.com/mail
Re: fat slow program DBI?
Well, I'm using the software versions packed with Red Hat 7.1, I supose are
near the lastest if not the lastest.
The perl proces takes a lot of memory, not much processor is used, but the
program answer very slow.
Answering Julio's questions:
* How big is each row?
- well, each row is 5 fields long: a date and four integer.
* What are the version numbers of all the software components youre
using(postgress, perl, DBI, ...)?
- The ones packed with RedHat 7.1. I have installed all these components
from the package.
* What else is running in your machine?
- DNS and X, but the amount of memory and processor used seems to be not
important.
* Have you checked whether there is a lot of paging/swapping in that
machine?
- well, about 700k swaping, not too much paging.
* How many concurrent clients are you running from this server?
- I have tested with about five clients, but the program is suposed to be
accessed by about 30 clients simoultaneously.
* Have you checked where is that most of the time is spent within your
code?
- looks like, 'cuz the perl process remains active in memory while the
browser has not displayed the full results.
Have not yet tested the map{} stuff, have to learn about it, but any hel
would be apreciated.
Thanks in advance,
Carlos López Linares.
> > I'm making a small system using perl and PostgresSQL, but have some
>problem when clients access tables with abou 2000 rows.
> > Well, I have a piece of code where I list all the data that comes from
>one table (the one with 2000 rows) and the perl process
> > becomes a fat (13Mgs in RAM) and slow (can take about 7 minutes) to show
>the results.
> >
> > the program is not complicated, it's as simple as:
> >
> > $query="select * from table";
> > $sth=$dbh->prepare($query);
> > $sth->execute();
> > while(@data=$sth->fetchrow_array){
> >print "$data[0]$data[1]";
> > }
> >
> >
> > I'm runing a RedHat 7.1 with a 2.4.2 SMP kernel on a dual Pentium III,
>256M RAM pc.
> > The clients are windows, but I have monitores the processes on a
>terminal and saw them fat, using quite some CPU but slow when
> > giving data to the clients. The networks is not charged, I have
>transmited a 3Mg file in less than a second.
>
>You might get better results -- especially with a list this small -- by
>sucking the
>results out in one pass:
>
>
> my $qry = $dbh->prepare( 'select * from blah' );
>
> print map { ... } $qry->fetchall_arrayref;
>
>I don't know enough about postgres internals, but you might get some
>improvement with something more like:
>
> my $qry = $dbh->prepare( $blah );
>
> sub handler
> {
> ...
> print map { ... } $qry->fetchall_arrayref;
> }
>
>i.e., prepare the thing once and just re-use it to get the data.
>
>If you want to see where it's really sucking up the 7 minutes you can use
>Benchmark to track the time and CPU use during each stage and see
>where the majority of time lies.
>
>sl
_
Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.
Re: db2 recursive query with placeholder
Hi Thomas, I wasn't able to reproduce your problem but I need more
information to duplicate your environment precisely. First however, you
might want to try upgrading your DB2 driver to the latest version (0.75).
If you still have the problem please send me the following:
DDL script to create and populate the table
DB2 version (run db2level)
CLI trace of the failure
DB2 trace of the failure
Dr. Watson log
Regards,
Robert
Please respond to Thomas Seeling <[EMAIL PROTECTED]>
To: dbi-users Mailinglist <[EMAIL PROTECTED]>
cc:
Subject: db2 recursive query with placeholder
Hello,
I have come upon a problem with traversing a "tree" in a
DB2 project called config database. The problem can be reduced to
the following script.
The script works with $sql1, where I do not use a placeholder,
and SEGVs with $sql2 with a placeholder for sup_ord_id.
Can anybody provide me with a hint?
#! usr/db/bin/perl5 -w
use strict 'vars';
use DBI;
my $dbh;
my $sql1=q{
WITH PROD ( product_id, sup_ord_id, product_class, product_type) AS
(
SELECT * FROM CO_PRODUCT WHERE sup_ord_id='TMRAEF'
UNION ALL
SELECT child.* FROM PROD parent, CO_PRODUCT child
WHERE parent.product_id = child.sup_ord_id
)
SELECT * FROM PROD WHERE product_class = 'TMR'
};
my $sql2=q{
WITH PROD ( product_id, sup_ord_id, product_class, product_type) AS
(
SELECT * FROM CO_PRODUCT WHERE sup_ord_id=?
UNION ALL
SELECT child.* FROM PROD parent, CO_PRODUCT child
WHERE parent.product_id = child.sup_ord_id
)
SELECT * FROM PROD WHERE product_class = 'TMR'
};
sub dumprows {
my $dbh=shift;
my $sql=shift;
my @parms=@_;
my $sth;
print "SQL: $sql\n";
$sth=$dbh->prepare($sql);
if ( scalar @parms>0 ) {
print STDERR "parms: ",join(' ',@parms),"\n";
$sth->execute(@parms);
}
else {
print STDERR "call with no parms\n";
$sth->execute;
}
while ( my $row = $sth->fetchrow_hashref ) {
foreach my $i (keys %{$row}) {
print ">$i = $row->{$i}\n";
}
}
}
$dbh=DBI->connect("dbi:DB2:CONFIG","user","pwd");
dumprows($dbh,$sql1);
dumprows($dbh,$sql2,$ARGV[0]||"TMRAEF");
$dbh->disconnect;
exit 0;
The table CO_PRODUCT is populated with this sample data:
COLUMNS: product_class, product_id, sup_ord_id, product_type
# MN-function, IND-name, dependency, whatsit
TMR, AEF, TMRAEF,PRODUCT
TMR, SENT36, TMRSENT, PRODUCT
TMR, NTMON36, SENT36,PRODUCT
TMR, TMEM36, SENT36,PRODUCT
TMR, OS2MON, SENT36,PRODUCT
I have to develop the DB2 version on NT4SP5 with:
This is perl, v5.6.0 built for MSWin32-x86-multi-thread
(with 1 registered patch, see perl -V for more detail)
Binary build 623 provided by ActiveState Tool Corp.
http://www.ActiveState.com
Built 16:27:07 Dec 15 2000
# perl5 -MDBI -e 'print $DBI::VERSION,"\n"'
1.14
# perl5 -MDBD::DB2 -e 'print $DBD::DB2::VERSION,"\n"'
0.74
--
Tschau...Thomas
"Do you wanna be a legend or a passing footprint on the sands of time?"
Senior Consultant, Tivoli Certified Enterprise Consultant + Instructor
santix AG,Max-Planck-Str. 7,D-85716 Unterschleissheim, Germany
+49-89-321506-0, Fax -99, [EMAIL PROTECTED], www.santix.de/~ths
Office Frankfurt/Main: Roentgenstr. 7, D-60388 Bergen-Enkheim, Germany
+49-6109-7329-30, Fax +49-6109-369375, Mobile +49-171-4416678
RE: Problem with Oracle Intermedia using DBI-DBD modules
I am trying to work with Oracle Intermedia and DBI. I have posted this problem to DBI
mailing list, unfortunately no one replied. I hope you can help me.
Here is the issue:
The Intermedia queries are little different from regular SQL statements.
Example of Intermedia Query:
[ select MY_TEXT_COL from MY_TABLE where (contains (MY_TEXT_COL,'FOO') > 0; ]
The DBI prepare statement doesn't return any error. But DBI execute returns this error
:
Can't execute Query ORA-00904: invalid column name (DBD ERROR: OCIStmtExecute)
The Intermedia queries work fine in sqlplus. I am wondering if DBI can handle
Intermedia queries?
I appreciate any help.
Thanks
-Bardia
-Original Message-
From: Madani, Bardia (B.) [mailto:[EMAIL PROTECTED]]
Sent: Friday, June 29, 2001 2:15 PM
To: '[EMAIL PROTECTED]'
Subject: RE: Problem with Oracle Intermedia using DBI-DBD modules
Have any one tried using Intermedia text with DBI modules?
Does any one know if DBI can handle Intermedia queries? How can I find out.
The trace file shows that the statement was successfully parsed with no errors, but we
get zero rows. But, the same query from sqlplus returns values.
I appreciate any help.
Bardia
-Original Message-
From: Madani, Bardia (B.) [mailto:[EMAIL PROTECTED]]
Sent: Thursday, June 28, 2001 10:42 AM
To: '[EMAIL PROTECTED]'
Cc: Madani, Bardia (B.)
Subject: Proble with Oracle Intermedia with DBI-DBD modules
I have problem using Oracle Intermedia queries with DBI modules. My code works with
regular queries (Example Below) and Intermedia works from sqlplus. But, from the
script with DBI I dont get any results when I convert my queries to Intermedia
syntax. Can DBI handle Intermedia syntax?
Here is sample Code for each:
Regular query: [ select i_id,description from items where upper(description) like
'%BOOK' ; ]
Sample Perl Code (WORKS):
my $term = "%BOOK%";
$query =qq{select i_id,description from items where (upper(description) like ?)
};
$dbh = DBI->connect($DATABASE, $USERNAME, $PASSWD, "Oracle")
or die "Can't connect to database $DATABASE: $DBI::errstr";
$q = $dbh->prepare($query) or die 'cannot prepare';
$q->execute($term);
while (($id,$description) = $q->fetchrow_array()){
print "$id => $description \n";
}
$q->finish;
$dbh->disconnect;
--
Intermedia query: select i_id, description from items where (contains
(description,'BOOKS') > 0);
Sample Perl Code (Does NOT WORK):
my $term = "BOOK";
my $query = qq{select i_id, description from items where (contains (description,?)
> 0 )};
$dbh = DBI->connect($DATABASE, $USERNAME, $PASSWD, "Oracle")
or die "Can't connect to database $DATABASE: $DBI::errstr";
$q = $dbh->prepare($query) or die 'cannot prepare';
$q->execute($term);
while (($id,$description) = $q->fetchrow_array()){
print "$id => $description \n";
}
$q->finish;
$dbh->disconnect;
I appreciate any Help.
-Bardia
Re: DBD::_::db::type_info() slow
> It sounded like he was calling it for nearly every value he was inserting > into his SQL. A standard location for caching the type_info information > would probably also help those DBDs that are simulating placeholders. Yes, indirectly. I am calling quote() for every value, and quote() was calling type_info() for every non-numeric value. -Dean
Re: Bind Parameters with MS Access
Thanks for the response and the request for code.
Original request: A lot is mailed on this list extolling the virtues of
binding parameters. Can this be done with an MS Access Database? I just get
an error.
Answer: Yes, binding parameters work with MS Access.
The real problem is that I'm trying to think of a way to speed up updating
an Access table from a csv file. I can't think of a way to use bind
parameters. The process I'm using is:
1) Read first line of file which is the column names
2) Read each subsequent line from file
3) If the row already exists in the table:
UPDATE table SET col1='value1',col2='value2' WHERE ID=id
else
INSERT INTO table(col1,col2) VALUES('value1','value2')
Because each UPDATE/INSERT sql is unique there is no way bind parameters
can be used. Unless anyone knows of a way ...
Ian
Re: DBD::_::db::type_info() slow
It sounded like he was calling it for nearly every value he was inserting
into his SQL. A standard location for caching the type_info information
would probably also help those DBDs that are simulating placeholders.
--
Mac :})
** I normally forward private questions to the appropriate mail list. **
Give a hobbit a fish and he eats fish for a day.
Give a hobbit a ring and he eats fish for an age.
- Original Message -
From: "Tim Bunce" <[EMAIL PROTECTED]>
To: "Dean Kopesky" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Tuesday, July 03, 2001 12:46
Subject: Re: DBD::_::db::type_info() slow
> How many times was type_info() being called?
>
> Tim.
>
> On Mon, Jul 02, 2001 at 01:19:10PM -0500, Dean Kopesky wrote:
> > Hi!
> >
> > While testing an application that generates SQL based on data extracted
> > from a database, I noticed that almost 80% of the runtime was being
> > consumed by DBI::db::quote(). A significant portion of that was in
> > DBD::_::db::type_info(). By caching the results of type_info_all(),
which
> > type_info() calls repeatedly, and by memoizing type_info() itself, I was
> > able to speed it up 5x or so.
> >
> > I have appended the diffs to this message. (I don't know if defining
new
> > db handle attributes willy-nilly is the best way to cache these values,
but
> > it's the best thing I could think of.)
> >
> > -Dean Kopesky / Bridge Information Systems / [EMAIL PROTECTED]
> >
> >
> > *** DBI.pm Mon Jun 4 14:01:39 2001
> > --- DBI.pm.2 Mon Jul 2 13:07:53 2001
> > ***
> > *** 1070,1079
> >
> > sub type_info {
> > my ($dbh, $data_type) = @_;
> > - my $tia = $dbh->type_info_all;
> > - return unless @$tia;
> > - my $idx_hash = shift @$tia;
> >
> > my $dt_idx = $idx_hash->{DATA_TYPE} || $idx_hash->{data_type};
> > Carp::croak("type_info_all returned non-standard DATA_TYPE index value
($dt_idx != 1)")
> > if $dt_idx && $dt_idx != 1;
> > --- 1070,1087
> >
> > sub type_info {
> > my ($dbh, $data_type) = @_;
> >
> > + my $ti_cache = $dbh->{'TypeInfoCache'};
> > + my $tia = $dbh->{'TypeInfoAllCache'};
> > + my $idx_hash = $dbh->{'IdxHashCache'};
> > +
> > + if ( ! $idx_hash ) {
> > + $dbh->{'TypeInfoCache'}= $ti_cache = {};
> > + $dbh->{'TypeInfoAllCache'} = $tia = $dbh->type_info_all;
> > + $dbh->{'IdxHashCache'} = $idx_hash = shift @$tia;
> > + }
> > + return unless $idx_hash;
> > +
> > my $dt_idx = $idx_hash->{DATA_TYPE} || $idx_hash->{data_type};
> > Carp::croak("type_info_all returned non-standard DATA_TYPE index value
($dt_idx != 1)")
> > if $dt_idx && $dt_idx != 1;
> > ***
> > *** 1082,1087
> > --- 1090,1099
> > my @ti;
> > my @data_type_list = (ref $data_type) ? @$data_type : ($data_type);
> > foreach $data_type (@data_type_list) {
> > + if ( exists $ti_cache->{$data_type} ) {
> > + return $ti_cache->{$data_type}->[0] unless wantarray;
> > + return @{$ti_cache->{$data_type}};
> > + }
> > if (defined($data_type) && $data_type != DBI::SQL_ALL_TYPES()) {
> > push @ti, grep { $_->[$dt_idx] == $data_type } @$tia;
> > }
> > ***
> > *** 1100,1105
> > --- 1112,1120
> > my @out = map {
> > my %h; @h{@idx_names} = @{$_}[ @idx_values ]; \%h;
> > } @ti;
> > +
> > + $ti_cache->{$data_type} = \@out;
> > +
> > return $out[0] unless wantarray;
> > return @out;
> > }
RE: getting return messages from non-selects in Informix
The perldoc I have does not refer to a sqlda structure, however it briefly talks about a sqlca structure... but I am unable to find the specific information I need. I'll have to track down the Informix manuals and look at this structure in there. Thanks, Curt On Tue, 3 Jul 2001, Wilson, Doug wrote: > Look at the perldoc for DBD::Informix, you have access to the sqlda > structure. > > Or the do() and execute() methods return the number of rows affected. >
RE: getting return messages from non-selects in Informix
That's kind of like the workaround I have in now, but it's not the solution I really want... I would like to display verbatim the return message from Informix since there will be some commands issued where looking at the return value for the number of rows updated will be insufficient. Thanks, Curt On Tue, 3 Jul 2001, Sterin, Ilya wrote: > Not sure about the attribute, but why not just use $DBI::errstr along with > the return value. If an error is return you can display the $DBI::errstr if > not, but no rows are returned/updated you can display the "No rows found" > message yourself. > > Ilya >
Re: DBD::_::db::type_info() slow
How many times was type_info() being called?
Tim.
On Mon, Jul 02, 2001 at 01:19:10PM -0500, Dean Kopesky wrote:
> Hi!
>
> While testing an application that generates SQL based on data extracted
> from a database, I noticed that almost 80% of the runtime was being
> consumed by DBI::db::quote(). A significant portion of that was in
> DBD::_::db::type_info(). By caching the results of type_info_all(), which
> type_info() calls repeatedly, and by memoizing type_info() itself, I was
> able to speed it up 5x or so.
>
> I have appended the diffs to this message. (I don't know if defining new
> db handle attributes willy-nilly is the best way to cache these values, but
> it's the best thing I could think of.)
>
> -Dean Kopesky / Bridge Information Systems / [EMAIL PROTECTED]
>
>
> *** DBI.pmMon Jun 4 14:01:39 2001
> --- DBI.pm.2 Mon Jul 2 13:07:53 2001
> ***
> *** 1070,1079
>
> sub type_info {
> my ($dbh, $data_type) = @_;
> - my $tia = $dbh->type_info_all;
> - return unless @$tia;
> - my $idx_hash = shift @$tia;
>
> my $dt_idx = $idx_hash->{DATA_TYPE} || $idx_hash->{data_type};
> Carp::croak("type_info_all returned non-standard DATA_TYPE index value
>($dt_idx != 1)")
> if $dt_idx && $dt_idx != 1;
> --- 1070,1087
>
> sub type_info {
> my ($dbh, $data_type) = @_;
>
> + my $ti_cache = $dbh->{'TypeInfoCache'};
> + my $tia = $dbh->{'TypeInfoAllCache'};
> + my $idx_hash = $dbh->{'IdxHashCache'};
> +
> + if ( ! $idx_hash ) {
> + $dbh->{'TypeInfoCache'}= $ti_cache = {};
> + $dbh->{'TypeInfoAllCache'} = $tia = $dbh->type_info_all;
> + $dbh->{'IdxHashCache'} = $idx_hash = shift @$tia;
> + }
> + return unless $idx_hash;
> +
> my $dt_idx = $idx_hash->{DATA_TYPE} || $idx_hash->{data_type};
> Carp::croak("type_info_all returned non-standard DATA_TYPE index value
>($dt_idx != 1)")
> if $dt_idx && $dt_idx != 1;
> ***
> *** 1082,1087
> --- 1090,1099
> my @ti;
> my @data_type_list = (ref $data_type) ? @$data_type : ($data_type);
> foreach $data_type (@data_type_list) {
> + if ( exists $ti_cache->{$data_type} ) {
> + return $ti_cache->{$data_type}->[0] unless wantarray;
> + return @{$ti_cache->{$data_type}};
> + }
> if (defined($data_type) && $data_type != DBI::SQL_ALL_TYPES()) {
> push @ti, grep { $_->[$dt_idx] == $data_type } @$tia;
> }
> ***
> *** 1100,1105
> --- 1112,1120
> my @out = map {
> my %h; @h{@idx_names} = @{$_}[ @idx_values ]; \%h;
> } @ti;
> +
> + $ti_cache->{$data_type} = \@out;
> +
> return $out[0] unless wantarray;
> return @out;
> }
Re: Calling OCIPasswordChange using Perl
Patches welcome. Or trymessing with the next version of Oracle::OCI. Tim. On Tue, Jun 19, 2001 at 03:24:47PM -0700, Molina, Gerardo wrote: > Has anyone figured out how to call OCIPasswordChange from Perl (with or > without DBI)? The reason I'm asking that Oracle is saying that > OCIPasswordChange function should be used instead of 'alter user..' to > accomplish password changes. I'm hoping someone has figured out how to do > this using Perl. > > I saw the following archive item where Tim Bunce talks about a possible > approach to incorporate this functionality into DBI but I don't know if > anyone ever implemented this or came up with an alternate solution. > > http://www.perl.jann.com/dbi-users/29/msg00157.html > > TIA, > Gerardo > >
Re: DBD::Oracle107 README, corrections
On Tue, Jun 19, 2001 at 01:24:36PM +0200, Axel Rose wrote: > Hello Tim, > > please delete from the DBD::Oralce 1.07 README the URL to > http://www.wmd.de/ > > I know for sure that the company "wmd" no longer exists. Thanks. > I have a minor compilation problem and would like to search > first in the mailing list archives. > At the moment I can't contact >www.arcana.co.uk >www.coe.missouri.edu/~faq/lists/dbiusers/ > Is this temporary only? No idea. Sorry. Tim.
RE: links
Not really a DBI issue, but You need to store the URL in the database and retrieve it in the select that also returns the name of the player. Your CGI will have to build the HTML around the name when your producing the list of results. -Original Message- From: Simon K. Chan [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 03, 2001 14:33 To: [EMAIL PROTECTED] Subject: links Hey Everybody, I have an online database where I use a CGI script that uses the DBI to return the values from Mysql. How do I return a link? For example, if I have a database for hockey players, and I return the name "Wayne Gretzky." Is there a way to make that a link that will go to say, NHL.com ?? Where would I insert the html tag http://www.nhl.com";>Wayne Gretzky ?? Many thanks. simon = # Simon K. Chan [EMAIL PROTECTED] "Great spirits have always encountered violent opposition from mediocre minds." -Albert Einstein __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail http://personal.mail.yahoo.com/
RE: Bind Parameters with MS Access
What error do you get and what is your code? Ilya -Original Message- From: Ian Summers To: [EMAIL PROTECTED] Sent: 07/03/2001 6:22 AM Subject: Bind Parameters with MS Access Hi A lot is mailed on this list extolling the virtues of binding parameters. Can this be done with an MS Access Database? I just get an error. Ian
RE: links
Not sure what you are talking about. Where do you store the link? Evidently your script is not a mind reader and doesn't know the link until you tell it where to get it from. There are too many ways to approach this, I won't even get into this. It's all pretty much common sense. Ilya -Original Message- From: Simon K. Chan To: [EMAIL PROTECTED] Sent: 07/03/2001 12:32 PM Subject: links Hey Everybody, I have an online database where I use a CGI script that uses the DBI to return the values from Mysql. How do I return a link? For example, if I have a database for hockey players, and I return the name "Wayne Gretzky." Is there a way to make that a link that will go to say, NHL.com ?? Where would I insert the html tag http://www.nhl.com";>Wayne Gretzky ?? Many thanks. simon = # Simon K. Chan [EMAIL PROTECTED] "Great spirits have always encountered violent opposition from mediocre minds." -Albert Einstein __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail http://personal.mail.yahoo.com/
Re: Bind Parameters with MS Access
Ian, Could you post a little source code please? Just to demonstrate your error. Mike --- Mike Lacey www.tek-tips.com -- a friendly, flame free, environment for computer professionals and students Perl forum at: http://www.tek-tips.com/gthreadminder.cfm/lev2/4/lev3/32/pid/219 - Original Message - From: "Ian Summers" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, July 03, 2001 1:22 PM Subject: Bind Parameters with MS Access > Hi > > A lot is mailed on this list extolling the virtues of binding parameters. > Can this be done with an MS Access Database? I just get an error. > > Ian >
Bind Parameters with MS Access
Hi A lot is mailed on this list extolling the virtues of binding parameters. Can this be done with an MS Access Database? I just get an error. Ian
RE: links
If your DB has the name, link stored in it then just select both columns and
then output them to your HTML formatted output. Small example:
$sth=$dbh->do("select player, link from hockeylist");
print qq!$player!;
-Original Message-
From: Simon K. Chan [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, July 03, 2001 1:33 PM
To: [EMAIL PROTECTED]
Subject: links
Hey Everybody,
I have an online database where I use a CGI script that uses the DBI to
return the values from
Mysql. How do I return a link?
For example, if I have a database for hockey players, and I return the name
"Wayne Gretzky." Is
there a way to make that a link that will go to say, NHL.com ??
Where would I insert the html tag http://www.nhl.com";>Wayne
Gretzky ??
Many thanks.
simon
=
#
Simon K. Chan
[EMAIL PROTECTED]
"Great spirits have always encountered violent opposition from mediocre
minds."
-Albert Einstein
__
Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail
http://personal.mail.yahoo.com/
links
Hey Everybody, I have an online database where I use a CGI script that uses the DBI to return the values from Mysql. How do I return a link? For example, if I have a database for hockey players, and I return the name "Wayne Gretzky." Is there a way to make that a link that will go to say, NHL.com ?? Where would I insert the html tag http://www.nhl.com";>Wayne Gretzky ?? Many thanks. simon = # Simon K. Chan [EMAIL PROTECTED] "Great spirits have always encountered violent opposition from mediocre minds." -Albert Einstein __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail http://personal.mail.yahoo.com/
RE: getting return messages from non-selects in Informix
Look at the perldoc for DBD::Informix, you have access to the sqlda structure. Or the do() and execute() methods return the number of rows affected. -Original Message- From: Curt Russell Crandall [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 03, 2001 9:15 AM To: [EMAIL PROTECTED] Subject: getting return messages from non-selects in Informix I have a FCGI script that lets you enter an sql statement in a textbox and on submit the statement is submitted to the database. If it is a select statement, a table with the results is displayed. If it is not a select statement, the program simply prints an error that fetch* didn't work. I'd like to make this program more intelligent by displaying the message returned by the database by successful update, delete, insert, etc. statements. I'm using Informix and I'm unable to find an attribute or function that allows me to do this. I believe in Sybase you can just do a fetch and grab a particular piece of the result set that would contain this data (don't have the perldoc for DBD::Sybase in front of me, so I don't know offhand what the syntax would be to do this). Is there's something equivalent I can use under Informix to get this information? Example update informix.vp_ppreq set ts = '2001-07-04 12:00:00' where prt_date = '2001-03-01' No rows found. -> this is what I want to get!!! Thank you, Curt Crandall
RE: getting return messages from non-selects in Informix
Not sure about the attribute, but why not just use $DBI::errstr along with the return value. If an error is return you can display the $DBI::errstr if not, but no rows are returned/updated you can display the "No rows found" message yourself. Ilya -Original Message- From: Curt Russell Crandall To: [EMAIL PROTECTED] Sent: 07/03/2001 10:14 AM Subject: getting return messages from non-selects in Informix I have a FCGI script that lets you enter an sql statement in a textbox and on submit the statement is submitted to the database. If it is a select statement, a table with the results is displayed. If it is not a select statement, the program simply prints an error that fetch* didn't work. I'd like to make this program more intelligent by displaying the message returned by the database by successful update, delete, insert, etc. statements. I'm using Informix and I'm unable to find an attribute or function that allows me to do this. I believe in Sybase you can just do a fetch and grab a particular piece of the result set that would contain this data (don't have the perldoc for DBD::Sybase in front of me, so I don't know offhand what the syntax would be to do this). Is there's something equivalent I can use under Informix to get this information? Example update informix.vp_ppreq set ts = '2001-07-04 12:00:00' where prt_date = '2001-03-01' No rows found. -> this is what I want to get!!! Thank you, Curt Crandall
getting return messages from non-selects in Informix
I have a FCGI script that lets you enter an sql statement in a textbox and on submit the statement is submitted to the database. If it is a select statement, a table with the results is displayed. If it is not a select statement, the program simply prints an error that fetch* didn't work. I'd like to make this program more intelligent by displaying the message returned by the database by successful update, delete, insert, etc. statements. I'm using Informix and I'm unable to find an attribute or function that allows me to do this. I believe in Sybase you can just do a fetch and grab a particular piece of the result set that would contain this data (don't have the perldoc for DBD::Sybase in front of me, so I don't know offhand what the syntax would be to do this). Is there's something equivalent I can use under Informix to get this information? Example update informix.vp_ppreq set ts = '2001-07-04 12:00:00' where prt_date = '2001-03-01' No rows found. -> this is what I want to get!!! Thank you, Curt Crandall
Re: The space of DBD::Oracle
That depends on too many factors to enumerate. Is there a reason you can't build them in an example of your target environment and see for yourself? -- Mac :}) ** I normally forward private questions to the appropriate mail list. ** Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age. - Original Message - From: "Wesley STROOP" <[EMAIL PROTECTED]> To: "dbi" <[EMAIL PROTECTED]> Sent: Tuesday, July 03, 2001 07:51 Subject: The space of DBD::Oracle > Can anyone tell me how large Perl 5.6.1 , DBI-1.16 and DBD-Oracle1.06 > is?
Re: Oracle, perl & DBI under debian
If you plan to run under WinNT, you should probably be using ActiveState Perl and their PPM to install DBI and DBD::Oracle. You will still need Oracle's SQL*Net client to actually connect to an Oracle database though and Oracle is the only source for that. -- Mac :}) ** I normally forward private questions to the appropriate mail list. ** Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age. - Original Message - From: "Vittorio" <[EMAIL PROTECTED]> To: "dbi-users" <[EMAIL PROTECTED]> Sent: Monday, July 02, 2001 16:53 Subject: Re: Oracle, perl & DBI under debian > Thanks Ilya but to put it simpler: > > Where can I find those Oracle client libs? > > Sterin, Ilya [02/07/01 09:34 -0600]: > > > > > > -Original Message- > > From: Vittorio > > To: [EMAIL PROTECTED] > > Sent: 07/02/2001 11:28 AM > > Subject: Re: Oracle, perl & DBI under debian > > > > Victor [02/07/01 17:01 +]: > > > Willing to use my laptop as a client of an oracle DB on an NT server > > > and network, to start with I've just begun to read the book by > > > Alligator Descartes and Tim Bunce "Programming the Perl DBI". > > > > > > Being at my very first steps on this field there's something somewhat > > > obscure to me and I wonder if someone could answer this easy question > > > of mine: > > > > > > To use perl and DBI:oracle do I need to install > > > > > > perl and the DBI stuff for oracle from the DBI site only > > > > > > If you read the book, it actually explains this pretty good IMO. You need > > perl (of course :-) as well as DBI and DBD::Oracle from www.cpan.org > > > > > > > > > > OR > > > > > > perl, the DBI stuff for oracle and A SERVER (PROPRIETARY) CLIENT FOR > > > ORACLE? > > > > You must have the Oracle client libs installed before you can compile > > DBD::Oracle.
The space of DBD::Oracle
Hi all, Can anyone tell me how large Perl 5.6.1 , DBI-1.16 and DBD-Oracle1.06 is? Thanks, Wez
Re: Help with InactiveDestroy attribute
On Tue, 3 Jul 2001 10:15:26 -0400
Jeff Boes <[EMAIL PROTECTED]> wrote:
> The following is my test case:
Oopsie. There's a chunk of bad code in there, because I was writing this against my
database abstraction layer and then took that out to test against DBI directly. I've
corrected the code below, but it performs the same way.
use strict;
use DBI;
my $db = DBI->connect('dbi:Pg:dbname=nexcerpt','','', { PrintError => 0 });
print 'main: ', @{ $db->selectrow_arrayref(q{select count(*) from pg_database} ) },
"\n";
$db->{InactiveDestroy} = 0;
exit if fork();
print 'child: ', @{ $db->selectrow_arrayref(q{select count(*) from pg_database}) },
"\n";
--
Jeff Boes vox 616.226.9550
Database Engineer fax 616.349.9076
Nexcerpt, Inc. [EMAIL PROTECTED]
Re: Mysql DBI Select Syntax ?
=?utf-8?B?QWJvdXQtdHcuY29tIOWFjeiyu+S6uuWKm+mKgOihjA==? writes: > Dear All, As I understand this, what you would really LIKE to do would be to sort/collate by the underlying numeric representation, rather than by the abstraction of the values provided through ENUM. Some rather sophisticated work-arounds have been suggested to layer MORE abstraction on top of this to get your desired result, but I would like to offer, for your consideration, a step backwards to a simpler implementation. Create another table that maps diploma codes to diploma names. 1 "junior_high" 2 "senior_high" 3 "junior_college" (in some places, "associate") 4 "university" 5 "master" 6 "doctor" You might even choose to go by tens to allow near-aliases or finer shades of meaning to be added in the future without having to re-org the dependent tables. Then, use table joins (or views) where you want the names, and use the "raw" numeric field when you want to sort by the representation value. If you want to get at the underlying representation, ENUM is the wrong thing to use -- it is built to hide that representation. If you need access to that representation, do so explicitly. SELECT from * table1,table2 WHERE table2.dc_key = table1.diploma_code AND table1.diploma_code = 2 and so forth. This is what joins and views do pretty well. It will also allow you to do what you indicated you wanted to do in later messages, which would be to SORT by the diploma_code, but still display the value. Furthermore, you are free to use regular text, without underscores or other restrictions, in the diploma names. I hope that this approach may offer or suggest something of value. -- John Lind [EMAIL PROTECTED]
RE: Mysql DBI Select Syntax ?
In this case, I really think the question is one of database design. In the domain
table you are creating to enumerate the types of degrees (a2), I would add a "rank"
column so I could evaluate on the rank column, and join back to this domain table in
queries where this evaluation needs to be made.
Once you have a numeric rank, see if you can then make your evaluation. As it is, the
rank is being determined by alphabetic order.
Steve H.
-Original Message-
From: About-tw.com ?? [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, July 03, 2001 4:40 AM
To: Steve Howard; [EMAIL PROTECTED]
Subject: Re: Mysql DBI Select Syntax ?
Dear Steven,
Thanks for your opinion.
Here is my complete example ,
my $dbh = DBI->connect("DBI:mysql:x",,, {RaiseError => 1 });
my $sth = $dbh->prepare( qq{
CREATE TABLE a1 (diploma
ENUM("junior_high","senior_high","junior_college","university","master","doc
tor")
});
$sth->execute;
$dbh->disconnect;
my $dbh = DBI->connect("DBI:mysql:x",,, {RaiseError => 1 });
my $sth = $dbh->prepare( qq{
CREATE TABLE a2 (diploma
ENUM("junior_high","senior_high","junior_college","university","master","doc
tor")
});
$sth->execute;
$dbh->disconnect;
my $dbh = DBI->connect("DBI:mysql:x",,, { RaiseError => 1 });
my $sth =
repare( qq{
SELECT a1.diploma a2.diploma
FROM a1,a2
WHERE a1.diploma <= a2.diploma
});
$sth->execute;
$dbh->disconnect;
I'd like to compare a1.diploma and a2.diploma, and my ideal rule is doctor > master
> university > junior_college > .
But the result is university > senior_high > junior_high > junior_college > master >
doctor
Is there any method let me get my ideal rule is doctor > master > university >
junior_college > senior_high > junior_high
Thanks in advance.
Sincerelly
Tom Wu
- Original Message -
From: "Steve Howard" <[EMAIL PROTECTED]>
To: "About-tw.com ??" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Tuesday, July 03, 2001 10:59 AM
Subject: RE: Mysql DBI Select Syntax ?
I'm not completely sure I know what you're asking. If you're wanting to put a
numerical equivalent to the possible string values, in MySQL you can use a CASE
statement, like this:
SELECT case
WHEN diploma = 'junior_high' THEN 1
WHEN diploma = 'senior_high' THEN 2
WHEN diploma = 'junior_college' THEN 3
WHEN diploma = 'university' THEN 4
WHEN diploma = 'master' THEN 5
ELSE 6
END
AS DIPLOMA
FROM Tablename
You can embed some version of that to get a numerical return from a table enumerated
as you have said, however, it
still shouldn't return as you have put in your WHERE clause. You would still
have to use:
WHERE diploma = 'senior_high'
If you only wanted Senior high grads.
Is this what you are asking?
Steve Howard
-Original Message-
From: About-tw.com ?? [mailto:[EMAIL PROTECTED]]
Sent: Monday, July 02, 2001 3:16 PM
To: [EMAIL PROTECTED]
Subject: Mysql DBI Select Syntax ?
Dear All,
my $dbh = DBI->connect("DBI:mysql:x",,, {RaiseError => 1 });
my $sth = $dbh->prepare( qq{
CREATE TABLE $table_name (
diploma
ENUM("junior_high","senior_high","junior_college","university","master","doc
tor")
});
When I do the following procedure
my $dbh = DBI->connect("DBI:mysql:x",,, { RaiseError => 1 });
my $sth = $dbh->prepare( qq{SELECT *FROM $table_nameWHERE
diploma = 2});
$sth->execute;
my $diploma = $sth -> fetchrow_array ;
$dbh->disconnect;
I can get $diploma = "senior_high"
Now here is my problem , How could I get the value of the $diploma = 2 ?
I'll really appreciated if someone can help me.
RE: SQL query
What is the difference between the two records that cause the multiple lines to be returned? Is it a date, or something else? If you are looking for something to guarantee only the latest row is returned, you can use a SELECT...INTO and select into a temp table grouped by the key, and using a MAX() then join to that, (Usually more efficient, but not always) or you can use a subquery in your SQL statement to limit to only that one. Examples (assuming a column named ThisDate as the difference) Select fault_no, MAX(ThisDate) as ThisDate INTO #temp FROM report_response GROUP BY fault_no SELECT r.fault_no ,r.one_line_summary FROM report_response r JOIN #temp t on r.fault_no = t.fault_no and r.ThisDate = t.ThisDate WHERE (r.reported_by LIKE '%J BLOGGS%' ) order by fault_no DESC Example of the second one I put forward: SELECT r.fault_no ,r.one_line_summary FROM report_response r WHERE (r reported_by LIKE '%J BLOGGS%' ) and r.ThisDate = (SELECT MAX(ThisDate) WHERE fault_no = r.fault_no) order by fault_no DESC however, for either of them to work, you need a way to distinguish between the row you want, and the row(s) you don't. Enjoy, Steve H. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 03, 2001 3:31 AM To: DBI Users Subject: SQL query Hi all, I have a table (report_response) which has (among others) fault_no and response_no fields. Now a fault_no can have multiple response_no's. The thing is, when I issue the following SQL: SELECT fault_no ,one_line_summary FROM report_response WHERE (reported_by LIKE '%J BLOGGS%' ) order by fault_no DESC my returned list displays: 1355 Glish leftovers on sagitta 1350 Site phones 1350 Site phones See those multiple occurances of 1350? This means there are 2 responses to the fault_no = 1350. How can I fudge the SQL to select the last response_no submitted? Regards, Stacy. BTW: Using DBI:1.14 with Oracle 7.3.3
RE: Problems Running from crontab.
How would this differ from when I set them in the code?
I do have $ENV{ORACLE_HOME}, $ENV{ORACLE_SID}, $ENV{LD_LIBRARY_PATH}
set in any code that runs from cron.
On 02-Jul-01 Steve Sapovits wrote:
>
> You can do this nice little Perl trick:
>
> BEGIN
> {
>if ($ENV{LD_LIBRARY_PATH} !~ /oracle.*lib/)
>{
> $ENV{LD_LIBRARY_PATH} = '/oracle/8.0.5/lib';
> exec($^X, $0, @ARGV);
>}
> }
>
> or something similar (e.g., we use a package that appends
> to LD_LIBRARY_PATH if the value isn't already in it).
>
> The exec piece executes your Perl script exactly as you did,
> using the same Perl interpreter.
>
>
> Steve Sapovits
> Global Sports Interactive
> Work Email: [EMAIL PROTECTED]
> Home Email: [EMAIL PROTECTED]
> Work Phone: 610-491-7087
> Cell: 610-574-7706
> Pager: 877-239-4003
>
>> -Original Message-
>> From:Scott T. Hildreth [SMTP:[EMAIL PROTECTED]]
>> Sent:Monday, July 02, 2001 2:44 PM
>> To: Scott T. Hildreth
>> Cc: [EMAIL PROTECTED]
>> Subject: RE: Problems Running from crontab.
>>
>>
>> I fixed it by setting the 'SHELL=/usr/local/bin/zsh' so
>> the LD_LIBRARY_PATH is getting set before Perl is executed.
>> I'm not sure why the behavior changed, but it works :-)
>>
>> On 02-Jul-01 Scott T. Hildreth wrote:
>> >
>> > I wonder if anyone has run into this problem. I upgrade on of our
>> > production servers to perl5.6.1, DBI-1.18, and using DBD::Oracle 1.06.
>> > I was using DBD::Oracle 1.07, but I went back to 1.06 to see if that
>> > is the problem. Anyway I have jobs that run in cron, in the past if
>> > I set the %ENV Vars, everything ran okay. since I update the Perl and
>> > DBI the following error occurs,
>> >
>> > install_driver(Oracle) failed: Can't load
>> >
>> '/usr/local/lib/perl5/site_perl/5.6.1/i686-linux/auto/DBD/Oracle/Oracle.so
>> '
>> > for
>> > module DBD::Oracle: libclntsh.so.1.0: cannot open shared object file: No
>> such
>> > file or directory at /usr/local/lib/perl5/5.6.1/i686-linux/DynaLoader.pm
>> line
>> > 206.
>> >
>> > ..It will run from command line, because it is getting the env from the
>> > shell,
>> > but not cron.
>> >
>> > The %ENV vars are set as,
>> >
>> > $ENV{ORACLE_SID} = $ARGV[0];
>> > $ENV{ORACLE_HOME} = '/oracle/8.0.5';
>> > $ENV{LD_LIBRARY_PATH} = '/oracle/8.0.5/lib';
>> >
>> > which worked until the updates. Does anyone know if this a problem with
>>
>> > Perl5.6.1 or DBI-1.18?
>> >
>> > Thanks,
>> > STH
>> >
>> > --
>> > E-Mail: Scott T. Hildreth <[EMAIL PROTECTED]>
>> > Date: 02-Jul-01
>> > Time: 12:14:12
>> > --
>>
>> --
>> E-Mail: Scott T. Hildreth <[EMAIL PROTECTED]>
>> Date: 02-Jul-01
>> Time: 13:40:59
>> --
--
E-Mail: Scott T. Hildreth <[EMAIL PROTECTED]>
Date: 03-Jul-01
Time: 06:30:13
--
Re: Problems Running from crontab.
I just changed the shell because our .zshenv has the enviorment variables setup. On 03-Jul-01 Alexander Farber (EED) wrote: > "Scott T. Hildreth" wrote: >> >> I fixed it by setting the 'SHELL=/usr/local/bin/zsh' so >> the LD_LIBRARY_PATH is getting set before Perl is executed. > > Why not just set the LD_LIBRARY_PATH and then execute > the Perl-script instead of changing sh to zsh? > >> I'm not sure why the behavior changed, but it works :-) > > ;-) -- E-Mail: Scott T. Hildreth <[EMAIL PROTECTED]> Date: 03-Jul-01 Time: 06:24:08 --
Re: SQL query
Hi Simon, Yes, I have a date/time field in the format "DD-MON- HH24:MI" Peter Bruhn suggested I try: SELECT DISTINCT fault_no ,one_line_summary FROM report_response WHERE (reported_by LIKE '%J BLOGGS%' ) order by fault_no DESC; This appears to work, but I'll look into views as well... Many thanks... Regards, Stacy. Simon Oliver wrote: > Does your table have a "submitted_date" field or some other serialised > or chronological field? If not your stuffed because RDBMs do not > guarantee the order records are stored in. > > If you do have a "submitted_date" field then you can create a view that > is grouped by max(submitted_date) and select against this query. > > Stacy Mader wrote: > > > Hi all, > > > > I have a table (report_response) which has (among others) > > fault_no and response_no fields. Now a fault_no can have > > multiple response_no's. > > > > The thing is, when I issue the following SQL: > > > > SELECT fault_no ,one_line_summary FROM report_response > > WHERE (reported_by LIKE '%J BLOGGS%' ) order by fault_no DESC > > > > my returned list displays: > > > > 1355 Glish leftovers on sagitta > > 1350 Site phones > > 1350 Site phones > > > > See those multiple occurances of 1350? This means there are 2 responses > > to the fault_no = 1350. How can I fudge the SQL > > to select the last response_no submitted? > > > > Regards, > > > > Stacy. > > > > BTW: Using DBI:1.14 with Oracle 7.3.3 > > -- > Simon Oliver > > ---
Re: SQL query
Does your table have a "submitted_date" field or some other serialised or chronological field? If not your stuffed because RDBMs do not guarantee the order records are stored in. If you do have a "submitted_date" field then you can create a view that is grouped by max(submitted_date) and select against this query. Stacy Mader wrote: > Hi all, > > I have a table (report_response) which has (among others) > fault_no and response_no fields. Now a fault_no can have > multiple response_no's. > > The thing is, when I issue the following SQL: > > SELECT fault_no ,one_line_summary FROM report_response > WHERE (reported_by LIKE '%J BLOGGS%' ) order by fault_no DESC > > my returned list displays: > > 1355 Glish leftovers on sagitta > 1350 Site phones > 1350 Site phones > > See those multiple occurances of 1350? This means there are 2 responses > to the fault_no = 1350. How can I fudge the SQL > to select the last response_no submitted? > > Regards, > > Stacy. > > BTW: Using DBI:1.14 with Oracle 7.3.3 -- Simon Oliver ---
MS Access 2K Query problem and trace file
Hi all,
I can't get a simple query to work in DBI 1.14 / DBD
ADO 1.17 / MDAC 2.6 SP1. Records with specific test
values can be manually added into the Access table no
problem. Deleting these test records and then trying to
have Perl / DBI insert the same records results in a
syntax error and the trace below.
This is such a simple query it has me stumped for the
'Syntax Error' returns. I have several other more
complex INSERTs to other tables working without
complaint. Deleting existing relationships, primary key
(anchor + table unique) and other columns in the table
made no difference.
Thanks in advance.
-Jim
# Schema - doesn't matter if Anchor / table primary
keys or not
Anchor Text(255) RequiredZero Length
(No) Indexed (Dups OK)
Table Text(255) RequiredZero Length
(No) Indexed (Dups OK)
and a query of:
# Slightly modified Cheetah book example on page 99
sub doPrepare {
# Initialize
local $anchor = shift;
local $table = shift;
# Sometimes we want to add a record, use
'import=new' to do so
$sth = $dbh->prepare("
INSERT INTO htmltables (Anchor, Table)
VALUES ($anchor, $table)
"); # or die "Can't prepare statement:
$dbh->errstr\n";
# Check if statement prepared correctly
#die "ERROR: Cannot prepare statement:
$DBI::errstr\n" unless (defined $sth);
# Execute statement at database level
$sth->execute(); # or die $sth->errstr;
return;
}
# Trace
DBI 1.14-nothread dispatch trace level set to 3
-> prepare for DBD::ADO::db
(DBI::db=HASH(0x1c48710)~0x2325054 '
INSERT INTO htmltables (Anchor,Table)
VALUES (testanchor,testtable)
')
New DBI::st (for DBD::ADO::st,
parent=DBI::db=HASH(0x2325054), id=)
dbih_setup_handle(DBI::st=HASH(0x232d3f0)=>DBI::st=HASH(0x232d3e4),
DBD::ADO::st, 232d3fc, Null!)
dbih_make_com(DBI::db=HASH(0x2325054),
DBD::ADO::st, 172)
<- prepare= DBI::st=HASH(0x232d3f0) at
dbi_test_query.pl line 70.
-> execute for DBD::ADO::st
(DBI::st=HASH(0x232d3f0)~0x232d3e4)
!! ERROR: 1 'Can't execute statement '
INSERT INTO htmltables (Anchor,Table)
VALUES (testanchor,testtable)
': -2147217900: OLE exception from "Microsoft OLE DB
Provider for ODBC Drivers":
[Microsoft][ODBC Microsoft Access Driver] Syntax error
in INSERT INTO
statement.
Win32::OLE(0.15) error 0x80040e14
in METHOD/PROPERTYGET "Execute"
-2147217900: '
<- execute= undef at dbi_test_query.pl line 79.
-- DBI::END
-> disconnect_all for DBD::ADO::dr
(DBI::dr=HASH(0x1cb8644)~0x1c48734)
<- disconnect_all= undef at DBI.pm line 450.
-> DESTROY for DBD::ADO::db
(DBI::db=HASH(0x2325054)~INNER)
<- DESTROY= undef during global destruction.
-> DESTROY for DBD::ADO::st
(DBI::st=HASH(0x232d3e4)~INNER)
<- DESTROY= undef during global destruction.
-> DESTROY for DBD::ADO::dr
(DBI::dr=HASH(0x1c48734)~INNER)
<- DESTROY= undef during global destruction.
<> DESTROY for DBI::db=HASH(0x1c48710) ignored
(inner handle gone)
<> DESTROY for DBI::st=HASH(0x232d3f0) ignored
(inner handle gone)
<> DESTROY for DBI::dr=HASH(0x1cb8644) ignored
(inner handle gone)
Re: Mysql DBI Select Syntax ?
Dear Steven,
Thanks for your opinion.
Here is my complete example ,
my $dbh = DBI->connect("DBI:mysql:x",,, {RaiseError => 1 });
my $sth = $dbh->prepare( qq{
CREATE TABLE a1 (diploma
ENUM("junior_high","senior_high","junior_college","university","master","doc
tor")
});
$sth->execute;
$dbh->disconnect;
my $dbh = DBI->connect("DBI:mysql:x",,, {RaiseError => 1 });
my $sth = $dbh->prepare( qq{
CREATE TABLE a2 (diploma
ENUM("junior_high","senior_high","junior_college","university","master","doc
tor")
});
$sth->execute;
$dbh->disconnect;
my $dbh = DBI->connect("DBI:mysql:x",,, { RaiseError => 1 });
my $sth =
repare( qq{
SELECT a1.diploma a2.diploma
FROM a1,a2
WHERE a1.diploma <= a2.diploma
});
$sth->execute;
$dbh->disconnect;
I'd like to compare a1.diploma and a2.diploma, and my ideal rule is doctor > master
> university > junior_college > .
But the result is university > senior_high > junior_high > junior_college > master >
doctor
Is there any method let me get my ideal rule is doctor > master > university >
junior_college > senior_high > junior_high
Thanks in advance.
Sincerelly
Tom Wu
- Original Message -
From: "Steve Howard" <[EMAIL PROTECTED]>
To: "About-tw.com ??" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Tuesday, July 03, 2001 10:59 AM
Subject: RE: Mysql DBI Select Syntax ?
I'm not completely sure I know what you're asking. If you're wanting to put a
numerical equivalent to the possible string values, in MySQL you can use a CASE
statement, like this:
SELECT case
WHEN diploma = 'junior_high' THEN 1
WHEN diploma = 'senior_high' THEN 2
WHEN diploma = 'junior_college' THEN 3
WHEN diploma = 'university' THEN 4
WHEN diploma = 'master' THEN 5
ELSE 6
END
AS DIPLOMA
FROM Tablename
You can embed some version of that to get a numerical return from a table enumerated
as you have said, however, it
still shouldn't return as you have put in your WHERE clause. You would still
have to use:
WHERE diploma = 'senior_high'
If you only wanted Senior high grads.
Is this what you are asking?
Steve Howard
-Original Message-
From: About-tw.com ?? [mailto:[EMAIL PROTECTED]]
Sent: Monday, July 02, 2001 3:16 PM
To: [EMAIL PROTECTED]
Subject: Mysql DBI Select Syntax ?
Dear All,
my $dbh = DBI->connect("DBI:mysql:x",,, {RaiseError => 1 });
my $sth = $dbh->prepare( qq{
CREATE TABLE $table_name (
diploma
ENUM("junior_high","senior_high","junior_college","university","master","doc
tor")
});
When I do the following procedure
my $dbh = DBI->connect("DBI:mysql:x",,, { RaiseError => 1 });
my $sth = $dbh->prepare( qq{SELECT *FROM $table_nameWHERE
diploma = 2});
$sth->execute;
my $diploma = $sth -> fetchrow_array ;
$dbh->disconnect;
I can get $diploma = "senior_high"
Now here is my problem , How could I get the value of the $diploma = 2 ?
I'll really appreciated if someone can help me.
Re: Problems Running from crontab.
"Scott T. Hildreth" wrote: > > I fixed it by setting the 'SHELL=/usr/local/bin/zsh' so > the LD_LIBRARY_PATH is getting set before Perl is executed. Why not just set the LD_LIBRARY_PATH and then execute the Perl-script instead of changing sh to zsh? > I'm not sure why the behavior changed, but it works :-) ;-)
SQL query
Hi all, I have a table (report_response) which has (among others) fault_no and response_no fields. Now a fault_no can have multiple response_no's. The thing is, when I issue the following SQL: SELECT fault_no ,one_line_summary FROM report_response WHERE (reported_by LIKE '%J BLOGGS%' ) order by fault_no DESC my returned list displays: 1355 Glish leftovers on sagitta 1350 Site phones 1350 Site phones See those multiple occurances of 1350? This means there are 2 responses to the fault_no = 1350. How can I fudge the SQL to select the last response_no submitted? Regards, Stacy. BTW: Using DBI:1.14 with Oracle 7.3.3
