Virtual tables INSERT/APPEND

[SpringBox]

Hi guys

Really impressed with the OLEDB virtual table connection ability but still having a few issues. This used to work but struggling using Lianja 7.2 Beta 3

As per the examples we can add a VT to our database and can browse and access data no problem but when we try to add data INSERT or APPEND, nothing gets added and INSERT always reports an error.

VFP is not open. Original table is a free VFP table. 25 records

ie

OPEN DATABASE PDSW
create virtualtable vt_iPadImport3 connstr "oledb:Provider=VFPOLEDB;Mode=Share Deny None;Data Source=S:\xxxxxxx\iPadImport.dbf;Exclusive=No" as select * from iPadImport

dir - yes the VT is there

use VT_iPadImport3 shared in 0
brow - yes data displayed
GO 6
? name - Test
REPLACE NAME WITH 'Line6'
? name - Line6
skip 0
GO 6
? name - Test

We are logged in as admin and there are no permission issues.

We get a Lianja messagebox saying Failed to update the record

We have tried deleting the VT and remaking it. Does the VT need to be in its own database?

Sometimes we get a SQL update error (below) in response to a REPLACE command and sometimes a "string too long" error.

update IPADIMPORT3 set NAME='Line6' where (NTITLE='' or isnull(NTITLE)) and NAME='test' and (INITIALS='' or isnull(INITIALS)) and (SALUT='' or isnull(SALUT)) and (ADL1='' or isnull(ADL1)) and (ADL2='' or isnull(ADL2)) and (ADL3='' or isnull(ADL3)) and (ADL4='' or isnull(ADL4)) and (POSTCODE='' or isnull(POSTCODE)) and (TELHOME='' or isnull(TELHOME)) and (TELWORK='' or isnull(TELWORK)) and (CONAME='' or isnull(CONAME)) and (MAKE='' or isnull(MAKE)) and (MODEL='' or isnull(MODEL)) and (CC=0 or isnull(CC)) and (DOORS=0 or isnull(DOORS)) and (BODY='' or isnull(BODY)) and (MILES=0 or isnull(MILES)) and (COLOUR='' or isnull(COLOUR)) and (TRIM='' or isnull(TRIM)) and (REGNO='' or isnull(REGNO)) and (REGDATE=ctod('') or isnull(REGDATE)) and (FUEL='' or isnull(FUEL)) and (GEARS='' or isnull(GEARS)) and (EXTRAS='' or isnull(EXTRAS)) and (OWNERS=0 or isnull(OWNERS)) and (CAP_ID='' or isnull(CAP_ID)) and (SMNO=0 or isnull(SMNO)) and (SOURCE=0 or isnull(SOURCE)) and (FIRSTCONT=ctod('') or isnull(FIRSTCONT)) and (LAST_OTDB=ctod('') or isnull(LAST_OTDB)) and (WANTS='' or isnull(WANTS)) and (NEW_USED='' or isnull(NEW_USED)) and (W_TRANS='' or isnull(W_TRANS)) and (W_FUEL='' or isnull(W_FUEL)) and (FUNDS='' or isnull(FUNDS)) and (BUDGET=0 or isnull(BUDGET)) and (OTDB='' or isnull(OTDB)) and (FINCO='' or isnull(FINCO)) and (PAYMENT=0 or isnull(PAYMENT)) and (PERIOD=0 or isnull(PERIOD)) and QUALDRIVE=.f. and DEMO=.f. and W_UP=.f. and DEALT=.f. and METSMGR=.f. and METBUSMGR=.f. and (LOSTSALE=0 or isnull(LOSTSALE)) and (BUYDAT=ctod('') or isnull(BUYDAT)) and (HANDOVER=ctod('') or isnull(HANDOVER)) and (NEXTCONT=ctod('') or isnull(NEXTCONT)) and (NEXTFOLLOW='' or isnull(NEXTFOLLOW)) and (OTDB_TYPE=0 or isnull(OTDB_TYPE)) and (FOLLOW_NO=0 or isnull(FOLLOW_NO)) and (MOTDUE=ctod('') or isnull(MOTDUE)) and (WARRDESC='' or isnull(WARRDESC)) and (WARREXP=ctod('') or isnull(WARREXP)) and (WARR_REFNO='' or isnull(WARR_REFNO)) and (LOST_NUM=0 or isnull(LOST_NUM)) and (WARRNUM=0 or isnull(WARRNUM)) and (PREV_OTDB='' or isnull(PREV_OTDB)) and (PREV_HOVER=ctod('') or isnull(PREV_HOVER)) and (DEALT_REG='' or isnull(DEALT_REG)) and (COMMENTS='' or isnull(COMMENTS)) and (FIND_CAR='' or isnull(FIND_CAR)) and (STOCK_NO='' or isnull(STOCK_NO)) and (NAMESOUND='' or isnull(NAMESOUND)) and (CARFROM=0 or isnull(CARFROM)) and (SETT=0 or isnull(SETT)) and (WALK_PHONE='' or isnull(WALK_PHONE)) and (CO_REF='' or isnull(CO_REF)) and (TELFAX='' or isnull(TELFAX)) and (TELMOBILE='' or isnull(TELMOBILE)) and (BUS_TYPE='' or isnull(BUS_TYPE)) and (HOTNESS=0 or isnull(HOTNESS)) and CUST_ID='_049540012' and (APPSPEC='' or isnull(APPSPEC)) and (APPSERHIST='' or isnull(APPSERHIST)) and (APPLSERV=ctod('') or isnull(APPLSERV)) and (APPPAINT='' or isnull(APPPAINT)) and (APPTYRES='' or isnull(APPTYRES)) and (APPOTHER='' or isnull(APPOTHER)) and (APPPREP=0 or isnull(APPPREP)) and (APPSIV=0 or isnull(APPSIV)) and (APPVALUED='' or isnull(APPVALUED)) and (APPDATE=ctod('') or isnull(APPDATE)) and (POSITION='' or isnull(POSITION)) and ISCHANGED=.f. and (RFLEXPIRE=ctod('') or isnull(RFLEXPIRE)) and (FINEXPDATE=ctod('') or isnull(FINEXPDATE)) and APPT=.f. and (EMAIL='' or isnull(EMAIL)) and (APPRSL_NO='' or isnull(APPRSL_NO)) and COMPANY_NO=6622 and (ADL5='' or isnull(ADL5)) and NOCONTACT=.f. and (CONTMETHOD=0 or isnull(CONTMETHOD)) and (DEPTTYPE='' or isnull(DEPTTYPE)) and (HOBBY1='' or isnull(HOBBY1)) and (HOBBY2='' or isnull(HOBBY2)) and (DEAL_ID='' or isnull(DEAL_ID)) and (EXTCUST_ID='' or isnull(EXTCUST_ID)) and (EXTSMNAME='' or isnull(EXTSMNAME)) and NOPHONE=.f. and NOTEXT=.f. and NOEMAIL=.f. and NOPOST=.f. and TPS=.f. and IMPORTED=.f. and ASKMGRDEL=.f. and ISEVENT=.f. and RRLEAD=.f. and (RRLEADID='' or isnull(RRLEADID)) and (RRLEADURL='' or isnull(RRLEADURL)) and (WANTSMAKE='' or isnull(WANTSMAKE)) and (TYREOSF=0 or isnull(TYREOSF)) and (TYREOSR=0 or isnull(TYREOSR)) and (TYRENSF=0 or isnull(TYRENSF)) and (TYRENSR=0 or isnull(TYRENSR)) and (TYRESPARE=0 or isnull(TYRESPARE)) and APPBODY=.f. and APPGLASS=.f. and APPMECH=.f. and APPINT=.f. and APPSMELL=.f. and APPDRIVEN=.f. and (TIMESTAMP=ctot('') or isnull(TIMESTAMP)) and CUSTGUID='da880650-f4d1-4539-9840-12567db7f368' and IPDMATCHSP=.f. and (IPADDRESS='' or isnull(IPADDRESS)) and INDIARY=.f. and (DIARYADDED=ctot('') or isnull(DIARYADDED)) and (DIARYDELAY=0 or isnull(DIARYDELAY)) and (DMPAY=0 or isnull(DMPAY)) and (DMPERIOD=0 or isnull(DMPERIOD)) and (DMCASH=0 or isnull(DMCASH)) and (ANNMILES=0 or isnull(ANNMILES)) and (WBODY='' or isnull(WBODY)) and (WFUEL='' or isnull(WFUEL)) and (WTRANS='' or isnull(WTRANS))


