Oracle Vs Access ! updateString isnt working

[ram kumar]

Am using the same code for oracle and access.

while, oracle is working fine and access is not working

Whats the problem?

objResultSet1.updateString("ROW_STATUS","Y");

objResultSet1.updateRow();

Do these methods work for access.

Its not updating the table rather it shows an exception :

it fails to execute

objResultSet1.updateString("ROW_STATUS","Y");

what i am doing is just i will read all the columns from that row and update the last column as set rowstatus = 'y';




code here :

   package smsproduct.dao;
 
import java.sql.Connection;
 
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
 
import smsproduct.utilities.SMSHandleIdNames;
import smsproduct.utilities.SMSSystemProperties;
 
public class AccessConfigurationReaderDAO implements iConfiguration
{
//To Read ROW by ROW
private Connection conn = null;
private Statement objStatement = null;
private ResultSet objResultSet = null;

//To Update ROW by ROW and also display update status immeditely
private Connection conn1 = null;
private Statement objStatement1 = null;
private ResultSet objResultSet1 = null;
 

private SMSSystemProperties objSMSSystemProperties = new SMSSystemProperties();
 
public String[][] retriveConfigurationDetails() 
{
if(conn==null)
{
conn= new SMSAccessDAOFactory().getConnection();
conn1=new SMSAccessDAOFactory().getConnection();

System.out.println("Connection Created AccessConfigurationReaderDAO retriveConfigurationDetails : "+conn);
}

String stTableName = new String();

String stRecords[][] = null;

int inNoOfRows = 0;

try

{

stTableName = objSMSSystemProperties.readProperties(SMSHandleIdNames.ORACLE_CONFIGURATION_TABLENAME);

System.out.println("stTableName = "+stTableName);

objStatement = conn.createStatement();

//TYPE_FORWARD_ONLY - row updation happens always in forward direction
//CONCUR_UPDATABLE - Multiple read and update at same time.
 
objStatement1 = conn1.createStatement(ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_UPDATABLE);


objResultSet = objStatement.executeQuery("Select count(*) from " + stTableName);

objResultSet.next();

inNoOfRows = objResultSet.getInt(1);

System.out.println("inNoOfRows : "+inNoOfRows);

/* If the initialization database is empty then stop the gateway application */
if(inNoOfRows == 0)
{
//System.out.println(objSMSErrorProperties.readProperties("1007"));
System.out.println("DataBase Intitialization Empty");
System.exit(0);
 
}

int inIndex = 0; //index to iterate upto value(inNoOfRows)

/* Get all the records from the table and populate a 2D string array */
 
objResultSet = objStatement.executeQuery("Select * from " + stTableName + " where ROW_STATUS = 'N' ");

stRecords = new String[inNoOfRows][16];

while(objResultSet.next())
{

//12 Entries totally - 0 to 11 - field names are self-descriptive.

System.out.println("\n\nAccessConfigurationReaderDAO : Reading Row - "+inIndex+"\n");

stRecords[inIndex][0] = objResultSet.getString("PROPNAME");
System.out.println("stRecords[inIndex][] : PROPNAME = "+stRecords[inIndex][0]);

stRecords[inIndex][1] = objResultSet.getString("TCPIP");
System.out.println("stRecords[inIndex][] : TCPIP = "+stRecords[inIndex][1]);

stRecords[inIndex][2] = objResultSet.getString("PORT");
System.out.println("stRecords[inIndex][] : PORT = "+stRecords[inIndex][2]);

stRecords[inIndex][3] = objResultSet.getString("MODEM_MAKE");
System.out.println("stRecords[inIndex][] : MODEM_MAKE = "+stRecords[inIndex][3]);

stRecords[inIndex][4] = objResultSet.getString("SYSCHARSET");
System.out.println("stRecords[inIndex][] : SYSCHARSET = "+stRecords[inIndex][4]);

stRecords[inIndex][5] = objResultSet.getString("GATEWAY_TYPE");
System.out.println("stRecords[inIndex][] : GATEWAY_TYPE = "+stRecords[inIndex][5]);

stRecords[inIndex][6] = objResultSet.getString("SCSA");
System.out.println("stRecords[inIndex][] : SCSA = "+stRecords[inIndex][6]);

stRecords[inIndex][7] = objResultSet.getString("MODEMNUMBER");
System.out.println("stRecords[inIndex][] : MODEMNUMBER = "+stRecords[inIndex][7]);

stRecords[inIndex][8] = objResultSet.getString("BAUDRATE");
System.out.println("stRecords[inIndex][] : BAUDRATE = "+stRecords[inIndex][8]);

stRecords[inIndex][9] = objResultSet.getString("MESSAGEBROKER_IPADDRESS");
System.out.println("stRecords[inIndex][] : MESSAGEBROKER_IPADDRESS = "+stRecords[inIndex][9]);

stRecords[inIndex][10] = objResultSet.getString("QUEUENAME");
System.out.println("stRecords[inIndex][] : QUEUENAME = "+stRecords[inIndex][10]);

stRecords[inIndex][11] = objResultSet.getString("LOG_DB_TYPE");
System.out.println("stRecords[inIndex][] : LOG_DB_TYPE = "+stRecords[inIndex][11]);

stRecords[inIndex][12] = objResultSet.getString("TableNameOrFilePath");
System.out.println("stRecords[inIndex][] : TableNameOrFilePath = "+stRecords[inIndex][12]);

stRecords[inIndex][13] = objResultSet.getString("LOG_CONDITION");
System.out.println("stRecords[inIndex][] : LOG_CONDITION = "+stRecords[inIndex][13]);

stRecords[inIndex][14] = objResultSet.getString("GATEWAY_MODE");
System.out.println("stRecords[inIndex][] : GATEWAY_MODE = "+stRecords[inIndex][14]);

stRecords[inIndex][15] = objResultSet.getString("ROW_STATUS");
System.out.println("stRecords[inIndex][] : ROW_STATUS = "+stRecords[inIndex][15]);

objResultSet1 = objStatement1.executeQuery("select ROW_STATUS from "+stTableName+" where PROPNAME = '"+stRecords[inIndex][0]+"'");
System.out.println("here");

//switches for the first row in the existing result set
objResultSet1.next();
[I]
//An Exception is thrown at this lines of code

//updates the current column pointed by the row cursor
objResultSet1.updateString("ROW_STATUS","Y");

//immediately updates the current cursor row
objResultSet1.updateRow();

//getting immediate updated value form table is possible and displayed
//as a result of this resultset type being
//updateable. - CONCUR_updatable and forward updation Type_forward_only 
//and hence updations are easy and immediate.

System.out.println("\nUpdated ROW_STATUS = "+objResultSet1.getString("ROW_STATUS"));
[/I]inIndex++;
}

return stRecords;
}
catch(SQLException e)
{
System.out.println("SQLException : AccessConfigurationReaderDAO : retriveConfigurationDetails "+e);
}

return null;

}

public void closeConnection() 
{
try
{
objStatement.close();
objStatement1.close();

objResultSet.close();
objResultSet1.close();

conn.close();
conn1.close();

System.out.println("\nAccessConfigurationReaderDAO : closeConnection : ConnectionClosed : ");
}
catch(Exception e)
{
System.out.println("\nAccessConfigurationReaderDAO : closeConnection : Exception while Closing : "+e);
}

}

 
}
 

