JDBC/ Inserting rows into an Access Database

[David Rancour]

Hi everyone..

I am getting into JDBC and am having what appears to be a stupid problem.
I
have an access database that I am testing against. There are 3 cols in the
users table (the only table) and they are as follows:

id - AutoNumber
username - Text, 250
password - Text 50

Anyway, I wrote the simpliest java program to insert a row into this table
automatically and everything appears to work but when I got back into the
database the row did not insert at all. Does anyone see any problems with
the basic code below:

import java.sql.*;

class javaDBTest
{
public static void main(String[] args)
{
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String url = "jdbcdbc:javaTest";
Connection con = DriverManager.getConnection(url,"","");
Statement stmt = con.createStatement();
String strSQL = "INSERT INTO users (username,password) values
('daffy duck','xa7guot9')";
System.out.println(strSQL);
try
{
System.out.println(stmt.executeUpdate(strSQL));
con.commit();
System.out.println("username inserted");
}
catch (SQLException e)
{
System.out.println(e.toString());
}
}
catch (Exception e)
{
System.out.println(e.toString());
}
}
}


output:

---------- Run Java Code ----------
INSERT INTO users (username,password) values ('daffy duck','blah')
1
username inserted
Normal Termination
Output completed (0 sec consumed).

..?? I get the line that says 'username inserted' and the output for the
executeUpdate statement is 1, so I assume that means that 1 record was
affected (ie - correct), but the row is not actually being inserted into
the
database. I can execute select statements against the connection and they
work fine. What am I doing wrong?? JDK 1.3 SE.

Thanks!!

David Rancour

[David Rancour]

I got a response to this question on another newsgroup. I thought that I'd
share it with you all. Basically you have reposition the cursor by either
closing the connection or moving to another row for the change to show up,
so below after calling executeUpate I added con.close(); and it worked fine.


Strange...oh well, if it was easy everyone would do it right?

David Rancour

"David Rancour" <david_rancour@hotmail.com> wrote:
>
>Hi everyone..
>
>I am getting into JDBC and am having what appears to be a stupid problem.
>I
>have an access database that I am testing against. There are 3 cols in the
>users table (the only table) and they are as follows:
>
>id - AutoNumber
>username - Text, 250
>password - Text 50
>
>Anyway, I wrote the simpliest java program to insert a row into this table
>automatically and everything appears to work but when I got back into the
>database the row did not insert at all. Does anyone see any problems with
>the basic code below:
>
>import java.sql.*;
>
>class javaDBTest
>{
> public static void main(String[] args)
> {
> try
> {
> Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
> String url = "jdbcdbc:javaTest";
> Connection con = DriverManager.getConnection(url,"","");
> Statement stmt = con.createStatement();
> String strSQL = "INSERT INTO users (username,password) values
>('daffy duck','xa7guot9')";
> System.out.println(strSQL);
> try
> {
> System.out.println(stmt.executeUpdate(strSQL));
> con.commit();
> System.out.println("username inserted");
> }
> catch (SQLException e)
> {
> System.out.println(e.toString());
> }
> }
> catch (Exception e)
> {
> System.out.println(e.toString());
> }
> }
>}
>
>
>output:
>
>---------- Run Java Code ----------
>INSERT INTO users (username,password) values ('daffy duck','blah')
>1
>username inserted
>Normal Termination
>Output completed (0 sec consumed).
>
>..?? I get the line that says 'username inserted' and the output for the
>executeUpdate statement is 1, so I assume that means that 1 record was
>affected (ie - correct), but the row is not actually being inserted into
>the
>database. I can execute select statements against the connection and they
>work fine. What am I doing wrong?? JDK 1.3 SE.
>
>Thanks!!
>
>David Rancour
>
>
>

[Paul Clapham]

How do you know the records are not being inserted? This question is not as
facetious as it appears: if you open the table in Access, then run your
program that inserts records, the Access display will not automatically
update itself to display the new records. Thus it appears that adding
records didn't work, although the records are in the database and will be
displayed if you close the Access display and reopen it.

PC2

