Virtual Tables - Further Configuration and Troubleshooting
Contents
- 1 See Also
- 2 MS SQL Server - Web Client - Server Error: The server failed to respond
- 3 MS SQL Server - Web Client - Slow Performance
- 4 Virtual table marked as (encrypted) or (Access Denied) / File does not exist
- 5 Underscores in table names
- 6 Exclude from updates
- 7 Exclude from WHERE clause
- 8 Float Precision
- 9 Further troubleshooting
See Also
- Syntax for the CREATE VIRTUALTABLE and ALTER VIRTUALTABLE Commands
- Properties and Methods of the CursorAdapter Class
- SET CONNSTR Command (default 'ODBC Connection')
MS SQL Server - Web Client - Server Error: The server failed to respond
If using Integrated Security methods for authentication, note that the login to the Lianja Cloud Server service uses the process user account, not the local user account (as used by the Lianja App Builder and Lianja App Center). Check the SQL Server log file to determine the process user details for your environment and ensure that it has the required access permissions. Alternatively, use ID and password authentication in the connection string or DSN (uid=xxxx;pwd=xxxx).
MS SQL Server - Web Client - Slow Performance
When selecting the ODBC Driver, use the latest 'ODBC Driver XX for SQL Server' that you have installed on your system. Do not use the generic 'SQL Server' driver.
ODBC Driver 17 for SQL Server can be found here. Download the version for your architecture. The x64 installer includes both the 32 bit and 64 bit drivers - the 32 bit driver is used with Lianja.
Virtual table marked as (encrypted) or (Access Denied) / File does not exist
If there is a problem accessing the remote data, '(Access Denied)' will be displayed next to the name of the virtual table (Data or Apps Workspaces) and an error information message will pop up if you attempt to open the virtual table.
In early versions of Lianja the virtual table will be marked (encrypted) as in the image.
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
Set Debug On
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.
DB_ODATATRACE
For web App debugging, the DB_ODATATRACE environment variable (registry on Windows) can be set to true. This creates odata_traceXXX.txt files in the debug directory.
From Lianja v5.3.
Submit a Ticket
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.