Overwriting a Access Database using JDBC

[Tushar]

Hi
I am creating a java app that uses JDBC to connect to an Access DB. I
am using SQL INSERT statement to add records to my empty DB. But each
time I execute my App , my DB keeps on increasing in size. Wht I want
is that each time I should execute my app, the initial records be
overwritten by the new ones.
Please help,
Tush

 

[Alan Gutierrez]

I am creating a java app that uses JDBC to connect to an Access
DB. I am using SQL INSERT statement to add records to my empty DB.
But each time I execute my App , my DB keeps on increasing in
size. Wht I want is that each time I should execute my app, the
initial records be overwritten by the new ones.

Then UPDATE them.

 

[Tushar]

here is my code ........ even on using the update statemnt it is not
updating the DB? Any suggestions?
import java.sql.*;
class Example
{
public static void main(String args[])
{
try
{
String url = "jdbcdbcriver={Microsoft Access
Driver (*.mdb)};DBQ="
+ "c:/db2.mdb";
System.out.println(url);
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection myConnection =
DriverManager.getConnection(url,null, null);

//SQL
String q= " UPDATE Addresses SET
ID='3',Name='MAnik',LastName='Desai',Spouse='Sabrina'";
Statement myStatement
=myConnection.createStatement();
int rs = myStatement.executeUpdate(q);

//Closing Statement
myStatement.close();

//Closing Connection
myConnection.close();

}

catch(java.lang.Exception ex)
{
ex.printStackTrace();
}
}

}

 

[Steve Sobol]

Hi
I am creating a java app that uses JDBC to connect to an Access DB. I
am using SQL INSERT statement to add records to my empty DB. But each
time I execute my App , my DB keeps on increasing in size. Wht I want
is that each time I should execute my app, the initial records be
overwritten by the new ones.

#1: use update, as someone else said, and #2: Access databases grow in size and
must periodically be compacted. There's a way to do this programmatically using
ActiveX, if I recall correctly, but I'm not sure if there's a way to do it
without ActiveX. You might need to use ActiveX and JNI.

--
JustThe.net - Apple Valley, CA - http://JustThe.net/ - 888.480.4NET (4638)
Steven J. Sobol, Geek In Charge / (e-mail address removed) / PGP: 0xE3AE35ED

"In case anyone was wondering, that big glowing globe above the Victor
Valley is the sun." -Victorville _Daily Press_ on the unusually large
amount of rain the Southland has gotten this winter (January 12th, 2005)

 

[Tushar]

Could you pls tell me the error in the above code.
Thanks

 

[alan]

here is my code ........ even on using the update statemnt it is not
updating the DB? Any suggestions?
import java.sql.*;
class Example
{
public static void main(String args[])
{
try
{
String url = "jdbcdbcriver={Microsoft Access
Driver (*.mdb)};DBQ="
+ "c:/db2.mdb";
System.out.println(url);
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection myConnection =
DriverManager.getConnection(url,null, null);

//SQL
String q= " UPDATE Addresses SET
ID='3',Name='MAnik',LastName='Desai',Spouse='Sabrina'";
Statement myStatement
=myConnection.createStatement();
int rs = myStatement.executeUpdate(q);

//Closing Statement
myStatement.close();

//Closing Connection
myConnection.close();

}

catch(java.lang.Exception ex)
{
ex.printStackTrace();
}
}

}

I'm not familiar with JDBC, co I can't comment on the Java.

But...

No stack trace?

The UPDATE statement will update every row in the database, by the way.

 

[alan]

here is my code ........ even on using the update statemnt it is not
updating the DB? Any suggestions?
import java.sql.*;
class Example
{
public static void main(String args[])
{
try
{
String url = "jdbcdbcriver={Microsoft Access
Driver (*.mdb)};DBQ="
+ "c:/db2.mdb";
System.out.println(url);
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection myConnection =
DriverManager.getConnection(url,null, null);

//SQL
String q= " UPDATE Addresses SET
ID='3',Name='MAnik',LastName='Desai',Spouse='Sabrina'";
Statement myStatement
=myConnection.createStatement();
int rs = myStatement.executeUpdate(q);

//Closing Statement
myStatement.close();

//Closing Connection
myConnection.close();

}

catch(java.lang.Exception ex)
{
ex.printStackTrace();
}
}

}

