This article is a continuation of Install HANA Express 2.0 On Ubuntu 16 Xenial. Please read the previous article if you don't have a HANA Express system installed.
hdbsql is a command line tool that allows you to connect to a HANA database and execute queries. To use this tool, you first need to login as the hxeadm user. You can do this by simply changing users in a terminal.
The hxeadm password is the password which you set when installing HANA. Once logged in, you can execute the hdbsql command tool.
The interactive terminal will load and a prompt will appear. Type the following connection arguments.
Replace the ip address, with the host you entered when installing HANA. Also replace 90 in the port number with the instance number you entered when installing HANA. The default instance number as of current is 90.
If the connection details are correct, the terminal prompt will change to hdbsql SYSTEMDB=> with a "Connected..." message. You are now ready to execute SQL queries. Execute the following SQL query to get a list of all schema's.
The result of the query above will show all system schemas. A schema defines the container that holds database objects such as tables, views, and stored procedures. Before you can create your own schema, you should first create a new user, this user will then own the schema. Listing 2 below shows how to create a simple user who has no privileges.
Now create a new schema and assign it to user JOHN.
Now reconnect to HANA using the new user.
You should get a message prompting you to change the password, go ahead and change the password to something memorable making sure that it is at least 8 characters long. Once you are logged in, execute the following SQL query.
You should now only see schemas that are owned by user JOHN. Notice that there is also a JOHN schema. This is because every user has its own schema.
hdbsql is a command line tool that allows you to connect to a HANA database and execute queries. To use this tool, you first need to login as the hxeadm user. You can do this by simply changing users in a terminal.
user@workstation# su hxeadm
The hxeadm password is the password which you set when installing HANA. Once logged in, you can execute the hdbsql command tool.
hxeadm@workstation:/usr/sap/HXE/HDB90> hdbsql
The interactive terminal will load and a prompt will appear. Type the following connection arguments.
hxeadm@workstation:/usr/sap/HXE/HDB90> \c -n 127.0.0.1:39013 -u SYSTEM -p PASSWORD
Replace the ip address, with the host you entered when installing HANA. Also replace 90 in the port number with the instance number you entered when installing HANA. The default instance number as of current is 90.
If the connection details are correct, the terminal prompt will change to hdbsql SYSTEMDB=> with a "Connected..." message. You are now ready to execute SQL queries. Execute the following SQL query to get a list of all schema's.
Listing 1
SELECT * FROM SCHEMAS
The result of the query above will show all system schemas. A schema defines the container that holds database objects such as tables, views, and stored procedures. Before you can create your own schema, you should first create a new user, this user will then own the schema. Listing 2 below shows how to create a simple user who has no privileges.
Listing 2
CREATE USER JOHN PASSWORD Password123
Now create a new schema and assign it to user JOHN.
Listing 3
CREATE SCHEMA MY_COMPANY OWNED BY JOHN
Now reconnect to HANA using the new user.
hxeadm@workstation:/usr/sap/HXE/HDB90> \c -n 127.0.0.1:39013 -u JOHN -p Password123
You should get a message prompting you to change the password, go ahead and change the password to something memorable making sure that it is at least 8 characters long. Once you are logged in, execute the following SQL query.
Listing 4
SELECT * FROM SCHEMAS
You should now only see schemas that are owned by user JOHN. Notice that there is also a JOHN schema. This is because every user has its own schema.
HANA DB Articles
-
JSON_TABLE Function
In this article, we'll take a look at how to use the ++JSON_TABLE++ function to represent JSON data stored in a regular column as a relational table.
31 March 2023 - 2718 views -
Connect To HANA DB Using Rust
Learn how to connect and query a HANA database using Rust with the hdbconnect package.
21 February 2023 - 2780 views -
If Table Exists Function
In this article, I explain how to create a function to determine if a table exists in SAP HANA.
31 October 2019 - 8857 views -
Connect To HANA DB Using NodeJs
This article explains how to connect to a SAP HANA system using the Node HANA client library.
28 October 2019 - 7138 views -
Connect To HANA DB Using Python
This article explains how to connect to a SAP HANA system using the Python hdbcli package.
11 October 2018 - 8001 views -
Adding Auto Numbers To Table Columns
This article explains how to create an auto generated column using IDENTITY.
14 March 2018 - 6284 views -
Connecting To SAP HANA Using PHP ODBC
This article describes how to install and configure PHP odbc to connect to a SAP HANA system on Ubuntu 16.
01 December 2017 - 7661 views