SQL Stored Procedure or User Defined Function

[iviowa]

Can I use a Stored Procedure or User Defined Functions, with a paramenter, as my data source for a grid?

I've created grids using SQL tables and Views. In fact, one of my Views joins a table to a user function (the function has no parameter).
Select * uf_temp1 ()

Now I have a Function that has a required parameter.
Select * from uf_temp2 (number)

I can't put that Function in a View because from what I can tell, Views will not let me pass in a paramenter to the Function. Even if it did, could I pass in a parameter from Alpha to the View?

Store Procedures and Functions offer such power to create temp tables and do lots of data manipulation before bring back the final dataset. I'd like to use those datasets in a Read Only grid.

[iviowa]

Re: SQL Stored Procedure or User Defined Function

I've never figured this out so am checking again for help.

I'm creating a grid. I need to run a sql user function or stored procedure that needs a parameter before it can run:
ufSample(@parameter)
upSample @parameter

I have several grids based on sql views. Those views often call user functions which may have a where clause - "select * from ufsample() where ID > 500". That works. How do I base a grid on ufSample(@parameter).

How do I pass the @parameter into a view and/or into a user function?

[Atta]

Re: SQL Stored Procedure or User Defined Function

This would be sweet to get a lesson on from Alpha Software. Willing even to pay for it.

[iviowa]

Re: SQL Stored Procedure or User Defined Function

I'll bet there is a way to pass in a parameter to a Stored Procedure or User Function then bring back that data into a Read Only webcomponet grid.

[Atta]

Re: SQL Stored Procedure or User Defined Function

Does anybody know how to use a stored procudere in a database setup??

I want to run a report on this stores procedure

[bob9145]

Re: SQL Stored Procedure or User Defined Function

What database are you using? You can execute sql code using the sql events.
Bob

function onBeforeSQLCommandExecute as v (e as p)
'This event fires before an INSERT, UPDATE or DELETE statement on a SQL table is executed.
'It allows you to compute your own SQL to execute in addition to, or in place of the SQL that
'Alpha Five generates. You can use this event if you want to call a
'stored procedure to update your database.

[iviowa]

Re: SQL Stored Procedure or User Defined Function with Parameters

I've run Stored Procedures in an AfterUpdateRecord events and that works great for me.

But I need to pass in parameters to a Stored Procedures (or Function) then use the resulting data in a Read Only grid. The Stored Procedure will do a lot of data retrieval based on those parameters and it's can't do the correct data retrieval without them.

It's common to use Stored Procedures with parameters to retrieve data for a .net aspx page. It there something similar for Alpha Five?

[cmkivio]

Re: SQL Stored Procedure or User Defined Function

I have exactly the same problem. I can execute the stored procedure as below in interactive but I can't figure out how to retrieve the returned value:

dim conn as sql::connection

?conn.open("::Name::modiloConnection")
= .T.

dim args as sql::arguments

args.Clear()

?args.set("TenantId",1)
= .T.

?args.set("SequenceId",2)
= .T.

?args.set("NextNumber",99999999,sql::ArgumentUsage::InputOutputArgument)
= .T.

?conn.Execute("exec NewSequenceNumber :TenantId, :SequenceId, :NextNumber",args)
= .T.

?args.Find("NextNumber")
= Data = 99999999
IsNull = .F.
Name = "nextnumber"
Usage = 2
XML = <SQLArgument>
<Name>nextnumber</Name>
<Data Type="N">99999999</Data> <------ NO CHANGE
<IsNull Type="L">0</IsNull>
<Usage>InputOutput</Usage>
</SQLArgument>

/Charles

[iviowa]

Re: SQL Stored Procedure or User Defined Function

I need to bump this post. I really like Alpha Software but still need to figure out this data retrieval issue.

I need to have Alpha pass parameters to a SQL Function or Stored Procedure then display the returned data in a grid.
For Example: Select * from ufAnnualCalcuation (@UserName, @StartDate, @EndDate, @CalcMethod)

Currently my grids are based on SQL Tables or Views which retrieve then filter the data. My SQL Views often call Functions but I can't pass a parameter to the Function via the requested View so I'm a bit lost.

Any guidance would be greatly appreciated.

[iviowa]

Re: SQL Stored Procedure or User Defined Function

A work around may be to have a Stored Procedure with parameters excecute on the OnGridInitialize event. This SP could insert data into a SQL Table. The grid would then be based on that Table based on the @UserName (and session.UserName). I ran a test to make sure the grid would not display until the Procedure finished, and that seemed to work. (I tested it by adding a " WAITFOR DELAY '00:01:15' " line to the Procedure - if it takes a long time (?) for the procedure to run then alpha will eventually timeout).

This doesn't seem to be the best solution since I have to create a Table to hold temporary data. I would still prefer to retrieve data directly from a Procedure or Function. Still hoping for that solution.

[WayneH]

Re: SQL Stored Procedure or User Defined Function

Ironic... I was up late last night trying to do the same thing. I have a series of views that need a parameter passed in. I can create a stored procedure to do the job and pass the parameter in via xbasic, but there is not way to return the resulting data set into Alpha.

I came to the same hokey conclusion that I would have to write the data into a temporary table. I figured I would create and destroy the table after, but I do like the idea of the post above. I think I could write the ulink value of the user in to a column, then limit the view that way. In this fashion, if two users run the same grid on two different data populations, it would work.

Sending the results of a stored procedure seems to be a programmatic event. A grid wants a finite mapping of fields based on a view from my late night review.

Maybe someone with more SQL has a better solution?

[iviowa]

Re: SQL Stored Procedure or User Defined Function

Thanks for the validation. Using ULink to identify the records inserted into the table, is a good idea since I could eventually have dulplicate user names.
I will prrobaby just delete the records from this Table for that ulink, rather than destroy the table, since other users may have data in there at the same time.
Again, I'm hoping for an even better solution.

[cmkivio]

Re: SQL Stored Procedure or User Defined Function

Basing a grid on Stored Procedure as data source will be available in v11.

[WayneH]

Re: SQL Stored Procedure or User Defined Function

Source?