Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Writing data to a Database

 
Frank Black
Greenhorn
Posts: 10
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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("jdbcdbc: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
Greenhorn
Posts: 16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 1923
Linux Postgres Database Scala
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Those smileys are common usage in the jdbc-forum, where this thread belongs.
 
Dirk Schreckmann
Sheriff
Posts: 7023
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Moving this to the JDBC forum...
 
Glen D'cruz
Greenhorn
Posts: 15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic