Chapter 17. HyperSQL via ODBC

How to access a HyperSQL Server with ODBC

Blaine Simpson

The HSQL Development Group

$Revision: 5999 $

2020-06-29

Table of Contents

Overview
Unix / Linux Installation
Windows Installation
Settings
Samples
Table of Settings

Overview

Support for ODBC access to HyperSQL servers was introduced in HSQLDB version 2.0. Modified versions of the PostgreSQL ODBC software (version 8.3) were developed and an installer for 32 bit Windows was made available for download. 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 FreeeStmt

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

KeywordAbbrev.Default Val.Purpose
DescriptionN/A Data source description
ServernameN/A[required]Name of Server
PortN/A9001HyperSQL Server Listen Port
UsernameN/A[required]User Name
PasswordN/A[required]Password
DebugB20MyLog logging level
FetchA7100Fetch Max Count Test to see if this applies to EXECDIRECT and/or prepared queries
SocketA84096Socket buffer size
ReadOnlyA0No/0Read Only
CommLogB30Log communications to log file
UniqueIndexN/A1Recognize unique indexes
UnknownSizesA90 [= max prec. for type]Unknown result set sizes
CancelAsFreeStmtC10Cancel as FreeStmt
UnknownsAsLongVarcharB80Unknowns as LongVarchar
BoolsAsCharB90Bools as Char
MaxVarcharSizeB0255Max Varchar size. Value of 0 will break everything. We will be changing 0 to mean unlimited and will then change the default to 0.
MaxLongVarcharSizeB18190Max LongVarchar size
RowVersioningA40Row Versioning
ShowSystemTablesA50Show System Tables
DisallowPrematureC30Disallow Premature
UpdatableCursorsC40Updatable Cursors
LFConversionC51 Windows, 0 UNIXLF <-> CR/LF conversion
TrueIsMinus1C60True is -1
BIN/A0Datatype to report BIGINT columns as
LowerCaseIdentifierC90Lower case identifier
SSLmodeCAdisableSSL mode
ABN/A Connection string suffix options

Abbreviations are for use in connection strings.


$Revision: 6145 $