Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Virtual table & insert/delete/update

  1. #1
    Member
    Join Date
    Apr 2013
    Posts
    88

    Virtual table & insert/delete/update

    Hi, created a virtual table for a table on MS Sql Server, definition is
    Tables in Database: msdev
    Name Path Type
    ------------------- ------------------------------------------------------------ --------------------
    + persons C:\Lianja\data\msdev\persons.dbf VirtualTable
    | connstr 'Driver={ODBC Driver 11 for SQL Server};Server=192.168.0.10,1434;Database=dev1;Uid =sa;Pwd=sa'
    | alias 'PERSONS'
    | as select * from dev1.dbo.persons

    can select
    select * from persons

    PID LASTNAME FIRSTNAME

    100 Smith John
    200 Brown Paul
    300 Poe Edgar

    3 records selected in 0ms

    but an insert/delete/update through the virtual table gives following error
    Thu 2. May 21:15:18 2013
    **** Lianja error ****
    insert into persons values (400,'Ritchie','Dennis')
    ^
    Cannot append record - errno 2

    Any way to find more about this 'errno 2' ? (the ms sqlserver account used has all the rights on this table)

    Regards

  2. #2
    Lianja Development Team barrymavin's Avatar
    Join Date
    Feb 2012
    Location
    UK, USA, Thailand
    Posts
    5,771
    How are you performing these operations?

    To see what's going on the SET DEBUG ON and look in the debug_client.txt file. It will contain info about the operations being performed and any data source specific error details.
    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
    Member
    Join Date
    Apr 2013
    Posts
    88
    Doing that from App Builder Recital/Vfp console

    used DEBUG got
    *
    * Lianja embedded database engine
    * Version 10.0.4
    * Compiled on May 2 2013 14:00:13
    *
    Error reported from file lib_exp1.c line 499 n=42
    cmdline=ODBC Driver 11 for SQL Server
    g_current=0
    database=C:\Lianja\data\msdev\
    u->name=<null>
    u->alias=<null>
    Stacktrace:

    Error reported from file lib_exp1.c line 499 n=42
    cmdline=ODBC Driver 11 for SQL Server
    g_current=0
    database=C:\Lianja\data\msdev\
    u->name=<null>
    u->alias=<null>
    Stacktrace:

    Error reported from file lib_exp1.c line 499 n=42
    cmdline=ODBC Driver 11 for SQL Server
    g_current=0
    database=C:\Lianja\data\msdev\
    u->name=<null>
    u->alias=<null>
    Stacktrace:

    Error reported from file lib_exp1.c line 499 n=42
    cmdline=ODBC Driver 11 for SQL Server
    g_current=0
    database=C:\Lianja\data\msdev\
    u->name=<null>
    u->alias=<null>
    Stacktrace:

    Error reported from file sql_insert.c line 573 n=127
    cmdline=insert into persons values(300,'Poe','Edgar')

    g_current=0
    database=C:\Lianja\data\msdev\
    u->name=c:\users\briensph\appdata\local\temp\_000015 700008.dbf
    u->alias=PERSONS
    Stacktrace:

  4. #4
    Lianja Development Team barrymavin's Avatar
    Join Date
    Feb 2012
    Location
    UK, USA, Thailand
    Posts
    5,771
    Thats not how you use virtual tables. They provide transparent data access via the local cursor.

    Use the NoSQL VFP-style commands on it.

    Also, thats the wrong log debug_client.txt is the log relating to the client.
    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

  5. #5
    Member
    Join Date
    Apr 2013
    Posts
    88
    ran the following from Recital/Vfp console tab, (everything went fine until a lianja crash on append blank)

    set debug on
    open database msdev
    dir
    use persons
    goto top
    ?persons.pid
    ?persons.lastname
    ?persons.firstname
    goto bottom
    ?persons.pid
    append blank

    debug_client says
    Class Cursoradaptor Object OBJECT(221700856,0) Method: name='CONSTRUCTOR'
    File ../../rco_cursoradaptor.cpp at line 586 datasource=Driver={ODBC Driver 11 for SQL Server};Server=10.231.93.64,1434;Database=dev1;Uid =sa;Pwd=sa username= password=
    File ../../dataadaptor.cpp at line 425 fetch() query=SELECT VERSION(1) as VER FROM sysresultset rowcount=0 colcount=0 hasFeature(QSqlDriver::QuerySize)=0
    File ../../dataadaptor.cpp at line 430 lasterror=[Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Executing SQL directly; no cursor. [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]'VERSION' is not a recognized built-in function name. QODBC3: Unable to execute statement
    File ../../dataadaptor.cpp at line 439 query=SELECT VERSION(1) as VER FROM sysresultset rowcount=0 colcount=0
    File ../../dataadaptor.cpp at line 425 fetch() query=SELECT TOP(1) * FROM sys.tables rowcount=1 colcount=27 hasFeature(QSqlDriver::QuerySize)=0
    File ../../dataadaptor.cpp at line 439 query=SELECT TOP(1) * FROM sys.tables rowcount=1 colcount=27
    File ../../rco_cursoradaptor.cpp at line 161 database type is 'MSSQL'
    File ../../dataadaptor.cpp at line 462 fetchQuery() query=select * from dev1.dbo.persons rowcount=-1
    File ../../rco_cursoradaptor.cpp at line 1632 cmdbuf=PId int,Lastname char(20),Firstname char(20),Contact char(10)
    File ../../rco_cursoradaptor.cpp at line 1978 row=1 col=0 value=100 type=int
    File ../../rco_cursoradaptor.cpp at line 1978 row=1 col=1 value=Smith type=QString
    File ../../rco_cursoradaptor.cpp at line 1978 row=1 col=2 value=John type=QString
    File ../../rco_cursoradaptor.cpp at line 1978 row=1 col=3 value=0123456789 type=QString
    File ../../rco_cursoradaptor.cpp at line 1978 row=2 col=0 value=200 type=int
    File ../../rco_cursoradaptor.cpp at line 1978 row=2 col=1 value=Brown type=QString
    File ../../rco_cursoradaptor.cpp at line 1978 row=2 col=2 value=Paul type=QString
    File ../../rco_cursoradaptor.cpp at line 1978 row=2 col=3 value=0112344556 type=QString
    File ../../rco_cursoradaptor.cpp at line 274 wka=0 command=CLOSE
    Class Cursoradaptor Object OBJECT(221700736,0) Method: name='CONSTRUCTOR'
    File ../../rco_cursoradaptor.cpp at line 586 datasource=Driver={ODBC Driver 11 for SQL Server};Server=10.231.93.64,1434;Database=dev1;Uid =sa;Pwd=sa username= password=
    File ../../rco_cursoradaptor.cpp at line 161 database type is 'MSSQL'
    File ../../dataadaptor.cpp at line 462 fetchQuery() query=select * from dev1.dbo.persons rowcount=-1
    File ../../rco_cursoradaptor.cpp at line 1632 cmdbuf=PId int,Lastname char(20),Firstname char(20),Contact char(10)
    File ../../rco_cursoradaptor.cpp at line 1978 row=1 col=0 value=100 type=int
    File ../../rco_cursoradaptor.cpp at line 1978 row=1 col=1 value=Smith type=QString
    File ../../rco_cursoradaptor.cpp at line 1978 row=1 col=2 value=John type=QString
    File ../../rco_cursoradaptor.cpp at line 1978 row=1 col=3 value=0123456789 type=QString
    File ../../rco_cursoradaptor.cpp at line 1978 row=2 col=0 value=200 type=int
    File ../../rco_cursoradaptor.cpp at line 1978 row=2 col=1 value=Brown type=QString
    File ../../rco_cursoradaptor.cpp at line 1978 row=2 col=2 value=Paul type=QString
    File ../../rco_cursoradaptor.cpp at line 1978 row=2 col=3 value=0112344556 type=QString
    File ../../rco_cursoradaptor.cpp at line 274 wka=0 command=ALLOWUPDATE
    File ../../rco_cursoradaptor.cpp at line 274 wka=0 command=ALLOWINSERT
    File ../../rco_cursoradaptor.cpp at line 274 wka=0 command=INSERT

    Regards

  6. #6
    Lianja Team yvonne.milne's Avatar
    Join Date
    Feb 2012
    Location
    Berkshire, UK
    Posts
    1,379
    Hi,
    Can you Submit a Ticket for this and include full structure information for the remote table and we will investigate.

    Many thanks,
    Yvonne

  7. #7
    Senior Member
    Join Date
    Feb 2012
    Posts
    1,214
    Hi Phjr,

    I guess due to old habbits, I have been updating SQL Server using the non virtual table syntax, I just tried to append blank for a virtual table with a SQL Server backend and the application did close on me too. So I don't think its isolated to your structure.

    If you are pressed for time (Got a demo to give), just use the existing SQLExec commands. It works for me.

    lchandle =SQLSTRINGCONNECT("driver={SQL server};server=<YourServerName>;Trusted_Connection =Yes")
    lstring ="insert into persons values (400,'Ritchie','Dennis')"
    = SQLEXEC(lchandle,lstring )

    Herb

    ps - Of course you would need to build the string dynamically to handle the single quotes sql uses, but that's not really difficult.

  8. #8
    Member
    Join Date
    Apr 2013
    Posts
    88
    Thanks, went fine this way. Does it mean I'll have to use custom sections with the Builder?
    Regards

  9. #9
    Lianja Development Team barrymavin's Avatar
    Join Date
    Feb 2012
    Location
    UK, USA, Thailand
    Posts
    5,771
    No it will be fixed in the next build as you have already submitted 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

  10. #10
    Lianja Development Team barrymavin's Avatar
    Join Date
    Feb 2012
    Location
    UK, USA, Thailand
    Posts
    5,771
    It has now been fixed and will be available in RC5.7
    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

Page 1 of 2 12 LastLast

Tags for this Thread

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