DbObject(3NS)

NAME

DbObject - This object serves as an interface between the object-oriented entity classes and the relational database. All object data is represented as a tcl array. This class translates between that array and the sybtcl input and output to the relational database.

SYNOPSIS

DbObject

handle
_dbuser
_dbpass
_dsquery
handleIndex
numHandles
waitInterval	30
tranFlag

Standard Public Interface

DbObject::Connect	{ {dbuser{}} {dbpass{}} {dsquery {}} }	: newHandleNumber
DbObject::Close	{ }	: closedHandleNumber
DbObject::SetHandle	{ {handle} }	: currentHandleIndex
DbObject::Query	{ class objref {where {}} {order {}} }
DbObject::Cancel	{ class objref }
DbObject::Next	{ class objref }	: 1(Success), 0(Failure)
DbObject::Count	{ class {where {}} {distinct *} }	: count
DbObject::Insert	{ class objref }	: oid
DbObject::Update	{ class objref }
DbObject::Delete	{ class objref }
DbObject::Begin	{ }
DbObject::Commit	{ }
DbObject::Rollback	{ }
DbObject::Sql	{ sqlString }
DbObject::Nextrow	{ }	: raw row

Private Interface

DbObject::BuildType	{ type value }	: formattedValue
DbObject::BuildUpdate	{ class objref }	: updateClause
DbObject::BuildSelect	{ class objref }	: selectClause
DbObject::BuildInsert	{ class objref }	: insertAttrclause
DbObject::BuildValue	{ class objref }	: insertValueClause
DbObject::BuildWhere	{ class where }	: whereClause

DESCRIPTION

DbObject::Connect

This procedure makes a connection to the database using the given dbuser, dbpass, and dsquery information. This same procedure can also be used to open multiple handles. If after the first invocation, no information is given, it will open another connection using the same information as given for the first handle. It returns the integer handle that is opened.

Inputs:: dbuser - (optional on 2nd invocation) - the login for the database; dbpass - (optional on 2nd invocation) - The password for the database; dsquery - ( optional database server). If none is provided, the DSQUERY environment variable is used.
Outputs:: handle(n) - an array of database connections
Returns:: handle - [1, 2 ....n] - An integer specifying a handle opened by this command.

DbObject::Close

This procedure closes the sybase connection of the highest number handle and returns that closed handle. Any variable used to hold that handle is unset.

Inputs: None
Outputs: None
Returns:: handle - [0, 1, 2 ....n] - An integer specifying a handle closed by this command

DbObject::SetHandle

This procedure set the current handle to the given handle and the current handle is returned. This remains the current handle until another DbObject::SetHandle is issued or that handle is closed. If no handle parameter is given, the current handle is returned.

Inputs:: handle - [1, 2 ......] - (optional) - A handle to set as the current handle.
Outputs: None
Returns:: handle - [0, 1, 2 ....n] - An integer specifying a the current handle

DbObject::Begin

This procedure begins a transaction on the private variable handle. It enforces single layer transactions, preventing nesting.

Inputs: None
Outputs: None
Returns: None

DbObject::Commit

This procedure commits a transaction on the private variable handle.

Inputs: None
Outputs: None
Returns: None

DbObject::Rollback

This procedure rolls back a transaction on the private variable handle. Since only single layer transactions are supported, it is important that control is returned immediately to the original transaction beginning. In doing this, rollback can be called multiple times without ill effect.

Inputs: None
Outputs: None
Returns: None

DbObject::Query

This procedure sets up a query that may involve a join across multiple tables, depending on how the object is defined. The given objref array specifies the attributes that are to be retrieved. The where clause and order by clause specify the filtering and ordering of the retrieved results. Since the where and order clauses are raw SQL strings, the application programmer is encouraged to define new member procedures so these SQL specifications remain private to this object. Note, the same objref given to this procedure must be used to retrieve the results with DbObject::Next.

Inputs:: class - The entity class to operate on; objref - A reference to an entity array representing the object instance. The attributes specified become the select list in the SQL, meaning only those values are returned in the query; where - [default {}] - the where string for the SQL statement. This is a raw where clause that does not include join constraints. All column names and values are database specific; order - [default {}] - the orderby clause for the SQL statement. All column names are database specific.
Outputs: None
Returns: None

DbObject::Next

This procedure retrieves the query results and places them in the objref array.. It is important that the given objref array is the same as the array used in DbObject::Query since the attributes given in the array determine what attributes get loaded with the returned values. Each attribute is given the value returned by the query and an oid is also assigned if the return code is 1.

Inputs:: class - The entity class to operate on; objref - A reference to an entity array representing the object instance. Only the values of the attributes given in the array are populated in the database.
Outputs:: objref - A reference to an entity array representing the object instance given to tail::Query. Each attribute is given the value returned by the query and an oid is also assigned if the return code is 1.
Returns:: 1 - A row is available; 0 - No rows returned.

DbObject::Count

This procedure counts the rows that match the given where statement. A key can be passed that is unique on the primary table in the case of a join to allow the count to be distinct. It

If no distinct key is given, the select statement is set to count(*)
If a key is given, the select clause is set to count(distinct $distinct)
Build the sequel string by concatenating the select, from and where clauses. The where clause is built by calling DbObject::BuildWhere
Send of the query by calling DbObject::Sql
Retrieve the count by calling DbObject::Nextrow and return this result

Inputs:: class - The entity class to operate on; where - [default {}] - the where string for the SQL statement. This is a raw where clause that does not include join constraints. All column names and values are database specific; distinct - [default *] - A key that is to be counted distinctly. This is used when a join returns multiple rows for a unique key in the primary table. these rows would be counted as one when the key is specified.
Outputs: None
Returns:: count - The count result

DbObject::Cancel

