Virtual table "local" does not function Read/Write

[cib]

Virtual table "local" does not function Read/Write
I try use local virtual table, but I can't write changes to original table:

open database southwind
create virtualtable vtcustomers alias "vtcustomers" connstr "local" as select * from customers
use vtcustomers
brow && write some changes
use
use vtcustomers
brow && changes not present

Please where is error?

[avianmanagement]

Not sure just guessing here, do you need to requery the vt before the 2nd use of it ?

[yvonne.milne]

Hi,

Can you Submit a Ticket for this, please?

Thanks,

Yvonne

[cib]

In RC9 it is already fixed.

Now I tried add column to virtual table with left join in Console:

open database southwind
create virtualtable vtProdSupp alias "vtProdSupp" connstr "local" as select *,suppliers.companyname from products left join suppliers on products.supplierid = suppliers.supplierid

For reading was all OK, but write modificatios are not possible. I assumed, that columns from the root table can be modified, only added column is readonly.

Martin

[hmischel@diligentsystems.com]

Hi Martin,

You can, but you have to let Lianja know which fields you want to ignore.


For example, I created two tables.

CREATE table testfname (NAME CHAR(10),id int)

CREATE table testlname (NAME CHAR(10),id int)

INSERT INTO testfname VALUES ('Herb',1)
INSERT INTO testlname VALUES ('Mischel',1)

I create my virtual table using both tables.

create virtualtable v_name connstr 'driver={SQL server};server=testserver;Trusted_Connection=Yes;d atabase=SQLAdmin' as select a.name fname ,b.name lname from testfname a inner join testlname b ON a.id = b.id

When I browse, I get the following

Herb|Mischel

When I update the Firstname. Here is the SQL that gets generated.

update testfname set FNAME='Herbert', LNAME='Mischel01' where isnull(FNAME,'')='Herb' and isnull(LNAME,'')='Mischel'

The problem of course is that Lname is not part of the backend table, So need to make sure the lname is not in the update query.

So When I create my virtualTable, I need to let Lianja know that I don't want certain fields as part of the update query.

The way to do that is at the time of creating your virtualtable using the keyfieldlist and properties attributes.

http://www.lianja.com/doc/index.php/CREATE_VIRTUALTABLE

alter virtualtable v_name connstr 'driver={SQL server};server=testserver;Trusted_Connection=Yes;d atabase=SQLAdmin' Keyfieldlist '*,-lname' properties 'updatefieldlist=*,-lname' as select a.name fname ,b.name lname from testfname a inner join testlname b ON a.id = b.id

Now when I update the fname (or any field), the following SQL is sent to the backend.

update testfname set FNAME='herbert' where isnull(FNAME,'')='herb'

VT can be very powerful.

Herb

[cib]

I added properties "updatefieldlist=*,-companyname". Still the same problem, cannot update in Console "brow" window:

open database southwind
create virtualtable vtProdSupp alias "vtProdSupp" connstr "local" properties "updatefieldlist=*,-companyname" as select *,suppliers.companyname from products left join suppliers on products.supplierid = suppliers.supplierid

Martin

[hmischel@diligentsystems.com]

Hi Martin,

Perhaps your query syntax is causing some confusion. Try this.

as select a.*,b.companyname from products a left join suppliers b on a.supplierid = b.supplierid.

Also - you need to use the keyfield list.

Herb