Re: [GENERAL] Two Machines - Question

On Mon, 15 Feb 1999, Paul Nelson wrote:

> Can I run Postgres on  one machine (Linux) and access it from another
> machine (Linux / Apache web server)?

Yes. ;-)


> Any help would be great.

Oh, additional information wanted? No problem:

First you define which host to trust in your network in
your "pg_hba.conf" file.

This looks like this on this host:

host         all         192.168.100.0 255.255.255.0     trust

All connections via 192.168.100.X are considered trusted.

Then you open a tcp/ip connection to your database host on
port 5432. You authenticate yourself as a user and you place
a query onto the host. (provided the given user is allowed
to do that)

When the query finished you retrieve the data via
the open socket.

After this you close the socket and process your data.

On this host this is accomplished using a tcl/tk script.
(caution: works with tcl 8.0 only because of the binary
datatype)

 == snip ==
#
# to be included into other sources ...
#
# set debugfile [open lalla.bin w]

proc {db_puts} {f len s} {
    # Write string s onto channel $f with the length $l
    puts -nonewline $f [binary format a$l $s]
}

# connect to a database and return the database communication channel (dbc)
proc {db_connect} {host port} {
    set dbc [socket $host $port]
    fconfigure $dbc -translation binary
    return $dbc
}


# authenticate onto a database "dbname" via the dbc $f with the username and password
proc {db_authenticate} {f dbname username password} {
    set dbc $f
# for debugging purposes put the output into a file named moerps.bin
#    set dbc [open moerps.bin w+]

    # Int32(296)
    # The size of the packet in bytes.
    puts -nonewline $dbc [binary format I 296]

    # Int32
    # The protocol version number. The most significant 16 bits are the major version number.
    # The least 16 significant bits are the minor version number.
    puts -nonewline $dbc [binary format H* 0001]
    puts -nonewline $dbc [binary format H* 0000]

    # LimString64
    # The database name, defaults to the user name if omitted.
    puts -nonewline $dbc [binary format a64 $dbname]

    # LimString32
    # The user name.
    puts -nonewline $dbc [binary format a32 $username]

    # LimString64
    # Any additional command line arguments to be passed to the backend by the postmaster.
    puts -nonewline $dbc [binary format H128 00]

    # LimString64
    # Unused.
    puts -nonewline $dbc [binary format H128 00]

    # LimString64
    # The optional tty the backend should use for debugging messages.
    puts -nonewline $dbc [binary format H128 00]
    flush $dbc
# close the debugging channel before reading from it
#    close $dbc
#    puts "getting auth response code ..."
    # First get the auth response code pattern (must be R)
    binary scan [read $dbc 1] a s
    # then read the bytes corresponding to the response code long
    if {$s == "R"} {
        # read the auth code response
        binary scan [read $dbc 4] I s
        # Auth. ok
        if {$s == 0} { return $s }
        # Auth. via KERBEROS V4 required
        if {$s == 1} { return $s }
        # Auth. via KERBEROS V5 required
        if {$s == 2} { return $s }
        # Auth. via unencrypted password required
        if {$s == 3} { return $s }
        # Auth. via encrypted password required
        if {$s == 4} {
            # fetch the salt to encrypt the password and store it into the result code
            set ss $s
            binary scan [read $dbc 2] s* s
            lappend ss $s
            return $ss
        }
    }
    return $s
}

# Send a disconnect char to the database, flush the channel and close it afterwards
proc {db_disconnect} {f} {
    puts -nonewline $f X
    flush $f
    close $f
    return
}


