brixxbox Wiki
  • Einstieg und Feature-Übersicht
    • Technische Übersicht
    • Praxisbeispiele
    • Responsive Design
    • Rollen und Rechte
    • Mandanten und Währungsfähigkeit
    • Multi-Language
    • Integrierte KI
    • Integriertes Archiv
  • App Erstellung
    • Design-Cockpit
    • How Tos
      • Custom Grid Column Date Format
      • Create Report REST
      • Telerik Extension Functions
        • Telerik Page Footer Sum
        • Telerik Page Header Sum
      • Printing in LAN
  • Client API Reference
    • Functions
      • addCalendarEventSource
      • addClassToGridRowCell
      • addEventListener
      • addToolbarButton
      • brixxGPT
      • businessBrixx
        • DHL Shiping
          • CreateShipment
          • GetLabel
        • DPD Shiping
          • CreateShipment
        • UPS Shiping
        • Inventory Movement
        • Currency Conveter
        • Datev Export
          • Datev Export SqlDataRead Example
          • Datev Export sqlMainData Example
          • Datev Export sqlGetCounter Example
        • GS1 Code Splitter
        • SEPA Export
        • Diamant Connector
          • Diamant OpenStack
          • Diamant Transaction
          • Diamant CloseStack
      • cancelCalendarChanges
      • callWebHook
      • changeHelpText
      • clearGridSelection
      • cloudPrint
      • cloudQuery
      • closeModal
      • composeEmail
      • createPublicAppUrl
      • copyConfigRecordById
      • createReport
        • ZUGFeRD
      • deleteAttachment
      • deleteConfigRecordById
      • deleteRecord
      • displayRecord
      • downloadAttachments
      • disableGridHyperLinks
      • enableNotifications
      • enableValidator
      • excludeFromReadonly
      • executeStoredProcedure
      • getAttachmentId
      • getAttachmentById
      • getAttachmentByFileName
      • getAttachmentsForCurrentRecord
      • getLastAttachmentByRecordId
      • getBrowserToken
      • getControlUnmodifiedValue
      • getCustomMessage
      • getCustomSetting
      • getCalcDateTime
      • getHtmlElement
      • getDate
      • getCalcDate
      • getFieldValue
      • getSessionToken
      • getUserClaim
      • getConfigRecordAttachmentIdByFileName
      • getSignatureImageBlob
      • getGeoLocation
      • getLabelText
      • globalSearch
      • initAllControls
      • initControl
      • isModal
      • isUserInRole
      • isRowSelected
      • inviteUser
      • localValue
      • loadAndDisplayRecord
      • loadAndDisplayRecordById
      • loadConfigRecordByValue
      • loadConfigRecordById
      • loadRecord
      • loadRecordById
      • logAdd
      • logout
      • markAllControlsAsUnModified
      • messageBox
      • newGridEntry
      • newRecord
      • printBlob
      • print
      • readOnlyMode
      • refresh
      • refreshAttachments
      • refreshDataSource
      • refreshBadges
      • reInitValidation
      • replaceText
      • serverFunction
      • setControlUnmodifiedValue
      • setFieldValue
      • setLabelText
      • setTooltipText
      • setToolbarButtonMode
      • setEnable
      • setVisibility
      • startBrixxbox
      • startMode
      • startScanner
      • showBlob
      • showMessage
      • showAttachments
      • showDiscussion
      • showWikiPage
      • sqlReadValue
      • sqlWrite
      • sqlRead
      • saveCurrentRecordWithoutEvents
      • saveCurrentRecord
      • saveConfigRecord
      • saveStructure
      • setBackgroundColor
      • setFontStyle
      • setFontWeight
      • setGridDefaults
      • setTextColor
      • setFocus
      • setDecimalDigits
      • switchTagControl
      • selectGridRows
      • showRowDetailPanel
      • showRowDetailButton
      • setGridAutoRefresh
      • setGridGrouping
      • showMessageBox (deprecated)
      • showTabPage
      • setFieldUnit
      • takePicture
      • triggerEvent
      • toggleGridSelection
      • unselectGridRows
      • uploadAttachment
      • updateTitle
      • validateInput
      • queryStoredProcedure
      • setValidatorValue
    • Variables
      • userId
      • recordId
      • record
      • isLoadingRecord
    • Controls
      • Accordion
      • AppConfig
      • Badge
      • Button
      • Camera
      • Chart
      • CheckBox
      • ComboBox
      • DateBox
      • DateTimeBox
      • DocViewer
      • FileImport
      • FormGroup
      • FormGroupRow
      • FullCalendar
      • Grid
      • GridConfig
      • GroupBox
      • HorizontalLine
      • HtmlTable
      • HtmlTemplate
      • Image
      • Label
      • MultilineTextBox
      • NumBox
      • TabControl
      • TabPage
      • TextBox
      • LinkLabel
      • RadioButton
      • Report
      • Row
      • Scanner
      • SignaturePad
      • Tag
      • TemplateGrid
      • TemplateGridElement
      • TimeBox
      • Unit
      • WedgeScanner
      • Widget
      • WidgetContainer
      • WysiwygText
    • Events
      • onAppStart
      • onAppInitialized
      • onAttachmentDeleted
      • onAttachmentsShow
      • onAttachmentsHide
      • onChange
      • onCellClick
      • onCellEdit
      • onClick
      • onChildAppClosed
      • onDataTransform
      • onEventClick
      • onEventChange
      • onFileImport
      • onKeyDown
      • onKeyPress
      • onModalClose
      • onMailHistoryShow
      • onRowClick
      • onRecordLoad
      • onRecordLoaded
      • onRecordSave
      • onRecordSaved
      • onRowSelectionChanged
      • onRowCreated
      • onRecordNew
      • onRecordDelete
      • onRecordDeleted
      • onKeyUp
      • onScan
      • onReturnFromModal
      • onSubDataRequest
      • onTabShown
      • onTimeSelected
      • onPictureTaken
      • OnCheckPermissions
    • Tips and Tricks
      • Date and Time
        • The moment.js Library
  • Server API Reference
    • Functions
      • loadConfigRecordById
      • saveConfigRecordById
      • sqlWrite
      • sqlRead
      • sendEmail
      • createReportPdfBlob
      • cloudPrint
      • businessBrixx
  • Configuration
    • Workspace Settings
      • CustomHeaderFooterBackgroundColor
      • CustomInviteText
      • CustomLogoBackgroundColor
      • CustomLogoUrl
      • RecordLockWaitTime
      • WhiteLabeling
    • App Editor
      • Custom HTML Templates
        • jsTree
        • Slider
      • App Editor Properties
        • AddToAttachements
        • AppAltViewName
        • AppAttachments
        • AppColor
        • AppConfigToStartName
        • AppConfigToStartSourceField
        • AppConfigToStartTargetField
        • AppDiscussion
        • AppHistory
        • AppIcon
        • AppMailHistory
        • AppMasterTable
        • AppParameters
        • AppPrefix
        • AppPreviewRecordId
        • AppTitle
        • AttaDocType
        • AttachementFilename
        • AutoDisableOnEdit
        • ButtonStyleClass
        • BlockInputDuringEventExecution
        • CascadeCopy
        • CascadeDelete
        • ChartData
        • ChartData
        • ChartDataJson
        • ChartLabel
        • ChartLabelAxesX
        • ChartLabelAxesY
        • ChartType
        • ComboBoxEditButton
        • ComboBoxListButton
        • ComboboxKey
        • ComboboxMinSearchLength
        • ComboboxMultiselect
        • ComboboxValue
        • ConcurrencyControl
        • CreateMenu
        • CssTemplateContent
        • Data
        • DateTimeUtc
        • DecimalDigits
        • DefaultValue
        • DevMode
        • DisableGridResponsiveness
        • EditAbsoloutGridHeight
        • EditDefineUnit
        • Enable
        • GridAutoRefreshIntervals
        • GridCellEdit
        • GridColumnFilter
        • GridColumnOrder
        • GridFilterVisibility
        • GridFooter
        • GridInlineEdit
        • GridNoHyperLink
        • GridPageSizeVisibility
        • GridRowOrderDragDropParam
        • GridSelectMode
        • GridSortingMode
        • GridToolbarVisibility
        • GroupGridColumns
        • HelpText
        • HiddenGridColumns
        • HideEditButtonColumn
        • HideSelectCheckboxColumn
        • HtmlTableClasses
        • HtmlTableDisablePagination
        • HtmlTemplateContent
        • Id
        • IncludeInGlobalSearch
        • InputMaskRegEx
        • InputTag
        • Label
        • LabelWidthClass
        • LabelWrap
        • LoadRecordOnLeave
        • ManualRefreshOnly
        • NoSpinButton
        • RecordObservation
        • RefersToConfig
        • ReportCulture
        • ReportId
        • RowReorder
        • SearchPrefixes
        • SelectAllOnFocus
        • ServerSidePaging
        • ServerSidePagingIndexColumns
        • SmoothScroll
        • TagValue
        • TextColor
        • ToolTip
        • ValidateInputBeforeExecution
        • ValidatorMessage
        • Visibility
        • WidthClass
        • WysiwygHtmlCleanup
        • WysiwygHtmlFragment
      • Controls
        • grid
        • combobox
    • Template Gallery
      • Extended CRM-System
      • Time Recording
      • Project Management
      • Digital Visitor-Management
      • Data Archive
      • Ticket-System
      • Digital Adressbook
      • Driver's Logbook
      • Order Management
      • Basic CRM
      • 🇩🇪 Erweitertes CRM-System
      • 🇩🇪 Zeiterfassung
      • 🇩🇪 Digitales Besuchermanagement
      • 🇩🇪 Datenarchiv
      • 🇩🇪 Projektmanagement
      • 🇩🇪 Ticketsystem
      • 🇩🇪 Digitale Adressverwaltung
      • 🇩🇪 Fahrtenbuch
      • 🇩🇪 Auftragsverwaltung
      • 🇩🇪 Basis CRM
    • Apps
      • ApiKeys
      • CloudGateway
      • Attachments
      • AuditHistory
      • DevelopmentChangeHistory
      • DocumentTypes
      • Jobs
        • Cron Definitions
      • Menu Editor
      • Reports
      • Roles
      • ServerSideFunction
      • SqlFunction
      • SqlStoredProcedure
      • SqlTrigger
      • StandardDatasources
      • StandardMessages
      • StdSqlEditor
      • Translations
      • UserDashboards
      • Users
      • WorkspaceFirewall
      • WorkspaceSettings
        • CustomInviteEmailContent
