com.sebtools.DataMgr
Component DataMgr (Data Manager)


I manage data interactions with the database. I can be used to handle inserts/updates.

hierarchy: WEB-INF.cftags.component
      com.sebtools.DataMgr
methods:

init

Major Methods
deleteRecord, getRecord, getRecords, insertRecord, loadXML, runSQL, saveRecord, saveRelationList, saveSortOrder, setColumn, startLogging, stopLogging, truncate, updateRecord

Informational Methods
getDatabase, getDatabaseShortString, getDatasource, getSupportedDatabases, getTableData, getXML, isLogging

Support Methods
clean, concat, createTable, CreateTables, escape, getCFDataType, getCreateSQL, getDatabaseTables, getDBDataType, getDBFieldList, getDBTableStruct, getFieldList, getFields, getMaxRowsPrefix, getMaxRowsSuffix, getNewSortNum, getPKFields, getPKFromData, getRecordsSQL, getStringTypes, getUpdateableFields, isValidDate, loadTable, logAction, queryparam, queryRowToStruct, readableSQL, runSQLArray


init
public DataMgr init ( required string datasource, string database, string username, string password )

I instantiate and return this object.

Output: suppressed
Parameters:
   datasource: string, required, datasource
   database: string, optional, database
   username: string, optional, username
   password: string, optional, password

Major Methods
deleteRecord
public void deleteRecord ( required string tablename, required struct data )

I delete the record with the given Primary Key(s).

Output: suppressed
Parameters:
   tablename: string, required, tablename - The name of the table from which to delete a record.
   data: struct, required, data - A structure indicating the record to delete. A key indicates a field. The structure should have a key for each primary key in the table.

getRecord
public query getRecord ( required string tablename, required struct data, string fieldlist="" )

I get a recordset based on the primary key value(s) given.

Output: suppressed
Parameters:
   tablename: string, required, tablename - The table from which to return a record.
   data: struct, required, data - A structure with the data for the desired record. Each key/value indicates a value for the field matching that key. Every primary key field should be included.
   fieldlist: string, optional, fieldlist - A list of fields to return. If left blank, all fields will be returned.

getRecords
public query getRecords ( required string tablename, struct data, string orderBy="", numeric maxrows, string fieldlist="", struct advsql, array filters )

I get a recordset based on the data given.

Output: suppressed
Parameters:
   tablename: string, required, tablename - The table from which to return a record.
   data: struct, optional, data - A structure with the data for the desired record. Each key/value indicates a value for the field matching that key.
   orderBy: string, optional, orderBy
   maxrows: numeric, optional, maxrows
   fieldlist: string, optional, fieldlist - A list of fields to return. If left blank, all fields will be returned.
   advsql: struct, optional, advsql - A structure of sqlarrays for each area of a query (SELECT,FROM,WHERE,ORDER BY).
   filters: string, optional, filters - Any array of filters to limit the result.

insertRecord
public string insertRecord ( required string tablename, required struct data, string OnExists="insert" )

I insert a record into the given table with the provided data and do my best to return the primary key of the inserted record.

Output: suppressed
Parameters:
   tablename: string, required, tablename - The table in which to insert data.
   data: struct, required, data - A structure with the data for the desired record. Each key/value indicates a value for the field matching that key.
   OnExists: string, optional, OnExists - The action to take if a record with the given values exists. Possible values: insert (inserts another record), error (throws an error), update (updates the matching record), skip (performs no action).

loadXML
public void loadXML ( required string xmldata, boolean docreate="false", boolean addcolumns="false" )

I add tables from XML and optionally create tables/columns as needed (I can also load data to a table upon its creation).

Output: suppressed
Parameters:
   xmldata: string, required, xmldata - XML data of tables to load into DataMgr follows. Schema: http://www.bryantwebconsulting.com/cfcs/DataMgr.xsd
   docreate: boolean, optional, docreate - I indicate if the table should be created in the database if it doesn't already exist.
   addcolumns: boolean, optional, addcolumns - I indicate if missing columns should be be created.

runSQL
public any runSQL ( required string sql )

I run the given SQL.

Output: suppressed
Parameters:
   sql: string, required, sql

saveRecord
public string saveRecord ( required string tablename, required struct data )

I insert or update a record in the given table (update if a matching record is found) with the provided data and return the primary key of the updated record.

Output: suppressed
Parameters:
   tablename: string, required, tablename - The table on which to update data.
   data: struct, required, data - A structure with the data for the desired record. Each key/value indicates a value for the field matching that key.

saveRelationList
public void saveRelationList ( required string tablename, required string keyfield, required string keyvalue, required string multifield, required string multilist )

I save a many-to-many relationship.

