[SQL] ERROR: copyObject: don't know how to copy 611

[Shane McEneaney]

I get the error ERROR:  copyObject: don't know how to copy 611
when I try to run the following procedure. The create table is causing
the problem.


CREATE FUNCTION "my_func" (int8) RETURNS int8 AS '
 declare
   v_my_var ALIAS FOR $1;
 begin
   create table my_table(my_id int);
 end;
' LANGUAGE 'plpgsql';


Can anybody explain this?

Thanks in advance,

shane

[SQL] removing a DB??

[Bruno Boettcher]

hello,

i have a nice command to create a db 'createdb', but hows when you want
to get rid of a DB??  searched for a removedb script
Took me some time to read through the
man page, find out how the command actually works, look up the SQL
commands and find out that there's also a DROP DATABASE SQL-command...

And then looking up that there'S really a dropdb command

would be really nice if in the man page there was a short ref, telling
me about related commands.

Then the question arouse, if i can create and destroy databases from
SQL, building my install script, it would be nice to test for the
presence of a DB and in case of inexistence, create it on the fly.

For this it would be nice if i could have found for each possible command
in psql the SQL counterpart, notably, how to list all available DB's
through SQL.

BTW shouldn't \l display also the internal DB's ? If it would i could
have searched for the right table

-- 
ciao bboett
==
[EMAIL PROTECTED]
http://inforezo.u-strasbg.fr/~bboett http://erm1.u-strasbg.fr/~bboett
===
the total amount of intelligence on earth is constant.
human population is growing

Re: [SQL] removing a DB??

[Jens Hartwig]

Hello Bruno,

you get a list of all available databases by issuing a query on
"pg_database":

  select * from pg_database;

What do you mean with "internal DB's"?

Regards, Jens

Bruno Boettcher schrieb:
> 
> hello,
> 
> i have a nice command to create a db 'createdb', but hows when you want
> to get rid of a DB??  searched for a removedb script
> Took me some time to read through the
> man page, find out how the command actually works, look up the SQL
> commands and find out that there's also a DROP DATABASE SQL-command...
> 
> And then looking up that there'S really a dropdb command
> 
> would be really nice if in the man page there was a short ref, telling
> me about related commands.
> 
> Then the question arouse, if i can create and destroy databases from
> SQL, building my install script, it would be nice to test for the
> presence of a DB and in case of inexistence, create it on the fly.
> 
> For this it would be nice if i could have found for each possible command
> in psql the SQL counterpart, notably, how to list all available DB's
> through SQL.
> 
> BTW shouldn't \l display also the internal DB's ? If it would i could
> have searched for the right table
> 
> --
> ciao bboett
> ==
> [EMAIL PROTECTED]
> http://inforezo.u-strasbg.fr/~bboett http://erm1.u-strasbg.fr/~bboett
> ===
> the total amount of intelligence on earth is constant.
> human population is growing

-- 
Herzliche Grüße, Jens Hartwig

=
Jens Hartwig
-
debis Systemhaus GEI mbH
10875 Berlin
Tel. : +49 (0)30 2554-3282
Fax  : +49 (0)30 2554-3187
Mobil: +49 (0)170 167-2648
E-Mail   : [EMAIL PROTECTED]
=

[SQL] Bad performing DELETE

[Hans-Jürgen Schönig]

>

I have two tables: t_haus is about 1400 row and t_host has 364000 entries.
Both tables are indexed on edvnr. I did a vacuum on my db and all indices
are rebuild.
I want to delete all Entries in t_haus where a row can be found in t_host.
When using "delete from t_haus where t_haus.edvnr=t_host.edvnr; " the
database performs extremely bad.

 explain delete from t_haus where t_haus.edvnr=t_host.edvnr;

NOTICE:  QUERY PLAN:

Merge Join  (cost=52178.53..56754.63 rows=6299767 width=14)
  ->  Sort  (cost=52038.25..52038.25 rows=364359 width=4)
->  Seq Scan on t_host  (cost=0.00..11700.59 rows=364359 width=4)
  ->  Sort  (cost=140.27..140.27 rows=1729 width=10)
->  Seq Scan on t_haus  (cost=0.00..47.29 rows=1729 width=10)

EXPLAIN

What can I do to speed it up?
I've also had troubles with DELETE before (when doing joins in the
WHERE clause).

Hans

