CapeSoft.Com
Clarion Accessories
MyTable
Documentation
Download Latest VersionIndex History Templates Classes
CapeSoft Logo

CapeSoft MyTable
Documentation

Installed Version Latest Version

Introduction

MyTable is a collection of classes which make it easier to work generically on table structures, without having the actual table structure in scope. This makes it a very powerful building block, on which to base other generic code.

Based on this it includes some templates for common usage as well.

Features


Webinars

Link Description
#613 Deep dive into adding Data Privacy features to an existing program and database. Emphasis on "reasonable security" as required by GDPR, POPI, CCPA etc

Errata: In the webinar Bruce says that the ID field is included as a Salt to encrypted fields to prevent row Cut & Paste. This is inaccurate. The Salt is used in the Password for the field (thus preventing row Cut & Paste).
#537 Initial Product release looking at ways to generically access any Table structure.

Requirements

MyTable requires StringTheory

Use in a Multi-DLL app, and the Storing and Using JSON in a BLOB feature requires jFiles.

The Securing Data at Rest  feature requires Cryptonite or NetTalk 11.41 Desktop (or later)

Multi DLL

If you have a multi-dll system (ie multiple apps) then you need to add this template to the DataDLL app.
In this app turn on both settings on the Multi-DLL tab.

Then add the Global extension to any other apps in the system. In these apps only the first checkbox on the Multi-DLL tab should be on.

Support

Your questions, comments and suggestions are welcome. See our web page (www.capesoft.com) for new versions. You can also contact us in one of the following ways:
CapeSoft Support
Email
Telephone +27 (0)87 828 0123

Installation

To download the latest installation please visit the CapeSoft Downloads page.

To install extract the Installation Program from the SAF file using the free CapeSoft Safe Reader (download for free from https://www.capesoft.com/utilities/Safe/safereader.htm).

Run the Installation Program for your version of Clarion.

Distribution

There are no MyTable files to distribute.

If you are using NetTalk then be sure to check out the requirements for Deploying a TLS Client or Server. This will be in play because MyTable will make use of the encryption offered by NetTalk.

License & Copyright

This template is copyright © 2024 by CapeSoft Software. None of the included files may be distributed. Your programs which use MyTable can be distributed without any MyTable royalties.

This product is provided as-is. CapeSoft Software and CapeSoft Electronics (collectively trading as CapeSoft), their employees and dealers explicitly accept no liability for any loss or damages which may occur from using this package. Use of this package constitutes agreement with this license. This package is used entirely at your own risk.

Use of this product implies your acceptance of this, along with the recognition of the copyright stated above. In no way will CapeSoft , their employees or affiliates be liable in any way for any damages or business losses you may incur as a direct or indirect result of using this product.

For the full EULA see https://capesoft.com/eula.html

Overview

MyTable is fundamentally a collection of utility classes that work on table fields, without knowing the structure of the table. The functionality has been broken into a number of classes.

MyTableBaseClass

The MyTableBaseClass contains basic functionality for debugging, translations, error trapping and so on.
This class is not typically used "as is". Rather it forms the base to a number of the other classes.

Error Handling is done via the ErrorTrap method. If an error occurs then the Error, and Errorcode properties are set and the ErrorTrap method is called. By default this passes the procedure name, any additional information, the error code and an interpretation of the error to Debugview.

MyTableClass

The MyTableClass is designed to be used as a utility class, requiring minimal setup. As such there are very few properties, and the methods are almost completely self-contained.

This class allows for manipulating fields, memos and blobs by name, and also supports storing, and accessing, JSON stored in blobs.

A single MyTableClass object can work on multiple tables on the same thread.

SecureMyTableClass

The SecureMyTableClass allows for fields inside a table to be stored securely when at rest. This class is designed to be "attached" to a specific table, so if you have multiple tables then you would have multiple instances of this class.

This class does need to be initialized properly, using the Init method, before use.

Labels and Names

Every field in a table structure has two identifiers, the label and the name. Unfortunately most programmers use these terms interchangeably referring to the label of the field as its name. [1] This is somewhat compounded by the use of NAME in method names - for example GetFieldName. In most cases these values are the same, but in some cases they can differ.

The MyTable class (NOT the SecureMyTableClass [2]) lets you specify whether you are using the label, or the name, when referring to the field, or blob name. All the methods that take, or return, a field identifier let you specify the identifier as either the Label or the Name. for example GetFieldNumber takes the Table, the identifier, and a parameter to say if the identifier is the Label or the Name of the field.

In versions of MyTable up to and including build 1.12 this parameter was optional, and defaulted to mt:name. Some early use of MyTable has shown this to be an incorrect choice. In almost all cases where the method is used it should be set to mt:label, not mt:name. Indeed omitting this parameter can lead to bugs in the code if (when) the name of the field is changed in the database - especially in SQL databases.

From build 1.13 this parameter is no longer optional, but is required. This means that code written, and using these methods, will break. The fix is simple, add either mt:label or mt:name to the call. In almost all cases it will be mt:label that is the desired option. A future build (probably some time away) may re-instate default values, but this time do it correctly as mt:label. Unfortunately doing this change in one step would be unsafe as code would change in meaning without a compiler error in between. When the second change occurs, code will continue to work fine without any changes being necessary


[1] Since most fields do not have a NAME declared, the uppercase of the Label acts as the Name. Put another way, if a Name is not declared then the Name is the same as the Label. So in most cases the distinction is irrelevant. However when it is declared the distinction becomes very relevant.

[2] The SecureMyTableClass does not support using the Name of the field - all methods in the SecureMyTableClass operate exclusively on the field Label. At this point there does not seem to be a need for Name support, so the methods will remain as-is. If an extra parameter is desirable in the future, then those parameters will default to mt:label, so all code will be backward compatible.

Feature: Get and Set Field Values by Name

In some cases it is useful to write code which works on a table, but does not know the details of the table structure. This allows for generic code which is not bound to a specific table structure, or dictionary. It is also useful to write generic code, which fetches a specific record in a table, based on a specific field value, again without knowing details of the table structure.

The MyTableClass has a number of methods that make this all possible. Specific methods that are useful to do this work are;


GetField, SetField These methods allow you to manipulate specific fields in the file record buffer.
GetBlob, SetBlob These methods allow you to manipulate specific Memo or Blob fields in the table structure.
Note that in MyTable the term BLOB is used to include both MEMO and BLOB field types.
GetKey Gets a Key which matches a specific field.
Get Gets a row from the table based on a specific field value.

1. In the following example  the current value of the PhoneNumber field is returned.

GetPhoneNumber             Procedure(*File pTable)
mt   MyTableClass
fld  Any
  code
  fld &= mt.GetField(pTable,'PhoneNumber')
  return fld

2. In the following example the value in the PhoneNumber field is changed to the new phone number.

SetPhoneNumber             Procedure(*File pTable, String pNewPhoneNumber)
mt   MyTableClass
  code
  mt.SetField(pTable,'PhoneNumber',pNewPhoneNumber)
3. Get the contents of a blob into a StringTheory object

PrintNotes                 Procedure(pTable)
mt MyTableClass
str  StringTheory
  code
  mt.GetBlob(pTable,'notes',str)
  ! can now do whatever you like with str

4. Get the Guid value for the currently loaded record in a table

GetGuid                   Procedure(*File pTable)
mt   MyTableClass
fld  Any
  code
  fld &= mt.GetField('Guid')
  return fld

5. Get the GuidKey from a table (where the GuidKey is the key on the Guid field)

GetGuidKey     Procedure(*File pTable)
mt  MyTableClass
key  &Key
  code
  key &= mt.GetKey(pTable,'guid')
  components = key{prop:components}
  return key

6. Get a record from the table where the guid is a specific value.

GetRecord        Procedure(*file pTable, String pGuid)
mt      MyTableClass
fld     Any
GuidKey &Key
  code
  mt.Get(pTable,'guid',pGuid)
 

Feature: Storing and Using JSON in a BLOB

This feature requires jFiles.

Databases, whether they are SQL or ISAM, are very good at storing structured data in a table, different values in different columns.  They are less good at storing unstructured data, which may vary from one customer to another.

MyTable provides a class called MyTableClass which allows you to store unstructured data in a BLOB (or MEMO) field, and then use this data on browses and forms. This data is stored as JSON inside the Blob, and MyTable (making use of jFiles) allows this data to be read from, and written to as needed. Data stored in this unstructured field can grow, or change, without the dictionary needing to change.

LoadBlob

This loads the contents of a Blob into a JsonClass object.

json  jsonClass
mt    MyTableClass
  code
  mt.LoadBlob(pTable,'attributes',json)

The json class can then be managed using any of the regular jFiles methods.

If you have a group then you can load the Blob straight into the group

myGroup   Group
firstname   String(20),name('FirstName')
age         Long ,name('Age')
paid        Byte ,name('Paid | boolean')
          End
mt        myTableClass
  code
  mt.LoadBlob(pTable,'someblob',myGroup)

The same applies to a Queue

myQueue   queue
name        string(20) ,name('Name')
score       decimal(5,2),name('Score')
passed      byte ,name('Passed | boolean')
          end
mt        myTableClass
  code
  mt.LoadBlob(pTable,'someblob',myQueue)

SaveBlob

This is the reverse of LoadBlob, and is used to store the Json currently in a JsonClass object straight into the blob.

json  jsonClass
mt    MyTableClass
  code
  mt.SaveBlob(pTable,'attributes',json)

As before there is also a Group form of this

mt    MyTableClass
  code
  mt.SaveBlob(pTable,'attributes',myGroup)

and a Queue form

mt    MyTableClass
  code
  mt.SaveBlob(pTable,'attributes',myQueue)

Using Unstructured Data on a Form, as a Group

If you create a group in the Local Data Pad of the form, then this group can be populated from the BLOB when the form opens, and stored in the blob when the form closes.

Once this is setup you can change the fields in the group as you wish, you do not need to adjust anything in the code. The group is stored in the BLOB, and retrieved from the BLOB.

There is a template for all this code - see Blob on a Form.

Hand Code

In the data declaration section

mt              MyTableClass
SettingsGroup   Group
Server            String(255)
Port              Long
                End

Then after the Window is opened;

mt.LoadBlob(Settings,'ServerSettings',SettingsGroup)
! Settings is the Table, ServerSettings is the Blob Name.

Then in TakeCompleted, before the parent call (ie before the record is saved)

mt.SaveBlob(Settings,'ServerSettings',SettingsGroup)

For more information see documentation for LoadBlob and SaveBlob.

Using Unstructured data on a Form, as a Queue

If you create a queue in the Local Data Pad of the form, then this queue can be populated from the BLOB when the form opens, and stored in the blob when the form closes. A typical queue might be something like a Name / Value store. In other words a queue of data where each data element has a name, and a value. Using this approach the user can add whatever data they deem appropriate for their situation.

For example you may have a customer database. Storing name/value pairs in a blob in the Customer table allows adhoc data about the customers to be captured.

There is a template for all this code - see Blob on a Form.

Hand Code

In the data declaration section

mt                MyTableClass
AttributesQueue   Queue
Name                String(255)
Value               String(255)
                  End

Then after the Window is opened;

 mt.LoadBlob(Settings,'Attributes',AttributesQueue)
! Settings is the Table, Attributes is the Blob Name.

Then in TakeCompleted,, before the parent call

mt.SaveBlob(Settings,'Attributes',AttributesQueue)

For more information see documentation for LoadBlob and SaveBlob.

Retrieving Unstructured data on a Browse or Report

A typical Clarion browse cannot make use of a BLOB in a data column. In order to display a field on a browse a local variable must be created, and that local variable placed in the browse (in the List Formatter).

There is a template for all this code - see Blob in a Browse.

Hand Code

In the data declaration section

ServerHost  String(255)
mt          myTableClass

Then, for ABC, in the SetQueueRecord embed point two lines of code are needed

Access:Settings.Fetch(Set:GuidKey) ! need to do this to get the blobs [1]
ServerHost = mt.GetValueFromInsideBlob(Settings,'ServerSettings','host')

For Legacy, in the Fill Queue routine embed point two lines of code are needed

Get(Settings,Set:GuidKey)          ! need to do this to get the blobs [1]
ServerHost = ThisMyTable2.GetValueFromInsideBlob(Settings,'ServerSettings','Host')

Note [1] : Obviously retrieving the whole row as a separate request for each row in the browse is sub-optimal. For TopSpeed (and other ISAM) tables this is probably not a huge performance issue as individual rows are fetched very quickly. For SQL browses it's far less desirable to do this because a single record fetch on the SQL database is quite expensive.

The more ideal approach is to include the BLOB in the VIEW for the browse. An embed for this purpose was added in Clarion build 11.0.13630. (So it is included in Clarion 11.1)

If you are using an older version of Clarion) then you can follow these instructions to add the embed points yourself.

