working with virtual tables

[strato]

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 ?

[barrymavin]

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.

[strato]

there are a lot of rows in the sql database table. If the wirtual table definition is

"select * from db.table"

, the execution of

sqleval("select * from virtualtable where field1 like 'a*' ")

function can take a long time.

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

sqleval("select * from virtualtable where field1 like 'a*' .and. .not. (Empty(field2))")

, then the parameter for requery must look something like

"field1 like 'a%' and not (field2 = '') "

, but this "translation "does not happen automatically

[barrymavin]

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.

[strato]

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" ...

[barrymavin]

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.

[HankFay]

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

[strato]

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?

[HankFay]

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

[barrymavin]

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?