Re: [SQL] ERROR: copyObject: don't know how to copy 611

[Tom Lane]

"Shane McEneaney" <[EMAIL PROTECTED]> writes:
> I get the error ERROR:  copyObject: don't know how to copy 611
> when I try to run the following procedure. The create table is causing
> the problem.

> CREATE FUNCTION "my_func" (int8) RETURNS int8 AS '
>  declare
>v_my_var ALIAS FOR $1;
>  begin
>create table my_table(my_id int);
>  end;
> ' LANGUAGE 'plpgsql';

> Can anybody explain this?

CREATE TABLE, like most other utility statements, isn't supported by
plpgsql at present.  This is fixed for 7.1 to the extent that you can
do examples like the above, but you still can't pass parameters to
the things --- for example CREATE TABLE $1 (foo int) won't work.

For these sorts of things you might want to consider pltcl or plperl,
which don't attempt to do any pre-parsing of SQL statements.  A little
slower, but much more flexible...

regards, tom lane

Re: [SQL] Bad performing DELETE

[Tom Lane]

=?iso-8859-1?Q?Hans=2DJ=FCrgen=20Sch=F6nig?= <[EMAIL PROTECTED]> writes:
> I have two tables: t_haus is about 1400 row and t_host has 364000 entries.
> Both tables are indexed on edvnr. I did a vacuum on my db and all indices
> are rebuild.
> I want to delete all Entries in t_haus where a row can be found in t_host.
> When using "delete from t_haus where t_haus.edvnr=t_host.edvnr; " the
> database performs extremely bad.

>  explain delete from t_haus where t_haus.edvnr=t_host.edvnr;
> NOTICE:  QUERY PLAN:
> Merge Join  (cost=52178.53..56754.63 rows=6299767 width=14)
>   -> Sort  (cost=52038.25..52038.25 rows=364359 width=4)
>   -> Seq Scan on t_host  (cost=0.00..11700.59 rows=364359 width=4)
>   -> Sort  (cost=140.27..140.27 rows=1729 width=10)
>   -> Seq Scan on t_haus  (cost=0.00..47.29 rows=1729 width=10)

I wonder if a hash join would be faster.  What does EXPLAIN show if
you first do "set enable_mergejoin to off"?  What's the actual
performance in both cases?

Also, it's possible that the performance problem isn't the fault of the
plan at all.  Are there multiple rows in t_host matching the deletable
rows of t_haus?  I'm wondering if there's some speed penalty associated
with trying to delete the same row multiple times in one command...

regards, tom lane

[SQL] count( distinct x )

[Anthony]

Apologies if this has been asked b4, but got this result when
attemplting to search the archives on the website

Not Found

The requested URL /mhonarc/pgsql-sql/search.cgi was not found on this
server.


Apache/1.3.12 Server at postgresql.rmplc.co.uk Port 80

The problem I have is with this statement:

select count( distinct area ) from areapostcode where postcode like
'BS1%'

the above statement fails with
ERROR:  parser: parse error at or near "distinct"

I am not the greatest when it comes to SQL, but the pgsql docs implied
that the above would work.

What I am trying to do is get a count of the no of matches from the
statement below
select distinct area from areapostcode where postcode like 'BS1%'

Not the count of:
select area from areapostcode where postcode like 'BS1%'

Can anyone help?

TIA
Bap.

Re: [SQL] count( distinct x )

[Jose Rodrigo Fernandez Menegazzo]

> The problem I have is with this statement:
>
> select count( distinct area ) from areapostcode where postcode like
> 'BS1%'
>
> the above statement fails with
> ERROR:  parser: parse error at or near "distinct"
>
> I am not the greatest when it comes to SQL, but the pgsql docs implied
> that the above would work.
>
> What I am trying to do is get a count of the no of matches from the
> statement below
> select distinct area from areapostcode where postcode like 'BS1%'
>
> Not the count of:
> select area from areapostcode where postcode like 'BS1%'
>

I don't have where to try it, but have you tried:

select distinct count(area) from areapostcode where postcode like 'BS1%'

Rodrigo F.

Re: [SQL] count( distinct x )

[Anthony]

Jose Rodrigo Fernandez Menegazzo wrote:

