Option 1a : Create a User DSN (Data Source Name)

This approach is optional. It is possible to create DSN-less connections (described below).

1. The ODBC Data Source Administrator can be executed by going to the RUN window (Windows key & R) and entering odbcad32
2. Go to the User DSN tab, and click the Add button
3. A list of installed drivers will appear. Select the latest ODBC Driver for SQL Server that is available. Ideally version 17 or later. Note that versions after 17 require that the connection be protected with a certificate.
4. Next assign the name, description and server this DSN will be using. For the movie example a good DSN name might be Movies, the description might be Driver Kit Example and the server can be selected from the drop-down or entered directly. I've found the drop-down to be spotty in detecting SQL Server Instances, so typing the name in can be more effective. The pattern is MACHINE\SERVER. On my local machine this is HULK\SQLEXPRESS.
5. The next step is to select an authentication method. The two most common options are With Integrated Windows Authentication (aka Trusted_Connection) or With SQL Server Authentication using a Login ID and password entered by the user. If you choose the latter then you will be prompted for an admin Login ID and password.
6. Two pages of options follow. These are outside the scope of this document. The defaults should be fine.
7. Click on Finish to finish creating the DSN

Option 1b :  Connection String for DSN Connection

In Clarion, for SQL tables, the OWNER attribute contains the connection string. The connection string contains all the information necessary to connect to the database, and can also contain settings for the database to use. The standard for connection strings is a semi-colon (;) separate list of setting=value;

Example;
DSN=DriverKit;UID=sa;PWD=password;Trusted_Connection=No;WSID=HULK;Encrypt=no;APP=MyApp;

In this example the last item is optional. The APP setting is especially useful though because it allows the server to identify the source of the traffic, which in turn makes the logs easier to filter, which in turn makes debugging and performance tuning easier.

Option 2 : Using a DSN-less connection

In the above example a DSN (Data Source Name) has been created on the client computer. This contains the server details, ODBC driver and database name. To create a connection without using a DSN those items need to be specified directly in the connection string. For example;

Driver={ODBC Driver 17 for SQL Server};Server=HULK\SQLEXPRESS;DATABASE=filedriverkit;UID=sa;PWD=password;Trusted_Connection=No;WSID=HULK;Encrypt=no;APP=MyApp;

The driver has been tested against {ODBC Driver 17 for SQL Server} and {ODBC Driver 18 for SQL Server}, so one of these should be installed and used. It is expected that in the future Microsoft will release ODBC Driver 19 etc, and then those can be used.

Option 3 : Using a Clarion Style Connection String

Collations

Collations determine the "alphabetical order" of string fields in the database.

When you create a new database without explicitly specifying a collation, SQL Server inherits the collation from the server-level (instance-level) collation. The server collation itself is chosen during SQL Server installation — it defaults to a collation based on the Windows locale of the machine, which for English (US) systems is typically SQL_Latin1_General_CP1_CI_AS.

If you are accessing an existing MS SQL database, then it already has a collation, and you will need to work within the boundaries of that collation. If you are creating a new database though, it may be advantageous to specify the collation directly, by setting the prop:CollationDB before calling the CREATEDB command.

Token	Meaning	Alternatives
SQL	Legacy SQL Server collation rather than a Windows collation.	Omit
Latin1_General	Locale/language sorting rules	French, Japanese, Arabic, etc.
CP1	Code Page 1. (Windows 1252, which is Western Europe)	CP1252, CP1253 and so on.
100	Sort rule version (100 = SQL Server 2008+)	90 (2005+), no number (legacy)
CI	Case Insensitive	CS (Case Sensitive)
AS	Accent Sensitive	AI (Accent Insensitive)
SC	Supplementary Characters support	Omit if not needed
KS	Kana Sensitive (Japanese)	Omit if not needed
WS	Width Sensitive (e.g. full vs half-width chars)	Omit if not needed
UTF8	UTF-8 encoding (SQL Server 2019+)	Omit for non-UTF-8

Because the install value may be quite old, it  is worth considering the collation for the new database carefully, because a newer collation may serve the program better moving forward. Changing the collation of the database, and data in the database, is hard to do after the database has been created.

A good collation to use (for US and Western Europe) is  Latin1_General_100_CI_AS_SC. This is a Case Insensitive sort, but is Accent Sensitive so distinguishes é from e. Some alternatives for Latin1 are in the table below. (This list is not exhaustive, see your SQL Server documentation for your locale.)

