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

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)')

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.