Rev and SQL Server Stored Proc

jsims · Post by **jsims** » Thu Sep 10, 2009 7:01 pm

Hello,

Does anybody here know if Rev can execute as SQL Server stored procedure that returns a recordset?

TIA,

chris9610 · Post by **chris9610** » Fri Sep 11, 2009 9:14 pm

Well since stored procedures are server scripts maybe you should try the revExecuteSql and see what the result is.

Good question.

jsims · Post by **jsims** » Sat Sep 12, 2009 3:24 am

Hi Chris,

As I understand it, revExecuteSql only returns a value indicating the number of affected records. I'm going to try using revDataFromQuery on Monday or Tuesday and just pass it "Exec myStoredProc @Param1=1, @Param2=2" and see what happens. I'm hoping Rev just passes the query along and waits for data to come back in a form it understands. Since the stored proc returns the results of a Select statement, I'm hoping Rev will be none the wiser.

If I have any success, I'll be sure to pass it along.

Take care

chris9610 · Post by **chris9610** » Mon Sep 14, 2009 1:37 pm

Well if you are looking for record data to be returned from the procedure then try the revDataFromQuery.

I personally have never used stored procedures but I do not see any reason why they should not work.

Good luck.

jsims · Post by **jsims** » Tue Sep 15, 2009 4:03 pm

For anyone following this topic, it does not appear that executing SQL Server stored procedures that return a recordset will work in Rev. Apparently, SQL Server returns two pieces of information: 1 indicates the success of the stored proc and the other is the data for the recordset. What ends up in "it" is the success of the stored proc (in my case, "OK"). The "result" in my case is empty. So the stored proc is executing successfully but the data returned from the final select statement of the stored proc must just be ignored.

If anyone out there knows my above statement to be false, any information indicating how this can be done in Rev would be greatly appreciated.

Have a nice day/night!

Janschenkel · Post by **Janschenkel** » Tue Sep 15, 2009 8:16 pm

The only trick I can think of, is to store the data in a temporary table rather than returning it from the stored procedure; and upon success, read the data from the temporary table using a regular SELECT query.
The temp table will only be accessible for your connection, will automatically be dropped once the connection closes - and of course you can wipe it clean in the stored procedure before filling it with the result.

HTH,

Jan Schenkel.

jsims · Post by **jsims** » Wed Sep 16, 2009 2:52 pm

Hi Jan,

Yeah, that's the only work-around I could come up with also. I was just hoping not to have to make 2 calls to the database (from Durham, NC, to Chicago, IL ... long story). If I decide to give Rev a try on the project, I'll have to use this technique.

On a slight tangent, do you think I'd have much luck with a Feature Request to support this in the rev odbc driver? I was thinking that the success of the Stored Proc could be but in "the result" and the data put into "it".

Thanks to all for your input!

Janschenkel · Post by **Janschenkel** » Wed Sep 16, 2009 7:50 pm

Well, the Quality Center is the place to go for filing enhancement requests, but I can't predict how much priority this item would be given.

An alternative to a straight remote conenction to the database, would be to run an application at the server which can do both things in sequence and give you back the result set in xml format or something similar.
I'm sure this would also make your db/sysadmin happier if he doens't have to leave the port open to the internet

Jan Schenkel.