Collation Prefix	Countries / Languages	Notes
French	France, Belgium, Switzerland, Canada	Handles French accent sorting rules (e.g. é, è, ê)
Spanish	Spain, Latin America	Traditional Spanish sorting (ñ, ch, ll handling)
Modern_Spanish	Spain, Latin America	Updated sorting rules; ch and ll no longer treated as single letters
German_PhoneBook	Germany, Austria, Switzerland	Phonebook style: ä sorts as ae, ö as oe, ü as ue
German	Germany, Austria, Switzerland	Dictionary style: ä sorts near a, ö near o, ü near u
Greek	Greece	This handles the Greek alphabet correctly, including characters like α, β, γ, δ, ε, and the various accented vowels used in modern Greek (ά, έ, ή, ί, ό, ύ, ώ).
Danish_Norwegian	Denmark, Norway	Handles æ, ø, å correctly; these sort after z
Finnish_Swedish	Finland, Sweden	Handles å, ä, ö correctly; these sort after z
Polish	Poland	Handles Polish characters (ą, ć, ę, ł, ń, ó, ś, ź, ż)
Czech	Czech Republic	Handles Czech diacritics (á, č, ď, é, ě, í, ň, ó, ř, š, ť, ú, ů, ý, ž)
Slovak	Slovakia	Similar to Czech but with Slovak-specific sorting rules

CREATE

One major difference in the MSSQL2 driver over the traditional MSSQL driver is the CREATE command. The traditional driver has a very simple CREATE command, which does not allow for much control over creating the table. The documentation goes so far as to say;

Although CREATE is supported, only basic use is recommended, because it cannot handle all of the features (i.e., constraints and relationships) that an actual SQL CREATE TABLE statement can support. Use of PROP:SQL is preferred in this case.

In contrast to this the object based SQL drivers implement CREATE, and by default create a table correctly. It also gives the developer a mechanism to control the specifics of the table creation.

Traditional Behavior	Object Based Behavior
If the table exists in the database, and CREATE is called, then the table is dropped and a new one is created.	If the table already exists in the database, then a File System Error (90) Error is set. If replacing the table is desired do a REMOVE(table) first.
After call to CREATE, prop:sql returns only the SQL for creating the last index.	The prop:SQL returned contains the complete CREATE TABLE, as well as any CREATE INDEX statements.
Creating tables with TIME fields would fail.	TIME data type supported.

DateTime Fields

MS SQL Server supports a DATETIME (and DATETIME2) data type. This s a type that combines a DATE and TIME together into a single column. Clarion does not have a matching data type, so in order to support this type there are several options.

The common option is to use a GROUP over a STRING. The exact pattern looks like this;

DateTime       String(8)
DateTimeGroup  Group,Over(DateTime)
DateTime_Date    Date
DateTime_Time    Time
               End

This is a very effective approach. The driver recognizes this structure, and automatically translates the DateTime field into a Date component, and a Time component. The program can then use DateTime_Date (and DateTime_Time) just like a regular Date and Time field.


One limitation with the above approach is that the precision of the time component is limited to the precision of the TIME data type. Since Clarion Time supports hundreds of  a second, this means this approach is fixed at 2 decimal places (ie, hundredths of a second). By contrast SQL engines allow for more precision than that - commonly 3 or 7 digits of precision.  For this reason it's possible to create a STRING(29) or a CSTRING(30) which maps to a DATETIME (or more commonly a DATETIME2) type on the backend. The Declaration of the field looks like this;

someDateTime    CSTRING(30),NAME('someDateTime | MSSQLTYPE=DateTime2(7)')

Note the extended name attribute which tells the program that the backend data type should be a DateTime2 with 7 digits of precision. The driver will then automatically translate this string into that type, and vice versa, as data is read in and out of the program. Having the Date and Time values in a string is fine for display, but less easy to process as a "date" or "time" value. In order to extract the date and/or time from the string you need to extract, and deformat the values. You can use the Clarion DEFORMAT and FORMAT commands for this, or make use of the StringTheory library (StringFormat and StringDeformat classes) to do the extraction for you.

The format of the string is a date part (@D010-) which takes the form yyyy-mm-dd then a space separator then a time part (@T04) which takes the form hh:mm:ss followed by a decimal point, and the fractional seconds. For Example;

