JDBC-ODBC

[Patrick Mugabe]

Hi everyone,
Can someone give me an example of how to connect to SQL Server 2000 using Java and do an 'insert' and be able to access the data from the SQL Server Query Analyser.
I have done several programs which are compiling with no errors but then when I go to check for the results I am findind nothing.
Please help...

[Jamie Robertson]

why don't you post your code that isn't working properly and we can give you some suggestions to make it work as you intended.
Jamie

[Patrick Mugabe]

Here is a section of my code that connects to the database:

if(obj == btOK)
{
//add try and Catch Blocks for Exception Handling
try
{
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    Connection con;
    //establish the connection with the data source
    String url = "jdbc:odbc:MyDataSource";
    con = DriverManager.getConnection(url, "sa" ,"mugapatr");
    //create the statement Object
    PreparedStatement stat = con.prepareStatement("insert into Shopper(shopperID, firstName, lastName, emailId, address, city, state, "
      + "countryId, phone, creditCardType, creditCardNo, expiryDate, pword) values(?,?,?,?,?,?,?,?,?,?,?,?,?)");
    //fill up the parameter values from the controls
    stat.setString(1, txtShopperID.getText());
    stat.setString(2, txtFName.getText());
    stat.setString(1, txtLastName.getText());
    stat.setString(1, txtEmail.getText());
    stat.setString(1, txtAddress.getText());
    stat.setString(1, txtCity.getText());
    stat.setString(1, txtState.getText());
    stat.setString(1, txtCountryID.getText());
    stat.setString(1, txtPhone.getText());
    stat.setString(1, (String)comboCreditCardType.getSelectedItem());
    stat.setString(1, txtCreditCardNo.getText());
    stat.setString(1, txtExpiryDate.getText());
    stat.setString(1, txtPword.getText());
    //insert record into table
    stat.executeUpdate();
    stat.close();
    con.close();
}//end try
catch(Exception ex)
{
System.out.println("Error encountered while entering data :" + ex);
}//end catch
}//end if with try and catch block

[ Edited by Dave to fix and format code ]
[ January 30, 2002: Message edited by: David O'Meara ]

[David O'Meara]

You need setString(1,...), setString(2,...), setString(3, ...) etc.
At the moment they are 1,2,1,1,1 etc so you are overwriting the first parameter.
I usually do this with an increment so that I can add/remove and re-order attributes without having to redo the indicies constantly.

int i=0;//MAKE SURE YOU START AT ONE!!!
stat.setString(++i, txtShopperID.getText());
stat.setString(++i, txtFName.getText());
stat.setString(++i, txtLastName.getText());
stat.setString(++i, txtEmail.getText());
stat.setString(++i, txtAddress.getText());
stat.setString(++i, txtCity.getText());
stat.setString(++i, txtState.getText());
stat.setString(++i, txtCountryID.getText());
stat.setString(++i, txtPhone.getText());
stat.setString(++i, (String)comboCreditCardType.getSelectedItem());
stat.setString(++i, txtCreditCardNo.getText());
stat.setString(++i, txtExpiryDate.getText());
stat.setString(++i, txtPword.getText());

Dave.

[Patrick Mugabe]

Sorry that was my mistake.
I have already corrected that part and it's still not working.
This is the code:

if(obj == btOK){//add try and Catch Blocks for Exception Handlingtry{
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    Connection con;    //establish the connection with the data source
    String url = "jdbc:odbc:MyDataSource";
    con = DriverManager.getConnection(url, "sa" ,"mugapatr");
    //create the statement Object
    PreparedStatement stat = con.prepareStatement("insert into Shopper(shopperID, firstName, lastName, emailId, address, city, state, "
      + "countryId, phone, creditCardType, creditCardNo, expiryDate, pword) values(?,?,?,?,?,?,?,?,?,?,?,?,?)");
    //fill up the parameter values from the controls
    stat.setString(1, txtShopperID.getText());
    stat.setString(2, txtFName.getText());
    stat.setString(3, txtLastName.getText());
    stat.setString(4, txtEmail.getText());
    stat.setString(5, txtAddress.getText());
    stat.setString(6, txtCity.getText());
    stat.setString(7, txtState.getText());
    stat.setString(8, txtCountryID.getText());
    stat.setString(9, txtPhone.getText());
    stat.setString(10, (String)comboCreditCardType.getSelectedItem());
    stat.setString(1, txtCreditCardNo.getText());
    stat.setString(1, txtExpiryDate.getText());
    stat.setString(1, txtPword.getText());
    //insert record into table
    stat.executeUpdate();
    stat.close();
    con.close();
}//end try
catch(Exception ex)
{
    System.out.println("Error encountered while entering data :" + ex);
}
//end catch
}
//end if with try and catch block

[ Edited to add code block again ]
[ January 31, 2002: Message edited by: David O'Meara ]

[David O'Meara]

There's a bug in either the JDBC-ODBC bridge or Access (not sure which, I'm assuming the bridge) where inserts are not
You can do a search in this forum for other solutions, but you can do these things: Firstly, explicitly commit your transactions. Secondly, perform a small select on the same connection.
I've never had the problem, but I've heard these steps can provide a solution.
Dave.

[Jamie Robertson]

We need more information on the exception thrown. Change your catch block to the following:

try
{
//....code....
}//end try
catch(SQLException sqle)
{
System.out.println("SQLException: " + sqle.getMessage());
sqle.printStackTrace();
}
catch(Exception ex)
{
System.out.println("Error encountered while entering data :" + ex.getMessage());
}
//end catch

let us know what the error message is. About David's suggestion to "Secondly, perform a small select on the same connection." I'm sure this is an access only problem (Access ODBC driver). Never seen it on anything other than MSAccess.
Jamie