Any thoughts?

Cheers


SpringBox

[barrymavin]

If you submit a ticket and attach the VFP table I will look into it.

I notice you haven’t specified a primarykey.

[barrymavin]

Hi Simon

So based on the ticket you submitted with a sample table I have investigated what you reported.

Let me explain…

A VT whether it uses odbc or oledb should have a basetable and a primarykey specified.

Without a primarykey and wheretype=3 not specified causes the sql update to include comparisons for all columns to handle optimistic locking.

VFP has limits on the length of the command line and the SQL command length. That’s the issue you are experiencing.

I have reported these errors better in 7.2beta4 rather than just failing without proper error messages being reported.

Nevertheless, you need to specify a primary key and a basetable. You can do this in the UI or using the alter virtualtable command in the console.

[SpringBox]

Hi Barry

Thanks for the reply. Have downloaded 7.2beta4 and we can now REPLACE data no problem in VT - thanks

However INSERT (Error 0) / APPEND (Record out of range) is still not working and always errors. Understand about string length so to test now have a VFP table with just 2 fields, both CHAR indexed on cust_id. Same error.

Tried everywhere to find the full connection string options / syntax for VFPOLEDB.

Does our connection string look right? What is wheretype=3 from your previous answer?

create virtualtable pdsw!vt_iPadImport connstr "oledb:Provider=VFPOLEDB;Mode=Share Deny None;Data Source=S:\pdscode9\iPadImport.dbf;Exclusive=No;Bas etable=iPadImport.dbf;Primarykey=cust_id" as select * from iPadImport


When we try with just the 2 fields

insert into vt_ipadImport ;
(name, cust_id);
VALUES ;
("TempName3", "something")

produces the same Error 0

Any thoughts?

Cheers

Simon

[barrymavin]

Hi simon

set debug on

look in the debug files and attach to a ticket.

[barrymavin]

Hi Simon

There was an issue in that last beta and I fixed it in Beta5.

[SpringBox]

Thanks Barry - works fine now.