2025-09-16 13:51:43.1234

Note that the separator between the date and time parts is a single space, and a T is not used. This is in line with how most databases display the value.

The database does not store the value as a CHAR, but rather as a DATETIME2. So even though the program sees it as a string, it's not stored as a string.

Driver Options

See Also Common Driver Options.

Option	Default Value	Description
/AUTOINCUSESSCOPEIDENTITY /AUTOINC /PREAUTOINC	n/a	These settings allow control over how identity fields are returned to the program after doing an ADD. The new drivers use none of these options, and hence these settings are not implemented. The new drivers make use of the OUTPUT clause in the generated SQL to return both identity, as well as any (not written) fields with default values. This is supported in SQL Server 2005 and later.
/BUSYHANDLING	n/a	This setting is used by the traditional driver to define the behavior when multiple threads talk to the same database at the same time (over the same connection). Since the connections in the new drivers are thread based, connections are not shared across threads. Therefore this option no longer has any meaning. Effectively the new drivers are always in BUSYHANDLING=2 mode.
/GATHERATOPEN		Not Implemented Yet. The driver behaves as if /GatherAtOpen=True
/HINT	n/a	The use case for this property has not been clarified. It has not been implemented. Developers who have used this property are encouraged to contact CapeSoft to discuss future implementation.
/LOGONSCREEN	True
/MULTIPLEACTIVERESULTSETS	n/a	This is a setting used by the traditional driver, but which is not applicable to the Obd driver. The function of this switch was to allow for multiple result sets to be stored in the server at the same time. Since the new drivers do not use cursors, no result sets are stored in the server, so this option has no meaning.
/SAVESTOREDPROC	n/a	Not Implemented. Some prepared statements are cached on the server, these are automatically removed when the FILE or VIEW is closed.
/TRUSTEDCONNECTION	false	Trusted connections are a way for the database connection to use the same credentials (User name) as the User used to log into Windows. In other words if the user logged into Windows as "Howard", then it connects to the database using the user name Howard (and no further password is required.) Obviously a user Howard has to exist in the MS SQL database list of users for this to work. Trusted Connections are usually set by simply adjusting the owner string. In other words the regular owner string looks something like this; server,database,user,password it becomes server,database,,;Trusted_Connection=Yes

prop:Explain

The output from the prop:Explain property is send to DebugView++ (or equivalent).

Field Options

The driver supports Extended Name Attributes for fields.

To set a field to a specific data type, unique to this driver, use MSSQL2Type(whatever) where whatever is a data type unique to MS SQL Server.
This will override the type set using SQLType(something) if it exists.

Example;

paid         Byte,name('paid | sqltype(tinyint) | MSSQL2Type(Integer)')

Field Types

This section discusses types, and type behaviors specific to the MS SQL Server database.

Properties

Property	Comment
prop:ChildrenAffected	Not supported by MSSQL.
prop:Hdbc	Returns the current connection handle. As used by the ODBC API.

Multiple Active Result Sets (MARS)

In the traditional MSSQL driver connections to the database (by default) are shared across threads^[1]. To allow multiple threads to interact with the database at the same time (over the same connection) the driver offers the option /MULTIPLEACTIVERESULTSETS=TRUE.

Since the object based drivers do not share connections across threads, this setting becomes unnecessary. It does no harm setting it, but it is simply ignored by the driver.

Note 1: The /BUSYHANDLING option determines this behavior. If /BUSYHANDLING=2 then connections are not shared between threads.

SEARCH

In order to use the SEARCH function in MSSQL Server, a single-component primary key is required.

SUSPENDRELATIONS

This command should be used with care.

With the MSSQL2 driver the SUSPENDRELATIONS call applies only to the table itself. Not to the connection or database as a whole. However, they disable not just the relationship tests, but also CHECK constraints (ie custom validations) and also UNIQUENESS checking. 

Note that this suspension is applied to the database, for the table. It thus applies to ALL PROGRAMS, not just the program or thread making the call. Care should be taken to ensure that RESUMERELATIONS is called or the relationships (and other constraints) will remain suspended indefinitely.

In MSSQL SUSPENDRELATIONS allows rows to be added, changed and deleted without relational checks (and without the other checks described above.) However it does not allow a table to be dropped, so the REMOVE command will still not work on a table if it is referenced by another table. EMPTY will work, but to drop the table you first need to also drop the tables, or references in the other tables.

