java restful web service, can't figure how to execute a specific Query

Question

Hello there and thanks in advance for any reply, I'm creating a restful web service and I would like to know:

How to execute a "table exporting query" from eclipse

the query works, it backs up the file, the only problem is it's not being executed for some reason from eclipse

Important to add, I'm using oracle's database express.

in sqlplus: $exp USERID=hr/password TABLES=(hr.students) FILE=exp_tab.dmp

from cmd: sqlplus /as sysdba $exp USERID=hr/password TABLES=(hr.students) FILE=exp_tab.dmp

The method, where I attempted to execute such query is combined with anotherinsert query

My objective is to backup the table (to a file on a local drive) after every insert

note: Yes, I know I can create a trigger for that.

public static boolean insertUser(int id, String name, String gender, int grade) throws SQLException, Exception { boolean insertStatus = false; Connection dbConn = null; try { try { dbConn = DBConnection.createConnection(); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } Statement stmt_backup = dbConn.createStatement(); Statement stmt = dbConn.createStatement(); String query = "INSERT into students(id, name, gender, grade) " + "values('"+id+"',"+"'"+name+"','"+gender+"','"+grade+"')"; String query_backup = "$exp USERID=hr/password TABLES=(hr.students) FILE=exp_tab.dmp"; stmt_backup.executeQuery(query_backup); stmt_backup.close(); //System.out.println(query); int records = stmt.executeUpdate(query); //System.out.println(records); //When record is successfully inserted if (records > 0) { insertStatus = true; } } catch (SQLException sqle) { //sqle.printStackTrace(); throw sqle; } catch (Exception e) { //e.printStackTrace(); // TODO Auto-generated catch block if (dbConn != null) { dbConn.close(); } throw e; } finally { if (dbConn != null) { dbConn.close(); } } return insertStatus; }

PS: I've tried doing that with one statement , and got the same result. Also I tried replacing executeQuery(query_backup); with executeUpdate(Query_backup);

Frédéric Vinet · Accepted Answer · 2015-11-14 12:54:54Z

You did well to ask for help. That will save you a lot of time.

First point: You need to understand better what a Java DB connection is done for. In fact, I think, but maybe I am wrong, that your mistake shows a lack of architectural understanding about client / server architecture (DBMS in the present context).

Quick answer: You can only execute SQL statement through a client connection (whatever the technology you use for establishing that connection). So everything is good for your INSERT request but you cannot execute your sqlplus command through your JDBC connection.
Second point: I find a little bit strange for a web service to make a backup of a table. What do you really want to do?

Third point : if you want to execute a command line statement from Java, here is the solution (but in your case I think it is a bad idea):

public class NewClass { final String userName = "myuser"; final String myPassword = "password"; public NewClass () { String[] myCommandWithParams=new String[4]; myCommandWithParams[0]="sqlplus"; myCommandWithParams[1]="USERID="+userName+"/"+myPassword; myCommandWithParams[2]="TABLES=(hr.students)"; myCommandWithParams[3]="FILE=exp_tab.dmp"; Runtime runtime = Runtime.getRuntime(); try { runtime.exec(myCommandWithParams); } catch (SecurityException | IOException ex) { System.err.println("There is a problem for executing the command"); } catch (IndexOutOfBoundsException ex) { System.err.println("myCommandWithParams can't be empty"); } } }

its not processing the cmd request :/ for example $exp userid=hr/password tables=(hr.students) file=test4.dmp when running the code it gives no error but the file test4.dmp doesnt exist :/

Frédéric Vinet · Accepted Answer · 2015-11-14 17:29:49Z

A remark : The Runtime.exec(String[]) doesn't take into account environment variables. If you want to set environment variables, you must use Runtime.exec(String[],String[]).

The second parameter corresponds to the list of environment variables you want to set. The syntax for these strings is "<VARIABLE_NAME>=<VALUE>". But it is easier to put the path of your sqlplus command in myCommandWithParams[0]. It is up to you to see what is best for you. I don't have the context.

thanks again for your help, I've abandon this backup approach and am studying how to schedule backup events with RMAN (recovery manager) (another oracle product) because it's a bad idea to backup after every insert, that will screw up the sever's performance :/
I agree with your choice. RMan is definitely the right solution for making backup and recovery with Oracle DBMS deployed in a standalone context.In an Enterprise context where you must take into account other database instances and other kind of servers and then you prefer to choose a higher level solution such as Tivoli

Collectives™ on Stack Overflow

java restful web service, can't figure how to execute a specific Query

2 Answers 2

3 Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Related