Results 1 to 9 of 9

Thread: Basic Parameterized View

  1. #1
    Member
    Join Date
    Apr 2019
    Location
    UK
    Posts
    44

    Basic Parameterized View

    Hi

    I'm trying to set up a parameterized view
    I have the properties defined as per the image attached
    The view is not filtering as expected (Default Cell=2)
    Charlie
    Attached Images Attached Images  

  2. #2
    Lianja MVP
    Join Date
    Feb 2012
    Location
    Berea, KY, USA
    Posts
    2,071
    Hi Charlie,

    Change Properties to:

    mycell=h2;

    Or, if there is really supposed to be a space before h and before 2, I would try with MyCell= h 2;

    I have never tried the latter. In fact, I follow the docs way.

    MyCell=;defaultnamedparameters=1;

    This results in no records being queried in the Data Buiilder.

    Then, from the Command Window:

    defaultnamedparameters = "0"
    MyCell = "h2
    or
    MyCell = " h 2"

    depending on which is accurate

    then open the view and it will have requeried.

    Hank

  3. #3
    Member
    Join Date
    Apr 2019
    Location
    UK
    Posts
    44
    Hi Hank
    Thankyou for the info
    I only have the documents which show examples like this below (are there other docs ?)

    So I thought the n was a pre porcessor to denote numeric, but I missed out the : (I was guessing)

    But now, I think it's my SQL that fails
    I've added MyCell=;defaultnamedparameters=1; to the properties

    select * from masterdata where cell = MyCell causes a 'Datasource name not found and no default driver specified'
    whereas - as a test with the same properties just left in
    select * from masterdata where cell = 2 creates a view with cell=2 (works OK)

    So do I need to declare MyCell publicly or Privately etc, or is My SQL wrong

    Basically - I want to fetch data where the field called cell = the variable MyCell


    Charlie
    Attached Images Attached Images  

  4. #4
    Lianja MVP
    Join Date
    Feb 2012
    Location
    Berea, KY, USA
    Posts
    2,071
    Hi Charlie,

    It seems that your database is not the current database.

    Hank

  5. #5
    Member
    Join Date
    Apr 2019
    Location
    UK
    Posts
    44
    Hi Hank
    If I list Stat, it says current database : activearpdata
    masterdata is a table in activearpdata ?
    Charlie
    Last edited by CharlieDenver; 2021-05-11 at 08:59.

  6. #6
    Member
    Join Date
    Apr 2019
    Location
    UK
    Posts
    44
    Hi Hank
    ** Update **
    A few times now I get "Access Denied" , but If I restart the App Builder it sorts it out?

    Anyway ..
    I did that and my Parameterized View functioned OK

    But then I setup as below and have results as listed
    Please can I ask, what if any are the disadvntages of closing and re using a table with the use XXX where XXX command
    (Assuming the update fields are set and other properties as required)

    Is there an equivalent REQUERY() function
    Does the parameterized view method work differently , in terms of close/open table

    Charlie
    Attached Images Attached Images  
    Last edited by CharlieDenver; 2021-05-11 at 11:49.

  7. #7
    Lianja Development Team barrymavin's Avatar
    Join Date
    Feb 2012
    Location
    UK, USA, Thailand
    Posts
    6,246
    Have you read the doc on this.
    https://www.lianja.com/doc/index.php...rameters_Views

    Parameter views are based on named parameters to a VT.

    See virtual table doc specifically the last paragraph at the bottom of the page.
    https://www.lianja.com/doc/index.php...ble_Properties

    looking at your example code I’m curious to know why you feel the need to use virtual tables and parameter views when you are using the native Lianja database.

    When building Lianja apps any data that is bound to a section is required when using a search panel or when the parent section data changes. In common usage it’s all done for you.

    You typically prevent loading of all data on startup by specifying WHERE 1=0 in your VT definitions.

    Please explain your usage scenario and whether you intend to build desktop or web apps and I can better guide you as I feel you are getting bogged down as you don’t understand VT usage properly and may not even need to use them if you use Lianja native database tables
    Last edited by barrymavin; 2021-05-12 at 00:12.
    Principal developer of Lianja, Recital and other products

    Follow me on:

    Twitter: http://twitter.com/lianjaInc
    Facebook: http://www.facebook.com/LianjaInc
    LinkedIn: http://www.linkedin.com/in/barrymavin

  8. #8
    Member
    Join Date
    Apr 2019
    Location
    UK
    Posts
    44
    Hi Barry
    Thanks or replying
    I have read the documents (Many Times)
    Yes absolutely - I am bogged down

    To date I've been using MS-SQL Back End, creating VFP DBC with remote views, updatefield etc (We also have legacy DBF standalone based systems)
    The Intention is to move everything to Lianja in stages I can manage
    First to have desktops Apps and use the LAN only, then secondly to move this all to Lianja Cloud and drop the LAN
    I'm looking at Web Apps too

    It's a learning curve as all I know is VFP - DBC - MS-SQL (I started looking at Lianja a few years back but had to shelve it for various reason)

    So, In essence wrongly or rightly I'm thinking that I will need deploy everything to SQL eventually ?


    In terms of VT , My understanding to date is that these are similar to VFP Views

    How I have been working with VFP ::
    So - say I need a record with other related information from SQL tables that I need to present to a user
    I will have constructed a Remote view with ? on my fields and setup the updatefields etc in the DBC
    Then whatever variables are set to by the user, the user gets the data on request with REQUERY()
    I can use the NODATA on form load when I add my views if needed
    I manage the results of the query in a grid or form

    If I'm barking up the wrong tree, it's going to be a case of re-education
    And likely a total internal rewire, I want to persevere with it, the penny will drop and I'll be away (it's just a case of how long it takes)
    Any guidance much appreciated

    Charlie
    Last edited by CharlieDenver; 2021-05-12 at 08:50.

  9. #9
    Lianja Development Team barrymavin's Avatar
    Join Date
    Feb 2012
    Location
    UK, USA, Thailand
    Posts
    6,246
    Charlie,

    If your intention is to use the Lianja database engine you do not need to over complicate things by using virtual tables. Just drag and drop native Lianja tables onto a page and relate the sections together.

    if your intention is to use MSSQL or other database to store your data then use virtual tables.

    You do not need to use SQL parameters as you have described. It’s much simpler than that..

    For example.

    Create a form section which is bound to a virtual table defined as SELECT * FROM customers WHERE 1=0

    Check the “Search panel visible” attribute in the section attributes.

    Add the customerid field as a search panel field and also as the search field for that section.

    When you open the app no data is retrieved as you have specified WHERE 1=0 on the SQL SELECT statement for the virtual table.

    Now, type a valid customerid into either the search panel or the search box in the page header.

    Lianja will construct a WHERE condition and override the WHERE 1=0 that you have specified in your virtual table definition. No parameters needed.

    Now, you can add other columns to search panel, enable the query builder or programmatically change the filter condition for the section and only those records that satisfy the combined condition will be selected for navigation.

    Additionally, by defining row filters with roles and permissions, you can force restrict rows being accessible to specific users.

    The functionality described above works across desktop, web and mobile apps.

    To better familiarize yourself with Lianja you can mock up a POC (Proof Of Concept) of an app very quickly, then go back through it and refine it.

    There are so many built-in features in Lianja i strongly recommend you go through the example apps and familiarize yourself before embarking on a “Code-first” approach to development.

    PS programmable SQL parameter markers and parameterized virtual tables are two different things. Don’t get bogged down and confuse yourself with fine details of virtual table usage. Build a POC first and you will gain a valuable insight into what can be achieved with little to no coding, this is the traditionally top down development approach I recommend with Lianja.
    Principal developer of Lianja, Recital and other products

    Follow me on:

    Twitter: http://twitter.com/lianjaInc
    Facebook: http://www.facebook.com/LianjaInc
    LinkedIn: http://www.linkedin.com/in/barrymavin

Bookmarks

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
  •  
Journey into the Cloud
Join us