Thread: newbie issues with PDO / stored procedures

Hi! I am new to PHP and Postgres; in the early stages of creating a web
app, just trying to achieve rudimentary results, yet trying to follow
"best practices" -- in the form of using Stored Procedures vs. Dynamic
SQL -- as I do hope to take it live to the scary ol' Internet one day.

I have a stored procedure (function), "select_user_details", of the form
"SELECT * FROM users;" which returns "SETOF users".

When I execute my prepared statement, "SELECT
select_user_details([userid])" via PDO, I get a rowset back which holds
all the columns of users as an array in one column. From my experiement
directly in pgAdmin Query tool, I guess that's what you expect when you
return SETOF, but I already miss the simple DSQL I had set up where I
could access returned columns via $row[columname] syntax.

I messed around with using fetch() but that didn't seem right (more on
that below). Then I took a look at fetchColumn() but I didn't like that
because you address columns by index # instead of column name. Also the
fact that you advance the row pointer with every call to any kind of
fetch*() seems like it isn't well suited to retrieving data from more
than one column.

Now it looks like my best option in hopes of being able to access
columns by semantically sensible names is to fetchObject() into PHP
objects representing my database business objects. Is this correct?
Suggestions? Alternatives? Insights?

In Postgres, is there any way to use named params in my stored
procedures rather than just addressing them by number?

Also, what do I tell my SP to return if I want to return a set of
columns that is not directly represented by some existing table/schema
in my database. For example "a few columns from table a, joined to a few
columns from table b..." that sort of thing.

To back up a few steps to the "big picture" standpoint, I can see pretty
clearly how things should work well if I am using a stored procedure to
return a single value, but I am not so sure when it comes to returning
multiple column recordsets. Is this the best way to go about returning,
say, a complete "User Details" record?

Bonus question: why, when I was first experimenting with this, and
trying to figure out how to "get at" my SELECTed data after calling
$spUsers->execute(), and used the form "$row = $spUsers->fetch();" did
the resulting $row contain a 2-element array? The element indices were
[select_user_details] and [0] and both contained the identical row
value. (Only one row is being returned by the SP at this point, and I
don't understand how, in the PHP code, that row was being appended to
the $row array more than once. Hence, confusion.)

Any links to existing tutorials/examples on these specifics would be
welcome as well.

Thanks for reading, and thanks for any help!

On Thu, 2008-06-12 at 05:46 -0400, pg 043g9j2g wrote:
> Hi! I am new to PHP and Postgres; in the early stages of creating a web
> app, just trying to achieve rudimentary results, yet trying to follow
> "best practices" -- in the form of using Stored Procedures vs. Dynamic
> SQL -- as I do hope to take it live to the scary ol' Internet one day.
>
> I have a stored procedure (function), "select_user_details", of the form
> "SELECT * FROM users;" which returns "SETOF users".
>
> When I execute my prepared statement, "SELECT
> select_user_details([userid])" via PDO, I get a rowset back which holds
> all the columns of users as an array in one column. From my experiement
> directly in pgAdmin Query tool, I guess that's what you expect when you
> return SETOF, but I already miss the simple DSQL I had set up where I
> could access returned columns via $row[columname] syntax.
>
> I messed around with using fetch() but that didn't seem right (more on
> that below). Then I took a look at fetchColumn() but I didn't like that
> because you address columns by index # instead of column name. Also the
> fact that you advance the row pointer with every call to any kind of
> fetch*() seems like it isn't well suited to retrieving data from more
> than one column.
>
> Now it looks like my best option in hopes of being able to access
> columns by semantically sensible names is to fetchObject() into PHP
> objects representing my database business objects. Is this correct?
> Suggestions? Alternatives? Insights?

I'm not sure this will work in your case, but the normal way to get
SETOF returning functions to return rows that look just like real table
rows is to define a view with the semantically sensible names and then
do your query against the view.

CREATE VIEW my_rows AS SELECT col1, col2, col3
       FROM setof_returning_function();

Then you will get a normal select output in your program and be able to
refer to $row->col1 etc.

This doesn't necessarily work so well when you want to hand in a
parameter, unless you can hand that in by doing a join of some kind:

CREATE VIEW my_rows AS SELECT col1, col2, col3,
                      jointable.pkey_col AS other_key
       FROM setof_returning_function(jointable.somecol), jointable;

Then you would need to do something like:

SELECT col1 FROM my_rows WHERE other_key = 75

Etc.


Hope this helps,
                    Andrew.

-------------------------------------------------------------------------
Andrew @ Catalyst .Net .NZ  Ltd,  PO Box 11-053, Manners St,  Wellington
WEB: http://catalyst.net.nz/            PHYS: Level 6, 150-154 Willis St
DDI: +64(4)803-2201      MOB: +64(272)DEBIAN      OFFICE: +64(4)499-2267
It is truth which you cannot contradict; you can without any difficulty
                      contradict Socrates. - Plato