"David Rancour" <david_rancour@hotmail.com> wrote in message
news:3b149664$1@news.devx.com...
>
> Hi everyone..
>
> I am getting into JDBC and am having what appears to be a stupid problem.
> I
> have an access database that I am testing against. There are 3 cols in the
> users table (the only table) and they are as follows:
>
> id - AutoNumber
> username - Text, 250
> password - Text 50
>
> Anyway, I wrote the simpliest java program to insert a row into this table
> automatically and everything appears to work but when I got back into the
> database the row did not insert at all. Does anyone see any problems with
> the basic code below:
>
> import java.sql.*;
>
> class javaDBTest
> {
> public static void main(String[] args)
> {
> try
> {
> Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
> String url = "jdbcdbc:javaTest";
> Connection con = DriverManager.getConnection(url,"","");
> Statement stmt = con.createStatement();
> String strSQL = "INSERT INTO users (username,password)
values
> ('daffy duck','xa7guot9')";
> System.out.println(strSQL);
> try
> {
> System.out.println(stmt.executeUpdate(strSQL));
> con.commit();
> System.out.println("username inserted");
> }
> catch (SQLException e)
> {
> System.out.println(e.toString());
> }
> }
> catch (Exception e)
> {
> System.out.println(e.toString());
> }
> }
> }
>
>
> output:
>
> ---------- Run Java Code ----------
> INSERT INTO users (username,password) values ('daffy duck','blah')
> 1
> username inserted
> Normal Termination
> Output completed (0 sec consumed).
>
> .?? I get the line that says 'username inserted' and the output for the
> executeUpdate statement is 1, so I assume that means that 1 record was
> affected (ie - correct), but the row is not actually being inserted into
> the
> database. I can execute select statements against the connection and they
> work fine. What am I doing wrong?? JDK 1.3 SE.
>
> Thanks!!
>
> David Rancour
>
>
>

[David Rancour]

No, I had closed out the database completely and reopened it...no new record.
Then I rebooted and opened the database - still the record is not there.
However closing the connection after the insert and opening the database
in access had the new record.

"Paul Clapham" <pclapham@core-mark.com> wrote:
>How do you know the records are not being inserted? This question is not
as
>facetious as it appears: if you open the table in Access, then run your
>program that inserts records, the Access display will not automatically
>update itself to display the new records. Thus it appears that adding
>records didn't work, although the records are in the database and will be
>displayed if you close the Access display and reopen it.
>
>PC2
>
>"David Rancour" <david_rancour@hotmail.com> wrote in message
>news:3b149664$1@news.devx.com...
>>
>> Hi everyone..
>>
>> I am getting into JDBC and am having what appears to be a stupid problem.
>> I
>> have an access database that I am testing against. There are 3 cols in
the
>> users table (the only table) and they are as follows:
>>
>> id - AutoNumber
>> username - Text, 250
>> password - Text 50
>>
>> Anyway, I wrote the simpliest java program to insert a row into this table
>> automatically and everything appears to work but when I got back into
the
>> database the row did not insert at all. Does anyone see any problems with
>> the basic code below:
>>
>> import java.sql.*;
>>
>> class javaDBTest
>> {
>> public static void main(String[] args)
>> {
>> try
>> {
>> Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
>> String url = "jdbcdbc:javaTest";
>> Connection con = DriverManager.getConnection(url,"","");
>> Statement stmt = con.createStatement();
>> String strSQL = "INSERT INTO users (username,password)
>values
>> ('daffy duck','xa7guot9')";
>> System.out.println(strSQL);
>> try
>> {
>> System.out.println(stmt.executeUpdate(strSQL));
>> con.commit();
>> System.out.println("username inserted");
>> }
>> catch (SQLException e)
>> {
>> System.out.println(e.toString());
>> }
>> }
>> catch (Exception e)
>> {
>> System.out.println(e.toString());
>> }
>> }
>> }
>>
>>
>> output:
>>
>> ---------- Run Java Code ----------
>> INSERT INTO users (username,password) values ('daffy duck','blah')
>> 1
>> username inserted
>> Normal Termination
>> Output completed (0 sec consumed).
>>
>> .?? I get the line that says 'username inserted' and the output for the
>> executeUpdate statement is 1, so I assume that means that 1 record was
>> affected (ie - correct), but the row is not actually being inserted into
>> the
>> database. I can execute select statements against the connection and they
>> work fine. What am I doing wrong?? JDK 1.3 SE.
>>
>> Thanks!!
>>
>> David Rancour
>>
>>
>>
>
>

