Thursday, November 16, 2006

Getting started with an Extension


Sue had blogged on how to get an extension started in jdev in 10 steps. In the presentation at OOW, I showed how little code it could take to start an extension and said we'd get a skeleton out of how to start one yourself. Well here's the Skeleton and here's a zip of it. With this skeleton, there's 3 steps to getting started with an extension.


Step 1: Download the zip.

Step 2: Got to [unziped location]/etc and update the contents of the build.properties. Here's the content of the file and the only line that has to be updated is the sdev.home which points to your install of SQL Developer 1.1


build.dir=built

sdev.home=/Users/klrice/Documents/workspace/raptor_trunk/ide

extension.id=sample.skeleton

extension.name=Skeleton Sample

extension.author=Kris Rice

extension.author.url=http://krisrice.blogspot.com

extension.descr=A Skeleton of an extension

update.url=http://esdev.sf.net


Step 3: Run ant deploy


Well it's done and deployed to your local copy of sqldeveloper. Wasn't that easy and painless? :-) Take a look at the Help->About and the Extensions tab will show the name from Step2.




This added 2 things. First an ls command when a script is run ( F5 ). Second a Reconnect on the connection. Here's a screen capture of both.




In case you didn't look in the source of the extension, it adds 2 things. First just like in the OOW presentation I mentioned there's a new hook in the scripting engine which allows custom command to be added. In this case we add ls as a command here. This extends the abstract class CommandListener which has a few methods:


public abstract class CommandListener {

public abstract boolean handleEvent(Connection conn, ScriptRunnerContext ctx, SQLCommand cmd);

public abstract void beginEvent(Connection conn, ScriptRunnerContext ctx, SQLCommand cmd);

public abstract void endEvent(Connection conn, ScriptRunnerContext ctx, SQLCommand cmd);

public void beginScript(Connection conn, ScriptRunnerContext ctx, SQLCommand[] cmd){
}

public void endScript(Connection conn, ScriptRunnerContext ctx, SQLCommand[] cmd){
}
}


}



By implementing and registering a command listener, an extension writer has the ability to add new command and listen in on when commands are run as scripts. Hopefully the names on the methods are pretty simple. The beginScript is before anything is run and endScript is after everything. The other 3 are before each command, after each command and the command itself.


So in this case I only implemented the handleEvent so I could process the ls then finally return a true to say I did something.


public boolean handleEvent(Connection conn, ScriptRunnerContext ctx, SQLCommand cmd) {
44 ResultSet rs = null;
45 if (cmd.getSql().equals("ls")) {
....
68 return true;
69 }
70 return false;
71 }


The second extension for the Reconnect has been explained here and here. What's new in this example is that the xml references a java class: className="sample.ReconnectAction" . This class extends another abstract here :


public abstract class AbstractMenuAction {
public abstract void launch();
public abstract void setArgs(String args);
}


In this launch, all we did was get a handle to the connection which will cause the framework to reconnect if the connection was dropped and put a status out to the toolbar.

  // get the connection 
// this will force a reconnect
getDBObject().getDatabase().getConnection();
// status basr entry
Ide.getStatusBar().setText("Reconnected.");


Now that everyone knows how to get started with extension, we should see lots of cool stuff. When you do create an extension drop Sue or myself a note and we'll add it to the list of known extensions.

27 comments:

Anonymous said...

Kris, I tried deploying the Skeleton and got the following error:

C:\temp\skeleton>C:\jdev1013\ant\bin\ant deploy
Buildfile: build.xml

init:

compile:
[delete] Deleting directory C:\temp\skeleton\built
[mkdir] Created dir: C:\temp\skeleton\built\classes
[mkdir] Created dir: C:\temp\skeleton\built\classes\META-INF
[javac] Compiling 3 source files to C:\temp\skeleton\built\classes

BUILD FAILED
C:\temp\skeleton\build.xml:43: C:\sqldev11sqldeveloper\ide\lib not found.

My sdev.home=C:\sqldev11\sqldeveloper

