Thread: Connection string
Hi,
I'm trying to connect to postgres database in a distributed environment, say from machine X to Machine Y (Machine Y has postgres DB)
How should my connection string look like in a program in Machine X.
EXEC SQL CONNECT TO ------??? (Do i need to specify the IP of DB server?)
I tried a few options but nothing works.
Can someone please write this connection string for me?
Thanks in advance,
~Harpreet
I'm trying to connect to postgres database in a distributed environment, say from machine X to Machine Y (Machine Y has postgres DB)
How should my connection string look like in a program in Machine X.
EXEC SQL CONNECT TO ------??? (Do i need to specify the IP of DB server?)
I tried a few options but nothing works.
Can someone please write this connection string for me?
Thanks in advance,
~Harpreet
On Wed, Aug 09, 2006 at 11:02:00AM -0400, Harpreet Dhaliwal wrote: > I'm trying to connect to postgres database in a distributed environment, > say from machine X to Machine Y (Machine Y has postgres DB) > How should my connection string look like in a program in Machine X. > > EXEC SQL CONNECT TO ------??? (Do i need to specify the IP of DB server?) > I tried a few options but nothing works. This appears to be ECPG so see "Connecting to the Database Server" in the ECGP chapter of the documentation: http://www.postgresql.org/docs/8.1/interactive/ecpg-connect.html If the documentation and examples don't help then please post exactly what you've tried and what happened (complete error message, etc.). -- Michael Fuhr
Hi
I already read that documentation.
My ECPG code for connecting to the DB server is:
EXEC SQL CONNECT TO 192.168.1.100:/xyz
i also tried
tcp:postgresql://192.168.1.100[:port][/dbname][ ?options]
unix:postgresql://192.168.1.100[:port][ /dbname][?options]
but unfortunately it say DB doesn't exist.I don't know the right way to use IP addresses while connecting to a postgres DB using ECPG.
On 8/9/06, Michael Fuhr <mike@fuhr.org> wrote:On Wed, Aug 09, 2006 at 11:02:00AM -0400, Harpreet Dhaliwal wrote:
> I'm trying to connect to postgres database in a distributed environment,
> say from machine X to Machine Y (Machine Y has postgres DB)
> How should my connection string look like in a program in Machine X.
>
> EXEC SQL CONNECT TO ------??? (Do i need to specify the IP of DB server?)
> I tried a few options but nothing works.
This appears to be ECPG so see "Connecting to the Database Server"
in the ECGP chapter of the documentation:
http://www.postgresql.org/docs/8.1/interactive/ecpg-connect.html
If the documentation and examples don't help then please post exactly
what you've tried and what happened (complete error message, etc.).
--
Michael Fuhr
On Thu, Aug 10, 2006 at 12:02:24AM -0400, Harpreet Dhaliwal wrote: > I already read that documentation. > > My ECPG code for connecting to the DB server is: > > EXEC SQL CONNECT TO 192.168.1.100:/xyz That format isn't shown in the documentation; the ecpg preprocessor should fail with a syntax error if you try using it. > i also tried > > tcp:postgresql://192.168.1.100[:*port*][/*dbname*][?*options*] The above is probably what you need, but without seeing the exact code you tried it's hard to say why it's not working. > unix:postgresql://*192.168.1.100*[:*port*][/*dbname*][?*options*] The ecpg preprocessor shouldn't allow this -- it should fail with an error like "unix domain sockets only work on 'localhost' but not on '192.168.1.100'". > but unfortunately it say DB doesn't exist. Are you sure the database exists? Can you connect to it with psql? > I don't know the right way to use IP addresses while connecting to a > postgres DB using ECPG. If you have a server on 192.168.1.100 listening on the default port (5432, or whatever PGPORT is set to) and you want to connect to a database named "mydb" on that server, then the following should work: EXEC SQL CONNECT TO tcp:postgresql://192.168.1.100/mydb; If the database is listening on another port, say 12345, then this should work: EXEC SQL CONNECT TO tcp:postgresql://192.168.1.100:12345/mydb; If you're getting 'database "mydb" does not exist' errors then try connecting with psql and make sure the database really does exist. If you still have trouble then please post a minimal but complete program so we can see everything you're doing. -- Michael Fuhr
This is a simple code snippet that i've written to check if i can connect to the postgres database server residing at IP 192.168.0.123. DB name is xyz
Also, user account jsb has the access to the database xyz.
------------------------------------------------------------
#include <stdio.h>
EXEC SQL INCLUDE sqlca
int main ()
{
EXEC SQL BEGIN DECLARE SECTION;
char abc[20];
EXEC SQL END DECLARE SECTION;
EXEC SQL CONNECT TO 'tcp:postgresql://192.168.0.123/xyz' USER jsb
printf("Error code is: %d ", SQLCODE);
}
---------------------------------------------------------------------------------------------------------------------------
The error code that it gives me is -402 that means it could not establish the connection.
Don't know whats going wrong.
Thanks,
Harpreet.
On 8/10/06, Michael Fuhr <mike@fuhr.org> wrote:
On Thu, Aug 10, 2006 at 12:02:24AM -0400, Harpreet Dhaliwal wrote:
> I already read that documentation.
>
> My ECPG code for connecting to the DB server is:
>
> EXEC SQL CONNECT TO 192.168.1.100:/xyz
That format isn't shown in the documentation; the ecpg preprocessor
should fail with a syntax error if you try using it.
> i also tried
>
> tcp:postgresql://192.168.1.100[:*port*][/*dbname*][?*options*]
The above is probably what you need, but without seeing the exact
code you tried it's hard to say why it's not working.
> unix:postgresql://*192.168.1.100*[:*port*][/*dbname*][?*options*]
The ecpg preprocessor shouldn't allow this -- it should fail with
an error like "unix domain sockets only work on 'localhost' but not
on '192.168.1.100'".
> but unfortunately it say DB doesn't exist.
Are you sure the database exists? Can you connect to it with
psql?
> I don't know the right way to use IP addresses while connecting to a
> postgres DB using ECPG.
If you have a server on 192.168.1.100 listening on the default port
(5432, or whatever PGPORT is set to) and you want to connect to a
database named "mydb" on that server, then the following should
work:
EXEC SQL CONNECT TO tcp:postgresql://192.168.1.100/mydb;
If the database is listening on another port, say 12345, then
this should work:
EXEC SQL CONNECT TO tcp:postgresql://192.168.1.100:12345/mydb;
If you're getting 'database "mydb" does not exist' errors then try
connecting with psql and make sure the database really does exist.
If you still have trouble then please post a minimal but complete
program so we can see everything you're doing.
--
Michael Fuhr
On Thu, Aug 10, 2006 at 11:57:14AM -0400, Harpreet Dhaliwal wrote: > EXEC SQL CONNECT TO 'tcp:postgresql://192.168.0.123/xyz' USER jsb It should work if you omit the quotes or use a variable reference. Try this: EXEC SQL CONNECT TO tcp:postgresql://192.168.0.123/xyz USER jsb; or EXEC SQL BEGIN DECLARE SECTION; char *connstr = "tcp:postgresql://192.168.0.123/xyz"; EXEC SQL END DECLARE SECTION; EXEC SQL CONNECT TO :connstr USER jsb; -- Michael Fuhr
On Thu, Aug 10, 2006 at 11:57:14AM -0400, Harpreet Dhaliwal wrote: > This is a simple code snippet that i've written to check if i can connect to > the postgres database server residing at IP 192.168.0.123. DB name is xyz > Also, user account jsb has the access to the database xyz. > ------------------------------------------------------------ > #include <stdio.h> > > EXEC SQL INCLUDE sqlca I take it you did not copy the file verbatim because this seem to lack the ';'. > EXEC SQL CONNECT TO 'tcp:postgresql://192.168.0.123/xyz' USER jsb The correct syntax would be: EXEC SQL CONNECT TO tcp:postgresql://192.168.0.123/xyz USER jsb; This should work at least it does for me. :-) Michael -- Michael Meskes Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: meskes@jabber.org Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!
On Fri, Aug 11, 2006 at 11:58:16AM +0200, Michael Meskes wrote: > > EXEC SQL CONNECT TO 'tcp:postgresql://192.168.0.123/xyz' USER jsb > > The correct syntax would be: > > EXEC SQL CONNECT TO tcp:postgresql://192.168.0.123/xyz USER jsb; The ECPG "Connecting to the Database Server" documentation does have an example for Unix sockets with quotes: EXEC SQL CONNECT TO 'unix:postgresql://sql.mydomain.com/mydb' AS myconnection USER john; Should that be changed? It's wrong on two counts as far as I can tell: with quotes the connection fails with a server error of FATAL: database "'unix:postgresql://sql.mydomain.com/mydb'" does not exist Without quotes the ECPG preprocessor fails with ERROR: unix domain sockets only work on 'localhost' but not on 'sql.mydom' I'm thinking that example should be: EXEC SQL CONNECT TO unix:postgresql://localhost/mydb AS myconnection USER john; Also, among the target formats the documentation includes: * an SQL string literal containing one of the above forms and the following paragraph says: In practice, it is probably less error-prone to use a (single-quoted) string literal or a variable reference. which might be the source of confusion here. -- Michael Fuhr
On Fri, Aug 11, 2006 at 07:01:55AM -0600, Michael Fuhr wrote: > The ECPG "Connecting to the Database Server" documentation does > have an example for Unix sockets with quotes: > > EXEC SQL CONNECT TO 'unix:postgresql://sql.mydomain.com/mydb' AS myconnection USER john; > > Should that be changed? It's wrong on two counts as far as I think so yes. > I'm thinking that example should be: > > EXEC SQL CONNECT TO unix:postgresql://localhost/mydb AS myconnection USER john; Right. > In practice, it is probably less error-prone to use a (single-quoted) > string literal or a variable reference. > > which might be the source of confusion here. This should be fixed. You're absolutely right. Michael -- Michael Meskes Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: meskes@jabber.org Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!
LOl..that ';' is quite obvious. Though i forgot to include that in the mail.
Sorry about that.
Also, do u think that while starting the postgresql server using 'pg_ctl start', there is some -i option that needs to be included with it so that the DB server accepts tcp connections?
One of the guys in the postgres community was talking about it. don't know how true is that because there's no such option like that for pg_ctl start in the postgres manual.
Can you please comment on that?
~Harpreet.
On 8/11/06, Michael Meskes <meskes@postgresql.org> wrote:
On Thu, Aug 10, 2006 at 11:57:14AM -0400, Harpreet Dhaliwal wrote:
> This is a simple code snippet that i've written to check if i can connect to
> the postgres database server residing at IP 192.168.0.123. DB name is xyz
> Also, user account jsb has the access to the database xyz.
> ------------------------------------------------------------
> #include <stdio.h>
>
> EXEC SQL INCLUDE sqlca
I take it you did not copy the file verbatim because this seem to lack
the ';'.
> EXEC SQL CONNECT TO 'tcp:postgresql://192.168.0.123/xyz' USER jsb
The correct syntax would be:
EXEC SQL CONNECT TO tcp:postgresql://192.168.0.123/xyz USER jsb;
This should work at least it does for me. :-)
Michael
--
Michael Meskes
Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org)
ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: meskes@jabber.org
Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!
Harpreet Dhaliwal wrote: > LOl..that ';' is quite obvious. Though i forgot to include that in the mail. > Sorry about that. > Also, do u think that while starting the postgresql server using 'pg_ctl > start', there is some -i option that needs to be included with it so that > the DB server accepts tcp connections? > One of the guys in the postgres community was talking about it. don't know > how true is that because there's no such option like that for pg_ctl start > in the postgres manual. If you want TCP connections, adjust postgresql.conf accordingly; either the tcpip_socket parameter, or listen_addresses, depending on the Postgres version you are using. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
On Fri, Aug 11, 2006 at 06:09:16PM +0200, Michael Meskes wrote: > On Fri, Aug 11, 2006 at 07:01:55AM -0600, Michael Fuhr wrote: > > The ECPG "Connecting to the Database Server" documentation does > > have an example for Unix sockets with quotes: > > > > EXEC SQL CONNECT TO 'unix:postgresql://sql.mydomain.com/mydb' AS myconnection USER john; > > > > Should that be changed? It's wrong on two counts as far as > > I think so yes. Will you take care of it or should I submit a patch? I've noticed a few other discrepancies between the documentation and actual behavior, like examples with "VARCHAR val;" that the preprocessor rejects with "ERROR: pointer to varchar are not implemented." -- Michael Fuhr
What kind of patch are you talking about?
On 8/11/06, Michael Fuhr <mike@fuhr.org> wrote:
On Fri, Aug 11, 2006 at 06:09:16PM +0200, Michael Meskes wrote:
> On Fri, Aug 11, 2006 at 07:01:55AM -0600, Michael Fuhr wrote:
> > The ECPG "Connecting to the Database Server" documentation does
> > have an example for Unix sockets with quotes:
> >
> > EXEC SQL CONNECT TO 'unix:postgresql://sql.mydomain.com/mydb' AS myconnection USER john;
> >
> > Should that be changed? It's wrong on two counts as far as
>
> I think so yes.
Will you take care of it or should I submit a patch? I've noticed
a few other discrepancies between the documentation and actual
behavior, like examples with "VARCHAR val;" that the preprocessor
rejects with "ERROR: pointer to varchar are not implemented."
--
Michael Fuhr
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
On Fri, Aug 11, 2006 at 11:40:53PM -0400, Harpreet Dhaliwal wrote: > What kind of patch are you talking about? A documentation patch. Michael Meskes, to whom I was responding, maintains ECPG. I was asking whether he wanted me to submit a patch to fix misleading parts of the documentation or whether he'd commit the necessary changes based on what we've already discussed in this thread. -- Michael Fuhr
Hello,
I'm really not able to connect to my database server.
Let me explain the whole thing once again so that I don't miss telling anything.
I have a user account jsb that owns directory /usr/local/pgsql/jsb
initdb is done on the same directory i.e. /usr/local/pgsql/jsb
So all the db related files are in the same directory.
Now there is a dabatase dbxyz created using pgadmin3 and user/profile jsb is the owner of
this database, which is again set using pgadmin3 only.
Now, in the machine (where all my middle tier programs would reside), I have a .pgc using which i am trying to connect to the Database server whose IP address is 192.168.0.123.
Also, in postgresql.conf file, I have set listen_address='*' and port = 5432.
Now, i have a test.pgc file whose contents are as follows:
---------------------------------------------------------
---------------------------------------------------------
#include <stdio.h>
EXEC SQL INCLUDE sqlca;
int main()
{
EXEC SQL BEGIN DECLARE SECTION;
char abc[20];
EXEC SQL END DECLARE SECTION;
EXEC SQL CONNECT TO tcp:postgresql://192.168.0.123:5432/dbxyz USER jsb;
printf("Error code is %d \n", SQLCODE);
}
-------------------------------------------------------------------
-------------------------------------------------------------------
Here User jsb is used because jsb owns database xyz and the database directory /usr/local/pgsql/jsb aswell
I always get error code -402.
I don't know where am i going wrong or what extra i need to do.
My deadline is approaching very close and I'm feeling baffled now coz i don't think so there's anything more i can do, not something in my knowledge.
A prompt and quick help would be greatly and deeply appreciated.
Thanks and regards,
harpreet
I'm really not able to connect to my database server.
Let me explain the whole thing once again so that I don't miss telling anything.
I have a user account jsb that owns directory /usr/local/pgsql/jsb
initdb is done on the same directory i.e. /usr/local/pgsql/jsb
So all the db related files are in the same directory.
Now there is a dabatase dbxyz created using pgadmin3 and user/profile jsb is the owner of
this database, which is again set using pgadmin3 only.
Now, in the machine (where all my middle tier programs would reside), I have a .pgc using which i am trying to connect to the Database server whose IP address is 192.168.0.123.
Also, in postgresql.conf file, I have set listen_address='*' and port = 5432.
Now, i have a test.pgc file whose contents are as follows:
---------------------------------------------------------
---------------------------------------------------------
#include <stdio.h>
EXEC SQL INCLUDE sqlca;
int main()
{
EXEC SQL BEGIN DECLARE SECTION;
char abc[20];
EXEC SQL END DECLARE SECTION;
EXEC SQL CONNECT TO tcp:postgresql://192.168.0.123:5432/dbxyz USER jsb;
printf("Error code is %d \n", SQLCODE);
}
-------------------------------------------------------------------
-------------------------------------------------------------------
Here User jsb is used because jsb owns database xyz and the database directory /usr/local/pgsql/jsb aswell
I always get error code -402.
I don't know where am i going wrong or what extra i need to do.
My deadline is approaching very close and I'm feeling baffled now coz i don't think so there's anything more i can do, not something in my knowledge.
A prompt and quick help would be greatly and deeply appreciated.
Thanks and regards,
harpreet
On 8/12/06, Michael Fuhr <mike@fuhr.org> wrote:
On Fri, Aug 11, 2006 at 11:40:53PM -0400, Harpreet Dhaliwal wrote:
> What kind of patch are you talking about?
A documentation patch. Michael Meskes, to whom I was responding,
maintains ECPG. I was asking whether he wanted me to submit a patch
to fix misleading parts of the documentation or whether he'd commit
the necessary changes based on what we've already discussed in this
thread.
--
Michael Fuhr
Also,
-402 sqlcode means "connection attempt to the database did not succeed"
does this mean that my application is connecting to the database server but somehow failing in connecting to the database dbxyz due to some authentication problems???
~Harpreet
-402 sqlcode means "connection attempt to the database did not succeed"
does this mean that my application is connecting to the database server but somehow failing in connecting to the database dbxyz due to some authentication problems???
~Harpreet
On 8/13/06, Harpreet Dhaliwal < harpreet.dhaliwal01@gmail.com> wrote:
Hello,
I'm really not able to connect to my database server.
Let me explain the whole thing once again so that I don't miss telling anything.
I have a user account jsb that owns directory /usr/local/pgsql/jsb
initdb is done on the same directory i.e. /usr/local/pgsql/jsb
So all the db related files are in the same directory.
Now there is a dabatase dbxyz created using pgadmin3 and user/profile jsb is the owner of
this database, which is again set using pgadmin3 only.
Now, in the machine (where all my middle tier programs would reside), I have a .pgc using which i am trying to connect to the Database server whose IP address is 192.168.0.123.
Also, in postgresql.conf file, I have set listen_address='*' and port = 5432.
Now, i have a test.pgc file whose contents are as follows:
---------------------------------------------------------
---------------------------------------------------------
#include <stdio.h>
EXEC SQL INCLUDE sqlca;
int main()
{
EXEC SQL BEGIN DECLARE SECTION;
char abc[20];
EXEC SQL END DECLARE SECTION;EXEC SQL CONNECT TO tcp:postgresql://192.168.0.123:5432/dbxyz USER jsb;
printf("Error code is %d \n", SQLCODE);
}
-------------------------------------------------------------------
-------------------------------------------------------------------
Here User jsb is used because jsb owns database xyz and the database directory /usr/local/pgsql/jsb aswell
I always get error code -402.
I don't know where am i going wrong or what extra i need to do.
My deadline is approaching very close and I'm feeling baffled now coz i don't think so there's anything more i can do, not something in my knowledge.
A prompt and quick help would be greatly and deeply appreciated.
Thanks and regards,harpreetOn 8/12/06, Michael Fuhr < mike@fuhr.org> wrote:On Fri, Aug 11, 2006 at 11:40:53PM -0400, Harpreet Dhaliwal wrote:
> What kind of patch are you talking about?
A documentation patch. Michael Meskes, to whom I was responding,
maintains ECPG. I was asking whether he wanted me to submit a patch
to fix misleading parts of the documentation or whether he'd commit
the necessary changes based on what we've already discussed in this
thread.
--
Michael Fuhr
--- Harpreet Dhaliwal <harpreet.dhaliwal01@gmail.com> wrote: > Hello, > > I'm really not able to connect to my database server. Does nmap -sS localhost show that port 5432 is open and used by postgresql?
i started the postmaster using pg_ctl in user a/c jsb.
Switched to root and ran nmap -sS localhost
for port 5432 it says
5432/tcp opne postgres
Switched to root and ran nmap -sS localhost
for port 5432 it says
5432/tcp opne postgres
On 8/13/06, Richard Broersma Jr <rabroersma@yahoo.com> wrote:
--- Harpreet Dhaliwal <harpreet.dhaliwal01@gmail.com> wrote:
> Hello,
>
> I'm really not able to connect to my database server.
Does
nmap -sS localhost show that port 5432 is open and used by postgresql?
What do i do next buddy?
~harpreet
~harpreet
On 8/13/06, Harpreet Dhaliwal <harpreet.dhaliwal01@gmail.com > wrote:
i started the postmaster using pg_ctl in user a/c jsb.
Switched to root and ran nmap -sS localhost
for port 5432 it says
5432/tcp opne postgresOn 8/13/06, Richard Broersma Jr <rabroersma@yahoo.com> wrote:
--- Harpreet Dhaliwal <harpreet.dhaliwal01@gmail.com> wrote:
> Hello,
>
> I'm really not able to connect to my database server.
Does
nmap -sS localhost show that port 5432 is open and used by postgresql?
On Sun, Aug 13, 2006 at 01:58:54AM -0400, Harpreet Dhaliwal wrote: > -402 sqlcode means "connection attempt to the database did not succeed" > > does this mean that my application is connecting to the database server but > somehow failing in connecting to the database dbxyz due to some > authentication problems??? What do the server's logs say? Can you connect to the database from the same machine using psql? If you add the following line to your program before the connect, what output do you get? ECPGdebug(1, stderr); -- Michael Fuhr
> What do i do next buddy? > ~harpreet When you created your database using PGadmin 3, was PGadmin 3 on your Postgresql Server or was it on the client machine that you are trying to establish an ECPG connection from? I just want to be sure that you are infact able to connect to your database from your client computer. Regards, Richard Broersma Jr.
Michael Fuhr wrote: > On Fri, Aug 11, 2006 at 11:40:53PM -0400, Harpreet Dhaliwal wrote: > > What kind of patch are you talking about? > > A documentation patch. Michael Meskes, to whom I was responding, > maintains ECPG. I was asking whether he wanted me to submit a patch > to fix misleading parts of the documentation or whether he'd commit > the necessary changes based on what we've already discussed in this > thread. Yes, please submit a documentation patch and I will apply it. Thanks. -- Bruce Momjian bruce@momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Postgres is not installed in the client computer.
When i try to connect to the postgres db from the computer in which postgres is installed, it goes through. No hassles in that.
Problem comes up when I'm trying to connect to the same database from a different computer in which no postgres is installed.
On 8/13/06, Richard Broersma Jr <rabroersma@yahoo.com> wrote:
> What do i do next buddy?
> ~harpreet
When you created your database using PGadmin 3, was PGadmin 3 on your Postgresql Server or was it
on the client machine that you are trying to establish an ECPG connection from?
I just want to be sure that you are infact able to connect to your database from your client
computer.
Regards,
Richard Broersma Jr.
> Postgres is not installed in the client computer. > When i try to connect to the postgres db from the computer in which postgres > is installed, it goes through. No hassles in that. > Problem comes up when I'm trying to connect to the same database from a > different computer in which no postgres is installed. If you try installing a seperate installation of pgadmin 3 on the client computer to test remote with your postgresql server could help you determine where your problem is. Also, it sounds like you might still have problems in your pg_hba.conf, or postgresql.conf files. I can't remember if you've already posted them but maybe it would hurt to post them again. Regards, Richard Broersma Jr.
Hi Richard,
I'm attaching pg_hba.conf and postgresql.conf files as you had asked for.
Don't know if I've missed anything in these two configuration files that would not let
my client machine to connect to the DB computer.
Also, what exactly do u want me to install on the client computer? A full fledged installation of postgres or what?
Thanks and regards,
~Harpreet
I'm attaching pg_hba.conf and postgresql.conf files as you had asked for.
Don't know if I've missed anything in these two configuration files that would not let
my client machine to connect to the DB computer.
Also, what exactly do u want me to install on the client computer? A full fledged installation of postgres or what?
Thanks and regards,
~Harpreet
On 8/13/06, Richard Broersma Jr <rabroersma@yahoo.com> wrote:
> Postgres is not installed in the client computer.
> When i try to connect to the postgres db from the computer in which postgres
> is installed, it goes through. No hassles in that.
> Problem comes up when I'm trying to connect to the same database from a
> different computer in which no postgres is installed.
If you try installing a seperate installation of pgadmin 3 on the client computer to test remote
with your postgresql server could help you determine where your problem is.
Also, it sounds like you might still have problems in your pg_hba.conf, or postgresql.conf files.
I can't remember if you've already posted them but maybe it would hurt to post them again.
Regards,
Richard Broersma Jr.
Attachment
ECPGdebug(1, stderr);
After including this line of code in my program,
it says:
raising sqlcode -402 in line 14, 'could not connect to database dbxyz in line 14'
Thanks,
~Harpreet.
After including this line of code in my program,
it says:
raising sqlcode -402 in line 14, 'could not connect to database dbxyz in line 14'
Thanks,
~Harpreet.
On 8/13/06, Michael Fuhr <mike@fuhr.org> wrote:
On Sun, Aug 13, 2006 at 01:58:54AM -0400, Harpreet Dhaliwal wrote:
> -402 sqlcode means "connection attempt to the database did not succeed"
>
> does this mean that my application is connecting to the database server but
> somehow failing in connecting to the database dbxyz due to some
> authentication problems???
What do the server's logs say? Can you connect to the database
from the same machine using psql? If you add the following line
to your program before the connect, what output do you get?
ECPGdebug(1, stderr);
--
Michael Fuhr
Michael,
Please ignore my last email
At last I got error code 0.
Problem was with pg_hba.conf file
ECPGdebug(1, stderr); showed that in pg_hba.conf there was setting for localhost only and not for other ip addresses.
I had to change the configuration for IPV4 local connections
It should have been something like
host all all 192.168.0.0/24 trust
instead of
host all all 127.0.0.1/32 trust (which is meant for localhost only)
I think i got it...right?
thanks alot for your help. Do you anymore inputs or warnings for me on this?
I'm highly obliged MIchael..
Thanks and regards,
~Harpreet
Please ignore my last email
At last I got error code 0.
Problem was with pg_hba.conf file
ECPGdebug(1, stderr); showed that in pg_hba.conf there was setting for localhost only and not for other ip addresses.
I had to change the configuration for IPV4 local connections
It should have been something like
host all all 192.168.0.0/24 trust
instead of
host all all 127.0.0.1/32 trust (which is meant for localhost only)
I think i got it...right?
thanks alot for your help. Do you anymore inputs or warnings for me on this?
I'm highly obliged MIchael..
Thanks and regards,
~Harpreet
On 8/13/06, Michael Fuhr <mike@fuhr.org> wrote:
On Sun, Aug 13, 2006 at 01:58:54AM -0400, Harpreet Dhaliwal wrote:
> -402 sqlcode means "connection attempt to the database did not succeed"
>
> does this mean that my application is connecting to the database server but
> somehow failing in connecting to the database dbxyz due to some
> authentication problems???
What do the server's logs say? Can you connect to the database
from the same machine using psql? If you add the following line
to your program before the connect, what output do you get?
ECPGdebug(1, stderr);
--
Michael Fuhr
Thanks alot all.
You guys have been really helpful.
I've crossed the first obstacle, a major one though...Phew.
Let me see where do I trip later :-)
~Harpreet
You guys have been really helpful.
I've crossed the first obstacle, a major one though...Phew.
Let me see where do I trip later :-)
~Harpreet
On 8/13/06, Richard Broersma Jr <rabroersma@yahoo.com> wrote:
> Postgres is not installed in the client computer.
> When i try to connect to the postgres db from the computer in which postgres
> is installed, it goes through. No hassles in that.
> Problem comes up when I'm trying to connect to the same database from a
> different computer in which no postgres is installed.
If you try installing a seperate installation of pgadmin 3 on the client computer to test remote
with your postgresql server could help you determine where your problem is.
Also, it sounds like you might still have problems in your pg_hba.conf, or postgresql.conf files.
I can't remember if you've already posted them but maybe it would hurt to post them again.
Regards,
Richard Broersma Jr.
On Sun, Aug 13, 2006 at 07:07:11PM -0400, Harpreet Dhaliwal wrote: > Problem was with pg_hba.conf file > > ECPGdebug(1, stderr); showed that in pg_hba.conf there was setting for > localhost only and not for other ip addresses. > > I had to change the configuration for IPV4 local connections > > It should have been something like > > host all all 192.168.0.0/24 trust > instead of > host all all 127.0.0.1/32 trust (which is meant for localhost > only) > > I think i got it...right? You might want to add 192.168.0.0/24 on another line rather than replacing 127.0.0.1. And allowing "trust" connections is bad security practice because anybody on one of the allowed IP addresses could connect as any user without being challenged for a password. Consider using a stronger authentication method and modifying the client code accordingly. http://www.postgresql.org/docs/8.1/interactive/auth-methods.html -- Michael Fuhr
Yeah. I just realized when I could not start pgadminIII in the DB server computer.
Added that line for localhost too :)
Yeah, i need stronger authentication for my application.
Thanks for the valuable advice.
Thanks again
~Harpreet.
Added that line for localhost too :)
Yeah, i need stronger authentication for my application.
Thanks for the valuable advice.
Thanks again
~Harpreet.
On 8/13/06, Michael Fuhr <mike@fuhr.org> wrote:
On Sun, Aug 13, 2006 at 07:07:11PM -0400, Harpreet Dhaliwal wrote:
> Problem was with pg_hba.conf file
>
> ECPGdebug(1, stderr); showed that in pg_hba.conf there was setting for
> localhost only and not for other ip addresses.
>
> I had to change the configuration for IPV4 local connections
>
> It should have been something like
>
> host all all 192.168.0.0/24 trust
> instead of
> host all all 127.0.0.1/32 trust (which is meant for localhost
> only)
>
> I think i got it...right?
You might want to add 192.168.0.0/24 on another line rather than
replacing 127.0.0.1. And allowing "trust" connections is bad
security practice because anybody on one of the allowed IP addresses
could connect as any user without being challenged for a password.
Consider using a stronger authentication method and modifying the
client code accordingly.
http://www.postgresql.org/docs/8.1/interactive/auth-methods.html
--
Michael Fuhr
> Hi Richard, > I'm attaching pg_hba.conf and postgresql.conf files as you had asked for. > Don't know if I've missed anything in these two configuration files that > would not let > my client machine to connect to the DB computer. > > Also, what exactly do u want me to install on the client computer? A full > fledged installation of postgres or what? It is the link to the download site of PG-Admin III. It is an client tool for postgresql cluster administration. Install this on the client computer for the purpose of connecting to your postgresql server. Don't confuse Pg-Admin III with postgresql. Postgresql is a standalone server. Pg-Admin III is a separate client administration that just happens to be bundled with postgresql on the windows port for user convenience. Regards, Richard Broersma Jr.
oops, for got the link: http://www.pgadmin.org/download/ --- Richard Broersma Jr <rabroersma@yahoo.com> wrote: > > Hi Richard, > > I'm attaching pg_hba.conf and postgresql.conf files as you had asked for. > > Don't know if I've missed anything in these two configuration files that > > would not let > > my client machine to connect to the DB computer. > > > > Also, what exactly do u want me to install on the client computer? A full > > fledged installation of postgres or what? > > It is the link to the download site of PG-Admin III. It is an client tool for postgresql > cluster > administration. > > Install this on the client computer for the purpose of connecting to your postgresql server. > Don't confuse Pg-Admin III with postgresql. Postgresql is a standalone server. Pg-Admin III is > a > separate client administration that just happens to be bundled with postgresql on the windows > port > for user convenience. > > Regards, > > Richard Broersma Jr. > >
On Sun, Aug 13, 2006 at 01:50:13AM -0400, Harpreet Dhaliwal wrote: > I'm really not able to connect to my database server. > ... Again, your example works nicely for me. This has to be a problem with your local setup. You should get the same connection problem using psql on the same machine with the same connection setting. Michael -- Michael Meskes Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: meskes@jabber.org Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!
On Fri, Aug 11, 2006 at 04:40:36PM -0600, Michael Fuhr wrote: > > > EXEC SQL CONNECT TO 'unix:postgresql://sql.mydomain.com/mydb' AS myconnection USER john; > > > > > > Should that be changed? It's wrong on two counts as far as > > > > I think so yes. > > Will you take care of it or should I submit a patch? I've noticed I you have the time to write the patch I woul dappreciate it. > a few other discrepancies between the documentation and actual > behavior, like examples with "VARCHAR val;" that the preprocessor > rejects with "ERROR: pointer to varchar are not implemented." Do you have an example? This surely looks like a bug. Michael -- Michael Meskes Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: meskes@jabber.org Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!
On Sun, Aug 13, 2006 at 10:30:24AM +0200, Michael Meskes wrote: > On Fri, Aug 11, 2006 at 04:40:36PM -0600, Michael Fuhr wrote: > > Will you take care of it or should I submit a patch? I've noticed > > I you have the time to write the patch I woul dappreciate it. I'll submit a patch. However, in the case of string literals not working, is that a documentation bug or a code bug? Are they supposed to work? > > a few other discrepancies between the documentation and actual > > behavior, like examples with "VARCHAR val;" that the preprocessor > > rejects with "ERROR: pointer to varchar are not implemented." > > Do you have an example? This surely looks like a bug. See several code examples in the last half of the "Using Host Variables" documentation: http://www.postgresql.org/docs/8.1/interactive/ecpg-variables.html Here's a complete example with code pasted from the documentation: % cat foo.pgc int main(void) { EXEC SQL BEGIN DECLARE SECTION; int v1; VARCHAR v2; EXEC SQL END DECLARE SECTION; return 0; } % ecpg foo.pgc foo.pgc:5: ERROR: pointer to varchar are not implemented Is that a documentation bug or a code bug? -- Michael Fuhr
On Tue, Aug 15, 2006 at 05:59:23AM -0600, Michael Fuhr wrote: > I'll submit a patch. However, in the case of string literals not > working, is that a documentation bug or a code bug? Are they > supposed to work? You shoudl be able to use a string constant or a char * variable as database name. There are a lot of test cases for connect available under ecpg/test/connect now. If string literals do not work we have fix it and add it to the test suite. > > > a few other discrepancies between the documentation and actual > > > behavior, like examples with "VARCHAR val;" that the preprocessor > > > rejects with "ERROR: pointer to varchar are not implemented." > > > > Do you have an example? This surely looks like a bug. > > See several code examples in the last half of the "Using Host > Variables" documentation: Ah, I see. It lacks the size information. Yes, this is a documentation bug. varchar is translated to a struct with an char array whose size needs to be given. I think it never worked without it. Michael -- Michael Meskes Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: meskes@jabber.org Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!
Hi Micheal,
sudde2nly a problem has cropped up in my connection.
Its kind of strange.
ECPGdegug(1, stderr) says
[9852]: connect: cold not open database dbxyz on 192.168.0.123 port 5432 for user jsb in line 16
could not connect to server: No route to host
Is the server running on host "192.168.0.110" and accepting
TCP/IP connections on port 5432?
My server is very much running.
I also made sure that TCP/IP connection is being accepted on port 5432 using nmap -sS localhost in my database server.
Don't know whats wrong now
Also, just an observation (don't know if that is the cause or not) In my clinet machine while compiling my test.c file using
gcc -o test test.c -lecpg -L/usr/lib/pgsql
there's not pgsql directory in /usr/lib.
Where exactly do i have to do this linking thing and to what I have to link it.
Thanks
~Harpreet
sudde2nly a problem has cropped up in my connection.
Its kind of strange.
ECPGdegug(1, stderr) says
[9852]: connect: cold not open database dbxyz on 192.168.0.123 port 5432 for user jsb in line 16
could not connect to server: No route to host
Is the server running on host "192.168.0.110" and accepting
TCP/IP connections on port 5432?
My server is very much running.
I also made sure that TCP/IP connection is being accepted on port 5432 using nmap -sS localhost in my database server.
Don't know whats wrong now
Also, just an observation (don't know if that is the cause or not) In my clinet machine while compiling my test.c file using
gcc -o test test.c -lecpg -L/usr/lib/pgsql
there's not pgsql directory in /usr/lib.
Where exactly do i have to do this linking thing and to what I have to link it.
Thanks
~Harpreet
On 8/13/06, Michael Fuhr <mike@fuhr.org> wrote:
On Sun, Aug 13, 2006 at 07:07:11PM -0400, Harpreet Dhaliwal wrote:
> Problem was with pg_hba.conf file
>
> ECPGdebug(1, stderr); showed that in pg_hba.conf there was setting for
> localhost only and not for other ip addresses.
>
> I had to change the configuration for IPV4 local connections
>
> It should have been something like
>
> host all all 192.168.0.0/24 trust
> instead of
> host all all 127.0.0.1/32 trust (which is meant for localhost
> only)
>
> I think i got it...right?
You might want to add 192.168.0.0/24 on another line rather than
replacing 127.0.0.1. And allowing "trust" connections is bad
security practice because anybody on one of the allowed IP addresses
could connect as any user without being challenged for a password.
Consider using a stronger authentication method and modifying the
client code accordingly.
http://www.postgresql.org/docs/8.1/interactive/auth-methods.html
--
Michael Fuhr
in my previous mail both the ip addressed should be read as 192.168.0.123....
thanks
harpreet
thanks
harpreet
On 8/15/06, Harpreet Dhaliwal < harpreet.dhaliwal01@gmail.com> wrote:
Hi Micheal,
sudde2nly a problem has cropped up in my connection.
Its kind of strange.
ECPGdegug(1, stderr) says
[9852]: connect: cold not open database dbxyz on 192.168.0.123 port 5432 for user jsb in line 16
could not connect to server: No route to host
Is the server running on host " 192.168.0.110" and accepting
TCP/IP connections on port 5432?
My server is very much running.
I also made sure that TCP/IP connection is being accepted on port 5432 using nmap -sS localhost in my database server.
Don't know whats wrong now
Also, just an observation (don't know if that is the cause or not) In my clinet machine while compiling my test.c file using
gcc -o test test.c -lecpg -L/usr/lib/pgsql
there's not pgsql directory in /usr/lib.
Where exactly do i have to do this linking thing and to what I have to link it.
Thanks~HarpreetOn 8/13/06, Michael Fuhr < mike@fuhr.org> wrote:On Sun, Aug 13, 2006 at 07:07:11PM -0400, Harpreet Dhaliwal wrote:
> Problem was with pg_hba.conf file
>
> ECPGdebug(1, stderr); showed that in pg_hba.conf there was setting for
> localhost only and not for other ip addresses.
>
> I had to change the configuration for IPV4 local connections
>
> It should have been something like
>
> host all all 192.168.0.0/24 trust
> instead of
> host all all 127.0.0.1/32 trust (which is meant for localhost
> only)
>
> I think i got it...right?
You might want to add 192.168.0.0/24 on another line rather than
replacing 127.0.0.1. And allowing "trust" connections is bad
security practice because anybody on one of the allowed IP addresses
could connect as any user without being challenged for a password.
Consider using a stronger authentication method and modifying the
client code accordingly.
http://www.postgresql.org/docs/8.1/interactive/auth-methods.html
--
Michael Fuhr
Its done
service iptables stop did the trick
firewall was running on my DB server
forgot to stop it
Thanks
~Harpreet
service iptables stop did the trick
firewall was running on my DB server
forgot to stop it
Thanks
~Harpreet
On 8/15/06, Michael Fuhr <mike@fuhr.org> wrote:
On Sun, Aug 13, 2006 at 10:30:24AM +0200, Michael Meskes wrote:
> On Fri, Aug 11, 2006 at 04:40:36PM -0600, Michael Fuhr wrote:
> > Will you take care of it or should I submit a patch? I've noticed
>
> I you have the time to write the patch I woul dappreciate it.
I'll submit a patch. However, in the case of string literals not
working, is that a documentation bug or a code bug? Are they
supposed to work?
> > a few other discrepancies between the documentation and actual
> > behavior, like examples with "VARCHAR val;" that the preprocessor
> > rejects with "ERROR: pointer to varchar are not implemented."
>
> Do you have an example? This surely looks like a bug.
See several code examples in the last half of the "Using Host
Variables" documentation:
http://www.postgresql.org/docs/8.1/interactive/ecpg-variables.html
Here's a complete example with code pasted from the documentation:
% cat foo.pgc
int main(void)
{
EXEC SQL BEGIN DECLARE SECTION;
int v1;
VARCHAR v2;
EXEC SQL END DECLARE SECTION;
return 0;
}
% ecpg foo.pgc
foo.pgc:5: ERROR: pointer to varchar are not implemented
Is that a documentation bug or a code bug?
--
Michael Fuhr
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
On Tue, Aug 15, 2006 at 03:56:03PM +0200, Michael Meskes wrote: > On Tue, Aug 15, 2006 at 05:59:23AM -0600, Michael Fuhr wrote: > > I'll submit a patch. However, in the case of string literals not > > working, is that a documentation bug or a code bug? Are they > > supposed to work? > > You shoudl be able to use a string constant or a char * variable as > database name. There are a lot of test cases for connect available under > ecpg/test/connect now. If string literals do not work we have fix it and > add it to the test suite. Will that be a minor fix that can be backpatched or will it be invasive enough to be fixed only in HEAD? I'll submit a documentation patch (or patches for different versions) but I'll need to know how much to change. -- Michael Fuhr
On Tue, Aug 15, 2006 at 07:31:31PM -0600, Michael Fuhr wrote: > Will that be a minor fix that can be backpatched or will it be > invasive enough to be fixed only in HEAD? I'll submit a documentation I just fixed it and applied the patch to 8.0 and 8.1 too. Please test it. 7.4 is way more different so I'd prefer to not touch it anymore. Michael -- Michael Meskes Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: meskes@jabber.org Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!
On Fri, Aug 18, 2006 at 06:01:02PM +0200, Michael Meskes wrote: > On Tue, Aug 15, 2006 at 07:31:31PM -0600, Michael Fuhr wrote: > > Will that be a minor fix that can be backpatched or will it be > > invasive enough to be fixed only in HEAD? I'll submit a documentation > > I just fixed it and applied the patch to 8.0 and 8.1 too. Please test > it. It works with a double-quoted string but not with a single-quoted string as the documentation mentions. % cat foo.pgc #include <stdio.h> int main(void) { ECPGdebug(1, stderr); EXEC SQL CONNECT TO 'tcp:postgresql://localhost/test'; EXEC SQL DISCONNECT; return 0; } % ecpg foo.pgc foo.pgc:5: ERROR: syntax error at or near "'tcp:postgresql://localhost/test'" -- Michael Fuhr
On Fri, Aug 18, 2006 at 02:54:19PM -0600, Michael Fuhr wrote: > It works with a double-quoted string but not with a single-quoted > string as the documentation mentions. It's not supposed to work with single-quoted strings. This is a documentation bug. Or does anyone think we should support single quotes too? Michael -- Michael Meskes Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: meskes@jabber.org Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!
On Sat, Aug 19, 2006 at 03:36:42PM +0200, Michael Meskes wrote: > On Fri, Aug 18, 2006 at 02:54:19PM -0600, Michael Fuhr wrote: > > It works with a double-quoted string but not with a single-quoted > > string as the documentation mentions. > > It's not supposed to work with single-quoted strings. This is a > documentation bug. Or does anyone think we should support single quotes > too? It doesn't matter to me; I'd just like to be sure the code is behaving as expected (whatever "expected" is) before I submit a documentation patch. Here's an excerpt from 5WD-02-Foundation-2003-09.pdf, 17.1 <connect statement>: <connection target> ::= <SQL-server name> [ AS <connection name> ] [ USER <connection user name> ] | DEFAULT Excerpt from 5.4 Names and identifiers: <connection name> ::= <simple value specification> <SQL-server name> ::= <simple value specification> <connection user name> ::= <simple value specification> Following along to 6.4 <value specification> and <target specification> and 5.3 <literal> suggests that single-quoted strings should be allowed. So far I haven't found anything to contradict that but I could be overlooking or misinterpreting something. -- Michael Fuhr