# puts rather a command string than a query to a host and returns
# the values in a list of lists. The first (0) element of the list
# contains the field names of the view.
# all following elements contain the data of the corresponding fields.
proc {db_query} {f query} {
    set dbc $f
# for debugging purposes print the query onto the screen
#    puts $query

    puts -nonewline $dbc "Q$query"
    puts -nonewline $dbc [binary format H* 00]
    flush $dbc
    set result {}
    set row {}
    set feldanz 0
    while {1} {
        set row {}
        # read a byte from the dbc and decide which responsecode is to be handled
#        puts -nonewline "Responsecode from dbc is : "
        binary scan [read $dbc 1] a s
#        puts $s

        # Handle an error condition
        if {$s=="E"} {
            set ss $s
            while {$s != "\000"} {
                binary scan [read $dbc 1] a s
#                puts $s
                set ss $ss$s
            }
            return $ss
        }

        # Handle an "Authentication request".
        if {$s=="R"} {
            binary scan [read $dbc 4] I s
#            puts -nonewline "Authentication code is: $s"
            continue
        }
        # Message is a Cursor Response
        if {$s=="P"} {
#            puts "cursor response"
            while {$s != 0 } {
                # the cursor response is nulled in this version.
                # maybe this should be changed but it doesn�t harm at the moment.
                binary scan [read $dbc 1] H* s
            }
            continue
        }
        # Message is a Table description
        if {$s=="T"} {
#            puts "getting table description"
            # get no. of colummns
            binary scan [read $dbc 2] S s
            set feldanz $s
#            puts "no. of fields is: $feldanz"
            for {set i 1} {$i<=$feldanz} {incr i} {
                # fetch the corresponding fieldname from dbc
#                puts "fetching one fieldname ..."
                set ss ""
                while {$s != "\000"} {
                    # get one character from the dbc
                    binary scan [read $dbc 1] a s
                    if {$s != "\x00"} {
                        # append the character to $ss
                        set ss "$ss$s"
                    }
                }
                # get the fieldname into $s from $ss
                set s $ss

#                puts -nonewline "Fieldname : $s"
                lappend row $s

                # fetch the Objectid from the dbc
                binary scan [read $dbc 4] I s
#                puts -nonewline "  objectid : $s"

                # fetch the objectsize from the dbc
                binary scan [read $dbc 2] s s
#                puts "  objectsize : $s"
            }
            lappend result $row
            continue
        }
        # An ASCII row is to be fetched ...
        if {$s=="D"} {
            # getting bitfield

            set j [expr ($feldanz % 8) ? round(($feldanz / 8) + 0.5) : ($feldanz / 8)]

#            puts "Handling a bitfield with $j bytes "
            # fetch the bitfield from the dbc to decide which fields are filled with values
            set bf ""
            for {set i 1} {$i<=$j} {incr i} {
#                puts "getting byte # $i"
                binary scan [read $dbc 1] B* s
                set bf "$bf$s"
#                puts "bitfield value is $bf"
            }

#            puts "fetching $feldanz fields from dbc"
            # fetch all fields of the particular row
            for {set i 1} {$i<=$feldanz} {incr i} {
                # fetch the field contents from the dbc if the corresponding bit in $bf is set
                set k [expr $i-1]
                if {[string range $bf $k $k]=="1"} {
                    binary scan [read $dbc 4] I l
#                    puts "fieldlength is: [expr $l-4]"
                    set ss ""
                    for {set j 5} {$j<=$l} {incr j} {
                        binary scan [read $dbc 1] a s
                        set ss $ss$s
                    }
                } else {
#                    puts "NULL-Value detected, field skipped"
                    set ss {}
                }
#                puts "$ss  "
                lappend row $ss
            }
            lappend result $row
#            puts $row
            continue
        }
        # a C states that a command execution is ready and that no more data follows.
        if {$s=="C"} {
#            puts "Handling C-Order"
            while {$s != 0} {
                binary scan [read $dbc 1] c s
            }
            break
        }
        # Merkmal einer leeren View
        if {$s=="I"} {
            break
        }
    }
    return $result
}

 == snip ==

Then you can do a simple:


set dbc [db_connect $dbhost $dbport]
db_authenticate $dbc $dbname $dbuser $dbuserpw

set l [db_query $dbc "select * from customers;"]

db_disconnect $dbc



In a tcl/tk script to retrieve a list of records into
the variable "l" from a database which resides on the
host "$dbhost". This script has the advantage to be
at least portable to Linux and Win NT. (Both platforms were
tested successfully)

I don't know too much about perl and so on, but the way
to go is always the same. (Ok, you have to define events to trigger
a faulty connection, you have to implement a recovery scheme and so on)

t++