[ic] Remote Database Connection Follow-up - Query Going Local in Certain Scripts

[Andrew Baltino]

Good afternoon.  I appreciate the help on this a few months ago.  I
pasted the assistance I received below.

This technique worked great when I was querying from a usertag in my
basket file.  However when I attempt to connect to the remote table in
"etc/log_transaction" using the query tag I get different results.  I
am trying to decrement a quantity field here and there really is not
another safe point I can think to do it.

In certain parts of the log_transaction script the query tag is
attempting reference the local database!  In other parts of the script
it gives me this error:


192.168.100.31 V5BfHpCv:192.168.100.31 - [07/September/2006:23:27:07
-0400] store_devel /cgi-bin/store_devel/process.html ERRORS on ORDER
L000003028:
> Error during creation of order routing log:
> Route log failed. at /home/store/interchange/lib/Vend/Order.pm line 1577.
>

Any suggestions?  I put the code in a usertag and received the same error.

I am running Interchange 4.8.5.

Could I write a PERLscript that excepts arguments that I would pass to
an SQL query and call this PERL script from IC?


Andy


-----------------------------------------------

Date: Fri, 30 Jun 2006 05:03:29 -0700
From: Peter <peter at pajamian.dhs.org>
Subject: Re: [ic] Interchange,  Connecting to Other (Not Default) and
       Remote  Database
To: interchange-users at icdevgroup.org
Message-ID: <44A51311.1040508 at pajamian.dhs.org>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed

On 06/30/2006 03:50 AM, Andrew N wrote:
> Hello again.
>
> I have an Interchange catalog running on one machine connecting to its
> one default database on the same machine.  However I must now access
> data from one table on another machine on the Internet.  Both are
> postgres databases.  I have never done anything like this.
>
> 1.  Connect to another database, meaning not the default pgsql database
> 2.  This other database is on another machine on the Internet, meaning
> not on the same local network
> 3.  Setup this other postgres database I am connected to to receive
> external connections
>
> ANY advice, tips, links, etc would be much appreciated.

Add a user to the remote database, assign the minimal privlidges you
need to that user for the tables(s) you want and assign a password.

Edit the pg_hba.conf file (in the data directory, same location as
postgresql.conf) for the remote database.  You need to add a line
towards the end of the file like this (note this is for my postgresql
8.0 database, older versions have a different format):
host    dbname   username        0.0.0.0/32       md5

...of course you should substitute the appropriate dbname, username, and
IP address of the interchange server.  You can do more lenient
permissions, of course, but I suggest makign it as tight as possible for
a remote connection.  The aove line would allow only the one specific
user to connect to the one specific db and only from the one specific IP
address and will use md5 password authentication.

Restart the remote postgresql db.

add or modify the catroot/dbconf/pgsql/tablename.pgsql.  Add or modify
the following lines:
Database mytable mytable.txt dbi:Pg:dbname=mydb;host=dbserver.example.com
Database mytable USER username
Database mytable PASS password

here mydb is the remote database name, host is the remote hostname,
username and password are for connecting to the remote db.

Apply changes and you should be set.

Peter