OpenInsight Community Source Code - New Pages

Btree Index Structure and Stats

mattVSL — Thu, 15 Dec 2011 21:09:14 CST

This program will parse through the structure of a Btree index, checking that linkages and cross references between index records exist and are correct.
It will also produce a statistics summary.

Subroutine Index_tree( files, indexes, showStruct, outFile)

Parameters:

Files: List of index files, @FM delimited
Indexes: List of indexes (@VM) to check for each File (@FM). If unspecified, all indexes in the File(s) are checked.
ShowStruct: If TRUE (default) then dump the index structure as it is parsed
OutFile: Filename of a DOS output file (Optional)

Files:	List of index files, @FM delimited
Indexes:	List of indexes (@VM) to check for each File (@FM). If unspecified, all indexes in the File(s) are checked.
ShowStruct:	If TRUE (default) then dump the index structure as it is parsed
OutFile:	Filename of a DOS output file (Optional)

The statistics returned for an index:

Total Tree Nodes Number of Node type '0' records
Total Value Nodes Number of Node type '1' records.
These are nodes for a particular index value with a large number of result keys
Total Leaf Nodes Number of Leaf (type '2') records.
These contain the result keys for index values
Total Index Records Sum of the above three totals
Total Exploded Values Number of index values that have Value Node structures
Total Values Total number of index values (including null)
Total Keys Total number of result keys associated with index values
Avg Values/Leaf Typical number of index values in a Leaf record
Avg Keys/Leaf Typical number of result keys in a Leaf record
Avg Keys/Value Typical number of result keys for an index value
Avg Depth Typical number of index records to read before finding the first results
Width per level Number of index records at each level of the tree
Longest Index Id The largest length ID found of an index record, followed by the number of bytes.
(The LH Service pre v4.6 has a 50 byte record key limit! v4.6+ has a 552 byte key limit)
Longest Index Value The largest length index value found, followed by the number of bytes.
(There is potential for an index value to be included in the ID of an index record)
Redundant Leaves Number of Leaf records with no result keys.
(Possibly indicates an un-optimised index that should be rebuilt)
Top 3 Values The three most common index values, followed by the number of result keys for each
Errors Number of referential mismatches and missing index records

Total Tree Nodes	Number of Node type '0' records
Total Value Nodes	Number of Node type '1' records. These are nodes for a particular index value with a large number of result keys
Total Leaf Nodes	Number of Leaf (type '2') records. These contain the result keys for index values
Total Index Records	Sum of the above three totals
Total Exploded Values	Number of index values that have Value Node structures
Total Values	Total number of index values (including null)
Total Keys	Total number of result keys associated with index values
Avg Values/Leaf	Typical number of index values in a Leaf record
Avg Keys/Leaf	Typical number of result keys in a Leaf record
Avg Keys/Value	Typical number of result keys for an index value
Avg Depth	Typical number of index records to read before finding the first results
Width per level	Number of index records at each level of the tree
Longest Index Id	The largest length ID found of an index record, followed by the number of bytes. (The LH Service pre v4.6 has a 50 byte record key limit! v4.6+ has a 552 byte key limit)
Longest Index Value	The largest length index value found, followed by the number of bytes. (There is potential for an index value to be included in the ID of an index record)
Redundant Leaves	Number of Leaf records with no result keys. (Possibly indicates an un-optimised index that should be rebuilt)
Top 3 Values	The three most common index values, followed by the number of result keys for each
Errors	Number of referential mismatches and missing index records

Output is sent to the System Receiver (as well as a DOS file, if specified), so can be RUN in System Monitor.

Requires: OpenInsight v7.2+

Source Code

Web Services

Exorsys — Mon, 03 Aug 2009 17:01:04 CDT

There is no abstract available for this page revision.

Microsoft_ADO_Equates

Exorsys — Thu, 02 Apr 2009 15:52:05 CDT

compile insert Microsoft_Ado_Equates

//--------------------------------------------------------------------
// Microsoft ADO
//
// (c) 1996 Microsoft Corporation. All Rights Reserved.
//
//
//
// ADO equates include file for VBScript
//
//--------------------------------------------------------------------

