Re: 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

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(at)fuhr(dot)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(at)fuhr(dot)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(at)jabber(dot)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(at)jabber(dot)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(at)postgresql(dot)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(at)jabber(dot)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(at)fuhr(dot)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

On 8/12/06, Michael Fuhr <mike(at)fuhr(dot)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

On 8/13/06, Harpreet Dhaliwal <harpreet(dot)dhaliwal01(at)gmail(dot)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,
> harpreet
>
>
> On 8/12/06, Michael Fuhr < mike(at)fuhr(dot)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(dot)dhaliwal01(at)gmail(dot)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

On 8/13/06, Richard Broersma Jr <rabroersma(at)yahoo(dot)com> wrote:
>
>
>
> --- Harpreet Dhaliwal <harpreet(dot)dhaliwal01(at)gmail(dot)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

On 8/13/06, Harpreet Dhaliwal <harpreet(dot)dhaliwal01(at)gmail(dot)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 postgres
>
>
> On 8/13/06, Richard Broersma Jr <rabroersma(at)yahoo(dot)com> wrote:
> >
> >
> >
> > --- Harpreet Dhaliwal <harpreet(dot)dhaliwal01(at)gmail(dot)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 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(at)jabber(dot)org
Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!

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(at)jabber(dot)org
Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use 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(at)momjian(dot)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(at)yahoo(dot)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

On 8/13/06, Richard Broersma Jr <rabroersma(at)yahoo(dot)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.
>

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.

On 8/13/06, Michael Fuhr <mike(at)fuhr(dot)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

On 8/13/06, Michael Fuhr <mike(at)fuhr(dot)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

On 8/13/06, Richard Broersma Jr <rabroersma(at)yahoo(dot)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.

On 8/13/06, Michael Fuhr <mike(at)fuhr(dot)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.

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(at)jabber(dot)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

On 8/13/06, Michael Fuhr <mike(at)fuhr(dot)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

On 8/15/06, Harpreet Dhaliwal <harpreet(dot)dhaliwal01(at)gmail(dot)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
> ~Harpreet
>
> On 8/13/06, Michael Fuhr <mike(at)fuhr(dot)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

On 8/15/06, Michael Fuhr <mike(at)fuhr(dot)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(at)jabber(dot)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(at)jabber(dot)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