SQL Plugin

Ömer Faruk ORUÇ

T.C. Hollingsworth

Introduction

The Structured Query Language (SQL) is a specialized language for updating, deleting, and requesting information from databases.

The Kate SQL Plugin allows you to:

  • Create a database

  • Connect to existing databases

  • Insert and delete data in the database

  • Execute queries

  • Display results in a table

Connecting to a Database

Select Add Connection from the SQL menu or toolbar, and then select the Qt database driver you want to use (including QSQLITE, QMYSQL3, QMYSQL, QODBC3, QODBC, QPSQL7, and QPSQL). If you can't see the desired driver, you need to install it. Then, press Next

If the database you selected uses a file, simply indicate the database's location and press the Next button. If it requires connecting to a server, you must enter the hostname of the server, your username and password, and any other information that particular driver may require. Then press Next

Finally, give a name to your connection, and press Finish.

Running Queries

INSERT/DELETE/UPDATE

You can insert, delete, and update data using the SQL plugin just like you would from the command line or from within a program. Simply enter a query and press the Run query.

Example 4.1. Some Example Queries

INSERT
INSERT INTO table_name ("feature1", "feature2", "feature3", "feature4", "feature5")
VALUES ("value1", "value2", "value3", "value4", "value5" )
DELETE
DELETE FROM table_name WHERE name = "text"
UPDATE
UPDATE table_name SET "feature1" = "text", "feature2" = "text", "feature3" = "text", "feature4" = "text", "feature5" = "text"

SELECT

After running a SELECT query, you can view the results as a table that will appear in the SQL Data Output tool view at the bottom of Kate, or as text in the SQL Text Output. If there is an error, you can see it in the text output.

Example 4.2. Example SELECT Query

SELECT * FROM table_name

In the SQL Data Output tool view, there are some buttons:

Resize columns to contents

Changes the size of columns to fit their contents.

Resize rows to contents

Changes the size of rows to fit their contents.

Copy

Selects all table contents and copies them to the clipboard buffer.

Export

Exports all table contents to a file, the clipboard, or the current document in the Comma Separated Values format.

Clear

Removes everything from table view

You can now change the colors displayed in the table in the SQL section of SettingsConfigure Kate.

Browsing

You can browse your database using the SQL Schema Browser tool view on the left. The information displayed varies depending on which database driver you are using.

To refresh this list, right-click anywhere in the tool view and select Refresh. To generate a query on any entry in the list, right-click on an entry, select Generate, and select the query type (SELECT, UPDATE, INSERT, and DELETE) from the submenu that appears.

Menu Structure

SQLAdd connection...

Adds a new connection using any database driver.

SQLRemove connection

Removes the selected connection.

SQLEdit connection...

Edits the current connection's settings.

Connections

All database connections you have created are listed between the Edit connection and Run query menu items. Select one to run queries or make modification to it.

SQLRun query (Ctrl+E)

Runs your query.

Thanks and Acknowledgments

The SQL Plugin was written by Marco Mentasti.

Special thanks to Google Code-In 2011 participant Ömer Faruk ORUÇ for writing much of this section.