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

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!

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

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.

How did the getConsole method come from?

@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) { ... }
}

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

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

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);
      }
   }
}

@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;
    }

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.

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);
        }
    }
}

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..

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)

Josh Taylor It's great, but

How to get Editor?