Smartsite Documentatie > SXML > Macro > SqlQuery Macro
SCAS Level
Executes sql queries/statements and shows the results.

Syntax

Smartsite SXML CopyCode image Copy Code 
<se:sqlquery>
    <se:parameters>
        <se:parameter name="sql">
            
        </se:parameter>
    </se:parameters>
</se:sqlquery>

Parameters

Name Data Type Properties Description
Connection String Trim
Sets the connection used for the Sql query.
Format String RawData
Sets the formatting Xml.
MaxRows Integer - Sets a limit for the number of rows that the output may contain.
Mode Enum - The mode determines if a datatable, a scalar or the number of affected records is returned.
Enum values:
  • DataTable
    A datatable is returned.
  • Scalar
    A scalar is returned.
  • NonQuery
    The affected record count is returned.
  • StoredProcedure
    Executes a stored procedure.
PagingId String - Sets the id of an associated paging macro.
ParameterValidation Enum - Determines how sql parameters are checked before executing.
Enum values:
  • Strict
    The parameters given in the macro must match those in query.
  • Relaxed
    The parameters given in the macro are not validated by Smartsite. They are only validated by the database.
Params Collection - Sql params Xml section.
ReferencedTables Collection - Sets the referenced tables for AIM dependency checking.
RowDelimiter String - Specifies the delimiter string that separates individual records.
RowFormat String RawData
Sets the formatting string.
SkipRows Integer - Specifies which row will be the first row in the output.
Sql String IsRequired
XmlDecode
Trim
Sql statement to execute.
UsePreparser Boolean - Sets if the preparser will be used when the query is executed.

Shared Parameters

Name Data Type Properties Description
Access String - Friendly name of the minimum access type level required by the site visitor to execute this macro.
AccessDenied String RawData
Sets the value that is returned when the site visitor has insufficient access.
AIM Enum - Sets the AIM scanning mode of the macro.
Enum values:
  • NotSet
    Not set.
  • Off
    No AIM scanning.
  • External
    Output XML scanning.
  • Internal
    Internal scanning, implemented by AIM-aware macros.
  • Autonomous
    Relations are to be linked to the current source item, instead of the rendered page.
  • SoftLink
    Should generate soft links (in combination with External flag).
  • InternalAutonomous
    Internal scanning, combined with the Autonomous setting.
  • ExternalAutonomous
    External scanning, combined with the Autonomous setting.
  • ExternalAutonomousSoftLink
    External scanning, combined with the Autonomous and softLink settings.
  • ExternalSoftLink
    External scanning, combined with the Softlink setting.
Condition String MustBeAttribute
ExpressionSyntax
XmlDecode
Sets the Viper expression to evaluate before actually executing the macro.
If the expression returns false, the execution of the module and its child modules is skipped.
Default String RawData
Sets the default value that will be returned when the execution would otherwise return an empty string.
Error String RawData
Sets the value to return in case on error occurs during execution.
The Viper expressions this.error(), this.error(full) and this.error(type) are available in this context.
Id String MustBeAttribute
Trim
Sets the Id of the macro. Macros with Ids set can be accessed using Viper by referring to page.[id].[methodname()].
LocalId String MustBeAttribute
Trim
Sets the local id of the macro in the current buffer scope.
Rem String RawData
NoCache
Trim
Sets the remarks. Remarks have no effect on the actual execution of the module.
They only improve the readability of the code.
ResultFormat String RawData
Sets the resultformat.
ResultType Enum - Sets the type of the result.
Enum values:
  • Undefined
    Not set
  • None
    No result
  • String
    Default type
  • Object
    Generic object
  • Binary
    Binary data
  • Boolean
    Boolean
  • DataTable
    DataTable
  • DateTime
    Date/time
  • Float
    Floating point numeric data
  • Integer
    Integer
  • Locator
    Extended Uri
  • Xml
    Xml Data
Save Collection - Sets the buffer names.
Status Enum - Gets a value indicating the ModuleStatus of this module.
Enum values:
  • Unknown
    Unknown
  • BeginInitialize
    Initializing the module
  • EndInitialize
    Module has fully initialized
  • BeginSetProperties
    Properties are about to be set
  • EndSetProperties
    Properties have been set
  • BeginExecute
    Execution starts
  • BeginFormatData
    Starting data formatting.
  • EndFormatData
    Ended data formatting.
  • BeginFormatResult
    Before formating result.
  • EndFormatResult
    After formatting result
  • EndExecute
    Execution is ending
  • StartLoseScope
    Execution has completed
  • EndLoseScope
    Module is about to unload
  • Error
    Module is in an error state
