Files
Database Design
Many dictionaries have a long and storied history, and began their days
with ISAM files. As object based drivers are primarily SQL based it's
worth stating some design tips which should be applied to existing
dictionaries for best functionality and performance;
| Tip |
Context |
| All Tables need a Primary Key |
Everywhere. Modern systems require primary keys, and the
object based drivers assume you have one. |
| Ideally (but not required), the Primary key should have one
component. |
Where possible the primary key should have a single component.
This is not a requirement, but will improve performance. |
Dictionaries coming from ISAM roots also tend to make use of features
which are common in ISAM files, but generally have been frowned upon, or
have not been available in the SQL context. Some of these limitations
have now been removed.
| Tip |
Context |
| Arrays |
Object based drivers support arrays,
storing the data either in the native array format (if the
database supports it) or in JSON if not. |
| Exclude Null Keys |
Previously not supported in SQL databases, now generally
supported. |
| Giving keys unique external names |
Used to be a requirement, but now not
necessary (and possibly hurtful) in object based drivers |
| STRING versus CSTRING |
There was a perception that STRINGs in the database needed to
be converted to CSTRING. This is not true for object based
drivers. |
| MEMOs and BLOBs |
Memos are supported by object based
drivers. BLOBs are supported by all SQL drivers. |
| Record Length |
In Topspeed the maximum length of a single record is 15K, not
including MEMO and BLOB fields.
In MsSql the maximum length is 8K, however VARCHAR and TEXT
fields can be excluded from this calculation.
In PostgreSQL the maximum length is 8K[1], however
VARCHAR, BYTEA and TEXT fields can be excluded from this
calculation.
In SQLite (and therefore also in InMemory) the default size is
4k, but is configurable up to 64K. BLOB and TEXT are not
included in the calculation.
Firebird defaults to 8K, but can be extended to 16K at the time
of the database creation. As with the others, BLOB and TEXT
fields are not included in this limit.
In summary, keeping the record under 8K in size is generally a
good idea. However it's not a hard limit as large strings
(VARCHAR fields) are usually not included in this calculation.
MEMO and BLOB fields are also not included.
[1] Technically the PostgreSQL value can be changed, but only by
recompiling the actual server. |
Arrays
None of the traditional SQL drivers support arrays. Object based drivers
however do support arrays.
There are some limitations;
- Arrays cannot be used in Keys, and (generally speaking) are not
easy to use in FILTER or ORDER attributes.
- Arrays of the GROUP, MEMO
and BLOB data types are not supported.
Changing the size of an array in the dictionary may, or may not, trigger
a table update (ie Error 47). Some databases set arrays to specific
sizes, others do not.
Storage of array data in the actual database can vary from one database
to another. How the data is stored in the database however is of little
concern to the program, and won't matter when writing Clarion code. The
rest of the section discusses different possible storage approaches, but
this is for background information only and is not important when using
FILE or
VIEW
structures.
Some databases (like PostgreSQL and Oracle) have direct support for
arrays, some (Firebird) have more limited support, and some (SQLite,
MySQL, MariaDb, MS SQL Server) make use of JSON functionality, storing
the array as a JSON text string, and using JSON commands to interact
with specific array values.
Auto Numbering
One of the joys of working with Clarion is that creating a
Primary
Key Identifier for each row in the table has always been
trivial. By creating a field (a number, usually a
LONG)
in the table, and then creating a key on this field, and setting the key
to
auto-number, the underlying code would
be generated to create unique values in this row. This approach, where
the program creates the value, is known as
Client
Side Auto Numbering.
There are quite a few structural problems associated with this approach,
and these became evident as time passed and bigger, and more
complicated, systems were designed. One of these problems is the case
where multiple programs are created (some of which may not be in the
Clarion language) which are adding data. Relying on each of them to
number the field correctly is not ideal.
Most SQL databases support the idea of an IDENTITY
[1]
column - that is one which is allocated
by the
server, when a record is added. Using that feature, the task of
doing auto-increments is moved onto the server, and the client need not
worry about the field at all.
Server Side Auto
Numbering is born. Since this option exists it is the work of
but a moment to identify the field, and let the server increment the
number for you.
Unfortunately it's not always quite that simple. Auto Numbering has
other issues and while this solves one problem, it opens the door to
others. Thus configuring your dictionary to use Server Side Auto
Numbering can be tedious.
The primary problem is that the program may need to know what the
auto-incremented value of the record just added is. In many cases the
ADD, of a parent record (for example
Invoice)
generates a number which will immediately be used when adding child
records (for example
LineItems). (Happily
the driver can sort this out for you.)
Secondly though, the
templates still need
to know that auto-numbering exists because when forms are encountered
the parent record is added when the form opens. This then gets that
unique ID value, so that child records can be added while the form is
open (again, think of the
Invoice /
LineItems situation.) As long as rows are being
added this works well, but it gets messy when an
ADD
is cancelled.
Thirdly, your SQL server may recycle numbers which have been used (but
discarded). Or it may not. Consult your SQL database documentation
to determine what yours does. Hopefully you are happy with the default
approach. Some object based drivers may offer a way to explicitly reuse
or not reuse numbers.
Fourthly a
LONG field type can hold a
maximum value of just over 2.1 billion. So you will encounter problems
with tables that accumulate data quickly, and may accumulate more than
2.1 billion rows over its lifetime (assuming row numbers are not
reused.) Ideally you would just use an
INT64
integer instead, but we don't (yet) have that as a natural data type in
Clarion. (A
REAL would be a good
alternative in this case though.)
Fifthly some databases allow you to override the Server-side generation
(by passing in a specific value) others do not. If a field should not be
set by the program you can add the
| READONLY |
attribute to the
External Name.
When this switch exists, the field will not be included in
ADD
statements. It will automatically be set though when reading the table,
and also after the
ADD command is called.
All of the above serves as a warning that Server Side Auto Incrementing
should be used deliberately, and with care. It works fine, but the
boundaries of the usage need to be understood.
Note 1:
Different database engines have different terminology for an auto
numbered field. MS SQL Server calls it an
Identity.
PostgresSQL refers to it as a
Serial,
SQLite uses
AutoIncrement, MySQL and
MariaDB call it
Auto_Increment, Firebird
calls it a
Sequence and so on. Regardless
of the database terminology the setting(s) in the dictionary refer to it
as
ISIDENTITY.
Implementing Client Side Auto Numbering
- Create a KEY on the field, and set Auto-Numbering on the key to ON. Typically
this is a single component key, and is the primary key for the
table, but there could be more components in the KEY
(as long as this field is the last one.)
- For SQL drivers you can make use of the \AutoInc
and \PreAutoInc Driver Strings to
create a SELECT statement which will most
optimally prime the auto incrementing value for you. If these
are not used then generic code will be generated by the templates,
which may not be optimal for your SQL database. Unless you are
adding lots of records, in a short space of time, this performance
factor though will not be important.
Implementing Server Side Auto Numbering
- Make sure Auto numbering is OFF for all keys
containing this field. For server-side auto-numbering the key MUST NOT have auto-numbering option turned
on.
- Instead, identify the field in question by setting the Field
User Option
ISIDENTITY=1
- In code, sometime before the record is added, tell the driver that
the field exists. (If you are using an APP this is done for you by
the template.) The code consists of two lines; (It is
documented here for hand-coded programs.)
SomeFile{PROP:ServerAutoInc}
SomeFile{PROP:ServerAutoInc,n} = m
Where n is the field number and m is the column number of the returning SQL
code.
OR
- (Use this step in place of step #3). Object based drivers can
automatically set this property, if the |
ISIDENTITY attribute is added to the field external
name. This is somewhat less work than the step #3, and also
doesn't require the program to add code to the program. Thus it
works equally well in App-generated, or hand-coded programs. This
step is in addition to, not in replacement of, step #2 above.
- If you want the templates to treat the file as
server-side-auto-numbered, specifically if you want forms to ADD the record on form open, then you need
to add a File User Option to the
table in the dictionary;
EmulateAutoNumKey=1
The mechanism for populating the assigned value, and retrieving it by
the program, has been different for each database engine over the years.
However recently the
RETURNING (or
OUTPUT)
SQL clause has been added to most common databases and so this is the
suggested and preferred way for object based drivers to get the value
(and the current drivers use these features.) This does limit access to
earlier versions of the database engine though.
| Database |
Version |
Year |
| MS SQL Server |
Sql Server 2005 |
2005 |
| PostgreSQL |
8.2 |
2006 |
| SQLite |
3.35.0 |
2021 |
| Firebird |
2.0 |
2006 |
| MariaDB |
10.5 |
2020 |
| MySQL |
8.0.27 |
2021 |
Using the
RETURNING clause incurs a slight
(and the emphasis is on slight) performance penalty when doing an
ADD. For this reason the
APPEND
command does not include the
RETURNING clause
and thus maintains its status as
A Faster ADD.
This makes
APPEND suitable for things like
bulk adds where the identity value is not needed in the procedure.
Tables that do not have an auto-numbered field do not incur this
performance penalty on
ADD.
After a call to
ADD or
PUT,
the identity field in the table record is updated to match the value in
the database. (In other words, after the write, the value in the file
record matches the database, regardless of it's value before the write.)
You cannot change the value of an IDENTITY field by using
PUT.
Values in any IDENTITY (or READONLY) fields are not sent to the server,
but are rather updated from the server value after the read.
UUID's
Auto Numbering (server or client side) presents a number of other issues
as well. This is not the place to discuss them in depth, but in no
particular order they include;
- They "leak" information from the system where they are used.The
most well-known example of this is the German Tank Problem. This introduces security
concerns, especially as databases move from the local LAN to wider
systems on the web or on mobile.
- They require a "single database" for adding records. It's
difficult (and prone to failure) to use auto-numbered records in
distributed environments. That might be as simple as exporting to
Excel, editing, and re-importing. Or it might be as complex as data
being sent to phones, new data captured on the phone, and then sent
back to the server.
- Merging data sets becomes hard work, and irreversible. So when the
data from two databases (say branch offices) needs to be
consolidated into a single database, everything has to be remapped -
which takes significant time and effort.
- In order to add child records to the database, the parent record
must be created (in order to get the parent ID) first. This
complicates a lot of UI (especially when it comes to managing the
CANCEL button).
Switching from an Auto Numbered field to a
Client-Side
UUID field overcomes these problems.
Aside: You may have seen GUID, or UUID, values
represented like this;
75163EA6-76C8-473D-A619-E9F30B489912.
While this is a common way of
displaying a
UUID, it's
not actually stored like this.
The stored value is a binary string, this display is the binary value,
but has been hex-encoded for viewing.
Client Side Generated UUID values
While there are variations, you can consider a Universally Unique
Identifier (
UUID) field to be a random
string field, which is 16 characters long. You add it to your record
structure as a
String(16). Because
the value in it is random, or at least a significant part of it is
random, data can be easily added by multiple different systems at the
same time, and all the issues described above simply go away.
Object based drivers support setting a field in the dictionary to be a
UUID. The driver will automatically populate the field (if it is
blank) or use the value in the field (if one is pre-set). In other
words, other than the file declaration, no program code is needed to
support UUID's.
There are four primary arguments
not to
use UUID's but to use
Auto Numbering
instead;
- Since the UUID consumes 16 bytes[1]
instead of 8 bytes (for a BigInt) or
4 Bytes (for a Long), and because
primary keys propagate into child tables, and many keys, a
database based on UUID's will consume more disk space than one
based on numbers. While this is a valid point, your context will
matter. For example wide tables (tables with many columns) will
likely take up space anyway, and an extra few bytes per row will
not make a significant differencw. Also, disk space is cheap, so
how much this size difference matters overall to a system is
doubtful. In most cases the percentage increase in size use is
likely to be minimal.
- Since UUID's are not numeric they require more CPU time to
process (comparing, sorting etc). True, but again, whether it is
significant or not depends on your context. Most CPU's are good at
dealing with (short) strings, and this is seldom the bottleneck in
database operations. Using UUID's is a common practice, so
databases are optimizing for this which in turn reduces this
performance penalty.
- Most databases use (by default) the Primary Key as the Clustered
Key. Meaning that records are physically laid out on disk
in this order. This can be more efficient when processing records
in that order. Since UUIDs are random, they tend to be added
out-of-order. Which can make ADD
slower. This can be mitigated by choosing another key to be the
clustered key. This may be advantageous if supported by your
database - not all of them do. If changing the clustered key
is not an option then a specific form of UUID (version 7 - see
below) deals with this by pseudo-ordering ID's. So this is a non
issue when using that form of the UUID.
- Collisions: Since randomness is involved, and multiple databases
could be adding data, it's possible to get two records added with
the same UUID, which in turn could be a problem. However while
this is possible, it's not probable.
Regardless of the UUID version chosen the random numbers generated
are huge. (Think of it as rolling 32 dice, and having them all
come up 6 at the same time.) Probability is a complex subject, and
humans are not good at comparing probabilities, but it's fair to
say you have more risk of being hit by a small rock falling from
the sky (ie a meteorite) than of encountering a collision in UUID
space.
There are 3 different kinds of UUID supported by the driver;
| Attribute |
Behavior |
| UUID4 |
The UUID conforms to the standard UUID format, version 4. This contains the most amount
of randomness (122 bits worth.) The value in the string is a
binary value, so cannot be displayed on the screen. This is
fine for most systems, but if you are exporting the data to a
text output you will need to encode the value. [2]
|
| UUID7 |
The UUID conforms to the standard UUID format, version 7 This contains significant
randomness (74 bits worth per millisecond.) The value in the
string is a binary value, so cannot be displayed on the
screen. The primary advantage of this format is that the high
bytes are an increasing value, based on the time. So data will
be added to the end of the table, thus avoiding the clustering
problem. |
UUID8
UUID |
Version 8 of the UUID standard allows programs to create the
UUID in any form they like. Mandating only that two nibbles in
the UUID are set to specific values. The version of UUID
supported by the classes does not even do that. The UUID8
implemented in these classes simply populates the string with
a alpha-numeric random text value. This results in around 80
bits of randomness. Since it is Alphanumeric it can be
exported and imported (to JSON, Excel etc) without the need
for additional encoding or decoding. In addition since it is
not binary, it can be stored ina simple Char
or VarChar field in the database,
which makes sorting and searching easier for the database
engine. |
To set a field in the dictionary;
- Create a String(16), (or String(36)
)typically (but not necessarily) called GUID.
(Do not use a CSTRING
field type here.)
- Set the External Name of the field name, adding one of the
following attributes; |
UUID4 | , | UUID7 | , | UUID8 | .
Setting the attribute to UUID4 or UUID7 implicitly makes the field |
BINARY | (You do not need to add the attribute here.)
- IMPORTANT If you are using UUID4
or UUID7 then any related
field, in another table, needs to have the attribute |
BINARY |. That field won't have the UUID
attribute (because it is populated by the parent field and so
should not be generated automatically here) but since the string
can contain binary characters it should be labelled as |
BINARY |. This is not necessary for UUID8
because UUID8 values are text, not
binary.
When adding new records simply clear the
GUID
field. The driver will populate a value in the field for you when
ADD or
APPEND is
called. If you wish you can prepopulate the field with a value, and
that value will then be used during the
ADD
or
APPEND.
The following code generates a
UUID
version 7, and saves it in the
Cus:Guid
field.
Cus:Guid = Customers{prop:uuid7}
Note 1: Guids are
often displayed as a 36 character Hex-Encoded string. However this is
just a display convention - the actual data is stored in 16 bytes.
Note 2: StringTheory has several methods for encoding
Binary values as text. Including
ToHex,
Base64Encode and
UUEncode.
Windows GUIDs
As mentioned above
UUID values are a 16
character binary string. This format is compact and efficient for
databases, but less efficient when displayed on the screen since the
UUID value is binary, and binary strings do
not usually display well. For this reason, on the Windows platform,
UUID's are usually displayed as a 36
character hex-encoded string, with four hyphen separators.
The object based drivers are aware of this, and contain a built-in
transformation - allowing the string declared in the dictionary to be
a
string(36) and the value in the
database to
remain as a 16 byte binary value.
This behavior is automatic IF the field declared in the dictionary
(and marked with one of the
UUID
attributes) is a
String(36). If you
don't want, or need, this functionality then declare it as a
String(16) instead.
ADD and APPEND
Using traditional drivers, the whole record is inserted when a
ADD
or
APPEND is done. The object based, SQL
drivers support
prop:Project.
This means that only the fields listed in
prop:Project
will be written to the database when an
ADD
or
APPEND is called. This can be faster if
only a few fields in the row are being set during the write.
In the context of an
ADD and
APPEND
the Projected fields are the fields being projected
into
the server from your program.
If supported by the driver, when a
ADD is
done, all server-side fields (identity, and
read
only fields as well as any not-projected fields which have default
values) in the record are reset to their current server-side value.
APPEND does not read the server side values, so
is slightly faster if the server-side values are not needed.
See also
PUT.
ADDRELATION
AddRelation(File pChildFile, File pParentFile, Long
pOnUpdate, Long pOnDelete, *? pChildField1, *? pParentField1, <*?
pChildField2>, <*? pParentField2>,...)
The
ADDRELATION command informs the
FILE of a relationship to a parent file. Relationships are always
defined in the child file, not in the parent file. In other words all
relationships are defined as Many-To-One (Many child rows to one parent
row.)
| Parameter |
Description |
| pChildFile |
The relationship is always defined form the child tables point
of view. The child requires that the parent row exists. Many
child rows can point to the same parent rows. (In some cases
only 1, or indeed no, child rows point to a specific parent
row.) |
| pParentFile |
the parent table in the relationship. |
| pOnUpdate |
The action to take if the parent field value changes. In other
words if the parent field (say CustomerId) changes, then all the
CustomerId fields in the children can have an action. This
action is one of driver:cascade, driver:clear, driver:restrict,
driver:NoAction. |
| pOnDelete |
The action to take if the parent row is deleted. In other
words if the parent row is deleted then all the children rows
can have an action. This action is one of driver:cascade,
driver:clear, driver:restrict,
driver:NoAction. |
| pChildField1 |
The field in the child, which matches the field in the parent
(defined in the next parameter). These fields must be of the
same type. |
| pParentField1 |
The field in the parent which matches the field in the child
(defined in the previous parameter.) |
| ... |
Up to 10 pairs of fields are supported. |
Some databases (specifically SQLite) require that for best performance
the call to
ADDRELATION is done before the
call to
CREATE. In all cases it is
ideal to add relationships early in the design life cycle, before the
tables have been created, or data has been added. Adding relationships
to existing databases can fail if the data in the tables contains values
that would violate the relationships.
If relationships have been defined in this way, and
CREATE
is called on the child table, then the existence of the parent table
will be checked, and it will also (if necessary) be created. Databases
require that parent tables are created (or exist) before child tables
can be created.
When the Update or Delete action is set to
driver:Clear,
this does not mean that strings are set blank, or that numbers go to
zero. It means the value of the field will be set to
NULL.
If the field itself is set as
NOT NULL then an
error might be generated when the table is created, or (more likely)
you'll get an error when deleting the parent record. Deleting the parent
record, would result in NULL being assigned to the field, but since the
field is defined as NOT NULL, this fails, which in turn causes the
DELETE to fail.
For more on relationships in general see
Relationships.
BINDFIELD
BindField(FILE pFile,String pName, *Long pField)
BindField(FILE pFile,String pName,
*Real pField)
BindField(FILE pFile,String pName, *String pField)
BindField(FILE pFile,String pName, *Cstring pField)
BindField(VIEW pView,String pName, *Long
pField)
BindField(VIEW pView,String pName, *Real
pField)
BindField(VIEW pView,String
pName, *String pField)
BindField(VIEW pView,String
pName, *Cstring pField)
| Parameter |
Type |
Description |
| pFile / pView |
File / View |
The label of the FILE or VIEW to which the field is being bound. |
| pName |
String |
The name of the field being bound. This should be the name as
it will appear in the SQL result set. |
| pField |
Long, Real, String, CString |
A local variable, one of a Long, real, String or Cstring to
hold the result. When the record buffer is updated (on a call to
NEXT, PREVIOUS) the local variable will also be updated. |
Using traditional drivers, when looping through a
FILE
or
VIEW, fields being returned are placed
into the file record buffer.
However when accessing SQL databases it's possible to
SELECT
values which are not in the file record. These might be calculated
values, or totals or whatever.
For example using
prop:Project
a specific set of fields could be set to be retrieved by a
SET
/
NEXT loop. For example;
Customers{prop:Project} = 'Cus:CustomerCode,COUNT(*)
AS CustomerRank'
In this case two fields are being projected, the normal field
Cus:CustomerCode
and also a calculated value which is placed into a field called
CustomerRank.
If the table contains a field (in this example
Cus:CustomerRank)
which can hold the calculated value then that works well. However the
table may not contain such a (spare) field. In this situation using
BindField to add an external value to hold the
result is necessary.
For example
CustomerRank Long
Code
BindField(Customers,'CustomerRank',CustomerRank)
Then that field can be used to hold the return value in the result set.
In this case, when a record is read (by the
NEXT
or
PREVIOUS command) the local variable
CustomerRank will be populated with the value
in the result set. In effect the local variable has be "bound" into the
record for the duration of the loop.
Only fields of type
Long,
Real,
String or
Cstring
can be bound in this way.
Fields bound in this way can be used in the
prop:Order
property. They are not applicable to the
prop:Filter
property.
Local variables used in
prop:Filter should
be bound, as normal, using the Clarion
BIND
command. Conversely local variables bound using
BIND
have no meaning in a
prop:Project or
prop:Order property. In other words
BIND
and
BINDFIELD are different, and should not
be conflated together.
The binding is cleared when the
FILE or
VIEW is closed, or when
ClearProps
is called.
BINDFIELD is supported in the commands
NEXT,
PREVIOUS,
GET ,
GETRANDOM
and
REGET.
Blobs
Blobs are a source of common problems in Clarion, because they don't
behave like regular fields and variables. The goal of this section in
the document is to clarify behavior, and to highlight places where the
behavior may vary from the behavior of traditional file drivers.
In the table below
aBlob is a blob
reference, assigned as
aBlob &= table{prop:blob,-n}
fil:blob is the blob as declared in the
dictionary
| Code |
Traditional Driver |
Object Based Driver |
s=aBlob{prop:Size}
s=fil:Blob{prop:size} |
The current size of the blob. |
| s=table{prop:size,-n} |
blank |
The current size of the blob |
{prop:Size} = x
|
Prop:Size
does not need to be called before setting the blob contents.
This behavior is not changing, however deviates from the blob
documentation. |
{prop:Size} = x
|
When setting the Blob Size, new space is not cleared. |
New space is cleared to nulls. |
s=aBlob{prop:value}
s=fil:Blob{prop:value}
s=table{prop:value,-n} |
The current contents of the blob are returned. |
aBlob[0:3] = 'abcd'
fil:Blob[0:3] = 'abcd' |
Assigns text into the blob. Makes the
blob bigger if necessary. |
aBlob{prop:value} = 'abcd'
fil:Blob{prop:value} = 'abcd'
table{prop:value,-n}
= 'abcd'
|
Assigns the text into the blob.
Replaces the existing value completely and adjusts the size up,
or down, as necessary. |
| CLEAR(FILE) |
|
|
BUFFER
The traditional drivers make use of a BUFFER command which allows the
FILE or VIEW to buffer some number of records, either some already read
or some number of future records. This can have a dramatic impact on
performance, although the command is only used in the ABC ViewManager,
in the shipping classes and templates.
BYTES64
Bytes64(FILE pFile),BIGINT
The
BYTES command returns a
LONG,
which limits the number to around 2.1 billion. This may not be enough
(for example the DOS2 driver supports files greater than 2 Gigabytes).
The
BYTES64 command is a new command which
returns a
BIGINT variable (at the time of
writing this, this is a
REAL.)
If this command is called against a traditional driver then the regular
BYTES command is called, and a value less
than 2.148 billion is returned.
See also
Pointer64 and
Records64.
CLEAR
The
CLEAR(File) command clears the record
buffer. It It is recommended that this form of CLEAR is called rather
than the
CLEAR(fil:record) form.
Using
CLEAR(File) clears the record, the
blobs and also clears all the properties by calling
ClearProps (clears
prop:Filter
et al) which are supported for many of the methods.
Note - If calling the method in the object directly then use the method
name
ClearFile instead of the word
Clear.
CLEARPROPS
CLEARPROPS(FILE pFile)
The
CLEARPROPS command clears all the
properties (
prop:Filter et
al) which are supported for many of the methods.
It does not clear the record buffer.
It does not clear
prop:Sql.
It is recommended to call this command before
COPY,
MERGE,
SET,
RESET,
This command also sets the
Error code back to
No
Error (0).
COMMIT
COMMIT(FILE pFile)
If
LOGOUTCONNECTION is
used to start a transaction, then
COMMIT
MUST be called with the primary table. Calling
COMMIT
with no parameters in this situation will cause a GPF.
For more information on commit see
Transactions.
Context
There's a popular saying;
"If you get get five database administrators in a
room, and ask a SQL question, you'll get ten opinions."
The primary reason for this is that SQL databases operate in many
different contexts. And almost all SQL advice is context specific.
Things that are optimal in one context may be sub-optimal in another.
When reading, or getting, advice, it is important for the expert to
understand your context, and to be offering advice that matches your
context. Be careful of implementing advice which may be good in one
context, but is not good in yours. Here are some broad, common,
contexts. Your context may overlap some of these, or may be quite
different.
- You make a commercial program, distributed to many customers, who
each make use of a SQL database to store the data your program
creates. They don't have a Database Administrator (DBA) on staff -
you are expected to install, and maintain, the database for them.
- You work internally at an organization. They have one, or many,
databases internally. Your program interacts with those databases
adding, and retrieving information. You have little, or no, input to
the organization and maintenance of the data.
- You make a web application where data is stored locally (or "in
the cloud") and is administered by your staff. The database may
support one, or many, web servers at the same time.
- Most of the work the program does is reading data and/or
generating reports. Performance should be optimized for speedy
reads. Or perhaps most of the work is gathering data, perhaps from
automated IoT devices. Performance should be optimized for speedy
writes. Or perhaps it's a combination of both, some tables should
have fast writes, others fast reads.
- Your tables are small (thousands of rows), or your tables are
large (millions, or billions, of rows).
These are just some of the context considerations. How you design your
database, and how you deploy your database, will depend largely on your
context. When advice is taken out of context then "myths" evolve.
For example, it's a common Clarion SQL myth that "tables should not have
keys declared in the dictionary. The database will determine the best
keys based on usage, and will create them as necessary." This advice
varies between sort-of-true, and not-true depending on your context.
Firstly it's important to understand that keys improve read performance,
at the cost of some write performance, and some disk space. Regardless
of context, keys are important for fast read performance. What can vary
greatly is how those keys are determined, created, and maintained. If a
DBA is overseeing the database this job generally falls to them. If
there is no DBA this job generally falls to the program. The SQL
database does not do it "by itself".
When it comes to database maintenance, your program runs in one of two
contexts;
- Your program "owns" the data. It creates the tables, looks after
them, updates them, alters them, and so on. In this scenario the
dictionary is the source of "truth" - as you update the dictionary
so the database updates as well. If you are coming from an ISAM
(TPS) background this is the context you are used to operating in.
In this context if you add a table in the dictionary it can be added
to the database. If you add keys to your FILE
structures, they can be created in the database and so on.
- Your program accesses the database, but does not own it. This is a
common situation when the program is merely accessing a database
that is updated and maintained by either a human (a DBA) or another
program. In this context the program adapts to the database, not the
other way around. The database itself is the source of truth.
In this context the FILE structure,
and keys can be a subset of what actually exist in the
database. You can define additional keys in the dictionary, but
these are not propagated into the database itself.
It is useful for the program, and by extension the driver, to know which
of these two contexts are in play. The driver can do work to ensure that
the database structure is kept up to date, however for backwards
compatibility reasons this feature is OFF by default. To turn this
feature on set the
Driver Option, either at
the global level or at the table level. It can also be set globally, and
overridden at the table level if desired.
The
OPEN command checks that the Dictionary
field set is a subset of the Database field set (ie fields can exist in
the database that are not in your dictionary.) It checks that
Primary and Unique keys declared in the dictionary are declared in the
database. Other than that, extra keys in the dictionary or in the
database are ignored.
CONNECT
Connect(FILE pFile, LONG pOptions=0)
The
CONNECT command is not supported by
traditional SQL drivers. It is new to object based drivers.
The object based driver typically connects to the database when a
command requires it. So the
CONNECT command
is not usually called in code. However there are situations where a
connection to the database is desirable, even if the code is not ready
yet to perform a command. In this case the
CONNECT
command can be called.
The
CONNECT command can take an
Options
parameter, which is File Driver dependent. See the specific driver
documentation on what can be used here.
See also
DISCONNECT.
Connection Pooling
Inside the driver, at a fairly low level, the driver has to "connect" to
the database. This is known as a connection. Typically the programmer
does not need to understand how this happens, or how it works, it just
happens.
For various performance and resource reasons multiple driver objects can
share the same same connection. Again this is done automatically by the
driver, and in most cases there's no reason to modify this behavior. In
some cases though it can be useful to prevent objects from sharing the
same connection, and so some control over the pool is offered.
Firstly it's worth understanding when connections will be pooled, and
when new connections will be created;
Connections are only pooled if the objects are on the same thread, and
if their owner strings are identical. Even a small change in the owner
string will trigger a separate connection, so that is one way to control
whether tables will share a connection or not. In addition to the owner,
a new property,
prop:Pool, also exists.
Connections will only be shared if the tables have the same
prop:Pool
number. The default value for
prop:Pool is
0.
Therefore, if there are two tables, and it is desirable for them not to
share a connection, then the
prop:Pool for
one of them can be set to a non-zero number.
prop:Pool is automatically set back to 0 when
CLOSE
is called
Pooling also comes into play when transactions are involved. After a
transaction is started, then all writes, to all tables sharing that
connection, are part of the transaction (whether the file was listed in
the
LOGOUT command or not.) Therefore by
setting
prop:Pool to a common (non-zero)
value before the tables are opened, the tables can be "isolated" from
the other tables. In most cases there will be no gain from this change,
but in some cases, where some writes must occur outside of the
transaction (while a transaction is underway) this property can be
useful.
If a table is open, and you wish it to change to a new connection, then
set
prop:Pool, and call
CONNECT.
It should be noted that if the file is open when this happens, it will
be closed. For this reason it is best done before the file is OPENed.
If for some reason it's desirable to get the actual connection handle,
this can be retrieved using
prop:Hdbc.
COPY
COPY(FILE
pFile, STRING pNewFileName)
The
COPY command is not supported by
traditional SQL drivers. Object based drivers allow the
COPY
command to be implemented by the driver.
The
COPY statement duplicates a table in
the database. The table to copy can be open or closed. If the new table
name is the same as the current table then the
COPY
statement is ignored.
If the destination table already exists, then
it
is DROPPED first (and all existing information in the table is
lost). If you want to merge (not copy) the table, then see the
MERGE command.
Note that this only copies the table structure, and data. It
does
not copy indexes, relationships, triggers and so on.
All supported properties should be set (or cleared)
before the call to COPY
.
Calling CLEAR(file) or CLEARPROPS(file)
will clear all the properties.
The
COPY command supports
prop:Project ,
prop:Filter
,
prop:SQLFilter,
prop:Limit,
prop:Offset. If a subset of the fields are
projected, then the destination table will set the remainder of the
fields to be their default value, which may be
NULL.
The destination table when created will include all the columns present
in the source table, regardless of whether all the columns are projected
or not.
All the properties are cleared at the end of the call to
COPY,
regardless of success or failure.
CREATE
The
CREATE command is seldom used with
traditional SQL drivers because it lacks the flexibility and power most
people require when creating SQL tables in their database. In an effort
to make creating tables simpler, the
CREATE
in object based drivers is more powerful, and safer to use.
| Traditional SQL Driver |
Object Based SQL Driver |
| Does an implicit DROP TABLE before
the CREATE TABLE statement. This
conforms to the behavior of ISAM drivers, however is problematic
if the OPEN command incorrectly
returns a File Not Found (02) error.
This problem has lead to many tables in databases being
inadvertently dropped. |
Does not do a DROP TABLE . If the
table already exists then CREATE
fails with a File System Error (90)
.
If the table should be dropped then use the REMOVE
command before calling CREATE. |
| No control over how the field is created in SQL. |
Supports Extended Name Attributes
including SQLTYPE, COLLATE, BINARY, NOTNULL, DEFAULT,
ISIDENTITY, UUID and more. |
| Does not support Server Side constraints |
Supports server-side constraints using Extended
Name Attributes. |
| Does not support Relationships |
Relationships can be defined using the ADDRELATION command. If this is done before
CREATE then foreign keys, and
foreign key constrains (and actions) are created. |
Custom Properties
Object based drivers support the setting, and retrieval of
custom
properties.This allows you to name, and set a property and
associate it with the table. The property is valid for the life of the
object (ie is limited to the thread if the file is threaded.)
To set a property the syntax is;
file{'someproperty',index} = 'whatever'
The
index parameter is optional, if omitted
it defaults to 0.
To retrieve a property the syntax is
something = file{'someproperty',index}
again, the index parameter is optional and defaults to 0.
The property name is not case sensitive. If the name is a number then
care should be taken not to overlap with property numbers defined in
property.clw or
CWDriver.inc.
(For best future-compatibility do not use a number as the name.)
Custom properties are useful because they are attached to the object,
meaning that they are in scope in any place where the FILE is in scope.
So they reduce the need for parameters (attached to the file) to be
passed.
DELETE
Delete(FILE pFile)
Delete(FILE pFile,String pFilter)
The traditional command
DELETE(File) works
as it always has. It deletes the most-recently-read record (which has
been retried using
GET,
REGET,
NEXT or
PREVIOUS.
Object based drivers extend the command allowing a filter statement to
be passed to the command. This allows multiple records to be deleted at
the same time.
If supported by the driver,
prop:Affected
is set with the number of rows which were deleted.
To delete all the records in a table see the
EMPTY
command.
Note: OVER and ARRAY fields cannot be used in
the filter.
DISCONNECT
Disconnect(FILE pFile)
The
DISCONNECT command is not supported by
traditional SQL drivers. Traditional drivers do support
file{prop:Disconnect} however in
traditional drivers that command applies to
all
FILE structures, and across all threads,
for that driver. The
DISCONNECT command
does not behave like that.
In object based drivers, calling
CLOSE on a
table disconnects the
FILE from the
connection manager. This won't necessarily close the connection; that
will only be done when the last table using the connection is closed.
Also, some commands can be executed when the file is in a closed state -
and these will necessarily make a connection. Therefore, if you use a
command, and the table is not open, you may want to call
CLOSE
or
DISCONNECT to release remove the table
from the connection.
It is not absolutely necessary to call either
CLOSE
or
DISCONNECT. If a File Object goes out of
scope
[1], then it will
automatically be CLOSEd and DISCONNECTed for you.
Note 1: The file
object goes out of scope when the FILE structure goes out of scope. The
FILE structure goes out of scope as follows;
a) If the FILE is declared locally in a procedure, then it goes out of
scope when the procedure ends.
b) If the FILE is declared locally in a MODULE then it behaves as if it
is global.
c) If the FILE is declared globally, with the ,THREAD attribute, then
it'll go out of scope when the thread ends.
d) If the FILE is declared globally, without the ,THREAD attribute, then
it'll go out of scope when the program ends.
Prop:Disconnect
Traditional drivers support using
prop:Disconnect
to close the connections to a database. This is a fairly dangerous
command as it applies across all threads, and to all tables using the
same database. It is primarily used when the developer is sure all
tables are closed (on all threads) and is used to preempt program hangs
caused by server-side disconnections
[1].
In object based drivers, connections are handled differently.
[2]
In this situation connections are threaded (ie each thread makes use of
its own connection) and the
DISCONNECT
command allows a specific table to disconnect from the database, however
the actual database connection is not disconnected until the last table
object has disconnected.
For this reason, in object based drivers, the call to
prop:Disconnect
has been deprecated. If a call to
prop:Disconnect
is made, then no action will be taken.
Note 1: The
main use of
prop:Disconnect is to prevent
program hangs caused by server-side disconnections. These occasional
hangs are a serious inconvenience to programs using traditional SQL
drivers. Since object based drivers use a different connection
management approach, which do not suffer from these hangs, this solution
is not necessary anymore.
Note 2:
This next paragraph is technical, and of no interest to most
programmers. Feel free to ignore.
There is a new class inside the class called the
DriverConnectionPoolClass.
A threaded instance of this class exists inside the driver, so each
thread has its own connections. Different tables connecting to the same
database, with the same database options, share the same connection.
When the last table disconnects from the database then the connection is
automatically closed.
Prop:Distinct
Before calling the
SET command, the
prop:Distinct property can be set. This will add
the
DISTINCT clause to the SQL
SELECT
statement. This tells the server to only return unique records,
duplicate records are not included in the result set. Object based ISAM
drivers may, or may not, implement this property so check driver
documentation.
customers{prop:Distinct} = true
Set(Cus:NameKey)
Loop ...
This property
is not reset to false except
on a
OPEN ,
CLOSE
,
CLEAR and
CLEARPROPS.
If you wish to re-use the
FILE with this
property off, then you must set it to
false
(if you previously set it to
true).
It is not necessary to set
prop:Paging to
false when
prop:Distinct or
prop:NotDistinct are used, then paging is
automatically disabled.
Rows in the table are unique. So retrieving all the fields in a row will
mean that
prop:Distinct will have no
effect (since all the rows will be fetched.) Therefore, in the
FILE
context,
prop:Distinct is only useful if
you are using
prop:Project to retrieve
only a subset of columns, and those columns do not include all the
fields in any unique index.
Since there is no row-identifying field in the result set, the results
of this request are automatically set to be read only (so no
PUT
calls are allowed.)
Expanding the example above;
customers{prop:Distinct} = true
customers{prop:Project} = 'Cus:Lastname,Cus:Age'
Set(Cus:NameKey)
Loop ...
This will return a list of names and ages, but without any duplicates.
If the
prop:Distinct is set, but the
prop:Project is not set, then the
prop:Distinct
is ignored and the implicit
prop:Readonly
is not applied.
See also
prop:NotDistinct.
Driver Options String
Clarion traditional drivers support two ways to send commands to the
driver. The first is via the
Driver Options
String - which is typically set in the dictionary. While this
can be a variable (as it is with the Owner and Full Path Names) is is
usually coded to a fixed value by developers.
In addition to this is the
SEND
command, which allows commands to be passed on an ad-hoc basis.
One disadvantage of the dictionary driver string is that it is set on a
table-by-table basis. Which means that for some settings the driver
string has to be duplicated on every table (or, at the very least, a
global variable has to be set for each table.)
Object based drivers (in Clarion 10 and later) introduce an additional
way
[1] to set driver options;
by setting a user-defined system property. The system property is named
Driver:xxx where
xxx
is the name of the driver. So, for example, the SQLite2 driver uses the
property
system{'Driver:SQLite2'}.
Example
System{'Driver:SQLite2'} = '/master=Dict'
System properties are
not threaded. Thus
you can set the property once, presumably when the program starts, and
it will then apply for all tables, and on all threads, using that file
driver.
Of course the regular file driver string remains. It is evaluated after
the system property. If the same setting is in the system property and
the file driver string, then the value in the file driver string will be
used. The file driver string can still be set on a table by table basis,
and if a variable is used, then the variable can be threaded.
The
SEND command can be used to send
additional commands and instructions to the driver (as has always been
the case). This naturally overrides the existing value in the
setting,however care should be taken with regard to when the
SEND
command is used. The system property and file driver string are
evaluated at specific points in the code
[2],
and this effectively "resets" the properties mentioned in the system
property and file driver string back to their original setting. And
SEND commands on these properties will be reset.
Other
SEND commands will not be reset.
The exact options available to the developer depend on the driver. See
the driver documentation for specific options and values.
While the driver strings and
SEND command
are useful to set behavior in the driver, it's also desirable to set
properties in the database engine itself. These options are very
database specific, and may even vary between one version of a database
and the next. For the options available to your database see the
database documentation. (For example, in SQLite the documentation is at
https://www.sqlite.org/pragma.html
.)
The object based drivers support these options at all three levels - in
the system property, the file driver string, and using the
SEND
command.
Example - system property;
System{'Driver:SQLite2'} = '/AUTOINC=TRUE
/pragma_cell_size_check=true /UNSAFE=Stop'
Example - file driver string;
Customer
FILE,DRIVER('SQLite2','/AUTOINC=TRUE
/pragma_cell_size_check=true')
Example,
SEND command;
SEND(Customer,'/AUTOINC=TRUE
/pragma_cell_size_check=true')
Some database commands are not instructions to set a setting, but rather
a request to get the current value of a setting. For example in SQLite
the command
PRAGMA data_version; is a request
to get the current value in
data_version.
Including this in a driver string doesn't make a lot of sense (since it
would happen on every
OPEN) however it is
useful to use with the
SEND command. The
results of the call are returned by the
SEND
command.
d = SEND(Customers,'/pragma_data_version')
Common Driver Options
| Option |
Default Value |
Description |
| /ALLOWDETAILS |
|
|
| /EXPLAIN=true |
false |
When this is on then ALL SET /
NEXT loops for the FILE,
and all VIEW structures, where
this is the primary file, will trigger a Query
Plan Explanation. Use this while developing to determine
where additional keys are needed to improve performance. |
| /LOGFILENAME |
|
|
| /LOGMESSAGE |
|
|
/MASTER=n
|
database
|
n is one of ;
database : the database is the
source of truth. Field and key changes in the dictionary will
not be propagated to the database. (The program will Error 47
if a field is in the FILE structure, but not in the database)
dict : The dictionary is the
source of truth. The database will be updated with new fields
and keys as they are added to the dictionary. Old fields and
keys will not be removed.
dictfield : Fields added to the
dictionary will be added to the database table. Keys added in
the dictionary will not be added to the table.
dictkey : Keys added to the
dictionary will be added to the database table. Fields added
to the dictionary will not be added to the table. (The program
will Error 47 if a field is in the FILE structure, but not in
the database).
strictdict : As for dict,
but fields and keys removed from the dictionary will be
removed from the database.
|
| /TURBOSQL=n |
false |
n is true or false.
When true no file structure
check is done when the table opens or is used. |
| /UNSAFE=n |
false |
n is one of;
false : Unsafe use of prop:Sql
is not allowed.
true : Unsafe use of prop:Sql
is allowed
stop : Unsafe use of prop:Sql
will trigger a STOP, and then the
prop:Sql command will proceed. |
Note 1: This
is only available in Clarion 10.11975 or later. User system properties
were introduced at this time.
Note 2: The
system property and driver string are parsed and processed at two points
in the life-cycle of the
FILE structure;
- When the object is initialized. This happens when the file is
"touched" in any way (by any command or property) for the first time
(on the thread, if the FILE is
threaded.) In other words, this can be thought of as happening once
per thread, when the FILE is first
used on the thread.
- When the file is opened. Indeed every time it is opened. This has
the potential to reset options set with SEND
which occurred before the OPEN.
DUPLICATE
The
DUPLICATE command has been
extended to support non-unique keys.
From the Clarion documentation;
The DUPLICATE procedure returns a non-zero value
(true) if writing the current record to the data file would post the
"Creates Duplicate Key" error.
The current behavior remains, but with a small extension;
If the
DUPLICATE(key) form of the command
is called
with a non-unique key (ie a key
with the
DUP attribute) the command will
return
true if a "matching" record already
exists. However writing to the disk in this case will not cause an
Error.
Calling it in the form
Duplicate(FILE) only tests unique keys. The above extension to
the behavior only applies when called with a non-unique-key as the
parameter.
EMPTY
Traditional drivers require that the file be open in Exclusive mode to
use the
EMPTY command. Object based drivers
do not have the same limitation. You can empty the table regardless of
the open mode.
EMPTY removes all the records from the
table. To remove one, or more than one record, see the
DELETE command.
Note: If you are using
server-side
relationships then the use of
EMPTY
can be damaging. If called when relationships are active then the
defined
On Delete behavior may be executed by
the database. This includes cascading deletes, clearing of child fields,
and so on. Indeed the call may fail for one, or more, rows if there is a
Restrict limitation placed on deleting the row.
This may not be intended behavior.
If the call to
EMPTY is part of a wider
process (to drop the data, and then re-populate it) then strong
consideration should be given to suspending relational updates (using
SUSPENDRELATIONS). For
more information see the section on
Relationships.
A similar issue exists when using the
REMOVE
command.
Exclude Empty Keys
A lot of programs get to SQL via an ISAM route. They start off as
Topspeed files, and at some point the system changes to being SQL.
Converting programs like this can be challenging because some features
of Topspeed are not supported by the traditional SQL drivers. One of
these features is the creation of Keys that make use of the option
Exclude Empty Keys.
Unlike
Arrays and
Memos
(two other common issues when converting from Topspeed) understanding
the functionality provided by the key, in this application, is critical
in duplicating that functionality in SQL. Different programs use the key
to provide different functionality.
Compact sort ordering
The original use of the key was to create compact sort orders on the
disk. By removing the blank values from the key, the key itself would
take up less space on the disk. In the modern era, disk space became
plentiful, and cheap, so this use case is not used much anymore (other
than as an historical artifact in the program.) This use-case is less
applicable to SQL.
If this was the reason for the option being set, then it can simply be
removed from the dictionary. The key can then be created as normal, and
can include all values. The program can simply be used as-is,
however it may be necessary to add, or alter, the
prop:Filter
when using the key to exclude rows with zero or blank values.
Not-Blank-Unique
A second common use case pairs the
Exclude Empty
Keys option with the
Require Unique Value
option. In this case the Tospeed driver enforces uniqueness on the field
IF a value is entered. < a href="#excludeemptykeysnote1">
[1]
So, for example, a phone number must be unique (if one is entered) but
can be left blank if unknown (and no uniqueness check is done if the
field is blank.)
This matches the SQL concept of a
Partial Index.
Most databases
[2]
support Partial Indexes, although some drivers may use an alternative
strategy to achieve the effect. If the program is using the key in this
way, then it can simply carry on using it like this, as long as the
index is created correctly. (using the
CREATE
statement will create it correctly.)
Note 1:
It's worth explicitly pointing out that if the
KEY
is not Case Sensitive, then the uniqueness is not case sensitive.
Meaning that if a product
a/123 exists
then you cannot add
A/123.
Note 2:
Partial Indexes are not supported in MySql or Maria / DB. Firebird
requires version 4.0 or later. SQLite requires version 3.8.0 or later.
MS SQL Server supports them (since SQL Server 2008) , but they are
called Filtered Indexes, and they cannot be unique. Where not supported
alternative techniques can be employed by the driver to achieve the same
effect.
EXISTS
Exists(FILE pFile),LONG
Exists(FILE pFile,STRING pTableName),LONG
The Clarion
EXISTS command allows the
program to query the disk system to see if a file exists. The object
based drivers extend this command
to check the database to
see if the table exists in the database or not.
The
pFile parameter does not need to be
open or closed (either is fine.) If this is a SQL driver, the connection
string (
OWNER) of the table must be set
before calling this command.
If the second parameter is omitted, or blank, then the
pFile table is checked. If
the second parameter is set then that table name (in the same database
as
pFile) is checked. The
pTableName
table does not need to match the structure of
pFile.
The function returns
true or
false.
If the database request fails then
Errorcode()
is set to one of
BadNameErr (45),
NoDriverSupport (80), or
BadOwnerErr
(122) and the reply value is
false.
EXCEPT
Except(FILE pFile)
Except(FILE pFile,STRING pFilter)
The
EXCEPT command is used when creating
Subsets. It allows results to be removed from the
current result set.
It typically (although does not have to) follows a call to
SUBSET.
Example
Create a list of customer email addresses, but exclude addresses listed
in the Blocked table.
Set(Cus:NameKey)
Subset(Customer,Cus:Email)
Subset(Blocked,Blo:Email)
Except(Blocked)
Example
Create a list of Invoices for this month, for which a Credit Note has
not been issued
Set(Inv:NumberKey)
Subset(CreditNote,'Cre:InvoiceId')
Union(Invoices,'Month(inv:date) = ' & Month(Today() & ' AND
Year(inv:date) = ' & Year(Today())
Except(Creditnote)
Other related commands are
UNION,
UNIONALL and
INTERSECT.
EXPORT
Export(FILE pFile,String pDosFileName,Long
pFormat=driver:csv,Long pOptions=0)
The
EXPORT command is used to
create a text file from the database records. To import data from a text
file see the
IMPORT command.
| Parameter |
Type |
Description |
| pFile |
FILE |
The label of the FILE to export. |
| pDosFileName |
STRING |
The full path and name of the file to save the export to. Note
that this command should assume that the import is done natively
on the database server, and the server needs access to write to
the actual CSV file. So this (full path name) should be valid
from the server-side. |
| pFormat |
LONG |
Defaults to Driver:CSV. This
parameter determines the format of the output. |
| pOptions |
LONG |
Default to 0. Not all options are supported in all formats.
Not all options are supported by all drivers. Consult driver
documentation for details. Can be a combination of the following
(added together);
driver:append - The data is
appended to an existing file.
driver:header - A header line is
added as the first line in the CSV file. If driver:append
is set, and the file is not blank, then the header is not added.
In other words, the header will only be added as the first line
in the file. |
The
EXPORT is primed using a
SET
command. After the
SET, instead of doing a
NEXT,
EXPORT is
called instead. All the properties relevant to the
SET
command (including filtering and ordering) are relevant here.
For the
Driver:CSV format the following
additional properties are also in play;
| Property |
Default Value |
Description |
| prop:Separator |
',' |
The separator to use between fields in the row. The property
can be up to 10 characters long. Null characters are not
permitted. |
| prop:LineEndings |
'<13,10>' |
The separator to use between rows. Default value is
Carriage-Return LineFeed. The property can be up to 10
characters long. Null characters are not permitted. |
| prop:AlwaysQuote |
false |
If set to true, then string fields are always quoted, even if
they do not contain reserved characters.
If false then only strings that contain reserved characters (the
separator, line endings, and double quote) are quoted. |
| prop:NullString |
'^null^' |
Null values are written to the text file using this
placeholder. The property can be up to 10 characters long. Null
characters are not permitted. Null strings are NOT affected by
the prop:AlwaysQuote property. |
Example
Create a list of customer email addresses, but exclude addresses listed
in the Blocked table.
Set(Cus:NameKey)
Subset(Customer,Cus:Email)
Subset(Blocked,Blo:Email)
Except(Blocked)
Example
Create a list of Invoices for this month, for which a Credit Note has
not been issued
Set(Inv:NumberKey)
Subset(CreditNote,'Cre:InvoiceId')
Union(Invoices,'Month(inv:date) = ' & Month(Today() & ' AND
Year(inv:date) = ' & Year(Today())
Except(Creditnote)
Other related commands are
UNION,
UNIONALL and
INTERSECT.
prop:Explain (SQL)
The
SET command allows you to create a
result set from the data in the database, and then loop through that
result set using the
NEXT and
PREVIOUS
commands.
Typically a
KEY (or
INDEX)
is used to define the sort order in the
SET.
However with the addition of the
prop:filter
and
prop:order properties
to the
SET command, it is possible for the
SET command to become quite complex.
Ultimately the server will decide which indexes to use, or whether a
full table scan is required. It can be useful to know which (if any)
index it ended up using, and if a full table scan was needed, perhaps
another index on the table could improve performance.
Explain mode is triggered by setting
prop:Explain
Customers(prop:Explain}
or
Customers(prop:Explain} = true
This property is ONLY applied to the next
SET
command, and is then automatically cleared. The
SET
command will still function, so the use of this property should not
affect the behavior of the actual code.
Obviously this property does some work, and so will slow down the query
a little bit. Thus it should be used for development only, and not
included in general code.
The format and destination of the output depends on the specific file
driver. Consult the driver documentation for information on the expected
results, where the results go, and the formatting of results.
See also:
Performance Tips
Extended Name Attributes
The External Name attribute of a field, as declared in the code (ie, as
set in the dictionary) is "visible" to the driver. For this reason extra
desired functionality for a specific field can be passed to the driver
in this way. The External name becomes a Pipe (
| )
separated list, which contains both the External Name and also
Extended
Attributes.
Clarion traditional drivers support some extended name attributes
already. These can be applied at the field level to specific fields in
the table declaration. This allows the developer more control over the
way the driver treats individual fields. It's especially useful for SQL
tables, although is used by some of the other drivers as well, where it
is appropriate to do so. For a more in-depth discussion of Extended Name
Attributes in general see
ClarionHub.
Note that for traditional drivers, the Attribute name is Case Sensitive,
and the spacing in the name attribute matters. In object based drivers
the attribute is not case sensitive, and extra white-space can be
included, or excluded, as you desire. Object based drivers support all
the traditional attributes, as well as some additional attributes.
For more information on the traditional attributes, the Clarion
Documentation is recommended.
| Attribute |
Support |
Applies |
Description |
| BINARY |
Traditional & Object Based |
FIELD, BLOB |
Specifies that the field will hold binary data. Can be used on
STRING fields. The driver will take
this into account when CREATE the
table. |
| CHECK |
Object Based |
FIELD |
See VALIDATE below. |
| CHECKFORNULL |
Traditional |
FIELD, BLOB |
In SQL, records with the NULL value
in the WHERE clause are not included
in the result set.
This switch alters WHERE clauses, so
that records with NULL fields are
included in the result set. |
| COLLATE |
Object Based |
FIELD, BLOB |
IN SQL when creating a table the COLLATE
attribute determines how the field is compared to other fields.
Are trailing spaces clipped? Is the field Case Sensitive? and so
on. |
DEFAULT=xxx
DEFAULT(xxx)
|
Object Based |
FIELD, BLOB |
When creating the table, specify that this field should be set
to this default value. Do not use quotes. Usually used in
conjunction with NOTNULL. Can be
set to a blank string using just DEFAULT=
or DEFAULT() with no value. |
| ISIDENTITY |
Object Based |
FIELD
|
Specifies that the field is a server-side auto-incrementing
field. There may be some restrictions on this based on the
specific driver, so check the driver documentation. The field
will not be included in ADD, APPEND, PUT,
UPSERT statements. |
NOTNULL
|
Object Based |
FIELD, BLOB |
When creating the table, specify that this field should be set
to NOT NULL as a constraint. |
| NOWHERE |
Traditional |
FIELD, BLOB |
Some back ends do not allow certain data types in a WHERE clause, but they fail to advise the
ODBC Accelerator Driver of this restriction. The NOWHERE
switch lets you manually advise of the restriction when WATCH
causes the driver to generate an error. |
| READONLY |
Traditional & Object Based |
FIELD, BLOB |
The field will not be included in ADD,
APPEND, PUT,
UPSERT statements. When ADD,
PUT or UPSERT
are called then readonly fields in the buffer are automatically
updated to reflect the database value. |
| REQ |
Object Based |
FIELD |
Equivalent to NOTNULL and <> 0 or <>
'' |
SELECTNAME=xxx
UPDATENAME=yyy
INSERTNAME=zzz |
Traditional |
FIELD, BLOB |
When Selecting, Updating, or Inserting, the specified name
will be used as the field name instead of the External Name, or
Label. Do not use quotes. |
SQLTYPE=xxx
SQLType(xxx)
|
Object Based |
FIELD, BLOB |
When creating the table, specify that this field should be set
to this type, regardless of the type Clarion would traditionally
use. Do not use quotes.
(Each driver also has a custom form of this equate, see
documentation for each driver.) |
| UUID4, UUID7,
UUID8 |
Object Based |
FIELD
|
These fields should contain a UUID value
when being added to the database. If the field is blank on an ADD or APPEND
then a UUID value will be generated into the field for you.
UUID's are an alternative to Auto
Numbering. |
> n, >=
n
< n, <=
n
<> n |
Object Based |
FIELD |
Used for server-side validation of
the field. Validation will be added when creating the table.
Ignored if the field is BOOLEAN. |
| VALIDATE(xxx) |
Object Based |
FIELD |
xxx is a SQL CHECK
expression which is included in the table declaration when CREATE is called. |
| WATCH |
Traditional |
FIELD, BLOB |
If set at the field level, then only
the fields with WATCH will be
checked when writing a record with a PUT. |
External Key Names
Most databases store key names external to the tables in the database.
In other words all the key names are stored internally in a table,
database wide, and thus the key names need to be unique.
Traditionally this means that Clarion developers have got into the habit
of putting unique external names against keys in the dictionary.
Typically (but not necessarily) by merging the prefix with the key name.
This, however, would be the wrong thing to do. It is wrong for two
reasons;
Firstly, it's unnecessary. The drivers automatically create a unique
name if one is not supplied. For traditional drivers it consists of
prefix_keylabel. So adding this as the external
name is redundant. Object based drivers use tablename_keyname.
[1]
Secondly, and more importantly, though it breaks
ALIAS
functionality when the database contains two tables of the same
structure.
An
ALIAS is a dictionary approach to
duplicating a table in the Clarion dictionary. It is primarily used for
two use cases; because multiple record buffers into the same table are
required, or because multiple tables with the same structure, (or the
same table in multiple databases) need to be accessed at the same time.
If the external key name is "fixed" then the
ALIAS
cannot
CREATE a clone of the table
because the key names will then be non-unique. Leaving the
External
Key Name blank will avoid this problem because then the prefix
of the
ALIAS will come into play (which
will then result in a unique key name.)
The
External Key Name is not in fact used
in program code anywhere. Code makes use of the key label, so the name
is irrelevant. Thus leaving the driver to generate unique names is the
best approach.
Setting the
External Key Name explicitly
in the dictionary has implications for the
CREATE
command (as mentioned above) as well as the
COPY
and
MERGE commands, which may
create "clones" of the table.
Note 1:
Object based drivers use the longer name because this allows the same
FILE in the program to create multiple tables
in the database which have the same structure, but different table
names. Since the same
FILE is used the
prefix would be common, thus if the prefix was used in the key name, the
key name would not be unique.
FROMQUEUE / TOQUEUE
FromQueue(FILE pFile, QUEUE pQueue)
ToQueue(FILE pFile, QUEUE pQueue)
There are situations where cloning a file structure as a local queue can
be advantageous. These new commands exist as a convenience to make it
somewhat easier to move data between queues and tables. While it is
possible to write short loops to move data between a queue and a table,
the loop can be as simple, or as complicated as you need it to be. These
built-in commands are very simple, limited in functionality, and do
minimal error checking. If nothing else though their code serves as a
foundation for more complex code which you may need in more complicated
situations.
First, the Queue must be created in a very specific way;
SomeQueueName
Queue(pre:Record),Prefix(SomePrefix)
! additional fields go here if desired
End
The prefix is required since the field names are the same as the table.
Declaring it using the
pre:Record means
that the Queue is declared with the same data structure as the File
record. If the file record changes, then the queue will as well.
Note that since
BLOB and
MEMO
fields are outside the Record structure, these commands cannot be used
with
BLOB and
MEMO
fields.
Using
TOQUEUE primes the contents of the
queue from the table using a
SET / NEXT
loop. All the properties which can be set for use by the
SET command are therefore in scope, and can be set
before the call to
TOQUEUE. These include
prop:Project and
prop:Filter. Since (short of running out of RAM)
Queues just work, this command is not expected to return any errors.
However if the file is closed, or the
SET
command generates an error, then that error will be set, and the command
will fail.
Calling
FROMQUEUE writes the data in the
queue to the table. It uses the
UPSERT
command, thus adding new records, or updating existing records. Again,
prop:Project is in scope and can be used to do
partial-record writes. BLOB and MEMO fields are left unchanged if the
record is being updated.
It is not necessary to call
TOQUEUE before
FROMQUEUE. When doing bulk adds to a table
doing that by filling the queue, then calling
FROMQUEUE
is a fast way to populate the table. If not already inside a transaction
then a transaction around the bulk insert (or update) will be done
automatically.
The queue can be declared as above, with additional fields added to the
queue. These additonal fields will not be used by the FROMQUEUE or
TOQUEUE commands.
The moving of data between the file record, and the queue buffer, is a
simple memory copy. No attempt is made to assign the data on a
field-level basis. Declaring the queue without using the pre:record, but
using just fields, will need to be done very carefully to avoid field
mismatches.
Example
CustomerQueue
Queue(Cus:Record),Prefix(CusQ)
End
code
Open(Customers,42h)
! Load the queue with any number of records
FromQueue(Customers,CustomerQueue) ! the queue records are
merged into the table with an UPSERT
Another Example
EmployeeQueue
Queue(Emp:Record),Prefix(EmpQ)
End
Code
Open(Employees,42h)
Set(Emp:NameKey)
Employees{prop:filter} = 'Emp:Age > 50'
ToQueue(Employees,EmployeeQueue)
! do what you like on the queue here
FromQueue(Employees,EmployeeQueue)
FLUSH (SQL)
For SQL drivers, when doing a SET/NEXT style loop, a
SELECT
statement is sent to the server and the results are fetched with the
NEXT and
PREVIOUS
commands.
When breaking out of the loop, in most cases, that result set is no
longer needed. In traditional drivers this information is only passed
through to the driver when the
FILE (or
VIEW) is closed. Object based drivers support
the
FLUSH command. This tells the driver
(and, if necessary, the database) that the result set is no longer
needed, and can be flushed from memory.
Along with the use of the
prop:Limit
property, this is the most useful thing you can do after a SET/NEXT loop
in order to improve performance, not just of the server, but of the
program as well.
FLUSH cannot be called if a transaction is
active. Doing so will set the error code to
56
Logout Already Active.
But wait, there's more.
If
STREAM has been called to mark
the start of a block of writes, then
FLUSH
closes the stream and allows the database to continue with normal
operations. This means that the performance gains from using
STREAM
and
FLUSH with ISAM files is translated to
similar performance gains in SQL.
GETRANDOM
The object based drivers introduce
GETRANDOM
as a new command. You will likely never need it. However when you do
come across a need for it, then it's a very useful command to have lying
around.
GetRandom(FILE pFile)
GetRandom(FILE pFile, String pFilter)
The
GETRANDOM function works the same as a
GET, except that it doesn't get a specific
record from the table, but rather a random record. This is useful when
say allocating an employee (randomly) to a bug, selecting a customer
(randomly) to win a prize, doing random audits and those sorts of places
where "just get a record by random" is a good solution.
If passed without a filter, then the command will prime the record
buffer with any record from the table. If passed with a filter then only
those records which pass the filter will be available to be chosen.
Example
Select an employee, who has the status "available", to be allocated to
this task;
GetRandom(Customer)
GetRandom(Employee,'emp:Available = 1')
If no record is available then the error
33 Record
Not Available is set.
IMPORT
Import(File pFile, String pDosFileName,Long pFormat,
Long pOptions=0)
Not Working Yet
The
IMPORT command is used to import text
files into the database. For exporting a table to CSV see the
EXPORT command.
| Parameter |
Type |
Description |
| pFile |
FILE |
The label of the FILE to import
into. |
| pDosFileName |
STRING |
The full path and name of the file to import from. Note that
this command should assume that the import is done natively on
the database server, and the server needs access to the actual
CSV file.So this (full path name) should be valid from the
server-side[1]. |
| pFormat |
LONG |
Defaults to Driver:CSV. This
parameter determines the format of the input. |
| pOptions |
LONG |
Default to 0. Not all options are supported in all formats.
Not all options are supported by all drivers. Consult driver
documentation for details. See below for possible options. |
The formats and options supported may depend on the specific database
driver. The individual driver documentation should be checked for any
deviations from this general information.
| Parameter |
Supported Values |
Description |
| pFormat |
driver:csv |
The format of the file being loaded is a CSV (common separated
value) file. |
| pOptions |
driver:header |
The first row in the text file is a header file, and should
not be imported. |
|
driver:upsert |
The Import will be done using the upsert methodology. This
means that if the row already exists in the database it is
updated to the values in the CSV file. If this is not used, then
the add methodology command will be used - which in turn means
that rows that already exist in the database will be left alone,
and the imported row will be ignored. |
Note 1:
For SQLite and InMemory drivers the currently running program hosts the
server, so the Server access rights and paths are the same as the
programs access rights and paths.
CSV
The import file can be in CSV format.When parsing this format the
following properties may be used. Not all drivers support all
properties.
| Property |
Default |
Description |
| prop:LineEndings |
CRLF
'<13,10>' |
The separator which separates each line in the CSV file |
prop:Separator
|
comma
','
|
The separator between each field in the line.
|
INTERSECT
Intersect(FILE pFile)
Intersect(FILE pFile,STRING pFilter)
The
INTERSECT command is used when creating
Subsets. It allows results to be removed from the
current result set if they do not appear in this set as well.
It typically (although does not have to) follows a call to
SUBSET.
Example
Find all customers that purchased NetTalk and StringTheory
Set(Cus:NameKey)
Subset(Orders,ord:CustomerId)
Union(Orders('Ord:ProductId = ''NetTalk''')
Union(Orders('Ord:ProductId = ''StringTheory''')
Example
Find all Products that have at least one sale every year for the last
five years.
Set(Products)
Subset(Orders,ord:ProductId)
Loop y = Year(Today()) - 5) to Year(today()) -1
Intersect(Orders,'YEAR(ord:date) = ' & y)
End
Other related commands are
UNION,
UNIONALL and
EXCEPT.
prop:Json
Traditional driver do not support
prop:Json
.
Object based drivers return the text version of the field from the
current record buffer, as a json encoded value. (See
prop:Value if you want the raw memory value and
prop:Text if you want the
unadorned text value). This property is especially useful if the
field is an array field. If the field is not an array then a
single-element-JSON-array is returned.
If the field is an array then a JSON array is returned, with each
element in the array corresponding to an array value.
DATE and
TIME
fields (but not
LONG fields masquerading
as date and time) are formatted into a human readable date (@D010- by
default) and time (@t04) and returned as JSON strings. Time values
are formatted as "+1" (meaning that 1 is added to the value before
formatting.) This behavior can be altered by the driver so see your
specific driver docs for non-standard behavior. (For example, the SQLite
driver formats the time with added milliseconds - ie hh:mm:ss.ccc. )
For Example;
j = customers{prop:Json,3}
Keys, Indexes, Run time Indexes and Sort Orders.
Clarion supports flat-file (ASCII, BASIC, DOS) drivers, ISAM (TPS, DAT,
BTrieve) drivers and SQL drivers. Clarion itself defines four different
key types in the dictionary. Understanding how these four types are used
in your program, and how they map to a SQL engine is important.
| Clarion Term |
ISAM |
SQL Term |
SQL |
| Key |
A sort order that is kept up to date as records are added to
the table. |
Primary Key, Unique Key, Foreign Key, Index |
These are kept up to date as the data is altered. |
| Static Index |
A sort order that is not kept up to date as records are added,
but is constructed on an ad-hoc basis using the BUILD
command before it is used. |
SQL has no direct equivalent of this
(although it can be mimicked in some database engines.) |
For purposes of the SQL File Drivers, Static Indexes in the FILE structure are treated the same as
Keys. In other words, from the SQL point of view, A Static Index
is just another Key.
|
| Run time Index |
A sort order that is undefined at compile time, but can be
defined at run time. This allows for different users to define
different sort orders. |
Run time Indexes have no real place in SQL. |
If you wish for additional indexes (keys) then you can simply
add them at the database level (or via a CREATEKEY
command.)
|
| Order |
A sort order that is defined in the dictionary,
but which is not included in the FILE
structure at all, and so does not appear in the data file. It is
used only at the template level as a simple way of collecting
relevant information for the templates. |
While keys are always discussed in terms of the
Order
of records, they are far more important when it comes to
Filtering
records. For optimal read performance it is important to have a key in
your table which matches the filter you are applying to a table. The
order of the result set, after the filter is applies is of secondary
importance.
It is not necessary for the list of keys and indexes in your dictionary
and in the SQL database to match. It is possible to define keys in your
dictionary that are not actually in the database, and it's possible to
have indexes in your database which improve performance, even if those
indexes are not declared in your
FILE
structure.
prop:Label
File{prop:Label,n} can be used to get the
label of a field, memo, blob or key in a table. Traditional drivers
however have no way to get the label of the
FILE
itself. (The name of the table can be returned by the
NAME
command, but there is no way to get the label.)
Object based drivers attempt to close this hole a bit by allowing
File{prop:Label,0} to return something other
than a blank string.
If not explicitly set (see below) it returns an upper case label for the
table. This is the Full Path Name of the table, as it was set when the
file structure was first touched. If the table has no path name then it
will be the first 8 characters of the Label.
The best way to get a valid value here is to set this property in code,
and then it can be retrieved later on. It can be set with;
table{prop:Label} = 'Whatever'
Note: Setting prop:Label
affects ALL instances of this structure, on all threads, for the life
of the program
Labels are always converted to uppercase by the driver. So after the
above code;
s = table{prop:Label,0} or
s
= table{prop:Label} will return
'WHATEVER'.
prop:Limit, prop:Offset & prop:Paging
When querying a database, the server builds a result set, and then
supplies that result set to the program. The result set is typically
constrained by using the
SET command, along
with the
prop:Filter.
Under the hood, the drivers (by default) make use of a paging system
which allows the server to build the results one page at a time. This
reduces load on the server. The size of the page is stored in
prop:FetchSize
and can be set by the
BUFFER command or by
directly setting the property. If not set the default value is used (at
the time of writing this value was 30, but it might change.)
All properties including prop:Limit
and prop:Offset should be set before
the call to SET .
For some queries using pages is inefficient. If you know the result set
will be a reasonable size (ie will fit into memory) and you know you
will be using all (or most of) the records in the set, or if the
combination of the filter and order forces a full table scan, then it
can be more efficient to query the database once, and get the whole
result set. In that case you can manually override the paging by setting
file{prop:Paging} = false
If you would prefer to do your own paging, then you can bypass the
SET/NEXT paging mechanism by setting your own
prop:Limit
and
prop:Offset[1]
values. The driver will treat this as a single request, and apply
your values rather than the built-in paging values. Setting the
prop:Offset
property only has effect if you set the
prop:Limit
property as well. Setting
prop:Limit to
-1 allows the
prop:Offset
to be used with "no limit".
file{prop:Limit} = 1000
file{prop:Offset} = 2000
In other situations you may have a firm idea of the number of records
you want to retrieve. For example you may be selecting the top ten sales
performers for the year, or the 10 biggest invoices and so on. In that
case setting a specific limit allows the server to optimize the request
for just the data you are looking for.
Note 1: Note that
paging using
prop:Offset has limitations
on tables which are dynamic - ie where records are being added or
deleted quickly. In a dynamic situation you may find better results
using
POSITION and
RESET
to move from one page to the next.
prop:Offset
though is particularly useful when multiple pages are handled at the
same time, and the next page is required before the end of the current
page is reached.
Logging
A key technique to debugging data access, or understanding how (and
when) the driver is being used, is to enable logging. When logging is
turned on the flow of commands and data through the driver becomes
visible, and can lead to a greater understanding of what is happening.
Not surprisingly, over the years, this feature has expanded in the
traditional drivers and it can be difficult to know what to do and when.
Trace.Exe
Any discussion of driver logging starts with the
Trace.Exe utility, which is located in the
\Clarion\Bin
folder. This program sets logging settings at the driver level (in
other words it applies to all tables using a specific driver.) It also
applies to ALL programs using that driver.
It works by setting settings in an INI file. The default INI file file
is in the
%AppData%\SoftVelocity\Clarion\Drivers.ini file.
It supports both traditional drivers, and object based drivers.
Since it is run outside your program, logging can be turned on and
off without changing, or recompiling your code. While useful, this
can have security (and performance) implications. For this reason,
in object based drivers, support for the INI file settings, and
hence for
Trace.Exe is
OFF
by default. It can be turned on by setting
system{prop:TraceFile}
= 'default' , or by setting
system{prop:TraceFile}
to a specific other INI name.
If supported, the settings are read by the program when the driver is
first accessed. So if changes are made to the settings in
Trace.Exe,
your program will need to be restarted in order for the new settings
to take effect.
It is possible for your program to specify an alternate INI file
location (
system{prop:TraceFile}) or to
set the settings to be read from the Windows Registry instead (
System{prop:TraceKey}).
If these are set then the
Trace.Exe
program has no effect, and the settings have to be set manually. These
settings apply to ALL drivers.
It is also possible to suppress global logging by setting
System{prop:DriverTracing} = 0
This overrides the settings in
system{prop:TraceFile}
and
System{prop:TraceKey} and turns off
all driver wide logging. However this does not affect specific
FILE or
VIEW
tracing as discussed later. To take effect this property MUST be set
before any file driver is initialized. In other words it only applies
at the point where the INI, or Registry, settings are read.
Driver-Wide Settings
As discussed above, global settings can be set using the
Trace.Exe
program. However that only works if the
Drivers.ini
file is in the default location, and has that name. It's possible to
set the name, and location of the INI file using
system{prop:TraceFile}.
Alternatively the settings can be stored in the Windows Registry using
system{prop:TraceKey}. For example;
system{prop:TraceFile} = 'c:\log\Settings.Ini'
system{prop:TraceKey} =
'HKEY_LOCAL_MACHINE\SOFTWARE\DriverKit'
If both
system{prop:TraceFile}and
system{prop:TraceKey}are set then the INI
file is ignored, and the registry settings are used.
For object-based drivers either
system{prop:TraceFile}
or
system{prop:TraceKey} MUST
be set to something for global Driver-Wide logging to take place.
Frankly, this approach to logging is
not
recommended for security, performance, and the sheer size of
the log file reasons.
The supported setting names are as follows;
| Setting |
Type |
Matching Code Property below |
| BindData |
Boolean/REG_DWORD |
PROP:LogBind |
| Details |
Boolean/REG_DWORD |
prop:Details |
| Profile |
Boolean/REG_DWORD |
prop:LogClarion |
| Trace |
Boolean/REG_DWORD |
prop:LogSQL
|
| TraceFile |
String/REG_SZ |
prop:Profile
|
File Settings
A better, and safer, way to log driver interactions is to log
information for a specific file, for a specific block of code. This
reduces the impact on performance, limits the security risk to the
area you are logging, and results in a smaller (and hence easier to
understand) log file. To simplify things even more, two new commands
have been added;
LOGGINGON
and
LOGGINGOFF. Using these
it's possible to quickly enable, and disable logging for a file. In
addition to these commands, the following properties are also
supported;
| Property |
Behavior |
| prop:LogBind |
In SQL the commands and the data are separate (most of the
time). This is known as parameterization.
If set to true then then the
values of fields which are "bound" to the SQL command are
shown in the log. |
| prop:LogClarion |
If set to true then the
Clarion commands which triggered the SQL are included in the
log. |
prop:LogData
prop:Details |
If set to true then the
contents of the record buffer, at the time of the Driver
command, is included in the log. |
| prop:LogExplain |
If set to true then the SQL
engine will be asked to provide a Query Plan for SET
/ NEXT loops. This plan is then
included in the log. |
| prop:Log |
Allows a custom line to be injected into the log at this
point. Recommend using LOG
command instead. |
| prop:LogSQL |
If set to true then this includes SQL statements in the log
file. |
Prop:Profile
Prop:Logfile |
If set (to a file name), this toggles logging on. All file
calls and errors returned by the file driver are logged.
Assigning a filename to PROP:Profile turns on logging, while
assigning an empty string ('') turns off logging. Querying
this property returns the name of the current logfile, and an
empty string ('') if profiling is turned off. |
For ad-hoc logging another two commands have been added.
LOG allows you to inject custom text
directly into the log file (or, if no log file is set, it sends the
text to
Debugview++).
LOGBUFFER does the same thing,
but logs the current contents of the buffer. If no log file is set
this is also sent to
Debugview++. This means, that at any point in
the code, the current contents of the buffer can be observed simply by
calling
LOGBUFFER. For example;
LogBuffer(Customers)
Because the log file can be appended by multiple users, on multiple
workstations, and by multiple threads, if the log is being sent to a
file then a large amount of information is included in each line
of the log.
A typical line in the log looks something like this;
[SQLite2][607][HULK][Gerald][4840][1][ 4/03/25][
7:21:37][][sqlput2.sqlite][Ident91][66][DATA] IDE91:NAME |
Brian
More Generally, the information is as follows;
[DriverName][Object ID][Computer Name][Windows
User][Process ID][Thread][Date][Time][Server][Database][Table
name][Open Status][type] data
Type is one of
[CLAR] : Clarion Code statement
[SQL ] : SQL Statement sent to the
database
[BIND] : A binding of a SQL parameter to
a specific value
[DATA] : The contents of a field in the
record buffer.
[EXPN] : The Query Plan as returned by
the server.
LOG
Log(FILE pFile, String pText)
Log(VIEW pView, String pText)
This performs the same function as setting
prop:Log
(but with tidier syntax). It writes to the currently open log file (as
opened by
LOGGINGON, or
prop:profile). If no file is currently open,
then it writes it to
Debugview++.
Example
Log(Customers,'Calculating Tax Advantages here')
See Also:
Logging
LOGBUFFER
LogBuffer(FILE pFile)
LogBuffer(VIEW pView)
If called on a
FILE then all the fields
(including blobs and memos) for the file structure are sent to the log.
When called on a
VIEW then all the
fields (including blobs and memos) for all the
FILE
structures in the
VIEW, are sent to the
log.
Neither form of this command applies
prop:Project
- meaning that all the fields are included in the output, regardless of
whether those fields are in the
VIEW or
not.
If no log file is active then the contents of the structure are sent to
Debugview++.
This means that at any point in your code, while you are debugging, you
can inspect the current contents of the
FILE
or
VIEW structure simply by calling
LOGBUFFER.
Example
LogBuffer(Customers)
LogBuffer(CustomersView)
See Also:
Logging
LOGGINGOFF
LoggingOff(FILE pFile)
LoggingOff(VIEW pView)
Ends logging which was started with a call to
LOGGINGON
(or by a call to
prop:Profile).
Example
LoggingOn(Products)
! some code goes here
LoggingOff(Products)
See Also:
Logging
LOGGINGON
LoggingOn(FILE pFile,<String pFileName>,Long
pOptions=0)
LoggingOn(FILE pFile,Long
pOptions=0)
LoggingOn(VIEW pView,<String pFileName>,Long pOptions=0)
LoggingOn(VIEW pView,Long pOptions=0)
This command turns on Logging for a file. This logs commands into a text
file, or
Debugview++.
| Parameter |
Type |
Description |
| pFile / pView |
File |
The label of the FILE or VIEW to log. All commands related to
this FILE or VIEW
will be added to the log file. Multiple FILE
and VIEW structures can write to
the same log file. |
| pFileName |
String |
The name of the text file to log to. If the file already
exists then the new log will be added to it.
If the parameter is omitted, or is a blank string, then the
output will be sent to Debugview++.
The filename cannot be a numeric value. |
| pOptions |
Long |
One or more of the following equates, added together. If this
parameter is omitted, or 0, then ALL the options,except
driver:LogExplain, default to
true.
driver:LogSQL : SQL commands, as
sent to the database, are included in the log. They are
identified with rows including [SQL ].
driver:LogClarion : Clarion
commands, as written in the code, are sent to the log. They are
identified as rows including [CLAR].
driver:LogData : The contents of
the record buffer are sent to the log. These are identified as
rows including [DATA].
driver:LogBind :
Parameterized values bound to a SQL statement are included in
the log. These are identified with [BIND].
driver:LogExplain : When doing a SELECT statement, caused by a SET/NEXT loop, the Query Plan (as explained
by the database engine) is included in the log. These lines are
identified with [EXPN]. |
For example;
LoggingOn(Customers)
Get(Customers,cus:PrimaryKey)
LoggingOff(Customers)
Or
LoggingOn(Customers,'Customers.Log',driver:LogSQL
+ driver:LogExplain)
Set(cus:PrimaryKey)
Loop
Next(Customers)
Break
End
LoggingOff(Customers)
See Also:
Logging
LOGOUT
For technical reasons, if an error occurs doing a call to
LOGOUT,
the program may GPF. To avoid this, and get an
Errorcode
set use the
LogoutConnection
command instead.
LOGOUTCONNECTION
LogoutConnection(FILE pFile)
For more information on
LOGOUTCONNECTION
see
Transactions.
Note that if
LOGOUTCONNECTION is used, then
ROLLBACK and
COMMIT
MUST take the primary table as a
parameter.
Example
LogoutConnection(Customers)
! some writes happen here
Commit(Customers)
Memos
In Clarion each data field has a type;
LONG,
STRING and so on. These types are all
fixed-length, the string has a declared length, and so on. Most
databases also support
BLOB (Binary Large
Object) fields. These are fields of indeterminate length, and which can
hold binary values (and which can grow very large.) In between these two
extremes lies a hybrid field, the
MEMO.
The
MEMO originated in the Clarion DAT
format (in Clarion Professional Developer, a DOS product). The DAT
format supports a single
MEMO field in the
record, and does not support
BLOB. To make
it easier to port DOS programs to Windows the TOPSPEED file driver
supported
MEMO (as well as
BLOB.)
None of the traditional SQL drivers support the
MEMO
type, although all support the
BLOB type.
That's a pity though because the
MEMO can
be used directly on a Window, as the source to an
ENTRY
or
TEXT control, or as a column in a
browse. The templates, and the language, mostly treat the
MEMO
as a large string.
When converting TOPSPEED programs to a SQL database it's common practice
to change the
MEMO into a
CSTRING.
While this makes the behavior of the type consistent (and hence the
conversion simpler) it can lead to issues where the chosen database has
a maximum record size. Converting to a
BLOB
is also possible, however a
BLOB cannot be
used as the source to Window controls, and so extra code has to be added
there. It also cannot be added to the VIEW by the templates - again
creating more work.
The object based drivers
do support the
MEMO type. How this is mapped to the database
is dependent on the specific driver. This will make conversions easier,
but more importantly makes it simpler to make use of the
MEMO
benefits in SQL-based applications.
There is however a catch. The Clarion Documentation states the
following;
Memory is allocated for a MEMO field's buffer when
the file is opened, and is de-allocated when the file is closed.
In practice that's not true. Both the TOPSPEED and CLARION drivers
support the use of the
MEMO field even if
the table is closed. In other words memory is set aside for the
MEMO
when the thread starts, regardless of whether the table is ever opened
or not. This can have a significant impact on the memory assigned
to each thread, especially if several large
MEMO
fields are in the dictionary.
With regard to blobs; the documentation for
BLOB
is equally clear - a
BLOB is not available
if the file is closed. In practice this is enforced, and if you access a
BLOB field when the file is not open, the
program will likely GPF.
Given that
MEMO support has been added to
the object based SQL drivers, there is a choice to be made. Either
follow the documentation (which would be ideal for memory reasons) or
follow the behavior of the DOS and CLARION driver (which would be ideal
for compatibility reasons.) As is usually the case, compatibility wins
and so the current behavior is preserved.
MERGE
Merge(FILE pFile,FILE pDestination)
Merge(FILE pFile,STRING pDestination)
MERGE is a new command. It allows the
contents of one table to be pushed into another table. The destination
table becomes a super-set of the source table. If a source record exists
which matches any unique key in the destination table then the
destination record is updated, not duplicated.
The
FILE structure of the Source File is
the list of fields that will be merged. Fields that exist in the SQL
table, but do NOT appear in the FILE declaration will not be merged.
If the second parameter is a
FILE then the
parameter MUST be a
FILE structure with an
object based driver. The Destination table does not need to be open. If
the Destination table has a Full Path Name variable or Owner variable
then those MUST be set correctly before this call. If the destination
table does not exist then it will be created.
If the second parameter is a
STRING then
it should name a table (in the same database) with a similar, or the
same structure. If the table does not exist it will be created for you.
The tables are assumed to have the same, or similar, table declarations.
The fields are matched by the column name in the database. Fields which
are in the destination file, but not the source file are set to their
(SQL) default value (which may be a
NULL).
Fields which are in the source file, but not the destination file, are
not copied.
If the second parameter is a
STRING it
should be the fully qualified table name (ie of the form
database.schema.tablename). If the destination table is in the same
database then the name can be just the destination tablename. If this
form of the function is used then the destination table MUST include all
the fields, with the same names, as exist in the source
FILE
declaration.
If the destination table is in the same database, or (if supported by
the database) a table in a different database on the same server, then
the process is fast and no data transfer to the client is required.
However if the destination table is a different driver or different
server, then the process is somewhat slower as data has to flow first
from the source database to the program, and from there to the
destination server. The destination driver MUST support the
UPSERT
method.
This command is a one-way, upsert, merge. No deletes are performed on
records from either table.
All supported properties should be set (or cleared)
before the call to MERGE
.
Calling CLEAR(file) or CLEARPROPS(file)
will clear all the properties.
The
MERGE command supports
prop:Project ,
prop:Filter
,
prop:SQLFilter,
prop:Limit,
prop:Offset. If a subset of the fields are
projected, then the destination table will set the remainder of the
fields to be their default value, or Null.
All the properties are cleared at the end of the call to
MERGE,
regardless of success or failure.
If the
MERGE command is called against a
traditional driver then the
Errorcode() is
set to
NoDriverSupport (80).
See Also
COPY .
NOMEMO
From the Clarion Documentation;
In Clarion the NOMEMO statement arms "memoless"
record retrieval for the next GET, REGET, NEXT, or PREVIOUS statement
encountered. The following GET, REGET, NEXT, or PREVIOUS gets the
record but does not get any associated MEMO or BLOB field(s) for the
record. Generally, this speeds up access to the record when the
contents of the MEMO or BLOB field(s) are not needed by the procedure.
None of the traditional SQL drivers support this command, and so this
occasionally significant optimization is not available to SQL users.
Object based drivers though do support
NOMEMO,
even for SQL databases. With one slight modification to the original
specification;
- For Object based drivers the NOMEMO
works for GET , GETRANDOM
and REGET as normal. It retrieves one
record without memo or blobs, and the property is reset for
subsequent reads.
- However for NEXT and PREVIOUS
loops, the NOMEMO call applies to the
whole result set as defined by the call to the SET
or RESET command, and is only cleared
by the next SET or RESET
command. In other words all NEXT and PREVIOUS calls inherit the NOMEMO
option from the most recent SET or RESET command. Therefore if NOMEMO
functionality is desired it must be called before SET
is called in order for it to apply.
NOMEMO also works on the
COPY
and
MERGE commands. It is cleared
after these calls regardless of success or failure. Calling
NOMEMO(file)
before calling
COPY or
MERGE
will result in the BLOB and MEMO fields not being copied, or merged. In
the case of a
MERGE existing BLOB fields in
the destination table are unaltered.
If a read is followed by a write, then the write will inherit the NoMemo
state of the read. In other words, if a record is read with
NOMEMO
active, and then it is
PUT, then the BLOB
and MEMO fields are not included in the
PUT.
If called before the
SEARCH
command, then the Blob and Memo fields are included in the search,
however the result set returned by the
NEXT
loop following the
SEARCH will not include
MEMOs or BLOBs. As above, as described by the SET / NEXT loop, it is not
cleared automatically.
NOMEMO will cleared by a call to
CLEAR(File)
or
CLEARPROPS(File).
Prop:NotDistinct
The opposite of
DISTINCT result sets, are sets
where
only records that are duplicated
are included.
The value in this property represents the number of times the row is
duplicated. So, to find all rows with 2 or more instances set this
property to 2. For all rows with 3 or more instances set it to 3, and so
on. To turn the property off, set it to 0. Setting it to 1 has no
meaning, and has the same effect as setting it to 2.
This property
is not reset to 0 except on
a
OPEN ,
CLOSE
,
CLEAR and
CLEARPROPS.
If you wish to re-use the
FILE with this
property off, then you must set it to 0 (if you previously set it to a
value).
Rows in the table are unique. So retrieving all the fields in a row will
mean that
prop:NotDistinct will have no
meaning (no rows will be fetched.) Therefore, in the
FILE
context,
prop:NotDistinct is only useful
if you are using
prop:Project to retrieve
only a subset of columns, and those columns do not include all the
fields in any unique index.
It is not necessary to set
prop:Paging to
false - when
prop:Distinct or
prop:NotDistinct
are used, then paging is automatically disabled.
Since there is no row-identifying field in the result set, the results
of this request are automatically set to be read only.
customers{prop:NotDistinct} = 2
customers{prop:Project} = 'Cus:Lastname,Cus:Age'
Set(Cus:NameKey)
Loop ...
The result set will contain one of each record (it doesn't return
duplicated rows) but all the rows in the result set exist more than once
in the table. As with
prop:Distinct,
the result set does not include row identifiers, however from this
result set you can do a select to fetch the rows with those values.
If the
prop:NotDistinct is set, but the
prop:Project is not set, then the
prop:NotDistinct
is ignored and the implicit
prop:ReadOnly
is not applied.
See also
prop:Distinct.
Nulls
In the object based drivers most of the Nulls functionality is handled
by the base class.
Known differences between the traditional driver handling of nulls, and
the object based driver handling of nulls is discussed below.
| Code |
Traditional Drivers |
Object Based Drivers |
GetNulls
|
Returned a string with length (4 * number of fields).
|
Returns a string with length (1 * number of fields).
|
| GetNulls |
String could not be manipulated directly |
String contains a + character
for non-null fields, and a .
character for null fields. This makes it possible to inspect the
string to see all the null fields, and also to construct a
string suitable for passing to SetNulls. |
POINTER64
Pointer64(FILE pFile),BIGINT
The
POINTER command returns a
LONG,
which limits the number to around 2.1 billion. This may not be enough
(for example the DOS2 driver supports files greater than 2 Gigabytes).
The
POINTER64 command is a new command
which returns a
BIGINT variable (at the
time of writing this, this is a
REAL.)
If this command is called against a traditional driver then the regular
POINTER command is called, and a value less
than 2.148 billion is returned.
See also
Bytes64 and
Records64.
POSITION
The
POSITION command returns a string, and
that string contains enough information to "mark" where the record is.
It can then be retrieved later, as a single record using
REGET, or a
SET /
NEXT sequence can be restored, using
RESET, to this position for further processing.
The actual string returned by
POSITION
varies by use-case and driver. No information about the contents, or
length of the position string should be assumed. Generally speaking,
when using
Position(file) or
Position(key)
the string should be at least large enough to hold the Primary Key
fields.
There are two forms of the command,
POSITION(file)
and
POSITION(key). Traditional SQL drivers
do not support
POSITION(file) since most
SQL engines do not support any kind of record numbering. However the
object based drivers treat this case
POSITION(PrimaryKey).
So the
POSITION(file) form is supported -
it's the same as
POSITION(PrimaryKey).
Since object based drivers require a primary key, and since primary keys
are always unique,
POSITION(AnyKey) is
always equivalent to
POSITION(PrimaryKey).
In other words,
POSITION always returns the
position of the primary key. This implies that all forms of
POSITION
when called on a
FILE, return the same
value.
If
prop:Project is set, and
the read did not include the primary key fields, then POS will fail,
returning an empty string . An error code
121
Invalid Project List will be set.
Note: Because the position string returned can
contain null characters (
chr(0)) the return
value MUST be stored in a
STRING, not a
CSTRING.
Note: Although
POSITION
returns a common value, regardless of the key used, the key passed into
RESET still matters. In traditional drivers
this means that the
Position(key) must
match the
Reset(key). In object based
drivers
POSITION returns a common value, so
any key can be used in the
RESET.
Note: This section specifically applies to
using
POSITION with a
FILE.
For use with a
VIEW see
here.
prop:Project
When reading a record from a file (using GET, NEXT or PREVIOUS),
typically all the fields are read from the file into the record
structure. In some cases though only a small number of fields are
necessary. Reducing the number of fields fetched from the server can
improve performance as less network traffic needs to flow over the wire.
prop:Project is introduced by object based
drivers to offer the programmer this level of control.
prop:Project is a comma separated list of
field names. Either the Clarion Label (including the prefix) or the
database column name can be used.
prop:Project is set in the usual way;
Tablename{prop:project} = 'cus:id, cus:name,
cus:phone'
If the Primary Key fields are NOT included in the projected list then
the read is automatically set into Read-Only mode, and the results read
by the SET/NEXT loop cannot be
PUT back
into the database. Doing a
PUT after a
read-only read results in an
Error Access Denied
(5). The use of POSITION, and by implication REGET are also
unavailable if the primary keys fields are not projected.
If a
GROUP is projected, then all the
fields inside the group are automatically projected.
If an
OVER field is projected, the the
over'd field is projected.
In addition to fields already in the table, it's possible to project
calculated fields into local variables outside of the record structure
using
BindField.
Read
From a read perspective,
prop:Project is
supported by
SET (which then affects the
following
NEXT and
PREVIOUS
calls), and
GET commands. Notably
REGET is not affected by
prop:Project
-
REGET always reads the whole record
into the buffer.
Write
From a write perspective, prop:Project
is supported by the ADD, APPEND,
COPY, MERGE,
PUT and UPSERT
commands. For ADD, APPEND,
COPY and MERGE
when doing a partial write the other fields are set to the (SQL Side)
default values, or null.
prop:Prefix
In traditional drivers prop:Prefix (aka prop:Pre) is a write-only
property, used when constructing tables using the dynamic file driver.
In object based drivers it can be read and returns the PREFIX attribute
of the table.
Primary Key
All tables in your dictionary should have a Primary Key. This is simply
good database design, and is a requirement for most database engines. A
Primary Key should be identified in the FILE structure by use of the
PRIMARY attribute. This is typically set in the
Dictionary Editor, for one of your keys. While the use of a Primary Key
is not enforced by Clarion itself, it should be considered a basic
requirement especially when using a SQL engine.
In the absence of a formally identified Primary Key, the object based
drivers will treat the first key marked as Unique as the Primary key.
However this approach may end up selecting a key that you do not expect.
Since most engines fix the primary key when the table is CREATEd in the
database this can have long-term consequences.
prop:Name
Unfortunately the Traditional driver behavior for
prop:name
is not consistent. Therefore the behavior of both traditional and object
based drivers should be considered as driver dependent. And to make
things more exciting, the values change once the FILE structure has been
initialized
[3] (on this thread, assuming the
FILE is Threaded.)
The different known behaviors are documented here;
Before Initialization;
| Type |
Driver |
Applies To |
Behavior |
Attributes |
| Traditional |
SQLite (maybe?) |
Field and Blob |
If the NAME is not set, then the LABEL (no prefix) is
returned. |
Not included [1] |
| Traditional |
All Other SQL |
Field, Memo and Blob |
If the NAME is not set then a blank string is returned. |
Not Included [1] |
| Traditional |
All ISAM |
Field and Blob |
If the NAME is not set then a blank string is returned. |
Included |
| Object Based |
All |
Field and Blob |
If the NAME is not set then a blank string is returned. |
Not Included [2] |
After Initialization;
| Type |
Driver |
Applies To |
Behavior |
Attributes |
| Traditional |
CLARION |
Field (not memo) |
If the NAME is not set then a blank string is returned. If the
name is set it is UPPER cased. |
Included (all spaces removed) |
| Traditional |
CLARION |
Memo |
If the NAME is not set then a blank string is returned. |
Included |
| Traditional |
All SQL |
Field and Blob |
If the NAME is not set, then the LABEL (no prefix) is
returned. |
Not Included [1] |
| Traditional |
All ISAM |
Field, Memo and Blob |
If the NAME is not set then a blank string is returned. |
Included |
| Object Based |
All |
Field, Memo and Blob |
If the NAME is not set, then the LABEL (no prefix) is
returned. |
Not Included [2] |
Note 1: To get the attributes for Fields
use the
WHO command.
Note 2: To get the attributes for fields
and blobs can use prop:attributes. For Fields WHO, can be used.
Note 3: The file is initialized on a call to
OPEN
(successful or not.) The file remains initialized after a
CLOSE,
so this is not the same as "The file must be Open". Other commands may
also trigger initialization, but if there are they are not known at this
time.
Note 4: Bonus tip - when called as just
File{prop:name},
The CLARION driver returns the full path name once the table has been
opened, even if the table has been closed. All the other ISAM drivers
return the table name after it has been closed, not including the path.
[todo] discuss setprop:name, and the behavior in the
new apps.
New Properties (and new uses for old
properties)
Properties are a useful way to extend the functionality of the driver,
without having to make use of the Object methods directly. For this
reason a number of new properties are supported, and can be used using
the regular property syntax. These properties only apply to object based
drivers, if used against traditional drivers they are ignored.
Some of these properties exist already and are supported by the VIEW
driver when applied to VIEWs. However traditional drivers may not
support these properties when used in a FILE context.
| Property |
Driver |
Applies To |
Commands |
Description |
| prop:AddOnly |
All |
FILE |
ADD, APPEND |
When ADDing records to a database, the primary key position of
the record is stored so that you can follow up the ADD
with a DELETE or PUT
if necessary. In cases where you are adding a lot of records,
and you know you won't be doing a PUT
or DELETE, you can set file{prop:AddOnly}
= true. This bypasses the record tracking, which saves
some time. This property is reset by any read (GET,
NEXT, PREVIOUS,
RESET, as well as CLEAR
and CLEARPROPS). If a PUT or DELETE
is done after an ADD (with this
property on) then a Record Not Available
(33) error is raised. See also prop:ReadOnly
below. |
| prop:Affected |
SQL |
FILE / VIEW |
DELETE, PUT |
When changing, or deleting rows, the number of rows affected
by the write will be stored in prop:affected. This does not
include rows updated internally by the server via cascading
relational writes. See prop:ChildrenAffected. |
| prop:Arrays |
All |
FILE |
|
Returns 1 if he driver supports arrays. Returns a blank string
if not. |
| prop:Attributes |
All |
FILE |
File Structure |
Returns the Extended Name Attributes
for a field or blob. Attributes are included in the Name of a
field in the file structure, after a Pipe ( | ) character. |
| prop:Bytes |
All |
FILE |
|
This behaves the same as the BYTES
command, but is not limited to returning a LONG.
Use this in cases where the returned value may be greater then a
LONG can hold (ie greater than
approximately 2.1 billion). See, for example, the DOS2 drivers'
support of files greater than 2 gigabytes in size. See also BYTES64. |
| prop:ChildrenAffected |
SQL |
FILE / VIEW |
DELETE, PUT |
Some databases return the number of related rows in other
tables also affected by the write. This property may not be
available in all drivers. Review driver documentation. |
| prop:ClearProps |
All |
SET / NEXT |
|
Clears all properties mentioned in this list (unless otherwise
noted.) Is equivalent to calling the CLEARPROPS
command. Automatically called when doing a CLEAR(File). |
| prop:Database |
|
|
|
Returns the DATABASE part of the
current SQL connection string. See also prop:Server
and prop:User. |
| prop:DatabaseName |
ALL |
FILE |
|
Returns the database engine name. In other words something
like SQLite, PostgreSQL, MySQL or whatever. |
| prop:Distinct |
SQL
|
FILE / VIEW |
SET
|
Causes the result set to only contain distinct records. For
more see Prop:Distinct.
|
| prop:Explain |
SQL |
FILE / VIEW |
SET
|
Gets feedback from the SQL database for a SQL command.
For more see prop:Explain.
Unlike the other properties this one is
reset to false immediately the SET
command is called. |
| prop:FastPut |
SQL |
FILE / VIEW |
PUT |
Default value is false. If set
to true, then a PUT
statement will not reset
read-only fields in the record buffer (from their corresponding
database values in the database.) This performs the same option
for a PUT that APPEND
does for ADD. |
| prop:Filter |
SQL |
FILE / VIEW |
COPY, MERGE,
SET
|
Causes the result set to be filtered. In the FILE context is
especially useful to set the end of the SET/NEXT
loop. |
prop:Interface
|
All
|
FILE
|
All READs and WRITEs.
|
Allows custom field handling to be added to a table, without
altering the shipping file driver. This is equivalent to calling
SetCustomType. (Not cleared by
ClearProps). |
| prop:GroupBy |
SQL |
FILE |
SET |
|
| prop:Having |
SQL |
FILE |
SET |
|
| prop:Json,FieldNumber |
ALL |
FILE |
|
Returns the field as a JSON array. If the field is not an
array, then the JSON is still a JSON array, just with a single
element. Where necessary JSON encoding is used to encode JSON
reserved characters. The field number is passed along with the
property to determine which field is returned. Use negative
field numbers to indicate Blobs and Memos.
somestring =
Customers{prop:json,fieldnumber} |
prop:Limit
|
SQL |
FILE / VIEW
|
COPY, MERGE,
SET |
Allows a FILE or VIEW loop to be limited to a specific number
of records. Can be used in a SET / NEXT
loop. Setting this to -1 tells the driver to fetch all records.
This property must be set to something for the Offset property
to apply.
|
| prop:NotDistinct |
|
|
|
|
| prop:OBD |
All |
FILE |
|
Returns 1 if the file is using an Object Based Driver. Returns
a blank string for traditional drivers. |
prop:Object
|
All
|
FILE /
KEY / BLOB / VIEW
|
|
As the name suggests, object based drivers are built using
classes, and each FILE or VIEW structure gets an
associated object assigned to it. It can be useful to access
this object directly. This property returns a pointer to the
DriverFileClass object. See Getting the
Object for the FILE Structure for more.If used against a
KEY or BLOB then the DriverFileClass object of the FILE is
returned. (Not cleared by ClearProps). |
prop:Offset
|
SQL |
FILE / VIEW |
COPY, MERGE,
SET |
Allows the initial records in the result set to be skipped.
This property is only applied if the LIMIT property is set. The
LIMIT property can be set to -1 to return all records.
|
| prop:Order |
SQL |
FILE / VIEW |
SET
|
Causes the result set to be ordered. In the FILE context is
especially useful for ad-hoc ordering of a SET/NEXT
loop. |
| prop:OrderBy |
SQL |
FILE / VIEW |
SET |
|
| prop:Paging |
SQL |
FILE / VIEW |
SET |
When set to true (which is the default) then Files and Views
fetch the records from the database one page at a time. In some
cases though it's faster to fetch the whole result set in one
request. Set prop:paging to false if this is the case. |
| prop:Pointer |
All
|
FILE
|
|
Functions the same as the POINTER
command, but is not limited to returning a LONG. Use this in
cases where the returned value may be greater then a LONG can
hold (ie greater than approximately 2.1 billion). See, for
example, the DOS2 drivers' support of files greater than 2
gigabytes in size. See also POINTER64.
(Not cleared by ClearProps). |
| prop:Pool |
SQL |
FILE |
OPEN |
Allows more control over the polling of database connections.
See Transactions. |
| prop:Project |
SQL |
FILE |
COPY, MERGE,
SET |
Allows for partial record reads and writes. |
| prop:ReadOnly |
All |
FILE |
SET |
When reading records (like in a SET/NEXT
loop) the driver needs to keep track of the record just loaded
in case you want to do a PUT or DELETE on the record. This tracking
takes up some time. To improve performance you can set file{prop:ReadOnly}
= true in which case this tracking is bypassed. If an ADD, APPEND,
PUT or DELETE
is done after a read (with this property on) then a, Access
Denied (5) error is raised. See also prop:AddOnly
above.
Prop:ReadOnly is not reset to
false except on a OPEN , CLOSE
, CLEAR and CLEARPROPS.
So if you set it for a file, then you are responsible to unset
it if you switch to using the file in read/write mode. |
| prop:Records |
All
|
FILE
|
|
Functions the same as the RECORDS
command, but is not limited to returning a LONG. Use this in
cases where the reply value may be greater then a LONG can hold
(ie greater than approximately 2.1 billion). See also RECORDS64.
|
| prop:RelationsOff |
SQL |
FILE |
SUSPENDRELATIONS
RESUMERELATIONS |
If server side relationships have been suspended, for this
connection, on this thread, then this property will return 1.
Otherwise it will return a blank string. |
| prop:Server |
|
|
|
Returns the SERVER part of the
current SQL connection string. See also prop:User
and prop:Database. |
| SQLFilter |
SQL |
FILE / VIEW |
COPY, MERGE,
SET |
|
| SQLOrder |
SQL |
FILE / VIEW |
COPY, MERGE,
SET |
|
| prop:User |
SQL |
FILE / VIEW |
|
Returns the USER part of the
current SQL connection string. See also prop:Server
and prop:Database. |
| prop:Where |
SQL |
FILE / VIEW |
COPY, MERGE,
SET |
This property is not set by the programmer, but is available
to the programmer to inspect after a call to SET. This contains the combined WHERE
clause as constructed by the class. |
| prop:WherePage |
SQL |
FILE / VIEW |
|
This is the same as the prop:where statement, but includes the
additional WHERE clauses used for the
paging. |
PUT
Using traditional SQL drivers, only the changed fields are sent to the
server. In other words, not all the fields in the record are updated.
This is more efficient, and it also protects other fields, that may have
been changed in the row, by other programs (or threads) between the
GET and
PUT calls.
The object based SQL drivers mimic this behavior.
In addition to this, the object based, SQL drivers support
prop:Project.
When reading the record,
prop:Project lets
you limit the fields that are being read from the table. This property
is used again in the
PUT. So not all the
fields are tested for changes, only the fields that are in
prop:Project,
which typically means the fields which were read in the first place.
In the context of a
PUT, the projected
fields are the fields being projected
into
the server from your program.
It is possible to adjust
prop:Project
between the read and the write, which in turn affords the programmer
even greater control over which fields are written back to the database.
If you write back a field, which was not read, then the write will only
happen on a field if the value of the field is different to the value
before the record was read. So adding fields to
prop:Project
should be done with care.
Some care should be taken when using
GET,
REGET,
NEXT and
PREVIOUS. All these commands also support
prop:Project. Changing the value of
prop:Project
between the read and the
PUT should be done
with caution. Typically you will not want to write back more fields than
were read (although it's possible to do so.) The drivers do not alter
the value in
prop:Project after a read.
Equally,
prop:Project is not cleared, or
reset, after a
PUT. For subsequent reads
the value should be cleared or reset or it will be used again.
By default, if supported by the driver, when a
PUT
is done, all server-side fields (identity, and readonly fields) in the
record are reset to their current server-side value. Use the
prop:FastPut property to suppress this behavior.
See also:
Add.
prop:Readonly
When a table is opened, access rights for the table are set. Performance
and security are improved if the table is opened in
read-only
mode. However since tables are opened generically, this is seldom used.
Even if the table is opened in the default
read-write
mode, there are times when telling the driver that a block of code will
be
read-only has performance implications.
For this reason
prop:ReadOnly has been
introduced in object based drivers.
Set the property to true or false. For example, setting it to true;
customers{prop:ReadOnly} = true
has the effect of telling the object that it will only be used in a
read-only context (for now.) While in this state calling
ADD
or
APPEND will result in an
Access
Denied (5) error. Calling
PUT or
DELETE will result in a
Record
Not Available (33) error. Once the
read-only
moment has passed the property can be set back to
false.
The property is set automatically on
OPEN
or
SHARE if the file is opened in
read-only
access mode. If the file is opened in
read-only
mode (via the
OPEN or
SHARE
commands) then attempting to set the property to
false
has no effect. If it's desirable to change the access mode after the
table has been opened then see
REOPEN.
RECORDS
Records(FILE pFile)
Records(FILE pFile, String pFilter),BIGINT
Records(FILE pFile, *? pField1, <*? pField2>,
<*? pField3>, ..., <*? pField15>),BIGINT
| Parameter |
Type |
Description |
| pFile |
File |
The label of the file to count. |
| pFilter |
String |
A filter expression which expresses the records to count. |
| pField1..pField15 |
Any |
Fields in the FILE record. Up to 15 fields can be entered
here. The count will be filtered using the current values in
these fields. These cannot be OVER or ARRAY fields. |
The
RECORDS command has been extended to
allow the counting of more things than just the records in the table.Two
possible approaches can be used; a filter expression, or a list of 1 to
15 fields with fixed values.
Example
Return the number of records with a date in January.
r = Records(Invoice,'Inv:Date >= Date(1,1,2024)
and Inv:Date <= Date(1,31,2024)')
or
r = Records(Invoice,'Month(Inv:Date) = 1 and
Year(Inv:Date) = 2024')
Return the number of Invoices allocated to Customer 5 on the 12th of
January
Inv:Date = Date(1,12,2024)
Inv:Customer = 5
r = Records(Invoice,Inv:Date,Inv:Customer)
Note: OVER and ARRAY fields cannot be used in
either the filter or as pField parameters.
RECORDS64
Records64(FILE pFile),BIGINT
The
RECORDS command returns a
LONG,
which limits the number to around 2.147 billion. This may not be enough
- many databases support more than 2 billion rows in a table. The
RECORDS64 command is a new command which
returns a
BIGINT variable (at the time of
writing this, this is a
REAL.)
If this command is called against a traditional driver then the regular
RECORDS command is called, and a value less
than 2.148 billion is returned. So this new command is backward
compatible with existing drivers.
See also
Bytes64 and
Pointer64.
REGET
Reget(FILE pFile, String pPosition)
Reget(KEY pKey, String pPosition)
The
REGET command reloads a row from the
FILE based on the
Position
parameter. The
Position is a string that has
previously been populated with a call to the
POSITION
command.
The
REGET command does
NOT
observe the
prop:Project
property. In other words it always loads all the fields in the record
buffer. Calling
REGET will clear the
prop:Project property.
The
REGET command does
NOT
support the length parameter. See
DOS2 for
more.
Traditional SQL drivers do not support
Reget(File).
Object based drivers treat this as
Reget(PrimaryKey)
so it is supported. Indeed since all calls to
REGET
(regardless of the key specified) are treated as
Reget(PrimaryKey),
they are effectively all equivalent.
See also :
RESET .
Relationships
In many aspects good SQL advice is limited to the specific context where
that advice works. It is critical to understand your own context well in
order to make good database design decisions. And perhaps no area in
database design is as contentious as relationships. They are enormously
useful in some cases, but equally unhelpful in others.
From a database perspective relationships allow the developer to define
connections between tables. This is primarily used for two reasons;
- To maintain data integrity (ie preventing "bad" data from being
added to the database and
- To improve performance when updating primary key values, or
deleting associated records.
For example, say there is an
Invoices table,
and each invoice contains multiple line items. If an invoice is deleted
from the system, then it makes sense to cascade the delete to the child
(
LineItems) records. In some relationships this
is desired. In others (for example, deleting a customer) you do not want
to cascade the delete (deleting
Invoices when
deleting
Customer would be bad.) And in some
cases you would want to prevent a row (say the
Customer)
from being deleted if child records (
Invoices)
exist.
With traditional (and object based) drivers all of this can be done in
the client program, and indeed Clarion is set up to support client-side
data integrity in this way. But doing it on the client can be slow,
especially if a lot of rows in different tables are updated. For example
changing the Unique ID value for a
Product
might result in millions of
LineItems rows
needing to be updated. Object based drivers add the possibility of doing
this work on the server, not the client.
Of course all magic comes at a price, and database relationships are no
different. There are two areas where having a relationship comes at a
cost;
- There's a performance penalty when adding or updating records. The
fields in the relationship need to be checked, and naturally that
comes at a cost.
- In distributed-data systems (think data on phones being
synchronized to a SQL database) the order of synchronization
suddenly becomes important. Parent tables have to be synchronized
before child tables.
So choosing to use relationships, on the server side, is a question of
balancing benefits in some cases with costs in others. There is no one
right answer which will work in every situation.
Traditional drivers make no attempt to support server-side
relationships, those are left to the developer to manage outside of the
program. Object based drivers add a command
ADDRELATION
which allows the developer (or template) to inform the file driver of
the desired relationships.
For some database drivers (most notably SQLite) this call should be made
before the table is created. The relationship is baked into the table
creation, and changing that afterwards can be "expensive". For most
other databases, relationships can be created (and updated) on the fly.
Relationships consist of a parent table, a child table, action on
update, action on delete, and the list of one or more linking field
pairs. Up to 10 field pairs are supported.
Relationships make some table maintenance more difficult. For example,
dropping a table is problematic if other tables are related to this
table, and contain child data. The common technique of using
REMOVE
or
EMPTY to empty a table, then
repopulating it with some data fails because you may not be allowed to
delete parent records, or worse, the delete cascades to the children (or
the children are cleared.)
So in some cases, especially when table maintenance is being done, it's
necessary to suspend and later on resume relationship checks. The new
commands
SUSPENDRELATIONS
and
RESUMERELATIONS have
been added to give the programmer more control. Some databases do this
globally, on a connection or session basis, or on a table by table
basis. Be aware that if it's done globally then this could affect other
threads in the program or other programs. Check your driver specific
documentation to fully understand the implications of these commands.
And, of course, use them only when necessary.
REOPEN
Reopen(FILE pFile,LONG pNewMode)
If the file is already open, then it can be reopened with different
access rights.
This is an alternative to calling
CLOSE and
then
OPEN with a new access mode parameter.
This bypasses all the clean-up that usually occurs when doing a
CLOSE
or
OPEN. Use this to change the access
mode, but without affecting other properties or record buffers that may
be set.
If the reopen is changing to a read-only access mode, then
prop:readonly
is set to true. If the reopen is changing to a read/write access mode
then
prop:readonly is left
unchanged.
If the file is currently closed, then
REOPEN
behaves like a normal
OPEN, except that the
File Name property is not updated (if the File Name property is a
variable.)
Example
Reopen(Customers,42h)
Customers{prop:readonly} = false
If the
REOPEN command is called against a
traditional driver then the
Errorcode() is
set to
NoDriverSupport (80).
REMOVE
REMOVE(FILE pFile)
The
REMOVE command for object based drivers
is the same as for traditional drivers. It deletes the table from the
database (in SQL terms it does a
DROP TABLE.
There is however an additional consideration when using
REMOVE
in a SQL context, where server-side relationships are active. If you are
using
server-side relationships then the
use of
REMOVE can be damaging. If called
when relationships are active then the defined
On
Delete behavior may be executed by the database. This includes
cascading deletes, clearing of child fields, and so on. Indeed the call
may fail for one, or more, rows if there is a
Restrict
limitation placed on deleting the row. This may not be intended
behavior.
If the call to
REMOVE is part of a wider
process (to drop the table, and then recreate it) then strong
consideration should be given to suspending relational updates (using
SUSPENDRELATIONS).
See also the
EMPTY command which
has similar implications, and the section on Server Side
Relationships
in general.
RENAME
RENAME(FILE pFile, STRING pNewFileName)
The
RENAME command is not supported by
traditional SQL drivers. However object based drivers allow the RENAME
command to be implemented by the driver. The following is the expected
behavior when used by a SQL driver;
This command renames the table in the database. Note that only the table
may be renamed. The keys may be renamed by the driver, or they may not
(depending on what the database requires.)
The table can be renamed if it is OPEN however note that the Full Path
Name setting for the table is not changed. Thus subsequent operations on
the table may fail if the Full Path Name is not updated at the same
time. (Unless the next command is a
CREATE.)
If the destination table already exists, then an error
Access
Denied (5) is generated.
RESULTGET
RESULTGET(FILE pFile, long pRow)
RESULTGET(FILE pFile, BIGINT pRow, long pColumn)
RESULTGET(FILE pFile, BIGINT pRow, long pColumn,
*Byte pNull)
RESULTGET is one of the functions that lets
the program access the result set directly.
RESUMERELATIONS
RESUMERELATIONS(FILE pFile)
This ends a matching
SUSPENDRELATIONS
command. Calling
CLOSE also automatically
triggers the end of the
SUSPENDRELATIONS
command. If
SUSPENDRELATIONS has not been
called then this does nothing. If
SUSPENDRELATIONS
has been called multiple times, then this resumes all of them. (In
other words there is not a counter counting Suspends and Resumes.)
Note that most
SUSPENDRELATIONS commands
apply to a connection, or the entire database (consult your driver
documentation for specifics.) However to work correctly it is necessary
to call
RESUMERELATIONSON for the same
table as
SUSPENDRELATIONS.
Calling
RESUMERELATIONS without a preceding
SUSPENDRELATIONS is meaningless and
harmless. No Error will be generated.
See also
SUSPENDRELATIONS
and
Relationships.
ROLLBACK
ROLLBACK(FILE pFile, STRING pSavePoint)
ROLLBACK(FILE pFile)
If
LOGOUTCONNECTION is
used to start a transaction, then
ROLLBACK
MUST be called with the primary table. Calling
ROLLBACK
with no parameters in this situation will cause a GPF.
For more information on rolling back to a save point, see
SAVEPOINT
Saving State
The templates (both ABC and Clarion) generate FILE structures as Global,
usually threaded, structures. This has a number of advantages, but also
means that there can be side-effects when a procedure is using the FILE
structure, and calls another procedure that uses it as well.
To help with this problem Clarion has three commands (
GETSTATE,
RESTORESTATE,
FREESTATE)
used to save the state of a
FILE so that
it can be restored later. This allows procedures (either the caller or
the callee) to snapshot the state of the structure, and then return it
to that state, thus ensuring that there are no side-effects.
For ISAM drivers this works well, and can be "perfect". However for SQL
drivers there are limitations, precisely because another program is
involved (the SQL server) and it does not necessarily have the ability
to do the same. Fortunately these limitations seldom matter to the
program.
It's also worth noting that saving a state is "expensive" in RAM terms.
There's quite a lot of information (properties, result set and so on)
which are stored. When you no longer need the state
FREESTATE
should be called.
FREESTATE is not called
automatically when
CLOSE is called
(because the state transcends the
FILE
open status.) It will be freed when the thread ends, but doing so when
it is no longer needed is recommended.
SAVEPOINT
SAVEPOINT(FILE pFile, STRING pSavePoint)
SAVEPOINT is a new command that can be used
inside a transaction (ie between a
LOGOUT
and a
COMMIT). It places a "marker" (the
pSavePoint parameter) into the transaction.
This allows a
ROLLBACK just to
that save point, rather than all the way back to the beginning of the
transaction.
For Example (this code is obviously highly abbreviated);
Logout(0,Invoices,LineItems) ![1]
Loop
Add(Invoice)
SavePoint('Invoice' & Inv:id)
Loop
Add(LineItem)
If Errorcode()
Rollback('Invoice' & Inv:id)
Break
End
End
End
Commit()
Note 1: For SQL
drivers actually listing all the tables is obsolete. See
Transactions
for more information on that.
SEARCH
SEARCH(FILE pFile, STRING pSearchString)
SEARCH(VIEW pView, STRING pSearchString)
Object based drivers introduce ranked
Full Text
Search features to the driver. This is done with the
SEARCH
command. This command is effectively a complex filter, and is applied
between the
SET command and the
NEXT
and
PREVIOUS commands.
Result sets are ordered by rank - in other words the most relevant
results are returned first.
Note that Full Text Search is not like
INSTRING
or
LIKE. It's not matching a string of
characters, but rather one or more whole words. Searching for the word
data does not return a match for
BackupData
or
DataManager - only for columns that
contain the whole word
data. The search
is not case sensitive.
By default the full text search is created across all the
string,
pstring,
cstring,
memo and
blob
fields in the table structure. Fields marked as
BINARY,
UUID or
BASE64
are excluded. Fields with a
Custom Field
Interface are excluded.
The syntax for the search string can be simple, but it can also be be
made more specific using a well formed query. The following operators
are supported;
| Operator |
Description |
| OR , | |
The OR operator (or
alternatively a pipe symbol can be used) returns a result set
where either of the terms is present. |
| AND, +, & |
The AND operator (or
alternatively a plus or ampersand symbol can be used) returns a
result set where both of the terms is present. |
| NOT, -, !, ~ |
The NOT (or alternatively a
minus, tilde or exclamation symbol) removes any row with this
term from the result set. |
| "text" |
Double quotes are used to indicate phrases which need to be
matched in the row. |
| ( ) |
Parenthesis are used to indicate operator precedence. |
All of the above is a formal way to say that the search string behaves
the way you would expect. Here are some examples;
Return result sets containing the word
RED or the word
BLUE.
'red | blue'
'red or blue'
File Example
Set(Pro:NameKey)
! [1]
Products{prop:filter} = 'Pro:Active = 1' ! [2]
Search(Products,'NetTalk')
Loop
Next(Products)
If Errorcode() then break;
! whatever
End
[1] : The result set will be order by relevance (known as the
rank)
first, and then the sort order specified by the
SET
will be applied after that.
[2] : The
SEARCH is in addition to, not in
place of, all the regular
SET behaviors. So
all the other properties that apply to the
SET
are still in play.
View Example
Find all the customers with the word
INC
in the record. The most relevant records are returned first.
Set(CustomersView)
Search(CustomersView,'inc')
Loop
Next(CustomersView)
If Errorcode() then break.
! whatever
End
Field Types
Fields in the row that are searched have to be text, not binary
fields.
If you have a BLOB or MEMO in your dictionary, and
that field is not marked as BINARY,
then the driver will create it as a TEXT type field (or similar) when
the table is created in the database. If the field is declared as a
TEXT (or similar) then it can be searched using full text search.
BLOB (or STRING, PSTRING) fields that are marked as
BINARY in the dictionary, and therefore created as a BLOB (or similar)
in the database cannot be used in a full text search.
SEND
SEND(FILE pFile, STRING pCommandList)
The
SEND command is used to send
instructions, and set values to the file driver which control the way
the driver behaves. For more information see
Driver
Options.
The command list is a space-separated list of commands, which may, or
may not, need associated values.
If the
SEND command is used without
specifying a value, then the current value of the setting is returned
and the value is unchanged. If a value is passed then the value after
(attempting) to set the value is returned. Some databases will not
accept a setting, so the setting, as used by the database, is returned.
(In other words, if the value returned does not equal the value
returned, then the option failed to set for some reason.)
The
SEND command can accept a
space-separated list of settings. If multiple settings are set at the
same time then only the value of the last setting is returned.
Note: Because it accepts a space separated
list care should be taken not to include extra spaces, such as before or
after the = sign. If spaces in the name or value are necessary, then
enclose the term in double quotes. For example;
Send(Customers,'/pragma_foreign_keys=ON')
! good
Send(Customers,'/pragma_foreign_keys = ON') ! bad
Send(Customers,'"/pragma_foreign_keys = ON"') ! good
SET, RESET, POSITION
SET(FILE pFile)
SET(FILE pFile, KEY pKey)
SET(FILE pFile, LONG pOffset)
SET(KEY pKey)
SET(KEY pKey, LONG pOffset)
SET(KEY pKey, KEY pKey)
SET(KEY pKey, KEY pKey, LONG pOffset)
The
SET command in Clarion comes in seven
different flavors. Fundamentally it breaks down into combinations of 3
parameters;
- The order to move through the table
- The start position
- The offset in the table to start from
For traditional drivers the
OFFSET
parameter is a
LONG, and thus has a
maximum value of 2.1 billion. If your
OFFSET
is, or may be, greater than this number then create a variable of type
BIGINT, populate that with the larger number,
and then call
SET with that variable as the
OFFSET parameter. Using this form will
ensure that the whole
BIGINT value is
passed to the driver. This is especially important when using the
DOS2 driver on files larger than two gigabytes.
For example;
ptr BIGINT
code
ptr = ! some number, perhaps > 2 gigs
SET(Customers,Ptr)
The traditional SQL Drivers do not support all seven forms of the
SET command. Typically they only support
SET(key) and
SET(key,key).
The other forms of the
SET command,
especially setting any kind of
OFFSET are
not supported. Object based drivers however do support all these forms.
Traditional drivers do not support
prop:Order
or
prop:SQLOrder when doing a
SET
on a File, object based drivers do.
The following points explain the interactions, and limitations of the
object based drivers new additional features;
- When no key (and no prop:Order or prop:SQLOrder) is specified (ie SET(File)),
then the Primary Key is used. So this is equivalent to calling SET(PrimaryKey) . However if SET(File)
is used and the prop:Order or
prop:SqlOrder are set then
those properties are used for the ORDER BY
clause.
- When a
OFFSET parameter is
used then this translates into an OFFSET-1
in the SQL Result Set.
This is different to setting prop:Offset,
as that requires prop:Limit to be set
as well, whereas the OFFSET parameter
applies to the result set even if prop:Limit
is not set. If prop:Limit is set, and
prop:Offset is set then prop:Offset
has precedence over the OFFSET
parameter.
Note that the OFFSET reflects the row
number that will be selected first. In other words Offset-1 rows are
discarded. So if OFFSET is set to 5
then the first record read by the NEXT
command will be the fifth record in the sequence.
By contrast, prop:Offset indicates the
number of rows that are skipped. If Prop:Offset
is set to 5, then the first row read by the NEXT
command would be the sixth row in the result set.
- If the SET(key,key) form of SET is
used, and prop:Order or prop:SQLOrder
are used, then those fields identified in prop:Order
and prop:SqlOrder will behave the same
as fields in the key. In other words the current value of the fields
will be taken into account when constructing the result set, and the
current values will be considered as the "starting position".
- If prop:SQLOrder is set, and it
contains a SQL calculation, then the built-in paging on the result
set is not possible, and the whole result set will be returned in a
single request. Consider using Prop:Limit
in this situation.
- The following properties are used by calls to SET
and RESET. They are NOT CLEARED after
the call, they must be manually cleared if necessary before future
calls to SET. They ARE cleared by a
call to CLOSE or CLEARPROPS
prop:Distinct, prop:Filter, prop:Limit,
prop:NotDistinct, prop:Offset, prop:Project, Prop:SqlFilter,
Prop:Order, Prop:SqlOrder.
Reset
SET(FILE pFile, STRING pPosition)
SET(KEY pKey,
STRING pPosition)
Despite the name, it's best to think of
RESET
as just another form of the
SET command.
It is possible to call
RESET without
calling
SET, so using
RESET
does not require a
SET . Rather
RESET simply adds another way of doing a
SET, and in all respects behaves in exactly
the same manner.
The initial values used by the
SET
command are not in the record buffer (as they are with a call to
SET) but rather in the
Position parameter passed into the
RESET command. The
Position
parameter is the result of an earlier call to
POSITION.
See also
REGET.
Position
POSITION(FILE pFile)
POSITION(KEY pKey)
Reset is based on a value returned by
POSITION.
In traditional drivers,
POSITION in turn
is based on the contents of the record buffer when it is called, and
importantly on a specific key when it is called. In other words a key
is chosen when calling
POSITION and that
same key needs to be used when calling the
RESET
command.
For example;
GET(file,5) ! it doesn't matter how the
record is fetched
p = POSITION(file:PrimaryKey)
RESET(file:PrimaryKey,p) ! works
RESET(file:OtherKey) ! fails
SQL object based drivers
SET supports
prop:Order and
prop:SQLOrder.
Since
RESET should be considered as a
form of
SET, it makes sense that
POSITION
should also support
prop:Order and
prop:SQLOrder[1].
However the only way to do this is to have
POSITION
return the "whole contents of the record buffer" (which is what it
does.)
In other words, the length of the string returned by the record buffer
is now the size of the record, not some size determined by the Primary
Key and selected key. While this requires more memory for the saved
position, it has some beeficial side effects;
POSITION
and
RESET are faster, and since the
POSITION contains the whole position, the
RESET can be done on any key, and is not
limited to the key, or
prop:Order set at
the time of the call to
POSITION.
Note 1: prop:SQLOrder
allows for calculated fields to be included into the
ORDER
BY clause. Since these clauses are outside the record buffer
they cannot be captured by
POSITION.
Hence if calculated fields are used in the
prop:SQLOrder
property then the
RESET position may not
match the actual record position, and thus in this case
RESET
cannot be used.
SET / NEXT Loop
From the earliest days of Clarion, embed code (and hand-code generally)
has used loops to walk through a FILE, in a pattern known as the SET /
NEXT Loop.
The basic pattern looks something like this;
Clear(Invoice)
Set(Inv:DateKey)
Loop
Next(Invoice)
If Errorcode() then break.
! do something
End
This code is ubiquitous and works well with ISAM files, but is not very
SQL friendly. A slightly expanded form of this code follows the same
approach;
Clear(Invoice)
Inv:Date = Date(1,1,2024)
Set(Inv:DateKey,Inv:DateKey)
Loop
Next(Invoice)
If Errorcode() then break.
If inv:Date > Date(1,31,2024) then break.
! do something
End
In the above code all the invoices for January are fetched. Again this
code is very fast in ISAM but also very inefficient in SQL. When
converting a program to SQL it is usually recommended to change this
code to use a VIEW. However with object based drivers it is possible to
make a small tweak to make this a lot more efficient for SQL back ends.
To understand why, it's important to understand that VIEWs have two
major efficiency benefits over a SET/ NEXT loop. Firstly they allow you
to limit the number of fields populated with each iteration through the
loop. If you are only going to use a few fields out of a lot of fields,
then this is good to do. Secondly they allow the result set to be
bounded by using a Filter.
Object based drivers add support for a number of old, and new,
properties to the SET/NEXT loop which better informs the engine on the
goals of the loop, and this results in code which is more efficient in
the database.
All properties should be set (or cleared) before
the call to SET . Unless otherwise
specified properties are not cleared by
the call to SET and must be manually cleared (if desired) before the
next set. Changing these properties during a loop is not recommended
(except where documented below.)
To ensure that properties set by previous calls do not affect the SET
/ NEXT loop, start the loop be calling ether Clear(file)
or ClearProps(File).
Prop:ClearProps
Calling
prop:clearprops sets all these
properties back to their default value (null or false.) This is the
same as calling
ClearProps.
customers{prop:clearprops}
Prop:Explain
A
SET command, along with all the
properties described here, result in a SELECT statement being sent to
the database.
If your loop runs slowly, it can be useful to see the way in which the
databases uses the statement. And specifically if it is able to make
use of keys on the table to best generate the result. prop:explain can
be set, before the call to SET, to get information from the database
regarding the query.
Prop:Explain is cleared automatically by a call to
SET.
The
SET still functions, and the code
runs as normal. The output is dependent on the driver.
For more information on this see
Explain.
Prop:Filter
In the example code at the top of this section, a
SELECT
statement is generated and a
Result Set
is constructed by the server. Using the
SET(key,key)
line sets the starting point of the result set, but crucially there's
no end point defined. So the server builds a result set of all the
invoices since Jan 1, 2024 - whereas we are really only interested in
the invoices actually in January. Letting the server in on that little
bit of information allows the server to build a smaller result set.
Object based drivers allow for this by adding support for the
prop:filter
statement. So a small tweak to the code becomes;
Clear(Invoice)
Inv:Date = Date(1,1,2024)
Invoice{prop:filter} = 'Inv:Date <= Date(1,31,2024)'
Set(Inv:DateKey,Inv:DateKey)
Loop
Next(Invoice)
If Errorcode() then break.
If inv:Date > Date(1,31,2024) then break. ! it doesn't
matter if this line is here or not
! do something
End
This is a lot simpler, and quicker to do than refactoring the code
completely to use a VIEW.
The code could also have been translated to this;
Clear(Invoice)
Invoice{prop:filter} = 'Inv:Date =>
Date(1,1,2024) AND Inv:Date <= Date(1,31,2024)'
Set(Inv:DateKey)
Loop
Next(Invoice)
If Errorcode() then break.
! do something
End
All the same rules that apply to
View{prop:filter}
apply here, and indeed
File{prop:SQLFilter}
is also supported as well.
If you use a
SET(key,key) form of the SET
statement, and also a Prop:Filter then the Prop:Filter will be
appended to the key filter. So if the key is
on say
Inv:Date, Inv:Time, and you set
Prop:order = 'Inv:Total > 0' then the
result is filtered by
Date, Time, Total.
If you use the
SET(file) or
Set(key)
form of the SET statement then the prop:filter will be used alone (as
there are no key fields.)
Various Clarion and SQL functions can be used in a filter. See
View
Filter Functions for more.
Prop:Limit
Another way to limit the result set generated by the SET / NEXT loop
is to use the
Prop:Limit setting. This is
useful in those cases where the number of records you want is limited.
For example, here's code to get the top ten invoices for the year;
Clear(Invoice)
Num = 0
Clear(Inv:Total,1)
Set(Inv:TotalKey,Inv:TotalKey) ! Total Key is descending highest to
lowest
Loop
Next(Invoice)
If Errorcode() then break.
If Num > 10 then break.
If Inv:Date < Date(1,1,2024) or Inv:Date > (12,31,2024)
then break.
Num += 1
! do something
End
Clearly we can use
prop:Filter to
optimize this, but
prop:Limit will also
be helpful;
ClearProps(Invoice)
Invoice{prop:filter} = 'Inv:Date =>
Date(1,1,2024) AND Inv:Date <= Date(1,31,2024)'
Invoice{prop:Limit} = 10
Set(Inv:TotalKey,Inv:TotalKey)
Loop
Next(Invoice)
If Errorcode() then break.
! do something
End
Prop:Limit is used to limit the result set
to a specific number of records.Use of
Prop:Limit
implies the same behavior as
prop:Paging =
false (the whole result is loaded into ram in one bite.)
Prop:Limit works for FILEs and VIEW's. It must be set (or cleared)
before the call to
SET. It is not reset
automatically (except on an
OPEN ,
CLOSE ,
CLEAR,
CLEARPROPS, where it is reset to false.)
Changing the value of this property to not-zero while inside a SET /
NEXT loop will have the effect of terminating the SET/NEXT loop at the
end of the current page. Changing the value of this property to zero
inside the LOOP has no effect.
Prop:Offset
If you wish to skip a number of records at the start of the result
set, then you can use
prop:offset to do
so. All the FilePointer forms of
SET also
implicitly set the
prop:offset to the
passed-in
FilePointer value.
Prop:Order
In the example at the top there was helpfully a key (
TotalKey)
which was on the field we wanted to sort by, and also helpfully it was
a descending key. In real-life we probably aren't that lucky. In which
case you likely have already converted this code to using a VIEW (or
worse, you are loading the whole thing into a Queue and then sorting
the Queue.)
Fortunately though object based drivers support
Prop:Order.
So you can use an ad-hoc ordering scheme where you don't have a key.
ClearProps(Invoice)
Invoice{prop:filter} = 'Inv:Date => Date(1,1,2024) AND Inv:Date
<= Date(1,31,2024)'
Invoice{prop:Limit} = 10
Invoice{prop:Order} = '-Inv:Total'
Set(Invoice)
Loop
Next(Invoice)
If Errorcode() then break.
! do something
End
If you use a
SET(key) form of the SET
statement, and also a
Prop:Order then
the Prop:Order will be appended to the Key Order. So if the key is on
say
Inv:Date, Inv:Time and you set
Prop:order = 'Inv:Total' then the result is
ordered by
Date, Time, Total.
If you use the
SET(file) form of the SET
statement then the prop:order will be used first (as there are no key
fields.)
Internally a unique order is required. If necessary the engine will
add the primary key fields to the end of the order, to make it unique.
Prop:Order (and prop:SQLOrder) work for FILEs and VIEW's. It must be
set (or cleared) before the call to
SET.
It is not reset automatically (except on an
OPEN
or
CLOSE, where it is reset to a blank
string.) Changing the value of this property while inside a SET / NEXT
loop will have no effect on the loop.
prop:Paging
Traditional drivers make use of a SQL Cursor to build a result set in
the database. The driver can then access that cursor to easily move
forward and backwards in the result set, update records and so on.
This is a simple and effective approach.
There are however disadvantages with this approach. It requires the
server to build the whole result set on the server, even if only a
small number of records will actually be retrieved. For example, a
Browse on a Customers table will trigger a (potentially large) cursor
on the back end, even if the user only views the first page of
results. And since that user may sit on that browse for some time, the
whole cursor is maintained in the database for that time, consuming
server resources.
Object based drivers take a different approach. Result sets are
constructed as "page requests". So a limited number of rows are
requested from the server, these are fetched, and then the server is
free to carry on as normal. After the request is made (and fulfilled)
no resources are consumed on the server.
However all magic comes at a price. There is some overhead in both
creating these pages, and in calculating the next page to fetch. If
the result set is known to be "small" (ie will fit into client-side
memory) then
prop:Paging can be set to
false. This allows the engine to bypass some
code and so slightly improve performance. It also results in one less
query to the database (since there's no "empty last page" to fetch.)
Prop:Paging works for
FILE
and
VIEW structures, and is set to
true by default. It must be set (or cleared) before the call
to
SET. It is not reset automatically
(except on an
OPEN ,
CLOSE
,
CLEAR,
CLEARPROPS,
where it is reset to true.) Changing the value of this property to
true or
false
while inside a SET / NEXT loop has no effect.
Prop:SqlFilter
Prop:SQLFilter is supported.
Prop:SqlOrder
Prop:SQLOrder is also supported. However
note that in the
FILE context, there is
no need to (and you should not) use a SQL Alias (or table name) when
identifying the field(s). This is different to the traditional drivers
which
require the use of the SQL Alias.
The use of SQL Aliases in the property is allowed with object based
drivers, and it will work, but this technique is brittle.
There is a catch though.
Prop:SQLOrder
allows for the result set to be sorted by a calculated value. For
example;
ORDER BY YEAR(CURDATE()) - YEAR(birthdate) ASC;
However it should be noted that since these values are opaque to the
driver, it's not possible for the driver to reliably "page" though the
file when this property is used. For this reason when making use of
the
Prop:SqlOrder field the entire
result set is returned. Consider using
prop:Limit
in cases like this to put some upper bound on the number of records
returned. This is only an issue when using a calculated value in the
order.
Prop:ReadOnly
When a record is read from the databases, some work is done to ensure
that a subsequent
PUT or
DELETE
command will be able to update that same record in the database. If
prop:ReadOnly is set then this work can be
avoided.
Since this property is used at the Table level, it is not cleared by
prop:Clear, nor is it reset in any way. So if
you use this to improve performance in a SET / NEXT context you
must clear it again after loop is complete.
If you attempt to do a
DELETE or
PUT
on a row loaded when this property was set then a
Record
Not Available (33) error is returned.
NOMEMO
Doing a SET/NEXT loop fetches all the fields in the table. If the
table includes
MEMO and/or
BLOB
fields, and you are not planning to use those fields in your loop,
then you can improve performance by using the
NOMEMO
command. Traditional SQL drivers do not support
NOMEMO,
but object based drivers do.
If you do a
PUT inside th
NOMEMOe
loop, and is active, then the
MEMO
and/Or
BLOB fields are not updated.
NOMEMO is automatically cleared by a call to
GET
or
REGET. It is also cleared by a call to
SET, however it will remain "active" for
the duration of the SET / NEXT loop. Changing this property inside the
loop has no effect on the results returned by the loop.
Subsets
The easiest way to think of a subset is as a complex filter. A filter (
prop:Filter) is great at getting a subset of
records into a result set. They work well when the decision of whether
to include a row or not can be determined simply by looking at the row.
So to get all the records in a specific date range, you need to look no
further than the date column.
There are a group of queries though that need to go deeper. Once the
condition lies outside the row itself, then
subsets
can become useful. SQL allows multiple results sets to be created, and
then those results combined (in various ways) to make a single result
set. The four operations are
UNION (and
related
UNIONALL),
EXCEPT
and
INTERSECT.
Command
|
Description
|
| EXCEPT |
Subtracts sets (A - B). |
| INTERSECT |
Finds common elements (A ∩ B) (removing duplicates) |
| UNION |
Combines sets (A + B) (removing duplicates) |
| UNIONALL |
Combines sets (A + B) (keeps duplicates).
If you know your data is unlikely (or unable) to contain
duplicates then UNIONALL is somewhat faster on the server
because the server then does not check for duplicates. |
The result of each operation results in a result set, and the next
operation acts on that set. So it's possible to start with a result set
(
UNION command) then add another set (
UNION) remove two sets (
EXCEPT
,
EXCEPT) and follow that up with another
UNION and an
INTERSECT.
A subset contains just enough fields to identify the rows that are
included (or excluded.) In practice this mostly means that the primary
key fields of the primary table are used. (However some other
combination of fields in the primary table could be used). Each command
in the subset (
UNION,
EXCEPT,
INTERSECT,
UNIONALL)
MUST return the same set of fields (same number, same type). The field
names might be different, but they are ultimately affecting a single
list, so they must match.
For example, say there is a list of customers. The goal is to find out
which good customers (customers who purchased something in 2022 and
2023) did not purchase anything in 2024.
To do this the program has to look at the customer list as a whole,
including customers who purchased in 2022, and 2023, but excluding those
who purchased in 2024.
Here's another example; You have a list of employees and need to know
which employees took no sick leave this year. In other words, take the
list of employees, and remove from that list any employee that took sick
leave.
A third example involves just the primary table. Each employee has a
manager (which is denoted by the
ManagerId
field). Each employee has a
role (for some
it's "Manager") and each employee has a
location
(for some it's "New Jersey".) The goal is to find all the managers who
do not manage someone located in New Jersey.
All three of these examples will be fleshed out below.
A Subset starts with a usual
SET command on
the table of interest (this is known as the
Primary
Table). In the first example it was
customers,
in the second and third it was
employees.
The subset is created using the (explicit, or implicit)
SUBSET command. This command indicates the key
identifiers of the parent table which constitute the list. In the first
example this would be the
CustomerId, in
the second and third the
EmployeeId. This
command might bb implicit, meaning that you don't need it at all. If
it's not used then the primary key fields of the primary table are
assumed. In that case the subset is created by using one of the
commands.
Then the subset of identifiers can be built using any number (and
combination) of the
UNION,
UNIONALL
,
EXCEPT and
INTERSECT
commands.
UNION adds rows,
EXCEPT
removes them, and
INTERSECT joins them. All
these commands only add or remove unique values. If you wish to add
non-unique values then you can use
UNIONALL
.
Example 1
Which customers purchased something in 2022 and 2023 did not purchase
anything in 2024?
Set(Cus:NameKey)
Subset(Customers,Cus:Id)
! Optional since the Cus:Id is the Primary Key
Field.
Subset(Orders,Ord:CustomerId)
Union(Orders,'Year(Ord:date) = 2022')
Intersect(Orders,'Year(Ord:date) = 2023')
Except(Orders,'Year(Ord:date) = 2024')
Loop
Next(Customers)
! etc
End
Let's work through this example;
Firstly the
SET command behaves as it
always does. It sets the starting point, sort order, filter, project
list and so on. That part is unrelated to the subset part that follows.
The
SUBSET command binds a table, and
identifying columns to the commands that follow. In this example
two tables are involved. The first is the
Customers
table (consider this to be the
primary table).
The field(s) are the identifying fields which are in play. If the
SUBSET for the primary table is not included,
then the primary key fields for the primary table are assumed. (In
this example it is specifically included for clarity, but it could be
omitted.)
The second
SUBSET command adds the
Orders
table, and indicates the foreign key fields(s) which match the primary
table. In this case
Ord:CustomerId matches
the
Cus:Id field. Note that the fields
don't need to be the same name, but they do need to be the same type.
They also need to be the same number of fields, no more and no less. If
the primary table has three fields in the primary key, then three fields
are needed here as well. The
SUBSET command
allows for up to 10 fields to be added.
Aside: In this example the subset is working
on a single foreign table, the
Orders
table. However this is not a limit - any number of foreign tables can be
used when creating the subset. The
SUBSET
command for each table only has to be called once for each table
regardless of the number of
UNION,
UNIONALL,
EXCEPT and
INTERSECT
commands used.
The next three lines determine which records are added and removed from
the subset. The
UNION commands add
customers that ordered something in the first year, the
INTERSECT
removes any that don't exist in the second year as well, and the
EXCEPT removes any which exist in the third
year.
The
LOOP through the resulting records then continues as
normal. Fundamentally all the subset does is act as a filter on the
SET /
NEXT loop.
Example 2
Which employees took no sick leave this year?
Set(Emp:NameKey)
Subset(Leave,Lea:EmpId)
Except(Leave,'Year(Lea:Date) = ' & Year(TODAY()) & ' AND
Lea:Type='''Sick''')
Since the first filtering command is
EXCEPT,
and not
UNION, the list starts with all
employees included. (This would also be true if the first command was an
INTERSECT, but if the first command is an
INTERSECT
then that's the equivalent of starting with
UNION
since again it starts with everyone, and remove anyone not in the
INTERSECT.)
This is in contrast to the earlier example which did start with
UNION,
and hence does not include anyone in the initial list.
So in this example the list starts with all employees, and then removes
any who took sick leave this year.
Example 3
Find all the managers who do not manage someone located in New Jersey.
Set(Emp:NameKey)
Union(Employees,'emp:role = ''Manager''')
Subset(Employees,Emp:ManagerId)
Except(Employees,'emp:location = ''NJ''')
In this case the list starts with a
UNION
to get a list of the employees who are Managers.
Because it did not start with a
SUBSET, the
SUBSET is implied. Since the primary key
field is
Emp:Id, that is the implied
SUBSET field on the primary table.
After the call to Union there is a
SUBSET
command, which is on the primary table, but indicates a field other than
the primary key field. This indicates that for the next commands,
the
Emp:ManagerId (which is the same type
as
Emp:Id, and ultimately contains an
Emp:Id value) is the subset field. (In this
case the employee has a
Emp:ManagerId
field indicating his manager.)
So all the Managers from the original list, are removed, for all the
employees located in New Jersey.
Limitations
There are some limitations to using subsets;
Firstly, all the tables involved need to be in the same database. At
this stage cross-database subsets (and by implication cross-driver
subsets) are not supported.
Secondly, if at all possible, either set prop:Paging
for the request to be false, or set prop:Limit as high as possible given the RAM
constraints of the program. Doing subset queries is (relatively)
expensive on the server side, so you do not want to do it, and then
only get a single page back (if the intention is to get the following
pages.)
Implied
Two statements in a Subset are implied in specific cases. These
simplify the calling code;
- If no SUBSET call is made, on the Primary table, then the
Primary Key fields are used. For example this line;
SUBSET(Customers,Cus:Id) ! This line is not
necessary
is not necessary (if Customers is
the primary file, and Cus:Id is the
primary key field.)
- If the first command (UNION, UNIONALL, EXCEPT
or INTERSECT) is a EXCEPT
or INTERSECT then the subset starts
with all the rows in the primary table. For example in this
sequence
UNION(Customers) ! This line is not
necessary
EXCEPT(Customers,'Cus:Status = 4')
it is not necessary to call the UNION
command, that is implied.
It should be noted that if the first command is UNION
or UNIONALL (on any table, not just
the primary table) then the inclusion of all the rows first is NOT implied.
Also implied is that a subset requires at least one command otherwise
it is not a subset. So calling
SUBSET ,
and then not calling any of
EXCEPT ,
INTERSECT ,
UNION
or
UNIONALL before calling the
NEXT
(or
PREVIOUS) commands simply results in
a normal
SET /
NEXT
loop. The
SUBSET commands are effectivly
ignored.
SUBSET
SUBSET(FILE pFILE,? pParm1,<? pParm2>,...,<? pParm10>)
The
SUBSET command binds a table, and one
or more fields, to the current subset. If not called at the start of the
subset, on the primary table, then the primary key fields of the primary
table are assumed. If
EXCEPT,
UNION,
UNIONALL,
or
INTERSECT are used to limit
the set, on a different table to the primary table, then
SUBSET
must be called first to bind that table, and related fields, to the
subset.
For more information on subsets see
subsets.
SUSPENDRELATIONS
SuspendRelations(FILE pFile)
The
SUSPENDRELATIONS command disables
Foreign Key integrity checking for the table. This potentially cascades
to other tables using this connection, or other threads in the program.
Consult the driver documentation for specific information.
This suspension is terminated by a call to
RESUMERELATIONS,
or a call to
CLOSE.
Use this command in cases where data in the table needs to be modified,
but relational updates should not be used. For example when using
REMOVE or
EMPTY
(where the intention is to put the existing data back) or where for some
other reason it's necessary to temporarily suspend relationships.
This command is independent of any (or no) calls to
ADDRELATION. It applies to the database, regardless
of when, how, or by who, the relationships were created.
See also
RESUMERELATIONS
and
Relationships.
SQL
SQL(FILE pFile, STRING pSql,<? pParm1>,<?
pParm2>,...,<? pParm20>)
A new command,
SQL, has been introduced as
a safer approach to calling embedded SQL statements. This replaces the
need for the
prop:Sql command, which is
inherantly vulnerable to SQL Injection attacks.
To understand the solution, it's best to first cover the problem.
Consider this statement;
Products{prop:sql} = 'SELECT * from Products where
name like ''%' & search & '%'';'
The problem here is that the statement is constructed from variables (in
this case search) which may contain harmful text. For example if this
came from a web site search bar, and contained the text
a%';
drop all tables; then the second command (
drop
all tables;) would be executed as well. Of course this second
command could be anything (none of it good.) This is known as a
SQL Injection attack.
Think of this SQL SELECT statement as a really tiny program. It's
written out as text, so the database "compiles" it, and then executes
it. Since the
drop all tables; command is
part of the program it gets compiled and executed.
The solution to this problem is to split the program part from the data
part. The program part is compiled, and then that compiled program is
passed, with the data, to the database for execution. Since the data
part is not compiled, it is not executed, and is safe (regardless of
what it contains.) This process is known as parameterization.
The
prop:Sql command is incapable of
performing parameterization because there is no way to separate the
"program" part and the "data" part of the statement.
The object based drivers offer a new command,
SQL,
which takes in the program part, and data part(s) as separate
parameters. In the program part, the parameters are represented as ?
n[1] where
n is the parameter number. The example above
translated to the SQL command is as follows;
SQL(Products,'Select * from Products where name
like like ''%'' + ?1 + ''%'';' , search)
Here's another example using two parameters;
SQL(Products,'Select * from Products where
name like ''%'' + ?1 + ''%''' and price >= ?2;' , search ,
minprice)
The SQL command supports up to 20 data values in the call.
If the same parameter is used more than once then the command can
contain the same
?n value, for example;
SQL(Products,'Select * from Customers where
firstname like ''%'' + ?1 + ''%'' or lastname like '%'' + ?1 + ''%''
;' , search )
Note 1: The exact
syntax of the SQL statement will vary from one database to another.
Note 2: The SQL statement can be called with
just the SQL string (ie without additional parameters), and so can be
used to execute any existing
prop:Sql
statement.
Note 3: The following is from the Clarion
documentation on
prop:Sql;
When you issue a SELECT statement using PROP:SQL,
the selected fields must match the fields declared in the named file
or view. In addition, if you use VIEW{Prop:SQL} to issue a SELECT
statement, the fields in the SELECT must be ordered based on the field
order in the file definition, not the PROJECT sequence.
In object based drivers no such care must be taken. The fields returned
can be in any order, whether working on a
FILE
or a
VIEW. The results are matched up to
the appropriate fields by
name. Indeed
any result set can be retrieved, and processed, even if it does not
access the file at all. See
Result Sets for
more on this.
SQL Reserved Words
In the beginning we had ISAM files, and life as good. We labeled the
fields in the table as anything we liked. Since there was no server
between the program and the data, there was no need for the concept of
limiting the names of tables or columns.
Along came SQL databases, and along with those the SQL language. SQL has
words which are used in the language, and so cannot be used as column,
or table, names. Dealing with this can be simple, or complicated - it
all depends on your context.
The first, and most important thing to understand is that the
NAME
and the
LABEL of a field are different
things. The label is the thing you use in your code. And the label can
be (almost) anything you like. Code that works against ISAM tables can
be the exact same code that works against SQL tables. SQL reserved words
do not affect your code. Do not read this section in the context of the
label - that is not applicable here - this section is the context of the
Column
name as stored in the SQL
database.
In the dictionary this is represented by the
External
Name setting for the column. Again, this is independent of the
label. If the external name setting is blank, then internally the name
is set to be the (upper case version of the) label, not including the
prefix.
There are two basic strategies that can be employed when you have a SQL
reserved word as the name of a column. Which one you choose will depend
on your context. Let's deal with the easy context first;
If the table is new to the database then simply changing the External
Name (or indeed, if it's really new, the label) to a non-reserved word
is the ideal. Figure out a different name for the column, and move on.
In the long run this is the best option because it makes ad-hoc queries
against the database (especially from non-Clarion programs) easier. If
you can do this, do it.
If, for whatever reason, the name of the column in the database is fixed
and cannot be changed, then the driver will wrap the word in quotes
[1][3] when it is used in a SQL
statement. You don't need to do anything, this happens automatically.
The only time it becomes a problem is when you start using the column
name in properties, SQL statements and so on. And of course when making
ad-hoc queries from other programs.
But wait, there's more. When you wrap the name in quotes like this, it
(often but not always) becomes case sensitive. In other words
"User"
is different to
"user" is different to
"USER". Again this isn't a train-smash, it just
makes using the name in ad-hoc SQL more precise.
If you have a reserved word as a label in your dictionary, and you do
not set an external name, then the driver has to choose a case for the
word. All the drivers (traditional, and object based) choose to make the
word uppercase in this situation. If you want to keep the name of the
column as-is, but at the same time make it not-upper-case, then you can
set the external name in the dictionary (with or without quotes
[2])
and the case of that setting will be maintained.
If after reading all of this, your conclusion is to avoid reserved words
as column names (either via the label, or via the external name) then
you have come to the right conclusion. If however it's too late for
that, then welcome to a pretty large club.
Note 1: Most
databases use double quotes but not all. Some use square brackets, or
back ticks, or whatever. This is mostly handled for you by the driver so
not something you need to worry about.
Note 2:
Traditional drivers
require that when
reserved words are used in External Names that the necessary quotes, or
brackets, or whatever are included. Object Based drivers do not require
this, and will use the appropriate delimiter when necessary. Object
based drivers don't mind the quotes or brackets being added in the
dictionary, but leaving them off allows the driver to use the
appropriate one as needed.
Note 3: The
traditional SQLite driver has a bug - it can
CREATE
a table using quotes around the external name, but it can't
OPEN
a table that has an external name with quotes. So with the traditional
SQLite driver it's necessary to set the External name to something
that's not reserved.
prop:SQLFilter
/ prop:SQLOrder
prop:SQLFilter and
prop:SQLOrder
allow you to inject absolute SQL code into the statement that is being
sent to the server. The code is sent as-is, and as such is not parsed,
processed, optimized or altered in any way. This is both a strength and
a weakness.
The strength is that it gives you very explicit control over these
clauses in the
SELECT statement. They can
either override, or append, the regular clauses.
The weaknesses though are significant, and so their use should be both
sparing and careful. You should use these only when all other options
have been exhausted. In no particular order;
-
They do not go through the parsing or binding engine, so care must
be taken to avoid security issues, especially the possibility of SQL
Injection. This becomes the developers responsibility, not the
driver's.
- They may be very dependent on the rest of the SELECT
statement. Since the SELECT may change (as
the driver evolves and is updated) this is a source of potential
future-compatible issues
- They don't change to take advantage of improvements in the SQL
engine. While the driver can be updated, bypassing the driver in
this way doesn't take advantage of the driver updates
- They are often bound to a specific database engine. This can make
changing engines harder. (While that's not a common task, it's an
important one when you need it.)
STREAM
While traditional SQL drivers did not implement
STREAM
(or
FLUSH), the object based
drivers do implement them to alert the database that a batch of writes
is about to occur. This allows the database to "wrap" the writes in a
block to improve performance. When upgrading programs that originated in
ISAM files (like Topspeed), which used
STREAM
and
FLUSH for performance gains, those
performance gains are now present in SQL as well.
Exactly how this wrapping occurs (if it occurs at all) is up to the
individual driver. Each database has different mechanisms for speeding
up bulk-write performance.
Unlike ISAM drivers, in SQL
STREAM does not
generally lock the table, however this is database dependent, so consult
your driver documentation to see if it has any locking effect.
You cannot call
STREAM while inside a
transaction. Doing so will result in the error code being set to
56 Logout Already Active.
For more information see the
Performance
section of this document.
Note: In practice, for most drivers, it's
likely that calling
STREAM starts a
transaction. Since transactions are based on the connection (not the
file) this also implies that any other files on the same connection will
also effectively be part of the
STREAM.
However
STREAM may do more than just
starting a transaction though, so for bulk writes it is recommend to use
it.
prop:Text
From the Clarion Documentation;
Prop:Text is an array property of a FILE that sets
or returns the specified MEMO or BLOB field's data. MEMO and BLOB
controls are negatively numbered, therefore the array element number
must be a negative value. MEMO and BLOB declarations begin with -1 and
decrement by 1 for each subsequent MEMO and BLOB, in the order in
which they appear within the FILE structure.
In other words,
prop:Text allows access to
set or get the value of a field, based on the field number in the
structure, and not based on the label.
Traditional driver do not support
prop:Text
on fields other than memos and blobs. If used on any other field type it
returns a blank string.
Object based drivers extend the support for
prop:Text
to all field types. It returns the text version of the field from the
current record buffer, as a string. (See
prop:Value
if you want the raw memory value). Behavior for memos and blobs is
unchanged.
If the field is an array then the "whole array" is returned, as a single
string value (usually null padded).
For Example;
s = customers{prop:Text,3) ! gets the text
version of the 3rd field in the Customers File
s = customers{prop:Text,-1) ! gets the text
version of the first memo or blob
customers{prop:Text,-1) = 'I saw a bear in
the wood' ! Sets the text of the first memo or blob
customers{prop:Text,4} = 1234 ! sets the value in the fourth field to
1234.
Transactions
Clarion offers three commands which facilitate transactions;
LOGOUT,
ROLLBACK
and
COMMIT.
The
LOGOUT command takes a
Seconds
parameter and a list of 1 or more
FILE
structures. The origins of this go back to the ISAM file drivers in
Clarion's early days.
The first, and most important thing to understand when dealing with
transactions in SQL is to understand that in SQL transactions do not
work across a subset of tables. Rather the transaction belongs to a
connection, and everything on
that
connection (and
only on that connection
[1]) is included in the
transaction - whether the file is listed in the
LOGOUT
command or not.
In object based drivers, connections are pooled on a THREAD basis - thus
transactions include commands performed on the thread, but do not
include commands performed on other threads
[2].
Since only commands executed on the same connection can be part of the
transaction, it follows that only commands to the same driver will be
included in that transaction.
Object based drivers introduce the concept of
Save
Points. This is done with the
SAVEPOINT
command, and with an optional parameter to the
ROLLBACK
command. Savepoints allow a partial rollback, back to a known good
location. None of the transaction is committed, but it allows part of
the transaction to be abandoned, and then the transaction can continue.
Traditional drivers will not allow multiple transactions (on the same
thread) to be active at the same time. This is self evident from the
nature of the
ROLLBACK and
COMMIT
commands - since they take no parameters it is not possible to
distinguish between transactions, and hence only one transaction is
allowed to be open.
Since the object based SQL drivers make use of server-side transactions,
where the transaction is connection based not table based, it is
possible to allow multiple connections, and hence possible to allow for
multiple transactions to be running at the same time. This is especially
valuable if two different databases are being updated at the same time.
Note though that these multiple transactions are not related. It's
possible for one to commit, and another to rollback. It should not be
assumed that all the open transactions end in the same way.
In order to support this behavior, a new
LOGOUT
command is used, called
LOGOUTCONNECTION.
A single table is passed into this command - this is referred to as the
Primary Table of the transaction. As above
ALL tables on this connection will automatically become part of the
transaction.
Equally, if a transaction is started with this command the
ROLLBACK
and
COMMIT commands MUST pass the same
primary table to the
ROLLBACK or
COMMIT
command. Failure to pass this parameter will result in a GPF.
If a
LOGOUT command fails and returns an
error code, it is no longer safe to then call
ROLLBACK
or
COMMIT. Calling either will result in a
GPF.
Open(Customers,42h) ! this will open on
the (default) connection pool 0
Products{prop:pool} = 1 ! this will open on connection
pool 1, not connection 0
Open(Products,42h)
LogoutConnection(Customers)
LogoutConnection(Products)
! some writes happen here
Commit(Customers) !
if Using LogoutConnection you MUST use Rollback(file) or Commit(file)
Rollback(Products) ! if
Using LogoutConnection you MUST use Rollback(file) or Commit(file)
Note 1: Connections
are pooled based on thread number, and owner string. If tables have the
same driver, and the same owner string, then typically they share a
connection. In some circumstances you may want to separate tables onto
different connections. You can do this by setting
prop:Pool
to a number other than 0. The number doesn't mean anything - but tables
with different
prop:Pool values will not
share a connection. Conversely tables with the same
prop:Pool
value will share a connection (if they're on the same thread, and with
the same owner string.)
For example;
glo:owner = 'some database connection string'
Customers{prop:pool} = 1234
Open(Customers,42h)
Products{prop:pool} = 5678
Open(Products,42h)
In the above code, the Customers and Products table will not share a
connection. By implication a
LOGOUT on one
will not include the other in a transaction.
Note 2: This
is assuming the file is THREADed. if the FILE structure is not THREADed
then
UNION
Union(FILE pFile)
Union(FILE pFile,STRING pFilter)
The
UNION command is used when creating
Subsets. It allows results to be added to the
current result set (not including duplicates).
It typically (although does not have to) follows a call to
SUBSET.
Example
Find all employees who took study leave, or who also participate in the
bursary program.
Set(Emp:NameKey)
Subset(Leave,lea:EmployeeId)
Union(Leave,'Type = ''study''')
Subset(Bursary,bur:EmployeeId)
Union(Bursary)
Other related commands are
EXCEPT,
UNIONALL and
INTERSECT.
UNIONALL
UnionAll(FILE pFile)
UnionAll(FILE pFile,STRING pFilter)
The
UNIONALL command is used when creating
Subsets. It allows results to be added to the
current result set, but includes a duplicate if the record is already
there.
It is a little faster than
UNION, so it can
be used in cases where the result is known to be unique. It can also be
used in cases where duplicates are explicitly desired.
Other related commands are
EXCEPT,
UNION and
INTERSECT.
UPDATE
Update(File pFile,String pFilter,?* pField1,String
pValue1,...,?*
pField10,String pValue10)
Update(Key pKey,?* pField1,String pValue1,...,?* pField10,String pValue10)
UPDATE is a new command, not present in
Traditional drivers. It is similar to a
PUT,
except that the record can be updated without the need to read it first.
Where the update does not require client-side code it is an efficient
way of bulk updating records on the server. Multiple fields (up to 10)
can be updated at the same time.
| Parameter |
Type |
Description |
| pFile |
File |
The label of the file to update. |
| pFilter |
String |
A filter expression which
expresses the records to update. If this is a blank string then
NO records are updated. If ALL the records should be updated
then this parameter should be set to driver:all. |
| pKey |
Key |
The set of rows to be updated is constructed from the key
fields, and their current values. If this form is used then the
pFile and pFilter
parameters are not used. If the key is not unique then all the
records matching the current key field values will be updated. |
| pField1..pField10 |
Any |
A field in the table. |
| pValue1..pValue10 |
String |
An expression containing
the new value. This expression needs to be capable of being
executed on the server. It can be a fixed value, or a
calculation based on the current value, or on other values in
the row.
Note that this value is considered to be an expression. It will
be evaluated. So a string that contains say a math value
('4+4+4') will be evaluated to 12. If you actually want to
actually set the field to be a string, |
Since the update is done completely on the server, the new value is
either a constant value, or an expression which can be executed on the
server.
This command is dangerous. Because it updates a number of records at the
same time, it's very easy to get the expression wrong, and thus affect a
large number of records with a single bad command. Some care should be
taken to ensure that the command is correct before unleashing it o
production databases. And, of course, as always, good backups of the
database are always recommended.
Fields in the file record are NOT updated with the values in the
database.
Some examples;
Update a field for all rows in the table to a fixed value.
Update(Customers,driver:all, Cus:Notify, 0)
Update a field for a single record, without reading the record first.
Update(Customers,'Cus:Id = 4',Cus:Overdue,true)
OR
Cus:Id = 4
Update(Cus:IdKey,Cus:Overdue,true)
Update a field, based on other fields in the row.
Update(Invoices,'Inv:date < ' & today()-30
& ' AND Inv:Paid = 0',Inv:Overdue, true)
Split a field, populating it into multiple new fields.
Update(Event,driver:all,Eve:Month,'Month(Eve:Date)',Eve:Year,'Year(eve:Date)')
UPSERT
Upsert(File pFile)
UPSERT is a new command, not present in
Traditional drivers. It is similar to an
ADD,
except that if the primary key value(s) already exist in the target
table, then the existing record is updated, and no error is generated.
UPSERT only matches the Primary Key values.
If other columns are unique, and the update would cause one of those
other unique keys to generate a
Creates Duplicate
Key (40) error, then that error will still occur.
For example this code;
Clear(file)
! set a bunch of fields here
Get(File,PrimaryKey)
If Errorcode()
Add(File)
Else
! reset all the fields, except the primary key)
Put(File)
End
Is now replaced by;
Clear(File)
! set a bunch of fields here
Upsert(File)
Validation
There's a lot to like about SQL, but generic advice for SQL is hard.
That's because context matters enormously when it comes to making
decisions. Every context is different and so the decisions you make will
depend a lot on what your context is. Validation is one of these areas
where there's no universally right answer - the correct approach depends
on your context.
Clarion templates are set up to do client-side validation. They check
the data before sending it to the database. This can be helpful as it
saves unnecessary calls to the database. It also allows the client to
impose restrictions which may be program related, not necessarily
database related.
(For example, the program may be limited to adding sales transactions,
but not be able to add expense transactions.)
If the program "owns" the database, and all the programs adding data to
the database are coded well, then this approach works well. It also
saves work for the database to do (and in some cases this is important
because the database CPU may be the constraining resource.)
However if the database is more open, or if other programs can write to
the database (including general access programs like SSMS, pgAdmin and
so on) then server-side validation may be a good thing to do as well.
Traditional drivers do not support server-side validation, but object
based drivers do.
In this context, to maintain data integrity, data validation is done on
the server before data is written to the database. While the dictionary
editor has options for
Field Validation
(on the
Validity Checks tab for a
field), these are not necessarily exhaustive possibilities from the SQL
point of view. Additionally, these options are not part of the file
structure, so extra code is needed to apply then to a
FILE
structure. These are therefore
client-side
validation rules.
Object based drivers allow for server-side validation rules to be placed
in the
Extended Name. This supplements (but
does not replace) the
Validity Checks tab
(which generates Client-Side code), by allowing for Server-Side
validation (in addition to client-side validation.) Possible options
are;
| Option |
Meaning |
> n
>= n
< n
<= n
|
The value must be greater than, less than, or in a range of
values. Two terms can be used.
Price | > 10 | < 1000
The above creates a range from 10 to 1000 (exclusive). |
<> n
<>
|
The value must not equal n.
If n is omitted then a blank
string (or 0 for numeric fields) is assumed. |
| BOOLEAN |
The value must be 1 or 0.
If this is set, then the above <
and > values (if they exist)
are ignored. This can be used with, or without the NOTNULL
option. Note that if the field is a string type then the value
MUST still be one of '0' or '1'. 'True'
and 'False' are not considered to
be boolean values. |
DEFAULT(n)
DEFAULT=n
|
When doing an ADD or APPEND,
if the field is not supplied, then default the value to n.
Do not use quotes around n. |
| INLIST(a,...,z) |
A comma separated list of allowed values. Do not use quotes on
string values. |
| NOTNULL |
Null values are not allowed |
| REQ |
The value cannot be null, and cannot contain a 0 or blank.
This is the same as NOTNULL | <> |
|
VALIDATE or
CHECK |
A validation exrpression which is used as the SQL CHECK
clause when creating the table in the database.
Validate(price % 100 = 0) |
In this table
n
can be a number, or a string, depending on the type of the field. Quotes
should NOT be used around string values.
Examples
Age Long,Name('Age
| > 0 | <= 130')
LastName String(100),Name('LastName | <> ')
Paid Byte,Name('Paid | Boolean ')
Score Byte,Name('Score | Default(100) |
NotNull')
Color String(10),Name('Color |
InList(Red,Blue,Green)')
Evaluation String(20),Name('Evaluation | Req')
Price
Long,name('Price | Validate(price % 100
= 0)')
prop:Value
Traditional driver do not support
prop:Value
on fields other than memos. If used on any other field type it returns a
blank string.
Object based drivers extend the support for
prop:Value
to all field types. It returns the "raw memory" value of the field (not
the text version of the field) from the current record buffer, as a
string. See also
prop:text .
Behavior for memos and blobs is unchanged.
If the field is an array then the "whole array" is returned, as a single
string value (usually null padded).
Note that Cstring and Pstring values are "full length", not
null-terminated. The first char in the Pstring is the number of bytes
the string uses, however the full string length is returned.
For Example;
s = customers{prop:value,3)
Detecting an Object Based Driver
To determine if a table is using an object based driver, or not, at run
time, use
prop:OBD;
If table{prop:OBD}
! object based driver
End
Getting the Object for the FILE Structure
The first time the FILE is touched (on a thread), an object for that
file is created. Assuming the FILE is
,THREAD
then each thread will have it's own instance of the File Record, and
each thread will have it's own instance of the Object. In most
situations you will not want to access the object directly - that's what
the File Driver commands are for. However if the driver class has
extra methods, or perhaps extends what a method returns, then you may
want to call the object directly. This is exactly the same as calling
the object via the File Driver commands, but can be desirable in some
situations.
For example, the DOS 2 Driver supports files larger than 2 (or 4)
gigabytes. Since the regular
BYTES command
returns a
LONG, to get the actual size of
the file you need to bypass the
BYTES command. Since the BYTES
method
returns a
BIGINT (Currently a
REAL)
if you call the method directly you can get a larger number returned.
Doing this is very straight-forward. In this example
ImportTable
is the name of the FILE structure.
ImportTable &DriverFileDos2Class
r real
code
ImportTable &= Import{prop:object}
r = ImportTable.Bytes()
Note that threaded FILE structures, by extension imply that each thread
has it's own object. So the object reference should be declared locally
(as above) or, in the case of a global declaration, that
declaration should be threaded as well. For example;
glo:ImportTable &Dos2DriverClass,Thread
Note: This example is somewhat contrived. The
BYTES64 command would work just as
well.
prop:Object can also be used for keys and
blobs. When used in this way it returns the
DriverFileClass object for the
FILE
which contains that
KEY or
BLOB.
Example
SomeProcedure (Key pKey)
somefile &FILE
code
somefile &=
pKey{prop:object} ! somefile now points to the FileDriver object for
the table that belongs to that key.
