TbsSQL to execute Stored Procedure with Table Result (on MSSQL)

Hi, I've been successfully using a TbsSQL connection, started with code like this:


to query my SQL Server database for a few simple TBS templates I've been using.  Today i began trying to execute a stored procedure (SP) that I've used in another language for years (not PHP) that returns a result set (table set) via a final SELECT statement at the end of the SP.  I use this feature of MSSQL and SPs a lot in this particular system.

I'm wondering what (if any) way is supported in the TBS database routines that will allow me to get the results of such a query into a PHP array, as I've done by using code such as: where the only difference is the SQL I have used before have been simple straightforward SELECT statements.

When I've tried using the same approach to execute an SP, the code seems to run the query (because the page load takes a few seconds, as I'd expect with that particular SP), but I get no results out.  Usually, I see a message like this:
I found this post in these forums, but had no success trying to get a MergeBlock (directly) to fix my problem.  I'd really rather get this particular result in a PHP array first, and then do the Merge after.

http://www.tinybutstrong.com/forum.php?thr=2021

Can anyone help with a specific example of how they've done this using TBS code (or might I have to branch off and use some more native PHP / odbc / mssql code to do this myself)?

Thanks.

Hi John,

As I understand, TbsSQL correctly fetches your SELECT statement, but you have an error message when trying to fetch an stored procedure that having the same SELECT statement.

The error seems to directly come from the PHP functions for ODBC.
"No tuples available at this result index in ..." means that the stored procedure returns no recordset.
According to some posts on the web, it can be because you SQL statement is in fact empty (double-check your the variable containing the SQL statement), or because the stored procedure return less or more than one SELECT statement. Check if the stored procedure ends correctly after the first SELECT.

Hi Skrol29,

What I meant to say was that I can use TbsSQL to return *some* data (with different, straightforward SELECT queries, as part of another script I built), so I know that simple approach works.

I also know that this SP works fine (it's one that I've used in another language, that access MSSQL also via ODBC, but not in PHP), and I believe it only returns one result set (based on running identical SQL in MSSQL Management Studio).

Ah, I'm not sure yet, but I just did another couple of tests and may have traced down one thing that upsetting either TbsSQL or PHP ODBC code: I was actually executing a few small statements before the actual SP call, so it looked something like this:


Now admittedly, I don't really *have* to do this particular query like that -- I can calculate that date in PHP first, and pass it in as a parameter through TbsSQL's mechanisms, but I wonder if you have any insight as to why this particular statement is choking here.  I'm pretty sure that my other non-PHP ODBC library handles this scenario okay.

Just curious -- this probably is not a big problem for me at this point.

Does the stored procedure have explicit OUTPUT parameters ?

In other case, I don't see why it can make trouble.

It does not have OUTPUT parms ... but as I implied, I wouldn't want you to spend any time on this particular scenario at this point, because I don't actually need to execute a statement like that (there are work arounds that are fine).  I originally posted because I was afraid I couldn't execute an SP (even in a statement where the only action is to execute that SP) where the result is a table.

Since I've found that I can, I think I'll be fine for now.  I'll let you know if I run into something similar and I really need to execute a statement like the above.

Thanks as always for your attention.