public class DatabaseManager
{
private Connection con;
private Statement stmt;
public DatabaseManager()
{
try
{
System.setProperty("myDataSource",
"c:/practicals/jdbc/database");
Class.forName ("sun.jdbc.odbc.JdbcOdbcDriver");
} // end try
catch(ClassNotFoundException e)
{
System.out.println ("errors");
}
} // end constructor
public void connection()
{
try
{
con = DriverManager.getConnection("jdbc:odbc:myDataSource","","");
stmt = con.createStatement();
// creating the table - can be created only once
stmt.execute("create table HoursLog ("+" programmer varchar(32),"+ "
day char (3)," + "hours integer);");
// populating the table
stmt.execute("insert into HoursLog values ('Pete', 'Mon', 2);");
stmt.execute("insert into HoursLog values ('Steve', 'Mon', 4);");
stmt.execute("insert into HoursLog values ('Wally', 'Tue', 8);");
stmt.execute("insert into HoursLog values ('Molly', 'Mon', 9);");
stmt.execute("insert into HoursLog values ('Pete', 'Thu', 3);");
System.out.println ("table populated");
} // end try statment
catch(SQLException e)
{
System.out.println ("Problems executing statment\n");
System.exit(-1);
} // end catch statment
} // end method connection
public void Transactions()
{
try
{
ResultSet result;
int data;
result = stmt.executeQuery ("SELECT hours FROM HoursLog WHERE
programmer='Pete';");
data = result.getInt("hours");
data = data - 2;
con.setAutoCommit(true);
stmt.executeUpdate ("UPDATE HoursLog SET hours = data WHERE
programmer= 'Pete'");
con.setAutoCommit(false);
} // end try statment
catch(SQLException e)
{
System.out.println ("Problems executing statment\n"+e.toString());
System.exit(-1);
} // end catch statment
} // end method Transactions
} // end class
DatabaseManager myDB = new DatabaseManager();
myDB.connection();
myDB.Transactions();
My problem is in method Transactions. It is giving me an error on
line
data = result.getInt("hours");
What I am trying to do is getting the number of hours of Pete, and
reducing it by 2.
Error given is- "java.sql.SQLException: [Microsoft][ODBC Driver
Manager] Invalid cursor state"
Can someone help me solve my problem
Thanks in Advance
At this point, the resultset is not positioned on a row. You need to position it. Take a
look at next() in java.sql.ResultSet.
> data = result.getInt("hours");
This will fail until you position the resultset.
> Error given is- "java.sql.SQLException: [Microsoft][ODBC Driver
> Manager] Invalid cursor state"
The invalid cursor state is a mispositioned resultset.
--
Lee Fesperman, FFE Software, Inc. (http://www.firstsql.com)
==============================================================
* The Ultimate DBMS is here!
* FirstSQL/J Object/Relational DBMS (http://www.firstsql.com)
Regards
Martin
##################################################################################################
import java.sql.*;
/**
* Database Access Implementation and some test queries
* @version 1.0
*/
public class AccessDB {
//Variablen...
private String dbDriver, dbURL, dbUser, dbPassword;
private Connection conn;
private Statement stmt;
/**
* Instantiates an instance of this class and starts go method.
*/
public static void main(String args[])
{
String dbDriver, dbURL, dbUser, dbPassword;
// MDB Zugriff ohne Authentifizierung
/* Hint: For accessing a mdb ACCESS database under Windows you have to
configure the odbc-environment
* Start-System....-Administration... (sorry I use a german version)
* System-DNS : database name without extension : "DB1"
* local "D:\..\..\DB1.mdb"
*/
dbDriver = "sun.jdbc.odbc.JdbcOdbcDriver";// JDBC driver
dbUser="testman"; // user
dbPassword="test"; // password
dbURL = "jdbc:odbc:DB1"; // protocoll access to database
DB1.mdb
AccessDB mdbaccess = new AccessDB( dbDriver, dbURL, dbUser, dbPassword);
mdbaccess.query();
}
/**
* Initialisierung eines AccessDB Objekts für den Zugriff auf die
* fokusierte Datenbank mit Zugriffsauthentifizierung
*/
public AccessDB(String dbDriver,String dbURL, String dbUser, String
dbPassword)
{
this.dbDriver = dbDriver;
this.dbUser = dbUser;
this.dbPassword = dbPassword;
this.dbURL = dbURL;
// Datenbanktreiber laden
try {
Class.forName(this.dbDriver);
}
catch (ClassNotFoundException e)
{
System.out.println("AccessBD:Initialisierungsfehler: " + e.toString() );
}
}
/**
* Etablishes a connection to database.
*/
public void connect()
{
try
{
conn = DriverManager.getConnection(dbURL, dbUser, dbPassword);
stmt = conn.createStatement();
}
catch (SQLException e)
{
System.out.println("AccessDB:connect(): Datenbank kann nicht geöffnet
werden:"
+ e.toString());
}
}
/**
* Sends a query to the data base and returns a result set
* @param query SQL statement that will sent to data base
* @return The result from database
*/
public ResultSet askDB (String query){
ResultSet rs = null;
try {
rs = stmt.executeQuery(query);
} catch (SQLException e)
{
System.out.println("AccessDB:askDB(): Fehler bei Datenbankabfrage: "
+ e.toString());
}
return rs;
}
/**
* Closes the statement.
*/
public void closeStatement()
{
try
{ stmt.close();}
catch (SQLException e)
{
System.out.println("AccessDB:Datenbankabfrage kann nicht geschlossen
werden: "
+ e.toString());
}
}
/**
* Close connection to mdb.
*/
public void closeConnection()
{
try
{conn.close();}
catch (SQLException e)
{
System.out.println("AccessDB:Datenbank kann nicht geschlossen werden:"
+ e.toString());
}
}
//********************************************************************************************
//********************************************************************************************
/**
* Method with all the required SQL queries.
* Implements the output on the command line.
* Databasetyp : MDB.
*/
private void query()
{
String query;
ResultSet rs;
this.connect(); // connection build up
//DB-Query
query = "SELECT * FROM DB1.Kunden ORDER BY KA";
//WHERE NAME LIKE 'G%'
System.out.println("SQL-Statement: "+ query);
rs = this.askDB(query);
try
{
System.out.println("Kundenstamm :");
System.out.println("=================");
while(rs.next())
{
System.out.println( rs.getString("KNr") +" "+ rs.getString("KNA") +" "+
rs.getString("KA") );
}
System.out.println();
}
catch (SQLException e)
{
System.out.println("AccessDB:goMBD() : Abfragefehler: "
+ e.toString());
}
this.closeStatement(); // Abfrage schliessen.
this.closeConnection(); // DB-Verbindung schliessen.
}
private void DBQuery1()
{
String query;
ResultSet rs;
this.connect(); // DB-Verbindungsaufbau
//DB-Query
query = "SELECT * FROM db2inst1.Staff WHERE NAME LIKE 'G%'";
System.out.println("SQL-Statement: "+ query);
rs = this.askDB(query);
try
{
System.out.println("Namen der Angestellten beginnend mit G :");
System.out.println("=======================================");
while(rs.next())
{
System.out.println( rs.getString(2) );
}
System.out.println();
}
catch (SQLException e)
{
System.out.println("AccessDB:go() : Abfragefehler: "
+ e.toString());
}
this.closeStatement(); // Abfrage schliessen.
this.closeConnection(); // DB-Verbindung schliessen.
}
}// end class accessdb
> } // end method Transactions
> } // end class
>
>
> DatabaseManager myDB = new DatabaseManager();
> myDB.connection();
> myDB.Transactions();
>
> My problem is in method Transactions. It is giving me an error on
> line
> data = result.getInt("hours");
>
> What I am trying to do is getting the number of hours of Pete, and
> reducing it by 2.
>
> Error given is- "java.sql.SQLException: [Microsoft][ODBC Driver
> Manager] Invalid cursor state"
>
> Can someone help me solve my problem
>
Try this:
result = stmt.executeQuery ("SELECT hours FROM HoursLog WHERE programmer='Pete';");
boolean foundSomething = result.next();
if(!foundSomething) {
// do somethig about this error
}
data = result.getInt("hours");
The result set is originally pointing at a place BEFORE the first line of the results.
Steve
NO! That is NOT true. You do NOT have to connect before each access (query)
and close the connection again before you make a new access. You can open a
connection and execute hundreds of queries against the data at that
connection. You don't have to close the connection again until you have done
everything you want to do with the data at that connection.
> 2) You can not return the resultset which open connection to the database,
> because a resultset is only a reference to the values contents by the
> connected database.
This is also false! You CAN pass a resultset to another method or class,
just like you can pass any other reference in Java.
***************
String queryTableSQL =
"select lastname, workdept, salary, hiredate " +
"from " + demoTable + " " +
"where workdept = 'D21' ";
/* Query the demonstration table to get information about certain employees.
*/
Statement queryTableStmt = null;
ResultSet rs01 = null;
try {
queryTableStmt = conn01.createStatement();
rs01 = queryTableStmt.executeQuery(queryTableSQL);
}
catch (SQLException excp) {
System.err.println(CLASS_NAME + "." + METHOD_NAME + " - Encountered
SQLException while trying to get information from " + demoTable + "
table. Error: " + excp);
excp.printStackTrace();
System.exit(16);
}
String spaces = " ";
/* Initialize the host variables used for handling the result set. */
String lastname = null;
String workdept = null;
BigDecimal salary = null;
java.sql.Date hiredate;
/* Print each line of the result set. */
try {
while (rs01.next()) {
lastname = rs01.getString(1);
workdept = rs01.getString(2);
salary = rs01.getBigDecimal(3);
hiredate = rs01.getDate(4);
System.out.println(lastname + spaces + workdept + spaces +
salary.toString() + spaces + hiredate);
}
}
catch (SQLException sql_excp) {
System.err.println(CLASS_NAME + "." + METHOD_NAME + " - Encountered
SQLException while reading " + demoTable + " table. Error: " + sql_excp);
sql_excp.printStackTrace();
System.exit(16);
}
/* Close the result set, dispose of the statement, and commit. */
try {
rs01.close();
queryTableStmt.close();
conn01.commit();
}
catch (SQLException sql_excp) {
System.err.println(CLASS_NAME + "." + METHOD_NAME + " - Encountered
SQLException while closing " + demoTable + " result set, closing statement,
or committing. Error: " + sql_excp);
sql_excp.printStackTrace();
System.exit(16);
}
}
***************
This example will correctly handle the process of looping through a result
set, even if the result set is empty. (If the result set is empty, the while
loop will simply be skipped in its entirety.) Of course you will probably
want to do something more than simply writing the data to the console but
that's up to you.
I would strongly suggest finding a JDBC tutorial or book before proceeding
much further. Google should turn up several online tutorials; see if you can
find one that explains things to you in the way that is clearest to you.
Rhino
Rhino ur code is incorrect , it WILL NOT correctly process a database
connection.
1. it looks like try & catch city
2. DO NOT DO.
try{ setup section 1}
catch{}
try{ try to do something with section1, also do some section 2 stuff}
catch{}
try {
>
> rs01.close();
>
> queryTableStmt.close();
>
> conn01.commit();
>
> }
Do the following for closing connections. it is much safer, and will catch
any problems.
try{ process select , display any results
}
Catch{ display any errors, set any reporting flags}
finally {
if (rset != null) {
try {
rset.close();
} catch (Exception ex) {
//print error here
// Error_stuff.handleError(ex, -1, -1);
}
}
if (queryTableStmt != null) {
try {
queryTableStmt .close();
} catch (Exception ex) {
//print error here
// Error_stuff.handleError(ex, -1, -1);
}
if (conn01 != null) {
try {
conn01.close();
} catch (Exception ex) {
//print error here
// Error_stuff.handleError(ex, -1, -1);
}
}
Do not have multiple try catches, for each operation ,have 1 only and beef
up on you error logic.
because , logically if your first set of code fails, for the select, records,
then everything else will too.
You are just making multiple Exceptions for no reason, where 1 will do.
and WTF is :
conn01.commit();?
you have not written any transactions, DO NOT commit!!, this is a very had
bug to find
for an example of full routine, and how it goes together, see below.
This will return a correctly sizing , table , without it being code
dependent, for col type or size.
by changing the call & execute statements ,it will work for a select.
public Vector getCountrytable() {
Vector returnedvector = new Vector();
try {
String The_qry = "";
ResultSet rset = null;
OracleCallableStatement cstmt = null;
The_qry =
"{?=call external_user.PACKAGE_02.RETURN_countries(" + "'"
+
"')}";
cstmt = (OracleCallableStatement) dbconn.prepareCall(The_qry);
cstmt.registerOutParameter(1, OracleTypes.CURSOR);
cstmt.execute();
//this will change it to a simple sql select ( just remove the above 4
lines)
// String sql ="select * from a_table"
// PreparedStatement st = connection.prepareStatement(sql);
// rset = st.executeQuery(); // Execute Query
rset = ((OracleCallableStatement) cstmt).getCursor(1);
java.sql.ResultSetMetaData rsmd = rset.getMetaData();
int ColumnCount = rsmd.getColumnCount();
// Loop through ResultSet rows
int loop = 0;
while (rset.next()) {
//we could use an object type here, and get the actual data
//without doing a toString. ( see get supplier record)
//but that requires keeping track of the column types
String[] record1 = new String[ColumnCount];
for (int i = 0; i < record1.length; i++) {
record1[i] = (String) rset.getString(i + 1); // COPY THE
DATA TO A LOCAL ARRAY OF THE RIGHT SIZE
}
loop++;
returnedvector.addElement(record1);
}
} catch (Exception e) {
Error_stuff.handleError(e, EXEPTION_ERROR, -1);
}
finally{
if (rset != null) {
try {
rset.close();
} catch (Exception ex) {
Error_stuff.handleError(ex, -1, -1);
}
}
if (cstmt!= null) {
try {
cstmt.close();
} catch (Exception ex) {
// Error_stuff.handleError(ex, -1, -1);
}
}
}
return returnedvector;
}
> Following all your help, I did as follows:
>
> public void Transactions()
> {
> ResultSet result = null;
> String query01 = null;
> String query02 = null;
> boolean flag;
> int data=0;
>
> query01 = "SELECT hours FROM HoursLog WHERE programmer='Pete'";
> query02 = "UPDATE HoursLog SET hours ="+ data+ "WHERE programmer=
> 'Pete'";
> System.out.println ("Trying to make a transaction on HoursLog
> table.");
> try
> {
> System.out.println ("Step 1");
> con.setAutoCommit(true);
> result = stmt.executeQuery (query01);
> System.out.println ("Step 2");
> flag = result.next();
> if (flag)
> {
> data = result.getInt("hours");
> data = data + 2;
> System.out.println ("Step 3");
> stmt.executeUpdate (query02);
> System.out.println ("Step 4");
> } // end if
> else
> {
> System.out.println ("Data searched not found.");
> } // end else
> con.setAutoCommit(false);
> System.out.println ("Transaction on HoursLog table completed.");
> } // end try statment
> catch(SQLException e)
> {
> System.err.println ("DatabaseMangager.Transactions() - Encountered
> SQLException while trying to make a transaction on HoursLog table.");
> System.err.println (e);
> e.printStackTrace();
> System.exit(16);
> } // end catch statment
> } // end method Transactions
>
> Now I, my problem is between Step 3 and Step 4. Here I am executing
> an Update Query. My problem should be that I am not assigning the
> variable data to the query correctly.
>
> Can you please give me further help.
>
>
> Thanks in Advance
>
>
>> Try this:
>> result = stmt.executeQuery ("SELECT hours FROM HoursLog WHERE
>> programmer='Pete';");
>> boolean foundSomething = result.next();
>> if(!foundSomething) {
>> // do somethig about this error
>> }
>> data = result.getInt("hours");
>>
>> The result set is originally pointing at a place BEFORE the first line of
>> the results.
>>
>> Steve
this will not compile, so how do you know it does not work?
int data =0;
query02 = "UPDATE HoursLog SET hours ="+ data+ "WHERE programmer=
'Pete'";
String data="0";
query02 = "UPDATE HoursLog SET hours ="+ data+ "WHERE programmer=
'Pete'";
might work, but you cannot directly add an int to a string.
also this will produce a query like:
UPDATE HoursLog SET hours =0WHERE programmer='Pete'"
notice there is a MISSING SPACE between "0" and WHERE, your query string is
wrong!!!
Also even if this is fixed in your code the hours will ALWAYS be 0 as the
SQL string will be generated before you have read and set up data.
A simple, and better, way to get around this is to use a PreparedStatement
such as
// initialise a prepared statement
PreparedStatement ps = con.prepareStatement("UPDATE HoursLog SET hours=?
WHERE programmer=?");
// read data (and possibly the programmer to update)
....
// and then set the values you have read into the prepared statement
ps.setInt(1, data); // hours to set
ps.setString(2, "pete"); // programmer to update
// and finally execute the update
ps.executeUpdate();
Using a prepared statement will allow you to create the statement in some
initialisation block, such as where you create the connection, and then have
it reused by setting the parameters as needed.
See the API documentation for more info at
http://java.sun.com/j2se/1.5.0/docs/api/java/sql/PreparedStatement.html
and also http://java.sun.com/docs/books/tutorial/jdbc/basics/prepared.html
"Xarky" <berna...@yahoo.com> wrote in message
news:bc42e1a.04110...@posting.google.com...
> Following all your help, I did as follows:
>
> public void Transactions()
> {
> ResultSet result = null;
> String query01 = null;
> String query02 = null;
> boolean flag;
> int data=0;
>
> query01 = "SELECT hours FROM HoursLog WHERE programmer='Pete'";
> query02 = "UPDATE HoursLog SET hours ="+ data+ "WHERE programmer=
> 'Pete'";
> System.out.println ("Trying to make a transaction on HoursLog
> table.");
> try
> {
> System.out.println ("Step 1");
> con.setAutoCommit(true);
> result = stmt.executeQuery (query01);
> System.out.println ("Step 2");
> flag = result.next();
> if (flag)
> {
> data = result.getInt("hours");
> data = data + 2;
> System.out.println ("Step 3");
> stmt.executeUpdate (query02);
> System.out.println ("Step 4");
> } // end if
> else
> {
> System.out.println ("Data searched not found.");
> } // end else
> con.setAutoCommit(false);
> System.out.println ("Transaction on HoursLog table completed.");
> } // end try statment
> catch(SQLException e)
> {
> System.err.println ("DatabaseMangager.Transactions() - Encountered
> SQLException while trying to make a transaction on HoursLog table.");
> System.err.println (e);
> e.printStackTrace();
> System.exit(16);
> } // end catch statment
> } // end method Transactions
>
> Now I, my problem is between Step 3 and Step 4. Here I am executing
> an Update Query. My problem should be that I am not assigning the
> variable data to the query correctly.
>
> Can you please give me further help.
>
>
> Thanks in Advance
>
>
public void Transactions()
{
ResultSet result = null;
String query01 = null;
boolean flag;
int data=0;
query01 = "SELECT hours FROM HoursLog WHERE programmer='Pete'";
System.out.println ("Trying to make a transaction on HoursLog
table.");
try
{
PreparedStatement ps = con.prepareStatement ("UPDATE ?HoursLog SET
hours = ? WHERE programmer = 'Pete'");
con.setAutoCommit(true);
result = stmt.executeQuery (query01);
flag = result.next();
if (flag)
{
data = result.getInt("hours");
data = data + 2;
ps.setInt(1,data);
ps.executeUpdate();
} // end if
else
{
System.out.println ("Data searched not found.");
} // end else
System.out.println ("11");
con.setAutoCommit(false);
System.out.println ("Transaction on HoursLog table completed.");
} // end try statment
catch(SQLException e)
{
System.err.println ("DatabaseMangager.Transactions() - Encountered
SQLException while trying to make a transaction on HoursLog table.");
System.err.println (e);
e.printStackTrace();
System.exit(16);
} // end catch statment
} // end method Transactions
"Andy Flowers" <notsu...@nowayhose.com> wrote in message news:<Fwmjd.36$127...@newsfe2-gui.ntli.net>...
The first ? (making ?Hourslog) is not required I believe ?
What error are you receiving ? Is it an exception or a compile time error ?
If it's an exception can you post the details.
"Xarky" <berna...@yahoo.com> wrote in message
news:bc42e1a.04110...@posting.google.com...
The error is given -
java.sql.SQLException: [Microsoft][ODBC Microsoft Access
Driver]Attribute cannot be set now
java.sql.SQLException: [Microsoft][ODBC Microsoft Access
Driver]Attribute cannot be set now
at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6879)
at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7036)
at sun.jdbc.odbc.JdbcOdbc.SQLSetConnectOption(JdbcOdbc.java:5068)
at sun.jdbc.odbc.JdbcOdbcConnection.setAutoCommit(JdbcOdbcConnection.java:651)
at DatabaseManager.Transactions(DatabaseManager.java:189)
at Launcher.main(Launcher.java:13)
"Andy Flowers" <notsu...@nowayhose.com> wrote in message news:<AgKjd.520$tb...@newsfe3-gui.ntli.net>...
Try moving the con.setAutoCommit(true); to be the first database statement
you use.
"Xarky" <berna...@yahoo.com> wrote in message
news:bc42e1a.04110...@posting.google.com...
> Hi,
DO NOT play with the "auto commit" flag during a transaction.
set the "con.setAutoCommit(false); " at the very start , just after opening
the connection .
then use
con.commit(); //to commit stuff to the database or
con.rollback(); //to cancel it
those solved my problems
steve <m...@me.com> wrote in message news:<0001HW.BDB60968...@news.newsguy.com>...