Suggested Template Changes

NOTE: Only necessary in builds prior to 11.0.13630

For ABC templates

ABGROUP.TPW - circa line 190, immediately after
#CALL(%ProjectQueueFields)
add
#Embed(%InsideView,'Inside View'),%Primary,%ActiveTemplateInstance,TREE('Inside View|' & %ActiveTemplate & '|' & %ActiveTemplateInstance & '|' & %Primary )

ABGROUP.TPW - circa line 260, immediately after
#CALL(%ProjectQueueFields)
add
#Embed(%InsideView,'Inside View'),%Secondary,%ActiveTemplateInstance,TREE('Inside View|' & %ActiveTemplate & '|' & %ActiveTemplateInstance & '|' & %CurrentSecondary)

For Clarion (Legacy) templates

CTLBROW.TPW - circa line 1437, immediately after
#FOR(%QueueField)
...
#ENDFOR
add
#Embed(%InsideView,'Inside View'),%Primary,%ActiveTemplateInstance,TREE('Inside View|' & %ActiveTemplate & '|' & %ActiveTemplateInstance & '|' & %Primary )

CTLBROW.TPW - circa line 1486, immediately after
#FOR(%QueueField)
...
#ENDFOR
add
#Embed(%InsideView,'Inside View'),%Secondary,%ActiveTemplateInstance,TREE('Inside View|' & %ActiveTemplate & '|' & %ActiveTemplateInstance & '|' & %CurrentSecondary)


Feature: Securing Data At Rest

This feature requires Cryptonite or NetTalk 11 Desktop (or later)        We live in an age where very sensitive data needs to be stored, and it needs to be stored in a safe way so that it cannot be stolen, or altered, by users, or programs, without the appropriate access. Data stored in persistent storage (like on a hard drive, or in a database) is known as "Data At Rest".

MyTable provides the SecureMyTableClass to provide proper security for data at rest, regardless of the file driver being used. This class provides Field-Level encryption to selected fields in the database.

Some databases provide security features to ensure that data is stored in a protected way, and this is certainly a welcome feature. It is however not sufficient to cover all the use cases which occur.

When considering how best to protect the data, it is worth understanding the sort of protections that may be required.
  1. Protecting the data from someone who gains access to the raw disk file (or backup file).
  2. Protecting the data from alteration by other programs, such as Database Management programs (aka TopScan)
  3. Detecting when the data has been illegally altered by other programs.
  4. Protecting the data from being copied from one row to another, while in its encrypted state.
  5. Protecting data from being decrypted simply because it is the same as some other known encrypted value.
  6. Protecting the data from programmers.
There are also two categories of data that have to be protected
  1. Passwords
  2. Everything else
To accomplish the above two basic security techniques are used to protect data which is at rest.
  1. Encryption
  2. Hashing

Dictionary Rules

  1. Only STRING, CSTRING, PSTRING, USTRING, MEMO and BLOB fields can be encrypted.
  2. Each row needs a unique unchanging identifier field, ideally a string (longer is better) although a number is acceptable (again, bigger is better.) This value is used as part of the encryption key, and effectively binds the data to this row. Note that if this value changes then the data cannot be decrypted.
  3. MyTable does not need to have any knowledge of the table structure - it will work on any table, using any driver, as long as it conforms to the rules.
  4. Values are stored as Base64 encoded text strings, so string fields should be sized to allow for SALT plus 25% more space than the largest data they can contain.
  5. Each table with encrypted fields MUST contain a LONG called EncryptionVersion. (Case does not matter)
  6. Fields can be specified as encrypted or hashed, in the dictionary as Extended Name Attributes or in the MyTable procedure extension.

Encryption