This procedure cancels the result of a query. It is used to discard unretrieved results if they are unneeded.

Inputs: None
Outputs: None
Returns: None

DbObject::Insert

This procedure inserts the object into the database. It only inserts into one table, so each attribute in the objref array must belong to that table or an error will be generated. Only the attributes that are given in the objref array are inserted. If a table contains attributes that are not given and they do not accept null, an error will be generated The oid is returned. It

Builds the attribute list using DbObject::BuildInsert
Builds the valueList using DbObject::BuildValue
Contructs the sql string using the constructed attribute list and value list.
Issues the insert using DbObject::Sql
If the table contains the primary key, retrieve the identity value by calling DbObject::Sql and return it as an oid.

Inputs:: class - The entity class to operate on; objref - A reference to an entity array representing the object instance. Only the values of the attributes given in the array are populated in the database.
Outputs:: objref(oid) - the unique identifier for the object
Returns:: oid - the unique identifier for the object.

DbObject::Update

This procedure updates the object represented by the given objref array. This array must contain a valid oid. Only the values of the attributes represented in the array are updated in the database so parts of the object can be updated. This procedure performs the update to only one table as indicated in the entity this(table) variable. Therefore, all parameters in objref must belong to this table or an error will occur.

Inputs:: class - The entity class to operate on; objref - A reference to an entity array representing the object instance. Only the values of the attributes given in the array are updated in the database.
Outputs: None
Returns: None

DbObject::Delete

This procedure deletes the object represented by the given objref array. The only required attribute is the oidsince this tells the procedure which object to delete.

Inputs:: class - The entity class to operate on; objref - A reference to an entity array representing the object instance.
Outputs: None
Returns: None

DbObject::Sql

This procedure issues a Sybase sql request. If a timeout (in seconds) is given, it will return to the Tcl event loop every timeout seconds.

Inputs:: sqlString - the Sybase sql string; timeout -(in seconds) [default -1] - This procedure blocks until the Sybase server satisfies the request. However, if a timeout, in seconds, is specified, Tcl will return to the event loop every timeout seconds.
Outputs: None
Returns: None

DbObject::Nextrow

This procedure retrieves the sybase row from the private handle connection..

Inputs:
Outputs: None
Returns:: nextrow - The next sybase row of a given sql search. It returns {} if no row is present.

DbObject::BuildWhere

This procedure concatenates the given where clause with any internal entity where clause and returns the final string. If the entity has no internal where clause, the given clause is simply returned.

Inputs:: class - The entity class to operate on; where - The given SQL where string
Outputs: None
Returns:: whereClause - Returns the final where clause.

DbObject::BuildType

This procedure formats the given value according to the given type. If the type is an integer, return the given value without modification. If it is a text or datetime type, return the value enclosed in single quotes. If the keyword function is used, the value after the keyword is simply returned. This allows the use of the sybase functions. If any parameter is specified as {} or "", it will be recorded in the database as a null. Nulls are returned as {}.

Inputs:: type - [Text, Datetime, EnumText, Integer, EnumInt] - the type of the given value; value - The value to be formatted
Outputs: None
Returns:: Returns either 'value' or value depending on type

DbObject::BuildUpdate

This procedure builds the SQL update string . Only the attributes found in objref are built in the list. the oid is not included

For each attribute in objref, find the type by calling Entity::GetAttribute
Format the value according to the type by calling DbObject::BuildType
Create the update clause and concatenate each "attribute=value" adding the commas between expressions.
Return the completed update clause, "attr1=value1, attr2='value2', etc" The quotes depend on attribute type.

Inputs:: class - The entity class to operate on; objref - The array reference that represents the entity object instance.
Outputs: None
Returns:: Returns the "attr1=value1, attr2='value2', etc" The quotes depend on attribute type

DbObject::BuildSelect

This procedure builds the SQL select clause used in an SQL query string. Only the attributes found in objref are built in the list. The oid is not included.

For each attribute in objref, find the database aliased column name by calling Entity::GetAttribute
Create the select clause and concatenate each value adding the commas between values.
Return the completed select clause, "t.attr1, e.attr2, etc"

Inputs:: class - The entity class to operate on; objref - The array reference that represents the entity object instance.
Outputs: None
Returns:: SQL select List - Returns the aliased column name "t.attr1, e.attr2, etc"

DbObject::BuildInsert

This procedure builds the SQL attribute list used in an SQL insert string. Only the attributes found in objref are built in the list. the oid is not included.

For each attribute in objref, create the attribute list and concatenate each attribute adding the commas between attributes.
Return the completed insert clause, "attr1, attr2, etc"

Inputs:: class - The entity class to operate on; objref - The array reference that represents the entity object instance.
Outputs: None
Returns:: SQL insert list - Returns the "attr1, attr2, etc"

DbObject::BuildValue

This procedure builds the SQL value list used in an SQL insert string. Only the values of the attributes found in objref are built in the list. The oid is not included

For each attribute in objref, find the type by calling Entity::GetAttribute
Format the value according to the type by calling DbObject::BuildType
Create the valueClause and concatenate each value adding the commas between values.
Return the completed value clause, "value1, 'value2', etc". The quotes depend on attribute type

Inputs:: class - The entity class to operate on; objref - The array reference that represents the entity object instance.
Outputs: None
Returns:: SQL value list - Returns the "value1, 'value2', etc" The quotes depend on attribute type

DIAGNOSTICS

procName

The following messages will be logged.

message1: Explanation
message2: Explanation

IMPLEMENTATION

namespace eval DbObject {
   variable handle
   variable _dbuser
   variable _dbpass
   variable _dsquery
   variable handleIndex 0
   variable numHandles 0
   variable waitInterval 30
   variable minSleepInterval 5
   variable tranflag
}