Writing data to a Database

[Frank Black]

HI,

I'm trying a simple write to a small database in a MS Excel file using the following;

import java.sql.*;

class Insert

{
public static void main(String args[])

{

try
{


Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection conn = DriverManager.getConnection("jdbc:odbc:project2");
String ins = "INSERT INTO logon VALUES(?,?,?)";

PreparedStatement pstmt = conn.prepareStatement(ins);
pstmt.setString(1, "X");
pstmt.setString(2, "Y");
pstmt.setString(3, "Z");

pstmt.executeUpdate();

conn.close();
}

catch (Exception e)
{
System.out.println(e);
}


}

}

It gives me the SQL error "[ODBC EXCEL DRIVER] Operation must use an updateable query"

Can anyone give me a hint?

Cheers.

[ edited to disable smilies -ds ]
[ June 10, 2004: Message edited by: Dirk Schreckmann ]

[Ron Hamilton]

I didn't know that you could use JDBC to write to an excel file. Can you repost your getConnection() method? (Those smiley faces are hard to read.)

I'm curious what connection string you're using to connect.

Thanks.

[Stefan Wagner]

Those smileys are common usage in the jdbc-forum, where this thread belongs.

Connection conn = DriverManager.getConnection("jdbc : odbc : project2");

[Dirk Schreckmann]

Moving this to the JDBC forum...

[Glen D'cruz]

Originally posted by Frank Black:
HI,

I'm trying a simple write to a small database in a MS Excel file using the following;

import java.sql.*;

class Insert

{
public static void main(String args[])

{

try
{


Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection conn = DriverManager.getConnection("jdbc dbc roject2");
String ins = "INSERT INTO logon VALUES(?,?,?)";

PreparedStatement pstmt = conn.prepareStatement(ins);
pstmt.setString(1, "X");
pstmt.setString(2, "Y");
pstmt.setString(3, "Z");

pstmt.executeUpdate();

conn.close();
}

catch (Exception e)
{
System.out.println(e);
}


}

}

It gives me the SQL error "[ODBC EXCEL DRIVER] Operation must use an updateable query"

Can anyone give me a hint?

Cheers.

[ edited to disable smilies -ds ]

[ June 10, 2004: Message edited by: Dirk Schreckmann ]

Hai,
You shouod query the database and not use the "INSERT INTO VALUES...."
EG: String query = (select * from tablename where columnNasme = ?);
pstmt.setString(1,X);

// To check the results use this method which is the easy way
ResultSet rs = pstmt. executeQuery();
String x, y,....
while(rs.next())
{
x = rs.getString("X");
y = rs.getString("Y");
System.out.print(x + " : " + y);
}

[Glen D'cruz]

Originally posted by Frank Black:
HI,

I'm trying a simple write to a small database in a MS Excel file using the following;

import java.sql.*;

class Insert

{
public static void main(String args[])

{

try
{


Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection conn = DriverManager.getConnection("jdbc dbc roject2");
String ins = "INSERT INTO logon VALUES(?,?,?)";

PreparedStatement pstmt = conn.prepareStatement(ins);
pstmt.setString(1, "X");
pstmt.setString(2, "Y");
pstmt.setString(3, "Z");

pstmt.executeUpdate();

conn.close();
}

catch (Exception e)
{
System.out.println(e);
}


}

}

It gives me the SQL error "[ODBC EXCEL DRIVER] Operation must use an updateable query"

Can anyone give me a hint?

Cheers.

[ edited to disable smilies -ds ]

[ June 10, 2004: Message edited by: Dirk Schreckmann ]

I had written an example, which i realise is not exactly wwhat you need.
The example i had written is for inserting data, by first querying the database and then inserting the data. But what you need is inserting data.
There are two ways you can do it as i have done the same example using Excel., but without using the prepared statement which i feel is not unecessay ,and i doubt it works as I tried your example with a few varations. I might be wrong too.
(1) static String SQLCommand =
"INSERT INTO TABLE_NAME" +
"VALUES" +
"(ID,FNAME, MI, LNAME......);"; // AN EXAMPLE

OR
(2) Class forName("sun.jdbc.odbc.Jdbc.OdbcDriver");
DriverManager.registerDriver(new JdbcOdbcDriver());
connection con = DriverManager.getConnection(url);
Statement stmt = con.createStatement (ResultSet.TYPE_SCROLL_INSENSITIVE,
Resultset.CONCUR_UPDATABLE);
ResultSet rs.executeQuery("SELECT* FROM TABLENAME);
rs.moveToInsertRow();
rs.updateInt("ID" 1);
rs.updateString("FNAME", "GLEN");
etc....
rs.insertRow(); // very important
I have no time but if you cannot understand it i will write the whole program out during the week end Ok

[Vero Aa]

if you used the Microsoft Data Sources (ODBC)/JDBC-ODBC bridge driver
to connect to your MS Excel database,

=> you must deselect the Read Only option

for forther information :
http://www.devx.com/Java/Article/17848