Virtual Tables - Further Configuration and Troubleshooting
Under Construction
Troubleshooting
Virtual Table marked as (encrypted) / File does not exist
If your virtual table has '(encrypted)' next to its name in the Databases sidebar (Data or Apps Workspaces), this indicates that there is a problem accessing the remote data.
If you switch to the Console Workspace, you may also see the error File 'name-of-your-virtual-table' does not exist reported, during the virtual table creation, or when you try to USE the virtual table.
If you created your virtual table manually - from the New virtual table dialog or using the CREATE VIRTUALTABLE command, rather than using Import ODBC database or Create Virtual Tables from ODBC from the Database Additional commands - it is useful to issue a DIR in the Lianja/VFP command window (with your database open) to check the details of the virtual table. If you have any errors in your connstr or select statement, these can be corrected in the Modify virtual table dialog in the Table Additional commands.
The next thing to check is whether you can access your remote data from the ODBC Console. Close your Lianja database - from the Data Workspace or using the CLOSE DATABASE command in the Lianja/VFP command window - then select the ODBC command window. Enter the Connstring details, then click the green tick to connect.
If you are unable to connect, check that the remote data server is running, that there are no network issues and that your user and password are valid and correctly entered. Tracing can be enabled in the Windows ODBC Data Source Administrator (32 bit) or Linux unixODBC odbc.ini file to help with determining connection problems if required.
Once connected, check that you can run the select statement used by your virtual table. Errors returned by the ODBC Driver will be reported.
For example, the Visual FoxPro Driver will return the following error if your table includes 'Integer (AutoInc)' type fields: ** Error 123 ** [Microsoft][ODBC Visual FoxPro Driver]Not a table.
If you are using a DSN and your user and password information is required, but is not stored in the DSN, this information needs to be included in the virtual table connstr. As in the screenshots above, the ora_employees virtual table could not be opened, but the select * from employees SQL statement was valid in the ODBC Console when the user and password details were entered.
The virtual table connstr can be changed in the Modify virtual table dialog ...
... and the ora_employees virtual table can now be opened and the '(encrypted)' notification has gone.
Underscores in Table Names
Lianja translates dots to an underscore when creating virtual tables remote tables with a schema.tablename notation. In turn, underscores are translated back to dots when remote tables are updated. This can cause issues if the remote table basename contains underscores, as in the example shown here.
To prevent this, set the basetable property to the name of the remote table and set the translatename property to false. Properties are semi-colon (;) separated and can be set in the Modify virtual table dialog ...
... and the remote film_text table can now be updated.