Timeout Float - Sets the timeout in seconds. In the overriden Execute() method, you can call CheckTimeout() to check whether execution has timed out.
Trim Enum MustBeAttribute
Gets a value indicating how the whitespace is trimmed.
Whitespace Enum MustBeAttribute
Gets a value indicating whether to preserve whitespace.
Enum values:
  • Inherit
    Inherit from parent.
  • Preserve
    Keep all whitespace.
  • Remove
    Remove all whitespace.
  • Normalize
    Convert all whitespace to single spaces.
  • SimpleFormat
    Convert all whitespace to single spaces, but keep single line breaks.

Local Viper Methods

Viper Extension Description
This.AffectedRecords   Returns the number of affected records when executing in scalar mode.
This.ChildLevelResult   Gets the result of the child levels.
This.ClearForcedResult   Sets the result of the macro.
used to override the normal handling of the result creation.
This.ColCount   Gets the number of columns in the datatable.
This.ColName   Gets the name of the current column.
This.ColNumber   Gets the number of the current column.
This.Field   Gets the field value of the current column in the current row, when using colformat.
This.Field.Exists   Checks whether the given field exists.
This.GetForcedResult   Gets the forced result of the macro, set by overriding the normal rendering using SetForcedResult().
This.Group   Gets the name of the current group.
This.GroupCount   Gets the total number of groups displayed.
This.GroupNumber   Gets the number of the current group.
This.GroupResult   Gets the result of the current group.
This.GroupRowNumber   Gets the number of the current row in the current group.
This.Level   Gets the current formatting level.
This.LevelResult   Gets the result of the current level.
This.LevelRowNumber   Gets the current row of the current level.
This.Location   Gets the location the current row points to, in a macro that can build URLs.
This.MaxRows   Gets a limit for the number of rows that the output may contain.
This.Name   Gets the title or name represented by the current row.
This.param   Gets the value of a parameter with the given index. Useful when using the viper:exec inside xslt formatting.
This.RawField   Gets the raw field value of the current column in the current row, when using colformat.
This.RenderTime ✓ Returns the render time of the macro in milliseconds.
This.RowCount   Gets the total number of rows in the datatable.
This.RowNumber   Gets the current row number.
This.RowResult   Gets the result of the current row.
This.SetForcedResult   Sets the result of the macro.
used to override the normal handling of the result creation.
This.SkipRows   Specifies which row will be the first row in the output.
This.Title   Gets the title represented by the current row.
This.VisibleRowCount   Gets the total number of rows displayed.
This.VisibleRowNumber   Gets the current visible row number.

Shared Local Viper Methods

Viper Extension Description
This.AIM.Relations ✓ Gets a list of all outgoing AIM relations created by the macro.
This.Buffer.Exists   Checks whether a local buffer exists.
This.Buffer.Get   Gets a local buffer.
This.Buffer.Set   Sets a local buffer.
This.Error.InnerException.Message   Returns the text message of the inner exception of the exception that has occurred.
This.Error.InnerException.SmartsiteCode   Returns Smartsite error code of the inner exception of the exception that has occurred.
This.Error.InnerException.Throw   Makes Smartsite throw the inner exception.
This.Error.InnerException.ToString   Returns the full information of the inner exception of the exception that has occurred, including the stack trace.
This.Error.InnerException.Type   Returns the full Type name of the inner exception of the exception that has occurred.
This.Error.Message   Returns the text message of the exception that has occurred.
This.Error.SmartsiteCode   Returns the Smartsite Error code of the exception that has occurred.
This.Error.Throw   Makes Smartsite throw the current exception.
This.Error.ToString   Returns the full information of the exception that has occurred, including the stack trace.
This.Error.Type   Returns the full Type name of the exception that has occurred.
This.FindExtension   Finds an extension macro.
This.GetParameter ✓ Gets the value of the given parameter/property.
This.ParameterExists ✓ Checks whether the given parameter/property is set.
This.Result   Returns the result of the macro.
Typically used in the ResultFormat property, to format, convert or encode the result.
This.SXMLPath   Gets the path to the macro in the current execution stack.

Expand image Examples

SXML