The error message indicates that deploy process omits the "\" between sqldev11 and sqldeveloper.

Unknown said...

My guess would be that's on OSX which is unix based and the build file has all / and ant is getting confused.

Try replacing the / with \ or get a mac ;)


-kris

Unknown said...

Excellent post! It has inspired me to write an extension. Is there a similar way to get the functionality of beginScript and endScript for the Execute command (F5)? If not what would be the best option of inserting SQL before and after an execute.

Unknown said...

I just looked at the sources and there's currently no way to hook into the Execute F9. I'll see if we can get it added for the next version.

-kris

Anonymous said...

Hi Kris,

I'd love to get going with this, but don't know how to set it up under JDeveloper. It's unlike the extension examples Sue talked about, and the source doesn't even recognize oracle.dbtools.
Any hints?

Thanks,
K.

Unknown said...

Take a look here : http://esdev.svn.sourceforge.net/viewvc/esdev/trunk/org.esdev/skeleton/

I just made a jdev application ( the .jws ) and a jdev project ( the .jpr )

Those should get you going. The project is looking at ../sqldeveloper as the location of sqldev to add all the libraries to the classpath.

-kris

Anonymous said...

Thanks a lot Kris, the .jws and .jpr did the trick, and got the new extension up & running...

K.

Anonymous said...

Any chance of this working on SQL Developer for Windows?

Steps on how to do that (for a noob)?

Thanks!

Unknown said...

This should work on any platform. What issues are you seeing?

-kris

Anonymous said...

Thanks Kris Rice.
I was able to create a new custom menu item "Create tables" on the drop down on an oracle connection (like Reconnect).

Now I have a requirement where in, I need to be able to generate the create table script for all the tables in the Oracle connection. (only create tables). The script I need is much like a subset of Generate the "Converted Models".
The new menu item ""Create tables" created above, needs to be plugged in with the Java class that generates this create table scripts.

Any help on this would be greatly appreciated.

Thanks in advance.

Anonymous said...

Thanks Kris Rice.
Your example was very helpful. I had a similar requirement where I need to extend OWBM to enable The following
1.Create a new menu item (say "Create Tables")

2. When the user clicks on "Create Tables", the right hand screen should populate the all the create table scripts for tables in that Oracle connection. ( Much like the subset of what Generate would produce on the "Converted Models".)
So with your example I have the item1 done "Create tables Menu item. But I need help or pointers on the Java class that needs to be plugged in.
For instance,
item type="CONNECTION"
className="sample.Createtables"
title= Create Table title
item
I need your help some tips on the developing the createtables java.

Thanks again

Unknown said...

I'm not sure I follow your question. If you looks at the reconnect, it's in java and shows how to get the actual sql connection. Then from that you can do anything.

-kris

Anonymous said...

Thanks for your response. Sorry about not making myself clear. Let me explain the latest status of my problem. I am trying to execute a query directly from a new dropdown menu click item (for eg Reconnect) without interacting with the user interface text entry and execute with.

Using your Skelton example as reference, Is there a way to capture a reference to the ScriptRunnerContext within the ReconnectAction.java ? So that I can then pass its reference as an argument to the handleEvent method on an instance of LS.Class.

//*** Code below*****

