How to execute sql query by passing a string and outputting results to the console output?

Answered

I've created an action event, and I have a string value that I'd like to execute using the existing connection and output the results to the console output window, in the same way that ctrl-enter would execute some highlighted string and output the results to the window. I can't simply overwrite the existing keymap because I have manipulated the highlighted string to create the query. For example, if I highlight "tableName", this action would want to run "SELECT * FROM tableName LIMIT 10".

 

I'm not sure if this is close or even running the query but not outputting the results to console, but here is what I have so far:

object EmptyDatabaseDepartment : DatabaseDepartment {
override val commonName = ""
override val departmentName = ""
override val icon = null
}
class ExecuteQueryAction : Alt1() {
override fun actionPerformed(e: AnActionEvent) {
val project = DataManager.getInstance().dataContextFromFocusAsync.blockingGet(2000)?.getData(CommonDataKeys.PROJECT)
val connection = DatabaseSessionManager.getFacade(
project as Project,
(getConsole(e)?.dataSource as LocalDataSource),
null,
null,
false,
null,
EmptyDatabaseDepartment
).connect().get()
SmartStatements.poweredBy(connection).simple().execute("SELECT * FROM tableName")
}
}
1
13 comments
Official comment

Hi Jrmalin!

You do not need to create a new session with connection using the DatabaseSessionManager - the console already has a session with which you need to execute your request.
And console also already has its own DatabaseDepartment.

You need to create a DataRequest.RawRequest, implement how it will be processed and send it for processing.

The query will be executed in the console connection and the result will be printed to the console output window.

override fun actionPerformed(e: AnActionEvent) {
val console = getConsole(e) ?: return
val request = createRequest(console, "SELECT * FROM tableName")
console.messageBus.dataProducer.processRequest(request)
}

fun createRequest(console: JdbcConsole, query: String): DataRequest.RawRequest {
return object : DataRequest.RawRequest(console) {
override fun processRaw(context: Context, connection: DatabaseConnectionCore) {
SmartStatementFactoryService.getInstance().poweredBy(connection).simple().execute(query)
}
}
}


Also data context you can get easier.
This way:

e.dataContext

Instead of:

DataManager.getInstance().dataContextFromFocusAsync.blockingGet(2000)


And you can get the project from the action like this:

val project = e.project

Hi Justin!

 

I'm very sorry I didn't notice your question earlier.

 

However, it is only running the query and logging it to the Output tab in GridImpl->GridCellTabs. Normally, when I execute a query through the normal execution method, a tab is created with a JbLayeredPane-TableScrollPane.

 

Each session and client has its own Output window (com.intellij.database.console.session.DatabaseLogView)

 

When a query is executed by a session, it is logged to the session's Output window (com.intellij.database.console.session.DatabaseSessionLogView), 

query running example (where session is com.intellij.database.console.session.MessageBusSession):

session.messageBus.dataProducer.processRequest(request)

 

When a query is executed by a session client (for example, a console or a data editor), it is logged in the client’s Output window (com.intellij.database.console.session.DatabaseClientLogView) + in the Output window of the session with which this client is associated, 

query running example (where console is com.intellij.database.console.JdbcConsole):

console.messageBus.dataProducer.processRequest(request)

 

If the result is not logged in the window you would like - you probably need to run the query using a different session or client. Find the session or client associated with the Output window in which you would like to see the result.

Please let me know if I misunderstood your problem

1

Justin Malin I have the same issue, but now I did, hope to help you.

DataRequest.QueryRequest will be display the query result.
public class ExecuteSqlAction extends RunQueryAction.Alt1 {
@Override
public void actionPerformed(@NotNull AnActionEvent e) {
var console = getConsole(e);
assert console != null;
var request = new CreateRequest(console, "select current_timestamp",DataRequest.newConstraints(),null);
console.getSession().getMessageBus().getDataProducer().processRequest(request);
}

public static class CreateRequest extends DataRequest.QueryRequest {
protected CreateRequest(@NotNull Owner owner, @NotNull String query, @NotNull Constraints constraints, @Nullable Object params) {
super(owner, query, constraints, params);
}
}
}
1

Josh Taylor ,Hi

Here is the code that not open a new tab for per execution

public static <T> T connectExecuteSql(JdbcConsole console, String sql, Function<RemoteResultSet, T> function) {
    RemoteConnection connection = null;
    RemotePreparedStatement remotePreparedStatement = null;
    RemoteResultSet remoteResultSet = null;
    if (console.getSearchPath() == null || CollectionUtils.isEmpty(console.getSearchPath().elements)) {
        throw new RuntimeException("请选择一个 Schema");
    }
    try (GuardedRef<DatabaseConnection> connectionGuardedRef = DatabaseConnectionManager.getInstance().build(console.getProject(), console.getTarget()).create()) {
        assert connectionGuardedRef != null;
        connection = connectionGuardedRef.get().getRemoteConnection();
        remotePreparedStatement = connection.prepareStatement(sql);
        remoteResultSet = remotePreparedStatement.executeQuery();
        return function.apply(remoteResultSet);
    } catch (Exception e) {
        loggger.error("执行异常{}", e.getMessage(), e);
        throw new RuntimeException(e);
    } finally {
        try {
            if (remoteResultSet != null) {
                remoteResultSet.close();
            }
            if (remotePreparedStatement != null) {
                remotePreparedStatement.close();
            }
            if (connection != null) {
                connection.close();
            }
        } catch (Exception e) {
            loggger.error("资源关闭异常{}", e.getMessage(), e);
        }
    }
}

