View source for Virtual Table Properties

==See Also==
* [[ALTER VIRTUALTABLE]]

* [[CREATE VIRTUALTABLE]]

* [[CursorAdapter]]

* [[Virtual Tables]]

* [[Virtual Tables - Further Configuration and Troubleshooting]]

* [[Virtual Tables - Local Development, Remote Deployment]]

* [[Virtual Tables - Local Stored Procedures]]

* [[SET CONNSTR]] Command (default 'ODBC Connection')

{| class="wikitable" width="100%"
!width="30%"|Property||width="70%"|Notes
|-
|valign=top|allowupdate||Allow updates: allowupdate=1
|-
|valign=top|autosync||Keep the local and remote cursor data in sync as changes are made multi-user: autosync=1
|-
|valign=top|basetable||Name of the base table to be used for insert, update and delete operations When combining data from multiple tables using a join you need to specify this. 
|-
|valign=top|concatwhere||To have the existing WHERE clause included in pagination: concatwhere=1
|-
|valign=top|database||
|-
|valign=top|datasourcehost||
|-
|valign=top|datasourceoptions||
|-
|valign=top|datasourcehost||
|-
|valign="top"|dbtype||The dbtype can be: lianja &#124; mssql &#124; mysql &#124; oracle &#124; postgres &#124; vfp.
This makes connections faster as Lianja does not need to use heuristics to determine the remote database type.<br>
There are various versions of mssql i.e. mssql2008, mssql2010, mssql2012, mssql2016. <br>
Specifying mssql handles > 2008 and causes the engine to use OFFSET .. FETCH rather than use the OVER ... ORDER BY clause.
|-
|valign=top|debug||Enable debugging: debug=1
|-
|valign="top"|fetchasneeded||Enable fetchasneeded: fetchasneeded=1
|-
|valign=top|fetchsize||Specify fetchsize: fetchsize=N
|-
|valign="top"|keepalive||Keep the ODBC connection alive when the request completes: keepalive=1
|-
|valign=top|keyfieldlist||Synonym of primarykey.  Used to uniquely identify the row.
|-
|valign=top|maxrecords||
|-
|valign=top|nodata||Open without loading data: nodata=1.  Note: this can also be set using object.setNamedParameter("nodata", "false").  The setNamedParameter() method is available on the [[Section]], [[CursorAdapter]] and [[Cursor]] [[:Category:Framework Classes|Framework Classes]] (from v4.1).
|-
|valign="top"|nosupportsoffset||Enable nosupportsoffset: nosupportsoffset=1
|-
|valign="top"|pagination||Enable pagination: pagination=1
|-
|valign=top|password||Connection password
|-
|valign=top|postfixwhere||Enable postfixwhere: postfixwhere=1.  The WHERE clause will be applied to the resultset.
|-
|valign="top"|precision||Specify precision for float fields (default is 2): precision=N 
|-
|valign=top|quotestring||The quotestring property is used to handle CRUD statements containing embedded ' characters.<br>This should be set according to the requirements of the backend SQL engine.<br>e.g. to replace single quotes by two single quotes for MSSQL:
<pre>create virtualtable vtmssql_table properties "quotestring='';..." </pre>
or to replace single quotes by \' for MSSQL:
<pre>create virtual table vtmysql_table properties "quotestring=\';..." </pre>
|-
|valign="top"|rowid||Enable rowid: rowid=1
|-
|valign=top|supportslimit||Specify that the backend database engine supports the LIMIT keyword: supportslimit=1
|-
|valign=top|supportsoffset||Specify that the backend database engine supports the OFFSET keyword: supportsoffset=1
|-
|valign=top|table||
|-
|valign="top"|translatename||Disable translation of remote table name (underscore to dot): translatename=false
|-
|valign=top|ttl||Specify TTL: ttl=N. Used with keepalive=1, connections are released after the TTL has expired.  Default is 30 which causes them to be released after 30 minutes.
|-
|valign=top|updatenamelist||
|-
|valign="top"|updatefieldlist||Comma-separated list of fields that can be inserted or updated. Prefix a field with a - to exclude it and use * to select all remaining fields. e.g. -custid,-custballance,*
|-
|valign=top|username||Connection username
|-
|valign=top|wheretype||
|-
|}

A property that is unrecognized, e.g. mypar=myvalue, will be added as a "named parameter" and will be the default value to be substituted if no setNamedParameter("mypar", "myvalue") is specified.  The setNamedParameter() method is available on the [[Section]], [[CursorAdapter]] and [[Cursor]] [[:Category:Framework Classes|Framework Classes]].  From v4.1.

[[Category:Documentation]]
[[Category:SQL]]
[[Category:Databases]]
[[Category:Lianja VFP Extensions]]
[[Category:VFP Command Extensions]]
[[Category:Third Party Database Connectivity]]
[[Category:Virtual Tables]]