public class ReconnectAction extends AbstractMenuAction {

@Override
public void launch() {

JOptionPane.showMessageDialog(null, " ReconnectAction " + "!","" , JOptionPane.INFORMATION_MESSAGE);
ScriptRunnerContext ctx=new ScriptRunnerContext();
SQLCommand cmd=new SQLCommand("ls") ;
cmd.setSql("ls");
sample.LS l= new sample.LS();
l.handleEvent(getDBObject().getDatabase().getConnection(),ctx, cmd);

//******* end

I am trying to call the handleEvent method on an instance the LS.Class.The above code gives me a null pointer exception within LS.java for the ScriptRunnerContext at the line below.

// ************code

ctx.getOutputStream().write(rs.getString(1).getBytes("UTF8"));

// ************code

The stack trace is below in case you want to refer.
java.lang.NullPointerException
at sample.LS.handleEvent(LS.java:80)
at sample.ReconnectAction.launch(ReconnectAction.java:45)
at oracle.dbtools.raptor.controls.sqldialog.ObjectActionController.handleEvent(ObjectActionController.java:127)
at oracle.ide.controller.IdeAction.performAction(IdeAction.java:551)
at oracle.ide.controller.IdeAction$2.run(IdeAction.java:804)
at oracle.ide.controller.IdeAction.actionPerformedImpl(IdeAction.java:823)
at oracle.ide.controller.IdeAction.actionPerformed(IdeAction.java:521)
at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1849)
at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2169)
at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:420)
at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:258)
at javax.swing.AbstractButton.doClick(AbstractButton.java:302)
at javax.swing.plaf.basic.BasicMenuItemUI.doClick(BasicMenuItemUI.java:1000)
at javax.swing.plaf.basic.BasicMenuItemUI$Handler.mouseReleased(BasicMenuItemUI.java:1041)
at java.awt.Component.processMouseEvent(Component.java:5488)
at javax.swing.JComponent.processMouseEvent(JComponent.java:3126)
at java.awt.Component.processEvent(Component.java:5253)
at java.awt.Container.processEvent(Container.java:1966)
at java.awt.Component.dispatchEventImpl(Component.java:3955)
at java.awt.Container.dispatchEventImpl(Container.java:2024)
at java.awt.Component.dispatchEvent(Component.java:3803)
at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4212)
at java.awt.LightweightDispatcher.processMouseEvent(Container.java:3892)
at java.awt.LightweightDispatcher.dispatchEvent(Container.java:3822)
at java.awt.Container.dispatchEventImpl(Container.java:2010)
at java.awt.Window.dispatchEventImpl(Window.java:1774)
at java.awt.Component.dispatchEvent(Component.java:3803)
at java.awt.EventQueue.dispatchEvent(EventQueue.java:463)
at java.awt.EventDispatchThread.pumpOneEventForHierarchy(EventDispatchThread.java:242)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:163)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:157)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:149)
at java.awt.EventDispatchThread.run(EventDispatchThread.java:110)


Thanks in advance
Wajahath

Unknown said...

If your just trying to run a script, here's the easiest way to do it.

oracle.dbtools.raptor.scriptrunner.utils.ScriptUtils.runAsScript(inStream, connName);

and the connName can be gotten from :
dbObject.getConnectionName()

This will run the inputstream as a script and the output will goto the log pane.

Anonymous said...

Thanks Kris,
I get the following error when I try to use the oracle.dbtools.raptor.scriptrunner.utils.ScriptUtils.runAsScript method.

cannot find symbol
[javac] symbol : method runAsScript(java.io.FileInputStream,java.sql.Connection)
[javac] location: class oracle.dbtools.raptor.scriptrunner.utils.ScriptUtils
[javac] oracle.dbtools.raptor.scriptrunner.utils.ScriptUtils.runAsScript(in, getDBObject().getDatabase().getConnection());

Anyway, just to clarify a little more. What I am trying to do is, Using a non-standard sql custom command (similar to "ls" in your Skelton sample) would internally execute a few sp's and sql statements within the handleEvent method in LS.java and return the desired output.
I am able to test this manually by entering the command (say for instance "ls") and returns me the desired output.
I want the same without the manual step of keying the command and instead be driven with the click of a new dropdown menu item.

So, as per my previous post If only I could get a handle for ScriptRunnerContext object (ctx) within ReconnectAction might resolve the issue.

Applogise If I am making this too complicated.

Waj

Unknown said...

You have a signature mis-match. The ScriptUtil takes in
First) an inputstream of what's to run
Second) a string which is the name of the connection.

So the code would look something like this:

String sql = "select 1 from dual";
StringBufferInputStream in = new StringBufferInputStream(sql);

ScriptUtils.runAsScript(in, getDBObject().getConnectionName());

Anonymous said...

