MSSQL2 Driver
The goal of this section is to highlight details about the MSSQL2 driver.
Work In Progress
The following commands are not yet functional;
SEARCH ,
IMPORT
,
EXPORT .
CONNECT
As of SQL Server 2022 the SQL Server Native Client has been deprecated
by Microsoft. As recommended by Microsoft the object based driver makes
use of the ODBC interface to the server. Since ODBC itself has it's
roots in MS SQL Server, the two approaches were always very similar.
Note that the 32 bit ODBC connection is used by 32 bit programs.
The latest Microsoft ODBC Driver for SQL Server can be found at
https://learn.microsoft.com/en-us/sql/connect/odbc/microsoft-odbc-driver-for-sql-server?view=sql-server-ver17.
The following steps will assume that a recent version of this driver has
been installed on the client computer.
Setting up an ODBC connection, on the client computer, is straight
forward, however it can be daunting when done for the first time.
Create a User DSN (Data Source Name)
This step is somewhat optional. It is possible to create DNS-less
connections (described below).
- The ODBC Data Source Administrator can be executed by Going to the
RUN window (Windows key & R) and entering odbcad32
- Go to the User DSN tab, and click the Add
button
- 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.
- Next assign the name, description and server this DSN will be
using. For the movie example a good DNS 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.
- 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.
- Two pages of options follow. These are outside the scope of this
document. The defaults should be fine.
- Click on Finish to finish creating the DSN
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. |
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
|
|
|
|
|
|
|
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.
UNIQUEIDENTIFIER
In Microsoft Sql Server the UNIQUEIDENTIFIER
field type is a 16 byte binary string, which is typically displayed as
a 36 byte hex-encoded string, with 4 hyphens.
If you wish to create a UNIQUEIDENTIFIER
field, you can add the SQLTYPE attribute
to the field declaration in the dictionary. For example;
Fieldname | UUID7 | SQLTYPE(UNIQUEIDENTIFIER) |
DEFAULT=NEWSEQUENTIALID()
When the CREATE function is called this
field will be created as a UNIQUEIDENTIFIER
and in this example the default value of this field (if not provided
during an INSERT) is a call to the SQL NEWSEQUENTIALID() function.
The Clarion Data Type for this field should be a STRING,
and the length set to either 16 or 36. If 16, then the field will be a
binary value in the record. If 36 then the field will be a 36
character, hex encoded value (with 4 hyphens). For example
0E33E2E7-A3BB-CA48-A3D9-DE0D9581C3B4.
Note that when reading and writing
the database the SQL Type is determined by inspecting the database. It
is not determined by the SQLTYPE setting
above. That setting is only used for CREATE
command not for the read and write commands. If the type in the
database is a UNIQUEIDENTIFIER then it maps
into the Clarion STRING, even if there
is no SQLTYPE setting in the dictionary.
Again, the string should either be 16, or 36 characters long.
If the field is set as SQLTYPE(UNIQUEIDENTIFIER)
then it will usually (but not necessarily) have UUID4 or
UUID7 set as well. The UUID4 and UUID7 settings are primarily used as
a client-side default when a row is being inserted, and the field is
blank. If the field has a server-side default set ( DEFAULT=NEWSEQUENTIALID()or
DEFAULT=NEWID() ) then the field does not
need to be populated on the client side, and so the UUID4 or UUID7
attributes are redundant. The server-side function NEWID() generates a
UUID version 4 value. The server-side NEWSEQUENTIALID() function
generates a time-sequenced value, however it is not a standard UUID
version 7 value.
Properties
| Property |
Comment |
| prop:ChildrenAffected |
Not supported by MSSQL. |
| prop:Hdbc |
Returns the current connection handle. As used by the ODBC
API. |
|
|
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.
