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")
}
}
3 comments
Comment actions Permalink
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
Comment actions Permalink

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
Comment actions Permalink

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

0

Please sign in to leave a comment.