Virtual Tables - Further Configuration and Troubleshooting

From Lianjapedia
Revision as of 04:06, 17 February 2020 by Barrymavin (Talk | contribs)

Jump to: navigation, search
Bm-noteicon.png
Pro Tip

One of the most common problems "The server failed to respond" when accessing a VT in a Web App is caused by not defining a PrimaryKey in the VT definition. This is required for Web Apps to be able to paginate records in the specified PrimaryKey order.


See Also

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

Marked as encrypted


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.



File 'vt' does not exist


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.


Test connection in ODBC Console


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.


Modify Virtual Table with uid/pwd


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 ...


Virtual Table now OK


... and the ora_employees virtual table can now be opened and the '(encrypted)' notification has gone.


Underscores in table names

Underscore in table name


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.


Modify Virtual Table so table name not translated


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 ...


Virtual Table now OK


... and the remote film_text table can now be updated.


Exclude from updates

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

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

Specify 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. The tracing includes performance metrics to assist you in tuning.

From Lianja v5.3.

debug Property

Specifying debug=1 in the properties of a virtual table enables debugging for that virtual table.

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 and trace files and giving full details of your problem, platform and the operations involved.