Iron Speed Technical Forums
Register Latest Topics
 
 
 


Reply
  Author   Comment  
liam5000

Registered:
Posts: 4
Reply with quote  #1 
I need to use Stored Procedures extensively in my application, as the results I want to display are practically impossible to do with views e.t.c.

If ISD allowed tablecontrol generation from stored procs, allowing parameters to be passed as filters @ tablecontrol that would be fantastic and would save many headaches.

I know ISD has the stored.proc->gridview CCW, but in order to retain a consistent feel throughout the app, stored procs as a datasouce would be a massive step forward and would save many hours of coding.

__________________
Liam
razi

Registered:
Posts: 262
Reply with quote  #2 
This is an often asked request, so let me elaborate on why this is not something we have done so far.

There are two major challenges when a stored procedure (or for that matter any non table or view) source is used:

First, the stored procedure (or XML, Web Services, Excel, or other data sources) do not provide us with a complete schema to use.  In order to make sure that we display the data in the proper format, we need to know all of the details of the fields including the name, the data type, etc.  Most stored procedures return an array of records, and we cannot reverse engineer the data types from the data.  When a schema is provided, it may also not map one-to-one with a grid.  For example, Web Services may contain a one-to-many mapping, or more complex structures for a field.

Second, the table control is much more sophisticated than a data grid or grid view.  The filtering, the sorting, the searching, the pagination, the reporting (Excel, Word, PDF) are all driven by the table control.  For example, the table control retrieves ONLY the first 10 records to display and then when the end user paginates to the next page, the table control requests the next 10 records.  So in order for all of the functionality of the table control to be supported, the stored procedure must support filtering, sorting, searching, pagination, reporting, etc.  This is a tall order for a run-of-the-mill stored procedure.

So unfortunately, the same functionality that can be provided by a table control cannot easily be implemented using a custom stored procedure.

We will look at ways where we can provide the current code customization in an easier to use approach - possibly through a drag-and-drop from the toolbox.

We will also look at how we can allow you to define a schema for non-table data sources, so you will be able to accommodate stored procedures and other data soruces.

Thanks for the suggestion.

Thanks,
Razi
JimiJ

Avatar / Picture

MVP Developer
Registered:
Posts: 1,962
Reply with quote  #3 
There were also some enlightening discussions regarding the same topic here:
http://sjc.ironspeed.com/post?id=904711

__________________

  Jaime Jegonia
[jts_logo]

Iron Speed MVP Developer
 

". . . and whoever sows generously will also reap generously" 2 Cor 9:6

JohnHadj

Registered:
Posts: 369
Reply with quote  #4 
Razi

Thank you for the detailed explanation on this topic.  As developers, understanding the technical issues you face helps us to be realistic with our expectations.

I too have often found the need to use a stored procedure like a view - mainly where queries are parameterised - not an unusual situation.

I can see why the general solution is difficult, but subsets of the general problem could be solved.

One specific problem is where a query is parameterised.  For example, a stored function like:

SELECT customer, sum(amount) WHERE invoicedate >= @d

It would be very useful and time-saving to have an easy way of creating a table-control-like panel targetting this function, with @d included aas one of the filters.  It may be that some aspects fo a table control cannot be implemented, or it may be that ISD automatically creates some intermediate layer of code or data to make full functionality possible, or it may be that some developer set-up is necessary (in a silimar way to virtual keys) but that's OK.

John


akeshgupta

Avatar / Picture

Iron Speed MVP
Design, Develop & Deliver

Registered:
Posts: 1,095
Reply with quote  #5 
Folks:

One of the workarounds (it may not be clean) is to call a SP to populate data in a temp table and then have Iron Speed display data.  It does assume certain things but can be useful in some situations.

Hope this helps some folks.


__________________
Akesh Gupta
Light Speed Solutions, LLC.
If I rest, I will rust !   Let's share the knowledge !
JohnHadj

Registered:
Posts: 369
Reply with quote  #6 
Razi

This is bubbling back up to the surface for me, and from the forum others too.

I don't think this is as complex as it seems, because a call to a table-valued function could be treated like a view.

What's wrong with just replacing [dbo].[ViewName]  with [dbo].[TableValuedStoredFunctionName(p1, p2, p3)] in the stored procedures ISD generates, with p1, p2, etc filled in from filter values?

ROW_NUMBER() works with these calls, and from what I can see (without doing the whole job) the metadata is available in the sqlserver catalog - eg in INFORMATION_SCHEMA.ROUTINE_COLUMNS, INFORMATION_SCHEMA_PARAMETERS

