Skip to main content

%SYS.PTools.SQLQuery

persistent class %SYS.PTools.SQLQuery extends %Library.Persistent

SQL Table Name: %SYS_PTools.SQLQuery

This is the parent class of %SYS_PTools.SQLStats.

This class stores the general info of a query from compile time: QueryText, QueryType, CursorName, RoutineName, and NameSpace. All of the run time data is stored in the %SYS_PTools.SQLStats class.

Error information is stored in ^%sqlcq(NAMESPACE,"PTools","Error",$J).

Property Inventory

Method Inventory

Properties

property CompileTime as %Numeric;
Number of Seconds it takes to compile the query
Property methods: CompileTimeDisplayToLogical(), CompileTimeGet(), CompileTimeGetStored(), CompileTimeIsValid(), CompileTimeLogicalToDisplay(), CompileTimeNormalize(), CompileTimeSet()
property CursorName as %String [ Required ];
What is the name of the cursor for this SQL Statement
Property methods: CursorNameDisplayToLogical(), CursorNameGet(), CursorNameGetStored(), CursorNameIsValid(), CursorNameLogicalToDisplay(), CursorNameLogicalToOdbc(), CursorNameNormalize(), CursorNameSet()
property Hash as %String (MAXLEN = 500);
hash the SQL so we can match incoming SQL with already stored Stats
Property methods: HashDisplayToLogical(), HashGet(), HashGetStored(), HashIsValid(), HashLogicalToDisplay(), HashLogicalToOdbc(), HashNormalize(), HashSet()
property ImportSchema as %String;
ImportSchema is used in generated code to handle sql statements that have unqualified table names.
Property methods: ImportSchemaDisplayToLogical(), ImportSchemaGet(), ImportSchemaGetStored(), ImportSchemaIsValid(), ImportSchemaLogicalToDisplay(), ImportSchemaLogicalToOdbc(), ImportSchemaNormalize(), ImportSchemaSet()
property InternalQueryText as %String (MAXLEN = 30000, TRUNCATE = 1);
Property methods: InternalQueryTextDisplayToLogical(), InternalQueryTextGet(), InternalQueryTextGetStored(), InternalQueryTextIsValid(), InternalQueryTextLogicalToDisplay(), InternalQueryTextLogicalToOdbc(), InternalQueryTextNormalize(), InternalQueryTextSet()
property NameSpace as %String;
What NameSpace the SQL was generated in
Property methods: NameSpaceDisplayToLogical(), NameSpaceGet(), NameSpaceGetStored(), NameSpaceIsValid(), NameSpaceLogicalToDisplay(), NameSpaceLogicalToOdbc(), NameSpaceNormalize(), NameSpaceSet()
property QueryText as %String (COLLATION = "SQLUPPER(255)", MAXLEN = 30000, TRUNCATE = 1) [ Calculated ];
Store the first 15000 charactors of the SQL Query in this field.
Property methods: QueryTextCompute(), QueryTextDisplayToLogical(), QueryTextGet(), QueryTextIsValid(), QueryTextLogicalToDisplay(), QueryTextLogicalToOdbc(), QueryTextNormalize(), QueryTextSQLCompute()
property QueryType as %String;
Is the Query an INSERT UPDATE DELETE or SELECT query
Property methods: QueryTypeDisplayToLogical(), QueryTypeGet(), QueryTypeGetStored(), QueryTypeIsValid(), QueryTypeLogicalToDisplay(), QueryTypeLogicalToOdbc(), QueryTypeNormalize(), QueryTypeSet()
property RoutineName as %String [ Required ];
What Routine is the SQL generated in
Property methods: RoutineNameDisplayToLogical(), RoutineNameGet(), RoutineNameGetStored(), RoutineNameIsValid(), RoutineNameLogicalToDisplay(), RoutineNameLogicalToOdbc(), RoutineNameNormalize(), RoutineNameSet()
property RunCount as %Integer [ InitialExpression = 0 ];
Number of times this query has be executed
Property methods: RunCountDisplayToLogical(), RunCountGet(), RunCountGetStored(), RunCountIsValid(), RunCountLogicalToDisplay(), RunCountNormalize(), RunCountSet()
relationship SQLStatsPointer as array of %SYS.PTools.SQLStats [ InitialExpression = $listbuild("%SYS.PTools.SQLStats","SQLQueryPointer",+$this,"children",1,1) , Transient , Inverse = SQLQueryPointer , Cardinality = children ];
Property methods: SQLStatsPointerGet(), SQLStatsPointerGetObject(), SQLStatsPointerGetObjectId(), SQLStatsPointerGetSwizzled(), SQLStatsPointerIsEmpty(), SQLStatsPointerIsValid(), SQLStatsPointerNewObject(), SQLStatsPointerRClose(), SQLStatsPointerRExec(), SQLStatsPointerRFetch(), SQLStatsPointerRelate(), SQLStatsPointerSQLCompute(), SQLStatsPointerSet(), SQLStatsPointerUnRelate()
property details as %Integer;
Do we have level 3 stats for this query
Property methods: detailsDisplayToLogical(), detailsGet(), detailsGetStored(), detailsIsValid(), detailsLogicalToDisplay(), detailsNormalize(), detailsSet()

Methods

classmethod Export(FileName="", delim="", ExportPlan=0) as %String

##class(%SYS.PTools.SQLQuery).Export(FileName,delim,ExportPlan)

FileName
Path and name of the file you want to export to.
delim
Default value $c(9) (tab) Delimiter for the columns of the export file.
ExportPlan
Optional. Default value = 0 if 1 then the SQL Show Plan will be included as part of the export
This method is used to export data from the %SYS.PTools.SQLQuery class to a delimited text file.
classmethod LogHeader(File, version, delim)
classmethod NewQuery(ByRef SQLText As %String, SQLType As %String, NameSpace As %String, RoutineName As %String, CursorName As %String, CompileTime As %Numeric = 0) as %Status
This method is called at compile time for a query It deletes all current stats for this query, as they could be very different after the compile

Indexes

index (master on RoutineName,CursorName) [IdKey, Type = key, Unique];
Index methods: masterCheck(), masterDelete(), masterExists(), masterOpen(), masterSQLCheckUnique(), masterSQLExists(), masterSQLFindPKeyByConstraint(), masterSQLFindRowIDByConstraint()

Inherited Members

Inherited Methods

Storage

Storage Model: CacheStorage (%SYS.PTools.SQLQuery)

^%sqlcq($NAMESPACE,"PTools","RunTime","SQLQueryD")(ID)
=
%%CLASSNAME
InternalQueryText
QueryType
CompileTime
Hash
NameSpace
RoutineName
CursorName
QueryText
ImportSchema
RunCount
details
FeedbackOpens in a new tab