Java & MS Access

[Davy Kelly]

Hey everyone,

I have got a small test class working with MS Access.

I can create a table, insert columns and values to the table & drop the table.

But I am looking for a little bit of info.

I want to be able to create the table if it is not there, but use it if it is there, I was going to use an if statement for this:

if(tableName != exist)
{
  create the table
}
//rest of code

How can I do this? how do I find the table is there or not?
I have searched google endlessly.

davy
[ April 22, 2007: Message edited by: Davy Kelly ]

[Jeanne Boyarsky]

Davy,
There are two main approaches for doing this:
1) The clean way - do a query against the system table that lists all the other tables (I'm not sure if Access has one of these, but you could find out by looking at the tables in some graphical view.)
2) The other way - do a query against your table and look at the error code if the table does not exist.

[Davy Kelly]

I have been looking at the API regarding ResultSet & ResultSetMetaData, trying to get the getTableName() method to work.

but I am not sure I know what you mean by your suggestions.

davy

[Davy Kelly]

Ok been playing around all day, I seem to have got this working.

I just made the database table through Access and I can now add to it, the program looks as if it is working the way I want it just now.

thanks

davy

[Davy Kelly]

Another quick question...

in my MS Access file, I have 18 columns.
column_1 I made as primary Key, which is using a string which is a date: e.g. 21 April 2007

what code would I use to find a certain row, if it existed, so I can update that row or make a new row if that certain row does not exist?

I was looking into the RowID interfaces equals(object)method but I am not sure this is what I need.

davy

[Jeanne Boyarsky]

Originally posted by Davy Kelly:
what code would I use to find a certain row, if it existed, so I can update that row or make a new row if that certain row does not exist?

You just do a query: select count(*) from table where pk = 'date'

If rs.next() returns false, you need to add a row. If it returns true, the row exists.

[Paul Clapham]

Originally posted by Jeanne Boyarsky:
If rs.next() returns false, you need to add a row. If it returns true, the row exists.

Well, not quite. That query will always return exactly one row. So you need to read it and extract the one column it contains. If the column is zero, there were no rows matching the query.

[Parthasarathy balakrishnan]

Originally posted by Davy Kelly:
Hey everyone,

I have got a small test class working with MS Access.

I can create a table, insert columns and values to the table & drop the table.

But I am looking for a little bit of info.

I want to be able to create the table if it is not there, but use it if it is there, I was going to use an if statement for this:

if(tableName != exist)
{
  create the table
}
//rest of code

How can I do this? how do I find the table is there or not?
I have searched google endlessly.

davy

[ April 22, 2007: Message edited by: Davy Kelly ]

hi,

i have seen you query...


Interesting one yaar
i have work with it....

here is the example please put the needed code...
-----------------------------------------------------------------
/**
* @(#)TableSearch.java
*
*
* @Parthasarathy.B
* @version 1.00 2007/4/23
*/

import java.sql.*;
public class TableSearch {
public static void main(String args[]){
Connection con; /*The Connection Interface*/
Statement st; /*The Statement Interface*/
ResultSet rs; /*The ResultSet InterFace*/
PreparedStatement ps=null;
boolean exists=false;
boolean Table_Creation=false;
try{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");/*loading of the Driver*/
con=DriverManager.getConnection("Jdbc:Odbc:TestConn","","");/*Establishing The Connection*/
st=con.createStatement();/*Statemnet Creation*/
//System.out.println("the value of before the boolean executed");
try{
//exists=st.execute("select * from +"ARGUMENT_TABLE_NAME"+");/* put your Table argument here And Comment Next Line*/
exists=st.execute("select * from TableCreate");
}catch(Exception e1){}
//System.out.println("the value of boolean is"+exists);
if(exists){
System.out.println("Table Name Already Exists "+exists);

}

if(exists==false){
System.out.println("Table Name Does Not Exists"+exists);
st.execute("create table TableCreate(name varchar(20),place varchar(20))");
System.out.println("Table Created");
}

}catch(Exception e){
e.printStackTrace(System.out);
System.out.println("The Exception is in ==>"+e.getMessage());
}
}
}


Note:

1.Please get the argument of the table name and put in "ARGUMENT_TABLE_NAME"
which i have mentioned in the comment area
2.Another familiar error all will make is
varchar2(20) ---> it will work in oracle database not in access

In Access
varchar(20) has to b given...


3.change the 2 line in the code:

a).//exists=st.execute("select * from +"ARGUMENT_TABLE_NAME"+");/* put your Table argument here And Comment Next Line*/

b).//st.execute("create table +"ARGUMENT_TABLE_NAME"+(name varchar(20),place varchar(20))");/*Pass your argument here*/
---------------------------------------------------------------------------

If any doubt regaurding this contact :
In Bangalore:
08066596116.

[Davy Kelly]

Cheers guys.

well I was thinking I would look for the row, so I would need to look in column_1 for a specific String.
then update all columns in that row with new data.

