SQLException: Data truncated

[joseph okon]

hi
I am trying to insert double data type into my database but it is giving me an exception which goes like this

  C:\Projects\Jude>javac DatabaseOperation.java

C:\Projects\Jude>java DatabaseOperation
Class Loaded
Connection is made
GtBank
Emem
1222
191
100.43
200.53
20/01/10
java.sql.SQLException: Data truncated for column 'debit_amt' at row 1
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1056)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:957)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3376)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3308)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1837)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1961)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2537)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2466)
        at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:735)
        at DatabaseOperation.<init>(DatabaseOperation.java:61)
        at DatabaseOperation.main(DatabaseOperation.java:77)
SQLException: Data truncated for column 'debit_amt' at row 1

C:\Projects\Jude>  

this my java code

  import java.awt.*;
import java.awt.event.*;
import java.sql.*;
import java.io.*;
import javax.swing.*;
import javax.swing.event.*;
import javax.sql.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DatabaseOperation {

		String bankType = "";
		String payeeName = "";
		int chequeNum = 0;
		int accountCode = 0;
		int pvNumber = 0;
		double debit_Amt = 0.0;
		double credit_Amt = 0.0;
		String TransactionDate = "";
		
			public DatabaseOperation(String bank,int cheque,double debit,double credit,int code,int pv,String payee,String date) {
			  String url="jdbc:mysql://localhost:3306/sarplast";
			  Connection sarConnection;
			  String user ="root";
			  String pass ="idoreyin";
			  //made Database connection.
				try {
				     
						sarConnection = DriverManager.getConnection(url,user,pass);
							if (sarConnection.isReadOnly()) {
                               System.out.println("Connection is read only");
							} else {
                               //sarConnection.setReadOnly(true);
							}

				sarConnection.setAutoCommit(false);
				System.out.println("Connection is made");
				
				
				bankType = bank;
				payeeName = payee;
				chequeNum = cheque;
				accountCode = code;
				pvNumber = pv;
				debit_Amt = debit;
				credit_Amt = credit;
				TransactionDate = date;
				System.out.println(bankType);
				System.out.println(payeeName);
				System.out.println(chequeNum);
				System.out.println(accountCode);
				System.out.println(debit_Amt);
				System.out.println(credit_Amt);
				System.out.println(TransactionDate);
				
				//create sql statement to insert into the database.
				String sqlQuery = "insert into account(banksName,chequeNum,debit_amt,credit_amt,"
                                + "accountCode,pvNumber,payeeName,TransactionDate)"      
                                + " values('bankType',chequeNum,'debit_Amt','credit_Amt','accountCode',"
                                 + "'pvNumber','payeeName','TransactionDate')":
				Statement stat = sarConnection.createStatement();
				stat.execute(sqlQuery);
				stat.close();
			}
			
				catch(Exception sqle){
				sqle.printStackTrace();
				
						
				System.out.println("SQLException: " + sqle.getMessage());
				//System.out.println("SQLException: " + sqle.getSQLState());
				//System.out.println("SQLException: " + sqle.getErrorCode());
				System.out.println("SQLException: " + sqle.getCause());
				}
				
				
				
			}
			
			
			public static void main(String args[]) throws Exception{
					DatabaseOperation dbo = new DatabaseOperation("GtBank",1222,100.43,200.53,191,002,"Emem","20/01/10");
			}
			
			{ 
				try{
					Class.forName("com.mysql.jdbc.Driver");
						System.out.println("Class Loaded");
				}catch(ClassNotFoundException cnf) {
					System.out.println("Class not Loaded");
				}
			}


}  

this is my database disription

  
mysql> describe account;
+-----------------+-------------+------+-----+---------+----------------+
| Field           | Type        | Null | Key | Default | Extra          |
+-----------------+-------------+------+-----+---------+----------------+
| accountID       | int(11)     | NO   | PRI | NULL    | auto_increment |
| banksName       | varchar(20) | NO   |     |         |                |
| chequeNum       | int(11)     | YES  |     | NULL    |                |
| debit_amt       | double      | NO   |     |         |                |
| credit_amt      | double      | NO   |     |         |                |
| accountCode     | int(11)     | YES  |     | NULL    |                |
| pvNumber        | int(11)     | YES  |     | NULL    |                |
| payeeName       | varchar(30) | NO   |     |         |                |
| TransactionDate | date        | YES  |     | NULL    |                |
+-----------------+-------------+------+-----+---------+----------------+
9 rows in set (0.13 sec)

mysql>  

can any member of the ranch tell me what is happening cause am loss , i can think of anything again
thanks in advance

[Ulf Dittmer]

The code doesn't actually use any of the parameters you're passing to it - it's passing along string literals. Instead of

... values('bankType',chequeNum, ...

you need something like

... values('"+bankType+"', "+chequeNum+", ...

It would be much better use PreparedStatement for this - that way you avoid the string concatenation, and also don't need to worry about quotes around string arguments.

[joseph okon]

hi Dittmer
sorry i don't really understand what you mean.
please can you expanciate it for me maybe with an illustration code
thanks
joe

[Ulf Dittmer]

In that case it's probably best to start by working through the JDBC tutorial (http://download.oracle.com/docs/cd/E17409_01/javase/tutorial/jdbc/index.html), and at first to get much simpler tables to work.