I'm not familiar with JDBC, co I can't comment on the Java.

But...

No stack trace?

The UPDATE statement will update every row in the database, by the way.

What is the value of rs ?

 

[Steve Sobol]

I'm not familiar with JDBC, co I can't comment on the Java.

But...

No stack trace?

The UPDATE statement will update every row in the database, by the way.

But the Java code looks OK.


--
JustThe.net - Apple Valley, CA - http://JustThe.net/ - 888.480.4NET (4638)
Steven J. Sobol, Geek In Charge / (e-mail address removed) / PGP: 0xE3AE35ED

"In case anyone was wondering, that big glowing globe above the Victor
Valley is the sun." -Victorville _Daily Press_ on the unusually large
amount of rain the Southland has gotten this winter (January 12th, 2005)

 

[Tushar]

Alan,
Thats wht is happening.... all the rows are getting filled by the same
values that I am updating. so in the end of my app I see a databse with
identical rows!!!....wht should I do to fill in a particular row just
once?

The Java code is correct.

Thanks
Tush

here is my code ........ even on using the update statemnt it is not
updating the DB? Any suggestions?
import java.sql.*;
class Example
{
public static void main(String args[])
{
try
{
String url = "jdbcdbcriver={Microsoft Access
Driver (*.mdb)};DBQ="
+ "c:/db2.mdb";
System.out.println(url);
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection myConnection =
DriverManager.getConnection(url,null, null);

//SQL
String q= " UPDATE Addresses SET
ID='3',Name='MAnik',LastName='Desai',Spouse='Sabrina'";
Statement myStatement
=myConnection.createStatement();
int rs = myStatement.executeUpdate(q);

//Closing Statement
myStatement.close();

//Closing Connection
myConnection.close();

}

catch(java.lang.Exception ex)
{
ex.printStackTrace();
}
}

}

I'm not familiar with JDBC, co I can't comment on the Java.

But...

No stack trace?

The UPDATE statement will update every row in the database, by

the way.

What is the value of rs ?

 

[Tushar]

Alan, value of rs is 0.
All help will be appreciated.
Tush

 

[Tushar]

Is there any syntax error in the UPDATE statement?
Tush

 

[Brian Hetrick]

wht should I do to fill in a particular row just once?

Umm, learning SQL would be a good first step. An INSERT statement
inserts a row, even if it duplicates another row. An UPDATE statement
has a WHERE selection clause telling what rows to update. Either will
raise errors if constraints are violated.

http://www.devguru.com/technologies/jetsql/quickref/jet_sql_intro.html
is a reasonable introduction to the SQL accepted by the Jet database
engine (what you get with Access), before Office 2000. Microsoft
greatly enhanced the DDL aspects of Jet SQL, and included a lot of
ANSIisms it hadn't before, with Access 2000. You can't get at these
through the Access UI or through DAO, but you can through ADO and
ODBC. The new aspects are described in a series of three Access
technical articles at
http://msdn.microsoft.com/library/d...n-us/odc_2003_ta/html/odc_landAccess00_ta.asp.
The three articles are "Fundamental Microsoft Jet SQL for Access
2000," "Intermediate ...," and "Advanced ..."

 

[Tushar]

I have a blank DB hence I am not giving the WHERE .

I just want to insert into DB without duplicating them....

 

[Tushar]

WHERE is optional in an UPDATE statement

 

[Brian Hetrick]

I have a blank DB hence I am not giving the WHERE .

It seems your problem is that in fact you do not have an empty
database. If you did, it wouldn't get bigger with each run of your
program and you wouldn't be getting duplicate rows.

If I understand correctly, you need to handle two cases:
- Handling a new database
- Handling an old database
You can try a statement of the form:
UPDATE Addresses SET Name = 'MAnik', LastName = 'Desai',
Spouse='Sabrina' WHERE ID='3';
This will then modify the row where the ID value is 3. If that state-
ment fails, then you probably want:
INSERT INTO Addresses (ID, Name, LastName, Spouse) VALUES
('3', 'MAnik', 'Desai', 'Sabrina');
which will create the row in the first place.

Another possibility is to throw away any data currently in the data-
base:

DROP TABLE Addresses;

and then recreate the table:

