Same SQl query runs in access but failed in sql script in access using java

Question

when i run the query in database the following query works

 INSERT INTO `M_DT_SaveAndClose` (`StoryName`, `Assignee`, `Createddate`, `CreatedBy`)
 VALUES (' DT_0019_BGL_2014_11_01_AM_01_MN_01_11_2014_12_07_06_281_000772.xml ',
 ' T0003SathyabamaT ', ' 2014-11-01 12:08:22  ', ' Admin  ')

But when i run the same query via sql script using java it throws error as

"Error executing: INSERT INTO `M_DT_SaveAndClose` (`StoryName`, `Assignee`, `Createddate`, `CreatedBy`) 
   VALUES (' DT_0019_BGL_2014_11_01_AM_01_MN_01_11_2014_12_07_06_281_000772.xml ',
   ' T0003SathyabamaT ', ' 2014-11-01 12:08:22  ', ' Admin  ').
 Cause: net.ucanaccess.jdbc.UcanaccessSQLException: data exception: invalid datetime format"

Query generation step in java :

   savequery = "INSERT INTO `M_DT_SaveAndClose` (`StoryName`, `Assignee`, `Createddate`, `CreatedBy`) VALUES (' " + storyidgen.concat(".xml").trim() + " ', ' " + usernames.trim() + " ', ' " + dateFormat.format(date).trim() + "  ', ' Admin  ');";

Access table structure:

   CREATE TABLE M_DT_SaveAndClose (\n"
            + "  slno Integer,\n"
            + "  StoryName VARCHAR,\n"
            + "  Assignee VARCHAR,\n"
            + "  [Createddate] DateTime,\n"
            + "  CreatedBy VARCHAR,\n"
            + "  [createdtime] DateTime\n"
            + ")

I am using ucanaccess-2.0.6.2.jar. Suggest some idea.

Why dont you trim ' 2014-11-01 12:08:22 ' before inserting? Did you try trimming this and then running query? — nullptr, Nov 01 '14 at 06:54

score 1 · Answer 1 · answered Nov 01 '14 at 06:55

1

I see a lot of white space that looks suspicious in your query. I suggest you remove it,

INSERT INTO `M_DT_SaveAndClose` 
    (`StoryName`, `Assignee`, `Createddate`, `CreatedBy`)
VALUES 
    ('DT_0019_BGL_2014_11_01_AM_01_MN_01_11_2014_12_07_06_281_000772.xml',
     'T0003SathyabamaT', '2014-11-01 12:08:22', 'Admin')

answered Nov 01 '14 at 06:55

Elliott Frisch

198,278
20
158
249

No am using trim during query generation – Dhinakar Nov 01 '14 at 06:55
@Dhinakar Not according to your Exception (or the query you posted). – Elliott Frisch Nov 01 '14 at 06:56

score 1 · Answer 2 · answered Nov 01 '14 at 09:04

Indeed, it is the leading and trailing spaces in the string literal for [Createddate] that are messing you up. Simply changing

... + " ', ' " + dateFormat.format(date).trim() + " ', ' Admin ' ...

to

... + " ', '" + dateFormat.format(date).trim() + "', ' Admin ' ...

made the error go away. However, your code will be cleaner, easier to maintain, and safer if you use a parameterized query like this

String savequery = 
        "INSERT INTO M_DT_SaveAndClose (StoryName, Assignee, Createddate, CreatedBy) " +
        "VALUES (?,?,?,?)"; 
try (PreparedStatement ps = conn.prepareStatement(savequery)) {
    ps.setString(1, storyidgen.concat(".xml").trim());
    ps.setString(2, usernames.trim());
    ps.setTimestamp(3, new Timestamp(date.getTime()));
    ps.setString(4, "Admin");
    ps.executeUpdate();
}

Same SQl query runs in access but failed in sql script in access using java

2 Answers2