//---- CursorTypeEnum Values ----
equ adOpenForwardOnly to 0
equ adOpenKeyset to 1
equ adOpenDynamic to 2
equ adOpenStatic to 3

//---- CursorOptionEnum Values ----
equ adHoldRecords to 0x00000100
equ adMovePrevious to 0x00000200
equ adAddNew to 0x01000400
equ adDelete to 0x01000800
equ adUpdate to 0x01008000
equ adBookmark to 0x00002000
equ adApproxPosition to 0x00004000
equ adUpdateBatch to 0x00010000
equ adResync to 0x00020000
equ adNotify to 0x00040000

//---- LockTypeEnum Values ----
equ adLockReadOnly to 1
equ adLockPessimistic to 2
equ adLockOptimistic to 3
equ adLockBatchOptimistic to 4

//---- ExecuteOptionEnum Values ----
equ adRunAsync to 0x00000010

//---- ObjectStateEnum Values ----
equ adStateClosed to 0x00000000
equ adStateOpen to 0x00000001
equ adStateConnecting to 0x00000002
equ adStateExecuting to 0x00000004

//---- CursorLocationEnum Values ----
equ adUseServer to 2
equ adUseClient to 3

//---- DataTypeEnum Values ----
equ adEmpty to 0
equ adTinyInt to 16
equ adSmallInt to 2
equ adInteger to 3
equ adBigInt to 20
equ adUnsignedTinyInt to 17
equ adUnsignedSmallInt to 18
equ adUnsignedInt to 19
equ adUnsignedBigInt to 21
equ adSingle to 4
equ adDouble to 5
equ adCurrency to 6
equ adDecimal to 14
equ adNumeric to 131
equ adBoolean to 11
equ adError to 10
equ adUserDefined to 132
equ adVariant to 12
equ adIDispatch to 9
equ adIUnknown to 13
equ adGUID to 72
equ adDate to 7
equ adDBDate to 133
equ adDBTime to 134
equ adDBTimeStamp to 135
equ adBSTR to 8
equ adChar to 129
equ adVarChar to 200
equ adLongVarChar to 201
equ adWChar to 130
equ adVarWChar to 202
equ adLongVarWChar to 203
equ adBinary to 128
equ adVarBinary to 204
equ adLongVarBinary to 205

//---- FieldAttributeEnum Values ----
equ adFldMayDefer to 0x00000002
equ adFldUpdatable to 0x00000004
equ adFldUnknownUpdatable to 0x00000008
equ adFldFixed to 0x00000010
equ adFldIsNullable to 0x00000020
equ adFldMayBeNull to 0x00000040
equ adFldLong to 0x00000080
equ adFldRowID to 0x00000100
equ adFldRowVersion to 0x00000200
equ adFldCacheDeferred to 0x00001000

//---- EditModeEnum Values ----
equ adEditNone to 0x0000
equ adEditInProgress to 0x0001
equ adEditAdd to 0x0002
equ adEditDelete to 0x0004

//---- RecordStatusEnum Values ----
equ adRecOK to 0x0000000
equ adRecNew to 0x0000001
equ adRecModified to 0x0000002
equ adRecDeleted to 0x0000004
equ adRecUnmodified to 0x0000008
equ adRecInvalid to 0x0000010
equ adRecMultipleChanges to 0x0000040
equ adRecPendingChanges to 0x0000080
equ adRecCanceled to 0x0000100
equ adRecCantRelease to 0x0000400
equ adRecConcurrencyViolation to 0x0000800
equ adRecIntegrityViolation to 0x0001000
equ adRecMaxChangesExceeded to 0x0002000
equ adRecObjectOpen to 0x0004000
equ adRecOutOfMemory to 0x0008000
equ adRecPermissionDenied to 0x0010000
equ adRecSchemaViolation to 0x0020000
equ adRecDBDeleted to 0x0040000

//---- GetRowsOptionEnum Values ----
equ adGetRowsRest to -1

//---- PositionEnum Values ----
equ adPosUnknown to -1
equ adPosBOF to -2
equ adPosEOF to -3

//---- enum Values ----
equ adBookmarkCurrent to 0
equ adBookmarkFirst to 1
equ adBookmarkLast to 2

