Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.java.programmer > #19092 > unrolled thread

problem in inserting record in ms access.

Back to article view | Back to comp.lang.java.programmer

Contents

  problem in inserting record in ms access. Navnath Gadakh <navnathgadakh@gmail.com> - 2012-10-03 21:47 -0700
    Re: problem in inserting record in ms access. Martin Gregorie <martin@address-in-sig.invalid> - 2012-10-04 19:39 +0000
    Re: problem in inserting record in ms access. Arne Vajhøj <arne@vajhoej.dk> - 2012-10-04 17:35 -0400
      Re: problem in inserting record in ms access. Martin Gregorie <martin@address-in-sig.invalid> - 2012-10-04 23:24 +0000
        Re: problem in inserting record in ms access. Arne Vajhøj <arne@vajhoej.dk> - 2012-10-05 20:27 -0400
          Re: problem in inserting record in ms access. Martin Gregorie <martin@address-in-sig.invalid> - 2012-10-06 11:36 +0000
            Re: problem in inserting record in ms access. Arne Vajhøj <arne@vajhoej.dk> - 2012-10-06 09:08 -0400
    Re: problem in inserting record in ms access. Roedy Green <see_website@mindprod.com.invalid> - 2012-10-04 20:33 -0700
      Re: problem in inserting record in ms access. Lew <lewbloch@gmail.com> - 2012-10-05 10:48 -0700

#19092 — problem in inserting record in ms access.

package javaapplication3;
import java.sql.*;

public class JavaApplication3 {
    Connection con;
    Statement st;
    ResultSet rs;
    
    public JavaApplication3()
    {
        connect();
    }
    
    public void connect()
    {
        try
        {
            String driver = "sun.jdbc.odbc.JdbcOdbcDriver";
            Class.forName(driver);
            
            String db = "jdbc:odbc:db1";
            con = DriverManager.getConnection(db);
            st = con.createStatement();
            String sql = "select * from Table1";
            rs = st.executeQuery(sql);
            
            while(rs.next())
            {
                String fname = rs.getString("fname");
                String lname = rs.getString("lname");
                String address = rs.getString("address");
                String email = rs.getString("email");
                String mobile = rs.getString("mobile");
                
                System.out.println(fname+lname+address+email+mobile);
            }
            
        }catch(Exception ex)
        {
            
        }
        
        try
    {
            rs.moveToInsertRow();
            rs.updateString("fname","abc");
            rs.updateString("lname","xyz");
            rs.updateString("address","mubmai");
            rs.updateString("email","abc@gmail.com");
            rs.updateString("mobile","99854874154");
            rs.insertRow();
            
            st.close();
            rs.close();
        
    }
    catch(Exception err)
    {
        System.out.println("Error!!!");
    }
        
        
    }
    public static void main(String[] args) {
        // TODO code application logic here
        new JavaApplication3();
    }
}

[toc] | [next] | [standalone]

#19106

On Wed, 03 Oct 2012 21:47:34 -0700, Navnath Gadakh wrote:

Your problem is here:

>             st = con.createStatement();
>

By default this statement causes executeQuery() to create a non-
updateable ResultSet, so your second try/catch block will fail when you 
try to update it. The Javadocs entry for the ResultSet Interface makes 
this quite clear. It also explains how to create updateable ResultSets.

As a minor point, why use two try/catch blocks when one will do?

Be aware that a thrown SQLException frequently is the head of a chain on 
related SQLException, so your catch block should show the messages from 
every exception in the chain or you can miss seeing all the debugging 
information that JDBC makes available.


-- 
martin@   | Martin Gregorie
gregorie. | Essex, UK
org       |

[toc] | [prev] | [next] | [standalone]

#19109