Output: suppressed
Parameters:
   tablename: string, required, tablename - The table holding the many-to-many relationships.
   keyfield: string, required, keyfield - The field holding our key value for relationships.
   keyvalue: string, required, keyvalue - The value of out primary field.
   multifield: string, required, multifield - The field holding our many relationships for the given key.
   multilist: string, required, multilist - The list of related values for our key.

saveSortOrder
public void saveSortOrder ( required string tablename, required string sortfield, required string sortlist, numeric PrecedingRecords="0" )

I save the sort order of records - putting them in the same order as the list of primary key values.

Output: suppressed
Parameters:
   tablename: string, required, tablename - The table on which to update data.
   sortfield: string, required, sortfield - The field holding the sort order.
   sortlist: string, required, sortlist - The list of primary key field values in sort order.
   PrecedingRecords: numeric, optional, PrecedingRecords - The number of records preceding those being sorted.

setColumn
public any setColumn ( required string tablename, required string columnname, string CF_Datatype, numeric Length="50", string Default, string Special, struct Relation )

I set a column in the given table

Output: suppressed
Parameters:
   tablename: string, required, tablename - The name of the table to which a column will be added.
   columnname: string, required, columnname - The name of the column to add.
   CF_Datatype: string, optional, CF_Datatype - The ColdFusion SQL Datatype of the column.
   Length: numeric, optional, Length - The ColdFusion SQL Datatype of the column.
   Default: string, optional, Default - The default value for the column.
   Special: string, optional, Special - The special behavior for the column.
   Relation: struct, optional, Relation - Relationship information for this column.

startLogging
public void startLogging ( string logtable="[runtime expression]" )

Output: suppressed
Parameters:
   logtable: string, optional, logtable

stopLogging
public void stopLogging ( )

Output: suppressed
truncate
public struct truncate ( required string tablename, required struct data )

I return the structure with the values truncated to the limit of the fields in the table.

Output: suppressed
Parameters:
   tablename: string, required, tablename - The table for which to truncate data.
   data: struct, required, data - A structure with the data for the desired record. Each key/value indicates a value for the field matching that key.

updateRecord
public string updateRecord ( required string tablename, required struct data )

I update a record in the given table with the provided data and return the primary key of the updated record.

Output: suppressed
Parameters:
   tablename: string, required, tablename - The table on which to update data.
   data: struct, required, data - A structure with the data for the desired record. Each key/value indicates a value for the field matching that key.

Informational Methods
getDatabase
public string getDatabase ( )

I return the database platform being used (Access,MS SQL,MySQL etc).

Output: suppressed
getDatabaseShortString
public string getDatabaseShortString ( )

I return the string that can be found in the driver or JDBC URL for the database platform being used.

Output: suppressed
getDatasource
public string getDatasource ( )

I return the datasource used by this Data Manager.

Output: suppressed
getSupportedDatabases
public query getSupportedDatabases ( )

I return the databases supported by this installation of DataMgr.

Output: suppressed
getTableData
public struct getTableData ( )

I return information about all of the tables currently loaded into this instance of Data Manager.

Output: suppressed
getXML
public string getXML ( string tablename )

I return the XML for the given table or for all loaded tables if none given.

Output: suppressed
Parameters:
   tablename: string, optional, tablename

isLogging
public boolean isLogging ( )

Output: suppressed
Support Methods
clean
public struct clean ( required struct Struct )

I return a clean version (stripped of MS-Word characters) of the given structure.

Output: suppressed
Parameters:
   Struct: struct, required, Struct

concat
public string concat ( required string fields, string delimeter="" )

I return the SQL to concatenate the given fields with the given delimeter.

Output: suppressed
Parameters:
   fields: string, required, fields
   delimeter: string, optional, delimeter

createTable
public string createTable ( required string tablename )

I take a table (for which the structure has been loaded) and create the table in the database.

Output: suppressed
Parameters:
   tablename: string, required, tablename

CreateTables
public void CreateTables ( string tables="[runtime expression]" )

I create any tables that I know should exist in the database but don't.

Output: suppressed
Parameters:
   tables: string, optional, tables - I am a list of tables to create. If I am not provided createTables will try to create any table that has been loaded into it but does not exist in the database.

escape
public string escape ( required string name )

I return an escaped value for a table or field.

Output: suppressed
Parameters:
   name: string, required, name

getCFDataType
public string getCFDataType ( required string type )

I return the cfqueryparam datatype from the database datatype.

Output: suppressed
Parameters:
   type: string, required, type - The database data type.

getCreateSQL
public string getCreateSQL ( required string tablename )

I return the SQL to create the given table.

Output: suppressed
Parameters:
   tablename: string, required, tablename

getDatabaseTables
public string getDatabaseTables ( )