TIME Fields

The TIME data type in SQL Server allows the field to declare the decimal precision of the field. This is written as Time(n). When n is 0 then this limits the resolution to seconds, when n is 1 to tenths of a second, n is 2 to hundreths of a second and so on. Up to seven decimal places are supported (by the database). By contrast the TIME data type in a Clarion structure contains hundredths of a second. This is directly equivalent to a TIME(2) field in SQL Server.


If the table was created in the database using some other means, then the most likely precision's to be encountered are TIME(0), TIME(3) and TIME(7). If these (or any others) are mapped to the Clarion TIME type then some of the data may be lost when reading or writing. For example, if a  TIME(3) value contains milliseconds, then if it is loaded into a Clarion TIME field, only hundredths of a second are loaded. The last digit of precision is lost.

Equally if a Clarion TIME field is written to the database, only 2 digits of precision are written, regardless of the back end precision. If the back end precision is less than 2, then the time is truncated before storing.

Unlike the case with strings, if digits of precision are lost when reading a TIME value, the driver does NOT generate a DataTruncatedError (132) Error. Rather the value is simply silently truncated.

If a time precision greater than 2 is required, then either a STRING field or a CSTRING field  should be used on the Clarion side. The length should be at least 9 plus the precision. So a Time(7) should be 9+7 = 16. If a CSTRING is used add 1 for the terminator.
For example;

EntryTime    String(14),Name('EntryTime | MSSQLTYPE=TIME(5)')
ExitTime     CString(17),Name('ExitTime | MSSQLTYPE=TIME(7)')

UNIQUEIDENTIFIER Type

In MSSQL the server-side UUID type is known as UNIQUEIDENTIFIER. If the SQLTYPE() in the dictionary is set to UUID then it will automatically be translated to UNIQUEIDENTIFIER on the server side.

Work In Progress

The following commands are not yet functional;

SEARCH  .

Boolean

PostgreSQL has a BOOLEAN data type. If a field is marked as Boolean, then this type is used.

Paid       BYTE,NAME('Paid | Boolean')

Booleans are always 0 or 1.

The only constraint that is supported is NOT NULL. Any other constraints declared in the dictionary are ignored.

Delivered  REAL,NAME('Delivered | Boolean | NotNull')

CLONE

The Postgres2 driver groups the options Driver:Constraints, Driver:ForeignKeys and Driver:UniqueKeys together. If any of these are specified then all of them are applied.

Connection Strings

The native connection string for PostgreSQL is a space separate list of keyword=value. if the value contains a space then the value must be quoted, with single quotes. Single quotes, and backslashes in a value must be encoded as \' and \\ respectively. Keywords are case sensitive.

Example

host=localhost port=5432 dbname=filedriverkit user=bruce password=Bruce1

An alternative format is the URI format

Example

postgresql://bruce:Bruce1@localhost:5432/filedriverkit

The PostgreSQL documentation for the connection string can be found at https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING.
A complete list of possible keywords is at https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-PARAMKEYWORDS.

Option : Using a Clarion Style Connection String

Collations

Collations determine the "alphabetical order" of string fields in the database.

