Connection string

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 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 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.

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

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

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

What do i do next buddy?
~harpreet

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!