Results 1 to 7 of 7

Thread: Virtual tables INSERT/APPEND

  1. #1
    Senior Member
    Join Date
    Jan 2014
    Posts
    374

    Virtual tables INSERT/APPEND

    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

  2. #2
    Lianja Development Team barrymavin's Avatar
    Join Date
    Feb 2012
    Location
    UK, USA, Thailand
    Posts
    6,729
    Blog Entries
    18
    If you submit a ticket and attach the VFP table I will look into it.

    I notice you havenít specified a primarykey.
    Principal developer of Lianja, Recital and other products

    Follow me on:

    Twitter: http://twitter.com/lianjaInc
    Facebook: http://www.facebook.com/LianjaInc
    LinkedIn: http://www.linkedin.com/in/barrymavin

  3. #3
    Lianja Development Team barrymavin's Avatar
    Join Date
    Feb 2012
    Location
    UK, USA, Thailand
    Posts
    6,729
    Blog Entries
    18
    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.
    Principal developer of Lianja, Recital and other products

    Follow me on:

    Twitter: http://twitter.com/lianjaInc
    Facebook: http://www.facebook.com/LianjaInc
    LinkedIn: http://www.linkedin.com/in/barrymavin

  4. #4
    Senior Member
    Join Date
    Jan 2014
    Posts
    374
    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

  5. #5
    Lianja Development Team barrymavin's Avatar
    Join Date
    Feb 2012
    Location
    UK, USA, Thailand
    Posts
    6,729
    Blog Entries
    18
    Hi simon

    set debug on

    look in the debug files and attach to a ticket.
    Principal developer of Lianja, Recital and other products

    Follow me on:

    Twitter: http://twitter.com/lianjaInc
    Facebook: http://www.facebook.com/LianjaInc
    LinkedIn: http://www.linkedin.com/in/barrymavin

  6. #6
    Lianja Development Team barrymavin's Avatar
    Join Date
    Feb 2012
    Location
    UK, USA, Thailand
    Posts
    6,729
    Blog Entries
    18
    Hi Simon

    There was an issue in that last beta and I fixed it in Beta5.
    Principal developer of Lianja, Recital and other products

    Follow me on:

    Twitter: http://twitter.com/lianjaInc
    Facebook: http://www.facebook.com/LianjaInc
    LinkedIn: http://www.linkedin.com/in/barrymavin

  7. #7
    Senior Member
    Join Date
    Jan 2014
    Posts
    374
    Thanks Barry - works fine now.

Bookmarks

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Journey into the Cloud
Join us