//---- MarshalOptionsEnum Values ----
equ adMarshalAll to 0
equ adMarshalModifiedOnly to 1

//---- AffectEnum Values ----
equ adAffectCurrent to 1
equ adAffectGroup to 2
equ adAffectAll to 3

//---- FilterGroupEnum Values ----
equ adFilterNone to 0
equ adFilterPendingRecords to 1
equ adFilterAffectedRecords to 2
equ adFilterFetchedRecords to 3
equ adFilterPredicate to 4

//---- SearchDirection Values ----
equ adSearchForward to 1
equ adSearchBackward to -1

//---- ConnectPromptEnum Values ----
equ adPromptAlways to 1
equ adPromptComplete to 2
equ adPromptCompleteRequired to 3
equ adPromptNever to 4

//---- ConnectModeEnum Values ----
equ adModeUnknown to 0
equ adModeRead to 1
equ adModeWrite to 2
equ adModeReadWrite to 3
equ adModeShareDenyRead to 4
equ adModeShareDenyWrite to 8
equ adModeShareExclusive to 0xc
equ adModeShareDenyNone to 0x10

//---- IsolationLevelEnum Values ----
equ adXactUnspecified to 0xffffffff
equ adXactChaos to 0x00000010
equ adXactReadUncommitted to 0x00000100
equ adXactBrowse to 0x00000100
equ adXactCursorStability to 0x00001000
equ adXactReadCommitted to 0x00001000
equ adXactRepeatableRead to 0x00010000
equ adXactSerializable to 0x00100000
equ adXactIsolated to 0x00100000

//---- XactAttributeEnum Values ----
equ adXactCommitRetaining to 0x00020000
equ adXactAbortRetaining to 0x00040000

//---- PropertyAttributesEnum Values ----
equ adPropNotSupported to 0x0000
equ adPropRequired to 0x0001
equ adPropOptional to 0x0002
equ adPropRead to 0x0200
equ adPropWrite to 0x0400

//---- ErrorValueEnum Values ----
equ adErrInvalidArgument to 0xbb9
equ adErrNoCurrentRecord to 0xbcd
equ adErrIllegalOperation to 0xc93
equ adErrInTransaction to 0xcae
equ adErrFeatureNotAvailable to 0xcb3
equ adErrItemNotFound to 0xcc1
equ adErrObjectInCollection to 0xd27
equ adErrObjectNotSet to 0xd5c
equ adErrDataConversion to 0xd5d
equ adErrObjectClosed to 0xe78
equ adErrObjectOpen to 0xe79
equ adErrProviderNotFound to 0xe7a
equ adErrBoundToCommand to 0xe7b
equ adErrInvalidParamInfo to 0xe7c
equ adErrInvalidConnection to 0xe7d
equ adErrStillExecuting to 0xe7f
equ adErrStillConnecting to 0xe81

//---- ParameterAttributesEnum Values ----
equ adParamSigned to 0x0010
equ adParamNullable to 0x0040
equ adParamLong to 0x0080

//---- ParameterDirectionEnum Values ----
equ adParamUnknown to 0x0000
equ adParamInput to 0x0001
equ adParamOutput to 0x0002
equ adParamInputOutput to 0x0003
equ adParamReturnValue to 0x0004

//---- CommandTypeEnum Values ----
equ adCmdUnknown to 0x0008
equ adCmdText to 0x0001
equ adCmdTable to 0x0002
equ adCmdStoredProc to 0x0004

//---- SchemaEnum Values ----
equ adSchemaProviderSpecific to -1
equ adSchemaAsserts to 0
equ adSchemaCatalogs to 1
equ adSchemaCharacterSets to 2
equ adSchemaCollations to 3
equ adSchemaColumns to 4
equ adSchemaCheckequraints to 5
equ adSchemaequraintColumnUsage to 6
equ adSchemaequraintTableUsage to 7
equ adSchemaKeyColumnUsage to 8
equ adSchemaReferentialContraints to 9
equ adSchemaTableequraints to 10
equ adSchemaColumnsDomainUsage to 11
equ adSchemaIndexes to 12
equ adSchemaColumnPrivileges to 13
equ adSchemaTablePrivileges to 14
equ adSchemaUsagePrivileges to 15
equ adSchemaProcedures to 16
equ adSchemaSchemata to 17
equ adSchemaSQLLanguages to 18
equ adSchemaStatistics to 19
equ adSchemaTables to 20
equ adSchemaTranslations to 21
equ adSchemaProviderTypes to 22
equ adSchemaViews to 23
equ adSchemaViewColumnUsage to 24
equ adSchemaViewTableUsage to 25
equ adSchemaProcedureParameters to 26
equ adSchemaForeignKeys to 27
equ adSchemaPrimaryKeys to 28
equ adSchemaProcedureColumns to 29

