SQL statement too long in jdbc

Question

I am doing a batch insert in SAP HANA and below is my code:

for (int i = 1; i <= ITERATION_MAX; i++) {

        String arraylist=Arrays.toString(listofdata.get(i).get_arraylist.replace("[","").replace("]",""));
        id=listofdata.get(i).get_id();
        name= listofdata.get(i).get_name();
        place=listofdata.get(i).get_place();
        year= listofdata.get(i).get_year();
        day=listofdata.get(i).get_day();
        rollno=   listofdata.get(i).get_rollno();
        main_subject= listofdata.get(i).get_main_subject();
        elective= listofdata.get(i).get_elective();
        Statement stmt = conn.createStatement();
        String sql="INSERT INTO SCHEMA.TABLE values("+

           +name+","
            place+","
            year+day+","
            rollno+","
            main_subject+","
            elective"+","
            "ARRAY("+arraylist+")" ;


        stmt.addbatch(sql);
    }
    stmt.executeBatch();
    stmt.close();
    conn.commit();
    conn.close();

The code works fine but it fails sometimes and the error shows

sql statement too long.

So because of this my whole batch(10000 rows) fails and I don't want to face. It will be good if I can know the maximum SQL statement length that I can use.

have something like

if (sql.length()<max_length){
 stmt.addbatch(sql);
} 
else{
}

But I wanted to make sure that is the maximum length. Any help is appreciated

Use prepared statements. Always specify the name of the columns instead of relying on their order. http://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html — JB Nizet, Mar 17 '17 at 06:35
do batch inserts [properly](http://stackoverflow.com/questions/3784197/efficient-way-to-do-batch-inserts-with-jdbc) — Scary Wombat, Mar 17 '17 at 06:35
have you tried querying M_SYSTEM_LIMITS 2GB seems to be the default maximum size for a query. — BevynQ, Mar 17 '17 at 06:36
@JBNizet I cannot use prepared statements here because HANA Array Type Conflict is there So I need Individual created statements — , Mar 17 '17 at 07:22
@ScaryWombat I hope my insert is proper because i cannot have a prepared statement in my case.Correct me if I am wrong — , Mar 17 '17 at 08:41

score 0 · Accepted Answer · answered Mar 18 '17 at 00:52

0

As @BevynQ mentioned, the current memory limit for SQL statements is 2GB. So you could check against that, I suppose. A more practical approach could be just to go with rule of thumb and do, say, 100 inserts a time.

Generally speaking, I wouldn't recommend using ARRAY data types in HANA at the moment (see the other discussions on this - it's really not that well supported and far from a first-class data type in my eyes).

answered Mar 18 '17 at 00:52

Lars Br.

9,949
2
15
29

Yeah you are correct and thanks for the clarification – Mar 20 '17 at 01:14

SQL statement too long in jdbc

1 Answers1