Alpha Video Training
Results 1 to 11 of 11

Thread: Can a Viewbox use a SQL Stored Procedure for the datasource

  1. #1
    Member
    Real Name
    Alan Sawyer
    Join Date
    Apr 2006
    Location
    western New York (rural)
    Posts
    261

    Default Can a Viewbox use a SQL Stored Procedure for the datasource

    I have a complex query with quite a few subqueries, and the SQL parser in the ViewBox doesn't like them, so I was trying to see if the SQL Statement could be a Stored Procedure??
    I would want to pass 3 arguments.

    I don't think I can create a view since the subqueries are using 1 of the arguments

    I could create a function and it would work
    select * from fnViewBox(:Arg1,:Arg2,:Arg3)

    But the view box sql editor doesn't like that either.
    Last edited by asawyer13; 01-02-2019 at 06:30 PM.

  2. #2
    Member
    Real Name
    Rich Fulham
    Join Date
    May 2011
    Location
    Portland, OR
    Posts
    995

    Default Re: Can a Viewbox use a SQL Stored Procedure for the datasource

    Haven't done it myself, but I'm pretty sure that since the LIST control can do that that the ViewBox control can as well. See the video "UX_V12--93 List Control - Using Native SQL For List Data". However, there is another technique that would most likely work and that is to define the "source" of the data to be "Static Data". In that method you list only the column names in the static data and in your xbasic you convert the query result set into JSON using one of the built-in aa functions like "VarToJsonStandard" and send back a little JavaScript code that gets a point to the LIST (ViewBox in your case) and calls the "populate" method with that json data.

  3. #3
    Member
    Real Name
    Garth McConnochie
    Join Date
    May 2009
    Location
    UK
    Posts
    138

    Default Re: Can a Viewbox use a SQL Stored Procedure for the datasource

    You could move the complex query to the database and make it a view

  4. #4
    Member
    Real Name
    Alan Sawyer
    Join Date
    Apr 2006
    Location
    western New York (rural)
    Posts
    261

    Default Re: Can a Viewbox use a SQL Stored Procedure for the datasource

    Quote Originally Posted by GarthMc View Post
    You could move the complex query to the database and make it a view
    The query has inline subqueries in it that needs to use an argument so I don't think I can put it in a view

    For example.

    Select field1
    from table1
    where field2 = (select field3 from table2 where field4 = :Arg1)

    I don't believe I can create a view that will work for that. I have the value for Arg1, but I can't just add it to the where clause of the query that's using the view.

    Does that make sense??

    Unless I'm missing something, which is always possible.

    I tried using the List with the ViewBox and it works in a simple test, but My viewbox is inside a Container, which is inside a PanelHeader which is inside a PanelLayout and for some reason it won't show the viewbox at all.

  5. #5
    Member
    Real Name
    Alan Sawyer
    Join Date
    Apr 2006
    Location
    western New York (rural)
    Posts
    261

    Default Re: Can a Viewbox use a SQL Stored Procedure for the datasource

    Quote Originally Posted by GarthMc View Post
    You could move the complex query to the database and make it a view
    Let me see if there is some way I can rework my query to be subqueries rather than inline.

  6. #6
    Member
    Real Name
    Alan Sawyer
    Join Date
    Apr 2006
    Location
    western New York (rural)
    Posts
    261

    Default Re: Can a Viewbox use a SQL Stored Procedure for the datasource

    I should add that if I use a view in the viewbox everything works great but I don't think I can get my complex query put into a view, that's why I started thinking of using a stored procedure as I can do it very easily there.

  7. #7
    Member
    Real Name
    Alan Sawyer
    Join Date
    Apr 2006
    Location
    western New York (rural)
    Posts
    261

    Default Re: Can a Viewbox use a SQL Stored Procedure for the datasource

    I was able to rework my complex query so it could be put into a view. Now to test that.
    Thanks for the replies.

  8. #8
    Member
    Real Name
    Alan Sawyer
    Join Date
    Apr 2006
    Location
    western New York (rural)
    Posts
    261

    Default Re: Can a Viewbox use a SQL Stored Procedure for the datasource

    I was able to redo my query into a view, however the real issue was my criteria. So the stored procedure stuff worked as it should, it was just me

  9. #9
    Member
    Real Name
    Rich Fulham
    Join Date
    May 2011
    Location
    Portland, OR
    Posts
    995

    Default Re: Can a Viewbox use a SQL Stored Procedure for the datasource

    BTW Alan,
    Did you try putting your original query in the ViewBox as a Nested SQL Query? On the "Data Source" tab for the ViewBox there is a "Query Type" field which can be set to "SQL Query" or "Nested SQL Query". I would think the builder for the latter type of query could have done what you want. You can make really complicated queries with that builder. Arguments can be used in the WHERE expression at any level in the Nested Query Builder.

  10. #10
    Member
    Real Name
    Frank Zwolinski
    Join Date
    Aug 2013
    Location
    Vernon, NJ USA
    Posts
    125

    Default Re: Can a Viewbox use a SQL Stored Procedure for the datasource

    Without knowing the relationships between your tables, there are many ways to write SQL queries to accomplish a task. This may or may not be of help here.
    Here is a simple example. Return a list customers who have purchase a specific product category.

    I verified that this query will work in both a list control and viewbox.
    SELECT distinct o.CustomerID
    FROM orders o inner join [Order Details] OD on od.Orderid = o.orderid
    left join products p on p.productID=od.productid
    where p.CategoryID in (select p.categoryid from products p where p.categoryid= :Arg1)

    Here is a method to move the argument to the main where clause but Alpha does not know how to parse this statement using left join sub queries.
    SELECT distinct o.CustomerID
    FROM orders o inner join [Order Details] OD on od.Orderid = o.orderid
    left join
    (select productid, categoryID from products) p on p.productID=od.productid
    where p.CategoryID = :Arg1

    Convert the above statement to a view

    create view view_CustomerProductCategory
    as
    SELECT distinct o.CustomerID, p.CategoryID
    FROM orders o inner join [Order Details] OD on od.Orderid = o.orderid
    left join
    (select productid, categoryID from products) p on p.productID=od.productid


    Now use the view as your record source.
    select CustomerID
    from view_CustomerProductCategory where categoryid=:Arg1

  11. #11
    Member
    Real Name
    jim wilson
    Join Date
    May 2019
    Posts
    21

    Default Re: Can a Viewbox use a SQL Stored Procedure for the datasource

    @frankiez +1 nice!

Similar Threads

  1. MySQL Stored procedure
    By sezeala in forum Mobile & Browser Applications
    Replies: 2
    Last Post: 02-15-2016, 08:47 AM
  2. Help with MS SQL INSERT Stored Procedure
    By jwilliamson in forum Mobile & Browser Applications
    Replies: 4
    Last Post: 12-08-2015, 09:57 AM
  3. How to run a Stored Procedure?
    By JetLi in forum Mobile & Browser Applications
    Replies: 6
    Last Post: 12-06-2013, 12:08 PM
  4. how to use sql server stored procedure?
    By arkeshtk in forum Application Server Version 9 - Web/Browser Applications
    Replies: 2
    Last Post: 08-04-2009, 12:54 AM
  5. MYSQL stored procedure
    By asawyer13 in forum Web Application Server v7
    Replies: 1
    Last Post: 06-28-2006, 01:07 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •