On Tue, Mar 02, 2004 at 05:31:57PM -0800, Tom Jackson wrote: > On Tue, 2004-03-02 at 16:55, Andrew Piskorski wrote:
> Umm, I think I was definitely confused by the idea of stored procedures. > Initially I thought they were just named procedure calls already in the > database. Somewhere along the way I read the scant AOLserver description > of how to use the store procedure calls. They had a "create > procedure..." in there, so I thought you could write whatever you > wanted. > > I think it turns out I was right in my first thought, so I doubt you > could do what I was thinking. _But_ the example shows that you can > return rows from the database besides the out variables. If stored > procedures can return rows, that would be a nice alternative to sending > a copy of the select statement every time you need data. > > Does anyone (probably AOL) have examples of how stored procedures are > used via ns_db? Um, just what "stored procedures" are you talking about here? Where are these examples or docs that you're looking at? This has nothing to do with ODBC per-se, right? The only "stored procedures" I know about are stuff like Oracle PL/SQL functions and procedures, and the equivalent in PostgreSQL. I'm told that in PostgreSQL you can easily define rowsets, then do a "select from my_function()" and get N rows back, exactly as if you selected from a table. Oracle can't do that, although it can do similar but less general stuff with ref cursors and user-defined SQL table types. E.g., for the latter Janine's example of a handy PL/SQL string parsing function in a "where id in ..." clause: http://openacs.org/forums/message-view?message_id=165954 AFAIK the current stock AOLserver Oracle driver doesn't support ref cursors, but people have patched it to do so in the past. I think with ref cursors support in the driver you could select what look like resultsets from a function. Anyway all that stuff I'm talking about is completely database and database driver specific. Or at least I think so - there's probably some SQL Stored Procedures standard but I don't think it's relevent for either Oracle or PostgreSQL. Tom, I suspect you already knew most of those details though so you were probalby thinking of something else?? Hm... AOLserver's ns_db API has sp_start, sp_exec, and sp_returncode commands. sp_start calls Ns_DbSpStart to "Start execution of a stored procedure". Tom, is that what you were wondering about? I have no idea what those are really for, OpenACS doesn't use it at all. I never noticed them in there before either. I don't think the Oracle or PostgreSQL drivers use the ns_db sp_* functionality at all. Maybe the Sybase driver uses it, and thus the AOL folks can comment? Is this something that the Oracle and PostgreSQL drivers could use but don't implement, or is it useless for those databases? Hm, I think the (external) Sybase driver does use it, but nssybpd.c says: * Ns_PdDbSpStart - * * Prepare the Sybase state structure for execution of a stored procedure * by cancelling any pending-but-uncollected results at the server, and * remembering the name of the stored procedure. (In fact, in reviewing * this code, I can't see where there's any advantage to using * SpStart/SpExec over DbExec -- at one time, DbExec wasn't capable of * returning the stored procedure return codes and multiple results, but * that's been fixed by now.) So maybe that sp_* stuff is obsolete? -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/ -- AOLserver - http://www.aolserver.com/ To Remove yourself from this list, simply send an email to <[EMAIL PROTECTED]> with the body of "SIGNOFF AOLSERVER" in the email message. You can leave the Subject: field of your email blank.