So was I wrong to look for the RowID interface?

Thanks Parthasarathy, I have not looked at your code yet, I am just in from work, but I will look after dinner.

davy

[Davy Kelly]

Sorry, just had a though....... careful now

could I do the following statement?
UPDATE tableName
SET column_2 = newData, column_3 = newData, column_4 = newData, etc
WHERE column_1 = 'specific_Search_Criteria'

sorry I think that will work only when I have the row, and its the row I need to find. just ignore, thinking outloud

davy

[Davy Kelly]

I am very sorry everyone,
Parthasarathy you took my earlier problem, sorry I got this sorted.

but what i was meaning was I am thinking of using an if statement:

if (certain row exists [I]by finding a certain String match in column_1[/I]) //I am stuck at this part
{
  then update the row
}
else
{ 
  create a new row
}

davy

[Jeanne Boyarsky]

Originally posted by Paul Clapham:
Well, not quite. That query will always return exactly one row. So you need to read it and extract the one column it contains. If the column is zero, there were no rows matching the query.

Oh yeah. I was thinking of select(*). Thanks for catching that Paul.

[Davy Kelly]

I managed it eventually

here was the code that worked for me:

try 
{
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    String dataSourceName = "mdbTEST";
    String dbURL = "jdbc:odbc:" + dataSourceName;
    Connection con = DriverManager.getConnection(dbURL, "",""); 
    Statement s = con.createStatement();
    try
    {
s.execute(selectStatement);
ResultSet rs = s.getResultSet(); // get any ResultSet that came from our query
boolean found = rs.next();
if (rs != null) // if rs == null, then there is no ResultSet to view
{
System.out.println("rs != null is True");
System.out.println("\tfound = "+found);
                if(!found)
{
System.out.println("Did Not find a specific Data");
s.execute(insertStatement); // insert some data into the table 
} //close else if
else
{
System.out.println("Found a specific date which is: "+date);
s.execute(updateStatement);
}//close else
System.out.println("\tOutside the While");
} //close if
    }
    catch (Exception err) 
    {
err.printStackTrace();
    }
    finally
    {
s.close(); // close the Statement to let the database know we're done with it
con.close(); // close the Connection to let the database know we're done with it
System.out.println("Finally");
    }
  }
  catch (Exception err)
  {
    err.printStackTrace();
  }
}

davy
[ April 26, 2007: Message edited by: Davy Kelly ]

[Davy Kelly]

hey people,

I got my database working really well.

I only get an error now if I try to store names like O'Reilly with ' being the problem.

how can I be able to add names like this without getting errors?

my update statement is like this:

s.execute("UPDATE "+tableName+" SET column_1 = '"+date+"', column_2 = '"+apps[0]+"' WHERE column_1 = '"+date+"'");

davy
[ April 30, 2007: Message edited by: Davy Kelly ]

[Ulf Dittmer]

Single quotes need to be escaped (by instead using two single quotes in a row), since theey are used as string delimiters by SQL. You can write code that performs this string manipulation, or instead use a PreparedStatement, which does it for you.

[Davy Kelly]

Thanks Ulf,

I have been doing a bit of research on the PreparedStatement.

my update statment right now is big. it deals with 19 columns.
main unique string, 12 Strings, 5 ints one more string.

so I was looking at this PreparedStatement. have I got this right?

PreparedStatement updateDiary;
String update = "UPDATE " +tableName+ " SET * WHERE column_1 = " +date;
updateDiary = con.preparedStatement(update);
updateDiary.setString(1, date);
for(int i = 0; i <= 11; i++)
{
  updateDiary.setString(i+2,apps[i]); //setting from column_2 using begining of said array
}
for(int j = 0; j <= 4; j++)
{
  updateDiary.setInt(j+14, stats[j]); //setting from column_14 using begining of said array
}
updateDiary.setString(19, noteData);

davy

[Ulf Dittmer]

No, this doesn't look right. A PreparedStatement has "?" placeholders where later on the parameters are set. You have none of those, but instead a "*" which has no meaning for a PreparedStatement. Have a look at the javadocs for an example.

[Davy Kelly]

Like this you mean?

 PreparedStatement insertRow;
    String insert = "INSERT INTO " +tableName+ " VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
    insertRow = con.preparedStatement(insert);
    insertRow.setString(1, date);
    for(int i = 0; i <= 11; i++)
    {
      insertRow.setString(i+2,apps[i]); //setting from column_2 using begining of said array
    }
    for(int j = 0; j <= 4; j++)
    {
      insertRow.setInt(j+14, stats[j]); //setting from column_14 using begining of said array
    }
    insertRow.setString(19, noteData);

and the update:

String update = "UPDATE " +tableName+ " SET column_1 = ?, column_2 = ?,.... WHERE column_1 = " +date;

davy

[ May 01, 2007: Message edited by: Davy Kelly ]
[ May 01, 2007: Message edited by: Davy Kelly ]