Oracle Access via ODBC

Exorsys — Thu, 02 Apr 2009 15:49:58 CDT

Compile Function Oracle_Connect(CharStr Username)
* Oracle connection
* call SQL_Error to look for errors after using this function
$Insert Logical
$insert Microsoft_Ado_Equates

DataSource = "DEV"
pwd = "alpha"
ConnectionString = "Provider=OraOLEDB.Oracle; User id=Access;Data Source=" : DataSource : ";Password=" : pwd : ";"
oConn = OleCreateInstance("ADODB.Connection")

oConn->ConnectionString = ConnectionString
x = oConn->Open()

Return oConn

** to check for ADO errors after any call

Compile function SQL_Error(connDB)
declare subroutine msg
eDescription = ""
if OleStatus() <> 0 then
Errors = connDB->Errors
eCount = Errors->Count
eNumber = ""
For ev = 1 To eCount
this_error = OLEGetProperty(Errors,"ITEM",ev-1)
eNumber<1,ev> = this_error->Number
eDescription<1,ev> = this_error->DESCRIPTION
Next ev
end
return eDescription

*** Oracle storeD procedure call
** Note: couldn't get ADdate type to interpret, so used varchar
** gave up trying to return a record set from an SP as well - query works though (see below)

Compile function RATE_ORACLE(yadda,yadda,yadda...
declare subroutine msg
Declare Function Oracle_Connect, SQL_Error
$insert Microsoft_Ado_Equates
$Insert Oracle_Common

Rates = ""
errMsg = ""
connDB = Oracle_Connect(@USERNAME)
ErrMsg = SQL_Error(connDB)
end
if ErrMsg = "" then
Gosub GetRates
end
rates<1> = errmsg
x = connDB->Close()
connDB = ""

return Rates

GetRates:

cmd = OleCreateInstance("ADODB.Command")
OlePutProperty(cmd, 'ActiveConnection', connDB )
OlePutProperty(cmd, 'CommandType', adCmdStoredProc )
OlePutProperty(cmd, 'CommandText', "XXX.TEST_pkg.Calc" )
params = cmd->Parameters
* temporary patch until adDate or adDBDate gets working
Date_conv = Oconv(Idate,'D4E')
Convert " " to "-" In Date_conv
param = oleCallMethod(cmd,'CREATEPARAMETER','pi_Eff_Date',adVarChar,adParamInput,Len(date_conv),Date_conv)
x = oleCallMethod(params,'APPEND',param)
param = oleCallMethod(cmd,'CREATEPARAMETER','pi_Zip',adVarChar,adParamInput,Len(Zip),Zip)
x = oleCallMethod(params,'APPEND',param)
param = oleCallMethod(cmd,'CREATEPARAMETER','pi_Class',adDouble,adParamInput)
OlePutProperty(param,"Value",Rate_Class/10)
x = oleCallMethod(params,'APPEND',param)
param = oleCallMethod(cmd,'CREATEPARAMETER','pi_Weight',adDouble,adParamInput)
OlePutProperty(param,"Value",Tot_weight)
x = oleCallMethod(params,'APPEND',param)
param = oleCallMethod(cmd,'CREATEPARAMETER',"po_Rate_Base_No",adDouble,adParamOutput)
x = oleCallMethod(params,'APPEND',param)
param = oleCallMethod(cmd,'CREATEPARAMETER',"po_AddOn_Factor",adDouble,adParamOutput)
x = oleCallMethod(params,'APPEND',param)
param = oleCallMethod(cmd,'CREATEPARAMETER',"po_Territory",adVarChar,adParamOutput,3)
x = oleCallMethod(params,'APPEND',param)
param = oleCallMethod(cmd,'CREATEPARAMETER',"po_Rate",adDouble,adParamOutput)
x = oleCallMethod(params,'APPEND',param)

x = cmd->Execute()
ErrMsg = SQL_Error(connDB)
If ErrMsg = "" then
For opv = 1 To 4
param = oleGetProperty(params,"Item",opv+8)
Rates<1+opv> = param->value
Next opv
Rates<5> = rates<5> * 10000
end
cmd = ""
param = ""
params = ""

Return

** Return a Record Set from a Query

GetUserProperties:

Row = ""
UserRecord = ""
Script = "SELECT A, B, C, D, F, E FROM DUAL"
affected = 0
rs = ConnDB->Execute(Script, affected,1)
Errmsg = LTL32_SQL_Error(ConnDB)
If Errmsg = "" then
rCount = 0
fields = rs->Fields
x = rs->MoveFirst()
loop Until rs->Eof or OleStatus() <> 0
rcount += 1
For iv = 1 To 7
item = OleGetProperty(Fields, 'ITEM', iv-1)
UserRecord<iv> = item->value
Next iv
For iv = 8 To 10
item = OleGetProperty(Fields, 'ITEM', iv-1)
UserRecord<iv,rcount> = item->value
Next iv
x = rs->MoveNext()
repeat
end
x = OleCallMethod(rs, 'Close')
rs = ""

Return

Get Cureent Windows User ID

Exorsys — Thu, 02 Apr 2009 15:31:55 CDT

EDIT SYSPROCS dll_advapi32
LONG STDCALL GetUserNameA(LPCHAR, LPLONG) AS GetUserName
SYSTEM MONITOR
run declare_fcns "DLL_ADVAPI32"

GetWinUser:

IDLen = 257
WinUser = Str(\00\, IDLen)
If GetUserName(WinUser, IDLen) then
WinUser = WinUser[1,IDLen-1]
End Else
WinUser = ""
end
* for testing on dev machine
Convert @LOWER_CASE To @UPPER_CASE In WinUser
end

Week Number

Kauko — Sun, 15 Feb 2009 10:03:47 CST

There is no abstract available for this page revision.

Paperbins

edgecumbe — Fri, 08 Aug 2008 00:41:41 CDT

OI paper bin functionality does not work correctly with newer printers, which use a wider range of codes than defined in OI.
Apologies: I was going to attach some code, but I don't seem to be able to.

Operating AREV32

dharmacek — Mon, 23 Jun 2008 08:26:59 CDT

dharmacek: I just converted one of my client ArevDOS installations to Arev32. I'm going to start a number of threads about my experience. Feel free to ask questions or post your own solutions/problems. PM: The Arev of DOS was version 3.12. It has been interoperating with OI 3.75 for years, and OI 7.3, then OI 8.0.6.

RGD OSDirectoryTree

zapher67 — Mon, 26 May 2008 20:47:32 CDT

A simple utility that returns an @FM delimited array of all the subdirectories under a given directory, including all child subdirectories. The array is returned flat in the order the directories are discovered.

Converting to AREV32

dharmacek — Mon, 23 Jun 2008 08:14:12 CDT

AREV32

zapher67 — Sat, 24 May 2008 18:24:21 CDT

This section contains information about using AREV32 in OpenInsight.

Installing and running the oeSocketServer Service

zapher67 — Wed, 12 Mar 2008 16:45:11 CDT

A whitepaper discussing how to install and run the oeSocketServer service.

See also the white paper on Configuring OECGI2 and the Proptype Ajax Framework example

Configuring OECGI2

zapher67 — Wed, 12 Mar 2008 16:45:52 CDT

White paper discussing how to install and configure the OECGI2 gateway on IIS and apache.

See also the whitepaper on installing and running the oeSocketServer service and the Prototype Ajax Framework Example .

Popups under Control/EdiTable cell

BarryStevens — Sun, 09 Mar 2008 17:32:23 CDT

This function will call a popup and place it under a control or EditTable cell.
the value will be returned to the control; OR the calling ssp only ( NoCtrlRet)

Can be used as a quickevent.

OLE Adobe PDF - FDF

bornecw — Mon, 28 Apr 2008 22:15:37 CDT

In order to get this to work, you must have Adobe Professional installed on the machine that will be running this code. Adobe Pro will install the necessary files to get the OLE working.

1) Create or edit a pdf file by adding text controls in the necessary places.
2) Paste the below code into OI.
3) Basic Utilization -

