Virtual table & insert/delete/update

[Phjr]

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

[barrymavin]

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.

[Phjr]

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:

[barrymavin]

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.

[Phjr]

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

[yvonne.milne]

Hi,
Can you Submit a Ticket for this and include full structure information for the remote table and we will investigate.

Many thanks,
Yvonne

[hmischel@diligentsystems.com]

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.

[Phjr]

Thanks, went fine this way. Does it mean I'll have to use custom sections with the Builder?
Regards

[barrymavin]

No it will be fixed in the next build as you have already submitted a ticket.

[barrymavin]

It has now been fixed and will be available in RC5.7