How to delete a table?

[Former Member]

I would like to completely remove a table from database, there are 0 entries in it. How can I do this?

[arvind-kumar_avinash]

At the time of initialization, all the tables are deleted and then tables are created as per the hybris type system compiled from the applicable items.xml files. If you have already removed the item from the items.xml, it will be deleted during initialization. However, in most cases, initialization is not an option. Please note that update cannot remove any table. So, you need to delete the table directly from the database.

You need a database client (e.g. database specific clients like Oracle SQL Developer, MySQL Workbench or a generic client like DBVisualizer, SQuirreL SQL etc.) to delete a table from the database. Please note that HSQLDB comes with a prepackaged client which you can access as follows:

A. Make sure hybris server is stopped.

B. Go to hybris/bin/platform/lib/dbdriver and check the version of HSQLDB driver. Assuming that it is hsqldb-2.3.4.jar, you need to execute the following command from the Command prompt (Windows) / Terminal (Mac):

 java -cp hsqldb-2.3.4.jar org.hsqldb.util.DatabaseManagerSwing

It will open a Java Swing application.

Note: you need to be in the hybris/bin/platform/lib/dbdriver before executing the above mentioned command from the Command prompt (Windows) / Terminal (Mac) e.g. $ cd /Users/arvind.avinash/HYB180800P/hybris/bin/platform/lib/dbdriver. Please change the path as per your directory structure.

C. Enter jdbc:hsqldb:file:/Users/arvind.avinash/HYB180800P/hybris/data/hsqldb/mydb in the URL field and press OK. It should open a window as given in the screenshot. Here, you can navigate to all tables as well as execute SQL commands.

Note: please note that you need to change / to \ in windows.

[former_member656022]

Hi Arvind,

I've followed the above said steps in my local, using hsqldb. I've dropped the table using DROP command. But after server startup, when I search with the itemtype through flexible search, it is still showing the table name in the converted sql query. Could you please assist that am I missing anything here.

Thanks,

Hari

[Former Member]

Thank you guys for the quick solution 🙂

[arvind-kumar_avinash]

You are most welcome.

[Former Member]

you can put your SQL commands into an .sql file and run it with: ant executesql -Dsql.file=/path/to/your.sql

Don't do this unless you're 100% sure that you want to run that specific query, there will be no prompt and there's no way to undo this apart from restoring a database backup.
Thus I suggest you double-check the SQL queries, which environment/system you're executing it on and make sure that you test this on a non-critical environment (for example your local machine) before executing it on any system which is business critical.

[Marko_salonen]

DROP TABLE table_name; should work? Have you removed the item from items.xml?

[Former Member]

Hello Marko,

when i try this from the HAC via the Tab "SQL Query" i get the following error message:

Provided query: DROP TABLE is not valid DQL or DML language

I am using default HSQLDB

Do you have any hint for me?