[Jack]

David -
I don't think it's your code. I had the same problem with MS Access a few
months ago ... I wrote a small test program to do selects and inserts and
it worked fine in Oracle, but did what you describe when I tried it on an
Access db: selects worked fine, but inserts never happened, even though the
statement appeared to execute successfully.

I gave-up after a short time. I suspect that either: (a) update permissions
were not enabled for the account (username/password) I was using for the
database itself (but this really *should* have thrown a SQLException), or
(b) it is a bug in the ODBC driver.

Jack

"David Rancour" <david_rancour@hotmail.com> wrote:
>
>No, I had closed out the database completely and reopened it...no new record.
>Then I rebooted and opened the database - still the record is not there.
>However closing the connection after the insert and opening the database
>in access had the new record.
>
>"Paul Clapham" <pclapham@core-mark.com> wrote:
>>How do you know the records are not being inserted? This question is not
>as
>>facetious as it appears: if you open the table in Access, then run your
>>program that inserts records, the Access display will not automatically
>>update itself to display the new records. Thus it appears that adding
>>records didn't work, although the records are in the database and will
be
>>displayed if you close the Access display and reopen it.
>>
>>PC2
>>
>>"David Rancour" <david_rancour@hotmail.com> wrote in message
>>news:3b149664$1@news.devx.com...
>>>
>>> Hi everyone..
>>>
>>> I am getting into JDBC and am having what appears to be a stupid problem.
>>> I
>>> have an access database that I am testing against. There are 3 cols in
>the
>>> users table (the only table) and they are as follows:
>>>
>>> id - AutoNumber
>>> username - Text, 250
>>> password - Text 50
>>>
>>> Anyway, I wrote the simpliest java program to insert a row into this
table
>>> automatically and everything appears to work but when I got back into
>the
>>> database the row did not insert at all. Does anyone see any problems
with
>>> the basic code below:
>>>
>>> import java.sql.*;
>>>
>>> class javaDBTest
>>> {
>>> public static void main(String[] args)
>>> {
>>> try
>>> {
>>> Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
>>> String url = "jdbcdbc:javaTest";
>>> Connection con = DriverManager.getConnection(url,"","");
>>> Statement stmt = con.createStatement();
>>> String strSQL = "INSERT INTO users (username,password)
>>values
>>> ('daffy duck','xa7guot9')";
>>> System.out.println(strSQL);
>>> try
>>> {
>>> System.out.println(stmt.executeUpdate(strSQL));
>>> con.commit();
>>> System.out.println("username inserted");
>>> }
>>> catch (SQLException e)
>>> {
>>> System.out.println(e.toString());
>>> }
>>> }
>>> catch (Exception e)
>>> {
>>> System.out.println(e.toString());
>>> }
>>> }
>>> }
>>>
>>>
>>> output:
>>>
>>> ---------- Run Java Code ----------
>>> INSERT INTO users (username,password) values ('daffy duck','blah')
>>> 1
>>> username inserted
>>> Normal Termination
>>> Output completed (0 sec consumed).
>>>
>>> .?? I get the line that says 'username inserted' and the output for the
>>> executeUpdate statement is 1, so I assume that means that 1 record was
>>> affected (ie - correct), but the row is not actually being inserted into
>>> the
>>> database. I can execute select statements against the connection and
they
>>> work fine. What am I doing wrong?? JDK 1.3 SE.
>>>
>>> Thanks!!
>>>
>>> David Rancour
>>>
>>>
>>>
>>
>>
>

[Ruchi Dhar]

