Data source independent connections in coming in RC5.3

[barrymavin]

In Lianja App Builder RC5.3 i've introduced "local" as a "known" connection to provide the ability to work with data locally then switch to server connections at runtime.

sqlConnect("local") and create VirtualTable myvtable connstr "local" can now be specified to work on local Lianja SQL cursors.

The connection string (or ODBC DSN) can also contain {macro} so that the connection can be switched programmatically without making any code changes.

[HankFay]

From the "what does this mean for developers" dept.: this means we can develop in the app builder, without deploying to Lianja SQL Server (LSql), in the exact same way that we will be deploying to LSql (or MS SQL or whatever), but without having to move data to LSql or wherever during development.

In this scenario, you might create a public variable in the App INIT (in App Settings, the cog on the bottom left of the app builder) holding the connection string (or multiples if you are going to have multiple backends), and then define the Virtualtable like so:

create virtualtable myDB!myvt connstr '{m.myconnstr}' as select * from mytable where myfield = '{m.myfieldval}' order by myotherfield

Change the value of m.myconnstr, open the view, and you're hitting against a different backend.

Notice the use of macro-substitution rather than parameters: this helps protect against sql injection attacks, and yet is completely transparent while developing.

BTW: have you ever wanted to change a view so it could query against, e.g., a different relation, i.e., a different Foreign Key value? In case you missed it, you can do that also:

use myDB!myVT in 0
loCA = cursoradapter("myVT")

At that point, you can change the SelectCmd for the CA, and the Requery the CA.

Fantastic stuff! Can't wait to play with it.

Hank

[phabio]

Hi all,
these days, I write a little, but I read it all .. luckily I have a lot of work.

quali sono le performance delle "virtual table"?
I can use as a datasource in FormPage?

thanks

[HankFay]

Hi all,
these days, I write a little, but I read it all .. luckily I have a lot of work.

quali sono le performance delle "virtual table"?
I can use as a datasource in FormPage?

thanks

Hi Phabio,

performance is the same as any other cursor in Lianja: it's a cursoradapter underneath, like every other cursor (including USE <table>) in Lianja.

A VT is a datasource like any other datasource.

Hank

[phabio]

then, if I create a VT from a table with many records, is charged locally the entire table?

View with VFP I had performance problems ..

[HankFay]

then, if I create a VT from a table with many records, is charged locally the entire table?

View with VFP I had performance problems ..

Hi Phabio,

when working with VFP views, are you trying to pull all records down for a large table? That's the only situation I've found where it's slow, but it's supposed to be slow.

Hank

[phabio]

Yes, the problem is exactly that.. :-)
In my old application I've used the view as a Datasource in some form.. but with more records, the performance in a lan application are not good..

[avianmanagement]

If you use parameters to pull just the data you want it is not so slow

[barrymavin]

The best solution when you have a lot of records on the server is to setup the VirtualTable to select either only one or no records then query them in the parentdatachanged delegate.

// MSSQL
create virtualtable myorders connstr 'my_dsn' alias myorders as select top(1) * from orders 

// MySQL
create virtualtable myorders connstr 'my_dsn' alias myorders as select * from orders limit 1

// LianjaSQL (optimizer does the work and just retrieves table metadata)
create virtualtable myorders connstr 'my_dsn' alias myorders as select * from orders where 1=0

// or alternatively (which is more database independent and faster)
create virtualtable myorders connstr 'my_dsn' alias myorders as select * from orders where 1=0

Now when you actually want to fetch related data you can just do this in the parentdatachanged delegate:

select myorders
ca = cursorAdaptor()

// Tell the CA what we want to retrieve
ca.selectCmd = "select * from orders where custid={customers.id}

// refresh the data from the server
ca.refresh()

// now update our UI grid section from the local cursor
Lianja.getElementByID("mygrid").refresh()

End result == less network traffic which we all like (and so do our users as the App is faster).

Now worth noting in RC5.3 (already released)

create virtualtable myorders connstr 'my_dsn' alias myorders properties 'autosync=1" as select top(1) * from orders

Will keep the local and remote cursor data "in sync" as changes are made multi-user.

[HankFay]

Yes, the problem is exactly that.. :-)
In my old application I've used the view as a Datasource in some form.. but with more records, the performance in a lan application are not good..

As David said, you use parameters (which Barry helpfully showed how to change on-the-fly). Under VFP the view would be opened with the NODATA command (use myview nodata), and then requeried. Until Barry adds NODATA to the USE command, it's easy enough to put the code Barry used into a little prg like pspOpenVT(<cview>,<calias>,<lNoData>,...) (our library routines begin with "psp" so they are easy to keep straight). Then you provide a place of the user to query which records they want.

If you want to show them all 2M records, Herb's idea (below), which VFP uses (except I think the default is 100 records), can work. But you will still need to give the user a way to select from that many records, so what's gained by showing them the first 50 records they probably don't want (of the 2M)?

Hank