How to use the Replace() function of Access with executeUpdate command in Java?

Question

I have used below code to replace a string by another string in executeUpdate method.The code is:

                  try
                  {
                   Connection cn=null;Statement st=null;
                  Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
                  String constring="jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=D:sandeep\\copy of abcdef.mdb;";
                  cn=DriverManager.getConnection(constring, "", ""); 
                  st=cn.createStatement();
                  String sql="UPDATE temp SET mobile = Replace(mobile, '-', '')";
                  st.executeUpdate(sql.toString());
                  }
                 catch(Exception ex){System.out.println(ex);
                  }

The exception is:

java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Undefined function 'Replace' in expression.

Does that SQL run manually? I thought for functions you had to create a CallableStatement? — CodeChimp
– CodeChimp, Commented Jan 24, 2014 at 12:21

Gord Thompson · Accepted Answer · 2014-01-24 12:52:16Z

Driver={Microsoft Access Driver (*.mdb)}; uses the older "Jet" database driver, and when using that driver the Replace() function is not available to queries executed from outside the Access application itself. In other words, that query will work if executed from within Access, but it will not work when executed from an external program like your Java application.

If you really need to use the Replace() function you could try installing the 32-bit version of the newer Access Database Engine (a.k.a. "ACE"). The newer version of the driver allows Replace() (and other functions) to be used in places where the older driver did not.

I just tested this using the newer "ACE" driver and the following code works for me.

Note that the driver name is slightly different: {Microsoft Access Driver (*.mdb, *.accdb)}

import java.sql.*;

public class JDBCQuery {

    public static void main(String args[]) {
        Connection con = null;
        Statement st = null;
        try {
            String connectionString = null;
            connectionString = 
                    "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};" + 
                    "DBQ=C:\\__tmp\\myDb.mdb;";
            con = DriverManager.getConnection(connectionString);

            st = con.createStatement();
            String sql = "UPDATE temp SET mobile = Replace(mobile, '-', '')";
            st = con.createStatement();
            st.execute(sql);
        } catch( Exception e ) {
            e.printStackTrace();
        } finally {
            try {
                if (ps != null) {
                    ps.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch( Exception e ) {
                e.printStackTrace();
            }
        }
    }
}

ravibagul91 · Accepted Answer · 2014-01-25 10:04:19Z

0

I think the problem with this line only

Replace

String constring="jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=D:sandeep\\copy of abcdef.mdb;";

By

String dbFileName="D:/sandeep/copy of abcdef.mdb";
String constring="jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};"+
                        "DBQ="+dbFileName+";";

answered Jan 25, 2014 at 10:04

ravibagul91

20.9k6 gold badges40 silver badges62 bronze badges

Collectives™ on Stack Overflow

How to use the Replace() function of Access with executeUpdate command in Java?

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related