Database connectivity using a unix shell
Hi
I need to connect to the postgres database using my unix shell.
How should i go about it?
Is libpq going to be helpful here?
Thanks and regards,
~Jas
I need to connect to the postgres database using my unix shell.
How should i go about it?
Is libpq going to be helpful here?
If postgres is install on the unix server, you can use the postgreSQL client that installs with
the server.
it is called psql.
Regards,
Richard Broersma Jr.
On Thu, 2006-06-29 at 10:24, Jasbinder Bali wrote:
Hi
I need to connect to the postgres database using my unix shell.
How should i go about it?
Is libpq going to be helpful here?
There is a shell interface for postgresql called psql.
If you have postgresql installed on a box, then the psql interface
should be there.
If you need to access postgresql from within a bash script, you can do
so with a construct kinda like this:
query="select * from sometable";
a=`echo $query|psql -tq dbname`;
do a psql --help to get a list of options for psql. -t means print only
the rows, no headers, and -q means to only display query output, no
messages or warnings.
in my bash script where and how do i specify the database connection
parameters
~Jas
Show quoted text
On 6/29/06, Scott Marlowe <smarlowe@g2switchworks.com> wrote:
On Thu, 2006-06-29 at 10:24, Jasbinder Bali wrote:
Hi
I need to connect to the postgres database using my unix shell.
How should i go about it?
Is libpq going to be helpful here?There is a shell interface for postgresql called psql.
If you have postgresql installed on a box, then the psql interface
should be there.If you need to access postgresql from within a bash script, you can do
so with a construct kinda like this:query="select * from sometable";
a=`echo $query|psql -tq dbname`;do a psql --help to get a list of options for psql. -t means print only
the rows, no headers, and -q means to only display query output, no
messages or warnings.
On Thursday 29 June 2006 09:08, "Jasbinder Bali" <jsbali@gmail.com> wrote:
in my bash script where and how do i specify the database connection
parameters
man psql
--
Alan
On Thu, 2006-06-29 at 11:08, Jasbinder Bali wrote:
On 6/29/06, Scott Marlowe <smarlowe@g2switchworks.com> wrote:
On Thu, 2006-06-29 at 10:24, Jasbinder Bali wrote:Hi
I need to connect to the postgres database using my unix shell.
How should i go about it?
Is libpq going to be helpful here?There is a shell interface for postgresql called psql.
If you have postgresql installed on a box, then the psql interface
should be there.If you need to access postgresql from within a bash script, you can
do So with a construct kinda like this:query="select * from sometable";
a=`echo $query|psql -tq dbname`;
in my bash script where and how do i specify the database connection
parameters
In psql you can use -U to specify your username. for password info, you
need to use a .pgpass file (i.e. no passwords on the command line).
http://www.postgresql.org/docs/8.1/interactive/libpq-pgpass.html
isn't my normal bash script different from psql.
In a bash script how wud u specify the db parameters
Show quoted text
On 6/29/06, Scott Marlowe <smarlowe@g2switchworks.com> wrote:
On Thu, 2006-06-29 at 11:08, Jasbinder Bali wrote:
On 6/29/06, Scott Marlowe <smarlowe@g2switchworks.com> wrote:
On Thu, 2006-06-29 at 10:24, Jasbinder Bali wrote:Hi
I need to connect to the postgres database using my unix shell.
How should i go about it?
Is libpq going to be helpful here?There is a shell interface for postgresql called psql.
If you have postgresql installed on a box, then the psql interface
should be there.If you need to access postgresql from within a bash script, you can
do So with a construct kinda like this:query="select * from sometable";
a=`echo $query|psql -tq dbname`;in my bash script where and how do i specify the database connection
parametersIn psql you can use -U to specify your username. for password info, you
need to use a .pgpass file (i.e. no passwords on the command line).http://www.postgresql.org/docs/8.1/interactive/libpq-pgpass.html
On Thu, 2006-06-29 at 11:29, Jasbinder Bali wrote:
isn't my normal bash script different from psql.
In a bash script how wud u specify the db parameters
Look at how I'm doing it here:
query="select * from sometable";
a=`echo $query|psql -tq dbname`;
Note that I'm calling psql from within a bash script. So, the connection
params are the same as for psql, cause that's what I'm using.
isn't my normal bash script different from psql.
In a bash script how wud u specify the db parameters
This link might have something that you would like?
http://www.psn.co.jp/PostgreSQL/pgbash/index-e.html
Regards,
Richard Broersma Jr.
jsbali@gmail.com ("Jasbinder Bali") writes:
in my bash script where and how do i specify the database connection
parameters
There are three ways:
1. You can store them in environment variables:
PGDATABASE=my_database
PGPORT=8901
PGUSER=superman
PGHOST=some.host.somewhere
export PGDATABASE PGPORT PGUSER PGHOST
2. You can specify them in command line parameters:
psql -h some.host.somewhere -p 8901 -U superman -d my_database
3. You could specify a service name
PGSERVICE=my_fave_db psql
where the pg_service.conf in your PostgreSQL installation contains an
entry:
[my_fave_db]
dbname=my_database
user=superman
host=some.host.somewhere
port=8901
4. You can mix these; perhaps start by having PGSERVICE provide part
of the information, then environment variables may add to/override
some of that, then command line parameters may add to/override
further...
For a lot of our production environments, we have set up a big,
comprehensive pg_service.conf file; local accounts then pick favorite
values for PGSERVICE...
--
let name="cbbrowne" and tld="acm.org" in String.concat "@" [name;tld];;
http://cbbrowne.com/info/lsf.html
"Why use Windows, since there is a door?"
-- <fachat@galileo.rhein-neckar.de> Andre Fachat
This is a variation of the same:
a=`psql -U postgres -h my.host.dom -Atc "select col1 from sometable where col2=6" dbname`
echo $a
Note that the -U and -h can specify the user name and hostname if necessary.
-A gets the data unaligned
-t gets the data without the decorations
-c "query" specifies the query to run
dbname is required if it is not the default.
Also see psql --help
elein
Show quoted text
On Thu, Jun 29, 2006 at 11:40:49AM -0500, Scott Marlowe wrote:
On Thu, 2006-06-29 at 11:29, Jasbinder Bali wrote:
isn't my normal bash script different from psql.
In a bash script how wud u specify the db parametersLook at how I'm doing it here:
query="select * from sometable";
a=`echo $query|psql -tq dbname`;Note that I'm calling psql from within a bash script. So, the connection
params are the same as for psql, cause that's what I'm using.---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
What if I don't have a shell script and instead of that I have a C code and
need to connect to the postgres database.
How do i accomplish this? do i still need to call this psql clinet interface
or there is some other way to do it..
~Jas
Show quoted text
On 6/29/06, Scott Marlowe <smarlowe@g2switchworks.com> wrote:
On Thu, 2006-06-29 at 13:29, Jasbinder Bali wrote:
this is what i've included in my shell script
query="select * from films";
a=`echo $query|psql -tq postgres`;it gives an error:
role root doesn't exist.where do i have to specify the role?
OK, unless this script NEEDS to be run as root, it's a good idea to run
it as an unprivaledged account. Create a new one if you can. If it has
to be run as root, so be it.You use -U, so it'd look like:
a=`echo $query|psql -tqU myname postgres`;
then you need a password in ~/.pgpass following the format I posted in
the previous message, unless your server is running in trust mode, in
which case you don't need to worry about the .pgpass file.On 6/29/06, Scott Marlowe <smarlowe@g2switchworks.com> wrote:
On Thu, 2006-06-29 at 11:29, Jasbinder Bali wrote:isn't my normal bash script different from psql.
In a bash script how wud u specify the db parametersLook at how I'm doing it here:
query="select * from sometable";
a=`echo $query|psql -tq dbname`;Note that I'm calling psql from within a bash script. So, the
connection
params are the same as for psql, cause that's what I'm using.
Import Notes
Reply to msg id not found: 1151606519.13129.24.camel@state.g2switchworks.com
Are you just asking random questions? What do you actually want to do? You've
asked how to access Postres from a shell - now you're using 'C'. Are you going
to work your way through Java, Perl and a host of others.
All of this information is *very* clearly available in the manual at:
http://www.postgresql.org/docs/8.1/interactive/index.html
It is an *excellent* manual. Apart from how to access Postgres in a multitude of
ways it includes a good reference on SQL itself.
Go have a read.
Jasbinder Bali wrote:
Show quoted text
What if I don't have a shell script and instead of that I have a C code
and need to connect to the postgres database.
How do i accomplish this? do i still need to call this psql clinet
interface or there is some other way to do it..~Jas
On 6/29/06, *Scott Marlowe* <smarlowe@g2switchworks.com
<mailto:smarlowe@g2switchworks.com>> wrote:On Thu, 2006-06-29 at 13:29, Jasbinder Bali wrote:
this is what i've included in my shell script
query="select * from films";
a=`echo $query|psql -tq postgres`;it gives an error:
role root doesn't exist.where do i have to specify the role?
OK, unless this script NEEDS to be run as root, it's a good idea to run
it as an unprivaledged account. Create a new one if you can. If it
has
to be run as root, so be it.You use -U, so it'd look like:
a=`echo $query|psql -tqU myname postgres`;
then you need a password in ~/.pgpass following the format I posted in
the previous message, unless your server is running in trust mode, in
which case you don't need to worry about the .pgpass file.On 6/29/06, Scott Marlowe <smarlowe@g2switchworks.com
<mailto:smarlowe@g2switchworks.com>> wrote:
On Thu, 2006-06-29 at 11:29, Jasbinder Bali wrote:
isn't my normal bash script different from psql.
In a bash script how wud u specify the db parametersLook at how I'm doing it here:
query="select * from sometable";
a=`echo $query|psql -tq dbname`;Note that I'm calling psql from within a bash script. So, the
connection
params are the same as for psql, cause that's what I'm using.
What if I don't have a shell script and instead of that I have a C code and
need to connect to the postgres database.
How do i accomplish this? do i still need to call this psql clinet interface
or there is some other way to do it..
In this case you could use ecpg:
http://www.postgresql.org/docs/8.1/interactive/ecpg.html
If you are interested in any other programming languages that can connect please see:
http://www.postgresql.org/download/
notice the section:
"Application Programming Interfaces"
Regards,
Richard Broersma Jr.
Hi,
Before using ECPG, i had a few questions.
Lets say i have a C code and want to do something with my postgres database.
What all header files do i need for ECPG.
Also how and where exactly am i going to write these EXEC SQL statements for
ECPG.
Can I be briefed about the steps to do this.
Regards,
~Jas
Show quoted text
On 6/29/06, Richard Broersma Jr <rabroersma@yahoo.com> wrote:
What if I don't have a shell script and instead of that I have a C code
and
need to connect to the postgres database.
How do i accomplish this? do i still need to call this psql clinetinterface
or there is some other way to do it..
In this case you could use ecpg:
http://www.postgresql.org/docs/8.1/interactive/ecpg.htmlIf you are interested in any other programming languages that can connect
please see:
http://www.postgresql.org/download/
notice the section:
"Application Programming Interfaces"Regards,
Richard Broersma Jr.
On Wednesday 05 July 2006 09:11, Jasbinder Bali wrote:
Hi,
Before using ECPG, i had a few questions.Lets say i have a C code and want to do something with my postgres
database.What all header files do i need for ECPG.
Also how and where exactly am i going to write these EXEC SQL statements
for ECPG.
Can I be briefed about the steps to do this.
http://www.postgresql.org/docs/8.1/static/server-programming.html
Sincerely,
Joshua D. Drake
On Wednesday 05 July 2006 09:11, Jasbinder Bali wrote:
Hi,
Before using ECPG, i had a few questions.
Lets say i have a C code and want to do something with my postgres
database.
What all header files do i need for ECPG.
Also how and where exactly am i going to write these EXEC SQL statements
for ECPG.
Can I be briefed about the steps to do this.http://www.postgresql.org/docs/8.1/static/server-programming.html
Joshua,
The original posting was in regard to client side script/programs that a user would connect to a
database. Doesn't the above link have application to client side connections?
Regards,
Richard Broersma Jr.
On Wednesday 05 July 2006 10:29, Richard Broersma Jr wrote:
On Wednesday 05 July 2006 09:11, Jasbinder Bali wrote:
Hi,
Before using ECPG, i had a few questions.
Lets say i have a C code and want to do something with my postgres
database.
What all header files do i need for ECPG.
Also how and where exactly am i going to write these EXEC SQL
statements for ECPG.
Can I be briefed about the steps to do this.http://www.postgresql.org/docs/8.1/static/server-programming.html
Joshua,
The original posting was in regard to client side script/programs that a
user would connect to a database. Doesn't the above link have application
to client side connections?
Yep, you are right. Here is a better link:
http://www.postgresql.org/docs/8.1/static/client-interfaces.html
Sincerely,
Joshua D. Drake
Regards,
Richard Broersma Jr.
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/