trouble finding documentation on grid based on stored procedure

[Anita Headley]

I am trying to build a grid based on a stored SQLServer procedure, but can't find anything in the documentation even though it's an option on the Query part of the grid component.

[mikeallenbrown]

Re: trouble finding documentation on grid based on stored procedure

Try this...

[B]exec My_Stored_Procedure_Name_Here parameter_1, parameter_2, parameter_3 [/B]

....the parameters, by the way, are optional.

ScreenShot_2015.04.22_002_.jpg

[Anita Headley]

Re: trouble finding documentation on grid based on stored procedure

There must be more to it than that - it needs fields or something. It won't let me preview without fields, but there are none to select from.

[mikeallenbrown]

Re: trouble finding documentation on grid based on stored procedure

Did you already create your stored procedure in SQL?

[mikeallenbrown]

Re: trouble finding documentation on grid based on stored procedure

That is how you do it. You need to create your stored procedure in SQL first. Then go back to Alpha and reference it. See my examples below...

ScreenShot_2015.04.22_004_.jpg

ScreenShot_2015.04.22_005_.jpg

ScreenShot_2015.04.22_003_.jpg

[Anita Headley]

Re: trouble finding documentation on grid based on stored procedure

I have already created and tested my stored procedure in SQLServer. It runs fine there. It is called run_tocas_lift_query and takes one numeric argument.

I put this in the SQL Statement after clicking Stored Procedure:
exec tocas_dba.run_tocas_lift_query :qrynum

I just don't know what to do next to get it to return my data on the grid.

[mikeallenbrown]

Re: trouble finding documentation on grid based on stored procedure

Oh I see ... take a look at the IMPORTANT - Additional Information On Basing A Grid On A Stored Procedure link

The syntax is a little different it appears: exec test @parameter_1 = :your_argument

ScreenShot_2015.04.22_006_.jpg

[Anita Headley]

Re: trouble finding documentation on grid based on stored procedure

I modified the SQL statement, set the properties that onSQLSelectQuery said were necessary and it still tells me I need fields.

'Your event must set these properties of the 'e' object:
'e.rs - a resultset of records to show in the Grid (need only be set if e.handled is set to .t.)
'e.handled - set to .t. if your event has computed the record count. set to .f. if you want Alpha Five to execute its own count query
'e.paginationHandled - set to .t. if the resultset that is returned by this event is already positioned at the correct starting logical record number.
' - The default value for this property is .f., which means that Alpha Five will assume that the first record in the result set that is returned
' - by the event (e.resultset) is positioned on the first logical record, and Alpha Five will be responsible for navigating to the correct
' - logical record in the resultset. (This property is only meaningful if e.handled is set to .t.)

e.handled = .f.
e.paginationHandled = .t.

[mikeallenbrown]

Re: trouble finding documentation on grid based on stored procedure

I'm getting the fields that I defined in my test SP. I haven't gone as far as setting up the server-side events however so when I run the grid I get errors.


ScreenShot_2015.04.22_007_.jpg

ScreenShot_2015.04.22_008_.jpg

ScreenShot_2015.04.22_009_.jpg

[mikeallenbrown]

Re: trouble finding documentation on grid based on stored procedure

I found this ...I hope it helps:

http://wiki.alphasoftware.com/~alpha...procedures+V11

[Anita Headley]

Re: trouble finding documentation on grid based on stored procedure

This was very helpful to get me down the path, but not to the finish line.

I have put the following code in onSQLSelectQuery:
//////////////
dim myflag as l
dim myrs as sql::resultset
dim qnum as N

qnum = e.arguments.find("qrynum").data
myflag = e.cn.execute("exec tocas_dba.run_tocas_lift_query @qrynum = :qrynum",e.arguments)

if myflag = .t. then
myrs = e.cn.resultset
e.rs = myrs
e.handled = .t.

else

e.fatalError = .t.

e.errorText = "Could not get record count. " + e.cn.callResult.text

end if
////////////////////////

The stored procedure returns records when run from SQLServer. It returns records when I click on 'Preview records in query' from the Query definition. When I try to do a Live Preview, myrs returns a rowcount of -1 and the grid shows up with just the LogicalRecNo showing. There are still no fields to select from (except LogicalRecNo). this is the text of my stored procedure:
/////////
CREATE PROCEDURE [tocas_dba].[run_tocas_lift_query] (@qrynum int)
as
begin
declare @qrytext nvarchar(4000)

SET @qrytext = (SELECT QUERY_TEXT FROM tocas_dba.TOCAS_SAVED_QUERYS WHERE QUERY_NUMBER = @qrynum)

execute sp_executesql @qrytext
end
//////////////////////////////////

I would appreciate any clues as to why this will not show records on the grid.

[GGandhi]

Re: trouble finding documentation on grid based on stored procedure

https://www.youtube.com/watch?v=f4ju_Px4bos
as expected david has a video on sql stored procedure. it is probably MySQL but take a look it may shed some ideas
I know exec is sqlserver stored procedure call name, try with call instead of exec in your statement and see if it changes and gives you the fields.

[Anita Headley]

Re: trouble finding documentation on grid based on stored procedure

debugging stored procedure grid.doc I set it up just as explained in the video and in the alphapedia article. It still does not show fields. The stored procedure runs using exec. Using 'Call' causes an error ( I am using SQLServer db). My stored procedure is not a simple select statement. Does it have to be? If so, then this is useless to me. My stored procedure is executing a pre-built SQL query that was stored in a text field in a table.

[Anita Headley]

Re: trouble finding documentation on grid based on stored procedure

P.S. what good would it be to have the fields from one run of the stored procedure? The list of fields would be different every time depending on the stored query that was being executed.

[Davidk]

Re: trouble finding documentation on grid based on stored procedure

Post a sample project with your grid, plus the SQL Server proc and query you're calling, plus a sample table. I'll have a look.