Encryption is a way of changing the data, using a key, so that it is no longer readable. Importantly though encrypted data can be decrypted. If this is done using the same key then this is known as Symmetric Encryption. MyTable makes use of Cryptonite to do the encryption and decryption. By default it uses the AES256 Algorithm, but this can be overridden if you prefer something else.

MyTable stores encrypted data in a base64 form, not a binary form. This makes the data 25% larger than the source data. Therefore fields that are designed to store encrypted values should be at least 25% larger than the largest value expected to be encrypted. The block-length of the encryption algorithm (128 bits in the case of AES 256) also comes into play, so round up to the next 16 bytes.

So, assuming a 128 bit block size, and base 64 encoding, the minimum string length should be 44 bytes.
For bigger strings, allow plenty of extra room, 160 characters encrypts and encodes to 236 characters.

MyTable clips data before encrypting it. Blank strings are not encrypted.

Hashing

Hashing is a way of changing the data so that it is no longer readable and also cannot be decrypted. When data is hashed (using the SHA256 algorithm by default) it is reduced to a constant length (regardless of the original string size) and the original string cannot be reconstructed from the hash.

Hashes are useful in two ways;
  1. They validate that a plain-text field has not been altered and
  2. Because they allow you to compare strings without actually knowing the original string. By comparing hashes you can determine if a string has been changed.
MyTable allows encrypted fields to have a partner Hash field. This allows tampering (of the encrypted value, or unencrypted value) to be detected. The parent field does not have to be encrypted, but hashing encrypted fields is supported.

Before saving the field (and before encrypting it, if it is being encrypted) a (salted) hash of the field is stored in another field. Then after loading the records (and decrypting if necessary), the value loaded is hashed and compared to the stored hash value. In this way tampering with the field value can be detected.

If a hash fails during the load process, then the incoming field is cleared in the record buffer. In other words the affected field is not loaded. The value in the database remains unchanged though, so it can be read by other - non-secure - access methods. However if the loaded record (with the fields blank) is saved, then the fields will be cleared in the database as well.

RowHashing

MyTable also allows multiple fields to share a specific hash field called RowHash. This can be useful for fields which are not a secret (and hence not encrypted) but where tampering with the field (ie editing of the field outside of the program itself) needs to be detected. This technique is known as rowhashing. It simplifies the field structure (and makes the record smaller) by allowing multiple fields to share the same hash. The order of AddField commands is important when rowhashing, as the fields are added together in the hash buffer in the order they are added.

It should also be mentioned that rowhashing relies on the list of fields in the rowhash being constant. In other words adding a field after-the-fact to a rowhash is difficult. Consider the following;

a) a RowHash is created consisting of 2 fields, FirstName and LastName
b) Later on an existing field (MiddleName with data in it) is added via ADDFIELD.
c) In this case the program tries to match a hash on 3 fields, with the value calculated in 2 fields (and hence the hash fails, and all 3 fields are cleared.)

Therefore if fields are added to the rowhash after the fact, then only new, blank, STRING, fields can be added. Blank fields are not included in the rowhash calculation and therefore blank fields can be added. For this reason when adding new (string) fields to the data record, consider carefully if they should be included in the rowhash, as adding them later is difficult.

Note that when multiple fields are used in the rowhash, and the hash does not match, then ALL the fields that make up the rowhash are cleared. It's not possible to determine which field was tampered with, so all fields are cleared.

Encryption Keys

Encryption keys are important because if you know the key to the encryption then you can decrypt the data. MyTable uses a key built up of four parts;
  1. The row identifier. (ie GUID value, or Autonumber ID or whatever.) This part is not a secret. By using the ID as part of the key, it is not possible to copy data from one row to another using an external database tool. In other words copying the phone number from Alice to Gertrude will not expose Alice's number to Gertrude, since it will not decrypt correctly.

    It also has the effect of ensuring that two values that are the same, are not encrypted to the same value. So if Alice's balance is the same as Gertrude's balance, and an attacker knows Alice's balance, then Gertrude's balance is not compromised.
  2. The Table identifier. Entered into the program by the developers. This part is not necessarily secret.  This protects the data from being copied (with the row identifier) from one table to another.
  3. The Program secret. Entered into the program by the developers. This part is a secret. This protects the data from external scanning programs operated by the customer. It prevents the customer from altering the data, to some known value, outside of the original program.
  4. The Customer secret. Entered into the program by the customer. This part is a secret. Not all programs will make use of the Customer key (many programs may choose to leave this blank) but it is an important protection for systems which need to protect data from programmers. Of course in this situation the code itself needs to be audited to ensure that the customer key is entered, and used, in a way that does not expose the key back to the programmer.

Passwords

Passwords which the user needs to enter should never be stored as their actual value. A password that is never stored is a password that is never compromised when at rest.

