JDBC batch

[rsasai]

Hi Tim
Do you support jdbc batch like these?
pstmt.addBatch()
pstmt.executeBatch()

Hana does not support multiple row insert...
-Uppsax

[timyates]

No, currently there's no support for batches

It will insert a series of things one after another if you pass a list of
lists to the insert handler

But not inside a batch

Tim

On 23 July 2015 at 12:54, Ryu Sasai [email protected] wrote:

Hi Tim
Do you support jdbc batch like these?
pstmt.addBatch()
pstmt.executeBatch()

Hana does not support multiple row insert...
-Uppsax

—
Reply to this email directly or view it on GitHub
#32.

[rsasai]

INSERT INTO table ("name","age") VALUES (?,?),(?,?),(?,?)

works with mysql but not HANA, which does not support multiple row insert.

Would it be possible for you to implement batch support, please?

-Uppsax

[timyates]

You should be able to do

INSERT INTO table ("name","age") VALUES (?,?)

Then pass

[['Tim',21],['Ryu',21]]

No?
On 23 Jul 2015 13:27, "Ryu Sasai" [email protected] wrote:

INSERT INTO table ("name","age") VALUES (?,?),(?,?),(?,?)

works with mysql but not HANA, which does not support multiple row insert.

Would it be possible for you to implement batch support, please?

-Uppsax

—
Reply to this email directly or view it on GitHub
#32 (comment)
.

[rsasai]

Yes, that's been perfectly working with mysql.
I am porting code for HANA, and HANA does not support multiple row insert, therefore, HANA driver complains when it parses
VALUES (?,?),(?,?)

I don't really want to call insert 100 times for 100 row insert.

Therefore, I am wondering if jdbc-module will start supporting batching like
addBatch()
pstmt.executeBatch()

-Uppsax

[timyates]

I don't think it does (?,?),(?,?)

It executes statements one after the other

I may be wrong...

Not sure when I'll get time to look into batching, I suspect it will end up
with the same thing...?
On 23 Jul 2015 1:41 pm, "Ryu Sasai" [email protected] wrote:

Yes, that's been perfectly working with mysql.
I am porting code for HANA, and HANA does not support multiple row insert,
therefore, HANA driver complains when it parses
VALUES (?,?),(?,?)

I don't really want to call insert 100 times for 100 row insert.

Therefore, I am wondering if jdbc-module will start supporting batching
like
addBatch()
pstmt.executeBatch()

-Uppsax

—
Reply to this email directly or view it on GitHub
#32 (comment)
.

[rsasai]

It will end up with the same functionality.
The thing is, I cannot insert multiple rows with one query into HANA.
At the moment the only solution that they suggest is batching.
I did the experiment with standard JDBC and it works with HANA.

FYI, this is the working code:

                int[] id = new int[] {11,22,33}; 
                int[] ep = new int[] {1111,2222,3333}; 

                PreparedStatement pstmt = connection.prepareStatement(
                            "INSERT INTO table (i,e) VALUES(?,?)"); 

                for (int i=0; i < id.length; i++) { 

                    pstmt.setInt(1, id[i]); 
                    pstmt.setInt(2, ep[i]); 

                    pstmt.addBatch(); 
                } 

                pstmt.executeBatch(); 

If you could find time to implement it, that would be a great help, but if you busy, that's fine. I will try to pursue a workaround.
-Uppsax

[timyates]

And what happens if you do:

PreparedStatement pstmt = connection.prepareStatement( "INSERT INTO table (i,e) VALUES(?,?)"); 
for (int i=0; i < id.length; i++) { 
    pstmt.setInt(1, id[i]); 
    pstmt.setInt(2, ep[i]); 

    pstmt.executeUpdate(); 
} 

As that's what the the mod does

[rsasai]

That should be fine because each iteration inserts only one row.

So, when you do

{
  action: "insert",
  stmt: "INSERT INTO xxx( a, b ) VALUES( ?, ? )",
  values: [ [ 10, 20 ], ... ]
}

if there are 1000 arrays in 'values' array, the mod issues 1000 separate inserts?
If so, it seems to me that implementing batch is a good idea, 'cause that would issue only one insert.

I don't think it does (?,?),(?,?)

I will review this bit in my code, because if the mod is doing the way you say, there's no reason for the mod not working with HANA. I might be doing something wrong.

-Uppsax

[timyates]

if there are 1000 arrays in 'values' array, the mod issues 1000 separate
inserts?

Yes

On 23 July 2015 at 17:38, Ryu Sasai [email protected] wrote:

That should be fine because each iteration inserts only one row.

So, when you do

{
action: "insert",
stmt: "INSERT INTO xxx( a, b ) VALUES( ?, ? )",
values: [ [ 10, 20 ], ... ]
}

if there are 1000 arrays in 'values' array, the mod issues 1000 separate
inserts?
If so, it seems to me that implementing batch is a good idea, 'cause that
would issue only one insert.

I don't think it does (?,?),(?,?)

I will review this bit in my code, because if the mod is doing the way you
say, there's no reason for the mod not working with HANA. I might be doing
something wrong.

-Uppsax

—
Reply to this email directly or view it on GitHub
#32 (comment)
.

[rsasai]

The mod is working perfectly with HANA with multiple row insert.
My sql was wrong. Thank you very much.

Although, I still think that it would be better if the mod had batch insert.
1000 separate inserts through network doesn't sound right against just 1 insert.

Could you consider implementing batch insert some time when you have time, please?
addBatch, executeBatch

-Uppsax

[timyates]

Cool :-) I'll leave this issue open to remind me ;-)

Wondering whether it should be a parameter to 'insert' (so that it runs all
the inserts through a batch), or a 'startBatch' message (with timeouts,
rollbacks, etc) and an 'executeBatch' message to commit the batch update

Quite like the former, as it's less work ;-) But the second route is
probably the right one, as it will reduce message size for thousands of
inserts

;-)

On 24 July 2015 at 12:21, Ryu Sasai [email protected] wrote:

The mod is working perfectly with HANA with multiple row insert.
My sql was wrong. Thank you very much.

Although, I still think that it would be better if the mod had batch
insert.
1000 separate inserts through network doesn't sound right against just 1
insert.

Could you consider implementing batch insert some time when you have time,
please?
addBatch, executeBatch

-Uppsax

—
Reply to this email directly or view it on GitHub
#32 (comment)
.