Generic ODBC/JDBC Connection

Connecting via ODBC Driver

The CData Virtuality Suite contains the CData Virtuality ODBC driver, enabling data consumption tools to connect to the CData Virtuality Server using the ODBC protocol. Before using such tools, the CData Virtuality ODBC driver must be installed, and the appropriate DSN must be created.

You can find the installation instruction for the CData Virtuality ODBC driver in the Administration Guide/Installation (Linux/Windows/macOS).

ODBC Parameters

Advanced Options 1/2

Parameter	Description
Recognize Unique Indexes	Check this option
Use Declare/Fetch	If `TRUE`, the driver automatically uses declare cursor/fetch to handle `SELECT` statements and keeps 100 rows in a cache. This is mostly a great advantage, especially if you are only interested in reading and not updating. It results in the driver not sucking down lots of memory to buffer the entire result set. If set to false, cursors will not be used and the driver will retrieve the entire result set. For very large tables, this is very inefficient and may use up all the Windows memory/resources. However, it may handle updates better since the tables are not kept open, as they are when using cursors. This was the style of the old podbc32 driver. However, the behavior of the memory allocation is much improved so even when not using cursors, performance should at least be better than the old podbc32
Parse Statements	Tell the driver how to gather the information about result columns of queries, if the application requests that information before executing the query. See also Server side prepare options below. The driver checks this option first. If disabled then it checks the Server side prepare option. If this option is enabled, the driver will parse an SQL query statement to identify the columns and tables and gather statistics about them such as precision, nullability, aliases, etc. It then reports this information in SQLDescribeCol, SQLColAttributes, and SQLNumResultCols. When this option is disabled (the default), the query is sent to the server to be parsed and described. If the parser can not deal with a column (because it is a function or expression, etc.), it will fall back to describing the statement in the server. The parser is fairly sophisticated and can handle many things such as column and table aliases, quoted identifiers, literals, joins, cross-products, etc. It can correctly identify a function or expression column, regardless of the complexity, but it does not attempt to determine the data type or precision of these columns
Ignore Timeout	Ignore `SQL_ATTR_QUERY_TIMEOUT` set using `SQLSetStmtAttr()`. Some tools issue `SQLSetStmtAttr(.., SQL_ATTR_QUERY_TIMEOUT, ...)` internally and sometimes it is difficult for users to change the value
Unknown Sizes	This controls what SQLDescribeCol and SQLColAttributes will return as to precision for character data types (varchar, text, and unknown) in a result set when the precision is unknown. This was more of a workaround for pre-6.4 versions of PostgreSQL not being able to return the defined column width of the varchar data type. Maximum: Always return the maximum precision of the data type; Don't Know: Return the "Don't Know" value and let application decide; Longest: Return the longest string length of the column of any row. Beware of this setting when using cursors because the cache size may not be a good representation of the longest column in the cache.
Data Type Options	Affects how some data types are mapped: Text as LongVarChar: PostgreSQL TEXT type is mapped to SQLLongVarchar, otherwise SQLVarchar; Unknowns as LongVarChar: Unknown types (arrays, etc) are mapped to SQLLongVarChar, otherwise SQLVarchar; Bools as Char: Bools are mapped to SQL_CHAR, otherwise to SQL_BIT
Max Varchar:	The maximum precision of the Varchar and BPChar(char[x]) types. The default is 254 which actually means 255 because of the null terminator. Note, if you set this value higher than 254, Access will not let you index on varchar columns!
Cache Size:	When using cursors, this is the row size of the tuple cache and the default is 100 rows. If not using cursors, this has no meaning
Batch Size:	Chunk size when executing batches with arrays of parameters. Setting 1 to this option forces one by one execution (the behavior before)
MaxLongVarChar:	The maximum precision of the LongVarChar type. The default is 4094 which actually means 4095 with the null terminator. You can even specify (-4) for this size, which is the odbc SQL_NO_TOTAL value
Use transaction on Declare/Fetch

Advanced Options 2/2