On 10/4/2012 12:47 AM, Navnath Gadakh wrote:
> package javaapplication3;
> import java.sql.*;
>
> public class JavaApplication3 {
>      Connection con;
>      Statement st;
>      ResultSet rs;

Make them private.

>      public JavaApplication3()
>      {
>          connect();
>      }
>
>      public void connect()
>      {
>          try
>          {
>              String driver = "sun.jdbc.odbc.JdbcOdbcDriver";

I will not recommend the JDBC ODBC bridge unless you are absolutely
forced to use it.

There are plenty of alternatives.

>              Class.forName(driver);
>
>              String db = "jdbc:odbc:db1";
>              con = DriverManager.getConnection(db);
>              st = con.createStatement();

As Martin has explained then this is very likely the cause of
your specific problem.

>              String sql = "select * from Table1";
>              rs = st.executeQuery(sql);
>
>              while(rs.next())
>              {
>                  String fname = rs.getString("fname");
>                  String lname = rs.getString("lname");
>                  String address = rs.getString("address");
>                  String email = rs.getString("email");
>                  String mobile = rs.getString("mobile");
>
>                  System.out.println(fname+lname+address+email+mobile);
>              }
>
>          }catch(Exception ex)
>          {

Always print the exception.

>          }
>
>          try
>      {
>              rs.moveToInsertRow();
>              rs.updateString("fname","abc");
>              rs.updateString("lname","xyz");
>              rs.updateString("address","mubmai");
>              rs.updateString("email","abc@gmail.com");
>              rs.updateString("mobile","99854874154");
>              rs.insertRow();

I would suggest using plain INSERT instead of this.

>              st.close();
>              rs.close();
>
>      }
>      catch(Exception err)
>      {
>          System.out.println("Error!!!");

Always ...

>      }
>
>
>      }
>      public static void main(String[] args) {
>          // TODO code application logic here
>          new JavaApplication3();

Don't do such heavy work in the constructor.

>      }
> }

Arne

[toc] | [prev] | [next] | [standalone]

#19113

On Thu, 04 Oct 2012 17:35:39 -0400, Arne Vajhøj wrote:

> On 10/4/2012 12:47 AM, Navnath Gadakh wrote:
> I will not recommend the JDBC ODBC bridge unless you are absolutely
> forced to use it.
> 
The OP mentioned (in the title) that he is using MS Access. Is there a 
JDBC driver for it?
 
> Always print the exception.
> 
And always print all chained SQLExceptions in the chain or you're likely 
to miss seeing something you need to read.

>>              rs.updateString("mobile","99854874154"); rs.insertRow();
> 
> I would suggest using plain INSERT instead of this.
>
Agreed. I didn't mention it because the OP may have thought he'd save 
time this way. 

However he could be surprised: when I've done this in a GUI program I've 
always made the change using INSERT/UPDATE/DELETE and then reloaded the 
JTable's model from the database. When I wrote one these programs, I 
thought there might be a delay while the table reloaded since there are 
10-12K rows in the table it updates, but to my surprise the displayed 
section of the Jtable is redisplayed in a second or two to show the 
change. Equipment: the Java program is on a 1.3 GHz Core Duo laptop that 
talks to the database over a 10 Mbs local LAN. The database was initially 
PostgreSQL 7.x on an 866MHz 512MB P3 box and is currently PostgreSQL 9.x 
on a 3GHz 4GB Athlon Duo. Both computers run Linux and, surprisingly, I 
haven't noticed that moving the database to the faster box made much 
difference to the speed of this app.
   
> Don't do such heavy work in the constructor.
>
Yep, and get into the habit of putting the session startup and close 
operations into separate methods: you only need to open the connection 
once at the start of the program and close it at the end of the program 
run.

Consider preparing the SQL statement(s) as part of startup. Apart from 
making each SQL call faster, you'll also protect your program from SQL 
injection attacks. The most a database access method should do is issue 
the SQL call, use the ResultSet to retrieve the results and then close 
the ResultSet. Similarly for INSERT/DELETE/UPDATE operations.  