Please help me out !

[Campbell Ritchie]

What's Access? It has restricted functionality compared to Oracle. But we usually discuss database-associated questions on a different forum, so I shall try moving this topic.

[Paul Sturrock]

while, oracle is working fine and access is not working

Whats the problem?

Probably Access

My guess is your Access driver (or Access itself) doesn't support updatable ResultsSets. Which driver are you using?

[ram kumar]

Originally posted by Paul Sturrock:

Probably Access

My guess is your Access driver (or Access itself) doesn't support updatable ResultsSets. Which driver are you using?

Hey, Both of you campbell / Sturrock thanks for the response.

Yes ! Campbell its Microsoft Access DB.

As per the forum norms you may move this topic to jdbc !


Am speaking about MS-Access, Am using the default driver for ms access found in the odbc in the control panel :

Please suggest me some valid point for that code whih is not working with ms-access !

Provide me a better way of implementing that functionality in ms-access !

else
i would just explain my boss that this isn't possible for this reason !
[ September 10, 2008: Message edited by: ram kumar ]

[Paul Sturrock]

Please suggest me some valid point for that code whih is not working with ms-access !

Access isn't a very good bit of software? The only (free) way to connect to Access from Java is via the JDBC-ODBC bridge and, as the documentation states, this isn't production quality code.


But before I get carried away criticising Access, what is the Exception message you see?

[ram kumar]

Originally posted by Paul Sturrock:

Access isn't a very good bit of software? The only (free) way to connect to Access from Java is via the JDBC-ODBC bridge and, as the documentation states, this isn't production quality code.


But before I get carried away criticising Access, what is the Exception message you see?

java.sql.SQLException : Too few Parameters : Expected 1


[ September 10, 2008: Message edited by: ram kumar ]