Collations in PostgreSQL get really complicated, really quickly. Going into every nuance is beyond the scope of this documentation (chat to your AI if you're confused.)

Your context  will play a big part in what you can do here. If you are connecting to a database that already exists, a database owned by another system, then your flexibility here is limited, and ultimately your program will respond to the database settings as they are. However if you are creating the database yourself (perhaps using the CREATEDB command or a SQL script) then you get a one-time opportunity to do it right. After the database has been created, the default cannot be changed.

By default, when a database is created in PostgreSQL, it queries the Operating System to get the default text collation. This is a poor choice, but for historical reasons, and backward compatibility, it still does this. It is a poor choice because the behavior of the database (and hence the behavior of your program) can vary (quite a lot) between one deployment and another. For example the OS collations suggested by Windows and Linux are quite different. Once set, the collation cannot be changed (even if the OS locale changes.) Worse, the database collation may not even be available if the Operating System changes (such as moving the server from Linux to Windows or vice versa.)

Therefore, when creating a new database, it is better to use a PostgreSQL collation, not the OS collation. These are known as ICU collations. A typical SQL statement to create a database would thus look something like this;

CREATE DATABASE somedatabase
  TEMPLATE template0
  ENCODING 'UTF8'
  LOCALE_PROVIDER = icu
  LC_COLLATE 'en-US-u-ks-level2'
  LC_CTYPE 'en-US-u-ks-level2'
  ICU_LOCALE = 'en-US-u-ks-level2';

ICU Strength	Level	Case-sensitive?	Accent-sensitive?	Description
PRIMARY	1	No	No	Only base letters matter (a = A = á)
SECONDARY	2	No	Yes	Accents matter, case ignored (a = A < á)
TERTIARY	3	Yes	Yes	Accents + case matter (A < a < Á)
QUATERNARY	4	Yes	Yes	Rare, punctuation and variants considered
IDENTICAL	5	Yes	Yes	Tie-breaker on code points

The locale in the above script (en-US) is not limited to US characters. It encompasses all unicode characters. However different countries have some slightly different rules when it comes to where "international" characters exist when sorting "alphabetically". For example;

ICU Locale	Country / Language	Notes on Sorting & Differences	Typical Use Cases
en-US	English (US)	Accents mostly ignored; a < b < c; ä sorts after a (treated as variant of a)	General English text, US-centric apps
en-GB	English (UK)	Similar to en-US; minor differences in ordering punctuation or symbols	UK English content
de-DE	German	ä sorts as ae (after a), ß as ss; accents considered; case-insensitive by default at level 2	German text, names, dictionaries
fr-FR	French	Accents matter; é after e; ç after c; secondary level for case	French names, dictionaries
es-ES	Spanish	ñ is considered a separate letter after n; accented vowels distinguished	Spanish text, sorting names alphabetically
it-IT	Italian	Accents matter (à, è, é) but sorted close to base letter; ch not treated specially	Italian text, names
pt-PT	Portuguese	ç after c; accented vowels sorted with base letters; ã and õ handled	Portuguese text
sv-SE	Swedish	å, ä, ö are distinct letters sorted after z; case-insensitive at level 2	Swedish text, names, dictionaries
nl-NL	Dutch	ij often treated as separate letter; accented letters handled with base	Dutch sorting
da-DK	Danish	æ, ø, å are distinct letters sorted after z	Danish dictionaries and names

Most PostgreSQL installs will include all of the above, and many other ICO collations. However, especially on Linux (which relies on an ICO library outside PostgreSQL) they may not all be available. If necessary you can inspect the list of available collations using the SQL script;

SELECT collname
FROM pg_collation
WHERE collprovider = 'i'
ORDER BY collname;

This won't actually match the collation the CREATE TABLE will use - only the first part needs to match. So if you see en-US-x-icu then this means the en-US collation is available.

So, knowing all the above, what do you need to do?

The answer is, almost nothing.

If you are not creating the database, or the tables, then your program will behave according to the database collations as they exist. There's not a lot you can change here. You can retrieve the default collation of the database using prop:CollationDB.

collation = Customers{prop:CollationDB}

If you are not creating the database, but you are creating the tables, then it is worth inspecting the default collation for the database to see if it is consistent with your needs. If not you will likely need to set the collation for all the strings at the field level, in the dictionary.

If you are creating the database then pay particular attention to the collation you use. If you are using the CREATEDB command, then the prop:CollationDB will be used. The default value, if this is not set by the program, is en-US-u-ks-level2. This is a case-insensitive collation, using a pretty standard alphabetical order. If you wish to change it you can do something like;

Customers{prop:CollationDB} = 'de-DE-u-ks-level2'
CreateDB(Customers,'MyAmazingDatabase')

Naturally this code needs to run before the database is created, once the database has been created this command no longer does anything.

Case Sensitive / Insensitive Keys

This section is provided for background information. It is unlikely that you will need to do anything in either the program, or database. Things should "just work". However if they do not this section is designed to explain the logic which determines how case sensitivity works in PostgreSQL.

In Clarion, a case sensitive key has an effect on two parts of the SQL SELECT statement; the WHERE clause and the ORDER BY clause.
The WHERE clause (ie the prop:Filter) is a equality

The order of characters is determined by the collation. In other words, something we call alphabetical order, is really just an arbitrary order, and the collation determines what that actual order is.

There are two collation settings in play for any string in the database. The first is the default collation of the database, the second is the collation for the string itself. The default collation is set when the database is created. If you are using an existing database, then you can't change this value. You can determine the collation of the database by executing the SQL

SELECT datcollate FROM pg_database WHERE datname = 'whateverdatabasename';

From your Clarion program you can also query prop:CollatationDB. This will return the same value.

collation = Customers{prop:CollationDB}

If the server is running on a Windows computer the value is likely to be something like English_United States.1252. If the server is running on a Linux computer the value is likely to be en_US.UTF-8. Or (in both cases) it might be something different like en-US-u-ks-level2. These collations (and the other locale-based locations) are case insensitive. It is very unlikely the database will have a default case-sensitive collation. This means that by default, sorting of the strings, and comparison of strings is case insensitive. Which is what most programs want.

If the collation is set to C or Posix then that would be case sensitive. If the collation is *-level3 then it is case sensitive. For more on the meanings of collation names see collation.

Note that in PostgreSQL, in many collations, lower case comes before uppercase. So apple comes before äpple comes before Apple comes before APPLE comes before banana. The order is consistent, but it may be unexpected to see lower case before upper case in a browse.

In Clarion this all matches a case-insensitive key exactly. Sorting alphabetically just works, filters just work, and so on.

In Clarion we also have case-insensitive keys. These are keys which use the underlying ASCII value for sorting. A comes before B, comes before a, comes before b, comes before ä.This is not terribly useful for sorting strings (like in browses), but occasionally it is useful when dealing with case sensitive real-world identifiers. When creating case-insensitive keys (with strings) in your dictionary, and when using the key in a GET or SET statement, the matching level-3 collation (or, if not a ICU collation then the C collation) is used.

It is also possible to set the collation of a string field when the table is created. The desired collation can be set in the dictionary, using the Extended Name Attributes. For example;

Name    String(100),Name('LastName | Collate(en-US-u-ks-level3)')

This would set this column to use the en-US collation, level 3. Level 3 is case sensitive. In this approach it's not necessary to make the key case sensitive. The field collation would ensure that the field is used in a case-sensitive way.

Note that it is possible to create two different keys on the same column, one case sensitive and the other not. The GET or SET command would then use the appropriate key for the desired outcome.

See also prop:Collation, prop:CollationDB

CREATEDB

The PostgreSQL2 driver creates the database using an icu collation. You can set the collation as described above.

Driver Options

Option	Default Value	Description
/BUSYTIMEOUT=n	5	Sets the lock_timeout for the connection. Value is in seconds.
/WHERE		Used with SEND. Same as setting {prop:Where}.

libpq

The Postgres2 driver connects to the Postgres database using the libpq.dll file. This bypasses the ODBC layer. This is similar to the sqlite3.dll which the SQlite2 driver uses. The Postgres2 driver ships with the libpq.dll file (as well as the other DLL's it requires.)

License

libpq is licensed under the LGPL 2.1 License. The source code to libpq is available from the https://www.postgresql.org/ftp/odbc/releases/.

libpq makes use of OpenSSL. Hence this product includes software developed by the OpenSSL Project for use in the OpenSSL Toolkit. This code is Copyright (c) 1998-2025 The OpenSSL Project. All rights reserved. It is licensed under the terms of the Apache license.

Updating libpq

The necessary libpq files ship with the SQL driver kit. However it may be desirable to update the libpq DLL from time to time so this section explains where the files come from. Obviously later versions of libpq may, or may not, be compatible with the driver itself.

Since Clarion (up to at least Clarion 12) is a 32 bit program, it is necessary to make use of the 32 bit version of libpq. The easiest way to get this is to install the latest version of the Postgres 32 bit ODBC driver. This can be retrieved from https://www.postgresql.org/ftp/odbc/releases/ . Note that the ODBC (libpq) version number is distinct from the Postgres database version number. There is a very high degree of compatibility between all versions of libpq, and Postgres since PosgreSQL database version 10.

After installing the ODBC driver the location for the actual ODBC files can be located in the Windows Registry. The key to example in the registry is Computer\HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\ODBC\ODBCINST.INI\PostgreSQL. Typically they will install into C:\Program Files (x86)\psqlODBC. There is likely one or more numbered sub directories, one of which will contain a bin folder. The DLLs will be located in that folder. Note that the number of the folder may not correspond to the number of the ODBC driver being installed.

Additional supporting DLL files;

• libssl-3.dll
• libcrypto-3.dll

Name Limits

All names in PostgreSQL are limited to 63 characters. Since index names are constructed using tablename_keyname syntax, it is recommended that table and keynames are limited to around 30 characters in the dictionary.

Primary Keys

PostgreSQL does not support case-sensitive Primary Keys, and Primary Keys may not Exclude Nulls. If those options are set for the primary key, they are ignored.

Storage

In Postgres all CHAR and VARCHAR fields are stored as utf-8 encoded strings.

If your application only makes use of ASCII text (in other words characters <= 127) then this is not a problem since utf-8 and ASCII overlap in this range. So an ASCII string is implicitly a utf-8 string as well. If you are only dealing with English letters, then this is no issue at all.

The PostgreSQL2 driver automatically converts all STRING, CSTRING , PSTRING and MEMO (not binary) fields into utf-8 encoding before writing, and decodes them after reading. There is nothing for you to do.

If there is an error when encoding or decoding the data, then an Error 140 Unable to convert to, or from, unicode is generated.

When converting from ANSI to utf-8 a CodePage is used. The CodePage for the file is automatically set (on each thread) based on the Clarion system{PROP:CharSet}^[1] setting.For example if your program has set;

System{prop:Charset} = CHARSET:GREEK

Then the code page used will be Windows 1253. A list of Clarion Charsets can be found in Equates.Clw.

If you have not set a charset in your program then the charset used will be Windows 1252.

If the automatic code page is not correct for your situation, then you can set

tablename{prop:codepage} =  whatever

In this case you are not setting the charset, but the code page. This is a value defined by Windows - for example

CP_ISO_8859_1        equate(28591)

The StringTheory.Inc file contains a number of code page equates, but any Windows Code Page value can be used here.

Note 1: In Clarion 11 a new system property, System{prop:codePage} was introduced. If set, this code page is used. If not set then the System{prop:CharSet} is used as described above. StringTheory 3.82 or later is required for System{prop:codePage} to be used.

SUSPENDRELATIONS/ RESUMERELATIONS

To suspend relationships in PostgreSQL the driver drops all the foreign key constraints for the table. Thus in PosgreSQL the driver executes this command on the specific table, not the whole database.

SUSPENDRELATIONS first queries the database to get a list of existing relationships. This is independent of calls to ADDRELATION. This is stored internally by the driver, and used during to RESUMERELATIONS recreate the relationships.


NOTE: Calling SUSPENDRELATIONS removes the relationships from the database. Failure to call RESUMERELATIONS will result in the relationships being permanently removed.

See also SUSPENDRELATIONS and RESUMERELATIONS.

Transactions

A PostgreSQL transaction will effectively terminate on the first error that occurs. It is extremely sensitive in this regard.

So, for example, if an ADD fails (because of a duplicate key error, or constraint or whatever then the transaction has "failed" and ROLLBACK will need to be called.

Movies

The simple MSSQL2 example is in your \Examples\DriverKit\Movies\MSSQL2 folder.

To load, compile, and run this example you will need to first have;

1. Compile the Drivers.
2. Registered the Driver into your Clarion.
3. Make sure your ODBC layer has either ODBC Driver 17 for SQL Server or ODBC Driver 18 for SQL Server installed.
4. Edit the connect string, in Connect.Ini, to match the requirements of your server. A sample Connect.Ini is in the application folder.

Create the Database

The File Menu has a button labelled Create DB. This button demonstrated creating the database in the MS SQL Server (if it doesn't already exist). The code also turns on Logging for the command so that any debug information can be sent to Debugview++. This can be very helpful in making sure the connection to the database is working. the code under the button is as simple as;
        LoggingOn(Movies)
        CreateDB(Movies)
        LoggingOff(Movies)

Import Some Data

As of writing this, the IMPORT function for the MSSQL2 driver is not yet operational. So currently the data is distributed as a JSON file, and jFiles is used to import it into the database. Click on the IMPORT button in the File Menu to import the data. The import process is very simplistic, and it does not display any progress. Once it is complete a MESSAGE will appear. A typical import time (from JSON) is around 5 minutes.

This is an interim approach for now - importing from CSV is a lot faster.

Version 1.09 - September 4, 2025
Remember to Recompile the Drivers.

• Add: Support for driver option /TrustedConnection
• Add: Better support for TIME fields.
• Add: More support for massaging Owner field. Support for different server port numbers.

Version 1.08 - August 18, 2025
Remember to Recompile the Drivers.

• First release of MsSql driver