1

Anna, thanks so much for the help! This was super useful, and I was able to get the console to successfully execute the intended query.

However, it is only running the query and logging it to the Output tab in GridImpl->GridCellTabs. Normally, when I execute a query through the normal execution method, a tab is created with a JbLayeredPane->TableScrollPane.

Is there a straightforward way to do that here? I'm happy to step through an example that exists elsewhere if possible. This is the last major step to getting my plugin working, so I'm eager to figure out a solution to this!

0

Hey, Anna! I know it's a long delay here, but I'm still having some of the same issues.

I tried pulling the `session` by calling `console.session`, but I still had the same issue. One thing to note - when I run a query through the normal "Execute" button, it shows an additional "row retrieved" line printed that is not displayed when I run the query via the plugin. For example, the bolded part is the part not displayed when the plugin is running the query:

postgres.public> select *
                 from table_name
                 limit 10
[2023-03-25 13:06:57] 1 row retrieved starting from 1 in 268 ms (execution: 24 ms, fetching: 244 ms)

I wonder if the lack of row retrieval is a clue as to why I'm not seeing the output table. From your screenshot, the piece wrapped in the red box is the part that my plugin query is missing.

0

How did the getConsole method come from?

0

@anonymous I believe we get that for free by inheriting from `RunQueryAction.Alt1()`. It looks like this:

class ExecuteQueryAction : RunQueryAction.Alt1() {
override fun actionPerformed(e: AnActionEvent) { ... }
}
0

@Justin Malin Thanks, How to get the execution result of "SmartStatementFactoryService.getInstance().poweredBy(connection).simple().execute(query)"?

0

@zion  Hi,I found the way to get the execution result of RawRequest, Here is the code ,It works fine on my plugin.

 public static <T> List<T> consoleExecuteSql(JdbcConsole console, String sql, Function<RemoteResultSet, T> remoteResultSetTFunction) {
        List<T> result = new ArrayList<>();
        Function1<ResultsProducer, List<T>> function = resultsProducer -> {
            // Fix that "Please, call advance before processCurrent() and make sure that results exits"
            resultsProducer.advanceSafe();
            resultsProducer.processCurrent(new ResultsProducer.Processor<List<T>>() {
                @Override
                public List<T> parameterResults(@NotNull List<? extends OutParameter<?>> list, @NotNull Function1<? super OutParameter<?>, ?> function1, int i) throws Exception {
                    return null;
                }

                @Override
                public List<T> results(@NotNull RemoteResultSet remoteResultSet, int i) throws Exception {
                    while (remoteResultSet.next()) {
                        result.add(remoteResultSetTFunction.apply(remoteResultSet));
                    }
                    return result;
                }

                @Override
                public List<T> updateCount(int i, int i1) throws Exception {
                    return null;
                }
            });
            return result;
        };
        // 构建查询
        DataRequest.RawRequest rawRequest = new DataRequest.RawRequest(console) {
            @Override
            public void processRaw(Context context, DatabaseConnectionCore databaseConnectionCore) throws Exception {
                SmartStatementFactoryService.getInstance().poweredBy(databaseConnectionCore).simple().execute(sql, function);
            }
        };
        // 让 console 执行 sql
        console.getMessageBus().getDataProducer().processRequest(rawRequest);
        return result;
    }

 

 

0

Thanks for the tip on QueryRequest! That works well.

Has anyone worked out how to get it to not open a new tab per execution, everytime you run it it always opens a new data tab, so it'll be like Result 1, Result 2, etc.

For the console, this is a setting called "Open results in new tab" under "Query execution", which has the same behaviour if checked.

0

Thanks for the quick reply, I'll give it a go. This will be for an opensource plugin, so I'll share my final implementation (I want to execute a compiled SQL file and show the query results).

I see that `newTab` is being set in `ScriptingClientDataConsumer`:

```
  @NotNull
  private ScriptingClientDataConsumer.ContentInfo reuseOrCreateContent(@NotNull DataRequest.Context context, int subQueryIndex, int resultSetIndex) {
     ConsoleDataRequest.ResultSetSubQuery resultSetSubQuery = getConsoleResultSetSubQuery(context, subQueryIndex);
     ScriptingClientDataConsumer.ResultsPlace place = this.getResultsPlace(resultSetSubQuery);
     boolean newTab = !(context.request instanceof ConsoleDataRequest) || ((ConsoleDataRequest)context.request).newTab;
```

Would be nice if it took into account the setting as well..

0

Got it to work, I think I was in the wrong EDT thread or something funky going on, anyway once I fixed that, it now works as expected.

You need to use `ConsoleDataRequest`, and if you modify the snippets above, you should be able to see what you need to send the request, I also don't need to check the output of the results (yet?).

 

val consoleDataRequest =
   ConsoleDataRequest.newConsoleRequest(console, editor, console.scriptModel, false)
       ?: throw Exception("ConsoleDataRequest is null")
console.getMessageBus().dataProducer.processRequest(consoleDataRequest)
```

The arguments for `newConsoleRequest` are:
```
public static ConsoleDataRequest newConsoleRequest(@NotNull JdbcConsoleBase console, @NotNull Editor editor, @NotNull ScriptModel<?> model, boolean newTab)
0

Please sign in to leave a comment.