Parameter	Description
Read Only	Whether the data source will allow updates
Show System tables	The driver will treat system tables as regular tables in SQLTables. This is good for Access so you can see system tables
LF <-> CR/LF conversion	Convert Unix style line endings to DOS style
Updatable Cursors	Enable the updatable cursor emulation in the driver
bytea as LO	Allow the use of bytea columns for Large Objects
Row Versioning	Allows applications to detect whether data has been modified by other users while you are attempting to update a row. It also speeds the update process since every single column does not need to be specified in the where clause to update a row. The driver uses the "xmin" system field of PostgreSQL to allow for row versioning. Microsoft products seem to use this option well. See the <a "https:="" odbc.postgresql.org="" faq.html"="" style="text-decoration: ; text-align: left;">faq for details on what you need to do to your database to allow for the row versioning feature to be used
Display Optional Error M	Display optional(detail, hint, statement position etc) error messages
True is -1	Represent `TRUE` as -1 for compatibility with some applications
Server side prepare	If set, the driver uses server-side prepared statements. See also Parse Statement option. Note that if a query needs to be described before execution, e.g. because the application calls SQLDescribeCol() or SQLNumResultCols() before SQLExecute(), the driver will send a Parse request to the server even if this option is disabled. In that case, the query that is sent to the server for parsing will have the parameter markers replaced with the actual parameter values, or NULL literals if the values are not known yet
Int8 As	Define what datatype to report int8 columns as
Numeric(without precision) As	Specify the map from numeric items without precision to SQL data types. numeric(default), varchar, double or memo(SQL_LONGVARCHAR) can be specified
Extra Opts	Combination of the following bits: 0x1: Force the output of short-length formatted connection string. Check this bit when you use MFC CDatabase class; 0x2: Fake MS SQL Server so that MS Access recognizes PostgreSQL's serial type as AutoNumber type; 0x4: Reply ANSI (not Unicode) char types for the inquiries from applications. Try to check this bit when your applications don't seem to be good at handling Unicode data
Level of rollback on errors	Specifies what to rollback should an error occur: Nop(0): Don't rollback anything and let the application handle the error; Transaction(1): Rollback the entire transaction; Statement(2): Rollback the statement.
OID Options	Show Column: Includes the OID in SQLColumns. This is good for using as a unique identifier to update records if no good key exists OR if the key has many parts, which blows up the backend; Fake Index: This option fakes a unique index on OID. This is useful when there is not a real unique index on OID and for apps which can't ask what the unique identifier should be (i.e, Access 2.0)
Connect Settings:	The driver sends these commands to the backend upon a successful connection. It sends these settings AFTER it sends the driver "Connect Settings". Use a semi-colon (;) to separate commands. This can now handle any query, even if it returns results. The results will be thrown away however!
TCP KEEPALIVE setting (by sec)	Specifies the TCP keepalive settings: disable: Check when client-side TCP keepalives are not used; idle time: The number of seconds of inactivity after which TCP should send a keepalive message to the server; interval: The number of seconds after which a TCP keepalive message that is not acknowledged by the server should be retransmitted

Advanced Options 3/X

Parameter

Description

Distributed Transaction related settings:
Allow connections unrecoverable by MSDTC?

How to test distributed transactions:

yes: MSDTC is needless unless applications crash. So don't check the connectivity from MSDTC;
rejects sslmode verify-[ca|full]: reject ssl connections with verify-ca or verify-full mode because in those cases msdtc could hardly establish the connection;
no: First confirm the connectivity from MSDTC

libpq parameters:(I)

Specify libpq connection parameters with conninfo style strings e.g. sslrootcert=c:\\myfolder\\myroot sslcert=C:\\myfolder\\mycert sslkey=C:\\myfolder\\mykey.
Though host, port, dbname, user, password, sslmode, keepalives_idle or keepalive_interval parameters can be set using this(pqopt) option, the use is not recommended because they are ordinarily set by other options. When some settings for those parameters conflict with other ordinary options, connections are rejected

Connecting via JDBC Driver

For data consumption tools that support connecting via a JDBC driver, we have one. If you have the CData Virtuality Suite installed, the file is found in ..\CData Virtuality Suite\Drivers, and if not, it can be downloaded from the server. For more information on the driver and its installation, please see the dedicated subpage in this section.