Sources: Executing commands from a database connector?

CRead · August 23, 2012 6:41AM

I use the database table source with custom SQL to bring data into Omnicope, but unlike a normal development environment, I can't execute commands, such as calls to refresh views. So running code like this fails:
CALL refresh_active_sites(@rc);
SELECT * FROM active_sites;

Is this prevented for security reasons, or should I be using a different approach?
Similarly, if I could do this, I could pass instructions to not lock tables along, because at the moment I cannot do this.

chris · August 23, 2012 6:56AM

Hi,

At the moment the Database source only supports you entering a single query.

We could build in the ability to execute commands, separated by a ";". We would then have to assume that there was exactly one "SELECT" command, which was the query, and all other commands should be executed in the order specified.

I've re-classified this as an idea, please vote and let us know if you have any further ideas for enhancing this functionality.

steve · August 23, 2012 7:37AM

Update: after some experimentation, it seems that SQL Server (at least) supports multiple statements.

The following works in a single "Database Table" custom SQL:


DROP TABLE abcd;
CREATE TABLE abcd (
   pub_id  char(4) NOT NULL 
);
SELECT * FROM abcd

On other databases you may need to use BEGIN and END. It may be that the CALL statement is not permitted in JDBC statements.

Can you achieve this using some kind of database trigger? What kind of database are you using?

CRead · August 23, 2012 7:48AM

Using MySQL. The code you wrote also gives a database error:
Database error
An error occurred connecting to or querying the database.
Can not issue data manipulation statements with executeQuery().

steve · August 23, 2012 8:30AM

Can you create a database VIEW which does your two statements? And then select from that view in Omniscope?

Welcome!

Categories

Ideas Parade

Tagged