How to change the "Ad Hoc SQL&quo...

  • Nobody/Anonymous


    Ad Hoc SQL is a really nice tool, but is ist possible to choose that not all querys are started at the same time?

    I'd like to have multiple sql-statements in the editor and hightlight the ones I need to be run, like you can do with many other query tools…

    Unfortunately I don't know much about JavaScript, but should be possible to change some code if this option doesn't exist already…? If it is, whta's the name of the …js that needs to bechanged?

    Thanks a lot!

  • Matthew Vandenbussche


    I am not aware of any ad hoc tools that let you select statements in an ad hoc setting and let you pick which ones to run? Could you let me know the name of these tools?

    You can comment out statement using:
    -  "-" or "//" will comment out all text appearing after till a new line
    - "/*" to "*/" to comment out a block of text

    If you don't know much about JavaScript then I would advise against trying this as your first venture into programing JavaScript and working in the TE framework.

    That said, if you still want to attempted this you will be looking into the client action file ./js/clientActions/adhoc.js.

    First start by reviewing:

    and install Firefox and the firebug plug-in, learn how to use firebug it is your best friend as a JavaScript programmer.

    Next I would recommend is to create a new adhoc action to work in so that you do not break the current TE. Rename the action to something like "adhoc2.js" and open the file up. You should comment out the class AD_HOC_BASE_CONTROLER if it is present in your version.

    Change the line:

    "CORE_CLIENT_ACTIONS.set("ADHOC", Class.create(basePageElement,"
    "CORE_CLIENT_ACTIONS.set("ADHOC2", Class.create(basePageElement,"

    next you will need to add your JS file into a jsList_*.xml file so that it will be picked up and loaded by the TE (use jsList_PredefinedClientActions.xml for now though you can create your own). 

    It maybe a good idea to review the tutorial on building a client action:
    and the jsList XML definition:

    Lastly open up the Adhoc menu item './menu/tools/menu_15_Adhoc.xml' and re-save it as 'menu_20_Adhoc.xml'. Change  'ADHOC' to  'ADHOC2' in the parameter who's name is 'action'. Lastly change the description tag to something like 'Ad Hoc SQL - Development' (note menu names are sorted alphabetically, menu files must start with 'menu_' and end with '.xml'.)

    Reload the TE, you should see the new menu under tools, and should be able to load your new action.

    The initial layout for the Ad Hoc is set up in the draw function on line 145 (positions may be slightly different depending on the TE version you have). Not that there are two layouts for the Ad hoc and if you change the behavior of on then you need to also change the other.

    The process:

    When you hit the "Run" button the function play is called located on line 676

    In the case where you are going to run statement against the database the function executeQuery is called on line 692.

    Next the ad hoc text is retrieved from the text box on 702 (this.elementUniqueID + '_SQL_TEXT_FRAME') and the SQL option are retrieved on the line below.

    next on line 708 we call the function analyzeQuery located on line 529. This query is used to parse the block of SQL into individual SQL statements.

    Lastly the SQL and SQL option are packaged into a request and sent to the executeSQL server action. The executeSQL server action is use to run almost all SQL within the TE, you should have no need to touch this.

    So as I see it, you have Three options. Change the way individual statements are inputed into the Ad hoc or the behavior of the input field so that statements can be individual identified. Or, after  analyzeQuery has parsed the statements prompt the user to select the statements they wish to run (I would make this optional). Or, change the statement parser so that if it sees an identifier at the beginning of a statement it does not return that SQL statement.

    The first, is the hardest and requires the most changes but will be the best option for what you want to do. The second is easier but it is intrusive to the user and will get annoying. The last is simple and easy for the user but dangerous because you are adding a parsing rule and it could have unintended concisenesses if you are not careful.

    Best of luck,


    If you wish to contribute this back or wish for us to look at your code and help you out. You will be required to sign a CLA (Contributers License Agreement) first. You can contact me directly if you have any questions about the CLA (it is not that scary, it just states that anything you write is your own code or you have the rights to license it, and that the IP you contribute is free to be reused.)

  • Nobody/Anonymous

    IBM's data studio does.  From memory a few other tools provide this.   I think this may be easy to provide.  I will have a quick look.  I keep meaning to add this, but never have been that motivated.  Used the old "-" technique.

  • Paul Johnson

    Paul Johnson - 2011-06-28

    Hello, I just wanted to comment that the highlighting feature has been added by Peter Prib and is in both TEr4-dev and TEr4.  Thank you Peter!


Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:

No, thanks