CODEWITHSUNDEEP
javamysqlsqloracle-database
Brarord
Brarord

Reputation: 651

How to get information about "operation finished with success" after native query?

I am creating web-app which will be used to database managament. Now i am trying to implement "sql interpreter" and after input some incorrect queries i need to print sql errors. Here is my code: 

public String executeSQL(String[] split){

    SessionFactory hibernateFactory = someService.getHibernateFactory();
    Session session = hibernateFactory.openSession();
    String feedback= null;
    for (int i = 0; i < split.length; i++) {
        try{
            String query = split[i];
            session.doWork(connection -> connection.prepareStatement(query).execute());
            feedback= "Success";
        }
       catch(Exception e){
            feedback= ((SQLGrammarException) e).getSQLException().getMessage();
       }

    }
    session.close();
    return feedback;
}

My question:
Is there any way to get "positive message"? I mean: if i will do for example: 'insert into table' i want message:
"1 rows affected"
You know what i mean, i want that information from sql compiler:
enter image description here

Upvotes: 2

Views: 62

Answers (1)

Jon Heller
Jon Heller

Reputation: 36807

You can use my open source program plsql_lexer to generate information about the success of an operation. The program imitates the feedback messages produced by SQL*Plus. The program handles all documented (and some undocumented) command types. The downside is that it must be installed on the database and requires a separate call to the database.

For example, after installing the program (which is mostly just download and run "@install"), create a simple function like this:

create or replace function get_success_message
(
    p_statement clob,
    p_number_of_rows number
) return varchar2 is
    v_success_message varchar2(4000);
    v_ignore          varchar2(4000);
begin
    --Get the feedback message.
    statement_feedback.get_feedback_message(
        p_tokens                  => plsql_lexer.lex(p_statement),
        p_rowcount                => p_number_of_rows,
        p_success_message         => v_success_message,
        p_compile_warning_message => v_ignore
    );

    return v_success_message;
end;
/

Now you can generate feedback messages by calling the function, like this:

select
    get_success_message('insert into test1 values(1)', 1) insert_1,
    get_success_message('insert into test1 values(1)', 0) insert_0,
    get_success_message('delete from test1', 50) delete_50
from dual;

INSERT_1         INSERT_0          DELETE_50
--------------   ---------------   ----------------
1 row created.   0 rows created.   50 rows deleted.

The program was built to create database-centric, private SQL Fiddles, and may help with other tasks related to running arbitrary database commands.

Upvotes: 1

Related Questions