Hi Suresh, Arvin and Selva, I understand the addBatch method. But I have a hard time understanding how compiler can do a good job not knowing the cardinality of the rowset at prepare time. Since we support prepare statement like this: Insert into t (a,b,c) values( ?[1000], ?[1000], ?[1000])
Where cardinality of the rowset is known at compile time, and I can see a very nice associated plan, I am wondering how efficient could be a process where a iterate over a prepare statement like this: Insert into t (a,b,c) values( ?, ?, ?) Where at compile time the prepare have no clue if a rowset plan is appropriate or a non rowset plan is better. Suresh has explained me over the phone that compiler would assume rowset, and recompile in case it is single value… but then if I keep calling this prepared statement with various random number of items, how is the compiler going to successfully prepare, and keep reuse the same plan? (this is really the use-case I am dealing with: variable length rowset at every calls, can go from 1 to 10000) That is why I was hoping to force a fixed cardinality plan, to avoid this difficult situation with varying cardinality that I suspect will result in many recompiles? I know that this syntax would not be jdbc standard (so not portable)… But how about if I am ready to pay the price of using non-standard feature? Since there is no DB like ours anyway ☺… I guess this question is more for dev list, as I am inquiring about possible non standard stuff, given I see half of it is already working (successful compile of Insert into t (a,b,c) values( ?[1000], ?[1000], ?[1000]) – I suspect there might be way to do the other half with some magic words … ? Eric From: Arvind GMAIL [mailto:narain.arv...@gmail.com] Sent: Friday, January 13, 2017 10:52 PM To: user@trafodion.incubator.apache.org Subject: RE: jdbc rowset usage? Hi Eric, Suresh You could also look at some the jdbc tests under the following for addbatch and related examples: https://github.com/apache/incubator-trafodion/tree/master/dcs/src/test/jdbc_test/src/test/java/org/trafodion/jdbc_test Thanks Arvind From: Suresh Subbiah [mailto:suresh.subbia...@gmail.com] Sent: Friday, January 13, 2017 7:50 PM To: user@trafodion.incubator.apache.org<mailto:user@trafodion.incubator.apache.org> Subject: Re: jdbc rowset usage? Hi Eric, The steps Selva mentioned are shown in the examples at https://examples.javacodegeeks.com/core-java/sql/jdbc-batch-insert-example/ Section 4. in this example should be relevant. We should have similar examples in JDBC tests, but I am not able to find them. Thanks Suresh On Fri, Jan 13, 2017 at 6:40 PM, Selva Govindarajan <selva.govindara...@esgyn.com<mailto:selva.govindara...@esgyn.com>> wrote: Just prepare the statement with '?' for parameters Do PreparedStatement.setXXX () for all parameters PreparedStatement.addBatch() Do the above in a loop, when you reach the required rowset size PreparedStatement.executeBatch(). Selva ________________________________ From: Eric Owhadi <eric.owh...@esgyn.com<mailto:eric.owh...@esgyn.com>> Sent: Friday, January 13, 2017 3:52 PM To: user@trafodion.incubator.apache.org<mailto:user@trafodion.incubator.apache.org> Subject: jdbc rowset usage? Hello, I am struggling to find the jdbc syntax to set an dynamic array parameter: Assuming I prepared this statement s with “Insert into t (a,b,c) values( ?[1000], ?[1000], ?[1000])” How do I set each parameter? Assuming a is INT, b is CHAR[10], c is INT? Am I doing something not really supported? Should I use AddBatch instead? Thanks in advance for the help, Eric
