Page 1 of 1

Xbase/VFP - Problems with Memo fields , Shared Files

Posted: Wed 09 Oct 2019 14:49
by halsdk
2 issues with most recent Driver Release and need help trying to resolve and not getting adequate help from support. Has Anyone else run into the same issues and if so, how was it resolved

I am using SQL server SSMS and have created an ODBC connection called "Test" and then created a 'Linked Server' using this connection and querying using Openquery()

1- Unable to included Memo Fields.
I created a simple test table with a couple of fields include 1 memo field. In my query I am simply doing a 'Select *' and get the following error. There are only 10 row

If any of the memo fields are empty/blank I get the error- which cannot be right as it possible to have memo fields without any data.

Msg 7342, Level 16, State 1, Line 10
An unexpected NULL value was returned for column "[MSDASQL].MEMO1" from OLE DB provider "MSDASQL" for linked server "TEST". This column cannot be NULL.

I make sure that each memo field had data and still get an error.

OLE DB provider "MSDASQL" for linked server "TEST" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
Msg 7341, Level 16, State 2, Line 10
Cannot get the current row value of column "[MSDASQL].MEMO1" from OLE DB provider "MSDASQL" for linked server "TEST". The provider cannot allocate memory or open another storage object on this column.

-- So Unable to include ANY memo field in a query - with or without data.

2- The other issue I am having is that I am unable to query ANY table in the Path that is setup in the connection if anyone else as ANY table in the same path open- Whether involved in the openquery or not. So basically I can only use this when absolutely no one else using any of the files

I tried to query a table called 'TestTable' A very simple query and with VFP opened a table called 'Country'

select * from openquery(TEST,'Select * from [Testtable]')

OLE DB provider "MSDASQL" for linked server "TEST" returned message "[Devart][ODBC][xBase]Cannot open file "j:\rna\Country.DBF". The process cannot access the file because it is being used by another process".
Msg 7350, Level 16, State 2, Line 10

I was told by support that I need to change the connection mode to 'Unsafe' but still get the same results.

If anyone can provide assistance it would be greatly appreciated.

Re: Xbase/VFP - Problems with Memo fields , Shared Files

Posted: Fri 18 Oct 2019 13:02
by Stellar
1. Thank you for the information. We know about the issue wirh reading DBF tables that contain Memo fields in SSMS. We're working to fix it.

2. To maintain data integrity, we added functionality that doesn't allow external applications to write to the file when our driver is working with this file. When data from a table is being read, an external application can interfere with this process by writing data to the file. This can lead either to errors or to data being retrieved incorrectly. We will add a new value for the SharedMode option which will enable the old type of behavior when opening the DBF table. This is less safe, but will allow you to use our driver like before.
The SharedMode property allows or prevents other processes from accessing DBF tables after our driver has established a successful connection. If a file is already in use by a third-party tool, for example Microsoft Visual FoxPro, while other processes are not allowed to access the file, then our driver won't be able to connect to the file.

Re: Xbase/VFP - Problems with Memo fields , Shared Files

Posted: Fri 18 Oct 2019 14:24
by halsdk
Thank you for your response.

1 ] The issue with the memo fields is critical at the moment as this was one of the main reasons for purchasing. Is there a Timeline of when this is expected to be ready? Is there any type of work-around that is possible to get this work in the meantime?

2 ] The problem is that regardless of what every settings I change, I cannot get the an Openquery to work fi there is any with any other table open in folder- not just the one being queried, but any other table which does not seem right. There is a support ticket [Devart #292074 ] that I have been working with someone in support and have even provided video of this not working. I should be able to open a table in Visual Foxpro in 'Shared' mode and still be able to do an openquery form SSMS- If not, then any thing I do with the Devart driver would have to be in a Single User environment which is not an option in any read-world application.

Re: Xbase/VFP - Problems with Memo fields , Shared Files

Posted: Mon 16 Mar 2020 12:53
by Stellar
DBF is a database file, therefore we restrict modifications to DBF files by other applications in the cmShared and cmExclusive connection modes.
The "Unsafe" mode was added for situations where it's necessary to read files used by other application.
The "Unsafe" mode should be used with caution because it allows multiple applications to simultaneously modify to the table.

In the Unsafe mode we get only the list of files in a catalogue when connecting to the database, while the table metadata is loaded by our driver when a file is accessed for the first time. This means that the driver accesses only a subset of tables used by the application.