CLB_PDF('CREATE','\\Ns1\test.pdf')
CLB_PDF('SET','DealershipName','TEST')
CLB_PDF('SET','MailingAddress','123 1st St')
CLB_PDF('SET','CityStZip','Ny, NY 21236')
CLB_PDF('SET','DealerNumber','')
CLB_PDF('SET','DateFromThrough',fdate:'-':tdate)
CLB_PDF('SET','DealerContactPerson','MikeVince')
CLB_PDF('SET','DealerContact','713-555-1212')
CLB_PDF('SET','PageNumber',PNo)
CLB_PDF('SET','OfPageNumber',TPages)
CLB_PDF('SET','PageTotal',OConv(AmtTotal,'MD2,$'))
CLB_PDF('SET','TotalNumberOfAgreements',@Reccount)
CLB_PDF('CLOSE','','','Register.fdf')
CLB_PDF('OPEN','','',nfilename)

_________________________________________________________________________________

Subroutine FDF_PDF(Type, Param2, Param3, FileName)
************************************************************************************************
*
*
* Name: FDF_PDF
*
* Original Programmer: Clay Borne
*
* Description: The FDF_PDF procedure fills in form fields in an Adobe PDF
*
* Parameters:
* Type [in] -- CREATE, SET, CLOSE, OPEN
*
* Param2 [in] -- If CREATE - FileName with path
* If SET - Control Name
* If CLOSE - BLANK
* If OPEN - BLANK
*
* Param3 [in] -- If CREATE - BLANK
* If SET - Value to Set Control to
* If CLOSE or OPEN - BLANK
*
* Returns:
* Nothing
*
* History (Date, Initials, Notes)
* 07/24/07 CWB Original Programmer
*
************************************************************************************************
COMMON /%%FDF_PDF%%/ FileName@,FDFName@,FdfAcX@,myFdf@
DECLARE FUNCTION OleCreateInstance,OleCallMethod

