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 ";
> 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 ";
>}
>else echo "no time out. Connection status is $status ";
> }
>
> 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 ";
> 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 ";
>}
>else echo "no time out. Connection status is $status ";
> }
>
> 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?
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 ";
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 ";
}
else echo "no time out. Connection status is $status ";
}
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?
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)";
> 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
[PHP] PostgreSQL/PHP: transactions: how-to abstract out?
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)";
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
