CODEWITHSUNDEEP
javadatabasejdbcviewh2
Maurizio Pozzobon
Maurizio Pozzobon

Reputation: 3084

Insert into view in h2 database

edit: I want to add values to a table (paziente) working with a view of that table (viewPaziente) and not directly the table.

edit2: Found a stupid mistake in the code, now it does give me an error, but it is not helping:

org.h2.jdbc.JdbcSQLException: Feature not supported: "VIEW"; SQL statement: INSERT INTO "viewPaziente" values(?,?,?,?,?,?,?,?,?,?,?,?,?) [50100-147]

Is it possible to insert a row in a view of a table?

I mean... I have a table "paziente" with many fields, I've created a view of Paziente and I want to add a row to paziente through the view. Is it possible to do this in H2?

I'm using the following code

public static boolean AddAnagrafica(String nome, String cognome, 
        String data, String telefono, String email,String codiceFiscale, boolean isDonna, String indirizzo, String citta, 
        String provincia, String cap, String paese ){
    Connection conn=null;
    try {
         conn = getConnection();
         PreparedStatement st = conn.prepareStatement("INSERT INTO \"viewPaziente\" values(?,?,?,?,?,?,?,?,?,?,?,?,?)");
         st.setInt(1, new Random().nextInt()); 
         st.setString(2,nome);
         st.setString(3,cognome);
         st.setString(4,data);
         st.setString(5,telefono);
         st.setString(6,email);
         st.setString(7,codiceFiscale);
         st.setBoolean(8,isDonna);
         st.setString(9,indirizzo);
         st.setString(10,citta);
         st.setString(11,provincia);
         st.setString(12,cap);
         st.setString(13,paese);
         st.executeUpdate();
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return false;
}

Upvotes: 3

Views: 9024

Answers (2)

Thomas Mueller
Thomas Mueller

Reputation: 50127

In H2, views are not updatable by default. To make them updatable, you need to use an 'instead of' trigger. An example on how to do that available in the H2 source repository.

Upvotes: 8

Dead Programmer
Dead Programmer

Reputation: 12585

If you want to copy specific columns to be inserted in to a view through java ,I hope this is what you are looking for, if not let me know.

st.executeQuery("insert into viewPaziente(ID, Name, Start_Date) values SELECT id, first_name, sysdate FROM Paziente Where <matching condition>");

More Information on Insert ,Update or Delete http://sql-plsql.blogspot.com/2009/03/insert-update-delete-views.html

Upvotes: 0

Related Questions