Older MySQL JDBC Driver issues with INSERT, UPDATE, DELETE

I haven’t worked on my wife’s recipe site in ages. Originally written as an experiment with JSP and Java, I think my mom and Karen are the only people that use it (which is fine with me). In any case, I recently moved it to a new host and saw a weird issue pop up in catalina.out where any INSERT, UPDATE or DELETE statements were erroring out with the message:

java.sql.SQLException: Can not issue data manipulation statements with executeQuery()

Turns out that earlier versions of the MySQL JDBC driver allowed you to run INSERT / UPDATE / DELETE statements using the executeQuery() method of the PreparedStatement class, which violated the JDBC specification, but worked nonetheless. The newer versions are JDBC compliant and don’t allow that behavior, which causes the above mentioned SQL exception. More from the MySQL forums.

One thought on “Older MySQL JDBC Driver issues with INSERT, UPDATE, DELETE”

  1. Hi, i have the same problem with my java connection of my BD ,this is my code java
    import java.awt.BorderLayout;
    import java.awt.Container;
    import java.awt.Dimension;
    import java.awt.GridLayout;
    import java.awt.event.ActionEvent;
    import java.awt.event.ActionListener;
    import java.sql.Statement;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.text.DateFormatSymbols;
    import java.text.ParseException;
    import java.text.SimpleDateFormat;
    import java.util.Calendar;
    import java.util.Date;
    import java.util.GregorianCalendar;
    import java.util.Locale;
    import java.util.logging.Level;
    import java.util.logging.Logger;
    import javax.swing.*;

    public class JCalendar extends JPanel {

    private Locale _locale = getDefaultLocale();

    private GregorianCalendar _calendar = new GregorianCalendar();

    private DateFormatSymbols _dateSymbols = new DateFormatSymbols();

    private SimpleDateFormat _formatMY = new SimpleDateFormat(“MMMM yyyy”);

    private int _firstDayOfWeek = _calendar.getFirstDayOfWeek();

    private final JLabel _monthYear = new JLabel(“”, SwingUtilities.CENTER);

    private final JLabel[] _daysOfWeek = new JLabel[7];

    private final JButton[] _daysNumber = new JButton[42];

    private final ActionListener _changeMonth = new ActionListener() {

    public void actionPerformed(final ActionEvent ae) {

    final int nb = “next”.equals(ae.getActionCommand()) ? 1: -1;
    _calendar.add(Calendar.MONTH, nb);
    updateMonthYear();
    updateDaysNumber();
    }
    };

    public JCalendar() {
    setLayout(new BorderLayout());
    // Month Panel
    final JPanel monthPanel = new JPanel();
    final JButton previous = new JButton(“<>”);
    next.addActionListener(_changeMonth);
    next.setActionCommand(“next”);
    _monthYear.setPreferredSize(new Dimension(320, 120));
    monthPanel.add(previous);
    monthPanel.add(_monthYear);
    monthPanel.add(next);
    add(monthPanel, BorderLayout.NORTH);

    // Day Panel

    final JPanel dayPanel = new JPanel();

    dayPanel.setLayout(new GridLayout(7, 7));
    for (int i = 0; i < 7; ++i)
    dayPanel.add(_daysOfWeek[i] = new JLabel(" ", SwingUtilities.CENTER));
    for (int i=0; i<_daysNumber.length; ++i)
    {

    //fenetre de confirmation du jour

    _daysNumber[i] = new JButton();
    _daysNumber[i].addActionListener(new ActionListener()
    {

    @Override
    public void actionPerformed(ActionEvent e)
    {

    Connection con = null;
    String DB="med_consul"; // base de données
    String Data = null;
    Statement st=null; // objet d'émission des requêtes
    ResultSet rs=null; // table résultat d'une requête
    String[] jm = {"Matin", "Soir"};

    JOptionPane jop = new JOptionPane(), jop2 = new JOptionPane();

    String nom = (String)jop.showInputDialog(null,"Choisissez un creneau pour " ,"RDV ",JOptionPane.QUESTION_MESSAGE,
    null,jm,jm[0]);
    if ((nom.equals("Matin"))||(nom.equals("Soir")))
    {

    jop2.showMessageDialog(null, "Votre disponibilite est le " + nom, "RDVvali", JOptionPane.INFORMATION_MESSAGE);

    try {
    try {
    Class.forName("com.mysql.jdbc.Driver");
    } catch (ClassNotFoundException ex) {
    Logger.getLogger(JCalendar.class.getName()).log(Level.SEVERE, null, ex);
    }

    con=DriverManager.getConnection("jdbc:mysql://localhost:3306/med_consul","root","");

    } catch (SQLException ex) {

    Logger.getLogger(JCalendar.class.getName()).log(Level.SEVERE, null, ex);
    }
    try {
    // création d'un objet Statement
    st=con.createStatement();
    // exécution d'une requête select
    } catch (SQLException ex) {
    Logger.getLogger(JCalendar.class.getName()).log(Level.SEVERE, null, ex);
    }
    Data = "INSERT INTO rdv('RDV','JourduMarche','Disponibilite' )";
    try {
    rs=st.executeQuery(Data);

    } catch (SQLException ex) {
    Logger.getLogger(JCalendar.class.getName()).log(Level.SEVERE, null, ex);
    }
    try {
    con.close();
    st.close();
    rs.close();
    } catch (SQLException ex) {
    Logger.getLogger(JCalendar.class.getName()).log(Level.SEVERE, null, ex);
    }
    System.out.println("Base " + DB + " fermée");

    }
    }

    });
    dayPanel.add(_daysNumber[i]);
    }

    add(dayPanel, BorderLayout.CENTER);

    // Remplissage des composants
    updateMonthYear();
    updateDaysOfWeek();
    updateDaysNumber();
    }

    // Réactualise la locale et réaffiche le contenu des composants
    private void updateLocale(final Locale locale) {
    _locale = locale;
    final Date tmp = _calendar.getTime();
    _calendar = new GregorianCalendar(_locale);
    _calendar.setTime(tmp);
    _firstDayOfWeek = _calendar.getFirstDayOfWeek();
    _dateSymbols = new DateFormatSymbols(_locale);
    _formatMY = new SimpleDateFormat("MMMM yyyy", _locale);
    updateMonthYear();
    updateDaysOfWeek();
    updateDaysNumber();
    }

    // Affiche le mois et l'année en cours
    private void updateMonthYear() {
    _monthYear.setText(_formatMY.format(_calendar.getTime()));
    }

    // Affiche les jours de la semaine
    private void updateDaysOfWeek() {
    final String[] weekDays = _dateSymbols.getShortWeekdays();
    for (int i = 1; i < weekDays.length; ++i) {
    final int index = (i – 2 + _firstDayOfWeek) % 7 + 1;
    _daysOfWeek[i – 1].setText(weekDays[index]);
    }
    }

    // Affiche le numéro des jours
    private void updateDaysNumber() {
    //apparition des marches dans le mois
    String var ="";
    int M1 = 2;
    int M2 = 3;
    int M3 = 4;
    int M4 = 6;

    final Date tmp = _calendar.getTime();
    _calendar.set(Calendar.DAY_OF_MONTH, 1);
    final int firstDay = _calendar.get(Calendar.DAY_OF_WEEK);
    final int LocalFirstDay = (firstDay – _firstDayOfWeek + 7) % 7 + 1;
    boolean full = false;
    for (int i = 0; i < _daysNumber.length; ++i) {
    //Détermine si le composant est affiché ou non
    final boolean isNotEmpty = i < LocalFirstDay – 1 || full;
    _daysNumber[i].setVisible(!isNotEmpty);

    // Affichage du jour
    if (!isNotEmpty) {
    final int dayOfMonth = _calendar.get(Calendar.DAY_OF_MONTH);
    //apparitions et frequences des marches

    if (dayOfMonth == M1)
    {
    var = var +"A";
    M1 +=3;
    }
    if (dayOfMonth == M2)
    {
    var = var +"B";
    M2 +=4;
    }
    if (dayOfMonth == M3)
    {
    var = var + "C";
    M3 +=5;
    }
    if (dayOfMonth == M4)
    {
    var = var + "D";
    M4 +=7;//frequences ….
    }

    _daysNumber[i].setText(String.valueOf(dayOfMonth)+var);
    var = "";//retourne vide apres chaque execution

    _calendar.add(Calendar.DAY_OF_MONTH, 1);
    full = 1 == _calendar.get(Calendar.DAY_OF_MONTH);

    }

    }
    _calendar.setTime(tmp);
    }

    public static void main(final String[] args)
    throws SQLException {

    SwingUtilities.invokeLater(new Runnable() {
    @Override
    public void run() {
    JFrame frame = new JFrame("Prog Doc ");
    frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
    frame.getContentPane().add(new JCalendar());
    frame.pack();
    frame.setVisible(true);

    }
    });
    }

    }

    help me please , i want to insert into my base

Leave a Reply

Your email address will not be published. Required fields are marked *