Configuring Code-based Objects

Configuring Code-based Objects

The SaveToDB add-in allows using SQL codes, HTTP queries, and text file queries like other database objects.

You may configure these objects in the following configuration fields:

In the first case, users may connect to such objects and select data into Excel tables.

In the second case, for example, you may configure processing Excel events using SQL codes or opening URLs from the context menu.

In the third case, you may use SQL codes to populate ribbon parameter values.

Specify the appopriate object type like CODE, HTTP, or TEXT in the fields like TABLE_TYPE, HANDLER_TYPE, and SELECT_TYPE.

You may use SQL codes in the INSERT_PROCEDURE, UPDATE_PROCEDURE, and DELETE_PROCEDURE fields to save data changes.

SQL Codes

SQL codes can contain parameters in the following formats:

  • @ParameterName for Microsoft SQL Server, Microsoft SQL Server Compact, and SQLite.
  • :ParameterName for Oracle Database, IBM DB2, MySQL, MariaDB, NuoDB, Snowflake, and PostgreSQL.

The SaveToDB add-in shows parameters on the ribbon, and users can change the values.

Here is an example of an SQL code:

SELECT * FROM dbo.StockTradeHistory th WHERE th.Symbol = @Symbol

HTTP Queries

The SaveToDB add-in shows URL parameters on the ribbon and supports the standard scheme of URL parameters: ?ParameterName1=Value1&ParameterName2=Value2...

So, you may paste URLs from a web browser into the fields like TABLE_CODE without any change.

Alternatively, you may redefine query parameters in the following format: {ParameterName=DefaultValue}

Use this to hide technical parameters.

For example, you may define a single parameter, Symbol, instead of URL parameters:

http://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20yahoo.finance.quotes%20where%20symbol%20in%20%28%22{Symbol=YHOO}%22%29&diagnostics=false&env=store%3A%2F%2Fdatatables.org%2Falltableswithkeys

The SaveToDB add-in includes parsers for HTML, XML, JSON, CSV, and plain text data.

So, you will get structured data in Excel.

You may tune parsers adding parser parameters at the end of URL separated by semicolons.

See details in the Connecting to Web Data topic.

Text File Queries

You may specify file names including code pages in the following format:

<File name>[;CodePage=<Code page>][;<Parser parameters>]

For example:

Contacts.csv;CodePage=65001

The file name can contain a relative path to the workbook directory.

In this case, you may move the file with the workbook to another place.

You may define query parameters in the following format: {ParameterName=DefaultValue}

For example:

{FileName=Contacts.csv};CodePage={CodePage=65001}

In this example, the add-in shows the FileName and CodePage parameters on the ribbon, and users can changed them.

The SaveToDB add-in includes parsers for HTML, XML, JSON, CSV, and plain text data.

So, you may connect not only to CSV files.

Parameter Insertions

Below is a summary table of special parameter insertions:

Query TypeType KeywordParameter FormatExamples
SQL code for Microsoft SQL Server,
Microsoft SQL Server Compact, and SQLite
CODE@Parameter[=DefaultValue]SELECT * FROM Sales.Contacts WHERE Name = @Name
SELECT * FROM Sales.Contacts WHERE Name = @Name=ABC
SQL code for Oracle Database, IBM DB2, MySQL,
MariaDB, NuoDB, Snowflake, and PostgreSQL
CODE:Parameter[=DefaultValue]SELECT * FROM SALES.CONTACTS WHERE NAME = :Name
SELECT * FROM SALES.CONTACTS WHERE NAME = :Name=ABC
HTTP queryHTTPStandard HTTP parametershttp://www.google.com/finance/historical?q=GOOG
HTTP queryHTTP{Parameter[=DefaultValue]}http://www.google.com/finance/historical?q={Symbol=GOOG}
https://www.google.com/search?as_q={Query}
Text queryTEXT{Parameter[=DefaultValue]}{FileName};CodePage=65001
{FileName};CodePage={CodePage=65001}
Macro commandMACRO{Parameter[=DefaultValue]}SayHello {Name=World}
Sheet1.SayHello {FirstName}, {LastName}
Windows Shell or CMDCMD{Parameter[=DefaultValue]}{FileName}
notepad.exe {FileName}
dir {Mask=*.*}
cmd /c dir {Mask=*.*}
cmd /k dir *.*
mailto:{Email}&subject=Thanks for the connection&body=Hello {FirstName},%0A

Remark: if the default value is specified, the equal sign "=" must have no leading or trailing spaces.

This website is using cookies. By continuing to browse, you give us your consent to our use of cookies as explained in our Cookie Policy.