BEGIN CASE

CASE TYPE = 'CREATE'
FdfAcX = OleCreateInstance("FdfApp.FdfApp" )
FdfAcX@ = FdfAcX
myFdf = OleCallMethod(FdfAcX,'FDFCreate')
myFdf@ = myFdf
FileName@ = Param2

CASE TYPE = 'SET'
ret = OleCallMethod(myFdf@,'fdfsetvalue',Param2,Param3, 0)

CASE TYPE = 'CLOSE'
ret = OleCallMethod(myFdf@,'FDFSetFile',FileName@)
ret = OleCallMethod(myFdf@, 'FDFSaveToFile',{PATH}:FileName )
ret = OleCallMethod(myFdf@,'fdfclose')

CASE TYPE = 'OPEN'
program= {PATH}:Filename
mode=3
rv=Utility("RUNWIN",program,mode)

END CASE

return

Rev Guy Reveals No1. Promoted Events

zapher67 — Wed, 12 Mar 2008 16:43:35 CDT

Backgound information on Event Processing in OpenInsight and covers how to create a promoted event.

Whitepapers

zapher67 — Sun, 02 Mar 2008 15:13:49 CST

A collection of whitepapers on various OpenInsight topics.

Sending and receiving e-mail using MAPI

zapher67 — Tue, 19 Feb 2008 15:16:27 CST

This whitepaper covers everything you need to know about sending and receiving e-mail via the MAPI interface built in to OpenInsight since v3.5x.

Mail

zapher67 — Thu, 21 Feb 2008 15:05:44 CST

There is no abstract available for this page revision.

RGD LatLong Conversion

zapher67 — Tue, 19 Feb 2008 15:12:19 CST

Function to convert latitude and longitude between degrees minutes and seconds and decimal format.

Pass the latitude and longitude in either format to this function and it will return the other format.

Handy for mapping applications.

Source code attached below.