-- 
martin@   | Martin Gregorie
gregorie. | Essex, UK
org       |

[toc] | [prev] | [next] | [standalone]

#19162

On 10/4/2012 7:24 PM, Martin Gregorie wrote:
> On Thu, 04 Oct 2012 17:35:39 -0400, Arne Vajhøj wrote:
>> On 10/4/2012 12:47 AM, Navnath Gadakh wrote:
>> I will not recommend the JDBC ODBC bridge unless you are absolutely
>> forced to use it.
>>
> The OP mentioned (in the title) that he is using MS Access. Is there a
> JDBC driver for it?

No.

But there are plenty of other database.

Going back I can see that I wrote something utterly incomplete.

When I said alternatives it was alternative databases not
JDBC drivers.

Egg on my face.

Arne

[toc] | [prev] | [next] | [standalone]

#19169

On Fri, 05 Oct 2012 20:27:11 -0400, Arne Vajhøj wrote:

> On 10/4/2012 7:24 PM, Martin Gregorie wrote:
>> On Thu, 04 Oct 2012 17:35:39 -0400, Arne Vajhøj wrote:
>>> On 10/4/2012 12:47 AM, Navnath Gadakh wrote:
>>> I will not recommend the JDBC ODBC bridge unless you are absolutely
>>> forced to use it.
>>>
>> The OP mentioned (in the title) that he is using MS Access. Is there a
>> JDBC driver for it?
> 
> No.
>
I thought that might be the case, and hence provides about the only valid 
reason for using the JDBC ODBC bridge. Thanks for the confirmation of 
driver availability.
 
> But there are plenty of other database.
> 
Indeed, unless the OP was told to use Access (but why, when Derby is 
freely available and has the advantage of being written in Java?). I've 
had to fix Access systems in the past and remain unimpressed with it.


-- 
martin@   | Martin Gregorie
gregorie. | Essex, UK
org       |

[toc] | [prev] | [next] | [standalone]

#19172

On 10/6/2012 7:36 AM, Martin Gregorie wrote:
> On Fri, 05 Oct 2012 20:27:11 -0400, Arne Vajhøj wrote:
>> But there are plenty of other database.
>>
> Indeed, unless the OP was told to use Access (but why, when Derby is
> freely available and has the advantage of being written in Java?).

And if one does not like JavaDB/Derby, then there is MySQL, PostgreSQL,
H2 and FireBird - plus the free editions of Oracle, DB2 and SQLServer.

 >                                                                   I've
 > had to fix Access systems in the past and remain unimpressed with it.

The Jet database engine was never anything special. The special was the
integrated application building tool. But that does not help when
writing the app in Java anyway.

Arne

[toc] | [prev] | [next] | [standalone]

#19117

On Wed, 3 Oct 2012 21:47:34 -0700 (PDT), Navnath Gadakh
<navnathgadakh@gmail.com> wrote, quoted or indirectly quoted someone
who said :

>
>package javaapplication3;

It is traditional to:

1. ask a question
2. describe what you expected the code to do.
3. describe what the code actually did.
-- 
Roedy Green Canadian Mind Products http://mindprod.com
The iPhone 5 is a low end Rolex. 

[toc] | [prev] | [next] | [standalone]

#19133

Roedy Green wrote:
> It is traditional to:
> 
> 1. ask a question

For good advice in this area see
http://www.catb.org/esr/faqs/smart-questions.html

This is pretty much the canon of Usenet (and everywhere else) request protocol.

> 2. describe what you expected the code to do.
> 3. describe what the code actually did.

Roedy's excellent point about this tradition is rooted in pragmatics. 

It is difficult for those motivated to help you if we have not enough data with which 
to make an assessment.

Another good source of advice on how to ask code questions:

http://sscce.org/

Roedy's mindprod.com also covers this advice.

-- 
Lew

[toc] | [prev] | [standalone]

Back to top | Article view | comp.lang.java.programmer

csiph-web