The SAP HANA Database Explorer Visual Studio Code extension was released to the Visual Studio Code marketplace in early 2022. Since then many new features have been introduced to improve the user experience and add new functionality. In this blog post I'll highlight some of them. You can find the full list of features in the change log.
Database object dependencies can be visualized using a graphical dependency viewer. The dependency viewer can be started either by right-clicking an object in the catalog browser and choosing "Open Dependency Viewer" from the context menu, or by right-clicking a database connection.
When starting the dependency viewer from an object in the catalog browser, the dependency viewer directly shows the dependencies of the selected object.
The dependencies can be explored in both directions, incoming and outgoing. Schemas are indicated as boxes surrounding the objects. Schemas can be collapsed by double-clicking them to focus on the relevant dependencies. Clicking on an object highlights it and its direct dependencies for easier analysis.
To further narrow down the analysis scope select object types (tables, views, procedures, etc.) can be excluded from the dependency graph.
When starting the dependency viewer from a database connection it is initially empty. You can select an object to visualize via the "Select Object" dropdown. This dropdown can also be used to switch to a different object if one was already previously selected.
The "refresh" button next to the object selection dropdown allows for reloading the dependencies, for example, if something has changed in the database since opening the dependency viewer.
The metadata information of procedures, functions, and views can be displayed graphically. The information shown in the graphical overview depends on the object type and shows the object schema, the object name, and additional information like columns or parameters where applicable.
The procedure and function metadata dialogs can be opened by clicking on a procedure or function in the catalog browser.
The procedure metadata shown includes the parameters, their types and directions, as well as buttons to generate a CALL statement or to analyze the SQLScript code. It it also possible to show the procedure's definition via the "CREATE Statement" tab.
The view metadata dialogs can be opened by clicking on a view in the catalog browser.
The SQL console can display a statement help panel that shows contextual information about the current statement, such as statement syntax (with links to the SAP HANA Cloud documentation) and accessed object information (columns for accessed tables and views, parameters for procedures and functions).
HDI containers can be added as dedicated database connections. The "Add Database" dialog offers the option "HDI Container" in addition to the existing database connection types. HDI container connections specify two users, a runtime user (typically the ..._RT user from a service key's "user" property) and a HDI design-time user (typically the ..._DT user from a service key's "hdi_user" property).
Local HDI container connections (just like the SAP HANA Database Explorer HDI container connections) offer two ways of opening a SQL console:
To ease the consumption of SAP Business Technology Platform service instances the HDI container credentials can also be added by checking "Input data as JSON" and copying and pasting a service key of a hana|hdi-shared service key.
The service key JSON has the following format:
Local database connections support authentication via Kerberos. If the database and the host system have been configured appropriately you can select the option "Authentication by current operating system user (Kerberos)" instead of entering user credentials.
When executing multiple statements in a SQL console you can choose how to proceed in case of an error. For every failed statement the SQL console allows you to choose whether to skip the failed statement, skip all failed statements, or abort the execution at the current point.
In addition to the Visual Studio Code tree filtering it is now possible to filter the list of databases via a dedicated filter functionality.
The advantage of the database filter is that is only filters on the database name while the Visual Studio Code tree filtering filters on all nodes in the tree.
Similar to the database list filter it is possible to filter the list of objects in the catalog browser via a dedicated filter functionality in addition to the Visual Studio Code tree filtering.
The database object filtering is done on the server side which is useful when there are many objects in the database.
The size of large object (LOB) column data in a result set can be configured via the setting "SAP HANA Database Explorer.LargeObjectSizeLimit".
Note that the size limit has to be specified in kilobytes, not bytes.
The LOB size limit as well as the result size limit can also be configured directly from a SQL console.
Stay tuned for more exciting new features in future releases!
New Features
Dependency Viewer
Database object dependencies can be visualized using a graphical dependency viewer. The dependency viewer can be started either by right-clicking an object in the catalog browser and choosing "Open Dependency Viewer" from the context menu, or by right-clicking a database connection.
When starting the dependency viewer from an object in the catalog browser, the dependency viewer directly shows the dependencies of the selected object.
The dependencies can be explored in both directions, incoming and outgoing. Schemas are indicated as boxes surrounding the objects. Schemas can be collapsed by double-clicking them to focus on the relevant dependencies. Clicking on an object highlights it and its direct dependencies for easier analysis.
To further narrow down the analysis scope select object types (tables, views, procedures, etc.) can be excluded from the dependency graph.
When starting the dependency viewer from a database connection it is initially empty. You can select an object to visualize via the "Select Object" dropdown. This dropdown can also be used to switch to a different object if one was already previously selected.
The "refresh" button next to the object selection dropdown allows for reloading the dependencies, for example, if something has changed in the database since opening the dependency viewer.
Database Object Metadata Visualization
The metadata information of procedures, functions, and views can be displayed graphically. The information shown in the graphical overview depends on the object type and shows the object schema, the object name, and additional information like columns or parameters where applicable.
The procedure and function metadata dialogs can be opened by clicking on a procedure or function in the catalog browser.
The procedure metadata shown includes the parameters, their types and directions, as well as buttons to generate a CALL statement or to analyze the SQLScript code. It it also possible to show the procedure's definition via the "CREATE Statement" tab.
The view metadata dialogs can be opened by clicking on a view in the catalog browser.
Statement Help Panel
The SQL console can display a statement help panel that shows contextual information about the current statement, such as statement syntax (with links to the SAP HANA Cloud documentation) and accessed object information (columns for accessed tables and views, parameters for procedures and functions).
Local HDI Container Connections
HDI containers can be added as dedicated database connections. The "Add Database" dialog offers the option "HDI Container" in addition to the existing database connection types. HDI container connections specify two users, a runtime user (typically the ..._RT user from a service key's "user" property) and a HDI design-time user (typically the ..._DT user from a service key's "hdi_user" property).
Local HDI container connections (just like the SAP HANA Database Explorer HDI container connections) offer two ways of opening a SQL console:
- A regular SQL console with the credentials of the "user". This SQL console connection can be used to interact with the deployed objects in the HDI container schema.
- An admin SQL console with the credentials of the "hdi_user". This SQL console can be used to interact with the HDI container API, for example, to get information about the deployed files or to trigger an HDI container export. It can also be used to access the HDI container monitoring views.
To ease the consumption of SAP Business Technology Platform service instances the HDI container credentials can also be added by checking "Input data as JSON" and copying and pasting a service key of a hana|hdi-shared service key.
The service key JSON has the following format:
{
"database_id": "12345678-9012-3456-7890-123456789012",
"host": "12345678-9012-3456-7890-123456789012.hana.prod-eu10.hanacloud.ondemand.com",
"port": "443",
"driver": "com.sap.db.jdbc.Driver",
"url": "jdbc:sap://12345678-9012-3456-7890-123456789012.hana.prod-eu10.hanacloud.ondemand.com:443?encrypt=true&validateCertificate=true¤tschema=MY_SCHEMA",
"schema": "MY_SCHEMA",
"hdi_user": "MY_SCHEMA_D17130MT4VNHRV7ABUILJU6DS_DT",
"hdi_password": "secret",
"user": "MY_SCHEMA_D17130MT4VNHRV7ABUILJU6DS_RT",
"password": "secret",
"certificate": "-----BEGIN CERTIFICATE-----\nMIIDrzCCApegAwIBAgIQCDvgVpBCRrGhdWrJWZHHSjANBgkqhkiG9w0BAQUFADBh\nMQswCQYDVQQGEwJVUzEVMBMGA1UEChMMRGlnaUNlcnQgSW5jMRkwFwYDVQQLExB3\nd3cuZGlnaWNlcnQuY29tMSAwHgYDVQQDExdEaWdpQ2VydCBHbG9iYWwgUm9vdCBD\nQTAeFw0wNjExMTAwMDAwMDBaFw0zMTExMTAwMDAwMDBaMGExCzAJBgNVBAYTAlVT\nMRUwEwYDVQQKEwxEaWdpQ2VydCBJbmMxGTAXBgNVBAsTEHd3dy5kaWdpY2VydC5j\nb20xIDAeBgNVBAMTF0RpZ2lDZXJ0IEdsb2JhbCBSb290IENBMIIBIjANBgkqhkiG\n9w0BAQEFAAOCAQ8AMIIBCgKCAQEA4jvhEXLeqKTTo1eqUKKPC3eQyaKl7hLOllsB\nCSDMAZOnTjC3U/dDxGkAV53ijSLdhwZAAIEJzs4bg7/fzTtxRuLWZscFs3YnFo97\nnh6Vfe63SKMI2tavegw5BmV/Sl0fvBf4q77uKNd0f3p4mVmFaG5cIzJLv07A6Fpt\n43C/dxC//AH2hdmoRBBYMql1GNXRor5H4idq9Joz+EkIYIvUX7Q6hL+hqkpMfT7P\nT19sdl6gSzeRntwi5m3OFBqOasv+zbMUZBfHWymeMr/y7vrTC0LUq7dBMtoM1O/4\ngdW7jVg/tRvoSSiicNoxBN33shbyTApOB6jtSj1etX+jkMOvJwIDAQABo2MwYTAO\nBgNVHQ8BAf8EBAMCAYYwDwYDVR0TAQH/BAUwAwEB/zAdBgNVHQ4EFgQUA95QNVbR\nTLtm8KPiGxvDl7I90VUwHwYDVR0jBBgwFoAUA95QNVbRTLtm8KPiGxvDl7I90VUw\nDQYJKoZIhvcNAQEFBQADggEBAMucN6pIExIK+t1EnE9SsPTfrgT1eXkIoyQY/Esr\nhMAtudXH/vTBH1jLuG2cenTnmCmrEbXjcKChzUyImZOMkXDiqw8cvpOp/2PV5Adg\n06O/nVsJ8dWO41P0jmP6P6fbtGbfYmbW0W5BjfIttep3Sp+dWOIrWcBAI+0tKIJF\nPnlUkiaY4IBIqDfv8NZ5YBberOgOzW6sRBc4L0na4UU+Krk2U886UAb3LujEV0ls\nYSEY1QSteDwsOoBrp+uvFRTp2InBuThs4pFsiv9kuXclVzDAGySj4dzp30d8tbQk\nCAUw7C29C79Fv1C5qfPrmAESrciIxpg0X40KPMbp1ZWVbd4=\n-----END CERTIFICATE-----"
}Kerberos Authentication
Local database connections support authentication via Kerberos. If the database and the host system have been configured appropriately you can select the option "Authentication by current operating system user (Kerberos)" instead of entering user credentials.
Multi-Statement Actions
When executing multiple statements in a SQL console you can choose how to proceed in case of an error. For every failed statement the SQL console allows you to choose whether to skip the failed statement, skip all failed statements, or abort the execution at the current point.
Database List Filtering
In addition to the Visual Studio Code tree filtering it is now possible to filter the list of databases via a dedicated filter functionality.
The advantage of the database filter is that is only filters on the database name while the Visual Studio Code tree filtering filters on all nodes in the tree.
Database Object Filter
Similar to the database list filter it is possible to filter the list of objects in the catalog browser via a dedicated filter functionality in addition to the Visual Studio Code tree filtering.
The database object filtering is done on the server side which is useful when there are many objects in the database.
LOB Size Limit
The size of large object (LOB) column data in a result set can be configured via the setting "SAP HANA Database Explorer.LargeObjectSizeLimit".
Note that the size limit has to be specified in kilobytes, not bytes.
The LOB size limit as well as the result size limit can also be configured directly from a SQL console.
Stay tuned for more exciting new features in future releases!
- SAP Managed Tags:
2 Comments
