Re: MySql Development Files

[Craig A. Berry]

At 11:44 AM 8/29/2001 +, Piyush Avichal wrote:

>Could somebody tell me where I can find the MySql Development Libraries
>required for the MySQL DBD. 

I've never heard of a MySQL port to VMS.  Indeed I don't know of any 
freeware or open source RDBMS on VMS.  Mimer at least has a free developer 
license and an ODBC interface that might work with DBD::ODBC:



You might also post to comp.os.vms and see if anyone has a MySQL port 
planned or in progress.

Re: MySql Development Files

[Brad Hughes]

"Craig A. Berry" wrote:
> 
> At 11:44 AM 8/29/2001 +, Piyush Avichal wrote:
> 
> >Could somebody tell me where I can find the MySql Development Libraries
> >required for the MySQL DBD.
> 
> I've never heard of a MySQL port to VMS.  Indeed I don't know of any
> freeware or open source RDBMS on VMS.  Mimer at least has a free developer
> license and an ODBC interface that might work with DBD::ODBC:

Dan O'Reilly of Process Software was looking into porting MySQL a couple
of years ago.  He got reasonably far, but I seem to recall that there were
a couple of Really Hard Obstacles to overcome.

Awhile back Interbase announced they're going the Open Source route,
and given its heritage an OpenVMS port wouldn't be surprising.

Brad

Keeping a database connection across a fork

[Jay Strauss]

Hi,

I'm trying to write a daemon that accesses my database.  I thought I could
create the connection in the parent, and use it in the child.  But that
doesn't seem to work.  Below is the code.  Any help would be appreciated

Thanks
Jay

#!/usr/bin/perl -w

use strict;
use DBI;
use POSIX qw(setsid);

$|=1;

my $service = "o817";
my $userid = "jstrauss";
my $passwd = "passwd";

my $dbh = DBI->connect("dbi:Oracle:$service","$userid","$passwd",
   { RaiseError => 1, AutoCommit => 0 }) or
   die "Can't connect to Oracle database: $DBI::errstr\n";

my $sth = $dbh->prepare("select fromuser, touser from refresh");

&daemonize;

while(1) {
   print "Hello...\n";

   $sth->execute;

   while (my @row = $sth->fetchrow_array ) {
 print "$row[0] - $row[1]\n";
   }

   sleep(5);
}

sub daemonize {
   chdir '/' or die "Can't chdir to /: $!";
   open STDIN, '/dev/null'   or die "Can't read /dev/null: $!";
   open STDERR, '>>/dev/null' or die "Can't write to /dev/null: $!";
   defined(my $pid = fork)   or die "Can't fork: $!";
   exit if $pid;
   setsidor die "Can't start a new session: $!";
   umask 0;
}

Problems installing DBI 1.20

[Danko, Gary]

I am trying to install Bundle::DBI to an Ultra 250 with Solaris 7. I
installed a fresh copy of Perl 5.6.1 from the Sun Freeware package.

I've enclosed a text file with a capture of my attempted install. I hope
someone can help. :)

Not sure if this is some sort of mailing list so please reply by mail if you
have any ideas. Thanks!

 <> 


bash-2.05# uname -a
SunOS sd-play-01 5.7 Generic_106541-17 sun4u sparc SUNW,Ultra-250
bash-2.05# perl -MCPAN -e 'install Bundle::DBI'

/usr/local/lib/perl5/5.6.1/CPAN/Config.pm initialized.


CPAN is the world-wide archive of perl resources. It consists of about
100 sites that all replicate the same contents all around the globe.
Many countries have at least one CPAN site already. The resources
found on CPAN are easily accessible with the CPAN.pm module. If you
want to use CPAN.pm, you have to configure it properly.

If you do not want to enter a dialog now, you can answer 'no' to this
question and I'll try to autoconfigure. (Note: you can revisit this
dialog anytime later by typing 'o conf init' at the cpan prompt.)

Are you ready for manual configuration? [yes] 


The following questions are intended to help you with the
configuration. The CPAN module needs a directory of its own to cache
important index files and maybe keep a temporary mirror of CPAN files.
This may be a site-wide directory or a personal directory.



First of all, I'd like to create this directory. Where?

CPAN build and cache directory? [/.cpan] 


If you want, I can keep the source files after a build in the cpan
home directory. If you choose so then future builds will take the
files from there. If you don't want to keep them, answer 0 to the
next question.



How big should the disk cache be for keeping the build directories
with all the intermediate files?

Cache size for build directory (in MB)? [10] 


By default, each time the CPAN module is started, cache scanning
is performed to keep the cache size in sync. To prevent from this,
disable the cache scanning with 'never'.

Perform cache scanning (atstart or never)? [atstart] 


To considerably speed up the initial CPAN shell startup, it is
possible to use Storable to create a cache of metadata. If Storable
is not available, the normal index mechanism will be used.

Cache metadata (yes/no)? [yes] 


The next option deals with the charset your terminal supports. In
general CPAN is English speaking territory, thus the charset does not
matter much, but some of the aliens out there who upload their
software to CPAN bear names that are outside the ASCII range. If your
terminal supports UTF-8, you say no to the next question, if it
supports ISO-8859-1 (also known as LATIN1) then you say yes, and if it
supports neither nor, your answer does not matter, you will not be
able to read the names of some authors anyway. If you answer no, names
will be output in UTF-8.

Your terminal expects ISO-8859-1 (yes/no)? [yes] 


The CPAN module can detect when a module that which you are trying to
build depends on prerequisites. If this happens, it can build the
prerequisites for you automatically ('follow'), ask you for
confirmation ('ask'), or just ignore them ('ignore'). Please set your
policy to one of the three values.

