Caché 3.2 optionally includes Version F.17 of the relational environment. F.17 is a continuation of the F relational environment which does not provide any of the Object-SQL interactions. For example, F.17 does not store Caché objects. To install F.17, you must perform a Caché for Windows custom installation or answer Yes to the appropriate prompt in the Caché for UNIX and Caché for OpenVMS versions.
For Caché 3.2, the pages are completely dynamic. There are no static HTML files; all Web pages are generated on the fly from information contained within the Caché Class Dictionary. The pages are actually generated via the %Documatic class, which means that Caché must be running for these pages to be available.
Note: If you previously had shortcuts or bookmarks to the static HTML pages, they will not work for 3.2.Two other new features of the Caché Class Reference are:
When documenting your own classes, you can use these new keywords:
<CLASS>classname</CLASS> formats and generates a hyperlink to the class.<PROPERTY>property</PROPERTY> generates a link to the property (in this class).
<METHOD>method</METHOD> generates a link to the method (in this class).
<EXAMPLE>lines of code</EXAMPLE> formats lines of code as an example.
The impact on existing applications depends on how a property uses the COLLATION parameter. Assume, for example, that the Person class has a Title property whose type is %String. Prior to Caché 3.2, if no collation was specified for the Title property, then STRING collation was used. If the Title property was indexed, the index was built using STRING collation.
In Caché 3.2, no collation for the Title property causes SQLSTRING collation to be used. So if you have indexed the Title property and you did not specify collation, your index data does not agree with the collation for the property. You must therefore choose one of two options to resolve this:
do ##class(Person).%PurgeIndices()
do ##class(Person).%BuildIndices()
This option changes the data in the index to agree with the collation (default) for the property.
If you want to continue to use STRING collation (and the existing index structures), then you need to change the property defintion and re-compile the class.
If the Title property already had specified the COLLATION parameter, then no change is needed. The current index already reflects the COLLATION.
Note: Because of this equality collation feature, be aware that your 3.2 queries may return different matching data than 3.1 queries.
attribute StrAttrib1 { type = %String(COLLATION="STRING(30)"); }
and
index TruncString { attributes = Name:String(30),City:String(5),State:String; }
You can use this parameter to index on strings that exceed the 255-character limit for Caché subscripts.
If the index is not Unique, PrimaryKey, or IdKey, then the default is INDEX, and
the indexed attributes are converted according to their collation before storing in the
index, with the ID stored as the last subscript. Adding the type=KEY; clause
causes the index to be checked for uniqueness, converted, and stores the ID in the data.
If the index is Unique, PrimaryKey, or IdKey, then the default is KEY, and the indexed attributes are NOT converted to their collated form when stored in the index, and the ID is stored in the data. This form of index can only be used for uniqueness checking and equality constraints.
For Unique and PrimaryKey indices, adding the type=INDEX; clause causes the
attributes to be converted to their collated form and stored in the index, with the ID
stored as the last subscript. Note also that if any of the attributes forming the index
are SQL-null, then the uniqueness check is not performed. This form of index can be used
for collation-sensitive constraint checking (such as lessthan, greaterthan, startswith, etc.).
Note that all indexes created through DDL are of type INDEX.
SQLSTRING[(length)] — prepends a space and strips trailing whitespace, truncating the result at length characters.The %SQLSTRING and %SQLUPPER unary functions can be used in the same manner that %ALPHAUP, %STRING, %UPPER, and %EXACT are used in SQL queries.SQLUPPER[(length)] — prepends a space, strips trailing whitespace, and converts to uppercase, truncating the result at length characters.
Also, when defining map subscript definitions, $$SQLSTRING({Field}) and $$SQLUPPER({Field}) can be used just like $$ALPHAUP({Field}), $$STRING({Field}), or $$UPPER({Field}).
Caché SQL also now supports %SQLSTRING and %SQLUPPER in the Collation clause of a field specification in a DDL statement. For example:
CREATE TABLE MyTable (MyField VARCHAR(10) %SQLSTRING)
will create a clases with an attribute MyField which has a datatype parameter
of COLLATION=SQLSTRING
Other improvements also mean that you can define a view within the same class as the table used in the view. However, if the class is a superclass, do not put the views into it, as you will get a (non-fatal) error that the viewname is not unique, when compiling the subclass(es).
You can even define a view based on another view, within the defining class. You can also make a query into a view AND a stored procedure at the same time.
A better way to define views is with the new view-class syntax, such as:
class SmallView
{
compileafter = Patient;
sqltablename = Females;
view;
viewquery =
{
Select ID,Name,Sex,DOB from Patient
where Sex="F"
}
}
This example projects the view under the name "Females".
Set factory = CreateObject("CacheObject.Factory")
Set resultset = factory.DynamicSQL("select ID,Name,DOB from Person")
Dim factory As CacheObject.Factory
Dim resultset As CacheObject.ResultSet
factory.Connect factory.ConnectDlg
resultset.Execute
Set rs =factory.DynamicSQL("select * from Person where ( Name =?)")
rs.Execute "Smith"
and "Smith" will be passed as a parameter in place of "?".
rs.SetParam 1,param1
rs.SetParam 2,param2
....
rs.Execute
%String(MAXLEN=1) or
you might change it to %ExactString(MAXLEN=1).
This table is in ^%SYS("sql","datatype").
ALTER TABLE <tablename> DROP CONSTRAINT <constraintname>
For example:
ALTER TABLE CustomerDemo DROP CONSTRAINT FK_CustomerDemo
1) When a field is added to a table through an ALTER TABLE <tablename> ADD COLUMN
statement, and the statement specifies a NOT NULL constraint on the
column, and there is no default value for the column; the statement will fail
if data already exists in the table. This is because after the completion of the DDL
statement, the NOT NULL constraint would not be satisfied for all the pre-existing rows.
A new SQLCODE error was created to report this error:
SQLCODE=-304
Attempt to add a NOT NULL field with no default value to a table which contains data.
2) When a field is added to a table through an ALTER TABLE <tablename> ADD COLUMN
statement, and the statement specifies a NOT NULL constraint on the column, and there is a
default value for the column, the statement will update any existing rows in the table and
assign the default value for the column to the field. For example:
CREATE TABLE Person (
Name VARCHAR(35),
DOB DATE)
INSERT INTO Person (Name, DOB) VALUES ('David Jones', 46639)
INSERT INTO Person (Name, DOB) VALUES ('Kyle Jones', 56239)
ALTER TABLE Person ADD COLUMN FavoriteColor NOT NULL DEFAULT 'Blue')
will populate the FavoriteColors column with the value "Blue" for the two rows which
exist in the table prior to the addition of the FavoriteColors column.
3) When a field is added to a table through an ALTER TABLE <tablename> ADD COLUMN
statement, and the statement does not specify a NOT NULL constraint on the column, and
there is a default value for the column, no updates of the column in any existing rows is
performed. The column value is NULL for those rows.
4) If there is no data in the table at the time of the ALTER TABLE <tablename> ADD COLUMN
statement, the column is just added to the table.
QueryToTable^%apiSQL(.query,table,display,.error)
where query is the SQL statement (or array) to execute, table is the name of the table to be created, display is a flag to write ouput (0=no), and error is an array of errors.
Note that TransactionRollBack() rolls back all the open transactions.
ConName1,ConString1[;ConName2,ConString2[;...
Both methods have an insert parameter which must be declared even if it is not used. It contains a 1 if the %Save is an Insert, and a 0 if it is an Update. The return %Status, and can cause the %Save to fail (with rollback). If %OnBeforeSave returns an error status, then %SaveData and %OnAfterSave will not be called.
The methods are run inside the transaction scope, but outside the locking scope of the %Save (i.e., before a lock is attempted, and after the lock is/may be released).
parameter POPSPEC{default="Address:##class(Address).PopulateSerial()"; }
list attribute Addr { type = Address; }
and not
parameter POPSPEC{default="Address:.Addr.PopulateSerial()"; }
list attribute Addr { type = Address; }
Note that the full syntax of POPSPEC at class level is:
<attribute>:<spec>,<attribute>:<spec>,...The full syntax of POPSPEC at attribute level is:
<spec>where <spec>=<value generator>[:<collection count>[:<key generator>]]
<value generator> = ##class(class).method()
= .method() [obj.method()]
= other() [##class(%PopulateUtils).other()
<collection count> = n [1:1:$r(n+1)]
<key generator> = default [##class%PopulateUtils).String(4)]
= ##class(class).method()
= .method() [obj.method()]
= other() [##class(%PopulateUtils).other()
The %IsValidDT() and %Normalize() methods were also changed similarly, so that you can now set a %TimeStamp field to just a date (e.g., +$H in ObjectScript) and the system appends the time "00:00:00".
For compatibility with other major SQL databases, Caché SQL now accepts a Date as a valid entry for a TimeStamp field. TimeStamp fields can now be compared (with >, <, >=, <=, =, <>, BETWEEN) to Date values.
When comparing/inserting a Date into a TimeStamp field, an explicit conversion should be used, as specified in the SQL Standard:
insert into mytable(ts) values ({fn CONVERT({fn curdate}, SQL_TIMESTAMP)})
A new property has been added to the CacheObject ResultSet object. The property QHandle returns the value of the QHandle variable passed through the query, as in the following VB sample code:
Dim rs As CacheObject.ResultSet
Dim syslist as New CacheObject.syslist
Set rs = Cache.ResultSet("Class", "Query")
ok = rs.Execute("a", "b")
qh = rs.QHandle
syslist.Set (qh)
for I=1 to syslist.count
msgbox "Qhandle field" & I & "=" & syslist.item(I)
next I
#sqlcompile select=RuntimeInput/output conversions are dependent on the %Runtime variable. Currently, the "ODBC" value is supported (meaning ODBC conversion and "" meaning no conversion).
For example, the following are now valid SQL regular identifiers:
My@Table
My#Field
My$Money
X@#$$#@
The following regular identifiers are not valid:
#Number
@Direction
$Money
This change means the Binary Modulo operator (#) can only be used with spaces
between the operands and the operator. For example:
NumericField # 10
is valid, while
NumericField#10
will be treated as a single regular identifier.
When creating tables through DDL, the Identifier Translation Table is still used to translate the @, #, $, and _ characters into valid Object Identifier characters. By default, the @, #, $, and _ characters are simply stripped from the identifier to create the Object identifier. For SQL Table names and SQL Field names, these characters are preserved in the SQLTABLENAME and SQLFIELDNAME properties, but for SQL Index Names and SQL Constraint Names, these characters are modified in the resulting table definition. Caché does not support separate names for Objects and SQL for Indices and Constraints.
ExportCDLAll^%apiOBJ() has been corrected to run on UNIX and OpenVMS platforms.
A new call, GetLastError^%apiOBJ(), returns the last error status code. Note that
set sc=$$$ERROR($$$GeneralError,"Some Error")
quit $$$OK
In this case, GetLastError() will return the Note that when these SQL tables are used in non-generated environments (such as programmer-written .MAC code), it is the responsibility of the programmer to include the appropriate files.
Table of Contents | Chapter 1 | Chapter 3 | Chapter 4
Top of Page