The problem is neither with java nor with MSAccess. Its with odbc. When
you open the table in Access and try to use the odbc to insert ot update
a record it will fail. Close your Access application and run your java appliction
now open access you will find the record.

"Jack" <jdonohue@irise.com> wrote:
>
>David -
>I don't think it's your code. I had the same problem with MS Access a few
>months ago ... I wrote a small test program to do selects and inserts and
>it worked fine in Oracle, but did what you describe when I tried it on an
>Access db: selects worked fine, but inserts never happened, even though
the
>statement appeared to execute successfully.
>
>I gave-up after a short time. I suspect that either: (a) update permissions
>were not enabled for the account (username/password) I was using for the
>database itself (but this really *should* have thrown a SQLException), or
>(b) it is a bug in the ODBC driver.
>
>Jack
>
>"David Rancour" <david_rancour@hotmail.com> wrote:
>>
>>No, I had closed out the database completely and reopened it...no new record.
>>Then I rebooted and opened the database - still the record is not there.
>>However closing the connection after the insert and opening the database
>>in access had the new record.
>>
>>"Paul Clapham" <pclapham@core-mark.com> wrote:
>>>How do you know the records are not being inserted? This question is
not
>>as
>>>facetious as it appears: if you open the table in Access, then run your
>>>program that inserts records, the Access display will not automatically
>>>update itself to display the new records. Thus it appears that adding
>>>records didn't work, although the records are in the database and will
>be
>>>displayed if you close the Access display and reopen it.
>>>
>>>PC2
>>>
>>>"David Rancour" <david_rancour@hotmail.com> wrote in message
>>>news:3b149664$1@news.devx.com...
>>>>
>>>> Hi everyone..
>>>>
>>>> I am getting into JDBC and am having what appears to be a stupid problem.
>>>> I
>>>> have an access database that I am testing against. There are 3 cols
in
>>the
>>>> users table (the only table) and they are as follows:
>>>>
>>>> id - AutoNumber
>>>> username - Text, 250
>>>> password - Text 50
>>>>
>>>> Anyway, I wrote the simpliest java program to insert a row into this
>table
>>>> automatically and everything appears to work but when I got back into
>>the
>>>> database the row did not insert at all. Does anyone see any problems
>with
>>>> the basic code below:
>>>>
>>>> import java.sql.*;
>>>>
>>>> class javaDBTest
>>>> {
>>>> public static void main(String[] args)
>>>> {
>>>> try
>>>> {
>>>> Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
>>>> String url = "jdbcdbc:javaTest";
>>>> Connection con = DriverManager.getConnection(url,"","");
>>>> Statement stmt = con.createStatement();
>>>> String strSQL = "INSERT INTO users (username,password)
>>>values
>>>> ('daffy duck','xa7guot9')";
>>>> System.out.println(strSQL);
>>>> try
>>>> {
>>>> System.out.println(stmt.executeUpdate(strSQL));
>>>> con.commit();
>>>> System.out.println("username inserted");
>>>> }
>>>> catch (SQLException e)
>>>> {
>>>> System.out.println(e.toString());
>>>> }
>>>> }
>>>> catch (Exception e)
>>>> {
>>>> System.out.println(e.toString());
>>>> }
>>>> }
>>>> }
>>>>
>>>>
>>>> output:
>>>>
>>>> ---------- Run Java Code ----------
>>>> INSERT INTO users (username,password) values ('daffy duck','blah')
>>>> 1
>>>> username inserted
>>>> Normal Termination
>>>> Output completed (0 sec consumed).
>>>>
>>>> .?? I get the line that says 'username inserted' and the output for
the
>>>> executeUpdate statement is 1, so I assume that means that 1 record was
>>>> affected (ie - correct), but the row is not actually being inserted
into
>>>> the
>>>> database. I can execute select statements against the connection and
>they
>>>> work fine. What am I doing wrong?? JDK 1.3 SE.
>>>>
>>>> Thanks!!
>>>>
>>>> David Rancour
>>>>
>>>>
>>>>
>>>
>>>
>>
>