[GENERAL] RPMs for PHP accessing PostgreSQL via ODBC over RedHat
Hi, We are currently running the following configuration on our server: Linux Red Hat 7.0 PostgreSQL 7.1beta4-1 PHP 4.0.1pl2 I need to add some RPM package (or perhaps an apache module) to enable PHP to access PostgreSQL via ODBC. In order to have PostgreSQL native access support we installed package 'php-pgsql-4.0.1pl2-9.i386.rpm'. Is there some similar RPM package to enable ODBC access in PHP4.0.1pl2? If not, how should I procceed? TIA, Paulo ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Re: RPMs for PHP accessing PostgreSQL via ODBC over RedHat
The case is really code compatibility. I have benn using PHP to access mSQL, MySQL and PostgreSQL natively for quite a while, *but* we need to port an apllication currently running on NT written in PHP and accessing MS Access using ODBC. We have already exported the database to PostgreSQL over RedHat Linux and have even configured phpPgAdmin for administering it through the Web. The thing is we want to keep our code using PHP ODBC functions to access the database and not use the native calls. I know I can build PHP from the source specifying options such as --with-unixodbc or --with-iodbc, *but* I am currently on an install where I only used RPMs to configure everything and was wondering how I could do that without having to build everything from tarballs (./configure, make, make install...) and keep RedHat RPM package facilities and perhaps enable PHP to comunicate to PostgreSQL through ODBC just by installing some RPM (I also won't have the need to have the database and the application at different servers - they are actually residing at the same machine). Any hints? TIA, Paulo - Original Message - From: Chris <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Monday, March 05, 2001 8:02 PM Subject: Re: [GENERAL] Re: RPMs for PHP accessing PostgreSQL via ODBC over RedHat > > > > >Is it really necessary to use PHP and ODBC? PHP has a native postgresql > > > >interface I believe... > > > > > > It sounds like the postgres server is a different machine to the web > > > server, so ODBC is needed to communicate between the 2.. > > > >No it isn't. Postgres should be accessible via PHP whether it's on the > >same server or on a remote server (and Postgres is configured to allow > >remote access). The only reason I can guess as to why ODBC is being used > >is to perhaps maintain code compatibility with the use of a different > >databse platform that the web server may have been using previously. > > > >-- Brett > > I've never tried so had no idea :) > > How do you configure it into PHP? > > ./configure --help | grep pgsql >--with-pgsql[=DIR] Include PostgreSQL support. DIR is the PostgreSQL >base install directory, defaults to > /usr/local/pgsql. > > > Chris Smith > http://www.squiz.net > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] [PHP] How to connect to PostgreSQL with phpPgAdmi
For the phpPgAdmin users, if you might
help!!!
I am running PostgreSQL 7.1 over
RedHat.
When trying to connect through the web everything
goes right. A script that is currently working:
$link =
pg_Connect("host=localhost port=5432
dbname=teste") or die ("Could not
connect");
$comando = "select * from
teste"; $result =
pg_exec($link,$comando); $linhas_retornadas =
pg_numrows($result);
echo
"nomesobrenome";
for ($i=0; $i < $linhas_retornadas; $i++) {
$row=pg_fetch_array($result,$i);
echo "" . $row["nome"] .
""; echo "" .
$row["sobrenome"] . ""; }
echo "";
pg_close
($link);?>
All *my databases are owned by user 'postgres'*
and I had to create another PostgreSQL *user 'apache' and give ownership of
table 'teste' in database 'teste' to this user* (apache) in order for this
connection to work.
My 'pg_hba.conf' file
reads:local
all
trusthost
all
127.0.0.1 255.255.255.255
trust
With phpPgAdmin I configured file 'config.inc.php'
as follows:
// The default database is used to connect to the
database to check the adv_auth// This can
actually be any database you currently have on your system. It
just// needs _a_ database to connect and check
the system tables.$cfgDefaultDB = "teste";
// You should change the superuser if different
from postgres// This is just used to filter
out the system functions when listing$cfgSuperUser =
"postgres";
// Set to true if you want to authenticate
against the passwd as well as the
username// In order to use adv_auth, you
must update the passwords in the user admin
section.// It is suggested that you
leave this as false until you are able to get in and update the
passwords.$cfgUsePass
=
false;$cfgServers[1]['local']
=
true;$cfgServers[1]['host']
=
'localhost';$cfgServers[1]['port']
= '5432';$cfgServers[1]['adv_auth'] =
false;$cfgServers[1]['stduser'] =
'apache';
No matter if I set
$cfgServers[1]['host'] as 'localhost' or as '127.0.0.1'
$cfgServers[1]['stduser'] as 'apache' or as 'postgres' or as none
No matter what I always get a blank Web page
with the following messages:
ERRORThe requested URL
could not be retrieved
While trying to retrieve the URL: [no URL]
The following error was encountered: Zero Sized
Reply
Squid did not receive any data for this request.
Your cache administrator is webmaster. Generated Sun, 04 Mar 2001
23:15:30 GMT by internet.gst.com.br (Squid/2.3.STABLE3)
The system tables in PostgreSQL are as
follows:
teste=# select * from
pg_user\g usename | usesysid | usecreatedb | usetrace | usesuper
| usecatupd | passwd | valuntil
--+--+-+--+--+---+--+-- postgres
| 26 |
t |
t |
t |
t | |
apache | 27 |
f |
f |
f |
f | | (2
rows)
teste=# select * from
pg_shadow\g usename | usesysid | usecreatedb | usetrace |
usesuper | usecatupd | passwd | valuntil
--+--+-+--+--+---++-- postgres
| 26 |
t |
t |
t |
t
| | apache
| 27 |
f |
f |
f |
f
| | (2 rows)
Please help!!! This shall most probably be some
problem of permission, but how do I manage to make phpPgAdmin to correctly
connect and access my databases?
TIA,Paulo
With which user Apache accesses PostgreSQL (was Re: [GENERAL] How to automatically start postmastrer with TCP/IP support (-i option) ???)
- Original Message - From: Uro Gruber <[EMAIL PROTECTED]> To: Paulo Parola <[EMAIL PROTECTED]> Sent: Sunday, March 04, 2001 7:14 PM Subject: Re: [GENERAL] How to automatically start postmastrer with TCP/IP support (-i option) ??? > Hi, > > Sunday, March 04, 2001, 10:38:04 PM, you wrote: > > PP> Hi, > > PP> So my questions are: > > PP> 1) How should I make so that PostgreSQL is automatically started accepting TCP/IP connections like I did by hand with the command above ( > PP> nohup /usr/bin/postmaster -i -D /var/lib/pgsql/data >server.log 2>>1 &) ??? > Use command pg_ctl (read the manual) > > PP> Which file should I edit to add option '-i' for accepting TCP/IP connections? Apparently, in shell script '/etc/rc.d/init.d/postgresql' the line that starts the postmaster reads 'su -l postgres > PP> -c "/usr/bin/pg_ctl -D $PGDATA -p /usr/bin/postmaster start >/dev/null 2>&1" < /dev/null', is that right? > You don't have to edit any data, you can tell this in with command > line i think option -o and then options switches. > It would not help me to issue the command from the command line, since I need TCP/IP connections set everytime I reboot my server. Since the shell script run by Linux to start the postmaster is '/etc/rc.d/init.d/postgresql' I finally came to the correct syntax of the line I should edit in this file: Original: su -l postgres -c "/usr/bin/pg_ctl -D $PGDATA -p /usr/bin/postmaster start >/dev/null 2>&1" < /dev/null Changed to: su -l postgres -c "/usr/bin/pg_ctl -o '-i' -D $PGDATA -p /usr/bin/postmaster start >/dev/null 2>&1" < /dev/null I then rebooted the server and it worked, as shown by the process running which has option '-i' set : postgres 836 0.0 2.0 5092 1288 ?S19:45 0:00 /usr/bin/postmaster -D /var/lib/pgsql/data -i > PP> 2) With which user shall I connect to the database? User "apache" or maybe user "nobody" ? Should I create a user 'apache' in PostgreSQL? > Anyone you like. You just have to make user. First connect you have to > make with pgsql user which is admin for sesrver and then you create > another user. And again read the manual, everything is in it. > My question should be: with which user Apache accesses PostgreSQL? From the message issued by PostgreSQL it is user 'apache'. Does that mean that *any* tables accessed through the Web should be owned by an user named 'apache' created with PostgreSQL program 'createuser'? For example: database test is owned by user 'postgres'. I then issue the following commands: $ createuser apache Shall the new user be allowed to create databases? (y/n) n Shall the new user be allowed to create more new users? (y/n) n CREATE USER $ psql -Upostgres teste Welcome to psql, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit teste=# \c teste You are now connected to database teste. teste=# \l List of databases Database | Owner | Encoding ---+--+--- template0 | postgres | SQL_ASCII template1 | postgres | SQL_ASCII teste | postgres | SQL_ASCII (3 rows) teste=# \d List of relations Name | Type | Owner ---+---+ teste | table | postgres teste=# alter table teste owner to apache; ALTER teste=# \d List of relations Name | Type | Owner ---+---+ teste | table | apache (1 row) And now my PHP script can connect to my table teste (owned by user 'apache') in database teste (owned by user 'postgres'). Is this the correct way to do it? TIA, Paulo > > > -- > , > Urosmailto:[EMAIL PROTECTED] > > > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] How to automatically start postmastrer with TCP/IP support (-i option) ???
Hi, I am trying to configure PostgreSQL 7.1 over RedHat 7.0 (according to Lamar Owen the RPM's for 7.1 were built on RedHat 6.2 and not 7.0 - does that implies any possible problems?). I have PostgreSQL already starting automatically but with no support to TCP/IP connections. I can see this by the following: [root@atlas pparola]# ps -auxw | grep postgrespostgres 836 0.0 2.0 5092 1284 ? S 17:14 0:00 /usr/bin/postmaster -D /var/lib/pgsql/dataroot 982 0.0 0.9 1488 584 pts/0 S 17:19 0:00 grep postgres And when trying to connect to PostgreSQL from PHP I get the following message: Warning: Unable to connect to PostgresSQL server: PQconnectPoll() -- connect() failed: Connection refused Is the postmaster running (with -i) at 'localhost' and accepting connections on TCP/IP port 5432? in /var/www/html/pgsql.php on line 3Could not connect -- So I started it by hand with the following commands: su - postgres cd /var/lib/pgsql/data nohup /usr/bin/postmaster -i -D /var/lib/pgsql/data >server.log 2>>1 & When accessing the same PHP script I got the following output: Warning: Unable to connect to PostgresSQL server: FATAL 1: user "apache" does not exist in /var/www/html/pgsql.php on line 3Could not connect - So my questions are: 1) How should I make so that PostgreSQL is automatically started accepting TCP/IP connections like I did by hand with the command above ( nohup /usr/bin/postmaster -i -D /var/lib/pgsql/data >server.log 2>>1 &) ??? Which file should I edit to add option '-i' for accepting TCP/IP connections? Apparently, in shell script '/etc/rc.d/init.d/postgresql' the line that starts the postmaster reads 'su -l postgres -c "/usr/bin/pg_ctl -D $PGDATA -p /usr/bin/postmaster start >/dev/null 2>&1" < /dev/null', is that right? 2) With which user shall I connect to the database? User "apache" or maybe user "nobody" ? Should I create a user 'apache' in PostgreSQL? TIA, Paulo
Re: [GENERAL] PostgreSQL 7.1 Linux Installation
Dear Mirko, Thanks for the reply. I have now PostgreSQL7.1 installed, but need to re-install package "php-pgsql-4.0.1pl2-9.i386.rpm". The installation procedure reports me the following: error: failed dependencies: libpq.so.2.1 is needed by php-pgsql-4.0.1pl2-9 And I have found the following files to exist on our system: [root@atlas PostgreSQL]# find /usr -name "libpq.so.*" /usr/lib/libpq.so.2 /usr/lib/libpq.so.2.0 /usr/lib/libpq.so.2.1 Should I make some sort of symlink or perhaps force installation of php-pgsql package? Can anybody give me some clue of how to procceed? TIA, Paulo Parola [EMAIL PROTECTED] - Original Message - From: Mirko Zeibig <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, February 22, 2001 7:20 PM Subject: Re: [GENERAL] PostgreSQL 7.1 Linux Installation > On Thu, Feb 22, 2001 at 04:07:06PM -0300, Paulo Parola wrote: > > [root@atlas PostgreSQL]# rpm -Uvh postgresql-7.1beta4-1.i386.rpm > > error: failed dependencies: > > postgresql = 7.0.2 is needed by postgresql-server-7.0.2-17 > > libpq.so.2.1 is needed by php-pgsql-4.0.1pl2-9 > > libpq.so.2.1 is needed by postgresql-server-7.0.2-17 > > > > [root@atlas PostgreSQL]# rpm -Uvh postgresql-server-7.1beta4-1.i386.rpm > > error: failed dependencies: > > postgresql = 7.1beta4 is needed by postgresql-server-7.1beta4-1 > > libpq.so.2 is needed by postgresql-server-7.1beta4-1 > > > > > You have to specify both RPMs at once during the update: > > rpm -Uvh postgresql-server-7.1beta4-1.i386.rpm postgresql-7.1beta4-1.i386.rpm > > should do the trick, unless the beta-rpm does not provide a libpq.so.2.1 (at > least with a build from CVS libpq.so.2.1 is installed, so I guess rpm is > clever enough to provide this one as well). libpq.so.2 is just a symlink to > libpq.so.2.1 > > Regards > Mirko > -- > [EMAIL PROTECTED] > http://sites.inka.de/picard/ >
[GENERAL] Location of 7.1 version for download
Where can I download PostgreSQL v7.1? Please forward me to the URL where I can download it. Is there already binaries or do I have to compile it? TIA, Paulo
[GENERAL] unions on views (workaround?)
Hi, I am currently porting a database from MS Access to PostgreSQL. I have many views and occasionally some UNIONS among these VIEWS. I have to keep PostgreSQL 7.0.2 for the moment (that's what my ISP provides). So I need to know if anyone has any suggestions about how to simulate a union among two views with SQL (I don't want to put this inteligence into the application). TIA, Paulo Parola [EMAIL PROTECTED]
[GENERAL] Case insensitive searches
Hi, When using mSQL I can perform case insensitive queries by using statements like below: select some_field from table where another_field clike '%substring_entered_by_user%' How should I do that with PostgreSQL? (I understand 'clike' is not a standard SQL feature and there is no similar in PostgreSQL). If I change the substring entered by the user, lets say for example 'more', to the following form '[mM][oO][rR][eE]' would it work? And if I have words with accents (lets say 'Künstler') how should I do to return the same entries no matter if the user types the accent or not? In the previous case, the search should return the same values no matter if the user entered the word 'Künstler' (with accent) or if he typed 'Kunstler' (without accent). TIA, PAulo
