ID #1077

Can I add my own strings to SQLyog auto-complete?

Yes, you can.

And that can be very useful if you have some standard strings used often for naming, commenting etc. Especially if those strings are long or have an awkward combination of characters/keystrokes.

You will need to add that string to the embedded database that the auto-complete feature uses. The embedded database is a SQLite database so you will need a SQLite client/Frontend. There are several options available:

1) Command-line client. The SQLite project maintains/distributes a command-line client available from here. You must choose the version for SQLite version 3. A guide to using it is here. And a complete SQLite syntax description is here.

2) Graphical frontends. Also (both free and commercial GUI-clients) are available.

3) Using PHP (or another server side scripting language). You can of course do that too, if you are running a webserver. Use must then enable the appropriate webserver extensions. With PHP note that there is an important difference between PHP5 and previous versions, as the SQLite code is compiled into the PHP5 binary. Refer to the PHP documentation at for further details. There is a good resource on PHP and SQLite here

4) ODBC etc. There is an ODBC driver available for SQLite. There also is a JDBC driver and a native StarOffice/OpenOffice driver. Both are in a early experimental ALPHA stage. None of those drivers are maintained by the SQLite project itself. And none of them (not the ODBC driver either) seem to work very well with the latest SQLite release (used by SQLyog). But that probably will be fixed one day, and you will be able to work with the SQLyog embedded database from a Office-suite database program (Microsoft Access, OpenOffice Base).

To insert your own keyword this_is_my_own for instance you must locate the keywords.db database file in the SQLyog installation directory, access it from your chosen client and insert the data ('this_is_my_own','1','0','32300') into the objects -table. Now the string 'this_is_my_own' will show up in the SQLyog keywords list with any MySQL version equal or higher than 3.23.x. The value '1' in 2nd column identifies it as a KEYWORD and 'autocomplete' displays it with the keyword-icon (a blue-colored 'k').


Note that SQLyog caches the embedded database in memory at startup, so if SQLyog is running when you are editing the database, SQLyog must be restarted for the change to take effect. Also note that if you update or reinstall SQLyog (Professional, Enterprise or Ultimate) the keywords.db file will be overwritten. So if you have more than a few keywords/strings of your own you should keep an external file (for instance an .sql reading like "INSERT INTO objects VALUES ...") with your strings for easy re-import. We will provide an option not to overwrite user entries in near future. We will also provide a 'user' category displaying its own icon. But also for the future (and especially not for the BETA period) we cannot guarantee that there will never be incompatible changes, so keeping your own data in a separate file for easy re-import will always be a good idea.

Also note that there is a restriction with user strings and BETA1: It is not posible to insert non-ASCII with other clients and read them correctly with this BETA. Also database objects with non-ASCII characters inserted by SQLyog will not show correctly up in other clients. This is because SQLite internal uses UTF-8 encoding, what is not supported by SQLyog 5.1. Actually it is 'wrong' what SQLyog does when inserting non-ASCII characters - but it does 'wrong the opposite way around' when reading data - so the end result is that it displays correctly - but only in SQLyog, not in other SQLite clients. This will most likely be fixed with 5.2 (where the code base is changed from ANSI to UNICODE) or soon thereafter.


Tags: -

Related entries:

You can comment this FAQ