newbie - jdbc Problem

X

Xarky

Hi,
I have the following code:

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:eek:dbc: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
 
L

Lee Fesperman

Xarky said:
Hi,
I have the following code:

public class DatabaseManager
{
...

ResultSet result;
int data;

result = stmt.executeQuery ("SELECT hours FROM HoursLog WHERE
programmer='Pete';");
Click to expand...

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");
Click to expand...

This will fail until you position the resultset.
Error given is- "java.sql.SQLException: [Microsoft][ODBC Driver
Manager] Invalid cursor state"
Click to expand...

The invalid cursor state is a mispositioned resultset.
 
M

Martin Meier

Hi,
I does have really read your statement, but try to understand the following
example, which is ment to be a framework to you:
Sorry, but I am from germany and some statements and exceptions are still in
german, so ignore them or make them fit to your sense.
And realize:
1)You have to connect and close the db connection at every time of access.
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 example or framework did work and a test database is created very
quickly:
- crate a database and edit the sql-query statements.
3)for accessing a different database type you have to implement the right
driver

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:eek:dbc: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
 
S

Steve Horsley

Xarky said:
Hi,
I have the following code:
Click to expand...
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> result = stmt.executeQuery ("SELECT hours FROM HoursLog WHERE
programmer='Pete';");
data = result.getInt("hours");
Click to expand...
} // 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
Click to expand...
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
 
R

Rhino

Martin Meier said:
Hi,
I does have really read your statement, but try to understand the following
example, which is ment to be a framework to you:
Sorry, but I am from germany and some statements and exceptions are still in
german, so ignore them or make them fit to your sense.
And realize:
1)You have to connect and close the db connection at every time of access.
Click to expand...

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.
Click to expand...

This is also false! You CAN pass a resultset to another method or class,
just like you can pass any other reference in Java.
This example or framework did work and a test database is created very
quickly:
- crate a database and edit the sql-query statements.
3)for accessing a different database type you have to implement the right
driver

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:eek:dbc: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
Click to expand...
 
R

Rhino

Steve Horsley said:
Xarky said:
Hi,
I have the following code:
Click to expand...
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> result = stmt.executeQuery ("SELECT hours FROM HoursLog WHERE
programmer='Pete';");
data = result.getInt("hours");
Click to expand...
} // 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
Click to expand...
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.
Click to expand...
While this analysis of the original poster's problem is essentially correct,
this solution is not very good: the technique is poor and some other
problems with the original poster's code are not addressed. Here is a better
example that the original poster can imitate:

***************
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
 
S

steve

Steve Horsley said:
Xarky said:
Hi,
I have the following code:
Click to expand...
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> result = stmt.executeQuery ("SELECT hours FROM HoursLog WHERE
programmer='Pete';");
data = result.getInt("hours");
Click to expand...
} // 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
Click to expand...
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.
Click to expand...
While this analysis of the original poster's problem is essentially correct,
this solution is not very good: the technique is poor and some other
problems with the original poster's code are not addressed. Here is a better
example that the original poster can imitate:

***************
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
Click to expand...

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();

}
Click to expand...





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 = (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;
}
 
X

Xarky

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
 
S

steve

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
Click to expand...

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!!!
 
A

Andy Flowers

As identified in another post, the statement
query02 = "UPDATE HoursLog SET hours ="+ data+ "WHERE
programmer='Pete'";
will fail as it will produce incorrect SQL.

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
 
X

Xarky

Hi,
I added the PreparedStatment, and it seems to work till that part.
Now, at runtime it is failing on line
con.setAutoComit(false);



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
 
A

Andy Flowers

PreparedStatement ps = con.prepareStatement ("UPDATE ?HoursLog SET hours = ?
WHERE programmer = 'Pete'");

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.
 
X

Xarky

After fixed the PreparedStatment by removing the ?, an error is being
given at
run time.

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)
 
A

Andy Flowers

Looks like it's an ordering problem related to the driver and how it talks
down to ODBC/Access.

Try moving the con.setAutoCommit(true); to be the first database statement
you use.

Xarky said:
After fixed the PreparedStatment by removing the ?, an error is being
given at
run time.

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 said:
PreparedStatement ps = con.prepareStatement ("UPDATE ?HoursLog SET hours
= ?
WHERE programmer = 'Pete'");

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.
Click to expand...
Click to expand...
 
S

steve

Hi,
I added the PreparedStatment, and it seems to work till that part.
Now, at runtime it is failing on line
con.setAutoComit(false);



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
Click to expand...

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
 
X

Xarky

Thanks,

those solved my problems

steve said:
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
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

try catch block not work in action performed 1
JDBC transaction isolation 16
Connected SQLite to my java program but information are not submitted 2
JDBC Conectivity 2
JDBC: Problem with execution of SQL Statement 3
Apache JDBC utils 44
Help in connecting database(JDBC) 1
jdbc and prepared statement problem 4

Members online

Total: 32 (members: 3, guests: 29)
Robots: 427

Forum statistics

Threads
473,768
Messages
2,569,574
Members
45,049
Latest member
Allen00Reed

Latest Threads

Top