Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: working with virtual tables

  1. #1
    Senior Member
    Join Date
    Oct 2012
    Posts
    125

    working with virtual tables

    the cursor for the virtual table has a requery() method. If I understand well, this parameter must conform to the syntax indicated by the dbtype property. Otherwise, requery() returns false and the cursor closes.
    Lianja is doing some transformation because e.g. 'like "*"' is replaced by 'like "%"' in the select statement.
    Is there another conversion that Lianja does? Could this conversion be affected?
    Someone has already written a conversion algorithm that "translate" the LIANJA / VFP sql dialect to e.g. mysql / mariaDB ?

  2. #2
    Lianja Development Team barrymavin's Avatar
    Join Date
    Feb 2012
    Location
    UK, USA, Thailand
    Posts
    5,972
    The whole point of virtual tables is that they are database independent.

    requery() is just the same as CREATE VIRTUALTABLE in the sense you need to do what you are doing.

    *most* SQL databases have some level of compatibility for built-in functions.

    When you use requery() you typically alter the WHERE condition...

    What exactly are you trying to achieve.
    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

  3. #3
    Senior Member
    Join Date
    Oct 2012
    Posts
    125
    there are a lot of rows in the sql database table. If the wirtual table definition is
    Code:
    "select * from db.table"
    , the execution of
    Code:
    sqleval("select * from virtualtable where field1 like 'a*' ")
    function can take a long time.
    Code:
    Requery("field1 like 'a*' ")
    seems to be a better solution. (Here 'a*'-> 'a%' conversion is done automatically by Lianja)
    However, if the where condition is more complex: eg
    Code:
    sqleval("select * from virtualtable where field1 like 'a*' .and. .not. (Empty(field2))")
    , then the parameter for requery must look something like
    Code:
    "field1 like 'a%' and not (field2 = '') "
    , but this "translation "does not happen automatically
    Last edited by strato; 2020-09-30 at 07:21.

  4. #4
    Lianja Development Team barrymavin's Avatar
    Join Date
    Feb 2012
    Location
    UK, USA, Thailand
    Posts
    5,972
    Firstly, you can use "not" rather than ".not.". the "."s are optional and are VFP dialect specific.

    Secondly, there are a wide variety of ways to perform virtual table queries. Look in the doc for "using parameterized views" and the virtual table properties.

    https://www.lianja.com/doc/index.php...rameters_Views
    https://www.lianja.com/doc/index.php...ble_Properties

    Thirdly, you don't select from a virtualtable you simply

    use where <condition>

    if you want to reduce the resultset otherwise you are fetching all data from the remote server and then filtering that on the client. Poor design.

    https://www.lianja.com/doc/index.php/USE
    https://www.lianja.com/doc/index.php/CursorAdapter

    You can try this interactively in the console.

    So, to reduce a large dataset you can...

    use vt_name nodata
    ca = cursoradaptor()
    ca.requery( "condition" )
    ...
    ca.requery( "condition" )
    ...
    etc

    If you want to code everything yourself.
    Last edited by barrymavin; 2020-09-30 at 07:35.
    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

  5. #5
    Senior Member
    Join Date
    Oct 2012
    Posts
    125
    As I see it, the problem is similar for each solution. The database independence of virtual tables is true, but for a specific app it is limited. Beyond one point you need to know the backend sql engine of the virtual table.
    The specific content of the setnamedparameter ("name", "value") for the page, the quotestring property for the Virtual Table, the WHERE <sqlpassthru> for use, and the requery ("condition") for the cursor adapter raise the same problem. Writing a database-independent app is only possible if these parameters are set according to the current database background.
    If I think well, it is possible to describe the parameters for each database separately, or you need a "translator" ...

  6. #6
    Lianja Development Team barrymavin's Avatar
    Join Date
    Feb 2012
    Location
    UK, USA, Thailand
    Posts
    5,972
    Well you are entitled to your opinion but I don't agree.

    I'm not sure what you are building but its not normal business Apps from what I gather.

    Once the virtual table has been created you can use it in Lianja Apps. Of course you need to know what the remote database tables are called and what data is contained within the columns otherwise it all makes no sense.

    If you don't know basic things about the remote database why and how can you use it?

    So you want to build a database independent App?

    Build your App against a database. A Lianja database is a container for tables and virtual tables.

    Create a database container for each of your target remote database types. Create your virtual tables as required for that database if you can't do it using the standard tools and features in Lianja.

    Read the doc links i provided earlier.

    Look at ALTER VIRTUALTABLE.

    If you need something special submit an ER.
    Last edited by barrymavin; 2020-09-30 at 08:54.
    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

  7. #7
    Lianja MVP
    Join Date
    Feb 2012
    Location
    Berea, KY, USA
    Posts
    2,011
    re: backend -- if you read the docs, you will see there are multiple ways to change the connection at runtime.

    I have an app that changes which sql database based on a user characteristic that is looked up in a separate database (that way I add a record and the new user is good-to-go). It's been in production for 21 months. So: it works, keep digging in the docs.

    Hank

    Hank

  8. #8
    Senior Member
    Join Date
    Oct 2012
    Posts
    125
    similar problem. 2 databases, with the same structure, one lianja, another mariadb. In one table, there are a few million records, of which the user only wants to manage between 2 specified dates. What should I write in the Where condition (called a parameter) so that it can be used in both cases? ... what should I write if I only need records in which the data in a given (third) column is still empty?

  9. #9
    Lianja MVP
    Join Date
    Feb 2012
    Location
    Berea, KY, USA
    Posts
    2,011
    Hi strato,

    It's all in the index. If you Google "lianja index date" with the quotes and you will find the articles you need that pertain to working with dates in Lianja

    If you are hitting a remote database (SQL Server, etc.) make sure there's an index on the field that has the date.

    How to use dates in a VT in Lianja is no different than in a SQL view anywhere.

    The namedparameters stuff is a brilliant way to handle parameters while the VT is not in use (but is live, in the Data Manager) and while in use in applications.

    Hank

  10. #10
    Lianja Development Team barrymavin's Avatar
    Join Date
    Feb 2012
    Location
    UK, USA, Thailand
    Posts
    5,972
    FYI Lianja handles this "translation" as you call it internally when virtual tables are used in Lianja Apps. It builds the SQL statements dynamically to perform CRUD operations on remote databases. It is more involved than just building a WHERE condition as nulls also have to be taken into consideration.

    What you seem to be asking is... Is there a way Lianja can expose that functionality so that you can programmatically use it in your custom code so you don't have to re-invent the wheel?
    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

Page 1 of 2 12 LastLast

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