Run sql statement programatically

Answered

Hello,

 

Is there a way to run an SQL statement programatically through database api?

I am trying to build a dialog panel where the user will choose several options, which will ultimately generate a query that will be executed (similar to how the "Modify table" dialog works). Now I got stuck at the executing the query part.

 

Any help is appreciated.

Thank you!

0
6 comments

Hi!

To run statements pragmatically you need DatabaseConnection object. To get the DatabaseConnection you can use DatabaseSessionManager.facade(...).runSync()/runAsync()/connect() methods. Next step is SmartStatements.poweredBy(connection).simple().exectue(..). So the code should look like:

DatabaseSessionManager.facade(args...).runAsync(conn -> {

  SmartStatements.poweredBy(conn).simple().execute("some sql", ...)

});

 

This is not the final API and can be changed in the future.

0

Hi,

Thank you so much for pointing me in the right direction. I had a little trouble identifying what all the parameters mean for DatabaseSessionManager.facade(...) method, but I was able to find a (probably dumb) workaround and make the code work.

For now, it seems that my code below works very well, but I have a feeling that all the missing / null parameters will backfire at some point.

0

Ibrahim Abdo @... How should it be implemented in the new version?

0

zion What exactly is not working and what version are you targeting?

0

It looks like in the latest IDE versions, like 2023.1 or 2023.2, the DatabaseConnection class does not have the getJdbcConnection() method anymore. Do you know what would be the alternative for this?

 

EDIT: I think I figured it out. In case someone else is looking:

Again, not sure if this is the best method to do it, but it was a starting point for me.

DatabaseSessionManager.getFacade(crtDataSource.getProject(), (LocalDataSource)crtDataSource.getDelegate(), null, null, true, null, new DbDept()).runAsync(conn -> {
    try {
        RemotePreparedStatement prepare = conn.getRemoteConnection().prepareStatement("SELECT NOW() AS now");
        prepare.execute();
        RemoteResultSet rs = prepare.getResultSet();
        rs.next();
        rs.getString("now");
    } catch (RemoteException | SQLException e) {
        throw new RuntimeException(e);
    }

    return null;
});
0

Hello!

It is true that instead of using getJdbcConnection(), you can use getRemoteConnection().

However, you can execute the query in a more convenient way using sessions. Session are our wrapper over the connection.

You need to

1. Create or get existing session by using com.intellij.database.console.session.DatabaseSessionManager#getSessions(com.intellij.openapi.project.Project, com.intellij.database.dataSource.LocalDataSource).

2. Create request com.intellij.database.datagrid.DataRequest.RawRequest and override processRaw method. Here you can get the connection from the second argument - com.intellij.database.dataSource.DatabaseConnectionCore#getRemoteConnection.

3. Send the request for execution session.getMessageBus().getDataProducer().processRequest(request).

1

Please sign in to leave a comment.