Stored Procedures, Dynamic SQL or static SQL and testing.

Stored Procedures, Dynamic SQL or static SQL and testing.

am 26.03.2011 14:00:23 von Richard Quadling

Hi.

What preferences do you all have when using SQL. Stored Procedures or
PHP generated dynamic SQL or parameterized static SQL.

Whichever you use how do you test them? Do you use unit testing in
your PHP code? If so, do you also have unit code testing for the SQL
statements (I suppose this mainly relates to SPs).

Does using a different RDBMS make a difference to the SQL coding style
you'd use?

This isn't for any sort of thesis, just trying to see what the general
consensus is of those that reply.

Thanks for answering.

Richard Quadling.

--
Richard Quadling
Twitter : EE : Zend
@RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY

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

Re: Stored Procedures, Dynamic SQL or static SQL andtesting.

am 26.03.2011 14:43:06 von Geoff Lane

On Saturday, March 26, 2011, Richard Quadling wrote;

> What preferences do you all have when using SQL. Stored Procedures or
> PHP generated dynamic SQL or parameterized static SQL.

> Whichever you use how do you test them? Do you use unit testing in
> your PHP code? If so, do you also have unit code testing for the SQL
> statements (I suppose this mainly relates to SPs).

> Does using a different RDBMS make a difference to the SQL coding style
> you'd use?

Personally, I prefer to use dynamic SQL and this doesn't really change
no matter which RDBMS or programming language I use. I prefer doing it
that way because it's easier to see what I've done (I don't need to
interrogate the database to get procedure definitions, which aren't
universally available) and I find it easier to debug because echoing
"

$query

" lets me see what I'm passing to the DB.

I don't usually test SQL separately but as part of the application.
However, when debugging I often copy the echoed query and then paste
it into the CLI or other facility to check it returns what I expect.

With all that said, sometimes I have no option but use a stored
procedure. An example is where I'm inserting a new row and I need it's
PK value (e.g. LAST_INSERT_ID() in MySQL).

HTH,

--
Geoff


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

Re: Stored Procedures, Dynamic SQL or static SQL and testing.

am 26.03.2011 15:08:04 von Phpster

On 2011-03-26, at 9:00 AM, Richard Quadling wrote:

> Hi.
>=20
> What preferences do you all have when using SQL. Stored Procedures or
> PHP generated dynamic SQL or parameterized static SQL.

I used both. SPs work for some of the larger reports, especially when combin=
ed with a view or two. For mist of the form interactions I tend to use regul=
ar SQL with prepared statements for simplicity and security





>=20
> Whichever you use how do you test them? Do you use unit testing in
> your PHP code? If so, do you also have unit code testing for the SQL
> statements (I suppose this mainly relates to SPs).

Yes. I generally write and test the SQL before turning it into a SP




>=20
> Does using a different RDBMS make a difference to the SQL coding style
> you'd use?

Generic SQL where possible. But occasionally I will use specific SQL for the=
RDBMS where appropriate. My current code base is not as layered as I would l=
ike to really make it a simple config switch to change the backend



>=20
> This isn't for any sort of thesis, just trying to see what the general
> consensus is of those that reply.
>=20
> Thanks for answering.
>=20
> Richard Quadling.
>=20
> --=20
> Richard Quadling
> Twitter : EE : Zend
> @RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY
>=20
> --=20
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>=20

Bastien Koert
Sent from my iPhone=

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