SAP HANA Database Explorer VSCode Extension Getting Started Tips

[thomas_jung]

You might have seen that SAP just recently released the first version of SAP HANA Database Explorer as a Visual Studio Code Extension.  You can get that extension here: SAP HANA Database Explorer - Visual Studio Marketplace

SAP HANA DB Explorer in VSCode Marketplace

For those of us that do a lot of local development in VSCode, even this early version with limited functionality is a welcome addition. In this blog post I wanted to share with you a few tricks and tips I picked up from first using the tool today.

Check the CF URL Endpoint

If you have the Cloud Foundry CLI installed, then the extension will use it to automatically determine the Database Explorer endpoint and fill the SAP HANA Database Explorer Connections section of the tool for you. But when I first started the extension I got a Not Authorized error.  It happens that I use multiple SAP BTP regions (EU10 and US10) and the extension was trying to connect to EU10 when my HANA Cloud instances and Database Explorer were all running in US10.

You can update this setting manually from the VSCode Preferences however. Use File->Preferences->Settings.

Then open the Extensions folder and find the SAP HANA Database Explorer section.  There is a entry there where you can manually set or override the SAP HANA Database Explorer:

Settings for SAP HANA Database Explorer

As soon as I updated the region in the URL to the correct one for my setup, the authentication errors disappeared and all the connections that I had configured previously in the SAP HANA Database Explorer web interface were immediately added to my extension.

Database Connections

Manual Connections

This extension also lets you directly configure connections locally. And I thought great, I have lots of connections that I have configured locally for development using default-env.json files. I can just take the configuration details from a default-env.json or use "cf env" to get service binding details.  That should provide everything that I need to setup a manual connection in this tool.

However one small issue I found was a difference in the formatting expected for the certificate of the connection. In the cf environment and in the default-env.json the certificate has a -----BEGIN CERTIFICATE----- and -----END CERTIFICATE----- section. However when I included the complete certificate in the VSCode Extension it reported an error that it was invalid. As soon as I removed the BEGIN and END section of the certificate, it validated and worked perfectly fine.

Certificate Formatting

With those setup learnings out of the way, I was off and using the extension with both direct Schema connections and HDI container connections. It might mostly be the SQL Console for now, but even this is a nice addition to the workflow of local development in VSCode.

SQL Console

SQL Console with HDI Container

 

Update

As of February 4th 2022, we updated the hana-cli tool to help out here.  We added the dbx output option to the systemInfo command which will read an existing connection and format and present the connect ready to use in the DBX VSCode Extension.  This includes the stripping of the begin and end certificate.

hana-cli systemInfo new dbx output option

[VolkerSaggau]

Thanks Thomas, very helpful

[martinstenzig]

I am running it on a Mac and get the message 'command 'databaseExplorer.refresh' not found' when I click the refresh button. Any idea? And/or what's the best way to submit issues?

[thomas_jung]

You might try asking the question directly in the Marketplace: https://marketplace.visualstudio.com/items?itemName=SAPSE.hana-database-explorer&ssr=false#qna

Just for some troubleshooting, do you see the database refresh via the Command Pallet?

 

Or the Keyboard Shortcuts:

Refresh is working for me, but it also shows up in the pallet and in keyboard shortcuts.

[martinstenzig]

Tried that too and get

Command 'Refresh Database List' resulted in an error (command 'databaseExplorer.refresh' not found)

[martinstenzig]

I submitted it through the marketplace. Thx Thomas!

[fcamps]

I tried this out yesterday and it looks good but also very limited. Unfortunately there is no export option like the HANA Database Explorer online. Furthermore I can't seem to browse the Catalog at all as this is completely empty. I can see the databases and use the .sql window, but that seems like it's all.

[lucian2]

I'm looking forward to see this extension developing, the existing SQL tools extension feels very bloated and has an awful UX/UI, so I'm very excited to see something else. At the moment the only thing that works for us is DBeaver, but it would be really nice to have something directly in vscode.

