Client/Server data access made easy in RC5.5

[barrymavin]

In Lianja App Builder RC5.5 client/server data access just got a whole lot easier. These details are in another post I've done containing the changelog but i'll reproduce it here as it's worth highlighting.

In RC5.5 I've added requery(expC) as a method in the CursorAdaptor class.

If the CursorAdaptor is bound to a VirtualTable then expC is the *new* SQL WHERE clause which is used to requery the remote ODBC data.

The local cursor data is then replaced with the result of the query. This can be used very effectively in the section parentdatachanged event.

It is worth remembering that in Lianja changes to the underlying local cursor are automatically reflected in the remote data source; there is no special coding required to achieve this.



// create a VirtualTable that selects no data (where 1=0 to will do that).
// This will typically be bound to a Form or Grid section in our UI.
create virtualtable orders connstr "mssql_dsn" as select * from sales.orders where 1=0
use orders
ca = cursorAdaptor()

// Notice how we can use {...} macros that are substituted at the time of
// the query in the underlying CursorAdaptor
ca.requery("custtid={customers.id} and order_date='20130404'")

// now update our grid containing the orders for the customer whose details
// are in a "Customers" form section above our "Orders" grid section.
Lianja.getObject("mydatagrid").refresh()

[hmischel@diligentsystems.com]

I have a need for this now.

When I create the cursorAdaptor, do I need to specify the virtualTableName?

In your example above, you are using the orders virtualTable, but you have not selected it. So how does Lianja know that ca is the cursoradaptor name referencing orders?


For now, I am just altering the virtualtable and that seems to work fine. Is there a benefit of using requery with a cursoradaptor over altering the virtualtable?

Thanks.

Herb

[lianjasupport]

Hi herb,

After the use statement that cursor is active so the cursoradaptor() function defaults to the current cursor if no alias is specified.

Yes requery is faster as the connection is already made.

[lianjasupport]

Also you can't be altering the virtualtable in a shared multi user environment if it is shared on a LAN.

[hmischel@diligentsystems.com]

It's smart enough to work with stored procedure parameters.
Very nice.

For anyone else using SQl Server, you can just specify your stored proc parameters in the cursoradaptor requery.

This is working nicely for me.

Create virtualtable v_closestus connstr 'driver={SQL server};server=testserver1;Trusted_Connection=Yes; database=Demo2' as execute p_getclosestlocationsUS @lat ={m.lat},@long = {m.long},@rows=20,@type ='All'
use v_closestus
ca = cursoradaptor()
ca.requery("@lat={m.lat} and @long = {m.long}")

This function hasnt gotten nearly the fanfare it deserves.
:)

[HankFay]

Also you can't be altering the virtualtable in a shared multi user environment if it is shared on a LAN.

Hi Barry,

could you explain this? The reason I ask is that when I deploy an app (that opens a VT and messagebox()'s the DBF() of the cursor) I am seeing two different temporary files. It would seem that changes to these temporary files would be handled separately, hence my confusion.

thanks,

Hank356

[hmischel@diligentsystems.com]

I understood this to mean that we shouldn't change the Data Definition of the virtual table, not the data that is being stored for each individual session.

I am under the impression that the Virtualtable is just a shell for each separate instance of the application. In essence a temporary table.

Herb

[HankFay]

I understood this to mean that we shouldn't change the Data Definition of the virtual table, not the data that is being stored for each individual session.

I am under the impression that the Virtualtable is just a shell for each separate instance of the application. In essence a temporary table.

Herb

Oh, you mean like doing an ALTER TABLE in the application? I think you're right: nothing else makes sense.

Hank

[barrymavin]

Thats correct. You cannot "change" the virtualtable using ALTER TABLE when deployed as many users will be opening and closing it concurrently. You cannot ALTER TABLE anything while in shared data runtime mode. So it is better and faster to use requery().

All SQL SELECTs create a temporary table and remove it when completed.

As an aside: Lianja uses (configurable) tmpfs on linux to store temporary tables. tmpfs is basically an in-memory temporary table so this is a big performance boost on app servers that have network connected data servers.

[HankFay]

Thats correct. You cannot "change" the virtualtable using ALTER TABLE when deployed as many users will be opening and closing it concurrently. You cannot ALTER TABLE anything while in shared data runtime mode. So it is better and faster to use requery().

All SQL SELECTs create a temporary table and remove it when completed.

As an aside: Lianja uses (configurable) tmpfs on linux to store temporary tables. tmpfs is basically an in-memory temporary table so this is a big performance boost on app servers that have network connected data servers.

That's great about the temp tables. In VFP with large queries (both wide and long, in reporting), it is not unusual to have .tmp file creation be the factor that, after everything else is tweaked, slows down the application, and in doing so creates measurable -- been there, done that -- queues on SQL Server.

[hmischel@diligentsystems.com]

Something looks to have changed recently on the cursoradaptor.

If you now create a table with 1=0, that where statement is staying even if you requery().

So in my trace I am seeing a double where clause.

Should I open a ticket?

Herb

[HankFay]

Something looks to have changed recently on the cursoradaptor.

If you now create a table with 1=0, that where statement is staying even if you requery().

So in my trace I am seeing a double where clause.

Should I open a ticket?

Herb

Hi Herb,

I saw that on some complex queries, but couldn't put together an easy example to submit a ticket. I could recreate it on straight VT's in SouthWind, or not single-joins. At that point I kept I wrote a wrapper that takes care of the requerying for me, using a straight requery where possible, and when not, munging the sql, resetting the selectmd, and doing a cursorrefresh.

Hank

[hmischel@diligentsystems.com]

Hi Hank,

Its not a show stopper. I just removed the original where clause and put the static where (in my case and not null on a specific column) and added an "And response ='{m.response}'"

Which works fine too.

I wasn't sure if this change was by design or not, so I have not opened a ticket.

Herb

[barrymavin]

Something looks to have changed recently on the cursoradaptor.

If you now create a table with 1=0, that where statement is staying even if you requery().

So in my trace I am seeing a double where clause.

Should I open a ticket?

Herb

Yes