Under some conditions, you may need to execute some custom code when Stonefield Query Studio opens a project or when the data dictionary is created or updated from a data source. For example, if you use the Cryptor library to encrypt data files, you must call a function in that library to decrypt the files before they are opened.

To execute custom code, create a text file called SQConfig.sqs in the project directory. Edit this file using any text editor (not a word processor, such as Microsoft Word, which stores binary files, but an editor such as Notepad that stores text files). This file can contain any code you wish, but it must be Visual FoxPro code. This code must accept three parameters.

The first parameter is a string representing the "mode" Stonefield Query Studio is in. The possible values are:

  • STARTUP: this string is passed when Stonefield Query Studio is started. In this case, because a project may not have been opened yet, Stonefield Query Studio looks in its own program directory for SQConfig.sqs.

  • OPENMETADATA: this string is passed when a project is opened. Any code that checks for this string is executed before the TreeView displays the configuration information for the project.

  • PREFILLCOLLECTIONS: this string is passed just before the table, field, and relation collections are filled when the data dictionary is created or updated from a data source.

  • POSTFILLCOLLECTIONS: this string is passed after the table, field, and relation collections are filled when the data dictionary is created or updated from a data source.

  • BEFOREPROCESSTABLES: this string is passed just before the tables in the database are processed and added to the data dictionary.

  • BEFOREMODIFYFIELD: this string is passed before any properties of an existing field in the data dictionary are changed to new values read from the database.

  • BEFORESAVEFIELD: this string is passed just before a field is saved in the data dictionary.

  • BEFORESAVETABLE: this string is passed just before a table is saved in the data dictionary.

  • GETTABLENAME: this string is passed to update the name of the table.

  • GETTABLEPATH: this string is passed to update the path of the table (only for VFP data sources).

  • HANDLEREMOVEDTABLE: this string is passed just before a table is removed from the data dictionary when it no longer exists in the database.

  • HANDLEREMOVEDFIELD: this string is passed just before a field is removed from the data dictionary when it no longer exists in the database.

The second parameter is a reference to either Stonefield Query Studio engine (in the case of STARTUP or OPENMETADATA) or an SQDiscover object, which is responsible for filling the data dictionary. There are several exposed properties for the SQDiscover object, including:

  • cType: the type of data source; either "ODBC," "ADO," or "VFP."

  • nHandle: the ODBC connection handle for the data source if cType is "ODBC."

  • oConnection: a reference to the ADO Connection object used to access the data source if cType is "ADO."

  • cDatabase: the name of the database.

  • oData: a reference to an SFQDataEngine object, which has oDatabaseCollection, oCursorCollection, oFieldsCollection, and oRelationCollection members.

The third parameter is only passed if the first parameter is "BEFORESAVEFIELD," "BEFORESAVETABLE," "GETTABLENAME," "GETTABLEPATH," "HANDLEREMOVEDTABLE," or "HANDLEREMOVEDFIELD." In the case of "BEFORESAVEFIELD," "BEFORESAVETABLE," "HANDLEREMOVEDTABLE," and "HANDLEREMOVEDFIELD," this parameter is a reference to an SQField (in the case of "BEFORESAVEFIELD" and "HANDLEREMOVEDFIELD") or SQTable (in the case of "BEFORESAVETABLE" and "HANDLEREMOVEDTABLE") object, which contain information about the field or table. For "GETTABLENAME," it's the name of the table. For "GETTABLEPATH," it's the path for the table.

For "HANDLEREMOVEDTABLE" and "HANDLEREMOVEDFIELD," the return value indicates whether the table or field should be removed from the data dictionary. The return value for all others is ignored.

Here's an example:

lparameters tcMode, toDiscover, toObject
do case

* When the metadata tables are opened, create certain public
* variables involved in index expressions so we don't get an error
* when we open the tables.

  case tcMode = 'OPENMETADATA'
    public gc_LotPic, ;
      gc_SrlPic, ;
      gc_AcctPic, ;
      gc_DatePic, ;
      gc_JcPic
    gc_LotPic  = "!!!!!!!!"
    gc_SrlPic  = replicate("!", 20)
    gc_AcctPic = "99999-999-9999"
    gc_DatePic = "99/99/9999"
    gc_JcPic   = "!!-!!!!-!!!!!!!"

* Once we're done filling collections, flag primary key fields (those
* with "ID" as the field name) as not reportable so we don't have
* to do this manually.

  case tcMode = 'POSTFILLCOLLECTIONS'
    local lnSelect
    lnSelect = select()
    select REPMETA
    replace REPORTABLE with .F., SORTABLE with .F., ;
      FILTERABLE with .F. for '.ID' $ OBJECTNAME
    select (lcSelect)

* Handle removing a table: don't remove tables for modules that may not be in
* the current database.

  case tcMode = 'HANDLEREMOVEDTABLE'
    lcModule = left(toObject.cAlias, 2)
    llRemove = .T.
    if inlist(lcModule, 'AM', 'CP', 'UP')
      if type('toDiscover.oModules') = 'U'
        addproperty(toDiscover, 'oModules', createobject('SFCollection'))
      endif
      luValue = toDiscover.oModules.GetKey(lcModule)
      if luValue = 0
        locate for left(TABLE_NAME, 2) = lcModule
        llRemove = found()
        toDiscover.oModules.Add(llRemove, lcModule)
      else
        llRemove = toDiscover.oModules.Item(lcModule)
      endif
    endif
    return llRemove
endcase

© Stonefield Software Inc., 2023 • Updated: 11/23/23
Comment or report problem with topic