> > The problem I have is with this statement:
> >
> > select count( distinct area ) from areapostcode where postcode like
> > 'BS1%'
> >
> > the above statement fails with
> > ERROR:  parser: parse error at or near "distinct"
> >
> > I am not the greatest when it comes to SQL, but the pgsql docs implied
> > that the above would work.
> >
> > What I am trying to do is get a count of the no of matches from the
> > statement below
> > select distinct area from areapostcode where postcode like 'BS1%'
> >
> > Not the count of:
> > select area from areapostcode where postcode like 'BS1%'
> >
>
> I don't have where to try it, but have you tried:
>
> select distinct count(area) from areapostcode where postcode like 'BS1%'
>
> Rodrigo F.

yes, it responds as if distinct is not in the query string.

Thanks,
Bap.

Re: [SQL] count( distinct x )

[Michael Fork]

I think you want

SELECT count(distinct(area)) FROM areapostcode WHERE postcode LIKE 'BS1%'

Michael Fork - CCNA - MCP - A+
Network Support - Toledo Internet Access - Toledo Ohio

On Mon, 27 Nov 2000, Anthony wrote:

> Apologies if this has been asked b4, but got this result when
> attemplting to search the archives on the website
> 
> Not Found
> 
> The requested URL /mhonarc/pgsql-sql/search.cgi was not found on this
> server.
> 
> 
> Apache/1.3.12 Server at postgresql.rmplc.co.uk Port 80
> 
> The problem I have is with this statement:
> 
> select count( distinct area ) from areapostcode where postcode like
> 'BS1%'
> 
> the above statement fails with
> ERROR:  parser: parse error at or near "distinct"
> 
> I am not the greatest when it comes to SQL, but the pgsql docs implied
> that the above would work.
> 
> What I am trying to do is get a count of the no of matches from the
> statement below
> select distinct area from areapostcode where postcode like 'BS1%'
> 
> Not the count of:
> select area from areapostcode where postcode like 'BS1%'
> 
> Can anyone help?
> 
> TIA
> Bap.
> 

Re: [SQL] count( distinct x )

[Anthony]

Michael Fork wrote:

> I think you want
>
> SELECT count(distinct(area)) FROM areapostcode WHERE postcode LIKE 'BS1%'
>

