I keep getting this exception. I want to insert using a query into a table called Toy.
It’s in this order as below:
Toy ID
, Toy Name
, age
, price(as currency data type)
, color
Exception: invalid character value for cast
x
import java.sql.*;
public class market{
public static void main(String[] args) throws Exception {
Connection c = DriverManager.getConnection("jdbc:ucanaccess://C:\Users\Thekr\OneDrive\Documents\Market.accdb");
Statement stmt = c. createStatement();
String query4= "INSERT INTO Toy VALUES (6,'JB Truck',3,20.00,'Yellow')";//here is where the exception is happening the data type of each column: integer,string,integer,currency data type that I dont know what is it,string
stmt.executeUpdate(query4);
// the excepting is :
// Exception in thread "main" net.ucanaccess.jdbc.UcanaccessSQLException:
// > UCAExc:::5.0.0-SNAPSHOT data exception: invalid character value for
// > cast at
// > net.ucanaccess.jdbc.UcanaccessStatement.executeUpdate(UcanaccessStatement.java:230)
// > at market.main(market.java:11) Caused by: java.sql.SQLDataException:
// > data exception: invalid character value for cast at
// > org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source) at
// > org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source) at
// > org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source) at
// > org.hsqldb.jdbc.JDBCStatement.executeUpdate(Unknown Source) at
// > net.ucanaccess.jdbc.ExecuteUpdate.executeWrapped(ExecuteUpdate.java:65)
// > at
// > net.ucanaccess.jdbc.AbstractExecute.executeBase(AbstractExecute.java:264)
// > at net.ucanaccess.jdbc.ExecuteUpdate.execute(ExecuteUpdate.java:48)
// > at
// > net.ucanaccess.jdbc.UcanaccessStatement.executeUpdate(UcanaccessStatement.java:228)
// > ... 1 more Caused by: org.hsqldb.HsqlException: data exception:
// > invalid character value for cast at
// > org.hsqldb.error.Error.error(Unknown Source) at
// > org.hsqldb.error.Error.error(Unknown Source) at
// > org.hsqldb.Scanner.convertToNumber(Unknown Source) at
// > org.hsqldb.types.NumberType.convertToType(Unknown Source) at
// > org.hsqldb.StatementDML.getInsertData(Unknown Source) at
// > org.hsqldb.StatementInsert.getResult(Unknown Source) at
// > org.hsqldb.StatementDMQL.execute(Unknown Source) at
// > org.hsqldb.Session.executeCompiledStatement(Unknown Source) at
// > org.hsqldb.Session.executeDirectStatement(Unknown Source) at
// > org.hsqldb.Session.execute(Unknown Source) ... 7 more
}
}
Advertisement
Answer
So I found out what was causing the Exception, ucanaccsess does not support a direct insertion of data,so you need to specify the column of the inserted data so the only change to my code will be :
String query4="INSERT INTO Toy(ToyID,ToyName,Age,Price,Color) VALUES (6,'JB Truck',3,20.00,'Yellow')";