[support-jeff]

Trying to get a feel for the typical scenario out there. Please post replies with others you may be using (including me-tos on those posted as replies)

[arjan.tijms]

Using it for my web application with postgreSQL where I have a lot of .sql files in the Web Root. (I load these .sql files from my application and send these to the DB). Its nice to be able to execute these directly from my IDE now. Before I had to manually copy and paste them pgAdmin and test run them from there, make changes etc.

(I’m actually also using my own SQL format, so I have extended the open source version of the plug-in to provide support for entering parameters. This has proven to be of even greater usage to me.

Pasted from a previous post, this entailed the following changes:

Basically, this entails the following changes in SQLEditor.java:

1) setting a custom document provider in init with a call to setDocumentProvider().
2) Adding a small amount of code to getSQLToBeExecuted()
3) Adding some widgets to the editor in createSourceViewer
4) Extending SQLTextViewer to provide support for delagating cut/copy/paste events to our own widgets and setting the editor’s dirty state.

)

[augustientje]

Me too 😉 Postgresql 7.4. I’m also using parameters in my query, so some support for that would be great!

[support-jeff]

Are you referring to using PreparedStatement? or to postgresql cursors? An example and/or use case would be very helpful. Thanx!

[Dimitrios Bougoulias]

@support-jeff wrote:

Trying to get a feel for the typical scenario out there. Please post replies with others you may be using (including me-tos on those posted as replies)

What about firbirdsql 1.5.1 with JCA/JDBC driver (JayBird) 1.5?

[arjan.tijms]

The way I’m using parameters in my SQL files is quite simple,

Something along the lines of:

select
     <columnName/>
from
      sometable

In my web app, I use something like,

loadQuery (“name of query”, mapWithArguments);
executeQuery();
etc…

Where mapWithArguments is a hash table containing the key columnName and some value. Although this isn’t standard SQL ofcourse, I’ve seen the scheme being used by many people. It just generates the full SQL text, and is thus not DBMS dependent.

[support-jeff]

Ok, so you are talking about runtime (i.e. “on-th-fly”) SQL generation. That is different from what I was thinking. I was thinking more along the lines of support for PreparedStatements. Example, I could copy from my app the SQL:

select a.x, a.y, a.z from MYTABLE a where a.id = ?

And then type in arbitrary values for the parameter and run it. This is much more in line with standards. Oracle supports this kind of thing in SQL*Plus using very limited PL/SQL syntax. Quite handy.

The runtime generation like what you refer to is unlikely to be supported in the tool. We are much more geared to Hibernate providing that level of support.

[henk]

I agree that supporting PreparedStatement is important. Afterall, this is a standard feature in JDBC.

Personally, I don’t like the indexed nature of setting the parameters. It’s really a shame they didn’t start out with named place holders. I believe SQL Server supports them using the @ sign, e.g.

select a.x, a.y, a.z from MYTABLE a where a.id = @myid

I’m no expert on this matter though, and have no idea whether this works through a JDBC connection at all.

Dynamic SQL (which is the term I believe is most often used for generating SQL on-the-fly), still has its uses. You can universally use named parameters with it, and insert partial SQL constructs (e.g. extra expressions in the where clause). There is a SQL injection risk with the latter, but that would be the programmer’s responsibility.

I don’t think this would be that hard to support if you are already going to support parameters in the editor using the question mark. Basically all you would have to do is adding a SQL editor preference for setting the placeholder syntax and whether it is named or index based.

E.g.

placeholder: “?” , type: index based
placeholder: “@” + PARAMETER_NAME, type: named
placeholder: “<” + PARAMETER_NAME + “/>”, type: named

Maybe the type setting could even be omitted as you could simply infer that from the use of PARAMETER_NAME. For giving the parameters values in the editor, using an index could always be an option.

With this approach, you would be compatible with a wide range of parameterized SQL dialects, whether they would be official standards or home-build.

[bonevich]

touch

[Tom Cole]

You didn’t include SQL Server 2000. That’s what I’m using it with.

[Liz Sommers]

I use the explorer with hsqldb (while developing and testing), mysql for some applications, and oracle 9i while supporting software.

I need it to work with all of these. And Postgres. I also have a few McKoi applications I support. All over the map here.

[support-jeff]

wow. you certainly could not vote for all of those! But message received.

[java-consulting]

I use PostgreSQL 8.0 beta 2 and if there’s one thing I’d like to see in the DB Explorer, it’d be complete support (at version 8.0 and later) for the many capabilities and types of this great ORDBMS. That’s not asking too much, is it? 😀

[support-jeff]

I hear ya. Can you provide more details and I will open an enhancement request? It is probably not likely to see the light of day for a while given current priorities (Oracle), but it would be good to capture these.

[stevedxu]

Will that be possible to view stored procedures in DB Explorer, thus make it a universal resource controller. Also when you select multiple tables and try to generate select statement or generate create table script, it generates only for the first table selected. It would be nice to have one click for all objects operations.

[Author]

Posts