DbConnect Function

DbConnect Function

DbConnect is a custom SpreadsheetWEB Hub function used for database calls. The formula allows the application to connect an external database or a SpreadsheetWEB Hub application’s database and return values to specified range reference.

For simpler type of queries, we suggest you check out Queries feature. The Queries feature allows you to retrieve data from applications in your workspace by using an intuitive user interface: Queries

Note: DbConnect function is only supported in SpreadsheetWeb Hub Private Cloud and Server accounts.

Syntax

DbConnect(connection_string, query, output_range)
Argument Name Type Required? Description
connection_string String Optional String that specifies a data source and the means for connecting to it.

You can omit this argument if you wanted to work on application’s own database.

By default only current server, localhost, SQLEXPRESS are allowed. Please contact to change privileges.
query String Required SQL query string.

Only SELECT statement is accepted by default, Please contact to change privileges.

 
output_range String Required String value that specifies the reference for the result to be populated.

The reference can be a regular cell or range reference or a named range.

e.g.,

  • “Sheet1!C3:G10”
  • “‘My Connection Sheet’!F25:T42”
  • “ResultTable”

Stubs in queries

DbConnect function has a stub support which allows user to use table names directly instead of auto-generated names in the database.

A table name is the friendly name you use in the Designer application.

DbConnect Function - Designer Database Names

Names should be supplied between square and curly brackets. E.g., [{Primary Table}] or [{Contacts}].

Sample use case to return “Name” column in the “Primary Table”:

DbConnect(
   "SELECT TOP 1 [Name] FROM [{Primary Table}]
    ORDER BY [CreationDate] DESC",
   "Sheet1!B6")

The result will be populated in the cell B6 of Sheet1.

Warning: Stubs can be used only when accessing the application’s own database.

Predefined Stubs

DbConnect also supports stubs for auto-generated tables which keep the metadata of records. Each of the following tables share Id, EditId and SequenceId columns along with the primary and secondary tables to be related easily.

  • Details: ActionUserId, ActionClientId, EntryDate, Notes
  • Tags: TagId, TagName
  • Overrides: Overrides

Examples

Get active data from the primary table of application database

  • Primary Table: “Pri”
  • Return range: “‘Historical Data’!A2:C10”
DbConnect(
    "SELECT [Client Name], [State], [Salary] FROM [{Pri}]
     WHERE IsActive = 1",
    "'Historical Data'!A2:C10")

Return the number of active records entered after Sept. 9, 2022

  • Primary Table: “Sales”
  • Details Table: “Details” (Predefined table)
  • Return range: “RecentRates”
DbConnect(
  "SELECT COUNT(*) FROM [{Sales}] a
   INNER JOIN [{Details}] b
   ON a.id = b.id
   WHERE b.EntryDate > '2022-09-01' AND a.IsActive = 1",
  "RecentRates")

Get “total Cost” value in active records with tag “Waiting Order”

  • Primary Table: “Orders”
  • Secondary Table: “OrderItems”
  • Tags Table: “Tags” (Predefined table)
  • Return range: “WaitingOrders”
DbConnect(
   "SELECT
      [Order].OrderId,
      SUM([OrderItems].Cost)
    FROM
    (([{Order}] AS [Order]
    INNER JOIN [{OrderItems}] AS [OrderItems] ON (
      [Order].Id = [OrderItems].Id
      AND [Order].EditId = [OrderItems].EditId ))
    INNER JOIN [{Tags}] AS [Tags] ON (
      [Order].Id = [Tags].Id
      AND [Order].EditId = [Tags].EditId ))
    WHERE (
      [Order].[IsActive] = 1
      AND [Tags].TagName = "Waiting Order" )
    GROUP BY
      [Order].OrderId",
   "WaitingOrders")

Get data from an external database (not allowed in private servers)

  • Connection String: “Data Source=.\SQLEXPRESS;Initial Catalog=MyDb;User ID=username;Password=pass;” (You can give a direct cell reference or name)
  • Return range: “EmployeeTable”
DbConnect(
   "Data Source=.\SQLEXPRESS;Initial Catalog=MyDb;User ID=username;Password=pass;",
   "SELECT [Id],[Name],[Occupation]
    FROM [Employee]",
   "EmployeeTable")
Tags:
Next SpreadsheetWebRecordInfo