I get a list of all tables in the current database.

Output: suppressed
getDBDataType
public string getDBDataType ( required string CF_Datatype )

I return the database datatype from the cfqueryparam datatype.

Output: suppressed
Parameters:
   CF_Datatype: string, required, CF_Datatype

getDBFieldList
public string getDBFieldList ( required string tablename )

I return a list of fields in the database for the given table.

Output: suppressed
Parameters:
   tablename: string, required, tablename

getDBTableStruct
public array getDBTableStruct ( required string tablename )

I return the structure of the given table in the database.

Output: suppressed
Parameters:
   tablename: string, required, tablename

getFieldList
public string getFieldList ( required string tablename )

I get a list of fields in DataMgr for the given table.

Output: suppressed
Parameters:
   tablename: string, required, tablename

getFields
public array getFields ( required string tablename )

I return an array of all real fields in the given table in DataMgr.

Output: suppressed
Parameters:
   tablename: string, required, tablename

getMaxRowsPrefix
public string getMaxRowsPrefix ( required numeric maxrows )

I get the SQL before the field list in the select statement to limit the number of rows.

Output: suppressed
Parameters:
   maxrows: numeric, required, maxrows

getMaxRowsSuffix
public string getMaxRowsSuffix ( required numeric maxrows )

I get the SQL after the query to limit the number of rows.

Output: suppressed
Parameters:
   maxrows: numeric, required, maxrows

getNewSortNum
public numeric getNewSortNum ( required string tablename, required string sortfield )

I get the value an increment higher than the highest value in the given field to put a record at the end of the sort order.

Output: suppressed
Parameters:
   tablename: string, required, tablename
   sortfield: string, required, sortfield - The field holding the sort order.

getPKFields
public array getPKFields ( required string tablename )

I return an array of primary key fields.

Output: suppressed
Parameters:
   tablename: string, required, tablename

getPKFromData
public string getPKFromData ( required string tablename, required struct fielddata )

I get the primary key of the record matching the given data.

Output: suppressed
Parameters:
   tablename: string, required, tablename - The table from which to return a primary key.
   fielddata: struct, required, fielddata - A structure with the data for the desired record. Each key/value indicates a value for the field matching that key.

getRecordsSQL
public array getRecordsSQL ( required string tablename, struct data, string orderBy="", numeric maxrows, string fieldlist="", string function="", struct advsql )

I get the SQL to get a recordset based on the data given.

Output: suppressed
Parameters:
   tablename: string, required, tablename - The table from which to return a record.
   data: struct, optional, data - A structure with the data for the desired record. Each key/value indicates a value for the field matching that key.
   orderBy: string, optional, orderBy
   maxrows: numeric, optional, maxrows
   fieldlist: string, optional, fieldlist - A list of fields to return. If left blank, all fields will be returned.
   advsql: struct, optional, advsql - A structure of sqlarrays for each area of a query (SELECT,FROM,WHERE,ORDER BY).
   filters: string, optional, filters - Any array of filters to limit the result.

getStringTypes
public string getStringTypes ( )

I return a list of datypes that hold strings / character values.

Output: suppressed
getUpdateableFields
public array getUpdateableFields ( required string tablename )

I return an array of fields that can be updated.

Output: suppressed
Parameters:
   tablename: string, required, tablename

isValidDate
public boolean isValidDate ( required string value )

Output: suppressed
Parameters:
   value: string, required, value

loadTable
public void loadTable ( required string tablename )

I load a table from the database into DataMgr.

Output: suppressed
Parameters:
   tablename: string, required, tablename

logAction
public any logAction ( required string tablename, string pkval, required string action, struct data )

Output: suppressed
Parameters:
   tablename: string, required, tablename
   pkval: string, optional, pkval
   action: string, required, action
   data: struct, optional, data

queryparam
public struct queryparam ( string cfsqltype, required any value, numeric maxLength, string scale="0", boolean null="no", boolean list="no", string separator="," )

I run the given SQL.

Output: suppressed
Parameters:
   cfsqltype: string, optional, cfsqltype
   value: any, required, value
   maxLength: numeric, optional, maxLength
   scale: string, optional, scale
   null: boolean, optional, null
   list: boolean, optional, list
   separator: string, optional, separator

queryRowToStruct
queryRowToStruct ( required QUERY )

Output: enabled
Parameters:
   QUERY: any, required, QUERY

readableSQL
public string readableSQL ( required array sqlarray )

Output: suppressed
Parameters:
   sqlarray: array, required, sqlarray

runSQLArray
public any runSQLArray ( required array sqlarray )

I run the given array representing SQL code (structures in the array represent params).

Output: suppressed
Parameters:
   sqlarray: array, required, sqlarray