Difference between revisions of "Virtual Table Properties"
From Lianjapedia
Yvonne.milne (Talk | contribs) |
Yvonne.milne (Talk | contribs) (→See Also) |
||
| (24 intermediate revisions by 2 users not shown) | |||
| Line 1: | Line 1: | ||
| − | |||
| − | |||
==See Also== | ==See Also== | ||
* [[ALTER VIRTUALTABLE]] | * [[ALTER VIRTUALTABLE]] | ||
| Line 15: | Line 13: | ||
* [[Virtual Tables - Local Stored Procedures]] | * [[Virtual Tables - Local Stored Procedures]] | ||
| + | |||
| + | * [[SET CONNSTR]] Command (default 'ODBC Connection') | ||
{| class="wikitable" width="100%" | {| class="wikitable" width="100%" | ||
| Line 23: | 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 | + | |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|codec||To handle character mapping between utf-8 and locale specific encoding for use with the VFP OLEDB driver, e.g. codec=windows-1252. See [[SET CODEPAGE|here for supported codepage values]]. From v7.1. | ||
|- | |- | ||
|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 | ||
| + | |- | ||
| + | |valign=top|convertuint||Set to 1 to correct the ODBC interpretation of BIT fields in MySQL: convertuint=1. From v6.0. | ||
|- | |- | ||
|valign=top|database|| | |valign=top|database|| | ||
| Line 31: | Line 35: | ||
|valign=top|datasourcehost|| | |valign=top|datasourcehost|| | ||
|- | |- | ||
| − | |valign=top|datasourceoptions|| | + | |valign=top|datasourceoptions||Specify database specific options, e.g.: datasourceoptions=SQL_ATTR_PACKET_SIZE:102410. From v6.0. |
|- | |- | ||
|valign=top|datasourcehost|| | |valign=top|datasourcehost|| | ||
|- | |- | ||
|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 | ||
| + | |- | ||
| + | |valign=top|descriptions||Specify field descriptions/captions. From v9.2. e.g.<br>descriptions=actor_id:Actor ID,first_name:First Name | ||
| + | |- | ||
| + | |valign=top|elapsedtime||If enabled, the elapsed times for various VT operations are written to the debug_client.txt file. From v6.3. | ||
| + | Enable elapsedtime: elapsedtime=1 | ||
|- | |- | ||
|valign="top"|fetchasneeded||Enable fetchasneeded: fetchasneeded=1 | |valign="top"|fetchasneeded||Enable fetchasneeded: fetchasneeded=1 | ||
|- | |- | ||
|valign=top|fetchsize||Specify fetchsize: fetchsize=N | |valign=top|fetchsize||Specify fetchsize: fetchsize=N | ||
| + | |- | ||
| + | |valign=top|includeblobs||Use this to cause varbinary fields to be saved to remote database servers in desktop Apps. Currently supports MySQL, MSSQL and Postgres. includeblobs=1. From v6.0. | ||
| + | |- | ||
| + | |valign=top|includememos||Use this to cause varchar fields to be saved to remote database servers in desktop Apps. Currently supports MySQL, MSSQL and Postgres. includememos=1. From v6.0. | ||
|- | |- | ||
|valign="top"|keepalive||Keep the ODBC connection alive when the request completes: keepalive=1 | |valign="top"|keepalive||Keep the ODBC connection alive when the request completes: keepalive=1 | ||
| Line 50: | Line 65: | ||
|valign=top|maxrecords|| | |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]] | + | |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]]. |
|- | |- | ||
|valign="top"|nosupportsoffset||Enable nosupportsoffset: nosupportsoffset=1 | |valign="top"|nosupportsoffset||Enable nosupportsoffset: nosupportsoffset=1 | ||
| + | |- | ||
| + | |valign="top"|nulls||When fetching data for a VT, NULLS are now respected when nulls=1. Specify the VT properties nulls=1 and nullifempty=1 to handle NULL values in MSSQL, MySQL and other third party databases (from v9.4). Requires [[SET NULL|SET NULL ON]]. From v9.4.8, [[SET NULL]] is automatically set ON for the VT when nulls=1. | ||
| + | |- | ||
| + | |valign="top"|nullifempty||When updating data, any blank field values are treated as NULL. Specify the VT properties nulls=1 and nullifempty=1 to handle NULL values in MSSQL, MySQL and other third party databases (from v9.4). Requires [[SET NULL|SET NULL ON]]. From v9.4.8, [[SET NULL]] is automatically set ON for the VT when nulls=1. | ||
|- | |- | ||
|valign="top"|pagination||Enable pagination: pagination=1 | |valign="top"|pagination||Enable pagination: pagination=1 | ||
| Line 74: | Line 93: | ||
|- | |- | ||
|valign=top|table|| | |valign=top|table|| | ||
| + | |- | ||
| + | |valign="top"|translatecolumnname||If this is true, column names with spaces are translated to use [] square bracket enclosures: translatecolumnname=true.<br>Note that when reading a MS SQL Server table that has spaces in the column names, these spaces are replaced with underscores so that they can be referenced in LianjaScript. Columns with underscores in their names should be excluded using the translatecolumnnames property (as below). | ||
| + | |- | ||
| + | |valign="top"|translatecolumnnames||Specify the columns that should/should not be translated when the translatecolumnname property is true, e.g.<br>translatecolumnnames=field,field<br>or<br> | ||
| + | translatecolumnnames=*,-field_name,-field_name | ||
|- | |- | ||
|valign="top"|translatename||Disable translation of remote table name (underscore to dot): translatename=false | |valign="top"|translatename||Disable translation of remote table name (underscore to dot): translatename=false | ||
| + | |- | ||
| + | |valign="top"|translatequeryfunc||Specify a function to be called to translate a native Lianja SQL WHERE condition for a target SQL database syntax. The function is called with two arguments: cWhereCondition, cDBtype. It should reside in the database container, e.g. : translatequeryfunc=yourFunc. From v6.0. | ||
|- | |- | ||
|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|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. | ||
| Line 81: | Line 107: | ||
|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 | ||
|- | |- | ||
| − | |valign=top|wheretype|| | + | |valign=top|wheretranslate||Set this to 0 for queries to MS SQL Server with an OVER (PARTITION BY ...) clause and a WHERE clause, if the WHERE clause is wrongly translated/repositioned. From v8.0. |
| + | |- | ||
| + | |valign=top|wheretype||Type of fields used for where clauses: 1 = KeyFieldList, 2 = KeyFieldList and updatable fields, 3 = KeyFieldList and modified fields, 4 = KeyFieldList and comparison of time stamps. | ||
|- | |- | ||
|} | |} | ||
| − | 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]] | + | 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]]. |
[[Category:Documentation]] | [[Category:Documentation]] | ||
Latest revision as of 07:07, 22 November 2023
See Also
- SET CONNSTR Command (default 'ODBC Connection')
| 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. |
| codec | To handle character mapping between utf-8 and locale specific encoding for use with the VFP OLEDB driver, e.g. codec=windows-1252. See here for supported codepage values. From v7.1. |
| concatwhere | To have the existing WHERE clause included in pagination: concatwhere=1 |
| convertuint | Set to 1 to correct the ODBC interpretation of BIT fields in MySQL: convertuint=1. From v6.0. |
| database | |
| datasourcehost | |
| datasourceoptions | Specify database specific options, e.g.: datasourceoptions=SQL_ATTR_PACKET_SIZE:102410. From v6.0. |
| 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. |
| debug | Enable debugging: debug=1 |
| descriptions | Specify field descriptions/captions. From v9.2. e.g. descriptions=actor_id:Actor ID,first_name:First Name |
| elapsedtime | If enabled, the elapsed times for various VT operations are written to the debug_client.txt file. From v6.3.
Enable elapsedtime: elapsedtime=1 |
| fetchasneeded | Enable fetchasneeded: fetchasneeded=1 |
| fetchsize | Specify fetchsize: fetchsize=N |
| includeblobs | Use this to cause varbinary fields to be saved to remote database servers in desktop Apps. Currently supports MySQL, MSSQL and Postgres. includeblobs=1. From v6.0. |
| includememos | Use this to cause varchar fields to be saved to remote database servers in desktop Apps. Currently supports MySQL, MSSQL and Postgres. includememos=1. From v6.0. |
| 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. |
| nosupportsoffset | Enable nosupportsoffset: nosupportsoffset=1 |
| nulls | When fetching data for a VT, NULLS are now respected when nulls=1. Specify the VT properties nulls=1 and nullifempty=1 to handle NULL values in MSSQL, MySQL and other third party databases (from v9.4). Requires SET NULL ON. From v9.4.8, SET NULL is automatically set ON for the VT when nulls=1. |
| nullifempty | When updating data, any blank field values are treated as NULL. Specify the VT properties nulls=1 and nullifempty=1 to handle NULL values in MSSQL, MySQL and other third party databases (from v9.4). Requires SET NULL ON. From v9.4.8, SET NULL is automatically set ON for the VT when nulls=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 | |
| translatecolumnname | If this is true, column names with spaces are translated to use [] square bracket enclosures: translatecolumnname=true. Note that when reading a MS SQL Server table that has spaces in the column names, these spaces are replaced with underscores so that they can be referenced in LianjaScript. Columns with underscores in their names should be excluded using the translatecolumnnames property (as below). |
| translatecolumnnames | Specify the columns that should/should not be translated when the translatecolumnname property is true, e.g. translatecolumnnames=field,field or translatecolumnnames=*,-field_name,-field_name |
| translatename | Disable translation of remote table name (underscore to dot): translatename=false |
| translatequeryfunc | Specify a function to be called to translate a native Lianja SQL WHERE condition for a target SQL database syntax. The function is called with two arguments: cWhereCondition, cDBtype. It should reside in the database container, e.g. : translatequeryfunc=yourFunc. From v6.0. |
| 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 |
| wheretranslate | Set this to 0 for queries to MS SQL Server with an OVER (PARTITION BY ...) clause and a WHERE clause, if the WHERE clause is wrongly translated/repositioned. From v8.0. |
| wheretype | Type of fields used for where clauses: 1 = KeyFieldList, 2 = KeyFieldList and updatable fields, 3 = KeyFieldList and modified fields, 4 = KeyFieldList and comparison of time stamps. |
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.
