DBOpen and DBGet

Created On
byadmin
You are here:

 

Works in Converted Applications and Designer Applications

 

DBOpen and DBGet are custom SpreadsheetWEB functions used for database calls. While DBOpen establishes a connection between the application and the database; DBGet uses this connection to get and send query results.

DBOpen and DBGet functions are only supported in SpreadsheetWeb Private Cloud and Server accounts.

Please note that; if you are creating a standard SpreadsheetWEB Web application, both functions should be in the display area of your application.

DBOpen

DBOpen creates a connection to the data source.

Syntax

=DBOpen(connection_string, pooling, output_range)

Argument Name
Type
Default
Description
connection_string String String that specifies a data source and the means for connecting to it. You can use 2 types of strings:

  1. Standard connection string: “Provider=SQLOLEDB; Data Source=localhost; Initial Catalog=Northwind; Integrated Security=SSPI”
  2. Generated Key for internal SpreadsheetWeb connections. This key allows user to connect all tables in a site without revealing critical information, such as database credentials. This key is accessible only to the System Admin user.
  3. Alternatively, SpreadsheetWEBConnectionString function can be used to get generated key dynamically.
pooling Boolean TRUE A logic value that enables or disables use of connection pool. A connection pool is a cache of database connections, maintained so that the connections can be reused, when required.[citation needed] Connection pools are used to enhance the performance of executing commands in a database.
output_range String “” String value that specifies the cell reference for the result. If empty, the result returns in the host cell, as a regular function. e.g. “Sheet1!C3”, “My Connection Sheet!F25”

Returns

Integer. Returns integer which is used by DBGet function to query database and set them to the application. If connection is not successful than the return value is 0.

Examples

Formula
Description
DBOpen(SpreadsheetWEBConnectionString(),,)
 Default and recommended use to connect an application in a SpreadsheetWEB system.
Function returns a value to its own cell. Pooling is enabled.
DBOpen(“Provider=SQLOLEDB; Data Source=localhost; Initial Catalog=Northwind; Integrated Security=SSPI”,,)
 Default and recommended use to connect an external database.
Function returns a value to its own cell. Pooling is enabled.
=DBOpen(“Provider=SQLOLEDB; Data Source=localhost; Initial Catalog=Northwind; Integrated Security=SSPI”,FALSE,)
 Function returns a value to the original cell. Pooling is disabled.
=DBOpen(“Provider=SQLOLEDB; Data Source=localhost; Initial Catalog=Northwind; Integrated Security=SSPI”,,”Sheet1!C4″)
 Function returns a value to the specified string. Pooling is enabled.

Please note that cell reference is given as string, NOT as an Excel cell reference.
=DBOpen(“Provider=SQLOLEDB; Data Source=localhost; Initial Catalog=Northwind; Integrated Security=SSPI”,FALSE,”Sheet1!C4″)
 Function returns a value to the specified string. Pooling is disabled.

DBGet

DBGet executes a select query against the DBOpen connected data source. If the query was successfully executed; the function will populate a two dimensional result to a range, defined as the third parameter of the function.

Syntax

=DBGet(dbopen_result, query_string, output_range, [single_cell])

Argument Name
Type
Default
Description
dbopen_result Reference Cell reference of DBOpen’s output range. Use DBOpen in its default form(connection_string,,).
query_string String SQL query string. Please note that DBGet queries are limited to SELECT statement.
output_range String String value that specifies the cell for the result to be populated. e.g. “Sheet1!C3:G10”, “My Connection Sheet!F25:T42”
single_cell Boolean FALSE (Optional) Set TRUE to get query result as a single string in JSON format. Result is written in first cell of output_range. JSON format can be used directly in Custom Web Page via JavaScript as well as providing performance improvement.

Returns

Integer. Returns 0 the original cell.

Array. Returns query results as an array and sets them to a specified range if single_cell argument is FALSE.

String. Returns JSON string if single_cell argument is TRUE.

Examples

Formula
Description
=DBGet(A2, “SELECT * FROM Customers”, “Sheet1!C3:D10”)
 Uses the connection defined on A2 (DBOpen’s output cell) and places the results on the C3:D10 range, in Sheet1.
=DBGet(A2, “SELECT * FROM Customers”, “Sheet1!C3:D10”, TRUE)
 Uses the connection defined on A2 (DBOpen’s output cell) and places the result on the C3 cell, in Sheet1. Result is in JSON string format.