THanks a lot Kris. That did take care of the problem. Now one last but remains, Can I have the output to appear in the script output window instead of the log pane.

This way I would be 100% complete.

Thanks again
Waj

Unknown said...

There's no real easy way to do that from a menu. Are you adding a menu inside the worksheet or to the main toolbar ?

Anonymous said...

Right now I have the new menu on the Right click of a connection. Similar to the"Reconnect" in the Skeleton example.

I would like to have it in the main toolbar as well but did not know how to proceed. Will having it on the main toolbar help in displaying the results in the script output window instead of the log pane ?

Anonymous said...

Hi Kris,

I want to go via the ODBC data source name to be able to connect to a non-oracle database. How will I able to do that in SQL Developer ? And if incase this needs an SQL Developer extension using JDeveloper. Please help me with any pointers on how to proceed.

Thanks you very much for your very valuable assistance.

Wajahath

Anonymous said...

Hi Kris,

Step 3: Run ant deploy

I can't find a build.xml in the skeleton folder. Do i need to deploy it without one ?

thanks,
Jochen

Unknown said...

You'll find the build.xml in the etc directory.

sursan said...

Hello,

I have tried the skeleton in SQLDeveloper 1.2.1

For the "ls", it returns an error ORA-00900 Invalid SQL statement.

For the reconnect, it says in the status bar "Reconnected", but how can I be sure?

And a final question, and here I am referring to another of your blogs - New datatype support (http://krisrice.blogspot.com/2006/08/new-datatype-support-and-how-to-add.html)
Do you think it will be possible to use this to implement the much-waited feature of displaying raw columns for a table ("Data" tab, not a select statement)?

Thank you in advance!

Unknown said...

It's very odd if you get an ora-00900 invalid statement from this:
select object_name from user_objects

Have you tried running that sql directly in the worksheet or sqlplus?

The "Data" tab does no use the method described in the blog about adding datatype support. I should have mentioned that works only with the read-only grid of data.

Karthik said...

Hi Kris,
I am trying to write a java plugin which would help me generate the ddl statement , with comments and i am also planning to add some additional features like generating a change script if a few columns were added.

I prefer not to do this in XML as java is much more flexible when it comes to writing out to a file.

I am trying to build this sample code. But i keep getting build errors. Can you please help ?
(this is only a partial list of the errors)

compile:
[delete] Deleting directory C:\JDeveloper\mywork\org.esdev\skeleton\built
[mkdir] Created dir: C:\JDeveloper\mywork\org.esdev\skeleton\built\classes
[mkdir] Created dir: C:\JDeveloper\mywork\org.esdev\skeleton\built\classes\M
ETA-INF
[javac] Compiling 3 source files to C:\JDeveloper\mywork\org.esdev\skeleton\
built\classes
[javac] C:\JDeveloper\mywork\org.esdev\skeleton\src\sample\LS.java:6: packag
e oracle.dbtools.raptor.scriptrunner does not exist
[javac] import oracle.dbtools.raptor.scriptrunner.CommandListener;
[javac] ^
[javac] C:\JDeveloper\mywork\org.esdev\skeleton\src\sample\LS.java:7: packag
e oracle.dbtools.raptor.scriptrunner does not exist
[javac] import oracle.dbtools.raptor.scriptrunner.SQLCommand;
[javac] ^
[javac] C:\JDeveloper\mywork\org.esdev\skeleton\src\sample\LS.java:8: packag
e oracle.dbtools.raptor.scriptrunner does not exist
[javac] import oracle.dbtools.raptor.scriptrunner.ScriptRunnerContext;
[javac] ^
[javac] C:\JDeveloper\mywork\org.esdev\skeleton\src\sample\LS.java:9: cannot
find symbol

Karthik said...

Never mind Kris. I used the newscriptrunner and so far i havent had any issues. Will let you know if i run into any other problems.

Thanks,
Karthik

Unknown said...

I need to update the skeleton to the latest will probably do it when 3.0 comes out. Let me know if you hit any other issues.