Re: [PHP] postgresql database access failure
The query was: $query = 'SELECT * FROM databasetablename'; So, database access seems to be the problem. Using the superuser account 'postgres', a user 'httpd' was created and all privileges were granted to the target database using the postgresql 'grant' command. However the user 'httpd' is not the owner of the database so perhaps that is the problem, although if this user could not access the database that would cause an error in the log (but no such error is seen)? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] postgresql database access failure
On 30/04/2011, Daniel Brown danbr...@php.net wrote:
Readers? Sounds like you spend too much time writing newsletters
(to the wrong address, since php-general-digest-h...@lists.php.net is
a self-help command list for digest-form subscriptions). ;-P
On Sat, Apr 30, 2011 at 04:41, e-letter inp...@gmail.com wrote:
?php
$db = pg_connect('dbname=databasename user=username');
$query = 'SELECT * FROM databasename';
$value=pg_fetch_result($query,1,0);
echo 'export of database is ',$value,'';
?
p
why does this fail?
How is it failing? What error(s) are you seeing on screen or in
your log files? Noting that $value would contain an array, is that
the problem? And why are you using ending quotes in your echo? You
should just place the semicolon immediately after $value.
I looked at the error file located at '/var/log/httpd/error_log',
which identifies an error:
...Apache/2.2.6 (Mandriva Linux/PREFORK-8.2mdv2008.0) PHP/5.2.4 with
Suhosin-Patch mod_put/2.0.8 configured -- resuming normal
operations...
...PHP Parse error: syntax error, unexpected T_VARIABLE, expecting
',' or ';'...
The file was changed as follows which caused the parse error shown above:
?php
$db = pg_connect('dbname=webcuttings user=httpd');
$query = 'SELECT * FROM articles';
$value=pg_fetch_result($query);
echo 'all files' $value;
?
The file was copied from the manual page, without understanding that
an array was being used.
/p
/body
/html
The following php code produces the user agent:
?php
echo '$_SERVER['HTTP_USER_AGENT']';
?
First of all, no it doesn't. Placed inside single quotes, it'll
not only try to return it verbatim (i.e. - the variable would be
printed to screen), but it'll also cause a parse error, as you reuse
single quotes in the variable key container.
My mistake; with the command:
?php
echo $_SERVER['HTTP_USER_AGENT'];
?
the result is:
Opera/9.80 (X11; Linux i686; U; en-GB) Presto/2.6.30 Version/10.61
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] postgresql database access failure
On Sun, 2011-05-01 at 09:24 +0100, e-letter wrote:
I looked at the error file located at '/var/log/httpd/error_log',
which identifies an error:
...Apache/2.2.6 (Mandriva Linux/PREFORK-8.2mdv2008.0) PHP/5.2.4 with
Suhosin-Patch mod_put/2.0.8 configured -- resuming normal
operations...
...PHP Parse error: syntax error, unexpected T_VARIABLE, expecting
',' or ';'...
The file was changed as follows which caused the parse error shown
above:
?php
$db = pg_connect('dbname=webcuttings user=httpd');
$query = 'SELECT * FROM articles';
$value=pg_fetch_result($query);
echo 'all files' $value;
?
The file was copied from the manual page, without understanding that
an array was being used.
The problem you've got there is a missing string concatenator in your
echo line. You should change that line to read:
echo 'all files' . $value;
note the . character? However, as you said, $value is actually an array,
so you would be better of using something like print_r() or var_dump()
on it.
--
Thanks,
Ash
http://www.ashleysheridan.co.uk
Re: [PHP] postgresql database access failure
The file was changed: ... $value=pg_fetch_result($query,1,1); echo 'all files' . var_dump($value); ... The resultant web page produces: bool(false) all files The php file was changed again: ... $value=pg_fetch_result($query); echo 'all files' . var_dump($value); ... The resultant web page produces: NULL all files The error log shows: ...PHP Warning: pg_fetch_result(): supplied argument is not a valid PostgreSQL result resource... The objective is to learn how to extract data from a database and print to a web browser, but not much progress made so far..! -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] postgresql database access failure
e-letter wrote: The file was changed: ... $value=pg_fetch_result($query,1,1); echo 'all files' . var_dump($value); ... The resultant web page produces: bool(false) all files The php file was changed again: ... $value=pg_fetch_result($query); echo 'all files' . var_dump($value); ... The resultant web page produces: NULL all files The error log shows: ...PHP Warning: pg_fetch_result(): supplied argument is not a valid PostgreSQL result resource... The objective is to learn how to extract data from a database and print to a web browser, but not much progress made so far..! There is a good example of how to use pg_fetch_result in the docs at http://php.net/manual/en/function.pg-fetch-result.php. On the basis of the code shown here, it's a bit hard to determine exactly what your problem is; however the odds are that the error supplied argument is not a valid PostgreSQL result resource results from a SQL syntax error, or possibly that you have failed to open a connection to pgsql. However, there are some tools to help you; see http://php.net/manual/en/function.pg-result-error.php For future reference, it helps to post all the code that is relevant to your problem, so in this case it would help, for example, to see how you are making the connection to pgsql and how the $query variable is populated. Cheers -- David Robley A seminar on Time Travel will be held two weeks ago. Today is Boomtime, the 49th day of Discord in the YOLD 3177. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] postgresql database access failure
Readers? Sounds like you spend too much time writing newsletters
(to the wrong address, since php-general-digest-h...@lists.php.net is
a self-help command list for digest-form subscriptions). ;-P
On Sat, Apr 30, 2011 at 04:41, e-letter inp...@gmail.com wrote:
?php
$db = pg_connect('dbname=databasename user=username');
$query = 'SELECT * FROM databasename';
$value=pg_fetch_result($query,1,0);
echo 'export of database is ',$value,'';
?
p
why does this fail?
How is it failing? What error(s) are you seeing on screen or in
your log files? Noting that $value would contain an array, is that
the problem? And why are you using ending quotes in your echo? You
should just place the semicolon immediately after $value.
/p
/body
/html
The following php code produces the user agent:
?php
echo '$_SERVER['HTTP_USER_AGENT']';
?
First of all, no it doesn't. Placed inside single quotes, it'll
not only try to return it verbatim (i.e. - the variable would be
printed to screen), but it'll also cause a parse error, as you reuse
single quotes in the variable key container.
--
/Daniel P. Brown
Network Infrastructure Manager
http://www.php.net/
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] postgresql database access failure
On Sat, Apr 30, 2011 at 12:23, Daniel Brown danbr...@php.net wrote: ?php echo '$_SERVER['HTTP_USER_AGENT']'; ? First of all, no it doesn't. Placed inside single quotes, it'll not only try to return it verbatim (i.e. - the variable would be printed to screen), but it'll also cause a parse error, as you reuse single quotes in the variable key container. Forgot the second of all before hitting send. Second of all, what does this have to do with your PostgreSQL problem? Did I miss something? -- /Daniel P. Brown Network Infrastructure Manager http://www.php.net/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PostgreSQL and select nextval
Alain Roger wrote:
Hi,
I'm getting an error message when i run the following SQL request :
$sql = INSERT INTO tmp_importedxls (rec_id, publisher) VALUES (SELECT
nextval('tmp_importedxls_rec_id_seq'),'$pb');
Error in SQL query: ERROR: syntax error at or near SELECT LINE 2: VALUES
(SELECT nextval('tmp_importedxls_rec_id_seq'),' ^
You don't use SELECT, just put nextval(...)
INSERT INTO tmp_importedxls (rec_id, publisher) VALUES
(nextval('tmp_importedxls_rec_id_seq'),'$pb')
Luck!
--
21:50:04 up 2 days, 9:07, 0 users, load average: 0.92, 0.37, 0.18
-
Lic. Martín Marqués | SELECT 'mmarques' ||
Centro de Telemática| '@' || 'unl.edu.ar';
Universidad Nacional| DBA, Programador,
del Litoral | Administrador
-
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PostgreSQL and select nextval
Richard Lynch wrote:
On Sun, August 12, 2007 2:35 am, Alain Roger wrote:
I'm getting an error message when i run the following SQL request :
$sql = INSERT INTO tmp_importedxls (rec_id, publisher) VALUES (SELECT
nextval('tmp_importedxls_rec_id_seq'),'$pb');
Error in SQL query: ERROR: syntax error at or near SELECT LINE 2:
VALUES
(SELECT nextval('tmp_importedxls_rec_id_seq'),' ^
I have the feeling that we can not use the select nextval(...) SQL
request
in an INSERT INTO one under PHP.
Is it true?
No.
PHP doesn't care diddly-squat what is in your query -- It just sends
it to PostgreSQL.
The query you have written just plain won't work in PostgreSQL, period.
Try it in the psql monitor.
OT:
Almost for sure, you just need to strip out the VALUES ( bit and the
closing paren for it.
Nop. If you don't put the VALUES ( you are passing a query with
applyable values for the table in the INSERT.
He only wants the next value in the sequence. In that case he just has
to not put the SELECT.
--
21:50:04 up 2 days, 9:07, 0 users, load average: 0.92, 0.37, 0.18
-
Lic. Martín Marqués | SELECT 'mmarques' ||
Centro de Telemática| '@' || 'unl.edu.ar';
Universidad Nacional| DBA, Programador,
del Litoral | Administrador
-
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PostgreSQL and select nextval
On Sun, August 12, 2007 2:35 am, Alain Roger wrote:
I'm getting an error message when i run the following SQL request :
$sql = INSERT INTO tmp_importedxls (rec_id, publisher) VALUES (SELECT
nextval('tmp_importedxls_rec_id_seq'),'$pb');
Error in SQL query: ERROR: syntax error at or near SELECT LINE 2:
VALUES
(SELECT nextval('tmp_importedxls_rec_id_seq'),' ^
I have the feeling that we can not use the select nextval(...) SQL
request
in an INSERT INTO one under PHP.
Is it true?
No.
PHP doesn't care diddly-squat what is in your query -- It just sends
it to PostgreSQL.
The query you have written just plain won't work in PostgreSQL, period.
Try it in the psql monitor.
OT:
Almost for sure, you just need to strip out the VALUES ( bit and the
closing paren for it.
--
Some people have a gift link here.
Know what I want?
I want you to buy a CD from some indie artist.
http://cdbaby.com/browse/from/lynch
Yeah, I get a buck. So?
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PostgreSQL and select nextval
Alain Roger wrote:
Hi,
I'm getting an error message when i run the following SQL request :
$sql = INSERT INTO tmp_importedxls (rec_id, publisher) VALUES (SELECT
nextval('tmp_importedxls_rec_id_seq'),'$pb');
Error in SQL query: ERROR: syntax error at or near SELECT LINE 2: VALUES
(SELECT nextval('tmp_importedxls_rec_id_seq'),' ^
I have the feeling that we can not use the select nextval(...) SQL request
in an INSERT INTO one under PHP.
Is it true?
If rec_id is a SERIAL it will increment itself:
INSERT INTO tmp_importedxls (publisher) VALUES ('$pb');
brian
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] postgreSQl and images
On Mon, November 6, 2006 5:17 pm, Børge Holen wrote: aaight... I get yer point there, BUT you see, what do you do when an artists changes it name... forget it, that was a bad choice... Oh no, it's a GREAT choice. First of all, if they really really changed their name, then I create a new record for them, and they have 2 Artist Profiles, so I can maintain historical accuracy. E.g., if Prince had played our venue every year, I'd want the shows in 1993-- to be 'Prince' and then from 1993 to 2000 to be 'tafkap' and then I'd be using the same pre-1993 record again from 2000 onwards. (Assuming his name does not change again.) I have done this many many many times for artist who marry, rename their bands, have side projects or perform solo as well as with their band, or suffer some other identity crisis. If you've worked with musicians, you would know that all of the above are not uncommon experiences. :-) If it's just a typo in an artist name, I just fix it. But the artist name is the ONE field we simply do not let the artist have access to. They'd end up destroying the historical accuracy of our web calendar listing their gigs back to 2003 (and earlier, if I ever get the owner to drag in his dead-tree bookings calendars so I can input them...) And since the ID3 tag comes from the DB in real-time, pre-pended to the mp3 stream with the static audio portion of the mp3 following, the actual output MP3 stream is corrected for the artist name as soon as I fix the typo. PHP ID3 rocks. :-) Actually, if you download the same MP3 file 2 successive times from my server, and if the artist has multiple images, you could trivially prove that I change the stream on the fly, as I choose the image at random. I think Rachel Sage has the most images in there, so that would be a good choice, if she has turned on downloads in her account. That's why only a tiny fraction of the 65000 mp3s are available. Artists have to login, choose the tracks and their usage parameters (streaming, download, radio, hifi, lofi, cafe speaker system) and they have to sign a release form. [aside] The release form is the worst possible non-contract I could manage to draft, on purpose, to avoid locking the artist into anything, while keeping the cafe lawyer happy -- Dang thing doesn't even have a DATE on it, much less read as an enforcable contract. So your version of the MP3 and mine will not match on our browser, wrt the ID3 meta-data being different images. The static audio portion will remain the same, of course. anyway... you see, in one of my fields of interests, you got dogs... see, dogs can change name, not just the calling name, but I mean completely change it all. second, they change apperance with growth. So to keep track on stuff you need to make a system work for you, not the other way around... Hence, all in the db... but no way someone would be * enought to put ... you know what.. In that case, I'd suggest a series of related records for each dog tied back to some unique ID. If there is an industry-standard ID for a dog that never changes throughout the course of its life, tie that into the central ID, or just make one up and convince users to tie their records together correctly through the business logic. I don't even attempt to map out which artists are really the same as which other artists, as the can of worms and social issues this would open up are legion. Prince/tafka looks a mere piker in this regard compared to some of the data I'd have to deal with... I'll pick a funny story of an artist, just as an example... Ken Vandermark was interviewed a couple years ago, after winning the McArthur Foundation genius grant (sic) and they got to talking about all the bands he was in. He's a free/improv jazz artist, plays mostly saxophone. Anyway, the interviewer asked him just how many bands he was in. Ken's answer was something not unlike this: I don't know. You'd have to track down all the bands I ever played in, and ask them [shrug]. If we played together every 2 or 3 years for awhile, but it's been 5 years now since we've been together, are we still a band? I dunno. I guess it just depends on whether we make plans to play together again or not. Call it about 500 bands, give or take. Now Ken's an extreme example, but these and similar issues turn out to be pretty non-trivial, once you start digging into it. NOone would EVER put a single mp3 file in a db ;) lol. There we agree. I don't know that nobody would ever put a single MP3 in a db. They might have a zillion teeny tiny MP3 files that they can more efficiently serve up from the DB, and have the research and stats to prove that it's better on their system. But if you have to ask the question about this matter, I'd say putting the data in your file-system is the Right Answer. :-) -- Some people have a gift link here. Know what I want? I want you to buy a CD from some starving artist. http://cdbaby.com/browse/from/lynch Yeah, I
Re: [PHP] postgreSQl and images
On Sat, November 4, 2006 11:26 am, Alain Roger wrote: I create a table with some large object (ref: OID) to store some images. When my PHP will display some data, it will also display the images stored as OID. However, i've read that before i must restore the image by exporting them to local (on server) file. isn't it easier in this case, to simply store the path and file name of file to DB and just read the data to display image on PHP pages ? what is the purpose in this case to store image a bytea / large object ? moreover, how my php code will load image from DB and stored there as OID ? isn't it too much complex in comparison with just storing path and name of image ? Yes, it will probably be easier all around to de-clutter your DB and just store the images on the file system, which is itself a highly-tuned mass-storage specialized database system. :-) You would *NOT* need to store the file temporarily on the hard drive: ?php $image_data = some_function_to_get_image_date_from_OID($OID); header(Content-type: image/jpeg); echo $image_data; ? No temp file needed. This DB versus file system for images has been debated on this list before. Check the archives. -- Some people have a gift link here. Know what I want? I want you to buy a CD from some starving artist. http://cdbaby.com/browse/from/lynch Yeah, I get a buck. So? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] postgreSQl and images
On Sat, November 4, 2006 5:38 pm, Børge Holen wrote: either you end up with a had as method of grouping them together, moreover you can have thousands of small files inside one dir with an id name to it, and yes the last one, thousands of directories with one file inside... Speaking as a guy who has 65,000+ mp3s on-line (though only a fraction of them available to the general public) I'd go crazy if they were all in the DB, not to mention that my webhost would kill me... But I'm not dumb enough to put them all in one directory either. foobar.mp3 goes in /f/o/foobar.mp3 Actually, which *drive* it is on is determined by the date the audio was recorded, as I have about a Terabyte available spread across 4 cheap IDE drives, and just change a simple include file when one of the drives is nearly full to start using up the next one. So it really turnes into one of these: drive1/f/o/foobar.mp3 drive2/f/o/foobar.mp3 drive3/f/o/foobar.mp3 . . . based on what date the live performance occurred, which is in my meta-data, which I need to get for the on-the-fly ID3 tags anyway. [*] It's all very crude and shoestring budget, but it works. Well, except until they turned off the A/C in the office last summer, and then spilled coffee grounds all over the box... :-( But I found an old computer in the closet that I had found in a dumpster and threw the hard drives in that, and it works. 495 MHz seems enough for my audio server. :-) I'm finishing up a process of copying the files to a real host so I'll have a crude 2-tier fail-over. Anyway, you have to plan this out with some idea of what scale and scope you are dealing with to avoid insanity, but cramming binary data into the DB seems like the least attractive choice to me personally. [*] It's a shame the MP3 players all seem to ignore my nifty JPEG ID3 data I'm pre-pending to the audio streams. You have to download the files just to see the artist photo. Sigh. PS Feel free to give a listen if you like accoustic music: http://uncommonground.com/radio_hifi.m3u http://uncommonground.com/radio_lofi.m3u (hifi, lofi, respectively, obviously) iTunes Podcast version is in beta if you're interested in being a beta-tester... Same audio, just in RSS/XML format. -- Some people have a gift link here. Know what I want? I want you to buy a CD from some starving artist. http://cdbaby.com/browse/from/lynch Yeah, I get a buck. So? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] postgreSQl and images
aaight... I get yer point there, BUT you see, what do you do when an artists changes it name... forget it, that was a bad choice... anyway... you see, in one of my fields of interests, you got dogs... see, dogs can change name, not just the calling name, but I mean completely change it all. second, they change apperance with growth. So to keep track on stuff you need to make a system work for you, not the other way around... Hence, all in the db... but no way someone would be * enought to put ... you know what.. NOone would EVER put a single mp3 file in a db ;) lol. There we agree. My problem originated with the planning... or rather lack of. Now? I think it works flawlessly so why fix it On Monday 06 November 2006 23:00, Richard Lynch wrote: On Sat, November 4, 2006 5:38 pm, Børge Holen wrote: either you end up with a had as method of grouping them together, moreover you can have thousands of small files inside one dir with an id name to it, and yes the last one, thousands of directories with one file inside... Speaking as a guy who has 65,000+ mp3s on-line (though only a fraction of them available to the general public) I'd go crazy if they were all in the DB, not to mention that my webhost would kill me... But I'm not dumb enough to put them all in one directory either. foobar.mp3 goes in /f/o/foobar.mp3 Actually, which *drive* it is on is determined by the date the audio was recorded, as I have about a Terabyte available spread across 4 cheap IDE drives, and just change a simple include file when one of the drives is nearly full to start using up the next one. So it really turnes into one of these: drive1/f/o/foobar.mp3 drive2/f/o/foobar.mp3 drive3/f/o/foobar.mp3 . . . based on what date the live performance occurred, which is in my meta-data, which I need to get for the on-the-fly ID3 tags anyway. [*] It's all very crude and shoestring budget, but it works. Well, except until they turned off the A/C in the office last summer, and then spilled coffee grounds all over the box... :-( But I found an old computer in the closet that I had found in a dumpster and threw the hard drives in that, and it works. 495 MHz seems enough for my audio server. :-) I'm finishing up a process of copying the files to a real host so I'll have a crude 2-tier fail-over. Anyway, you have to plan this out with some idea of what scale and scope you are dealing with to avoid insanity, but cramming binary data into the DB seems like the least attractive choice to me personally. [*] It's a shame the MP3 players all seem to ignore my nifty JPEG ID3 data I'm pre-pending to the audio streams. You have to download the files just to see the artist photo. Sigh. PS Feel free to give a listen if you like accoustic music: http://uncommonground.com/radio_hifi.m3u http://uncommonground.com/radio_lofi.m3u (hifi, lofi, respectively, obviously) iTunes Podcast version is in beta if you're interested in being a beta-tester... Same audio, just in RSS/XML format. -- --- Børge Kennel Arivene http://www.arivene.net --- -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] postgreSQl and images
On Saturday 04 November 2006 18:26, Alain Roger wrote: Hi, I create a table with some large object (ref: OID) to store some images. When my PHP will display some data, it will also display the images stored as OID. However, i've read that before i must restore the image by exporting them to local (on server) file. No you don't. isn't it easier in this case, to simply store the path and file name of file to DB and just read the data to display image on PHP pages ? depends. what is the purpose in this case to store image a bytea / large object ? Keeping track af thousands of small images... in my case anyway. makes it very easy to keep track of what you got'n not. moreover, how my php code will load image from DB and stored there as OID ? the usual way? isn't it too much complex in comparison with just storing path and name of image ? depends again. You wouldn't be in my shoes storing all those small images on the fs and then keep track of them. either you end up with a had as method of grouping them together, moreover you can have thousands of small files inside one dir with an id name to it, and yes the last one, thousands of directories with one file inside... no, no and nono thanks a lot Al. -- --- Børge Kennel Arivene http://www.arivene.net --- -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Postgresql PHP
Hello Danny, Tuesday, March 22, 2005, 6:07:55 PM, you wrote: DB Any recommendations on books for postgresql PHP usage. Just get a good book on Postgres, the PHP side of it can be easily picked-up from the PHP manual itself. Best regards, Richard Davey -- http://www.launchcode.co.uk - PHP Development Services I do not fear computers. I fear the lack of them. - Isaac Asimov -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PostgreSQL/PHP: transactions: how-to abstract out?
Not exactly sure why your transactions aren't working but if your script
already has an open connection to the database and you issue another
call to pg_connect with the same connect string PHP will return the
existing connection and should not create another connection to the
database, that is provided you do not pg_close the connection between
calls.
Manual Excerpt: If a second call is made to pg_connect() with the same
connection_string, no new connection will be established, but instead,
the connection resource of the already opened connection will be
returned. You can have multiple connections to the same database if you
use different connection string.
The manual page for pg_connect is:
http://www.php.net/manual/en/function.pg-connect.php
Storing your db connection as a global will work.
On Thu, 2003-01-09 at 21:56, Jean-Christian Imbeault wrote:
Hi. I thought I had abstracted out the SQL querying part of my code out,
just to find out today that it doesn't work when it comes to
transactions. I had come up with this code:
function sql_query($sql) {
$conn = pg_connect(dbname=JC user=postgres);
$res = pg_exec($conn, $sql);
if (!$res) {
echo CONNECTION: could not execute query ($sql)br;
die;
}
else return $res;
}
I had transactions in my code implemented like this:
$sql = BEGIN;
sql_query($sql);
[some sql that should be in a transaction ...]
$sql = COMMIT;
sql_query($sql);
This doesn't work. Now that I look at my code I clearly see why. All sql
queries are executed using a new Postgres connection, hence the use of
BEGIN/COMMIT as I was using them have no effect.
Can someone recommend a way to abstract out my DB layer while still
being able to use transactions?
I was thinking of using the same function but if the incoming query
contained the word BEGIN, saving that and all future queries in a
session var and when the COMMIT comes in executing all the saved queries
as one (i.e. BEGIN;[];COMMIT). One drawback is that all queries
will be written out to disk (as session vars) and that will slow things
down. Another drawback is that I have to abort if not COMMIT comes in.
And a few more drawbacks ...
I was also thinking about maybe the $sql a GLOBAL or first building up
my query as as long string (BEGIN;[];COMMIT) and *then* sending it
to my sql_query() function.
The last two seem easier to implement, safer, and more efficient but
they don't seem elegant because I haven't abstracted out the fact that
I want a transaction. Whenever I write an SQL query I have to think
does this need to be in a transaction and then use a different coding
technique depending on the answer. And if the future something that
didn't need to be in a transaction now needs to be in a transaction I
have to revisit my code and change the code.
I'm sure someone out there must have thought about this and come up with
an elegant solution and way of abstracting out the DB layer from PHP.
Can anyone share their solution with me or give me some pointers to
reference material?
Thanks,
Jc
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PostgreSQL/PHP: transactions: how-to abstract out?
Jason Sheets wrote:
Manual Excerpt: If a second call is made to pg_connect() with the same
connection_string, no new connection will be established, but instead,
the connection resource of the already opened connection will be
returned. You can have multiple connections to the same database if you
use different connection string.
You're right! I did some more testing and the problem is with my testing
code. I don't know why but the following code times out *but*, PHP
throws an error saying the code has timed out *but* calling
connection_status() says the code did *not* time out!
Any idea why connection_status() returns 0 when it should return 2??
My code:
set_time_limit(2);
echo set execution limit to 2 seconds BR;
register_shutdown_function(timed_out);
require_once(db_functions/sql_query.inc);
$sql = BEGIN;;
$res = sql_query($sql);
$sql = insert into test(test) values('testing 4');;
$res = sql_query($sql);
//This will cause the script to time out
$i = 0;
while(true) {$i++;}
$sql = COMMIT;;
$res = sql_query($sql);
function timed_out() {
$status = connection_status();
if ($status == 2) {
echo the script timed out BR;
}
else echo no time out. Connection status is $status BR;
}
The OUPUT:
set execution limit to 2 seconds
Fatal error: Maximum execution time of 2 seconds exceeded in
/www/htdocs/jc/shut.php on line 16
no time out. Connection status is 0
Jc
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PostgreSQL/PHP: transactions: how-to abstract out?
Jean-Christian
If you are only doing an insert then you do not need the transactions
BEGIN and COMMIT because that is already done for you on a single
insert. PGSQL is transaction based so if it does not go then it will not
work.
-Ray
On Thu, 2003-01-09 at 22:23, Jean-Christian Imbeault wrote:
Jason Sheets wrote:
Manual Excerpt: If a second call is made to pg_connect() with the same
connection_string, no new connection will be established, but instead,
the connection resource of the already opened connection will be
returned. You can have multiple connections to the same database if you
use different connection string.
You're right! I did some more testing and the problem is with my testing
code. I don't know why but the following code times out *but*, PHP
throws an error saying the code has timed out *but* calling
connection_status() says the code did *not* time out!
Any idea why connection_status() returns 0 when it should return 2??
My code:
set_time_limit(2);
echo set execution limit to 2 seconds BR;
register_shutdown_function(timed_out);
require_once(db_functions/sql_query.inc);
$sql = BEGIN;;
$res = sql_query($sql);
$sql = insert into test(test) values('testing 4');;
$res = sql_query($sql);
//This will cause the script to time out
$i = 0;
while(true) {$i++;}
$sql = COMMIT;;
$res = sql_query($sql);
function timed_out() {
$status = connection_status();
if ($status == 2) {
echo the script timed out BR;
}
else echo no time out. Connection status is $status BR;
}
The OUPUT:
set execution limit to 2 seconds
Fatal error: Maximum execution time of 2 seconds exceeded in
/www/htdocs/jc/shut.php on line 16
no time out. Connection status is 0
Jc
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PostgreSQL/PHP: transactions: how-to abstract out?
You could try leaving off the ;...
Try $sql = BEGIN
Try $sql = COMMIT
That should work...
On Thu, 2003-01-09 at 22:23, Jean-Christian Imbeault wrote:
Jason Sheets wrote:
Manual Excerpt: If a second call is made to pg_connect() with the same
connection_string, no new connection will be established, but instead,
the connection resource of the already opened connection will be
returned. You can have multiple connections to the same database if you
use different connection string.
You're right! I did some more testing and the problem is with my testing
code. I don't know why but the following code times out *but*, PHP
throws an error saying the code has timed out *but* calling
connection_status() says the code did *not* time out!
Any idea why connection_status() returns 0 when it should return 2??
My code:
set_time_limit(2);
echo set execution limit to 2 seconds BR;
register_shutdown_function(timed_out);
require_once(db_functions/sql_query.inc);
$sql = BEGIN;;
$res = sql_query($sql);
$sql = insert into test(test) values('testing 4');;
$res = sql_query($sql);
//This will cause the script to time out
$i = 0;
while(true) {$i++;}
$sql = COMMIT;;
$res = sql_query($sql);
function timed_out() {
$status = connection_status();
if ($status == 2) {
echo the script timed out BR;
}
else echo no time out. Connection status is $status BR;
}
The OUPUT:
set execution limit to 2 seconds
Fatal error: Maximum execution time of 2 seconds exceeded in
/www/htdocs/jc/shut.php on line 16
no time out. Connection status is 0
Jc
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PostgreSQL
[EMAIL PROTECTED] wrote: PHP is compiled with PostgreSQL support but I can not connect. I get this error message What does it mean and what do I need to do to fix it Warning: pg_connect() unable to connect to PostgreSQL server: connectDB() -- connect() failed: Connection refused Is the postmaster running (with -i) at 'localhost' and accepting connections on TCP/IP port '5583'? in /home/sites/home/web/test/test.php on line 2 either your postmaster is not running... or your postmaster does not accept TCP/IP connections... read the documentation for PostgreSQL Installation it has tons of info there for your convenience... try editing your pg_hba.conf file for postgreSQL... so it will accept tcp IP connections: host all IP 255.255.255.0 trust where IP is the ip address of the host that will access the postgreSQL database at the server. i hope i got it right -- Lejanson C. Go, SDE I -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PostgreSQL - WHILE loop working too well / WARNING: Unable to jump to row 2 on PostgreSQL...
On Thursday 30 May 2002 11:30, Steve G wrote:
Please do not reply to an existing post. Start a new one!
I do not understand why PHP is trying to pull row 2!! I want to create an
array of data and construct a table from a query. The echo of $rows shows
2 rows, so if I'm setting my variable to 0 and incrementing with each loop,
it doesn't make sense to me why it tries to pull row 2. It should stop at
row 1 because the next loop would make the variable 2 and the if says if
$ii is less than 2.
The if statement only runs once. At the time it is run the statement is true
(ie $ii 2) ...
WHAT AM I MISSING!?
I have a while loop written to pull data from a postgreSQL database. I
have 2 and only 2 rows of data in this table. Probably labeled within
PostgreSQL as '0' and '1'. The browser displays the error message along
with the proper table listed below it!!
... but inside your while loop you have $ii++, which results in ...
Warning: Unable to jump to row 2 on PostgreSQL result index 2 in
/var/www/html/steve/frontdoor.php on line 92
sgaas Steve Gaas
mjohnson Matt Johnson
Basically you're going about this the wrong way. Replace this:
?
if ( $ii $rows) {
while ($tabledata = pg_fetch_array($results, $ii)) {--(LINE 92)
$username = $tabledata[username];
$firstname = $tabledata[firstname];
$lastname = $tabledata[lastname];
echo TR\n;
echo td$username/td\n;
echo td$firstname/td\n;
echo td$lastname/td\n;
echo /tr\n;
$ii++;
}
}
?
with:
/** Untested use with extreme caution **/
while ($tabledata = pg_fetch_array($results)) {
$username = $tabledata[username];
$firstname = $tabledata[firstname];
$lastname = $tabledata[lastname];
echo TR\n;
echo td$username/td\n;
echo td$firstname/td\n;
echo td$lastname/td\n;
echo /tr\n;
}
--
Jason Wong - Gremlins Associates - www.gremlins.com.hk
Open Source Software Systems Integrators
* Web Design Hosting * Internet Intranet Applications Development *
/*
Occam's eraser:
The philosophical principle that even the simplest
solution is bound to have something wrong with it.
*/
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PostgreSQL query taking a long time
use EXPLAIN to get more information about how postgres executes this query.
I'm not sure about this, but there are some issues with fields of type int8.
It may help to cast explicitly the fields involved in join like:
WHERE bible.book::int4 = books.id::int4
Ask it the postgres mailing list, too!
btw, what version of pg do you have?
- Original Message -
From: K Old [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Friday, December 21, 2001 5:51 PM
Subject: [PHP] PostgreSQL query taking a long time
| Hello all,
|
| I have a PostgreSQL database that is storing The Bible. It has 31,103
| records in it and I have a PHP page executing this query:
| SELECT
| books.book_name, bible.chapter, bible.verse, bible.versetext
| FROM asv_bible bible, book_bible books WHERE bible.book = books.id ORDER BY
| random() LIMIT 1
|
| The database schema is:
|
| /*
| Sequences
| */
| CREATE SEQUENCE book_bible_seq start 1 increment 1 maxvalue 2147483647
| minvalue 1 cache 1;
|
| /*
| Table structure for table asv_bible
| */
| CREATE TABLE asv_bible (
|id int8 NOT NULL,
|book int8,
|chapter int8,
|verse int8,
|versetext text,
|CONSTRAINT asv_bible_pkey PRIMARY KEY (id)
| );
|
|
|
| /*
| Table structure for table book_bible
| */
| CREATE TABLE book_bible (
|id int4 DEFAULT nextval('book_bible_seq'::text) NOT NULL,
|book_name varchar(20),
|CONSTRAINT book_bible_pkey PRIMARY KEY (id)
| );
|
| Right now it takes 9 seconds to return the results. I was wondering if
| anyone could offer any help with lowering the time it takes to run?
|
| Or if this is the normal runtime for a database of this size, I'd just like
| confirmation.
|
| Thanks,
| Kevin
|
|
| _
| Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp.
|
|
| --
| PHP General Mailing List (http://www.php.net/)
| To unsubscribe, e-mail: [EMAIL PROTECTED]
| For additional commands, e-mail: [EMAIL PROTECTED]
| To contact the list administrators, e-mail: [EMAIL PROTECTED]
|
Re: [PHP] PostgreSQL query taking a long time
Hi,
You dont have the book field in the asv_bible table indexed. Use this to
index it
CREATE INDEX myindexname ON asv_bible(book);
(the primary key fields are ok because PostgreSQL creates a unique index to
implement the PRIMARY KEY constraint).
Hope that helps :)
--
Shane
I have a PostgreSQL database that is storing The Bible. It has 31,103
records in it and I have a PHP page executing this query:
SELECT
books.book_name, bible.chapter, bible.verse, bible.versetext
FROM asv_bible bible, book_bible books WHERE bible.book = books.id ORDER BY
random() LIMIT 1
The database schema is:
/*
Sequences
*/
CREATE SEQUENCE book_bible_seq start 1 increment 1 maxvalue 2147483647
minvalue 1 cache 1;
/*
Table structure for table asv_bible
*/
CREATE TABLE asv_bible (
id int8 NOT NULL,
book int8,
chapter int8,
verse int8,
versetext text,
CONSTRAINT asv_bible_pkey PRIMARY KEY (id)
);
/*
Table structure for table book_bible
*/
CREATE TABLE book_bible (
id int4 DEFAULT nextval('book_bible_seq'::text) NOT NULL,
book_name varchar(20),
CONSTRAINT book_bible_pkey PRIMARY KEY (id)
);
Right now it takes 9 seconds to return the results. I was wondering if
anyone could offer any help with lowering the time it takes to run?
Or if this is the normal runtime for a database of this size, I'd just like
confirmation.
Thanks,
Kevin
_
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] PostgreSQL connection problems
You need to configure you database in the pg_hba.conf. The pg_hba.conf is in your postgres directory. Put one line like this in your pg_hba.conf host yourdatabase yourhost 255.255.255.255 trust - Original Message - From: Alberto [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, September 27, 2001 7:51 AM Subject: [PHP] PostgreSQL connection problems Warning: Unable to connect to PostgreSQL server: No pg_hba.conf entry for host myhost, user myuser, database mydb in /path/to/principal.php on line 27 that's $bdConexion = pg_connect (host=.$bdHost. port=.$bdPuerto. dbname=.$bdBD. user=.$bdLogin. password=.$bdPassword); PHP is already compiled with PostgreSQL support. PostgreSQL is running on a remote server. Thnx in advance -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] PostgreSQL vs. Interbase
Hi Please discuss as to your point of view the advantage of PostgreSQL over Interbase and/or vise versa. I'm considering three (3) important points I'm just evaluating Interbase and there is an important point that did not come out in the thread. It seems that Borland went back on key commitments to the open source development team and lost their trust and goodwill. As a result, the source has forked, with most of the momentum behind the fully open source Firebird project. Borlands commitment to their own 'official' version looks pretty thin. You can find info on Firebird at: http://firebird.sourceforge.net http://www.ibphoenix.com http://www.interbase2000.com Things are at an early stage, but Firebird have already identified and fixed a major security hole during their security audit of the Borland code, and offer binary builds for a number of platforms. Looks like the Firebird fork is the one with the future. Geoff Caplan -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] PostgreSQL vs. Interbase
Altunergil, Oktaywrote: The link that goes to interbase's web site in freshmeat.com redirects to http://www.borland.com/interbase/ which does not list the product as free or open source? Is there another version? oktay My understanding is that the current open source version is available from: http://www.borland.com/devsupport/interbase/opensource/ Ongoing certified versions with Borland support will be available from somewhere on borland.com for a price and any future open source versions will be available from SourceForge. Cheers, Luke Welling -- PHP and MySQL Web Development by Luke Welling and Laura Thomson http://www.amazon.com/exec/obidos/ASIN/0672317842/tangledwebdesign -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] PostgreSQL vs. Interbase
Arnold Gamboa wrote: I'm currently in serious research on what database to use for a B2B site which is expected to hold millions of records. I have in so far considered two open source databases - Interbase and PostgreSQL. With this in mind, I'm sending this email to both the PostgreSQL and Interbase mailing lists and also on PHP's. I would appreciate what ever information you can provide. Thanks :) My inquiry: Please discuss as to your point of view the advantage of PostgreSQL over Interbase and/or vise versa. I'm considering three (3) important points 1. Speed 2. Data Reliability 3. Portability I first saw your message on the PostgreSQL list, but figured what I was going to answer belonged here more. One other thing you might like to consider is documentation. Interbase probably has better documentation than PostgreSQL from their respective primary sources, but you will find a *lot* more documentation on using PHP with PostgreSQL than with Interbase. Where PHP examples and documentation is database specific, it is mostly MySQL, some PostgreSQL, some Oracle and very little of any other. Cheers, Luke Welling -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP] PostgreSQL vs. Interbase
The link that goes to interbase's web site in freshmeat.com redirects to http://www.borland.com/interbase/ which does not list the product as free or open source? Is there another version? oktay -Original Message- From: Luke Welling [mailto:[EMAIL PROTECTED]] Sent: Monday, May 07, 2001 3:17 PM To: [EMAIL PROTECTED] Subject: Re: [PHP] PostgreSQL vs. Interbase Arnold Gamboa wrote: I'm currently in serious research on what database to use for a B2B site which is expected to hold millions of records. I have in so far considered two open source databases - Interbase and PostgreSQL. With this in mind, I'm sending this email to both the PostgreSQL and Interbase mailing lists and also on PHP's. I would appreciate what ever information you can provide. Thanks :) My inquiry: Please discuss as to your point of view the advantage of PostgreSQL over Interbase and/or vise versa. I'm considering three (3) important points 1. Speed 2. Data Reliability 3. Portability I first saw your message on the PostgreSQL list, but figured what I was going to answer belonged here more. One other thing you might like to consider is documentation. Interbase probably has better documentation than PostgreSQL from their respective primary sources, but you will find a *lot* more documentation on using PHP with PostgreSQL than with Interbase. Where PHP examples and documentation is database specific, it is mostly MySQL, some PostgreSQL, some Oracle and very little of any other. Cheers, Luke Welling -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] PostgreSQL vs InterBase
On Friday 02 March 2001 00:23, Shaun Thomas wrote: On Thu, 1 Mar 2001, Meir kriheli wrote: I use both of the databases (Interbase 6.01 and PostgreSQL 7.1beta4). PostgreSQL has more features comapared to Interbase (the procedureal language is very robust and there are many datatyps to choose from. Also you can have some kind of object support in it to inherit tables for example), but it's windows implemenation is very hard (at least for me, I like to compile it). Postgres also has a nasty show-stopping bug they don't seem to want to fix. Try making a stored procedure with many parameters, and send a single null to it. I dare you. I so love having all of my other parameters, and the return value of the function turned into null because postgres can't tell where a null occoured. I don't like stored procedures, or functions in any programming language that accepts many parameters, and I avoid the as much as I can. Those things are hard to debug and write (hmm, what paraemeters should go here ? :-( ) and they hinder the readability of the code. As for null values, I don't use them as well, I work with different databases and each one has their quirks about null. Basiclly null is undefined and should stay that way. Usually I decide on an invalid value (such as -1) and pass it to the function. I also love the fact that you can't drop foreign keys, modify columns, drop columns without rebuilding the entire table, etc. We use it here, but it makes me want to pull my hair out. If someone would just combine postgres and mysql, we'd have the best database in the universe. Fast and stable, with all of the RDBMS anyone could want. Well I can do all of this in mysql because there's no referential integrity. You can't drop or modify a column which is reference by a foreign key, beacuse that would break the integrity. You don't need to rebuld the table, just drop the foriegn key and off you go. But as it stands, postgres is still a bit player with an incomplete feature set. But I don't want to start a holy war here, so I'll drop it. OK. As for speed both are very fast (even when compared to commerical DB, in my tests the deafult install of Interbase outperfomed the default install of Oracle 8i about 10X, tested on P166 with 96MB and PII400 with 192MB). This only occours if you don't know how to optimize Oracle. Oracle is *very* picky about *everything*. You need index tablespaces on separate disks from the data tablespaces, and yet another one for system tablespaces. You should also have one for archive logs, redo logs, and of course your temporary tables. Setting it to threading mode is also nice for connection pooling and to stop killing your machine under heavy load. That, and the machines you've quoted are in no way powerful enough for production Oracle databases, period. You should also run oracle on some kind of Solaris/Sun combo. Raw mount points direct to the actual disks is ideal, but loopback filesystems work just as well. Remember to cluster your raid into 4 - 6 arrays of 3+ disks, too. There is no such thing as a default Oracle install, because installing oracle on a single user machine with one disk and only a little ram (yes, anything under 512 is very little for Oracle) will make Oracle look like a piece of crap. The point about Oracle is that it *lets* you do all of those optimizations, and if you're good at it, it will outperform almost any other database you throw at it. Trust me on this one. Well let me see, 1. I have several databases to choose from, Which give me the performance I need. 2. I don't have to be a DBA to manage them, I don't have to use raids to get performance and I don't have to split indexes between hard disks to get adaquete preformence. 3. I don't a supercomputer and I can use them in moderate hardware for my needs, and I don't have to dedicate plenty of RAM to them. 4. This databases are open-source and don't have linking problems. Anyone tried to install Oracle 8.0.5 on Linux (patch-O-rama for Glibc). 8i is a resource hog (JVM in the database, are they nuts ?) and i won;t touch it with a stick, 5. I can expect this databases to work for me out of the box (out of the install in this case) 6. All this databases are free and I can choose the one I want according to the task I'm facing, without paying outrages licensing fee to Oracle, and spending lots of money on unnedded hardware. Isn't the choice obvious ? Why would anyone touch Oracle - I guess it is only because of their hype and spin - much like some other company I won't mention. BTW, I was in Oracle's OPP program for quite a while, but left it when 8i came out, it was an overkill. (But I sure miss those Oracle parties, They were outrages, or as one of their representives said to me, they had lots of money the needed to spend). OK, I'll stop now, I'm running out of air, I think I'm turning blue, Help !! Help !! someone
Re: [PHP] PostgreSQL vs InterBase
On Wednesday 28 February 2001 06:19, [EMAIL PROTECTED] wrote: Arnold Gamboa wrote: I hve heard a great deal about InterBase. Please comment on which is better: 1. Speed 2. Data Reliability 3. Compatibility with PHP Thanks for your comments. -- 1. I think Postgresql 2 and 3 I can't say which is better. Both seem to work ok - you might want to search for postgresql and persistent connections in the archives - seems they aren't perfect yet. I use both of the databases (Interbase 6.01 and PostgreSQL 7.1beta4). PostgreSQL has more features comapared to Interbase (the procedureal language is very robust and there are many datatyps to choose from. Also you can have some kind of object support in it to inherit tables for example), but it's windows implemenation is very hard (at least for me, I like to compile it). As for speed both are very fast (even when compared to commerical DB, in my tests the deafult install of Interbase outperfomed the default install of Oracle 8i about 10X, tested on P166 with 96MB and PII400 with 192MB). -- Meir Kriheli There's someone in my head, but it's not me - Pink Floyd -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] PostgreSQL vs InterBase
On Thu, 1 Mar 2001, Meir kriheli wrote: I use both of the databases (Interbase 6.01 and PostgreSQL 7.1beta4). PostgreSQL has more features comapared to Interbase (the procedureal language is very robust and there are many datatyps to choose from. Also you can have some kind of object support in it to inherit tables for example), but it's windows implemenation is very hard (at least for me, I like to compile it). Postgres also has a nasty show-stopping bug they don't seem to want to fix. Try making a stored procedure with many parameters, and send a single null to it. I dare you. I so love having all of my other parameters, and the return value of the function turned into null because postgres can't tell where a null occoured. I also love the fact that you can't drop foreign keys, modify columns, drop columns without rebuilding the entire table, etc. We use it here, but it makes me want to pull my hair out. If someone would just combine postgres and mysql, we'd have the best database in the universe. Fast and stable, with all of the RDBMS anyone could want. But as it stands, postgres is still a bit player with an incomplete feature set. But I don't want to start a holy war here, so I'll drop it. As for speed both are very fast (even when compared to commerical DB, in my tests the deafult install of Interbase outperfomed the default install of Oracle 8i about 10X, tested on P166 with 96MB and PII400 with 192MB). This only occours if you don't know how to optimize Oracle. Oracle is *very* picky about *everything*. You need index tablespaces on separate disks from the data tablespaces, and yet another one for system tablespaces. You should also have one for archive logs, redo logs, and of course your temporary tables. Setting it to threading mode is also nice for connection pooling and to stop killing your machine under heavy load. That, and the machines you've quoted are in no way powerful enough for production Oracle databases, period. You should also run oracle on some kind of Solaris/Sun combo. Raw mount points direct to the actual disks is ideal, but loopback filesystems work just as well. Remember to cluster your raid into 4 - 6 arrays of 3+ disks, too. There is no such thing as a default Oracle install, because installing oracle on a single user machine with one disk and only a little ram (yes, anything under 512 is very little for Oracle) will make Oracle look like a piece of crap. The point about Oracle is that it *lets* you do all of those optimizations, and if you're good at it, it will outperform almost any other database you throw at it. Trust me on this one. -- +-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+ | Shaun M. ThomasINN Database Programmer | | Phone: (309) 743-0812 Fax : (309) 743-0830| | Email: [EMAIL PROTECTED]AIM : trifthen | | Web : hamster.lee.net | | | | "Most of our lives are about proving something, either to | | "ourselves or to someone else." | | -- Anonymous | +-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] PostgreSQL vs InterBase
From: "Shaun Thomas" [EMAIL PROTECTED] The point about Oracle is that it *lets* you do all of those optimizations, and if you're good at it, it will outperform almost any other database you throw at it. Trust me on this one. I'm sorry, I can't trust you very much on this. Having worked as a high-end Oracle tuner on a 1 terabyte database for 3 years and having recently been involved in a new DB2 app, I find DB2 to be as good if not better than Oracle in very many important ways. Not even including a cost that's about 1/4 of a highly discounted Oracle cost model for an identical HA envioronment. Mark C. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] PostgreSQL vs InterBase
Hi We have been using Interbase for all our php development and have not experienced problems. The oprational database is 4gigs and if we execute large queries on it we recieve sub-second responses so speed is definetly not a problem Interbase is a good way to go if you have need for a relational database and of course a bonus is that it is free :) - Original Message - From: Arnold Gamboa [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, February 27, 2001 11:00 AM Subject: [PHP] PostgreSQL vs InterBase I hve heard a great deal about InterBase. Please comment on which is better: 1. Speed 2. Data Reliability 3. Compatibility with PHP Thanks for your comments. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] PostgreSQL vs InterBase
I know this isn't one of the databases that you;re inquiring about, but we've been using MySQL (www.mysql.com) and it has been working beautifully. We have an email server with a MySQL db that's about 4 Gigs now a it's still running strong. MySQL is free and might be worth looking into :-) Arnold Gamboa wrote: I hve heard a great deal about InterBase. Please comment on which is better: 1. Speed 2. Data Reliability 3. Compatibility with PHP Thanks for your comments. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- 209 Media http://www.209media.com> Ron Wills [EMAIL PROTECTED]> Programmer -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] PostgreSQL vs InterBase
Arnold Gamboa wrote: I hve heard a great deal about InterBase. Please comment on which is better: 1. Speed 2. Data Reliability 3. Compatibility with PHP Thanks for your comments. -- 1. I think Postgresql 2 and 3 I can't say which is better. Both seem to work ok - you might want to search for postgresql and persistent connections in the archives - seems they aren't perfect yet. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] Postgresql session handling
I [EMAIL PROTECTED] wrote: Can someone give me a simple example script that uses postgres session handling, that works with register_globals "off"? As a followup again, it seems what I've been running into is a bug in PHP: http://bugs.php.net/bugs.php?id=8772 http://bugs.php.net/bugs.php?id=9002 So, hopefully, someday, it will be fixed. -- Steve [EMAIL PROTECTED] Now playing: Moon and Sun Part II: North's Son (Amorphis - "Black Winter Day") -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] Postgresql session handling
I [EMAIL PROTECTED] wrote: It seems the pgsql_session_write() function is not even being invoked. Here it is: As a followup, it seems that turning register_globals "on" allows the pgsql_session_write() function to be called, and my test script works if I replace $HTTP_SESSION_VARS["count"] with $count. However, my environment is such that I have register_globals "off", which seems to conflict with sessions. In fact, I even get a Undefined variable: HTTP_SESSION_VARS error for the line $HTTP_SESSION_VARS["count"]++; Can someone give me a simple example script that uses postgres session handling, that works with register_globals "off"? Thanks, -- Steve [EMAIL PROTECTED] "And when you walk in golden halls, you get to keep the gold that falls" -- Black Sabbath, "Heaven and Hell" -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] Postgresql session handling
The writing of the session data occurs *after* the server-browser HTTP
connection is cut.
If you have any error-reporting happening in your session_write function,
you won't see it.
Alter that function to log errors to a file or something.
--
Visit the Zend Store at http://www.zend.com/store/
Wanna help me out? Like Music? Buy a CD: http://l-i-e.com/artists.htm
Volunteer a little time: http://chatmusic.com/volunteer.htm
- Original Message -
From: Bolt Thrower [EMAIL PROTECTED]
Newsgroups: php.general
Sent: Tuesday, February 20, 2001 12:45 PM
Subject: [PHP] Postgresql session handling
PHP 4.04pl1, PostgresQL 7.0.3 on a RedHat 6.2 system.
I'm trying to get session data to be stored in a
postgres table. I'm using the pgsql session handler from
http://www.csh.rit.edu/~jon/projects/php/pgsql_session_handler/, and I
think I've got it set up correctly, but I thought I'd ask here first.
With the following test script
+-
| ? include("pgsql_session_handler.inc"); ?
| ?
| session_start();
| session_register("count");
| $HTTP_SESSION_VARS[count]++;
| ?
| html
| head
| titleTest page/title
| /head
| body
| Hello! You've been here ?= $HTTP_SESSION_VARS[count]; ? times!br
|
| ? print "To continue, A HREF=\"test-session.php\"click here/a"; ?
|
| /body
| /html
+-
I can see php querying the database for session data (in the postgres
logs), but never writing session data to the database. Thus, the
counter is always '1'.
Any ideas where I'm going wrong? Why wouldn't session data get
written to the database?
Thanks,
--
Steve [EMAIL PROTECTED]
Now playing: Five Magics
(Megadeth - "Rust In Peace")
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] Postgresql session handling
"Richard Lynch" [EMAIL PROTECTED] wrote:
The writing of the session data occurs *after* the server-browser HTTP
connection is cut.
If you have any error-reporting happening in your session_write function,
you won't see it.
Alter that function to log errors to a file or something.
It seems the pgsql_session_write() function is not even being invoked.
Here it is:
+-
| function pgsql_session_write($key, $val)
| {
| /* debugging */
| $fp = fopen("/tmp/phpdebugwrite","w") or die ("can't open file");
| fwrite($fp, "HERE");
| fclose($fp);
| /* end debug */
|
| global $pgsql_session_handle, $pgsql_session_table;
|
| $key = addslashes($key);
| $val = addslashes($val);
| $now = time();
|
|
|
| /*
| * Delete any existing data for this session and then insert a new row
| * containing the current session data, all in a single transaction.
| * This should prevent collisions between multiple session instances.
| *
| * Thanks to "Will Fitzgerald" [EMAIL PROTECTED].
| */
| $query = 'begin; ';
| $query .= "delete from $pgsql_session_table where session_id = '$key'; ";
| $query .= "insert into $pgsql_session_table values('$key', $now, '$val'); ";
| $query .= 'commit;';
| $result = @pg_exec($pgsql_session_handle, $query);
|
| $ret = (pg_cmdtuples($result) == 0);
| pg_freeresult($result);
|
| return ($ret);
| }
+-
I've added the debugging statements at the top. Can you see anything
wrong with this function? At the end of the include file in which
this appears, is the session_set_save_handler() call:
session_set_save_handler(
'pgsql_session_open',
'pgsql_session_close',
'pgsql_session_read',
'pgsql_session_write',
'pgsql_session_destroy',
'pgsql_session_gc'
);
Thanks,
--
Steve [EMAIL PROTECTED]
"And when you walk in golden halls, you get to keep the gold that falls"
-- Black Sabbath, "Heaven and Hell"
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] Postgresql session handling
"Richard Lynch" [EMAIL PROTECTED] wrote:
The writing of the session data occurs *after* the server-browser HTTP
connection is cut.
If you have any error-reporting happening in your session_write
function,
you won't see it.
Alter that function to log errors to a file or something.
It seems the pgsql_session_write() function is not even being invoked.
Here it is:
+-
| function pgsql_session_write($key, $val)
| {
| /* debugging */
| $fp = fopen("/tmp/phpdebugwrite","w") or die ("can't open file");
| fwrite($fp, "HERE");
| fclose($fp);
| /* end debug */
Why don't you just use error_log() function and write errors to a file?
|
| global $pgsql_session_handle, $pgsql_session_table;
|
| $key = addslashes($key);
| $val = addslashes($val);
| $now = time();
|
|
|
| /*
| * Delete any existing data for this session and then insert a new
row
| * containing the current session data, all in a single transaction.
| * This should prevent collisions between multiple session instances.
| *
| * Thanks to "Will Fitzgerald" [EMAIL PROTECTED].
| */
| $query = 'begin; ';
| $query .= "delete from $pgsql_session_table where session_id =
'$key'; ";
| $query .= "insert into $pgsql_session_table values('$key', $now,
'$val'); ";
| $query .= 'commit;';
| $result = @pg_exec($pgsql_session_handle, $query);
I've seen this kind of implmentatin
delete
insert
IMHO, 2 sql operation that requires locks for session handling is just a
waste
of resources. (delete requires higher lock level than select, transaction
needs more resources, for each delete, insert requires fsync() and requires
more disk head movements, etc, etc)
Unless you would like to implement some kind of session key validation to
session handlers, simple
select
insert or update ...
may give better performance with many web servers and users. (may not make
any significant improvement, though. Disabling PostgreSQL's fsync() for
session db would much better result. Just FYI)
|
| $ret = (pg_cmdtuples($result) == 0);
| pg_freeresult($result);
|
| return ($ret);
| }
+-
I've added the debugging statements at the top. Can you see anything
wrong with this function? At the end of the include file in which
this appears, is the session_set_save_handler() call:
You will not get output from die('.') ( or any print/echo) in session
handlers. Just use error_log() or implement user defined error handler
so that you can trigger error to display session handler errors.
my example pg_session_write() looks like:
function pg_session_write ($session_id, $session_data) {
global $db_session, $HTTP_SERVER_VARS, $HTTP_COOKIE_VARS;
if (!$db_session) {
error_log("session_write(): Cannot connect to database.",0);
}
if (strlen($session_data) intval(SESS_DATA_MAX)) {
error_log('Session data too large. Unable to update session data for
session '. $session_id, 0);
}
if (strlen($session_id) != 32) {
error_log("session_write(): Invalid Session ID",0);
return 0;
}
$session_id = addslashes($session_id);
$session_data = addslashes($session_data);
$uid = isset($HTTP_COOKIE_VARS['t_uid']) ?
addslashes($HTTP_COOKIE_VARS['t_uid']) : '';
$query = 'SELECT session_id, i_session_counter FROM '. SESS_TABLE ." WHERE
session_id = '$session_id'";
$result_id = pg_exec($db_session,$query);
$session_exist = pg_numrows($result_id);
if ($session_exist == 0) {
// $query = 'INSERT INTO '. SESS_TABLE ." (session_id, i_time_created,
i_last_active, t_remote_addr, t_session_data) VALUES ('$session_id', ".
time() .", ". time() .", '".
isset($HTTP_ENV_VARS['HTTP_X_FORWARDED_FOR'])?$HTTP_ENV_VARS['HTTP_X_FORWARDED_FOR']:$HTTP_SERVER_VARS['REMOTE_ADDR']
."', '$session_data')";
$query = 'INSERT INTO '. SESS_TABLE ." (session_id, i_time_created,
i_last_active, t_uid, t_remote_addr, t_session_data) VALUES ('$session_id',
". time() .", ". time() .", '$uid', '". $HTTP_SERVER_VARS['REMOTE_ADDR']
."', '$session_data')";
}
else {
$rec = pg_fetch_array($result_id,0,PGSQL_ASSOC);
$query = 'UPDATE '. SESS_TABLE ." SET t_session_data =
'$session_data',
i_last_active = ". time() .", t_uid = '$uid' , i_session_counter = ".
intval(++$rec['i_session_counter']) ." WHERE session_id = '$session_id'";
}
$rows_affected = pg_cmdtuples(pg_exec($db_session,$query));
//error_log($query, 0);
if (!$rows_affected) {
error_log('session_write(): Failed to INSERT or UPDATE session.',0);
}
return $rows_affected;
}
session_set_save_handler(
'pgsql_session_open',
Re: [PHP] PostgreSQL + PHP + SQL warning messages
El Jue 25 Ene 2001 16:36, Evelio Martinez escribi: Hi! Is there any way to get the equivalent sqlca.sqlcode value of informix in PostgreSQL from php ? I would like to use the sqlcode to print the messages in Spanish. The problem is that informix puts in an array (sort of) all the info about the last query. What sort of information would you like? Last insert? You have an pg_getlastoid() function. Hope it helps. Saludos...:-) -- System Administration: It's a dirty job, but someone told I had to do it. - Martn Marqus email: [EMAIL PROTECTED] Santa Fe - Argentinahttp://math.unl.edu.ar/~martin/ Administrador de sistemas en math.unl.edu.ar - -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
