[RESOLVED] Commit and Rollback in Prepared Statements

Myth_UK_

Using Prepared Statements: Is it possible to have multiple insert statements with commit() and rollback()?

I know how to do this Procedural Style, but haven't seen any examples of an OOP Style using Prepared Statements. Is it possible?

An example, or a link to an example would be nice :rolleyes:

laserlight

Myth(UK) wrote:
I know how to do this Procedural Style, but haven't seen any examples of an OOP Style using Prepared Statements. Is it possible?

Why not post what you know, and state what database extension you use?

Myth_UK_

laserlight;10945770 wrote:
Why not post what you know, and state what database extension you use?

I did post what I know, which was nothing, hench the question. :queasy:
Showing a version that doesn't use Prepared Statements would be pointless.

I am using MySQLi.

laserlight

Myth(UK) wrote:
Showing a version that doesn't use Prepared Statements would be pointless.

I do not think so, because prepared statements should be orthogonal to the use of commit and rollback.

Basically, everything should be the same, i.e., you need to turn off autocommit, perform the inserts, commit, rollback, etc. The only difference should be that you just need to prepare the statement once, then bind and execute as needed.

ixalmida

laserlight;10945779 wrote:
orthogonal

Wow, I'd love to play you in Boggle sometime!

😃

halojoy

laserlight is a welleducated young man.
So, his language can sometimes be academic and scientific.
And this is close to a very exact way to express things.
As long as people know the same words, it is perfect & works well.

Myself, I had to consult the Wikipedia.
http://en.wikipedia.org/wiki/Orthogonality
And now I think I know sort of what it is. I have learned a new word today 🙂

Orthogonal as used in
Computer science

Orthogonality is a system design property facilitating feasibility and compactness of complex designs. Orthogonality guarantees that modifying the technical effect produced by a component of a system neither creates nor propagates side effects to other components of the system. The emergent behavior of a system consisting of components should be controlled strictly by formal definitions of its logic and not by side effects resulting from poor integration, i.e. non-orthogonal design of modules and interfaces. Orthogonality reduces testing and development time because it is easier to verify designs that neither cause side effects nor depend on them.

For example, a car has orthogonal components and controls (e.g. accelerating the vehicle does not influence anything else but the components involved exclusively with the acceleration function). On the other hand, a non-orthogonal design might have its steering influence its braking (e.g. electronic stability control), or its speed tweak its suspension.[1] Consequently, this usage is seen to be derived from the use of orthogonal in mathematics: One may project a vector onto a subspace by projecting it onto each member of a set of basis vectors separately and adding the projections if and only if the basis vectors are mutually orthogonal.

An instruction set is said to be orthogonal if any instruction can use any register in any addressing mode. This terminology results from considering an instruction as a vector whose components are the instruction fields. One field identifies the registers to be operated upon, and another specifies the addressing mode. An orthogonal instruction set uniquely encodes all combinations of registers and addressing modes.

Bjom

On a more general perspective, orthogonal simply means "unrelated".

That's where it came from:

"In mathematics, two vectors are orthogonal if they are perpendicular, i.e., they form a right angle. The word comes from the Greek ὀρθός (orthos), meaning "straight", and γωνία (gonia), meaning "angle"."

And all other meanings are derived from that.

Think of a cartesian coordinate systems as an example: the axis are orthogonal. They describe two distinct dimensions.

halojoy

Myth(UK);10945778 wrote:
I am using MySQLi.

I am using PDO for MySQL and sometimes PDO for SQLite 3.
I have used MySQLi in object->mode quite a bit, so I know that as well.

I have struggled some hours to make Transaction + Rollback to work with PDO.
http://usphp.com/manual/en/function.PDO-beginTransaction.php

I say it is like laserlight said:
- If you use direct ->query() or ->exec()
- or use $db->prepare + ->bindParam() + $stm->execute()
... should not matter.

If transactions work, with commit/rollback, it will work with both ways.

There are some restrictions, though, when
1. Rollback will often not work on a transaction with CREATE TABLE, DROP TABLE statements in MySQL.
2. MySQL standard MyISAM database does NOT support transactions at all
(This was why I did not get any Transaction to work this evening. All my DB are MyISAM)

From Wikipedia:

MyISAM is the default storage engine for the MySQL relational database management system. It is based on the older ISAM code but has many useful extensions.
The major deficiency of MyISAM is the absence of transactions support. Therefore in recent MySQL versions, the InnoDB engine has widely started to replace MyISAM to allow additional referential integrity constraints, and higher concurrency.

ixalmida

halojoy;10945788 wrote:
laserlight is a welleducated young man.
So, his language can sometimes be academic and scientific.

Huh...I was going with "woman", based on the feminine, "witch" tag (sorry laser, but the picture is ambiguous).

I like to think that I have a good vocabulary but I wouldn't have reached beyond "peripheral" or "tangental". "Orthagonal" is beyond my meager vocabulary!

One of the reasons I love Boggle is that you can either win by knowing the most unusual, complex words, or you can be extremely fast at listing common, simpler words. I tend to win via the latter but I can only improve my game by learning new words. I can do that by scouring the dictionary myself and committing new words to memory...but playing against smarter people (and even possibly winning) is much more fun.

Myth_UK_

I did know the meaning of Orthogonal... but if I hadn't, I would have consulted wikipedia, not phpbuilder.

Getting back to the original question... I will just have to try it. What made me question IF it could be done, was the use of the execute() statement.