Difference between revisions of "Virtual Table Properties"

From Lianjapedia
Jump to: navigation, search
(14 intermediate revisions by 2 users not shown)
Line 1: Line 1:
''Under Construction''
 
 
 
==See Also==
 
==See Also==
 
* [[ALTER VIRTUALTABLE]]
 
* [[ALTER VIRTUALTABLE]]
Line 13: Line 11:
  
 
* [[Virtual Tables - Local Development, Remote Deployment]]
 
* [[Virtual Tables - Local Development, Remote Deployment]]
 +
 +
* [[Virtual Tables - Local Stored Procedures]]
 +
 +
* [[SET CONNSTR]] Command (default 'ODBC Connection')
  
 
{| class="wikitable" width="100%"
 
{| class="wikitable" width="100%"
Line 21: Line 23:
 
|valign=top|autosync||Keep the local and remote cursor data in sync as changes are made multi-user: autosync=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 remote table.
+
|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 and the updatefieldlist described below.  
 
|-
 
|-
 
|valign=top|concatwhere||To have the existing WHERE clause included in pagination: concatwhere=1
 
|valign=top|concatwhere||To have the existing WHERE clause included in pagination: concatwhere=1
Line 34: Line 36:
 
|-
 
|-
 
|valign="top"|dbtype||The dbtype can be: lianja | mssql | mysql | oracle | postgres | vfp.
 
|valign="top"|dbtype||The dbtype can be: lianja | mssql | mysql | oracle | postgres | vfp.
This makes connections faster as Lianja does not need to use heuristics to determine the remote database type.
+
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|debug||Enable debugging: debug=1
Line 48: Line 52:
 
|valign=top|maxrecords||
 
|valign=top|maxrecords||
 
|-
 
|-
|valign=top|nodata||Open without loading data: nodata=1
+
|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"|nosupportsoffset||Enable nosupportsoffset: nosupportsoffset=1
Line 79: Line 83:
 
|valign=top|updatenamelist||
 
|valign=top|updatenamelist||
 
|-
 
|-
|valign="top"|updatefieldlist||Comma-separated list of fields that can be updated.
+
|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|username||Connection username
Line 86: Line 90:
 
|-
 
|-
 
|}
 
|}
 +
 +
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:Documentation]]
 
[[Category:SQL]]
 
[[Category:SQL]]
[[Category:Commands]]
 
 
[[Category:Databases]]
 
[[Category:Databases]]
 
[[Category:Lianja VFP Extensions]]
 
[[Category:Lianja VFP Extensions]]

Revision as of 04:05, 26 August 2019

See Also

Property Notes
allowupdate Allow updates: allowupdate=1
autosync Keep the local and remote cursor data in sync as changes are made multi-user: autosync=1
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 and the updatefieldlist described below.
concatwhere To have the existing WHERE clause included in pagination: concatwhere=1
database
datasourcehost
datasourceoptions
datasourcehost
dbtype The dbtype can be: lianja | mssql | mysql | oracle | postgres | vfp.

This makes connections faster as Lianja does not need to use heuristics to determine the remote database type.
There are various versions of mssql i.e. mssql2008, mssql2010, mssql2012, mssql2016.
Specifying mssql handles > 2008 and causes the engine to use OFFSET .. FETCH rather than use the OVER ... ORDER BY clause.

debug Enable debugging: debug=1
fetchasneeded Enable fetchasneeded: fetchasneeded=1
fetchsize Specify fetchsize: fetchsize=N
keepalive Keep the ODBC connection alive when the request completes: keepalive=1
keyfieldlist Synonym of primarykey. Used to uniquely identify the row.
maxrecords
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 Framework Classes (from v4.1).
nosupportsoffset Enable nosupportsoffset: nosupportsoffset=1
pagination Enable pagination: pagination=1
password Connection password
postfixwhere Enable postfixwhere: postfixwhere=1. The WHERE clause will be applied to the resultset.
precision Specify precision for float fields (default is 2): precision=N
quotestring The quotestring property is used to handle CRUD statements containing embedded ' characters.
This should be set according to the requirements of the backend SQL engine.
e.g. to replace single quotes by two single quotes for MSSQL:
create virtualtable vtmssql_table properties "quotestring='';..." 

or to replace single quotes by \' for MSSQL:

create virtual table vtmysql_table properties "quotestring=\';..." 
rowid Enable rowid: rowid=1
supportslimit Specify that the backend database engine supports the LIMIT keyword: supportslimit=1
supportsoffset Specify that the backend database engine supports the OFFSET keyword: supportsoffset=1
table
translatename Disable translation of remote table name (underscore to dot): translatename=false
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.
updatenamelist
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,*
username Connection username
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 Framework Classes. From v4.1.