Powered by GitBook
On this page
  • Demo
  • Syntax
  • Parameters
  • Return Value
  • Description
  • Examples
  • Notes
Export as PDF
  1. Client API Reference
  2. Functions

sqlWrite

This method executes an SQL statement that can modify data (INSERT, UPDATE, DELETE) or return data (when the statement includes SELECT, OUTPUT INSERTED, or RETURNING clauses).

PrevioussqlReadValueNextsqlRead

Last updated 1 month ago

See SQL Statements for how to create statements.

Demo

Syntax

sqlWrite(statementName, parameterJson, cmdOptions)

Parameters

Parameter
Type
Description

statementName

string

The name of the SQL statement to execute

parameterJson

object

A JSON object with additional parameters. All controls in the current configuration are automatically set as parameters.

cmdOptions

object

(optional) A JSON object with options for the request

cmdOptions.timeout

number

(optional) Timeout for the SQL request in milliseconds. Default is 30 seconds.

cmdOptions.connectionKey

string

(optional) A key to a custom settings entry with the connection string to an external MSSQL database

Return Value

The method returns a Promise that resolves to an object with the following properties:

Property
Type
Description

ModifiedRecordCount

number

The number of affected rows for INSERT, UPDATE or DELETE operations

SubData

array

Returned data when the SQL statement includes SELECT, OUTPUT INSERTED, or RETURNING clauses