CREATE TABLE Addresses (ID INTEGER PRIMARY KEY,
Name TEXT (30) NOT NULL,
LastName TEXT (30) NOT NULL,
Spouse TEXT (30));

(obviously you should use whatever fields and datatypes you need as I
am just guessing). This will give you an 'empty' database.

 

[Tushar]

Hey,
When I was using INSERT ( in the same fashion as u hv mentioned), each
time I used to execute my DB, the existing DB would get appended with
the same data again. Somebdy suggested to use UPDATE for inserting into
DB. UPDATE will re-write stuff and not append the db.

SInce I am using UPDATE to fil lin data into the DB, I can't use the
WHERE clause ( coz the DB is Emply!) When I tried to update in the
fashion I have posted before........it said Syntax error in UPDATE
Command......

Whts the error.

I am using Access DB

 

[Rhino]

WHERE is optional in an UPDATE statement

That is true but it doesn't mean you can simply omit WHERE to get the result
you want.

For example, if you have 100 rows in a table and each has a unique key, like
a customer number, and you do an UPDATE without a WHERE, you will update
every one of the rows in that table.

Normally, you only want to update specific rows. For example, you may want
to update the address for customer #46. To do this, you would write
something like this:

UPDATE mytable
set address = '123 Main Street'
where customer_number = 46

If you wrote this instead:

UPDATE mytable
set address = '123 Main Street'

without any WHERE clause, EVERY SINGLE CUSTOMER IN YOUR TABLE will get their
address changed to 123 Main Street, not just customer #46.

So PLEASE use a WHERE in your UPDATE statement *UNLESS* you really do want
to change every row of the table in the same way.

Rhino

 

[Alan Gutierrez]

But the Java code looks OK.

Okay, Tushar.

You are not updating any rows if rs is zero. You're probably
running your application for the first time. No rows, no update.

Have you tryed playing around with SQL in Access, or using one
of the many JDBC shell utitities. SQL is really easy stuff.

-- create table foo --
CREATE TABLE foo (bar integer, baz varchar(32));

-- delete all rows in foo --
DELETE FROM foo;

-- Add a row to foo --
INSERT INTO foo (bar, baz) VALUES (1, "fred");

-- Update foo --
UPDATE foo set baz = "barney" WHERE bar = 1;

-- get rid table foo entrely --
DROP TABLE foo;

You'll generally find that you have to write update and insert
statements for all the data in your database. It will feel
redundant until you get used to it.

As someone else noted, optional in the documentation means
optional the same way that import declarations are optional in
java. If you don't put them there, that's fine, but you can use
any classes external to the package. Make sense?

You really ought to slow down and read an SQL primer. If you are
really in a hurry, just serialize your objects to a file for now.

Good luck.

 

[steve]

I have a blank DB hence I am not giving the WHERE .

I just want to insert into DB without duplicating them....

you cannot approach, the database in such a cavalier way.

1. learn the commands needed to update a database , using sql first
2. test without the auto commit on!!


you are in a position where you can do serious Database damage , going about
it this way.

you MUST have a "where" for an update, unless you want to update every
record.

 

[Brian Hetrick]

Whts the error.

The error is basically in the way you are approaching the database.

The data manipulation language part of SQL deals primarily with /sets/
of rows, not with individual rows. The UPDATE command is used to
change the content of a set of rows. If there is no WHERE clause on
the UPDATE command, the UPDATE happily updates all the rows of the
table. If there happen to be no rows in the database, the UPDATE
command happily updates all zero rows in the table.

You are, as I understand it, trying to do a conditional insertion: if
the row is not already there, then insert it. The way to do this is
to (i) check if the row is already there and (ii) if not, insert it.
You can check if the row is already there in several ways; one way is
to UPDATE the /set of rows/ comprising that particular row (like,
WHERE ID = '3') and check if the update affected a non-zero count of
rows. (Another, more polite, way is to SELECT the set of rows
comprising that particular row and check if the recordset returned has
any records. Don't forget to close the recordset afterwards.) So
there will be a conditional in your Java checking what the database
SQL returns. If the row is not there, you want to do an INSERT into
the appropriate table to put the row there.

There are two tricks to thinking about databases: (i) the database is
not a file, it is a data co-processor spiritually akin to, say, a
floating point co-processor; and (ii) that data co-processor deals in
sets of rows.