It may need a bit of development work to put facilites into ISD to nominate a filter as a function parameter rather than part of a WHERE clause, but the result will be a whole class of applications will be possible that weren't before.

John
dingjing

Avatar / Picture

MVP Developer
Registered:
Posts: 256
Reply with quote  #7 
Don't forget ISD must also support Oracle, MySQL, Access. Something working great only for SQL server is not likely to be included in ISD.
JohnHadj

Registered:
Posts: 369
Reply with quote  #8 
I think that the INFORMATION_SCHEMA is defined by ISO SQL so may well be included in all conforming SQL implementations.

Having said that, I imagine that most applications are built against one specific vendor's database, and so if some features of ISD are only available on some platforms that's a platform limitation that developers must take into account.

John

JohnHadj

Registered:
Posts: 369
Reply with quote  #9 
I've just spent 15 minutes doing a quick experiment that demonstrates the feasibility of ISD using table-valued stored functions in the same way as views.

1.  I built a test application (called JunkTest) using a VIEW (called Junk) that included datetime, integer and nvarchar fields, populated with around 2000 rows.

2.  I tested the application, all OK.

3.  I renamed the view JunkView and wrote a stored function returning the same fields as JunkView and called it Junk.  I set it to recieve one integer parameter for test purposes.

4.  I edited the ISD stored procedures pJunkTestGet, pJunkTestGetList, pJunkTestGetStats, pJunkTestExport, pJunkTestDrilldown so that [Junk] was replaced with [Junk](33)

5.  I ran the test application and it behaved exactly as it had before, ie the function was being called to return results, the results were being filtered and sorted, paging was working.

6.  I looked at INFORMATION_SCHEMA.ROUTINE_COLUMNS and the result column specifications were present (analogous to the INFORMATION_SCHEMA.COLUMNS for the view)

7.  I looked at INFORMATION_SCHEMA.PARAMETERS and the input parameter specifications were present

I conclude from 1-5 that it is possible for ISD to use table-valued stored procedures in place of views without loss of table panel functionality

I conclude from 6-7 that, for SQL Server at least, ISD has enough information to build applications against table-valued stored procedures and enough information to provide parameters for them.

(and it took longer to write this than to do the test!)

John

dingjing

Avatar / Picture

MVP Developer
Registered:
Posts: 256
Reply with quote  #10 
Table-valued function is not supported in Oracle, MySQL, or Access.
JohnHadj

Registered:
Posts: 369
Reply with quote  #11 
leaving Access to one side, how would someone who uses Oracle store parameterised queries in their database?

eg:

SLECT sum(amount), area FROM sales
WHERE InvoiceDate > X and InvoiceDate < Y

where X and Y are user parameters.

Would they use "dynamic SQL" ?  If so, can't ISD do the same?

John

azwiefel

Registered:
Posts: 43
Reply with quote  #12 
All,
Has anyone attempted JohnHadj process in ver 12.2?
His work around solves an issue I've run into. I'm not clear on what's being replaced in the pJunkTestGet, pJunkTestGetList, pJunkTestGetStats, pJunkTestExport, pJunkTestDrilldown

In my case  I replaced the
--  SET @l_from_str = '[dbo].[vwCommissionSumByDateByEmployee] vwCommissionSumByDateByEmployee_';

with

SET @l_from_str = '[dbo].[ParameterizedCommissionQuery]1,"4/1/2013 00:00:00",NULL _ ';

SQL is throwing an error "Incorrect syntax near the keyword 'FROM'." when executed.
I might possibly misunderstood the steps.

Any help with this would be greatly appreciated.
Thanks

__________________
Quote of the Week:
We must not allow the clock and the calendar to blind us to the fact that each moment of life is a miracle and a mystery.
– H.G. Wells (1866-1946)
JimiJ

Avatar / Picture

MVP Developer
Registered:
Posts: 1,962
Reply with quote  #13 
http://sjc.ironspeed.com/post/no-more-option-to-entertain-new-features-8312867?pid=1293994594#gsc.tab=0


__________________

  Jaime Jegonia
[jts_logo]

Iron Speed MVP Developer
 

". . . and whoever sows generously will also reap generously" 2 Cor 9:6

Previous Topic | Next Topic
Print
Reply

Quick Navigation:

Easily create a Forum Website with Website Toolbox.

Download Iron Speed Designer

Terms of Service Privacy Statement