Description

The sqlWrite method executes an SQL statement that is stored in the database. This method is intended for operations that modify data in the database, but it can also return data if the SQL statement is appropriately structured.

The method automatically detects whether the SQL statement should return results:

  • For simple INSERT, UPDATE, or DELETE statements, only the count of affected records is returned.

  • If the SQL statement begins with SELECT or includes OUTPUT INSERTED or RETURNING clauses, the returned data is made available in the SubData array.

Examples

Simple INSERT Statement without Return Value

// Execute a simple INSERT statement
const result = app.sqlWrite("InsertNewCustomer", {
    customerName: "Example Company Ltd", 
    contactPerson: "Jane Doe"
});
console.log(`${result.ModifiedRecordCount} records were inserted`);

UPDATE Statement with Custom Options

// UPDATE with longer timeout
const result = app.sqlWrite("UpdateCustomerAddress", {
    customerId: 12345,
    street: "123 Main Street",
    city: "London",
    zipCode: "E1 6AN"
}, {
    timeout: 60000 // 60 seconds timeout
});
console.log(`${result.ModifiedRecordCount} customer addresses were updated`);

Statement with Data Return (OUTPUT INSERTED)

// INSERT with OUTPUT INSERTED clause
const result = app.sqlWrite("InsertProductWithReturn", {
    productName: "New Product",
    price: 99.99,
    category: "Electronics"
});

if (result.SubData && result.SubData.length > 0) {
    console.log(`Product with ID ${result.SubData[0].ProductId} was created`);
    // Access other returned fields
    console.log(`Creation date: ${result.SubData[0].CreatedDate}`);
}

Using an External Database Connection

// Execute SQL statement on external database
const result = app.sqlWrite("DeleteExpiredOrders", {
    olderThanDays: 365
}, {
    connectionKey: "ExternalArchiveDB"
});
console.log(`${result.ModifiedRecordCount} old orders were deleted`);

Notes

  • The SQL statements must be predefined in the system.

  • All parameters are automatically protected against SQL injection attacks.

  • When using OUTPUT INSERTED or RETURNING clauses, the results are returned in the SubData array while the number of inserted/updated rows is stored in ModifiedRecordCount.

Use for pure read operations when you don't need to modify data.

sqlRead