psql still not happy :(

SELECT count(distinct(area)) FROM areapostcode WHERE postcode LIKE 'BS1%';
ERROR:  parser: parse error at or near "distinct"

Thanks,
Bap.

>
> Michael Fork - CCNA - MCP - A+
> Network Support - Toledo Internet Access - Toledo Ohio
>
> On Mon, 27 Nov 2000, Anthony wrote:
>
> > Apologies if this has been asked b4, but got this result when
> > attemplting to search the archives on the website
> >
> > Not Found
> >
> > The requested URL /mhonarc/pgsql-sql/search.cgi was not found on this
> > server.
> >
> >
> > Apache/1.3.12 Server at postgresql.rmplc.co.uk Port 80
> >
> > The problem I have is with this statement:
> >
> > select count( distinct area ) from areapostcode where postcode like
> > 'BS1%'
> >
> > the above statement fails with
> > ERROR:  parser: parse error at or near "distinct"
> >
> > I am not the greatest when it comes to SQL, but the pgsql docs implied
> > that the above would work.
> >
> > What I am trying to do is get a count of the no of matches from the
> > statement below
> > select distinct area from areapostcode where postcode like 'BS1%'
> >
> > Not the count of:
> > select area from areapostcode where postcode like 'BS1%'
> >
> > Can anyone help?
> >
> > TIA
> > Bap.
> >

Re: [SQL] count( distinct x )

[Anthony]

Kenn Thompson wrote:

> What about
>
> select count(*) from (select distinct area from areapostcode where postcode like 
>'BS1%')
>

select count(*) from (select distinct area from areapostcode where
postcode like 'BS1%');
ERROR:  parser: parse error at or near "select"

Thanks, any more ideas?

>
> >>> Anthony <[EMAIL PROTECTED]> 11/27/00 12:24PM >>>
> Jose Rodrigo Fernandez Menegazzo wrote:
>
> > > The problem I have is with this statement:
> > >
> > > select count( distinct area ) from areapostcode where postcode like
> > > 'BS1%'
> > >
> > > the above statement fails with
> > > ERROR:  parser: parse error at or near "distinct"
> > >
> > > I am not the greatest when it comes to SQL, but the pgsql docs implied
> > > that the above would work.
> > >
> > > What I am trying to do is get a count of the no of matches from the
> > > statement below
> > > select distinct area from areapostcode where postcode like 'BS1%'
> > >
> > > Not the count of:
> > > select area from areapostcode where postcode like 'BS1%'
> > >
> >
> > I don't have where to try it, but have you tried:
> >
> > select distinct count(area) from areapostcode where postcode like 'BS1%'
> >
> > Rodrigo F.
>
> yes, it responds as if distinct is not in the query string.
>
> Thanks,
> Bap.

Re: [SQL] count( distinct x )

[Tom Lane]

Anthony <[EMAIL PROTECTED]> writes:
> select count( distinct area ) from areapostcode where postcode like
> 'BS1%'
> the above statement fails with
> ERROR:  parser: parse error at or near "distinct"

What Postgres version are you running?  Support for count(distinct foo)
was added in 7.0, IIRC.

regards, tom lane

Re: [SQL] count( distinct x )

[Anthony]

Tom Lane wrote:

> Anthony <[EMAIL PROTECTED]> writes:
> > select count( distinct area ) from areapostcode where postcode like
> > 'BS1%'
> > the above statement fails with
> > ERROR:  parser: parse error at or near "distinct"
>
> What Postgres version are you running?  Support for count(distinct foo)
> was added in 7.0, IIRC.
>
> regards, tom lane

select version();
version
---
PostgreSQL 6.5.3 on i686-pc-linux-gnu, compiled by gcc egcs-2.91.66
(1 row)

bugger!

any ideas how to get same result at relative speed with different query?

Thanks Tom.

Re: [SQL] count( distinct x )

[Anthony]

Najm Hashmi wrote:

> Anthony wrote:
>
> > Michael Fork wrote:
> >
> > > I think you want
> > >
> > > SELECT count(distinct(area)) FROM areapostcode WHERE postcode LIKE 'BS1%'
> > >
> >
> > psql still not happy :(
> >
> > SELECT count(distinct(area)) FROM areapostcode WHERE postcode LIKE 'BS1%';
> > ERROR:  parser: parse error at or near "distinct"
> >
> > Thanks,
> > Bap.
> >
> > >
> > > Michael Fork - CCNA - MCP - A+
> > > Network Support - Toledo Internet Access - Toledo Ohio
> > >
> > > On Mon, 27 Nov 2000, Anthony wrote:
> > >
> > > > Apologies if this has been asked b4, but got this result when
> > > > attemplting to search the archives on the website
> > > >
> > > > Not Found
> > > >
> > > > The requested URL /mhonarc/pgsql-sql/search.cgi was not found on this
> > > > server.
> > > >
> > > >
> > > > Apache/1.3.12 Server at postgresql.rmplc.co.uk Port 80
> > > >
> > > > The problem I have is with this statement:
> > > >
> > > > select count( distinct area ) from areapostcode where postcode like
> > > > 'BS1%'
> > > >
> > > > the above statement fails with
> > > > ERROR:  parser: parse error at or near "distinct"
> > > >
> > > > I am not the greatest when it comes to SQL, but the pgsql docs implied
> > > > that the above would work.
> > > >
> > > > What I am trying to do is get a count of the no of matches from the
> > > > statement below
> > > > select distinct area from areapostcode where postcode like 'BS1%'
> > > >
> > > > Not the count of:
> > > > select area from areapostcode where postcode like 'BS1%'
> > > >
> > > > Can anyone help?
> > > >
> > > > TIA
> > > > Bap.
> > > >
>
> Hi,
> I think this might work: It works on my machine, and I have postgres 7.xx
>  SELECT distinct(count(area)) FROM areapostcode WHERE postcode LIKE 'BS1%'
>
> Regrads
> Najm

no, this one succedes, but returns the count of
select area from areapostcode where postcode like 'BS1%'
not the count of
select distinct area from areapostcode where postcode like 'BS1%'

but I have just replied to this list with the following

select version();
version
---
PostgreSQL 6.5.3 on i686-pc-linux-gnu, compiled by gcc egcs-2.91.66
(1 row)

Re: [SQL] removing a DB??

[Mathijs Brands]

On Mon, Nov 27, 2000 at 12:34:38PM +0100, Jens Hartwig allegedly wrote:
> Hello Bruno,
> 
> you get a list of all available databases by issuing a query on
> "pg_database":
> 
>   select * from pg_database;
> 
> What do you mean with "internal DB's"?
> 
> Regards, Jens

You can also get a list of databases by executing psql -l from the
commandline.

My 0.02 euro,

Mathijs
-- 
"It is a great thing to start life with a small number of really good books
 which are your very own". 
Sir Arthur Conan Doyle  (1859-1930)

Re: [SQL] count( distinct x )

[Anthony]

Kenn Thompson wrote:

> Ok- messy, but it works
>
> CREATE VIEW testview AS
> select distinct area from areapostcode where postcode like 'BS1%';
>
> SELECT COUNT(*) FROM testview;
>
> kenn
>
> >>> Anthony <[EMAIL PROTECTED]> 11/27/00 01:07PM >>>
> Kenn Thompson wrote:
>
> > What about
> >
> > select count(*) from (select distinct area from areapostcode where postcode like 
>'BS1%')
> >
>
> select count(*) from (select distinct area from areapostcode where
> postcode like 'BS1%');
> ERROR:  parser: parse error at or near "select"
>
> Thanks, any more ideas?
>
> >
> > >>> Anthony <[EMAIL PROTECTED]> 11/27/00 12:24PM >>>
> > Jose Rodrigo Fernandez Menegazzo wrote:
> >
> > > > The problem I have is with this statement:
> > > >
> > > > select count( distinct area ) from areapostcode where postcode like
> > > > 'BS1%'
> > > >
> > > > the above statement fails with
> > > > ERROR:  parser: parse error at or near "distinct"
> > > >
> > > > I am not the greatest when it comes to SQL, but the pgsql docs implied
> > > > that the above would work.
> > > >
> > > > What I am trying to do is get a count of the no of matches from the
> > > > statement below
> > > > select distinct area from areapostcode where postcode like 'BS1%'
> > > >
> > > > Not the count of:
> > > > select area from areapostcode where postcode like 'BS1%'
> > > >
> > >
> > > I don't have where to try it, but have you tried:
> > >
> > > select distinct count(area) from areapostcode where postcode like 'BS1%'
> > >
> > > Rodrigo F.
> >
> > yes, it responds as if distinct is not in the query string.
> >
> > Thanks,
> > Bap.

CREATE VIEW testview AS
-> select distinct area from areapostcode where postcode like 'BS1%';
ERROR:  DISTINCT not supported in views

I think it's time to get Mr. Sysadmin to upgrade to v7 ;)

Thanks all, if anyone has an ideas of how to get this working on 6.5.3, then please 
help.
I'll check back in the morning, and try any suggestions, if no joy will try to get 
PostgreSQL
upgraded.

Thanks all.
Bap.

Re: [SQL] a script that queries database periodically

[Serge Canizares]

Why not use perl with  DBI?  If you know php, perl is just as easy.

Bernie Huang wrote:

> I was thinking of writing up a PHP script and put into crontab, which is
> somehow easier than a shell script, but PHP is an apache module, so I
> cannot execute PHP under crontab (it has to be executed in a browser
> right?).
>
> Thanks.
>
> - Bernie

Re: [SQL] count( distinct x )

[Tom Lane]

Anthony <[EMAIL PROTECTED]> writes:
> I think it's time to get Mr. Sysadmin to upgrade to v7 ;)

That's a good idea on many grounds, not only this one ;-)

However, if you really need a 6.5.* solution, you could do

SELECT DISTINCT foo INTO TEMP TABLE mytemp FROM ...
SELECT COUNT(*) FROM mytemp;
DROP TABLE mytemp;

regards, tom lane

Re: Re(2): Re(2): [SQL] 7.0.3 BUG

[Alain Toussaint]

> >That's a rather interesting version report, seeing as how there is
> >no such gcc release as 2.95.3 according to the GCC project's homepage.

quick tidbit,there's no gcc 2.95.3 from GCC's but there's is one from
Pentium gcc (see  ),the pentium gcc group in this
case used gcc 2.95.2,applied their pentium patches and released the thing
as pgcc 2.95.3,that's the stock compiled used by mandrake.

Alain Toussaint

[SQL] Damaged table "pg_access"

[Jens Hartwig]

Hello all,

when I try to access the system-table "pg_class", I get the following
error:

zeda=# select * from pg_class;
NOTICE:  get_groname: group 1 not found
pqReadData() -- backend closed the channel unexpectedly.
This probably means the backend terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!#

What happened? Is there any way to repare the damaged table?

Thanks in advance for all of you, who spend their valuable time!

Best regards, Jens

=
Jens Hartwig
-
debis Systemhaus GEI mbH
10875 Berlin
Tel. : +49 (0)30 2554-3282
Fax  : +49 (0)30 2554-3187
Mobil: +49 (0)170 167-2648
E-Mail   : [EMAIL PROTECTED]
=

Re: [SQL] a script that queries database periodically

[Poul L. Christiansen]

I have a bash script in crontab with the following line:
"lynx -dump 
http://127.0.0.1/postgres/myPhpFile.php>${currentDir}php_output.html"

You can also just put that line directly into crontab.

Poul L. Christiansen

Bernie Huang wrote:

> Hello,
> 
> My boss wants me to write a script that will send a email report to him
> monthly with the result of database queries.
> 
> For example, an email would be like:
> 
> Monthly report
> +--+-+---+
> | Vehicles | Mileage | # of Signouts |
> +--+-+---+
> | Vehicle1 | 10324   | 5 |
> +--+-+---+
> | Vehicle2 | 19384   | 6 |
> +--+-+---+
> ...
> 
> I was thinking of writing up a PHP script and put into crontab, which is
> somehow easier than a shell script, but PHP is an apache module, so I
> cannot execute PHP under crontab (it has to be executed in a browser
> right?).  I guess a shell script is necessary.  So, is it possible to
> call 'psql' and returning its query result and I can use sendmail to
> email the result? Any other idea?
> 
> Thanks.
> 
> - Bernie
> 

Re: [SQL] a script that queries database periodically

[Roberto Mello]

"Poul L. Christiansen" wrote:

> > I was thinking of writing up a PHP script and put into crontab, which is
> > somehow easier than a shell script, but PHP is an apache module, so I
> > cannot execute PHP under crontab (it has to be executed in a browser
> > right?).  I guess a shell script is necessary.  So, is it possible to
> > call 'psql' and returning its query result and I can use sendmail to
> > email the result? Any other idea?

PHP does not have a scheduling facility? AOLserver (the web/application
server that powers AOL) has had such facility (and many many others for
db-backed websites) since 1995. ns_schedule_proc.
http://www.aolserver.com

-Roberto
-- 
Computer ScienceUtah State University
Space Dynamics Laboratory   Web Developer
USU Free Software & GNU/Linux Club  http://fslc.usu.edu
My home page - http://www.brasileiro.net/roberto

Re: [SQL] a script that queries database periodically

[Mathijs Brands]

On Mon, Nov 27, 2000 at 10:44:39PM +0100, Poul L. Christiansen allegedly wrote:
> I have a bash script in crontab with the following line:
> "lynx -dump 
> http://127.0.0.1/postgres/myPhpFile.php>${currentDir}php_output.html"
> 
> You can also just put that line directly into crontab.
> 
> Poul L. Christiansen

Be sure to redirect the output of the command in your crontab to /dev/null
or something similar. If you don't, the output (HTML) will be emailed to
you each time the script is run. Whether this side effect is desirable is
of course up to you (or your boss, for instance).

Getting even more off-topic,

Mathijs
-- 
"Where is human nature so weak as in a bookstore!" 
Henry Ward Beecher  (1813-1887) 

Re: [PHP] Re: [PHP-DB] Re: [SQL] a script that queries database periodically

[Joe Stump]

>   Because PHP is supposed to solve web development problems. And this is
> one of them. It's very useful.

Why solve one that is already solved? PHP isn't here to reinvent the wheel - 
get crontab and quit crying.

--Joe


> 
>   -Roberto
> -- 
> Computer Science  Utah State University
> Space Dynamics Laboratory Web Developer
> USU Free Software & GNU/Linux Clubhttp://fslc.usu.edu
> My home page - http://www.brasileiro.net/roberto
> 
> -- 
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
> To contact the list administrators, e-mail: [EMAIL PROTECTED]

= Joe Stump[EMAIL PROTECTED] http://www.miester.org =

  Need a programmer? http://www.google.com/search?q=joe+stump+resume

Re: [SQL] a script that queries database periodically

[Brett W. McCoy]

On Mon, 27 Nov 2000, Bernie Huang wrote:

> I was thinking of writing up a PHP script and put into crontab, which is
> somehow easier than a shell script, but PHP is an apache module, so I
> cannot execute PHP under crontab (it has to be executed in a browser
> right?).  I guess a shell script is necessary.  So, is it possible to
> call 'psql' and returning its query result and I can use sendmail to
> email the result? Any other idea?

Is there any reason to not use Perl & DBI or the Pg.pm module?

Brett W. McCoy
 http://www.chapelperilous.net/~bmccoy/
---
Exhilaration is that feeling you get just after a great idea hits you,
and just before you realize what is wrong with it.

Re: [PHP-DB] Re: [SQL] a script that queries database periodically

[Roberto Mello]

Jason wrote:
> 
> aolserver is a web/application server.  PHP is a server-side scripting
> language.  Why exactly *should* it have a job scheduler?
> 
> Some (such as myself) might also ask why should a web server have a job
> scheduler, but that's a thread for a different list :)

Because PHP is supposed to solve web development problems. And this is
one of them. It's very useful.

-Roberto
-- 
Computer ScienceUtah State University
Space Dynamics Laboratory   Web Developer
USU Free Software & GNU/Linux Club  http://fslc.usu.edu
My home page - http://www.brasileiro.net/roberto

[SQL] a script that queries database periodically

[Bernie Huang]

Hello,

My boss wants me to write a script that will send a email report to him
monthly with the result of database queries.

For example, an email would be like:

Monthly report
+--+-+---+
| Vehicles | Mileage | # of Signouts |
+--+-+---+
| Vehicle1 | 10324   | 5 |
+--+-+---+
| Vehicle2 | 19384   | 6 |
+--+-+---+
...

I was thinking of writing up a PHP script and put into crontab, which is
somehow easier than a shell script, but PHP is an apache module, so I
cannot execute PHP under crontab (it has to be executed in a browser
right?).  I guess a shell script is necessary.  So, is it possible to
call 'psql' and returning its query result and I can use sendmail to
email the result? Any other idea?

Thanks.

- Bernie


begin:vcard 
n:Huang;Bernie
tel;fax:(604)664-9195
tel;work:(604)664-9172
x-mozilla-html:TRUE
org:Environment Canada;Standards and Technology Services
adr:;;700-1200 West 73 Ave.;Vancouver;BC;V6P 6H9;Canada
version:2.1
email;internet:[EMAIL PROTECTED]
title:Programmer
x-mozilla-cpt:;0
fn:Bernie Huang
end:vcard

Re: [SQL] a script that queries database periodically

[Mathijs Brands]

On Mon, Nov 27, 2000 at 11:22:45AM -0800, Bernie Huang allegedly wrote:
> Hello,
> 
> My boss wants me to write a script that will send a email report to him
> monthly with the result of database queries.
> 
> For example, an email would be like:
> 
> Monthly report
> +--+-+---+
> | Vehicles | Mileage | # of Signouts |
> +--+-+---+
> | Vehicle1 | 10324   | 5 |
> +--+-+---+
> | Vehicle2 | 19384   | 6 |
> +--+-+---+
> ...
> 
> I was thinking of writing up a PHP script and put into crontab, which is
> somehow easier than a shell script, but PHP is an apache module, so I
> cannot execute PHP under crontab (it has to be executed in a browser
> right?).  I guess a shell script is necessary.  So, is it possible to
> call 'psql' and returning its query result and I can use sendmail to
> email the result? Any other idea?
> 
> Thanks.
> 
> - Bernie

Sure you can. You can configure PHP to run seperately from any webserver.
You would then be able to write PHP scripts that can be used just like any
other UNIX scripts, for instance from a crontab.

I don't know if you've configured and built PHP before, but it is actually
not that difficult. However, be sure to look at the INSTALL file that comes
with PHP. What it comes down to is probably the following:

1 - extract PHP source
2 - change to the PHP source directory and execute the following configure
command:
  ./configure --prefix=/usr/local/standalone-php --without-mysql --with-pgsql
3 - build the source by giving the make command
4 - do a 'make install' after PHP has been succesfully build

A bit off-topic, but I hope this is helpful.

Cheers,

Mathijs

Ps. I just thought of something else. You could write a PHP script that runs
under apache (like your're used to) and request it with wget, lynx or a
similar tool from a crontab. That way you wouldn't need to configure a new
PHP installation.
-- 
"A book is a fragile creature.  It suffers the wear of time,
 it fears rodents, the elements, clumsy hands." 
Umberto Eco