Received: from tidal.ipinc.com (tidal.ipinc.com [199.245.188.85]) by hub.org (8.10.1/8.10.1) with ESMTP id e8MLjfs86559; Fri, 22 Sep 2000 17:45:41 -0400 (EDT) Received: from ipinc.com (jliang.ipinc.com [199.245.188.130]) by tidal.ipinc.com (8.9.3/8.9.3) with ESMTP id OAA77923; Fri, 22 Sep 2000 14:44:54 -0700 (PDT) (envelope-from jliang@ipinc.com) Message-ID: <39CBD205.9A922DDE@ipinc.com> Date: Fri, 22 Sep 2000 14:41:25 -0700 From: Jie Liang Organization: ipinc.com X-Mailer: Mozilla 4.7 [en] (X11; U; FreeBSD 3.4-RELEASE i386) X-Accept-Language: en MIME-Version: 1.0 To: nbrito@cmet.net CC: pgsql-sql-owner@hub.org, pgsql-sql@hub.org, pgsql-general@postgresql.org, pgsql-sql@postgresql.org, pgsql-general@hub.org Subject: Re: how to store a query, that results in a table References: <39CB65A0.9DEF7ABB@cmet.net> Content-Type: multipart/alternative; boundary="------------44B95CD2C5794CFE884E72F4" X-Archive-Number: 200009/185 --------------44B95CD2C5794CFE884E72F4 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Hi, there, If the client machine is a trusted machine in your company, use remote shell 'rsh' can call a script of SQL. If not , you had better use embeded SQL. I don't know how visual basic embed SQL, but I think is same way as other language, in postgres: #db> create user robot with password 'wowwow'; $db> grant all on table1 to robot; In perl , it's very similar like this: use Pg; my $conn; sub connectDb { $conn = Pg::connectdb("dbname=db host=dbserver port=5432 user=robot password=wowwow"); if ( $conn->status != Pg::PGRES_CONNECTION_OK ) { die "Cant open postgres! : " . $conn->errorMessage . "\n"; } return; } #main connectDb(); my $query="select * from table1 where row1=parameter1"; print $query,"\n"; my $res=$conn->exec($query); $res->cmdStatus || die $conn->errorMessage.":$!\n"; my $rows= $res->ntuples; for (my $k = 0 ; $k < $rows; $k++){ my $field1=$res->getvalue($k,0); my $field2=$res->getvalue($k,1); print $field1,"\t",field2,"\n"; } root wrote: > hi. > I have a problem. > I require to store a query sql in postgresql-7.0 like: > example. > select * from table1 where row1(table1)=parameter1 > If i execute this query directly, I don't have problem. > I want to store this query in order to execute from a client program > (visual basic 6.0), but i don't know how to? > I tried to store the query like a function (create function ...), but it > was impossible to find a way in order to obtain a table like result. > Only we could obtain a single row by using the rtype " returns setof > varchar". > I hope a soon answer. > Thank you. > Nelson B. -- Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 jliang@ipinc.com www.ipinc.com --------------44B95CD2C5794CFE884E72F4 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit Hi, there,

If the client machine is a trusted machine in your company, use remote shell 'rsh' can
call a script of SQL.
If not , you had better use embeded SQL.

I don't know how visual basic embed SQL, but I think is same way as other language,
in postgres:
#db> create  user robot with password 'wowwow';
$db> grant all on table1 to robot;

In perl , it's very similar like this:
use Pg;
my $conn;
        sub connectDb {
           $conn = Pg::connectdb("dbname=db host=dbserver port=5432 user=robot password=wowwow");
           if ( $conn->status != Pg::PGRES_CONNECTION_OK ) {
                die "Cant open postgres! : " . $conn->errorMessage . "\n";
                }
           return;
         }
#main
        connectDb();
        my $query="select * from table1 where row1=parameter1";
        print $query,"\n";
        my $res=$conn->exec($query);
        $res->cmdStatus || die $conn->errorMessage.":$!\n";
        my $rows= $res->ntuples;
        for (my $k = 0 ; $k < $rows; $k++){
                my $field1=$res->getvalue($k,0);
                my $field2=$res->getvalue($k,1);
                print $field1,"\t",field2,"\n";
        }
 

root wrote:

hi.
I have a problem.
I require to store a query sql in postgresql-7.0 like:
example.
select * from table1 where row1(table1)=parameter1
If i execute this query directly, I don't have problem.
I want to store this query in order to execute from a client program
(visual basic 6.0), but i don't know how to?
I tried to store the query like a function (create function ...), but it
was impossible to find a way in order to obtain a table like result.
Only we could obtain a single row by using the rtype " returns setof
varchar".
I hope a soon answer.
Thank you.
 Nelson B.
-- 
Jie LIANG

Internet Products Inc.

10350 Science Center Drive
Suite 100, San Diego, CA 92121
Office:(858)320-4873

jliang@ipinc.com
www.ipinc.com
  --------------44B95CD2C5794CFE884E72F4--