Difference between revisions of "Virtual Tables - Further Configuration and Troubleshooting"
Yvonne.milne (Talk | contribs) |
Barrymavin (Talk | contribs) (→See Also) |
||
| Line 14: | Line 14: | ||
* [[SET CONNSTR]] Command (default 'ODBC Connection') | * [[SET CONNSTR]] Command (default 'ODBC Connection') | ||
| + | |||
| + | * [[Using_Parameters_Views|Using Parameterized Views]] | ||
==Virtual table marked as (encrypted) or (Access Denied) / File does not exist== | ==Virtual table marked as (encrypted) or (Access Denied) / File does not exist== | ||
Revision as of 22:40, 9 February 2020
Contents
See Also
- Syntax for the CREATE VIRTUALTABLE and ALTER VIRTUALTABLE Commands
- Properties and Methods of the CursorAdapter Class
- SET CONNSTR Command (default 'ODBC Connection')
Virtual table marked as (encrypted) or (Access Denied) / 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.
Note: From Lianja v2.1.0, if there is a problem accessing the remote data, '(Access Denied)' will be displayed next to the name of the virtual table and an error information message will pop up if you attempt to open the virtual table.
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 command 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.
Exclude from updates
If your SQL select statement has columns that you want to include in your virtual table, but that should not be updated, you can use the updatefieldlist property to exclude them from updates.
In the example here, the updatefieldlist is set to include all fields (*) apart from the last_update field (-last_update).
Exclude from WHERE clause
Similarly, if your SQL select statement has columns that you want to include in your virtual table, but that should not be used in the WHERE clause for UPDATE and DELETE operations, you can use the keyfieldlist property to exclude them.
In the example here, the keyfieldlist is set to include all fields (*) apart from the last_update field (-last_update).
Float Precision
The precision property can be used to specify the precision for float fields.
In the example here, the precision is set to 4 decimal places. The default is 2.
The command set precision to <expN> can also be used.
(From Lianja v2.0.1.)
Further troubleshooting
Debugging can be enabled from the Lianja/VFP command window in the Console Workspace with the command:
set debug on
If you are experiencing problems with a virtual table, start a new session of the Lianja App Builder and set debug on before any other operation. On exiting the Lianja App Builder, you can view the files in the debug sub-directory of your Lianja installation.
For assistance from the Lianja Support Team, please sign in to the Lianja web site and Submit a Ticket (Tickets menu), attaching the debug files and giving full details of your problem, platform and the operations involved.
