Trouble with oracle sql in java

Question

Hi I am currently testing out a large PreparedStatement, but I seemed to be stuck in the rut. At the moment I have an error "Caused by: Error : 933, Position : 243"

Here is my code for reference:

//Create a query string
    String sqlQuery=null;
    sqlQuery="insert into tbl_email_template(template_code,template_file_name,template_file,status,"
            + "system,type,email_from,email_subject,email_reply_to,email_content,uploaded_by,"
            + "dcreate,dmodify) values(?,?,?,?,?,?,?,?,?,?,?,?,?);";

    System.out.println(sqlQuery);
    System.out.println("Total:"+rsmd.getColumnCount());
    Clob clob = con.createClob();

    File file = new File("template.msg");
    MapiMessage msg = MapiMessage.fromFile(file.toString());
    FileInputStream fis = new FileInputStream(file);

    clob.setString(1, msg.getBody());

    Date date = new Date(1,2,3);

    PreparedStatement pstmt = con.prepareStatement(sqlQuery);
    pstmt.setString(1, "haha");
    pstmt.setString(2, file.getName());
    pstmt.setBlob(3, fis,file.length());
    pstmt.setString(4, "A");
    pstmt.setString(5, "MRS");
    pstmt.setString(6, "BK");
    pstmt.setString(7, "haha@hoho.hk");
    pstmt.setString(8, msg.getSubject());
    pstmt.setString(9, "harhar@hehe.hk");
    pstmt.setClob(10, clob);
    pstmt.setString(11, "Generic User123");
    pstmt.setDate(12,date);
    pstmt.setDate(13,date);
    pstmt.execute();

    System.out.println("Done");

maybe use [executeUpdate](https://docs.oracle.com/javase/7/docs/api/java/sql/PreparedStatement.html#executeUpdate()) — Scary Wombat, Jun 23 '17 at 04:06

score 1 · Accepted Answer · answered Jun 23 '17 at 03:17

1

The ; should be removed:

sqlQuery="insert into tbl_email_template(template_code,template_file_name,template_file,status,"
        + "system,type,email_from,email_subject,email_reply_to,email_content,uploaded_by,"
        + "dcreate,dmodify) values(?,?,?,?,?,?,?,?,?,?,?,?,?);";

should read:

sqlQuery="insert into tbl_email_template(template_code,template_file_name,template_file,status,"
        + "system,type,email_from,email_subject,email_reply_to,email_content,uploaded_by,"
        + "dcreate,dmodify) values(?,?,?,?,?,?,?,?,?,?,?,?,?)";

For the rest: I can not count the characters as given from your error, so your error might or might not be solved by this....

answered Jun 23 '17 at 03:17

Norbert

6,026
3
17
40

are you sure - see https://stackoverflow.com/questions/18941539/is-the-semicolon-necessary-in-sql – Scary Wombat Jun 23 '17 at 04:05
Yes, look at the comments at the accepted answer there regarding the multiple statements and semi column – Norbert Jun 23 '17 at 04:07
what multiple statements has this OP got? One single insert – Scary Wombat Jun 23 '17 at 04:09
Still the ; works disruptive in this. That is not said that it is the error observed. So the user will have to test and see if it resolves (user seems to be offline). – Norbert Jun 23 '17 at 04:11
see also https://stackoverflow.com/questions/10914269/preparestatement-appears-to-be-stripping-semicolon -- basically it would appear that your answer (at best) not useful or (at worst) is wrong – Scary Wombat Jun 23 '17 at 04:13
My experience: Add a ; in the jdbc.execute leads to issues. – Norbert Jun 23 '17 at 04:15
For once removing a ; actually works and I can run my query – lifrah Jun 23 '17 at 05:16
That's strange, maybe an Oracle driver problem? For completeness can you post you full error – Scary Wombat Jun 23 '17 at 05:33

Trouble with oracle sql in java

1 Answers1