Chapter 17. HyperSQL via ODBC

Overview

Support for ODBC access to HyperSQL servers was introduced in HSQLDB version 2.0. Improvements were made to the server code for version 2.5.1 to allow an unmodified PostgreSQL ODBC driver (version 11) to be used. This chapter has been adapted from the original ODBC documentation and added to this Guide.

The current version supports a large subset of ODBC calls. It supports all SQL statements, including prepared statements and result set metadata, but it does not yet support database metadata, so some applications may not work.

Unix / Linux Installation

Install unixODBC and PostgreSQL psqlodbc RPM or package. See https://help.interfaceware.com/v6/connect-to-postgresql-from-linux-or-mac-with-odbc

See the Settings section about individual driver runtime settings.

The unixODBC graphical program "ODBCConfig" just does not work for any driver I have ever tried to add. If the same applies to you, you will need to edit the files

/etc/unixODBC/odbc.ini Driver definitions
/etc/unixODBC/odbcinst.ini Global DSN definitions
$HOME/.odbc.ini Personal DSN definitions

Depending on your UNIX or unixODBC distribution, your etc config files may be directly in /etc/ instead of in the unixODBC subdirectory.

Windows Installation

Download and install PostgreSQL ODBC software. We tested with version 11 of this software in Unicode mode, but other versions may also work. In Windows, go to ODBC Data Source Administrator (via Administrative Tools, Data Source (ODBC) or ODBC DataSource in different versions of Windows) and click on Add to add a PostgreSQL data source. You can then configure the data source.

See the Settings section about individual driver runtime settings.

These DSN definition screens are not identical to what you see, but the individual settings are the same. The Data Source field is the name of the ODBC data source. The database is the name of the HyperSQL database on the server. In this example, the default server database name is indicated with a slash. Use localhost as the Server name for the local machine. The User Name is a user name of the HyperSQL database, by default SA. You must set a non-empty password for the user, otherwise connection cannot be established.

The HyperSQL server must be started before testing the connection.

Then option screen 1 of 2.

... and 2 of 2.

Settings

This section applies to both UNIX and Windows. The setting heading includes the descriptive name as shown by the Windows ODBC DS Administrator, as well as the real keyword names that UNIX users will use.

The PostgreSQL ODBC Driver product consists of two driver variants. You should try to use the Unicode variant first, since it supports the later and better ODBC protocol. Use the ANSI variant if the Unicode variant won't work for your application. The way you select the driver variant for a DSN is platform-specific. For UNIX, set the DSN setting Driver to the key as defined in the uniXODBC config file /etc/unixODBC/odbcinst.ini. For UNIX, select the driver after you click Add on the User DSN screen, or switch it afterwards with the DSN's Manage DSN button.

Driver settings can also be made at connection time by just appending keyword abbreviation/value assignment pairs to the ODBC connection string, delimiting each such pair with a semicolon. Base connection Strings are language-dependent, but you always append a String in this form

;A0=0;B9=1

See the Table below for a concise list of the abbreviations you may use. The appendix also shows the default values for settings (this is really only necessary for UNIX users, since the Windows DSN manager always shows the current effective value).

Runtime Driver Settings

Database	ODBC does not allow an empty string for a DSN database name. Therefore, you must specify DSN database name of "/" (without the quotes) to indicate the default database
Recognize Unique Indexes
Cancel as FreeStmt	Find out what this experimental feature is for.
MyLog	Enables fairly verbose runtime logging to the indicated file. With value 1 will write coded mylog() messages to the MyLog file. With value 2 will write both mylog() and inolog() messages to MyLog file.
CommLog	Enables runtime communiction logging to the indicated file. With value 1, will write coded qlog() messages to the CommLog.
Unknown Sizes	This controls what SQLDescribeCol and SQLColAttributes will return as to precision for the variable data types when the precision (for example for a column) is unspecified. For the recommended `sql_enforce_strict_size mode`, this setting will have no effect. Maximum: Always return the maximum precision of the data type. Dont Know: Return "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. MS Access: Seems to handle Maximum setting OK, as well as all the others. Borland: If sizes are large and lots of columns, Borland may crash badly (it doesn't seem to handle memory allocation well) if using Maximum size.
Max Varchar	Use this setting only as a work-around for client app idiocy. Generally, the database should enforce your data constraints. The maximum precision of the VARCHAR and CHAR types (perhaps others). Set to 1 larger than the value you need, to allow for null terminator characters. The default is 255 right now. 0 really means max of 0, and we need to change this ASAP so that 0 will mean unlimited. 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. If not using cursors, this is how many tuples to allocate memory for at any given time. The default is 100 rows for either case.
Max LongVarChar	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.
ReadOnly	Whether the datasource 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 updateable cursor emulation in the driver. Fred will be implementing real Updatable ResultSets.
Row Versioning	Will turn on MVCC currency control mode, once we implement this.
True is -1	Represent TRUE as -1 for compatibility with some applications.
Int8 As	Define what datatype to report int8 columns as.
Extra Opts	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.
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).
OID Options	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. default value is a sentence unit (it is a transaction unit before 8.0).
Connection 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!

Samples

The HyperSQL Engine distribution contains these same ODBC client code examples in the sample subdirectory.

Table of Settings

See the above section for descriptions and usage details. This section just contains a list of the available settings.

Table 17.1. Settings List

Keyword	Abbrev.	Default Val.	Purpose
Description	N/A		Data source description
Servername	N/A	[required]	Name of Server
Port	N/A	9001	HyperSQL Server Listen Port
Username	N/A	[required]	User Name
Password	N/A	[required]	Password
Debug	B2	0	MyLog logging level
Fetch	A7	100	Fetch Max Count Test to see if this applies to EXECDIRECT and/or prepared queries
Socket	A8	4096	Socket buffer size
ReadOnly	A0	No/0	Read Only
CommLog	B3	0	Log communications to log file
UniqueIndex	N/A	1	Recognize unique indexes
UnknownSizes	A9	0 [= max prec. for type]	Unknown result set sizes
CancelAsFreeStmt	C1	0	Cancel as FreeStmt
UnknownsAsLongVarchar	B8	0	Unknowns as LongVarchar
BoolsAsChar	B9	0	Bools as Char
MaxVarcharSize	B0	255	Max Varchar size. Value of 0 will break everything. We will be changing 0 to mean unlimited and will then change the default to 0.
MaxLongVarcharSize	B1	8190	Max LongVarchar size
RowVersioning	A4	0	Row Versioning
ShowSystemTables	A5	0	Show System Tables
DisallowPremature	C3	0	Disallow Premature
UpdatableCursors	C4	0	Updatable Cursors
LFConversion	C5	1 Windows, 0 UNIX	LF <-> CR/LF conversion
TrueIsMinus1	C6	0	True is -1
BI	N/A	0	Datatype to report BIGINT columns as
LowerCaseIdentifier	C9	0	Lower case identifier
SSLmode	CA	disable	SSL mode
AB	N/A		Connection string suffix options

Abbreviations are for use in connection strings.