Sigleton DB connection tripping on itself

------_=_NextPart_001_01C6AA91.618CB3DE
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Good Day List,
=20
I have a singleton DB connection that I am trying to use throughout my
application but It keeps tripping on itself.
=20
My main question is: If I am in the middle of a transaction can I
prepare multiple statements eand execute them or will this cause an
error?
=20
Here is a code example that tries to reset a users password:
$login =3D SOME_LOGIN;
=20
$sql =3D "SELECT " . USER_TABLE_ID . " FROM " . USER_TABLE . " " .
"WHERE " . USER_TABLE_LOGIN . " =3D :login;";
=20
try {
$this->db->beginTransaction();
$stmt =3D $this->db->prepare($sql);
$stmt->execute(array(':login' =3D> $login));
$rs =3D $stmt->fetchAll(PDO::FETCH_ASSOC);
=20
// MAKE SURE THERE IS ONLY ONE USER WITH THIS LOGIN
if (count($rs) !=3D 1) {
$this->db->rollBack();
$stmt =3D null;
return false;
}
// ONLY ONE USER RETURNED - UPDATE PASSWORD
if ($password =3D $this->generatePassword()) {
=20
$sql =3D "UPDATE " . USER_TABLE . " " .
"SET " . USER_TABLE_PWD . " =3D '";
if ($this->sha1) {
$sql .=3D sha1($password);
}else {
$sql .=3D $password;
}
$sql .=3D "' " .
"WHERE " . USER_TABLE_ID . " =3D :userid;";
=20
$stmt =3D $this->db->prepare($sql);
$stmt->execute(array(':userid' =3D> $rs[0][USER_TABLE_ID]));
$this->db->commit();
$stmt =3D null;
return $password;
}
// COULD NOT GENERATE NEW PASSWORD
else {
$this->db->rollBack();
$stmt =3D null;
return false;
}
}catch (PDOException $e) {
$this->db->rollBack();
$stmt =3D null;
return false;
}
=20
Ken Vandegrift
kvandegrift@sharis.com =20
Web Administrator
Sharis Mgmt. Corp
=20

------_=_NextPart_001_01C6AA91.618CB3DE--

Re: Sigleton DB connection tripping on itself

Vandegrift, Ken wrote:
> Good Day List,
>
> I have a singleton DB connection that I am trying to use throughout my
> application but It keeps tripping on itself.
>
> My main question is: If I am in the middle of a transaction can I
> prepare multiple statements eand execute them or will this cause an
> error?

There's no reason why it can't. However, if any of those queries fail
the whole transaction will get rolled back.

What errors are you getting?

--
Postgresql & php tutorials
http://www.designmagick.com/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: [PHP-DB] Sigleton DB connection tripping on its

Well, I have a situation where an Order object holds a collection of
LineItem objects and of course they all use the same DB connection via=
a
singleton.

The Order object has a method that creates new LineItem objects from an
ORDER_DTL table.

Code snippet:

// SET ORDER ITEMS
$stmt =3D $this->db->prepare($detailSQL);
$stmt->execute(array(':orderid' =3D> $orderID));
while ($row =3D $stmt->fetch(PDO::FETCH_ASSOC)) {
$lineItem =3D new GCLineItem($row['item_id']);
$lineItem->__set('quantity', $row['quantity']);
$lineItem->__set('price', $row['price']);
$this->items[] =3D $lineItem;
}

Within the while loop I am loading GCLineItem objects that themselves
try to create a statement object which is where the error is triggered
since both share the same DB connection and the statement objects trip
over themselves.

I get an SQL error HY090 Buffer error...

My only solution was to let the GCLineItem objects create another DB
connection for themselves. This is not ideal and is a hack in my
opinion.

I could run a fetchAll on the Order statement object and this would
complete one statement object before each GCLineItem objects creates
there own statement object - correct? I may have just answered my own
question, but if you or anybody else can think of a better solution -=
I
am all ears!


Ken Vandegrift
kvandegrift@sharis.com
Web Administrator
Sharis Mgmt. Corp

-----Original Message-----
From: Chris [mailto:dmagick@gmail.com]=20
Sent: Tuesday, July 18, 2006 5:32 PM
To: Vandegrift, Ken
Cc: php-db@lists.php.net
Subject: Re: [PHP-DB] Sigleton DB connection tripping on itself

Vandegrift, Ken wrote:
> Good Day List,
> =20
> I have a singleton DB connection that I am trying to use throughout=
my

> application but It keeps tripping on itself.
> =20
> My main question is: If I am in the middle of a transaction can I=20
> prepare multiple statements eand execute them or will this cause an=20
> error?

There's no reason why it can't. However, if any of those queries fail
the whole transaction will get rolled back.

What errors are you getting?

--
Postgresql & php tutorials
http://www.designmagick.com/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Sigleton DB connection tripping on itself

Vandegrift, Ken wrote:
> Well, I have a situation where an Order object holds a collection of
> LineItem objects and of course they all use the same DB connection via a
> singleton.
>
> The Order object has a method that creates new LineItem objects from an
> ORDER_DTL table.
>
> Code snippet:
>
> // SET ORDER ITEMS
> $stmt = $this->db->prepare($detailSQL);
> $stmt->execute(array(':orderid' => $orderID));
> while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
> $lineItem = new GCLineItem($row['item_id']);
> $lineItem->__set('quantity', $row['quantity']);
> $lineItem->__set('price', $row['price']);
> $this->items[] = $lineItem;
> }

So the code you posted before has nothing to do with the problem? That
makes it kinda hard to work out what's going on.

> Within the while loop I am loading GCLineItem objects that themselves
> try to create a statement object which is where the error is triggered
> since both share the same DB connection and the statement objects trip
> over themselves.

Code ? Just the constructor and load method should do.

> I get an SQL error HY090 Buffer error...

What sort of database is it? (I'm guessing db2, mssql or oracle). Not
that it really matters of course.

--
Postgresql & php tutorials
http://www.designmagick.com/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php