Instead of storing the password, a Hash of the password should be stored. (This has the side effect of allowing passwords to be of unlimited length - if a system limits the length of a password, then it's not storing it as a hash.)

In addition the password should be combined with a unique string value before hashing. This string value (known as the Salt) ensures that two people with the same password end up with different hash values. The Salt is stored, along with the hash, and is not a secret.

Thus passwords (which the user needs to use the program) are stored as Salted, Hashed values.

Passwords to other programs

Passwords used by the program to access other programs cannot be stored as hash vales, because the actual password is needed to access the other program. So, for example, the password to an email account must be Stored, not Hashed, because it will be needed when communicating with the Email server.

In this situation the password should be stored as an Encrypted field. Storing passwords is inherently dangerous though - a compromise to this program can expose the passwords used in other programs.

Versioning

One problem with encrypting data is that changing the encryption from an unencrypted value to an encrypted value, or changing from one algorithm to a more secure algorithm is problematic.
To solve this problem MyTable makes use of a field in the table which MUST be called EncryptionVersion.

This field should not be touched by the program at all, and is under MyTable's control.

Currently two versions are supported;
Version Done By Details
1 Cryptonite AES 256 with SHA 256 hash. (compatible with version 3)
3 NetTalk 11.41 Desktop or later AES 256 with SHA 256 hash. (compatible with version 1)

Sorting and Searching

When fields are encrypted they can no longer be sorted, since encryption by it's nature changes the order. In some cases this can be a problem if you wish to say protect email addresses, but at the same time sort a browse based on the email address.

In this situation it's necessary to have another field, a plain-text field, which contains sufficient characters (but not all the data) to create a useful sort. For example the first few characters of an email address, or name, would be sufficient to act as a sorting column, while at the same time (potentially) leaking the minimal amount of information.

By contrast it is possible to search for a specific value (ie do a FETCH or GET) on a table, where the field is encrypted. The only requirement is that the search field exactly matches the field being searched for, and that the search is encrypted before the GET. In other words something like;

smt.Init()
Cus:Email = 'whatever@somewhere.com'
smt.EncryptRecord(Customers)
Access:Customers.Fetch(Cus:EmailKey)
smt.DecrytRecord()

Extended Name Attributes

Extended Name Attributes are added to the External Name of a field in the data dictionary. The External Name can contain a pipe-separated list of attributes which can be inspected by program code. (This differs from Field Options which are only available to templates, not code.) The following attributes are supported;
Attribute Description
encrypted(mytable) Specifies that the field will be encrypted (using MyTable)
encryptedlookup(mytable) Specifies that the field will be encrypted (using MyTable), but the ID field will not be part of the password. This is useful for fields which will be used as a lookup.
hash(hashfield) Specifies that hashfield contains a hash of this field. One field to one hash field. A single hashfield cannot contain the hashes for multiple fields.
rowhash(mytable) Specifies that this field is included in the Rowhash.
salt This is the SALT field for this table. If not set then use SetSaltFieldName.

Source Code Example

smt  SecureMyTable
  code
  smt.Init(Customers,'Password')
  Open(Customers)
  Loop
    Next(Customers)
    smt.DecryptRecord()
    ! do whatever
    smt.EncryptRecord()
    Put(Customers)
  End

Data Privacy

Database Best Practices

  1. Tables should have a primary key. This key should not be some personal-identifying data. ie not person name, or ID number, or Social Security number etc. Autonumber is ok. GUID is better.

Dictionary Checklist

  1. Identify tables that contain personal, or secret, information (Customers, Employees, Users and so on)
  2. Make sure table has a primary key - add salt Extended Name Attribute
  3. Add EncryptionVersion LONG field
  4. Identify fields containing private information - add encrypted(mytable) Extended Name Attribute
  5. Identify sort fields, Add surrogate sort field. Add surrogate sort key.
  6. Identify Tamper-Proof fields, Add Hash field (or add to RowHash field) - add hash or rowhash Extended Name Attribute
  7. Identify Password (Proof of knowledge) fields. Add PasswordHash field - add Extended Name Attribute

Application Checklist

  1. Add MyTable, StringTheory, Cryptonite / NetTalk & jFiles global extensions
  2. MyTable Global Extension options - set the Program Secret, and if desired the customer secret - consider using global variables for these.
  3. Identify procedures that use secure tables.
  4. Add the appropriate extension template in those procedures (browses, forms, reports, process)
    Note that the Browse extension extends the browse, so make sure that extension is highlighted in the extension list when adding a browse extension.
  5. For Hand-Code apply calls to DecryptRecord and EncryptRecord. Use Hand-Code template to generate object where possible.

Template Reference

Global Extension

General Tab

Disable All MyTable Features
This disables the MyTable template, and no MyTable code will be generated into the application. This is useful for debugging.

Multi-DLL Tab

This is part of a Multi-DLL program
If this app is part of a suite of apps, where you are making your own DLL's, then tick this option on in all the apps, including the DLL apps and the EXE apps in the suite.
Export MyTable Class from this DLL
Check this box if this app is the root DLL of the app suite.

MyTable on Form

Text Tab

Use this tab when the Memo ro BLOB contains a plain (unencrypted) text value.
Blob or Memo
The blob or memo field in theh table structure that contains the text value.
String
A string field where the data will be copied to, so it can be used on the window (presuambly by a TEXT control).

Json Tab

Use this tab when the Memo or BLOB field contains data using the JSON structure.
Blob or Memo
The Blob or Memo field in the table structure which contains JSON data.
String, Group or Queue
A String, Group or Queue to receive the JSON that was in the Memo or Blob.
Match On
One of mt:label or mt:name. Determines if the fields in the group or queue are matched by the field label, or the field name.

Secure Tab

This tab takes care of decrypting and encrypting blobs, memos and fields, so they can be used in the procedure.
Table
The table the blob, memo or field belongs to.
Table Secret
The table secret for the table.
Salt Field
The name of the Salt field in the table.
Field, Blob or Memo
The Field, Blob or Memo to deal with.
Encrypted
Checked if the field itself is encrypted.
Row Hashed
Checked if the field is part of the Row Hash.
Hash Field
The name of the hash field which is check to see that the field itself has not been tampered with.
Password Hash
Passwords (applicable to this program) should be stored as Salted Hash fields. Passwords for other programs (that this program will use internally) are not set here - they should be set as encrypted fields, not salted hash fields.
Salt Field
Enter the field to use as the SALT field here. This is usually the primary key field; if this field changes then the stored hash becomes invalid.
New Password Field
This is the field on the form where the user can enter a new password. If this field is blank then the table field is not updated. The actual hash field is not put on the form.

Blob in a Browse

Class Reference

Class Hierarchy

Classes

MyTableBaseClass

This class contains generic debugging, translation and error handling methods. All the other classes include this functionality.

Properties

Property Description
Error (Long) Contains a MyTable Error Code if an error occurred. See ErrorTrap and InterpretError methods.
Errorcode (Long) Contains a Clarion ErrorCode if an error occurred when doing some file access.
Inited (Long) Set to 1 if the object has been initialized. (ie the INIT method for the object has been called.)
ShowErrors If true then a MESSAGE will appear when ErrorTrap is called. False by default.

Methods

MyTableBaseClass

Error

Error ()

Description

Returns the contents of the Error property.

Return Value

String

See Also

InterpretError, ErrorCode, ErrorTrap

MyTableBaseClass

ErrorCode

ErrorCode()

Description

Returns the contents of the Errorcode property.

Return Value

Long

See Also

InterpretError, Error, ErrorTrap

MyTableBaseClass

ErrorTrap

ErrorTrap (String pMethod, String pStr)

Description

Is called whenever an object encounters an error.  By default the error is sent to Debugview so that you can note that it happened. Note that the Error and Errorcode properties are in scope here. Use InterpretError to turn the error property into a string.

If you wish to handle errors differently then override this method and replace it with your own code.

Parameters

Parameter Description
pProcedure The name of the method where the error occurred.
pStr Any additional information the method may pass in to make the error easier to debug or understand.
Return Value

Nothing

Example

mt  MyTableBaseClass
  code
  mt.ErrorTrap('BrowseCustomers','Result is ODD')

See Also

InterpretError

MyTableBaseClass

GetElapsedTimeUTC

GetElapsedTimeUTC()

Description

Returns the milliseconds elapsed since 1 January 1970 00:00:00 UTC.

Return Value

REAL

Example

r  real
mt  MyTableBaseClass
  code
  r = mt.GetElapsedtimeUTC()



MyTableBaseClass

GetLocalTimeDifference

GetLocalTimeDifference()

Description

Returns the milliseconds difference between local time and utc time. UTC = local time + difference.

Return Value

REAL

Example

r  real
mt  MyTableBaseClass
  code
  r = mt.GetLocalTimeDifference()



MyTableBaseClass

GetMachineID

GetMachineID()

Description

Returns the MachineGuid, as stored in the Windows registry.

Return Value

String

Example

id string(255)
mt  MyTableBaseClass
  code
  id = mt.GetMachineID()



MyTableBaseClass

Init

Init()

Description

Sets the Inited property to true. Typically fleshed out in derived classes. Whether this method needs to be called or not depends on the requirements of the derived class.

Return Value

None

Example

mt  MyTableBaseClass
  code
  mt.Init()



MyTableBaseClass

GetMachineName

GetMachineName()

Description

Returns the Machine Name, as stored in the Windows registry.

Return Value

String

Example

name string(255)
mt  MyTableBaseClass
  code
  name = mt.GetMachineName()



MyTableBaseClass

GetWindowsUserName

GetWindowsUserName()

Description

Returns the User Name of the person currently logged into Windows.

Return Value

String

Example

user string(255)
mt  MyTableBaseClass
  code
  user = mt.GetwindowsUserName()



MyTableBaseClass

InterpretError

InterpretError()

Description

Returns a description of an error based on the value in the error property.

Parameters

None

Return Value

A string.

See Also

ErrorTrap

MyTableBaseClass

Start

Start()

Description

Sets the object back to a virgin state ready for reuse.

Parameters

Parameter Description
pField The field number of the field to alert.
Return Value

None

Example

mt   MyTableBaseClass
  code
  mt.start()

See Also

MyTableBaseClass

Trace

Trace(String pStr)

Description

A debugging method that makes it easy to send strings to Debugview.

Parameters

Parameter Description
pStr A String to send to Debugview
Return Value

None

Example

mt   MyTableBaseClass
  code
  mt.start()

See Also

Classes

MyTableClass

This is a generic class for dealing with tables where the exact table definition is unknown. This allows generic code to be written, against an unspecified table declaration. Code written using this approach does not need to change if the table structure is updated.
This class works by finding, and then using, fields in the table based on their label (aka their "name".)

Properties

This class is derived from the MyTableBaseClass so the properties from that class also exist.
Property Description
jsonQueue And internal queue of JsonClass objects, used when ParseBlob is used without passing in a JsonClass object.

Methods

This class is derived from the MyTableBaseClass so the methods from that class also exist.
MyTableClass

CleanFieldName

CleanFieldName(*StringTheory pFieldName)

Description

Removes the prefix (if it exists) from a fieldname. Also removes any extended attributes (if they exist) from a fieldname. This is a utility method and is not usually called directly.

Parameters

Parameter Description
pFieldName A StringTheory value containing the fieldname.
Return Value

None

Example

fieldname  StringTheory
  code
  fieldname.SetValue(lower(Who(pGroup,FieldNumber)))
  self.CleanFieldName(fieldname)

See Also

MyTableClass

ClearTable

ClearTable(*File pTable,Long pN=0)

Description

Clears all the fields in a table record, including all BLOB and MEMO fields (IF the table is OPEN.) Can be used in place of
Clear(table) or Clear(tab:record) so that any BLOB's or MEMO fields are correctly initialized and cleared as well. This is especially important for BLOBs in SQL tables which need to be initialized before they can be used.

Parameters

Parameter Description
pTable The table which contains the record to be cleared.
pN A numeric constant; either 1 or -1. If omitted or zero, numeric variables are cleared to zero, STRING variables are cleared to spaces, and PSTRING and CSTRING variables are set to zero length. If n is 1, each field is set to the highest possible value for that data type. For the STRING, PSTRING and CSTRING data types, that is all ASCII 255. If n is -1, each field is set to the lowest possible value for that data type. For the STRING data type, that is all ASCII zeroes (0). For the PSTRING and CSTRING data types, that is a zero length string.

BLOBs and MEMOs are not affected by this paameter. In all cases BLOBs and MEMOs are cleared to an empty string.
Return Value

mt:ok if the operation is successful. mt:notok if the passed in Table parameter is null.

Example

mt  MyTable
  code
  mt.ClearTable(Customers)  

See Also

MyTableClass

FieldInUniqueKey

FieldInUniqueKey(*File pTable, String pFieldName, Long pLabel)

Description

Returns true if the field is in a unique key in the table declaration.

Parameters

Parameter Description
pTable A StringTheory value containing the fieldname.
pFieldName
pLabel
Return Value

If the field is in a unique key, then returns true. If not in a unique key then returns false.

Example

mt  MyTableClass
  code
  result = mt.FieldInUniqueKey(Customers,'Id')

See Also

MyTableClass

Get

Get (*File pTable, String pFieldname, String pValue, Long pLabel)
Get (*File pTable, String pFieldname1, String pValue1, String pFieldname2, String pValue2, Long pLabel)

Description

Loads a record from the table, where the fieldname matches a specific value.

Parameters

Parameter Description
pTable The table containing the field.
pFieldName The name of the field. This name is not case sensitive.
pValue The value to load.
pLabel If set to mt:name then the value in the External Name is used when matching the FieldName parameter.  If set to mt:Label (recommended) then the LABEL of the field is used instead of the NAME.
Return Value

mt:ok if a record is found. mt:notok if the Get fails for some reason.

If the pTable parameter is null then Error is set to mt:MissingTableParameter and ErrorTrap will be called.

If the Field name is not found then Error is set to mt:FieldNotFound and ErrorTrap will be called. The table record will be cleared.

If there is no key on that field then Error is set to mt:KeyNotFound and  ErrorTrap will be called. The table record will be cleared.

When selecting a key then the preference is given to single component keys. If a multi-component key is used then the first record in that key will be returned by this call.

If a record matching that specific value is not found then the Error property will be set to mt:RecordNotFound. The record buffer will be cleared. ErrorTrap will NOT be called. The Errorcode property will contain the Table error from the read. Errorcode() and Error() are still valid at the end of the method call.

Example

SetPaid   Procedure(*File pTable, Long pValue)
mt        MyTable
  code
  If mt.Get(pTable,'id',5) = mt:ok
    mt.SetField(pTable,'Paid',pValue,mt:label)
    Put(pTable)
  End

See Also

GetKey, SetField

MyTableClass

GetBlob

GetBlob(*File pTable, String pBlobName, StringTheory pBlob, Long pLabel)

Description

Takes the value that is currently in the blob, and moves it into the StringTheory object. It identifies the blob field using the blob name.

This method works on the currently loaded record.

This method only works on Blob fields. Use GetField for regular fields.

This method requires the table structure to be open.

Parameters

Parameter Description
pTable The table containing the blob.
pBlobName The name of the blob to move to the StringTheory object. This parameter is not case sensitive.
pBlob A StringTheory object to accept the current contents of the blob.
pLabel If set to mt:name then the value in the External NAME is used when matching the BlobName parameter.  If set to mt:Label (recommended) then the LABEL of the field is used instead of the NAME.
Return Value

mt:ok if successful.
mt:notok if the pTable parameter is blank or the pBlobName field is not found.

If the blob is not found (by name) then the Error property is set to mt:BlobNotFound and the ErrorTrap method will be called. The pBlob parameter will be cleared.

If the pTable parameter is blank then the Error property will be set to mt:MissingTableParameter and the ErrorTrap method will be called.

If the table is not open then the Error property will be set to mt:FileNotOpen and the ErrorTrap method will be called.

If the pBlob parameter is null then the Error property will be set to mt:MissingStringTheoryParameter and the ErrorTrap method will be called.

Example

GetNotes   Procedure(*File pTable)
str        StringTheory
mt         MyTableClass
  code
  mt.GetBlob(pTable,'Notes',str,mt:label)

See Also

GetBlobNumber, SetBlob, SetValue, GetField, ErrorTrap

MyTableClass

GetBlobNumber

GetBlobNumber(*File pTable, String pBlobName, Long pLabel)

Description

Gets the Blob number for the named blob in the table structure.

Parameters

Parameter Description
pTable The table containing the blob.
pBlobName The name of the blob to find. do not include the prefix in the name. The case of this parameter does not matter.
pLabel If set to mt:name then the value in the External NAME is used when matching the BlobName parameter.  If set to mt:Label (recommended) then the LABEL of the field is used instead of the NAME.
Return Value

The number of the blob in the table structure. Blobs are numbered from -1 to -n where n is the total number of blobs and memos in the table.

If the pTable parameter is null then Error is set to mt:MissingTableParameter and ErrorTrap will be called.
If the blob is not found then 0 is returned.

Example

mt          MyTableClass
blobNumber  Long
  code
  blobnumber = mt.GetBlobNumber(customers,'notes',mt:label)

See Also

GetBlob, GetField, GetFieldNumber

MyTableClass

GetField

GetField(*File pTable, String pFieldname, Long pLabel)

Description

Get a reference to a field inside a table, by name. This method works on the currently loaded record.
this method only works on regular fields. Use GetBlob for Blob fields.

Parameters

Parameter Description
pTable The table containing the field.
pFieldName The name of the field. This field is not case sensitive.
pLabel If set to mt:name then the value in the External NAME is used when matching the FieldName parameter.  If set to mt:Label (recommended) then the LABEL of the field is used instead of the NAME.
Return Value

An ANY containing a reference to the field. Changing the value in the ANY then changes the value in the field.

If the pTable parameter is null then Error is set to mt:MissingTableParameter and ErrorTrap will be called.
If the field is not found then a Null is returned.

Example

GetPhoneNumber    Procedure(*File pTable)
mt                MyTableClass
Fld               Any
  code
  Fld &= mt.GetField(pTable,'phoneNumber',mt:label)
  return Fld      ! returns the contents currently in the PhoneNumber field.
See Also

GetFieldName, GetFieldNumber, GetKey, GetBlob, SetField, GetFieldUnique, GetFieldValue

MyTableClass

GetFieldName

GetFieldName(*File pTable, Long pFieldNumber, Long pCase=mt:CaseLower, Long pLabel)

Description

Gets the name of a field in the Table. The field can be either a regular field in the record, or a memo or blob field. If a memo or blob field then the field number is < 0.

Parameters
Parameter Description
pTable The table containing the field.
pFieldNumber The field number of the field in the structure.
pCase Can be one of mt:CaseLower, mt:CaseUpper or mt:CaseAsIs. If omitted then mt:CaseLower is used.
pLabel If set to mt:name then the value in the External Name is returned.
If set to mt:Label (recommended) then the LABEL of the field is returned.
Note that if the case is mt:CaseAsIs then the LABEL is always UPPER case.

Return Value

A string containing the name of the field. The field is returned "cleaned" with the prefix and piped values removed.

If the pTable parameter is null then Error is set to mt:MissingTableParameter and ErrorTrap will be called.
If the FieldNumber is not found then a blank string is returned.

Example

mt    MyTableClass
name  string(100)
  code
  name = mt.GetFieldName(Customers, 2,mt:label)

See Also

GetField, GetFieldNumber, CleanFieldName, GetFieldUnique, GetFieldValue

MyTableClass

GetFieldNumber

GetFieldNumber(*File pTable, String pFieldName, Long pLabel)

Description

Get the field number of a field in the table declaration.

Parameters
Parameter Description
pTable The table containing the field.
pFieldName The name of the field. Do not include the prefix in the name.  The case of this parameter does not matter.
pLabel If set to mt:name then the value in the External NAME is used when matching the FieldName parameter.  If set to mt:Label (recommended) then the LABEL of the field is used instead of the NAME.

Return Value

A Long containing the number of the field.

If the pTable parameter is null then Error is set to mt:MissingTableParameter and ErrorTrap will be called.
If the field is not found then 0 is returned.

Example

mt  MyTableClass
x   Long
  code
  x = mt.GetFieldNumber(customers,'email',mt:label)

See Also

GetField, GetFieldName,, GetBlobNumber, GetFieldValue

MyTableClass

GetFieldUnique

GetFieldUnique(*File pTable, String pFieldName, Long pLabel)

Description

Determine if a field in the table is declared as unique or not. A field is considered to be unique if it is the only component in a unique key.

Parameters
Parameter Description
pTable The table containing the field.
pFieldName The name of the field. Do not include the prefix in the name.  The case of this parameter does not matter.
pLabel If set to mt:name then the value in the External NAME is used when matching the FieldName parameter.  If set to mt:Label (recommended) then the LABEL of the field is used instead of the NAME.

Return Value

True if the field is unique, false if not.

If the pTable parameter is null then Error is set to mt:MissingTableParameter and ErrorTrap will be called.
If the field is not found then false is returned.

Example

mt  MyTableClass
x   Long
  code
  x = mt.GetFieldUnique(customers,'email',mt:label)

See Also

GetField, GetFieldName, , GetBlobNumber, GetFieldValue

MyTableClass

GetFieldValue

GetFieldValue(*File able, String pFieldname, Long pLabel)

Description

Get the current value of a field inside a table, by name. This method works on the currently loaded record.
This method only works on regular fields. Use GetBlob for Blob fields.

Parameters

Parameter Description
pTable The table containing the field.
pFieldName The name of the field. This field is not case sensitive.
pLabel If set to mt:name then the value in the External NAME is used when matching the FieldName parameter.  If set to mt:Label (recommended) then the LABEL of the field is used instead of the NAME.
Return Value

A string containing the contents of the field.

If the pTable parameter is null then Error is set to mt:MissingTableParameter and ErrorTrap will be called.
If the field is not found then a blank string is returned.

Example

GetPhoneNumber    Procedure(*File pTable)
mt                MyTableClass
Phone             String(100)
  code
  Phone = mt.GetFieldValue(pTable,'phoneNumber',mt:label)
See Also

GetFieldName, GetFieldNumber, GetKey, GetBlob, SetField, GetFieldUnique, GetField

MyTableClass

GetKey

GetKey(*File pTable, String pFieldname, Long pLabel)
GetKey(*File pTable, String pFieldname, String pFieldname2, Long pLabel)

Description

Searches for a key on the table which has the fieldname as the first component of the key. Up to two field names can be passed. If two fields are passed then a key with those two fields, in that order, at the start of the key will be returned.

Parameters

Parameter Description
pTable The table containing the field.
pFieldName The name of the field. This parameter is not case sensitive. Do not include the prefix in the name. 
pFieldName2 (optional) The name of a second component field in the key. Do not include the prefix in the name.  The case of this parameter does not matter.
pLabel If set to mt:name then the value in the External NAME is used when matching the FieldName parameter.  If set to mt:Label (recommended) then the LABEL of the field is used instead of the NAME.
Return Value

A reference to a key, where the fieldname is the first field in the key. Preference is given to single-component-keys (or two-component keys in the case of two fieldnames)
 
If no key exists with the field as the first component of the key, then a NULL is returned.

Example

mt   MyTableClass
key  &key
  code
  key &= mt.GetKey(Customers,'Guid',mt:label)


See Also

Get, GetPrimaryKey

MyTableClass

GetPrimaryKey

GetPrimaryKey(*File pTable)

Description

Searches for a primary key on the table

Parameters

Parameter Description
pTable The table containing the key.
Return Value

A reference to a key, where the key is set as a primary key.
If no primary key exists, then the first declared unique key is returned. If there is no unique key in the table declaration then a NULL is returned.

Example

mt   MyTableClass
key  &key
  code
  key &= mt.GetPrimaryKey(Customers)


See Also

GetKey

MyTableClass

GetValueFromInsideBlob

GetValueFromInsideBlob(*File pTable, String pBlobName, String pName, Long pLabel)
Description

This method requires jFiles.

If the blob contains a JSON Group, then this extracts a value from inside that JSON. This method calls LoadBlob with the table and blobname, and then parses the Json for the pName value.

This method works on the currently loaded record, it does not change the file record buffer.

This method is not suitable for Blobs where the JSON holds a queue.

Parameters

Parameter Description
pTable The table containing the blob.
pBlobName The name of the blob in the table.
pName The name of the value in the JSON to fetch. This field is NOT case sensitive. So if there are multiple fields in the JSON, with names differing only in case, it'll always return the first one.
pLabel If set to mt:name then the value in the External NAME is used when matching the BlobName parameter.  If set to mt:Label (recommended) then the LABEL of the field is used instead of the NAME.
Return Value

The value of the JSON node. If the node is not found then a blank string is returned.

If the pTable parameter is null then the Error property is set to mt:MissingTableParameter, and a blank string is returned.

If there is no blob found with that name then the Error property is set to mt:BlobNotFound and a blank string is returned.

Example

mt    MyTableClass
age   Long
  code
  age = mt.LoadBlob(Customers,'attributes','age',mt:label)

See Also

SaveBlob, LoadBlob

MyTableClass

LoadBlob

LoadBlob(*File pTable, String pBlobName, *JsonClass pJson, Long pLabel) [1]
LoadBlob(*File pTable, String pBlobName, *Group pGroup, Long pLabel)  [1]
LoadBlob(*File pTable, String pBlobName, *Queue pQueue, Long pLabel)  [1]
LoadBlob(*File pTable, String pBlobName, *String pString, Long pLabel)
Description

[1] This method requires jFiles.

Use this when the blob contains a valid JSON string. This method parses that string into a jFiles JSONCLASS object. This method works on the currently loaded record.

If a JsonClass object is passed to the method, then it will be used. If it is not passed, but a Group or Queue is passed instead then the JSON will be parsed directly into the GROUP or QUEUE structure.

This method works on the currently loaded record, it does not change the file record buffer.

Parameters

Parameter Description
pTable TThe table containing the blob.
pBlobName The name of the blob in the table.
pJson A jFiles JsonClass object to hold the parsed value.
pGroup
pQueue		 Pass one of these instead of the pJson parameter to parse the JSON directly into the passed Group or Queue.
pString This version of the method does not use jFiles.
pLabel If set to mt:name then the value in the External Name is used when matching the FieldName parameter.  If set to mt:Label (recommended) then the LABEL of the field is used instead of the NAME.
Return Value

mt:ok if the method is successful.
If the pTable parameter is null then the Error property is set to mt:MissingTableParameter, and mt:notok is returned.
IIf there is no blob found with that name then the Error property is set to mt:BlobNotFound and mt:notok is returned.

Example

mt    MyTableClass
json  JsonClass
  code
  mt.LoadBlob(Customers,'attributes',json,mt:label)

See Also

SaveBlob, GetValueFromInsideBlob

MyTableClass

Open

Open(*File pTable)

Description

Opens the table if it is not open. Tables are not created. There is no equivalent Close method.

Parameters

Parameter Description
pTable The table to Open.
Return Value

mt:ok if successful.
mt:notok if the pTable parameter is blank or the table cannot be opened.


If the table cannot be opened then the Error property is set to mt:CantOpenTable and the ErrorTrap method will be called.

If the pTable parameter is blank then the Error property will be set to mt:MissingTableParameter and the ErrorTrap method will be called.

Example

str   StringTheory
mt    MyTableClass
  code
  str.SetValue('myTable is amazing')
  mt.open(Customers)

See Also



MyTableClass

SaveBlob

SaveBlob(*File pTable, String pBlobName, *JsonClass pJson, Long pLabel)[1]
SaveBlob(*File pTable, String pBlobName, *Group pGroup, Long pLabel)[1]
SaveBlob(*File pTable, String pBlobName, *Queue pQueue, Long pLabel)[1]
SaveBlob(*File pTable, String pBlobName, *String pString, Long pLabel)

Description

[1] This method requires jFiles.

Takes the contents of the pJson object, and places it in the Blob. This method works on the currently loaded record - it does not save the record to the disk.

If a JsonClass object is passed to the method, then it will be used. If it is not passed, but a Group or Queue is passed instead then the JSON will be saved directly from the GROUP or QUEUE structure.

This method works on the currently loaded record, it does not change the file record buffer.

Parameters

Parameter DDescription
pTable The table containing the blob.
pBlobName The name of the blob in the table.
pGroup
pQueue		 Pass one of these instead of the pJson parameter to save the JSON directly from the passed Group or Queue.
pString This version of the method does not use jFiles.
pLabel If set to mt:name then the value in the External Name is used when matching the FieldName parameter.  If set to mt:Label (recommended) then the LABEL of the field is used instead of the NAME.
Return Value

mt:ok if the method is successful.
If the pTable parameter is null then the Error property is set to mt:MissingTableParameter, and mt:notok is returned.
If there is no blob found with that name then the Error property is set to mt:BlobNotFound and mt:notok is returned.

Example

mt    MyTableClass
json  JsonClass
  code
  json.Save(AttributesGroup)
  mt.ParseBlob(Customers,'attributes',json,mt:label)

See Also

Load Blob, GetValueFromInsideBlob

MyTableClass

SetBlob

SetBlob(*File pTable, String pBlobName, StringTheory pBlob, Long pLabel)

Description

Takes the value that is currently in the StringTheory object, and moves it into the blob. It identifies the blob field using the blob name. This method works on the currently loaded record.

This method requires the table structure to be open.

Parameters

Parameter DDescription
pTable The table containing the blob.
pBlobName The name of the blob to accept the contents of the StringTheory object. This parameter is not case sensitive.
pBlob A StringTheory object to move to the blob.
pLabel If set to mt:name then the value in the External NAME is used when matching the BlobName parameter.  If set to mt:Label (recommended) then the LABEL of the field is used instead of the NAME.
Return Value

mt:ok if successful.
mt:notok if the pTable parameter is blank or the pBlobName field is not found.

If the blob is not found then the Error property is set to mt:BlobNotFound and the ErrorTrap method will be called.

If the pTable parameter is blank then the Error property will be set to mt:MissingTableParameter and the ErrorTrap method will be called.

Example

str   StringTheory
mt    MyTableClass
  code
  str.SetValue('myTable is amazing')
  mt.SetBlob(Customers,'Notes',str,mt:label)

See Also

GetBlobNumber, GetBlob, SetField, SetValue

MyTableClass

SetField

SetField (*File pTable, String pFieldName, String pValue, Long pLabel)

Description

Sets the value of a specific field to a specific value.

Parameters

Parameter Description
pTable The table containing the field.
pFieldName The name of the field to set. This parameter is not case sensitive.
pValue The new value for the field.
pLabel If set to mt:name then the value in the External NAME is used when matching the FieldName parameter.  If set to mt:Label (recommended) then the LABEL of the field is used instead of the NAME.
Return Value

mt:ok if successful.
mt:notok if not successful.

If the field is not found then the Error property is set to mt:FieldNotFound and the ErrorTrap method will be called. The current value of the field will be unchanged.

If the pTable parameter is blank then the Error property will be set to mt:MissingTableParameter and the ErrorTrap method will be called. The current value of the field will be unchanged.

Example

SetPaid   Procedure(*File pTable)
mt    MyTableClass
ans   Long
  code 
  ans = mt.SetField(pTable,'paid',mt:label)

See Also

GetBlobNumber, GetBlob, GetField, SetValue

MyTableClass

SetValue

SetValue (*File pTable, String pFieldName, String pValue, Long pLabel)

Description

Sets the value of a specific field, or blob to a specific value. Use SetField or SetBlob if you know the FieldName is a field, or blob. Use this method when you don't know if the field is a regular field, or a blob.

Parameters

Parameter Description
pTable The table containing the field or blob.
pFieldName The name of the field or blob to set. This parameter is not case sensitive.
pValue The new value for the field or blob.
pLabel If set to mt:name then the value in the External NAME is used when matching the FieldName parameter.  If set to mt:Label (recommended)  then the LABEL of the field is used instead of the NAME.
Return Value

mt:ok if successful.
mt:notok if not successful.

If the field is not found then the Error property is set to mt:FieldNotFound and the ErrorTrap method will be called. The current value of the field will be unchanged.

If the pTable parameter is blank then the Error property will be set to mt:MissingTableParameter and the ErrorTrap method will be called. The current value of the field will be unchanged.

Example

SetNotes   Procedure(*File pTable, String pNotes)
mt    MyTableClass
  code 
  mt.SetValue(pTable,'notes',pNotes,mt:label)

See Also

GetBlob, GetField,  SetField, SetBlob

MyTableClass

ViewBlob

ViewBlob(*File pTable, String pBlobName, Long pLabel)

Description

Send the contents of a Blob field to DebugView.

Parameters

Parameter Description
pTable The table containing the blob.
pBlobName The name of the blob in the table. This parameter is not case sensitive.
pLabel If set to mt:name then the value in the External NAME is used when matching the BlobName parameter.  If set to mt:Label (recommended) then the LABEL of the field is used instead of the NAME.
Return Value

Nothing

Example

mt  MyTableClass
  code
  mt.ViewBlob(Customers,'notes',mt:label)

See Also

ParseBlob, SaveBlob

Classes

SecureMyTableClass

This class requires Cryptonite

This class allows table rows, or table fields, to be made secure.

Properties

This class is derived from the MyTableClass, so the properties for that class, and the properties for the MyTableBaseClass also exist.
Property Description
ClearTamperedFields If set to true (the default) then fields are cleared if they fail the hash test, or the hash fails to decrypt.
Encrypted fields that themselves fail to decrypt are always cleared.
CustomerSecret
String(255)		 The secret for this customer (optional). Can be set via the SetCustomerSecret method.
DesiredEncryptionVersion
Long		 The version of the encryption desired. Currently set to one of mt:NoEncryption or mt:Aes256EncryptionWithSha256Hash. This property is set in the Start method. It should not be changed by the developer.
ProgramSecret
String(255)		 The secret for this program. Set via the SetProgramSecret method.
SaltFieldName
String(255)		 The name of the unique field in the table. This is typically the GUID field (and this property defaults to GUID).
The Salt Field is used by derived classes, typically to bind encrypted fields to a specific row.
Table
&File		 The table which is attached to the object, via the Init method.
TableSecret The secret for this table (optional). Can be set via SetTableSecret method.
TamperingDetected Set by the DecryptRecord method if any of the fields fail the hash test.
TamperingFieldList A list of the fields protected by a hash that failed the hash test. (Note, all the fields protected by one hash will fail the test if that hash fails.)

Methods

This class is derived from the MyTableClass, so the methods for that class, and the methods for the MyTableBaseClass also exist.
SecureMyTableClass

AddField

AddField(String pFieldName, Long pFieldEncrypted, <String pHashFieldName>)

Description

Adds a field to the list of fields that must be encrypted.

Parameters

Parameter Description
pFieldName The name of the Field, Memo or Blob to encrypt. You do not need to include the prefix in the field name.
pFieldEncrypted One (or more) of;

mt:encrypt the field will be encrypted
mt:hash the (unencrypted) value of the field will be added to the rowhash value.
pHashFieldName If the field has a personal hash field then  add the name of the HashField here. The hash field should be a string type of at least 48 bytes long. If using a Ustring it should be at least 96 bytes long.
Notes

Combining multiple fields into one field for the Rowhash, is not without risk. See the section on RowHashing for more infomation.

Return Value

None

Example

smt     SecureMyTableClass
  code
  smt.Init(Customers,ProgramSecret)
  smt.SetSaltFieldName('id')
  smt.AddField('PhoneNumber')
  smt.AddField('Notes','NotesHash')


See Also

EncryptRecord, DecryptRecord, Init, ParseTable

SecureMyTableClass

DecryptRecord

DecryptRecord()

Description

Decrypt a record after it has been loaded. All the fields, and blobs, that have been added via the AddField method, will be decrypted.

This method is "safe" meaning that it can be called multiple times on the same record. If the record is already decrypted (or was never encrypted) then nothing will happen.

Parameters

None

Return Value

mt:ok if successful, mt:notok if not successful.

Example

smt     SecureMyTableClass
  code
  smt.Init(Customers,ProgramSecret)
  smt.SetSaltFieldName('id')
  smt.AddField('PhoneNumber')
  Get(Customers)
  smt.DecryptRecord()
 

See Also

EncryptRecord, Init, AddField

SecureMyTableClass

EncryptRecord

EncryptRecord()

Description

Encrypt a record before it is saved. All the fields, and blobs, that have been added via the AddField method, will be encrypted.

This method is "safe" meaning that it can be called multiple times on the same record. If the record is already encrypted (or was never decrypted) then nothing will happen.

Parameters

None

Return Value

mt:ok if successful, mt:notok if not successful.

Example

smt     SecureMyTableClass
  code
  smt.Init(Customers,ProgramSecret,TableSecret,CustomerSecret)
  smt.SetSaltFieldName('id')
  smt.AddField('PhoneNumber')
  Cus:PhoneNumber = '011 1234 567'
  smt.EncryptRecord()
  Add(Customers)
 

See Also

DecryptRecord, Init, AddField

SecureMyTableClass

Init

Init(*File pTable, String pProgramSecret, <String pTableSecret>, <String pCustomerSecret>)

Description

Attaches a specific SecureMyTableClass object to a specific database table. Before changing from one table to another (for the same object) call the Start method. This method makes an implicit call to ParseTable.

Parameters

Parameter Description
pTable The table that this class will be working on.
pProgramSecret The secret key, provided by the developer of the program.
pTableSecret Optional. The secret key for this table, provided by the developer of the program. Using this prevents data from being taken from one table, and placed in another table, where the rows have the same row Id.
pCustomerSecret Optional. The secret key as entered by the customer. This prevents the customer data from being exposed to the developers. It also ensures the data for one site is encrypted to differnet values than another site. Be warned though, if the customer forgets this secret then all encrypted data is lost. So use this feature with care.
Return Value

mt:ok if successful.
If the pTable parameter is null then ErrorTrap is called, and mt:notok is returned.

Example

smt     SecureMyTableClass
  code
  if smt.Init(Customers,ProgramSecret) = mt:ok
    smt.SetSaltFieldName('id')
  end
See Also

EncryptRecord, DecryptRecord, AddField, SetSaltFieldName, ParseTable

SecureMyTableClass

MakeHash

MakeHash(StringTheory pStr, Long pAlgorithm=cs:CALG_SHA_256)

Description

Hashes the contents of a StringTheory object, using the desired hashing algorithm. The default algorithm is SHA256.

Parameters

Parameter Description
pStr the StringTheory object to hash. The contents of the object are changed so only the hash is inside the object when it is done.
pAlgorithm the algorithm to use. Can be one of cs:CALG_MD5, cs:CALG_SHA, cs:CALG_SHA_256, cs:CALG_SHA_384, cs:CALG_SHA_512 and others. See the Cryptonite documentation for a full list. If omitted the default is cs:CALG_SHA_256.
Return Value

Returns mt:ok if successful, and mt:notok if not successful.
If successful then the contents of the pStr object are changed.

Example

str  StringTheory
smt  SecureMyTableClass
  code
  str.SetValue('My Table is wonderful')
  smt.MakeHash(str)

See Also

MakePasswordHash (1)

SecureMyTableClass

MakePasswordHash (1)

MakePasswordHash(StringTheory pStr, String pSalt, String pPassword)

Description

Takes in a Salt and Password, and returns a StringTheory object containing the Salted-Hashed-Password.

Parameters

Parameter Description
pStr A StringTheory object to hold the calculated Salted Hash.
pSalt The Salt value.
pPassword The Password value
Return Value

Returns mt:ok if successful, and mt:notok if not successful.
If successful then the contents of the pStr object are changed.

Example

str  StringTheory
smt  SecureMyTableClass
  code
  smt.MakePasswordHash(loc:password,loc:salt,str)

See Also

MakeHash, MakePasswordHash (2)

SecureMyTableClass

MakePasswordHash (2)

MakePasswordHash(String pSalt, String pPassword)
MakePasswordHash(String pPassword)

Description

Takes in a Salt and Password, and returns a String containing the Salted-Hashed-Password.

Parameters

Parameter Description
pSalt The Salt value. If omitted the table salt field is used.
pPassword The Password value
Return Value

Returns a String containing the hash, if the operation is successful. A blank string if not successful.

Example

newPassword  String(255)
smt  SecureMyTableClass
  code
  cus:passwordhash = smt.MakePasswordHash(cus:guid,newPassword)

See Also

MakeHash, MakePasswordHash (1)

SecureMyTableClass

ParseTable

ParseTable()

Description

Processes the table structure of the current table, searching for extended attributes, and making the appropriate call to AddField. In other words transforms the dictionary extended attributes into the necessary AddField commands.

This method is called from the Init method, and does not need to be called manually.

Return Value

Nothing

See Also

Init, Extended Name Attributes,  AddField, SetSaltFieldName

SecureMyTableClass

SetSaltFieldName

SetSaltFieldName(String pSaltFieldName)

Description

Sets the name of the salt field. This should be the unique field for the row. The contents of this field are used as part of the encryption key, so effectively this "binds" the data in the row to this specific ID. If the contents of this field are changed, while the row is still encrypted, then the encrypted data will be lost.

If this method is not called then the fieldname, as set by an extended attribute in the table declaration (ie the dictionary) is used. If no extended attribute is set then the default Salt Field Name (GUID) will be used. So if your table contains an unchanging field called GUID then you don't need to call this method.

Parameters

Parameter Description
pSaltFieldName The name of the salt field.
Return Value

None

Example

smt     SecureMyTableClass
  code
  smt.Init(Customers,ProgramSecret)
  smt.SetSaltFieldName('id')
  smt.AddField('PhoneNumber')
  Cus:PhoneNumber = '011 1234 567'
  smt.EncryptRecord()
  Add(Customers)

See Also

Init, Extended Name Attributes

SecureMyTableClass

Start

Start()

Description

Sets the object back to a virgin state ready for reuse

Parameters

Parameter Description
pField The field number of the field to alert.
Return Value

None

Example

See Also

SecureMyTableClass

UpgradeRecords

UpgradeRecords(Long pProgressControl=0)

Description

This method loops through all the records in the table, and if the EncryptionVersion field is not the version in the DesiredEncryption property, then it decrypts the record (with the old version), encrypts the record (with the new version) and saves the record.

For each record (that has the wrong version number) the ValidateRecord method will be called after the record has been decrypted. If the ValidateRecord method returns mt:RecordFiltered then the record will not be encrypted, and saved. If the ValidateRecord returns mt:OutOfRange then the method

Parameters

Parameter Description
pProgressControl If this field is set, then the prop:progress of this control is updated in the range 0 to 100. In other words the progress control will be updated as the method iterates through the records.
Return Value

None

Example

smt  SecureMyTableClass
  code
  smt.Init(Customers,'secret','secret','secret')
  smt.Upgraderecords(?progress1)



See Also

Init, ValidateRecord

SecureMyTableClass

ValidateRecord

ValidateRecord()

Description

Sets the object back to a virgin state ready for reuse

Parameters

Parameter Description
pField The field number of the field to alert.
Return Value

None

Example

See Also

Version History

Version 1.26 - 23 July 2024
 Version 1.25 - 2 October 2023
 Version 1.24 - 7 July 2023
 Version 1.23 - 11 August 2022
 Version 1.22 - 11 January 2022
 Version 1.21 - 10 January 2022
 Version 1.20 - 3 January 2022
 Version 1.19 - 25 November 2021
 Version 1.18 - 9 September 2021
 Version 1.17 - 26 August 2021
 Version 1.16 - 4 June 2021
 Version 1.15 - 28 May 2021
 Version 1.14 - 24 May 2021
 Version 1.13 (12 May 2021)
 Version 1.12 (27 April 2021)
 Version 1.11 (19 April 2021)
 Version 1.10 (14 April 2021)
 Version 1.09 (13 April 2021)
 Version 1.08 (8 March 2021)
 Version 1.07 (23 February 2021)
 Version 1.06  (9 November 2020)
Version 1.05 (21 July 2020) Version 1.04 (1 June 2020) Version 1.03 (21 May 2020) Version 1.02 (4 May 2020) Version 1.01 (21 November 2019) Version 1.00 (11 November 2019)