-------------------------------------------------------------------------

Andrew McMillan wrote:
> I'm not sure this will work in your case, but the normal way to get
> SETOF returning functions to return rows that look just like real table
> rows is to define a view with the semantically sensible names and then
> do your query against the view.
>
> CREATE VIEW my_rows AS SELECT col1, col2, col3
>        FROM setof_returning_function();
>
> Then you will get a normal select output in your program and be able to
> refer to $row->col1 etc.
>
> This doesn't necessarily work so well when you want to hand in a
> parameter, unless you can hand that in by doing a join of some kind:
>
> CREATE VIEW my_rows AS SELECT col1, col2, col3,
>                       jointable.pkey_col AS other_key
>        FROM setof_returning_function(jointable.somecol), jointable;
>
> Then you would need to do something like:
>
> SELECT col1 FROM my_rows WHERE other_key = 75
>
> Etc.
>
>
> Hope this helps,
>                     Andrew.
>
> -------------------------------------------------------------------------
> Andrew @ Catalyst .Net .NZ  Ltd,  PO Box 11-053, Manners St,  Wellington
> WEB: http://catalyst.net.nz/            PHYS: Level 6, 150-154 Willis St
> DDI: +64(4)803-2201      MOB: +64(272)DEBIAN      OFFICE: +64(4)499-2267
> It is truth which you cannot contradict; you can without any difficulty
>                       contradict Socrates. - Plato
>
> -------------------------------------------------------------------------
>
It helped a lot! Thank you!

Now I am using DSQL in my application in a form like 'SELECT * FROM
my_usp(:param);' and using PDO's bindParam() function to pass in params.

So I think I am still achieving the intended security of using SPs
against SQL injection by not directly inserting request args in my SQL,
as well as being able to pass in params without explicitly creating a
VIEW first.

I have even had success in joining two parameterized SPs together as in
'SELECT my_usp1.[only a few columns...], my_usp2.[only a few columns...]
FROM my_usp1(param) JOIN my_usp2(param) [etc.]'.  Am I incurring some
kind of inefficiency by not having an execution plan for that JOINed
statement, or does PDO->prepare() take care of that?

Which brings me to a re-statement of one of my earlier questions, which
I still have not found the answer to:

Is there any way to / how do i... write a function that returns a subset
of columns from each of two JOINed tables?

I have accomplished this by defining a VIEW for my desired output schema
first and having the function return that type, but, is there any simple
"generic" type I can use as a return type to avoid having to do so?
Right now I am working in a RAD/prototyping mode and it slows me down a
bit to have to formalize everything in that way.

Thanks for any assistance!

On Tue, 2008-06-24 at 19:44 -0400, pg 043g9j2g wrote:

> >
> It helped a lot! Thank you!
>
> Now I am using DSQL in my application in a form like 'SELECT * FROM
> my_usp(:param);' and using PDO's bindParam() function to pass in params.
>
> So I think I am still achieving the intended security of using SPs
> against SQL injection by not directly inserting request args in my SQL,
> as well as being able to pass in params without explicitly creating a
> VIEW first.

If your reasons for doing all this obfuscation are simply to avoid SQL
injection issues, then it seems to me that you are putting your efforts
into the wrong place.

PDO's bindParam() function is *already* achieving your goal.  Or PDO is,
when used correctly, even without binding parameters to names.

By jumping through these sorts of hoops you are making your code
significantly less maintainable (code needs to be maintained in two
different languages in two different places), and less efficient (the
database cannot plan as effectively), and more fragile (the separate
code bases may get out of sync, introducing a whole new set of
possibilities for error)....  and I could go on more too :-)



> I have accomplished this by defining a VIEW for my desired output schema
> first and having the function return that type, but, is there any simple
> "generic" type I can use as a return type to avoid having to do so?
> Right now I am working in a RAD/prototyping mode and it slows me down a
> bit to have to formalize everything in that way.

If you want to enforce read-only, (on top of the SQL injection avoidance
which PDO bought you already) then a view is a good way.  By default all
views in PostgreSQL are read-only, though they can be made writable with
a little extra effort.

Regards,
                    Andrew McMillan.


-------------------------------------------------------------------------
Andrew @ Catalyst .Net .NZ  Ltd,  PO Box 11-053, Manners St,  Wellington
WEB: http://catalyst.net.nz/            PHYS: Level 6, 150-154 Willis St
DDI: +64(4)803-2201      MOB: +64(272)DEBIAN      OFFICE: +64(4)499-2267
  Don't tell any big lies today.  Small ones can be just as effective.
-------------------------------------------------------------------------