{"id":180,"date":"2011-01-20T13:35:48","date_gmt":"2011-01-20T21:35:48","guid":{"rendered":"http:\/\/aklaver.org\/wordpress\/?p=180"},"modified":"2011-01-20T13:35:48","modified_gmt":"2011-01-20T21:35:48","slug":"sql-inside-jedit","status":"publish","type":"post","link":"https:\/\/aklaver.org\/wordpress\/2011\/01\/20\/sql-inside-jedit\/","title":{"rendered":"SQL inside Jedit"},"content":{"rendered":"

I use Jedit<\/a> as my primary text editing tool. I recently ran across a plugin (SQL<\/a>) to the editor that allows for running SQL from within an editor\u00a0 buffer. What follows is an explanation of how to set it up and run it.\u00a0 Some background, Jedit is a Java application so if you plan to use it you will need a JVM installed. I have found that to have it run well you need to use the Sun(Oracle) Java code.<\/p>\n

Set up:
\nJava runtime 1.6+
\nJedit 4.3+
\nInstalling the SQL plugin from the Plugin Manager will install dependencies\u00a0 most notable Project Viewer.
\nThe set up for SQL has global and per project parts.
\nThe global portion is set up through the Plugin Manager plugin options menu.\u00a0 By default SQL only returns 10 rows and that can be changed here. I have yet to find a way to get it to return with no limits. I would also suggest checking the Show Toolbar and\u00a0 Popup messages options. The most important global setting is the JDBC page, where you set up the path to the JDBC drivers you wish to use.\u00a0 SQL supports quite a few databases\ufeff\ufeff\ufeff \ufeff(Oracle, MySQL, PostgreSQL, DB2, Sybase, Microsoft SQL Server see help file), though notably not SQLite.<\/p>\n

Project Viewer (PV) is installed as a dependency because it is used to create per project settings for SQL. It is possible to use\u00a0 a PV project for just the SQL settings and not use the rest of the package, though I would suggest checking PV out. One note, PV seems to like to throw non fatal errors on a regular basis. They seem to be related to redraw operations and so far have not caused me any problems.\u00a0 Once you have created a project you can go to the project SQL setting to enter the necessary information.\u00a0 See screenshot:<\/p>\n

\"\"<\/a><\/p>\n

Once you have the server set up the fun starts. As an example I wrote a script to CREATE a table and another to populate it with data.\u00a0 If you look at the screenshot you will see the SQL toolbar I alluded to earlier, it starts with Database:. The second button to the right of the drop down box is the Execute buffer command. Clicking on that causes the entire sql script in the selected buffer to execute. The result is shown in the message box, in this case for the CREATE TABLE statement. I did this again on the buffer holding the INSERT commands .<\/p>\n

\"\"<\/a><\/p>\n

So now we have a table with data, now what? See below. In the right buffer I have written a SELECT statement against the previously created table. In this case I could have done Execute buffer but for illustration used another method Execute selection. This is the first button to the right of the drop down list. It only runs whatever text is selected.<\/p>\n

\"\"<\/a><\/p>\n

So what about variable substitution? In the first screenshot below I have used the drop down list Preprocessors to select variable substitution. In the left buffer is another SELECT query this time using the substitution variable ‘?’. The second screenshot shows the dialog box that pops up for data entry. For more than one variable the plugin will cycle through them and present a dialog for each.<\/p>\n

\"\"<\/a>\"\"<\/a><\/p>\n

One more interesting feature, SqlVFS . This allows one to browse the database through the Jedit File Browser. When you open the File Browser select the Plugins dropdown and then Show databases. Double clicking on the database allows you start drilling down through the database structure.<\/p>\n

\"\"<\/a>\"\"<\/a>In the short time I have been using this tool I have found it quite helpful, so I thought would pass along my experience.\u00a0 Saves a round trip between the text editor and a terminal window.<\/p>\n","protected":false},"excerpt":{"rendered":"

I use Jedit as my primary text editing tool. I recently ran across a plugin (SQL) to the editor that allows for running SQL from within an editor\u00a0 buffer. What follows is an explanation of how to set it up … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"ngg_post_thumbnail":0,"footnotes":""},"categories":[5],"tags":[],"class_list":["post-180","post","type-post","status-publish","format-standard","hentry","category-postgres"],"_links":{"self":[{"href":"https:\/\/aklaver.org\/wordpress\/wp-json\/wp\/v2\/posts\/180","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/aklaver.org\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/aklaver.org\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/aklaver.org\/wordpress\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/aklaver.org\/wordpress\/wp-json\/wp\/v2\/comments?post=180"}],"version-history":[{"count":12,"href":"https:\/\/aklaver.org\/wordpress\/wp-json\/wp\/v2\/posts\/180\/revisions"}],"predecessor-version":[{"id":193,"href":"https:\/\/aklaver.org\/wordpress\/wp-json\/wp\/v2\/posts\/180\/revisions\/193"}],"wp:attachment":[{"href":"https:\/\/aklaver.org\/wordpress\/wp-json\/wp\/v2\/media?parent=180"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/aklaver.org\/wordpress\/wp-json\/wp\/v2\/categories?post=180"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/aklaver.org\/wordpress\/wp-json\/wp\/v2\/tags?post=180"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}