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.

[hmischel@diligentsystems.com]

Here is something I do internally, but it might be worth incorporating in the architecture.

Perhaps having a setting in the virtual table that specifies a fetchsize based on a key.

Something like this

create virtualtable myorders connstr 'my_dsn' alias myorders properties "FetchSize =50" "Key = OrderID" as select * from orders

So If I have a thousand rows in orders and I select * from the table, only the first 50 would show in my grid (unless I pass in specific Orderid)
I envision the grid would have an action toolbar with navigation buttons and have a caption "showing 1-50 of 1000" so the user knows there are more rows.

I think this could help developers who may not be using this type of logic in there backend stored procedures.

Herb

[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

[barrymavin]

Herb, the problem with that approach is the full query is still performed on the server. There is a fetchsize in the CA. I need to enable it in the properties of the VT.

One of the shortcomings of MSSQL is the lack of a limit clause on sql select. Most other databases (e.g MySQL, LIanjaSQL) have this. MSSQL TOP(n) is not a substitute when paging through large amounts of data.

I will revisit deferred fetches later, only drawback with it (having looked at it) it requires a permanent connection to the server resultset which can be resource intensive on a heavily loaded system.