Policy on building prerequisites (follow, ask or ignore)? [ask] 


The CPAN module will need a few external programs to work properly.
Please correct me, if I guess the wrong path for a program. Don't
panic if you do not have some of them, just press ENTER for those. To
disable the use of a download program, you can type a space followed
by ENTER.

Where is your gzip program? [/usr/local/bin/gzip] /usr/local/bin/gzip
Where is your tar program? [/bin/tar] /usr/bin/tar
Where is your unzip program? [/bin/unzip] /usr/bin/unzip
Where is your make program? [/bin/make] /usr/local/bin/make
Warning: lynx not found in PATH
Where is your lynx program? [] 
Warning: wget not found in PATH
Where is your wget program? [] /usr/local/bin/wget
Warning: ncftpget not found in PATH
Where is your ncftpget program? [] 
Warning: ncftp not found in PATH
Where is your ncftp program? [] 
Where is your ftp program? [/bin/ftp] /usr/bin/ftp
What is your favorite pager program? [/bin/more] /usr/bin/more
What is your favorite shell? [/sbin/sh] /usr/local/bin/bash


Every Makefile.PL is run by perl in a separate process. Likewise we
run 'make' and 'make install' in processes. If you have any
parameters (e.g. PREFIX, LIB, UNINST or the like) you want to pass
to the calls, please specify them here.

If you don't understand this question, just press ENTER.

Parameters for the 'perl Makefile.PL' command?
Typical frequently used settings:

POLLUTE=1increasing backwards compatibility
LIB=~/perl   non-root users (please see manual for more hints)

Your choice:  [] 
Parameters for the 'make' command?
Typical frequently used setting:

-j3  dual processor system

Your choice:  [] 
Parameters for the 'make install' command?
Typical frequently used setti

parameter unknown: problem with DBD::Pg quote method

[Alex Krohn]

Hi,

I came across this problem that seems to be with DBD::Pg's quote
function that is easily reproducible. This test script:

my $dbh = DBI->connect("DBI:Pg:dbname=test", "postgres", "");
my $val = $dbh->quote(q!\'?:!);
print "val: $val\n";
my $sth = $dbh->prepare("INSERT INTO foo (a) values ($val)");
$sth->execute;

produces:

DBD::Pg::st execute failed: parameter unknown at mytest.pl line 6.

If I use mysql, or Oracle, as a driver, it works as expected. I searched
through the list, and the only answer seemed to be to use placeholders,
which isn't really an option in my situation.

Any ideas on workarounds, or a patch to DBD::Pg would be great. 

Please cc me any replies as I'm not on the list.

Cheers,

Alex

counting no. of records matching condition.

[Rajeev Rumale]

Greeting every one,

I need to know the best way to count the number of occurances of multiple
fields in a single table matching some conditions.

For example I have a table consisting of fields
id, name, type, status,

Here the type field can have values
W-Worker,
S-supervisior
M-Manager
A-Adminstrative staff
T-Tempory / Contract worker

And the status field can have values
P-present on duty
L-On Leave
A-Absent (without applying leave )
O-Off duty (long vacation given for perticular positions as per
company terms)



Here I need to calculate
1. Total no of emplaoyees,
2. No employees of each type,
3. No. of employees Present on duty,
4. No. of employees Absent.

Can we do this with a single sql statement.  Currently I am using one for
each of the condition, which definately not a good way.  The other way I can
look for is to fetch all records and then do calcuations in the Perl Script.

I am use MySql, with Active Perl on a win2k and IIS.

Kindly suggest.

Regards

Rajeev Rumale


-
Your diamonds are not in far distant mountains or in yonder seas; they are
in your own backyard, if you but dig for them.

DBD::Oracle

[Karina Nahagama]

My name is Karina Nagahama,
I want to install DBD::Oracle, but I have problems in building it.

I download DBD-Oracle-1.08.tar.gz from
http://velocity.activestate.com/code/cpan/d/db/DBD-Oracle.html

I' working on Windows NT,
using Apache 1.3.20,Perl 5.6.1,mod_perl1.25_01 and also DBI is installed
too.
The database I'm using is Oracle 8, and it works well.

I read the readme.txt from the package but I'm unable to build .

I'm unclear about what is wrong.

Apache 1.3.20,Perl 5.6.1,mod_perl1.25_01 work well.

In the path of the system perl is writen as 'C:\Perl\bin',
and my perl.exe is in there.Is this wrong?
In order to execute Makefile.PL from My path,
I copied the file in the path ('C:\Perl\bin').
I couldn't run Makefile.PL in Ms-Dos Prompt by typing 'perl Makefile.PL'
So I executed Makefile.PL by clicking it,
but unfortunatelly the next message appears:
   Configuring DBD::Oracle
   Multiple Oracle homes: e:\Ora8i e\OraMngr
   Using Oracle in e:/OraMngr
   Can't start e:/OraMngr/rdbms: No such file or directory
   I can't find the header files I need in your Oracle Installation.
   You probably need to install some more Oracle components.

Also the next message was written at listener8i.log:
30-AUG-01 16:24:42 * step2 *
(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.50)(PORT=1095)) * service_update *
STEP2 * 406
NNC-00406: $BL>A0(B: "LISTENER8i_pisvr2" $B$,$"$j$^$;$s!#(B
 NNC-00406: $BL>A0(B: "step2" $B$,$"$j$^$;$s!#(B
  NNC-00406: $BL>A0(B: "PLSExtProc" $B$,$"$j$^$;$s!#(B
   NNC-00406: $BL>A0(B: "Ora8i0.pint" $B$,$"$j$^$;$s!#(B

What do I need to do in order to install DBD::Oracle?
Sorry for trouble you.
I really apreciate your collaboration.
karina.kcom.ne.jp

DBD::Oracle 1.09 build failure

[Eric Kolve]

I am building DBD::Oracle 1.09 against Oracle 8.17 client libs, DBI
1.20, perl 5.005_03 on RH linux 6.1 and I get the following error when I
run 'make':

cc -c /OraHome1/rdbms/demo /OraHome1/rdbms/public /OraHome1/plsql/public
/OraHome1/network/public -I/OraHome1/rdbms/demo -I/OraHome1/rdbms/demo
-I/usr/lib/perl5/site_perl/5.005/i386-linux/auto/DBI -Dbool=char
-DHAS_BOOL -I/usr/local/include -O2-DVERSION=\"1.09\"
-DXS_VERSION=\"1.09\" -fpic -I/usr/lib/perl5/5.00503/i386-linux/CORE 
Oracle.c
cc: /OraHome1/plsql/public: No such file or directory
cc: /OraHome1/network/public: No such file or directory
In file included from dbdimp.h:52,
 from Oracle.h:20,
 from Oracle.xs:1:
/OraHome1/rdbms/demo/oci.h:1668: ociextp.h: No such file or directory
In file included from /OraHome1/rdbms/demo/oci.h:1673,
 from dbdimp.h:52,
 from Oracle.h:20,
 from Oracle.xs:1:
/OraHome1/rdbms/demo/ociap.h:168: nzt.h: No such file or directory
In file included from dbdimp.h:52,
 from Oracle.h:20,
 from Oracle.xs:1:
/OraHome1/rdbms/demo/oci.h:1684: ociextp.h: No such file or directory
make: *** [Oracle.o] Error 1

ORACLE_HOME = /OraHome1


I can build DBD::Oracle 1.08 with no problems.  Let me know if any other
information would be helpful.

--eric

DBD::Oracle 1.10 build failure

[E Kolve]

I am building DBD::Oracle 1.09 against Oracle 8.17 client libs, DBI
1.20, perl 5.005_03 on RH linux 6.1 and I get the following error when I
run 'make':

mkdir blib
mkdir blib/lib
mkdir blib/lib/DBD
mkdir blib/arch
mkdir blib/arch/auto
mkdir blib/arch/auto/DBD
mkdir blib/arch/auto/DBD/Oracle
mkdir blib/lib/auto
mkdir blib/lib/auto/DBD
mkdir blib/lib/auto/DBD/Oracle
mkdir blib/man1
mkdir blib/man3
cp Oraperl.pm blib/lib/Oraperl.pm
cp ocitrace.h blib/arch/auto/DBD/Oracle/ocitrace.h
cp Oracle.pm blib/lib/DBD/Oracle.pm
cp mk.pm blib/arch/auto/DBD/Oracle/mk.pm
cp oraperl.ph blib/lib/oraperl.ph
cp Oracle.h blib/arch/auto/DBD/Oracle/Oracle.h
cp dbdimp.h blib/arch/auto/DBD/Oracle/dbdimp.h
/usr/bin/perl -p -e "s/~DRIVER~/Oracle/g" <
/usr/lib/perl5/site_perl/5.005/i386-linux/auto/DBI/Driver.xst >
Oracle.xsi
/usr/bin/perl -I/usr/lib/perl5/5.00503/i386-linux
-I/usr/lib/perl5/5.00503 /usr/lib/perl5/5.00503/ExtUtils/xsubpp 
-typemap /usr/lib/perl5/5.00503/ExtUtils/typemap Oracle.xs >xstmp.c &&
mv xstmp.c Oracle.c
cc -c -I/OraHome1/rdbms/demo -I/OraHome1/rdbms/public
-I/OraHome1/plsql/public -I/OraHome1/network/public
-I/OraHome1/rdbms/demo -I/OraHome1/rdbms/demo
-I/usr/lib/perl5/site_perl/5.005/i386-linux/auto/DBI -Dbool=char
-DHAS_BOOL -I/usr/local/include -O2-DVERSION=\"1.10\"
-DXS_VERSION=\"1.10\" -fpic -I/usr/lib/perl5/5.00503/i386-linux/CORE 
Oracle.c
cc -c -I/OraHome1/rdbms/demo -I/OraHome1/rdbms/public
-I/OraHome1/plsql/public -I/OraHome1/network/public
-I/OraHome1/rdbms/demo -I/OraHome1/rdbms/demo
-I/usr/lib/perl5/site_perl/5.005/i386-linux/auto/DBI -Dbool=char
-DHAS_BOOL -I/usr/local/include -O2-DVERSION=\"1.10\"
-DXS_VERSION=\"1.10\" -fpic -I/usr/lib/perl5/5.00503/i386-linux/CORE 
dbdimp.c
cc -c -I/OraHome1/rdbms/demo -I/OraHome1/rdbms/public
-I/OraHome1/plsql/public -I/OraHome1/network/public
-I/OraHome1/rdbms/demo -I/OraHome1/rdbms/demo
-I/usr/lib/perl5/site_perl/5.005/i386-linux/auto/DBI -Dbool=char
-DHAS_BOOL -I/usr/local/include -O2-DVERSION=\"1.10\"
-DXS_VERSION=\"1.10\" -fpic -I/usr/lib/perl5/5.00503/i386-linux/CORE 
oci7.c
cc -c -I/OraHome1/rdbms/demo -I/OraHome1/rdbms/public
-I/OraHome1/plsql/public -I/OraHome1/network/public
-I/OraHome1/rdbms/demo -I/OraHome1/rdbms/demo
-I/usr/lib/perl5/site_perl/5.005/i386-linux/auto/DBI -Dbool=char
-DHAS_BOOL -I/usr/local/include -O2-DVERSION=\"1.10\"
-DXS_VERSION=\"1.10\" -fpic -I/usr/lib/perl5/5.00503/i386-linux/CORE 
oci8.c
oci8.c: In function `oci_hdtype_name':
oci8.c:118: `OCI_DTYPE_DATETIME' undeclared (first use in this function)
oci8.c:118: (Each undeclared identifier is reported only once
oci8.c:118: for each function it appears in.)
oci8.c:119: `OCI_DTYPE_INTERVAL' undeclared (first use in this function) 


I can build DBD::Oracle 1.08 with no problems.  Let me know if any other
information would be helpful.

--eric

Error while building DBD Oracle driver

[TAMBE,ROHIT (Non-HP-Singapore,ex4)]

Hi all,
   I'm using Perl 5.004_04 built for PA-RISC1.1 on HP-UX B.11.00 and Oracle
8.0.5
   I am installing DBI 1.20 and DBD-Oracle-1.08. I could successfully
install the DBI.
While installing the DBD I got foll. error. 

  syntax error at Makefile.PL line 808, near "'' for "
  syntax error at Makefile.PL line 820, near "@rules for "
  Execution of Makefile.PL aborted due to compilation errors.

How to get over this problem? Moreover is this DBD-Oracle-1.08 a working
version or
yet to be tried out? If not working yet then which is the working version
at present?



Thnaks
Rohit Tambe
TN:8313586

how to enable 'error possibly near <*> indicator'...

[iowa]

hiho,

i'm stuck on trying to make DBD::Oracle error messages more
verbose...

i've one machine (Linux, DBI 1.13, DBD::Oracle 1.03, Oracle
8.1.5) which produces 'nice' error messages that contain 
'error possibly near <*> indicator at...' information and
another machine (Linux, DBI 1.20, DBD::Oracle 1.10, Oracle
8.1.7) which refuses to produce anything more than DBD ERROR:
OCIStmtExecute/Describe...

did i miss something in DBI.pm or DBD::Oracle? or is it some 
missing magic in the local oracle installation/configuration?

cheers,
-iowa 

Oracle 8i / ORACLE_HOME in registry

[Tim Callaghan]

Tim,

Can you please let me know why DBD:Oracle will not run on an Oracle 8.1.6
client machine?

By default, 8.1.6 does not create a registry key for ORACLE_HOME.  The
machines in question have no problem executing Enterprise Manager, SQL*Plus,
or PowerBuilder applications so I have to believe the installation of Net8
is not an issue.  

If I create HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ORACLE_HOME and put
C:\Oracle\Ora81 in my registry everything works fine.

Thanks for your time,
Tim Callaghan

DBD::Oracle-1.10 build problem with Oracle-8.1.7

[Mark Borges]

[ I'm not on the mailing list, so I apologize if this is already a known
  reported problem. Also, if this is insufficient information please
  let me know and I can supply a full verbose report as directed in
  the README. ]

Attempting to build DBD::Oracle-1.10 under Solaris-2.6 and
Oracle-8.1.7, I encountered the following compilation error:

 oci8.c: In function `oci_hdtype_name':
 oci8.c:118: `OCI_DTYPE_DATETIME' undeclared (first use in this function)
 oci8.c:118: (Each undeclared identifier is reported only once
 oci8.c:118: for each function it appears in.)
 oci8.c:119: `OCI_DTYPE_INTERVAL' undeclared (first use in this function)
 make: *** [oci8.o] Error 1

These are not defined in my 8.1.7's copy of .../rdbms/demo/oci.h,
but similar data types (OCI_DTYPE_DATE, OCI_DTYPE_TIME, etc.) are.

Here is the relevant excerpt from oci.h:

 /*-Descriptor Types--*/
 /* descriptor values range from 50 - 255 */
 #define OCI_DTYPE_FIRST 50 /* start value of descriptor type */
 #define OCI_DTYPE_LOB 50 /* lob  locator */
 #define OCI_DTYPE_SNAP 51 /* snapshot descriptor */
 #define OCI_DTYPE_RSET 52   /* result set descriptor */
 #define OCI_DTYPE_PARAM 53  /* a parameter descriptor obtained from ocigparm */
 #define OCI_DTYPE_ROWID  54  /* rowid descriptor */
 #define OCI_DTYPE_COMPLEXOBJECTCOMP  55
   /* complex object retrieval descriptor */
 #define OCI_DTYPE_FILE 56/* File Lob locator */
 #define OCI_DTYPE_AQENQ_OPTIONS 57/* enqueue options */
 #define OCI_DTYPE_AQDEQ_OPTIONS 58/* dequeue options */
 #define OCI_DTYPE_AQMSG_PROPERTIES 59  /* message properties */
 #define OCI_DTYPE_AQAGENT 60 /* aq agent */
 #define OCI_DTYPE_LOCATOR 61  /* LOB locator */
 #define OCI_DTYPE_INTERVAL_YM 62 /* Interval year month */
 #define OCI_DTYPE_INTERVAL_DS 63 /* Interval day second */
 #define OCI_DTYPE_AQNFY_DESCRIPTOR  64   /* AQ notify descriptor */
 #define OCI_DTYPE_DATE 65/* Date */
 #define OCI_DTYPE_TIME 66/* Time */
 #define OCI_DTYPE_TIME_TZ 67 /* Time with timezone */
 #define OCI_DTYPE_TIMESTAMP 68   /* Timestamp */
 #define OCI_DTYPE_TIMESTAMP_TZ 69/* Timestamp with timezone */
 #define OCI_DTYPE_TIMESTAMP_LTZ 70 /* Timestamp with local tz */
 #define OCI_DTYPE_UCB   71   /* user callback descriptor */
 #define OCI_DTYPE_LAST  71/* last value of a descriptor type */
 /*---*/


The appended patch works for me, but is probably not correct in
general for all versions of Oracle8i.

== begin patch ==
$ diff -u oci8.c.orig oci8.c 
--- oci8.c.orig Thu Aug 30 08:48:47 2001
+++ oci8.c  Fri Aug 31 11:09:27 2001
@@ -115,8 +115,16 @@
 case OCI_DTYPE_AQMSG_PROPERTIES:   return "OCI_DTYPE_AQMSG_PROPERTIES";
 case OCI_DTYPE_AQAGENT:return "OCI_DTYPE_AQAGENT";
 case OCI_DTYPE_LOCATOR:return "OCI_DTYPE_LOCATOR";
-case OCI_DTYPE_DATETIME:   return "OCI_DTYPE_DATETIME";
-case OCI_DTYPE_INTERVAL:   return "OCI_DTYPE_INTERVAL";
+  /* case OCI_DTYPE_DATETIME:  return "OCI_DTYPE_DATETIME";
+case OCI_DTYPE_INTERVAL:   return "OCI_DTYPE_INTERVAL";
+  */
+case OCI_DTYPE_DATE:   return "OCI_DTYPE_DATE";
+case OCI_DTYPE_TIME:   return "OCI_DTYPE_TIME";
+case OCI_DTYPE_TIME_TZ:return "OCI_DTYPE_TIME_TZ";
+case OCI_DTYPE_TIMESTAMP:  return "OCI_DTYPE_TIMESTAMP";
+case OCI_DTYPE_TIMESTAMP_TZ:   return "OCI_DTYPE_TIMESTAMP_TZ";
+case OCI_DTYPE_INTERVAL_YM:return "OCI_DTYPE_INTERVAL_YM";
+case OCI_DTYPE_INTERVAL_DS:return "OCI_DTYPE_INTERVAL_DS";
 case OCI_DTYPE_AQNFY_DESCRIPTOR:   return "OCI_DTYPE_AQNFY_DESCRIPTOR";
 }
 sv = sv_2mortal(newSViv(hdtype));
== end patch ==

RE: counting no. of records matching condition.

[Steve Howard]

Unless I'm missing something, you cannot do this all with one query. You can
do it with two queries, but I'm not sure that would be any more efficient
than handling each with a separate query. The reason you can't get it all in
one query is that one of these will require a "Group by" clause, and since
you are wanting results that have nothing to do with either the aggregate or
the group by, trying to combine that will throw an error. This is untested
since I didn't actually have your table to work with, but I tested the
concept to be sure I was right on that before I typed this in:

Query 1 (Gives total number of employees, and those present, and those
absent):

SELECT COUNT(*) as TOTAL,
(SELECT COUNT(*) FROM Table WHERE status = 'P') as PRESENT,
(SELECT COUNT(*) FROM Table WHERE STATUS IN ('L', 'A', 'O')) AS ABSENT
FROM Table

Query 2 (Gives the number of employees of each type):

SELECT type, COUNT(*) as NUMBER
FROM Table
GROUP BY type

Now, just embed those into your Perl and you can get the results you are
looking for into a report.

Hope this helps,


Steve H.

-Original Message-
From: Rajeev Rumale [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, August 29, 2001 10:51 PM
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: counting no. of records matching condition.


Greeting every one,

I need to know the best way to count the number of occurances of multiple
fields in a single table matching some conditions.

For example I have a table consisting of fields
id, name, type, status,

Here the type field can have values
W-Worker,
S-supervisior
M-Manager
A-Adminstrative staff
T-Tempory / Contract worker

And the status field can have values
P-present on duty
L-On Leave
A-Absent (without applying leave )
O-Off duty (long vacation given for perticular positions as per
company terms)



Here I need to calculate
1. Total no of emplaoyees,
2. No employees of each type,
3. No. of employees Present on duty,
4. No. of employees Absent.

Can we do this with a single sql statement.  Currently I am using one for
each of the condition, which definately not a good way.  The other way I can
look for is to fetch all records and then do calcuations in the Perl Script.

I am use MySql, with Active Perl on a win2k and IIS.

Kindly suggest.

Regards

Rajeev Rumale


-
Your diamonds are not in far distant mountains or in yonder seas; they are
in your own backyard, if you but dig for them.

Re: DBD::Oracle-1.10 build problem with Oracle-8.1.7

[john mcilwain]

You need the DBD::Oracle 1.12 build, it fixed my problems ;)

It should be on the mirrors by now...

-john

>From: Mark Borges <[EMAIL PROTECTED]>
>Reply-To: [EMAIL PROTECTED]
>To: [EMAIL PROTECTED]
>CC: [EMAIL PROTECTED]
>Subject: DBD::Oracle-1.10 build problem with Oracle-8.1.7
>Date: 31 Aug 2001 11:21:45 -0700
>
>[ I'm not on the mailing list, so I apologize if this is already a known
>   reported problem. Also, if this is insufficient information please
>   let me know and I can supply a full verbose report as directed in
>   the README. ]
>
>Attempting to build DBD::Oracle-1.10 under Solaris-2.6 and
>Oracle-8.1.7, I encountered the following compilation error:
>
>  oci8.c: In function `oci_hdtype_name':
>  oci8.c:118: `OCI_DTYPE_DATETIME' undeclared (first use in this function)
>  oci8.c:118: (Each undeclared identifier is reported only once
>  oci8.c:118: for each function it appears in.)
>  oci8.c:119: `OCI_DTYPE_INTERVAL' undeclared (first use in this function)
>  make: *** [oci8.o] Error 1
>
>These are not defined in my 8.1.7's copy of .../rdbms/demo/oci.h,
>but similar data types (OCI_DTYPE_DATE, OCI_DTYPE_TIME, etc.) are.
>
>Here is the relevant excerpt from oci.h:
>
>  /*-Descriptor 
>Types--*/
>  /* descriptor values range from 50 - 
>255 */
>  #define OCI_DTYPE_FIRST 50 /* start value of descriptor 
>type */
>  #define OCI_DTYPE_LOB 50 /* lob  
>locator */
>  #define OCI_DTYPE_SNAP 51 /* snapshot 
>descriptor */
>  #define OCI_DTYPE_RSET 52   /* result set 
>descriptor */
>  #define OCI_DTYPE_PARAM 53  /* a parameter descriptor obtained from 
>ocigparm */
>  #define OCI_DTYPE_ROWID  54  /* rowid 
>descriptor */
>  #define OCI_DTYPE_COMPLEXOBJECTCOMP  55
>/* complex object retrieval 
>descriptor */
>  #define OCI_DTYPE_FILE 56/* File Lob 
>locator */
>  #define OCI_DTYPE_AQENQ_OPTIONS 57/* enqueue 
>options */
>  #define OCI_DTYPE_AQDEQ_OPTIONS 58/* dequeue 
>options */
>  #define OCI_DTYPE_AQMSG_PROPERTIES 59  /* message 
>properties */
>  #define OCI_DTYPE_AQAGENT 60 /* aq 
>agent */
>  #define OCI_DTYPE_LOCATOR 61  /* LOB 
>locator */
>  #define OCI_DTYPE_INTERVAL_YM 62 /* Interval year month 
>*/
>  #define OCI_DTYPE_INTERVAL_DS 63 /* Interval day second 
>*/
>  #define OCI_DTYPE_AQNFY_DESCRIPTOR  64   /* AQ notify 
>descriptor */
>  #define OCI_DTYPE_DATE 65/* Date */
>  #define OCI_DTYPE_TIME 66/* Time */
>  #define OCI_DTYPE_TIME_TZ 67 /* Time with 
>timezone */
>  #define OCI_DTYPE_TIMESTAMP 68   /* Timestamp */
>  #define OCI_DTYPE_TIMESTAMP_TZ 69/* Timestamp with 
>timezone */
>  #define OCI_DTYPE_TIMESTAMP_LTZ 70 /* Timestamp with local tz 
>*/
>  #define OCI_DTYPE_UCB   71   /* user callback 
>descriptor */
>  #define OCI_DTYPE_LAST  71/* last value of a descriptor 
>type */
>  
>/*---*/
>
>
>The appended patch works for me, but is probably not correct in
>general for all versions of Oracle8i.
>
>== begin patch ==
>$ diff -u oci8.c.orig oci8.c
>--- oci8.c.orig Thu Aug 30 08:48:47 2001
>+++ oci8.c  Fri Aug 31 11:09:27 2001
>@@ -115,8 +115,16 @@
>  case OCI_DTYPE_AQMSG_PROPERTIES:   return 
>"OCI_DTYPE_AQMSG_PROPERTIES";
>  case OCI_DTYPE_AQAGENT:return "OCI_DTYPE_AQAGENT";
>  case OCI_DTYPE_LOCATOR:return "OCI_DTYPE_LOCATOR";
>-case OCI_DTYPE_DATETIME:   return "OCI_DTYPE_DATETIME";
>-case OCI_DTYPE_INTERVAL:   return "OCI_DTYPE_INTERVAL";
>+  /* case OCI_DTYPE_DATETIME:  return 
>"OCI_DTYPE_DATETIME";
>+case OCI_DTYPE_INTERVAL:   return 
>"OCI_DTYPE_INTERVAL";
>+  */
>+case OCI_DTYPE_DATE:   return "OCI_DTYPE_DATE";
>+case OCI_DTYPE_TIME:   return "OCI_DTYPE_TIME";
>+case OCI_DTYPE_TIME_TZ:return "OCI_DTYPE_TIME_TZ";
>+case OCI_DTYPE_TIMESTAMP:  return "OCI_DTYPE_TIMESTAMP";
>+case OCI_DTYPE_TIMESTAMP_TZ:   return "OCI_DTYPE_TIMESTAMP_TZ";
>+case OCI_DTYPE_INTERVAL_YM:return 
>"OCI_DTYPE_INTERVAL_YM";
>+case OCI_DTYPE_INTERVAL_DS:return 
>"OCI_DTYPE_INTERVAL_DS";
>  case OCI_DTYPE_AQNFY_DESCRIPTOR:   return 
>"OCI_DTYPE_AQNFY_DESCRIPTOR";
>  }
>  sv = sv_2mortal(newSViv(hdtype));
>== end patch ==


_

RE: counting no. of records matching condition.

[Robert Goff]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

> SELECT COUNT(*) as TOTAL,
>   (SELECT COUNT(*) FROM Table WHERE status = 'P') as PRESENT,
>   (SELECT COUNT(*) FROM Table WHERE STATUS IN ('L', 'A', 
> 'O')) AS ABSENT
>   FROM Table

I don't think sub-selects are suppored in MySQL, unless I've missed
something. (I'm reading this in the mysql modules list.)

- --
Hemaglobe (n.) The bloody state of the world.
==
Robert Goff  http://aisling.cc/resume/
Technical Writer/Editor, Webmaster505-564-8959


-BEGIN PGP SIGNATURE-
Version: PGP Personal Privacy 6.5.3

iQA/AwUBO5D8SQLt+lN6EYZ6EQLdhQCg0JPn4KHxqIxZK3RSMVItxGJQbLAAoLOX
I1It4uGbxeLiHNkZIKEE+URr
=TUbW
-END PGP SIGNATURE-

Re: Oracle 8i / ORACLE_HOME in registry

[Tim Bunce]

Can you please start debugging it, fix the problem, and send me a patch?

Thanks for your time.
Tim.

On Fri, Aug 31, 2001 at 01:27:18PM -0400, Tim Callaghan wrote:
> Tim,
> 
> Can you please let me know why DBD:Oracle will not run on an Oracle 8.1.6
> client machine?
> 
> By default, 8.1.6 does not create a registry key for ORACLE_HOME.  The
> machines in question have no problem executing Enterprise Manager, SQL*Plus,
> or PowerBuilder applications so I have to believe the installation of Net8
> is not an issue.  
> 
> If I create HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ORACLE_HOME and put
> C:\Oracle\Ora81 in my registry everything works fine.
> 
> Thanks for your time,
> Tim Callaghan

RE: :Oracle

[Sterin, Ilya]

You should use the ppm utility to install modules.

In command prompt:

ppm install DBD::Oracle

or

ppm install DBD::Oracle8

This will install precompiled binaries.

If you want to compile yourself through nmake, you must have Visual C++
compiler installed on your system as well as the Oracle client.

perl Makefile.PL
nmake
nmake test
nmake install

Ilya

> -Original Message-
> From: Karina Nahagama [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, August 30, 2001 4:43 AM
> To: [EMAIL PROTECTED]
> Subject: DBD::Oracle
>
>
> My name is Karina Nagahama,
> I want to install DBD::Oracle, but I have problems in building it.
>
> I download DBD-Oracle-1.08.tar.gz from
> http://velocity.activestate.com/code/cpan/d/db/DBD-Oracle.html
>
> I' working on Windows NT,
> using Apache 1.3.20,Perl 5.6.1,mod_perl1.25_01 and also DBI is installed
> too.
> The database I'm using is Oracle 8, and it works well.
>
> I read the readme.txt from the package but I'm unable to build .
>
> I'm unclear about what is wrong.
>
> Apache 1.3.20,Perl 5.6.1,mod_perl1.25_01 work well.
>
> In the path of the system perl is writen as 'C:\Perl\bin',
> and my perl.exe is in there.Is this wrong?
> In order to execute Makefile.PL from My path,
> I copied the file in the path ('C:\Perl\bin').
> I couldn't run Makefile.PL in Ms-Dos Prompt by typing 'perl Makefile.PL'
> So I executed Makefile.PL by clicking it,
> but unfortunatelly the next message appears:
>Configuring DBD::Oracle
>Multiple Oracle homes: e:\Ora8i e\OraMngr
>Using Oracle in e:/OraMngr
>Can't start e:/OraMngr/rdbms: No such file or directory
>I can't find the header files I need in your Oracle Installation.
>You probably need to install some more Oracle components.
>
> Also the next message was written at listener8i.log:
> 30-AUG-01 16:24:42 * step2 *
> (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.50)(PORT=1095)) *
> service_update *
> STEP2 * 406
> NNC-00406: $BL>A0(J: "LISTENER8i_pisvr2" $B$,$"$j$^$;$s!#(J
>  NNC-00406: $BL>A0(J: "step2" $B$,$"$j$^$;$s!#(J
>   NNC-00406: $BL>A0(J: "PLSExtProc" $B$,$"$j$^$;$s!#(J
>NNC-00406: $BL>A0(J: "Ora8i0.pint" $B$,$"$j$^$;$s!#(J
>
> What do I need to do in order to install DBD::Oracle?
> Sorry for trouble you.
> I really apreciate your collaboration.
> karina.kcom.ne.jp
>
>

Re: Keeping a database connection across a fork

[Stephen Clouse]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On Wed, Aug 29, 2001 at 03:23:39PM -0500, Jay Strauss wrote:
> I'm trying to write a daemon that accesses my database.  I thought I could
> create the connection in the parent, and use it in the child.  But that
> doesn't seem to work.  Below is the code.  Any help would be appreciated

Initiate the database connect in the child.  You generally can't share database 
connections across processes.  You *definitely* can't share Oracle connections 
across processes.

- -- 
Stephen Clouse <[EMAIL PROTECTED]>
Senior Programmer, IQ Coordinator Project Lead
The IQ Group, Inc. 

-BEGIN PGP SIGNATURE-
Version: PGP 6.5.8

iQA/AwUBO5F3cwOGqGs0PadnEQIj3ACdFqzGbC+DqRFO5tCW10Swa9ebRkoAoJ26
ocRVaOPs3jVFK7YnD3HlrRrw
=nt62
-END PGP SIGNATURE-

Re: Keeping a database connection across a fork

[Steven Lembark]

-- Stephen Clouse <[EMAIL PROTECTED]>

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On Wed, Aug 29, 2001 at 03:23:39PM -0500, Jay Strauss wrote:
>> I'm trying to write a daemon that accesses my database.  I thought I
>> could create the connection in the parent, and use it in the child.  But
>> that doesn't seem to work.  Below is the code.  Any help would be
>> appreciated
>
> Initiate the database connect in the child.  You generally can't share
> database  connections across processes.  You *definitely* can't share
> Oracle connections  across processes.

Actually you can.  Trick is to turn on the NO-DESTROY option (forgot the
exact keyword) and let the children destroy things when they exit.  It's
actually fairly workable to share a database handle w/ the sub-proc's
preparing their own statement handles.  The main problem is that is a
True Pain (tm) to debug forks via the perl debugger and DBI isn't normally
the sort of thing you want to debug with printf's :-)

--
Steven Lembark   2930 W. Palmer
Workhorse Computing   Chicago, IL 60647
+1 800 762 1582

Re: Keeping a database connection across a fork

[Stephen Clouse]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On Sat, Sep 01, 2001 at 02:30:57PM -0500, Steven Lembark wrote:
> Actually you can.  Trick is to turn on the NO-DESTROY option (forgot the
> exact keyword) and let the children destroy things when they exit.  It's
> actually fairly workable to share a database handle w/ the sub-proc's
> preparing their own statement handles.  The main problem is that is a
> True Pain (tm) to debug forks via the perl debugger and DBI isn't normally
> the sort of thing you want to debug with printf's :-)

It still can't be done with Oracle (which is what he was trying).  This is the 
nature of their libraries and their network protocol.  It has nothing to do with 
DBI or Perl.

- -- 
Stephen Clouse <[EMAIL PROTECTED]>
Senior Programmer, IQ Coordinator Project Lead
The IQ Group, Inc. 

-BEGIN PGP SIGNATURE-
Version: PGP 6.5.8

iQA/AwUBO5GE/AOGqGs0PadnEQJ1xgCgqxw68F+sLGuH54o5d2y6AusOt6YAnir8
9dzAVqVvUfC6g0m5+C13w1/k
=u/OE
-END PGP SIGNATURE-

Re: Keeping a database connection across a fork

[qmi]

Hi, All:
 I have the almost same problems and I tried to initiate the database
connection in the child. But every time I can only make database connection and
do something only once. If I want to do it again it always shows me the
application error and my server is killed(the program exit). If I re-start the
server then it can make the database connection again but no more. The following
is my code:


 while ($new_sock = $main_sock->accept()) {
$pid = fork();
die "Cannot fork: $!" unless defined($pid);
if ($pid == 0) {
   while (defined ($buf = <$new_sock>)) {
  if ($buf eq 'gettableinfo') {
gettableinfo();
 }
  elsif ($buf eq 'Analysis') {{
  Analysis();
   }
}
 exit(0);
  }
   }
close ($main_sock);

sub gettableinfo() {
my   $dsn = "dbi:ODBC:mysource";
 my $proxy = "hostname=path124-161b4;port=";
  my $dbh = DBI->connect("dbi:Proxy:$proxy;dsn=$dsn", '', '');
 # do something for this database handler
..
}

sub Analysis() {
 my   $dsn = "dbi:ODBC:mysource";
 my $proxy = "hostname=path124-161b4;port=";
  my $dbh = DBI->connect("dbi:Proxy:$proxy;dsn=$dsn", '', '');
 # do something for this database handler
 
}
Every time I disconnect the database handler but still doesn't work more than
twice. Any help will be appreciated!



Qing

Re: parameter unknown: problem with DBD::Pg quote method

[Alex Pilosov]

Quote function is not supposed to quote ? character.

You must do as follows:
> my $dbh = DBI->connect("DBI:Pg:dbname=test", "postgres", "");
> my $val = q!\'?:!
> my $sth = $dbh->prepare("INSERT INTO foo (a) values (?)");
> $sth->execute($val);

(And no explicit calls to quote are necessary)

On Wed, 29 Aug 2001, Alex Krohn wrote:

> Hi,
> 
> I came across this problem that seems to be with DBD::Pg's quote
> function that is easily reproducible. This test script:
> 
> my $dbh = DBI->connect("DBI:Pg:dbname=test", "postgres", "");
> my $val = $dbh->quote(q!\'?:!);
> print "val: $val\n";
> my $sth = $dbh->prepare("INSERT INTO foo (a) values ($val)");
> $sth->execute;
> 
> produces:
> 
> DBD::Pg::st execute failed: parameter unknown at mytest.pl line 6.
> 
> If I use mysql, or Oracle, as a driver, it works as expected. I searched
> through the list, and the only answer seemed to be to use placeholders,
> which isn't really an option in my situation.
> 
> Any ideas on workarounds, or a patch to DBD::Pg would be great. 
> 
> Please cc me any replies as I'm not on the list.
> 
> Cheers,
> 
> Alex
> 
> 

Re: parameter unknown: problem with DBD::Pg quote method

[Alex Krohn]

Hi,

> > I came across this problem that seems to be with DBD::Pg's quote
> > function that is easily reproducible. This test script:
> > 
> > my $dbh = DBI->connect("DBI:Pg:dbname=test", "postgres", "");
> > my $val = $dbh->quote(q!\'?:!);
> > print "val: $val\n";
> > my $sth = $dbh->prepare("INSERT INTO foo (a) values ($val)");
> > $sth->execute;
> > 
> > produces:
> > 
> > DBD::Pg::st execute failed: parameter unknown at mytest.pl line 6.
> > 
> > If I use mysql, or Oracle, as a driver, it works as expected. I searched
> > through the list, and the only answer seemed to be to use placeholders,
> > which isn't really an option in my situation.
>
> Quote function is not supposed to quote ? character.
> 
> You must do as follows:
> > my $dbh = DBI->connect("DBI:Pg:dbname=test", "postgres", "");
> > my $val = q!\'?:!
> > my $sth = $dbh->prepare("INSERT INTO foo (a) values (?)");
> > $sth->execute($val);
> 
> (And no explicit calls to quote are necessary)

Placeholders isn't really an option in my case.

I realize quote should not quote the ? character, but the driver should
understand that question marks inside of quotes is not to be used as
placeholders, but is rather a question mark. i.e. the following works
of course:

my $sth = $dbh->prepare ('INSERT INTO foo (a) VALUES ("hello?")');

as the driver realizes that ? is not a placeholder. It's the strange
mix above that causes a problem (oddly, if I remove the :, it works
fine). 

Does anyone more familiar with the DBD::Pg internals know what the
sequens is to look for that will catch this? If I remove the : or the \
from the insert above, it works fine. 

Cheers,

Alex