A few issues:

• Catalog Browser doesn't seem to be working at all.


• I don't want to type my password every time.


• On a Linux ARM64 laptop, local connections are not working at all (probably a limitation of the HANA driver).


• Cloud connections are a bit confusing to set up.


• This is the only extension that I have that doesn't follow the naming convention in settings

[Dan_vL]

For bullet point 3 above, noting that support for ARM64 on Linux is something that is being considered for the SAP HANA Client.

The current list of supported platforms can be found at SAP Note 3136015 - SAP HANA Client Supported Platforms for 2.11.

 

[cgwaters]

Is the VS Code extension compatible with Microsoft Windows? The Prerequisites section states:

"The Cloud Foundry CLI is required for accessing the list of databases from SAP HANA Database Explorer."

The link in that sentence leads to https://docs.cloudfoundry.org/cf-cli/install-go-cli.html ("Installing the cf CLI") which lists installation steps for Linux and Mac OS X, but not Windows.

[thomas_jung]

I use it on Windows. I also run the cf CLI on Windows as well. That section of the help you are referring to is the package manager section. You can refer to the Compressed Binary section for Windows instructions: Installing the cf CLI | Cloud Foundry Docs

But if you want to install it via a package manager on Windows, there is Chocolatey.
Chocolatey Software | Cloud Foundry CLI 8.2.0

[sensen_wei2]

Hi

Very nice tool! But I had errors when try it out on my pc:

Could not load database from SAP HANA

Any idea why?

Best regards

Sensen

[former_member748]

Hello thomas.jung

Thanks so much for the blog post, looking forward to using it and having more extensions available in VSCode for HANA development!

Is there also an extension for calculation view editor or will it be available?

Best regards,

Stefania

[thomas_jung]

No Calculation View extension yet.  And I'm afraid I don't have any visibility to the roadmap plans there either.  Sorry.

[former_member748]

thomas.jung no worries, thanks for the response! Would be cool to have this type of extension on local available 😎

[frank1980]

Hello thomas.jung

i have some trouble with working on Hana on-premise. I have set up my local connection.

i also set up SAP HANA User Store.

If i open SQL Console from Database List, it is fine to me

But when  i want to change the connection,  i getting the database windows with fetching database  and a error message "Please log into Cloud Foundry and try again"

How can i fix it? maybe you have already explained it, but i dont get it.

in this way, Hana Hana Database Explorer as default Browser is not usable for me.

I open SQL File and can´t select a database.

And Please can we get autocomplete for the Editor.

Best regards

Frank

[thomas_jung]

I don't work on the product team, so I can't really address these issues. I'd suggest opening a support ticket.

[KarthikCheers]

Hello thomas.jung

Thanks for this blog post. I am using VS Code for CAP Project development and installed Hana Database Explorer extension and using it for some time.

Is there a way to download the data  from the Query RESULTS?. I do not see the RESULT menus(action to be performed on results) in extension, that we can see in Database Explorer..

Thought of checking here..any other ways to download the results as part of extension in VS Code?

thank you
Karthik

[Dan_vL]

We are looking into adding the ability to download the SQL results from the SQL Console and to have the ability to specify the format (CSV or fixed width, include the column names as a header etc.).  New features are often listed at change log.
Here are a few other options until it is available:

• The SAP HANA database explorer has a download toolbar item in the results set tab.  This can be seen in Step 1, sub step 1 of the tutorial Export and Import Data and Schema with SAP HANA Database Explorer


• The SAP HANA database explorer provides export data wizards and the SAP HANA database provides an export into statement.


• Use a tool such as HDBSQL with the -o statement.  The following tutorial mission provides more details on the SAP HANA Client which includes HDBSQL.   Use Clients to Query an SAP HANA Database

Regards,
Dan van Leeuwen

[KarthikCheers]

thank you very much. appreciate the response with details.